데이터베이스에서 데이터를 조회하고 테이블을 정의하는 등의 단순한 행위는 SQL로 충분히 가능하다. 그러나, SQL 중간에 분기처리를 한다던가 조회된 결과를 변수로 사용하거나 예외처리를 하는 기능은 제공되지 않는다. 이러한 SQL의 단점을 개선해서 나온것이 바로 PL/SQL(Procedural Language extension to SQL)이다.
이번 포스트에서는 예제를 통해 PL/SQL을 알아보도록 하겠다.
1. PL/SQL(Procedural Language extension to SQL)이란?
1-1. SQL을 확장한 절차적 언어.
1-2. 여러 SQL을 하나의 블록(Block)으로 구성하여 SQL을 제어할 수 있음.
- Commit, Rollback, 예외처리 등.
1-3. 조건문(IF), 반복문(LOOP)을 사용하여 유연한 프로그래밍이 가능함.
1-4. 커서(Cursor)를 사용하여 대용량 데이터를 처리할 때, 데이터를 분할하여 처리할 수 있음.
1-5. 동적 SQL를 통하여 문자열로 작성된 SQL을 실행할 수 있으며, DML, DDL, DCL을 사용할 수 있음.
예) EXECUTE IMMEDIATE 'GRANT SELECT ON SCOTT.EMP TO PUBLIC';
2. 기본 구조.
2-1. DECLARE, BEGIN, EXCEPTION, END가 한 블럭(Block)을 이룸.
|
1
2
3
4
5
6
7
8
|
DECLARE --변수 정의
BEGIN -- 처리 로직 시작
EXCEPTION -- 예외 처리
END -- 처리 로직 종료
;
|
cs |
- DECLARE: 변수를 정의하는 영역으로 생략이 가능함.
- BEGIN: 작업 영역, BEGIN과 END는 반드시 한쌍으로 작성해야 함.
- EXCEPTION: 예외처리 영역으로 BEGIN과 END 사이에 작성하며 생략이 가능함.
- END: 작업 종료를 알리는 키워드로 마지막에 블록의 끝을 알리는 문자(;)를 입력해야함.
2-2. 한개의 블록은 BEGIN과 END 사이에 여러번 사용할 수 있음.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
--Block 1
DECLARE
BEGIN
--Block 2
DECLARE
BEGIN
--Block 2-1
DECLARE
BEGIN
EXCEPTION
END
;
EXCEPTION
END
;
--Block 3
DECLARE
BEGIN
EXCEPTION
END
;
EXCEPTION
END
;
|
cs |
3. 예제 소스.
- 스키마는 오라클을 설치하면 기본적으로 제공하는 SCOTT을 사용
- DEPTNO가 10인 부서의 부서번호, 부서명, 지역을 조회하는 PL/SQL
- PL/SQL을 이용하여 작성하였지만 예제를 위해 간단하게 작성한 것.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
|
--실행 결과를 출력하도록 설정
SET SERVEROUTPUT ON
--스크립트 경과 시간을 출력하도록 설정
SET TIMING ON
DECLARE
--변수를 정의하는 영역
/**
* PL/SQL에서 사용할 변수를 정의.
* IDENTIFIER [CONSTANT] DATATYPE [NOT NULL] [DEFAULT 값];
*/
V_STRD_DT VARCHAR2(8);
V_STRD_DEPTNO NUMBER;
V_DEPTNO NUMBER;
V_DNAME VARCHAR2(50);
V_LOC VARCHAR2(50);
V_RESULT_MSG VARCHAR2(500) DEFAULT 'SUCCESS';
BEGIN
--작업 영역
/**
* DEPTNO가 10인 부서의 부서번호, 부서명, 지역을 조회.
*/
--기준일자 - 내장함수 사용.
V_STRD_DT := TO_CHAR(SYSDATE, 'YYYYMMDD');
--조회 부서번호 변수 설정
V_STRD_DEPTNO := 10;
BEGIN
--조회 - INTO절로 조회된 데이터 저장.
SELECT T1.DEPTNO
, T1.DNAME
, T1.LOC
INTO V_DEPTNO
, V_DNAME
, V_LOC
FROM SCOTT.DEPT T1
WHERE T1.DEPTNO = V_STRD_DEPTNO
;
END
;
--조회 결과 변수 설정
V_RESULT_MSG := 'RESULT > DEPTNO='||V_DEPTNO||', DNAME='||V_DNAME||', LOC='||V_LOC;
--조회 결과 출력
DBMS_OUTPUT.PUT_LINE( V_RESULT_MSG );
EXCEPTION
--예외 처리
WHEN OTHERS THEN
V_RESULT_MSG := 'SQLCODE['||SQLCODE||'], MESSAGE =>'||SQLERRM;
DBMS_OUTPUT.PUT_LINE( V_RESULT_MSG );
END
;
--작업 종료
|
cs |
4. 상세 구조.
4-1. DECLARE
|
1
2
3
4
5
6
7
8
9
10
|
DECLARE
V_STRD_DT VARCHAR2(8);
V_STRD_DEPTNO NUMBER;
V_DEPTNO NUMBER;
V_DNAME VARCHAR2(50);
V_LOC VARCHAR2(50);
V_RESULT_MSG VARCHAR2(500) DEFAULT 'SUCCESS';
|
cs |
- 변수를 정의하는 영역
- IDENTIFIER [CONSTANT] DATATYPE [NOT NULL] [DEFAULT 값];
* CONSTANT는 상수를 의미, 초기값이 있어야하며 값을 변경할 수 없음.
* DATATYPE에는 컬럼의 기본 타입 외에 EXCEPTION, CURSOR, %ROWTYPE 등 확장된 타입이 있음.
* NOT NULL은 무조건 값이 존재해야하고 초기값이 있어야하며 값은 변경할 수 있음.
* DEFAULT는 [ := '초기값' ]으로 사용할 수 있음.
4-2. BEGIN
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
BEGIN
/**
* DEPTNO가 10인 부서의 부서번호, 부서명, 지역을 조회.
*/
--기준일자 - 내장함수 사용.
V_STRD_DT := TO_CHAR(SYSDATE, 'YYYYMMDD');
--조회 부서번호 변수 설정
V_STRD_DEPTNO := 10;
BEGIN
--조회 - INTO절로 조회된 데이터 저장.
SELECT T1.DEPTNO
, T1.DNAME
, T1.LOC
INTO V_DEPTNO
, V_DNAME
, V_LOC
FROM SCOTT.DEPT T1
WHERE T1.DEPTNO = V_STRD_DEPTNO
;
END
;
--조회 결과 변수 설정
V_RESULT_MSG := 'RESULT > DEPTNO='||V_DEPTNO||', DNAME='||V_DNAME||', LOC='||V_LOC;
--조회 결과 출력
DBMS_OUTPUT.PUT_LINE( V_RESULT_MSG );
|
cs |
- 작업 영역
- 변수에 값을 대입할 때는 [ := ]을 사용함. 예) V_NAME := 'dochi';
- TO_CHAR, TO_DATE와 같은 오라클 내장함수도 사용할 수 있음.
- INTO절을 사용하여 SELECT절에서 조회한 결과를 변수에 대입할 수 있음.
* 반드시 1개의 행만 조회되어야 함.
* 조회된 컬럼의 순서에 따라 변수에 값이 저장됨.
* 데이터 타입이 %ROWTYPE인 경우 하나의 변수에 모든 컬럼 값을 저장할 수 있음.
4-3. EXCEPTION과 END
|
1
2
3
4
5
6
7
8
|
EXCEPTION
WHEN OTHERS THEN
V_RESULT_MSG := 'SQLCODE['||SQLCODE||'], MESSAGE =>'||SQLERRM;
DBMS_OUTPUT.PUT_LINE( V_RESULT_MSG );
END
;
|
cs |
- 예외처리와 작업 종료
- [ WHEN OTHERS THEN ] 구문은 상위 조건에 부합하는 것이 없을 경우 처리하는 의미.
* ELSE와 같음.
* [ MERGE INTO ] 문에서 사용하는 [ WHEN [NOT] MATCHED THEN ]과 유사한 구문.
* 상위 조건에는 사용자 정의 예외(USER DEFINED EXCEPTION)를 처리.
- SQLCODE와 SQLERRM은 내장변수로 오류코드와 메시지를 담고 있음.
5. 마치며.
- 이것으로 PL/SQL에 대하여 간단하게 알아보고 예제를 통하여 각각의 영역을 살펴보았는데, 예제처럼 단순하게 조회만 하는 용도라면 굳이 PL/SQL을 사용할 필요는 없다. 오히려 리소스만 낭비할 뿐이다.
- 다음 포스트에서는 조건문을 사용하는 방법에 대해서 알아보도록 하겠다.
'DATABASE > ORACLE' 카테고리의 다른 글
| [PL/SQL] 무작정 시작하기 (4) - CURSOR (0) | 2020.01.14 |
|---|---|
| [PL/SQL] 무작성 시작하기 (3) - LOOP, WHILE 반복문 (0) | 2020.01.13 |
| [PL/SQL] 무작정 시작하기 (2) - IF 조건문 (0) | 2020.01.13 |
| 20190925 (0) | 2019.09.25 |
| 자주 사용하는 오라클 DML 정리 (0) | 2019.09.23 |
댓글