[Oracle] PL/SQL 기초 (정의, 특징, 사용방법, 변수선언 방법)

[Oracle] PL/SQL 기초 (정의, 특징, 사용방법, 변수선언 방법)

안녕하세요. 갓대희 입니다. 이번 포스팅은 [PL/SQL 기초] 입니다. :) 

▶ PL/SQL (Procedural Language extension to SQL)

 - SQL을 확장한 절차적 언어(Procedural Language)이다. 

 - 관계형 데이터베이스에서 사용되는 Oracle의 표준 데이터 엑세스 언어로, 프로시저 생성자를 SQL과 완벽하게 통합한다.

 - 유저 프로세스가 PL/SQL 블록을 보내면, 서버 프로세서는 PL/SQL Engine에서 해당 블록을 받고 SQL과 Procedural를 나눠서 SQL은 SQL Statement Executer로 보낸다.

 - PL/SQL 프로그램의 종류는 크게 Procedure, Function, Trigger 로 나뉘어 진다.

 - 오라클에서 지원하는 프로그래밍 언어의 특성을 수용하여 SQL에서는 사용할수없는 절차적 프로그래밍 기능을 가지고 있어 SQL의 단점을 보완하였다.

▶ 장점

 - 프로시저 생성자와 SQL의 통합

 - 성능 향상 : 잘 만들어진 PL/SQL 명령문이라는 가정하에 좋아진다.

 - 모듈식 프로그램 개발 가능 : 논리적인 작업 을 진행하는 여러 명령어들을 하나의 블록을 만들 수 있다.

 - 이식성이 좋다

 - 예외 처리 가능

 => 또한 SQL의 다음 단점을 해결 가능하다.

1) 변수가 없다.

2) 한번에 하나의 명령문만 사용 가능하기 떄문에 트래픽이 상대적으로 증가한다.

3) 제어문이 사용 불가. (IF, LOOP)

4) 예외처리가 없다. 등등

▶ 기본 특징

 - 블록 단위의 실행을 제공한다. 이를 위해 BEGIN과 END;를 사용한다. 그리고 마지막 라인에 /를 입력하면 해당 블록이 실행된다.

 - 변수, 상수 등을 선언하여 SQL과 절차형 언어에서 사용

 - 변수의 선언은 DECLARE절에서만 가능하다. 그리고 BEGIN 섹션에서 새 값이 할당될 수 있다.

 - IF문을 사용하여 조건에 따라 문장들을 분기 가능

 - LOOP문을 사용하여 일련의 문장을 반복 가능

 - 커서를 사용하여 여러 행을 검색 및 처리

 - [ PL/SQL에서 사용 가능한 SQL은 Query, DML, TCL이다. ]

   DDL (CREATE, DROP, ALTER, TRUNCATE …), DCL (GRANT, REVOKE) 명령어는 동적 SQL을 이용할 때만 사용 가능하다.

 - [ PL/SQL의 SELECT문은 해당 SELECT의 결과를 PL/SQL Engine으로 보낸다. ]

   이를 캐치하기 위한 변수를 DECLARE해야 하고, INTO절을 꼭 선언하여 넣을 변수를 꼭 표현해주어야 SELECT 문장은 반드시 한 개의 행이 검색되어야 한다.

   그리고 이를 INTO절을 꼭 사용해야한다. 또한 검색되는 행이 없으면 문제가 발생한다.

▶ 기본 PL/SQL Block 구조

영역	설명	옵션/필수
DECLARE (선언부)	PL/SQL에서 사용하는 모든 변수나 상수를 선언하는 부분으로서 DECLARE로 시작 => 변수/상수/커서 등 을 선언	옵션
BEGIN (실행부)	절차적 형식으로 SQL문을 실행할수있도록 절차적 언어의 요소인 제어문, 반복문, 함수 정의 등 로직을 기술할수있는 부분이며 BEGIN으로 시작	필수
EXCEPTION (예외 처리부)	PL/SQL문이 실행되는 중에 에러가 발생할수있는데 이를 예외 사항이라고 한다. 이러한 예외 사항이 발생했을때 이를 해결하기 위한 문장을 기술할수있는 부분	옵션
END (실행문 종료)		필수

▶ PL/SQL Block의 종류

1) 익명 블록 : 이름이 없는 PL/SQL Block을 말한다.

2) 이름 있는 블록 : DB의 객체로 저장되는 블록이 있다.

 - 프로시저 : 리턴 값을 하나 이상 가질 수 있는 프로그램을 말한다.

 - 함수 : 리턴 값을 반드시 반환해야 하는 프로그램을 말한다.

 - 패키지 : 하나 이상의 프로시저, 함수, 변수, 예외 등의 묶음을 말한다.

 - 트리거 : 지정된 이벤트가 발생하면 자동으로 실행되는 PL/SQL 블록이다.

▶ PL/SQL 프로그램의 작성 요령

 - PL/SQL블록 내에서는 한문장이 종료할때마다 세미콜론(;)을 사용하여 한문장이 끝났다는것을 명시

 - END 뒤에 세미콜론( ; )을 사용하여 하나의 블록이 끝났다는 것을 명시

 - 단일행주석은 -- 이고 여러행 주석은 /* */입니다.

 - 쿼리문을 수행하기 위해서 '/'가 반드시 입력되어야 하며, PL/SQL 블록은 행에 '/'가 있으면 종결된것으로 간주.

[참고] 오라클에서 화면 출력을 위해서는 PUT_LINE이란 프로시저를 이용 

 - DBMS_OUTPUT.PUT_LINE(출력할 내용) 같이 사용. 

 - 위 프로시저를 사용하여 출력되는 내용을 화면에 보여주기 위해서는 환경 변수 SERVEROUTPUT(디폴트값이 OFF이므로) ON으로 변경.

 - SET serveroutput ON

 

-- 메시지 출력하기 예시

SET serveroutput ON

BEGIN 

    dbms_output.put_line('God Dman!!');

END;

결과 - God Damn!!

▶ 변수 선언

 - 블록내에서 변수를 사용하려면 선언부(DECLARE)에서 선언해야하며 변수명 다음에 데이터 타입을 기술해야 한다.

 - 문법

identifier [CONSTANT] datatype [NOT NULL]

[:=|DEFAULT expression];

 

identifier       변수명(식별자)

CONSTANT    상수로 지정 (초기치를 반드시 할당해야함)

datatype       자료형을 기술

NOT NULL     값을 반드시 포함

expression     Literal, 다른 변수, 연산자나 함수를 포함하는 표현식

 - 문법으로만 보면 이해가 어렵다..

* 예시)

    DECLARE 변수이름 데이터타입;

ex) DECLARE NAME VARCHAR2(10);

 

    DECLARE 변수이름 데이터타입 :=값;

ex) DECLARE NAME VARCHAR2(10) := '갓댐';

 

    DECLARE 변수이름 데이터타입 DEFAULT 기본값;

ex) DECLARE NAME VARCHAR2(10) DEFAULT '갓대미';

* 변수를 한번에 여러개 선언방법

   DECLARE 

NAME    VARCHAR2(20);

AGE      NUMBER(2); 

GENDER VARCHAR(50)   DEFAULT '남';

* 변수선언하여 사용방법

ex)

DECLARE NAME VARCHAR2(20) := '이효리';

BEGIN

DBMS_OUTPUT.put_Line('이효리'|| NAME); -- 출력

END;

* 변수 Type을 선언할때 꼭 명시적으로 작성하지 않고 사용하는 방법도 있다.

1. %ROWTYPE

 - 해당 테이블이나 뷰의 컬럼 속성을 그대로 들고 오는 형태이다. 

 - 사용방법 : 변수명 테이블이름%ROWTYPE

ex)

DECLARE 

DATA EMP%ROWTYPE;

BEGIN

SELECT  * INTO DATA 

FROM  EMP 

WHERE  EMPNO = '1234';

DBMS_OUTPUT.PUT_LINE(DATA.ENAME ||','||DATA.DEPTNO);

END;

2. %TYPE

 - 해당 테이블의 컬럼 속성을 지정하여 그대로 들고 오는 형태이다.

 - 사용방법 : 변수명 테이블이름.컬럼명%TYPE

ex)

DECLARE 

V_ENAME  EMP.ENAME%TYPE;

V_DEPTNO EMP.DEPTNO%TYPE;

BEGIN

SELECT  ENAME, DEPTNO INTO V_ENAME, V_DEPTNO

FROM  EMP 

WHERE  EMPNO = '1234';

DBMS_OUTPUT.PUT_LINE(V_ENAME ||','||V_DEPTNO);

END;

▶ 변수 대입 방법

* 명시적인 값 대입

 - 변수값을 저장하기 위해서는 := 를 사용한다.

 - := 의 좌측에는 변수를 , 우측에는 값을 기술

   identifier := expression;

* SELECT 문을 이용하여 값 대입

 - 기존 SELECT 문과는 다르게 INTO절이 추가 된다.

   INTO절에는 조회 결과 값을 저장할 변수를 기술. select 문은 INTO절에 의해 하나의 행만을 저장 가능

 - 문법

SELECT select_list

INTO {variable_name1[,variable_name2,..]|record_name}

FROM table_name

WHERE condition;

※ SELECT 다음에 기술한 컬럼은 INTO 절에 있는 변수와 1:1로 대응해야하기 때문에 개수와 데이터 타입, 길이를 일치시켜야함

---

관련글

2018/06/03 - [4. Database/4_3 Oracle 기본] - [Oracle] PL/SQL 기초2 - 반복문

2018/06/14 - [4. Database/4_3 Oracle 기본] - [Oracle] PL/SQL 기초3 - 커서

---