2020/01/13 - [DATABASE/ORACLE] - [PL/SQL] 무작정 시작하기 (1) - PL/SQL 이란?
2020/01/13 - [DATABASE/ORACLE] - [PL/SQL] 무작정 시작하기 (2) - IF 조건문
2020/01/13 - [DATABASE/ORACLE] - [PL/SQL] 무작성 시작하기 (3) - LOOP, WHILE 반복문
지난 시간까지 조건문과 반복문에 대해서 알아보았다. 이번 포스트에서는 반복문과 짝꿍을 이뤄서 PL/SQL에서 가장 많이 사용되는 CURSOR에 대해서 알아보도록 하겠다.
1. CURSOR란?
1-1. SELECT문을 통해 얻어진 결과 행들은 메모리에 저장되는데, 이 저장되는 장소를 커서(CURSOR)라고 함.
1-2. PL/SQL에서는 원래 단일행에 대한 처리만 가능하지만, CURSOR를 이용하면 복수개의 행을 처리할 수 있음.
1-3. CURSOR에 있는 데이터를 가지고 오는것을 FETCH라고 함.
1-4. CURSOR 선언 -> CURSOR OPEN -> FETCH -> CURSOR CLOSE 순으로 사용됨.
1-5. 무시적 커서와 명시적 커서가 있음.
1-6. 4개의 속성을 가지며, 커서의 종류별로 사용이 달라짐.
- SQL%ISOPEN
- SQL%FOUND
- SQL%NOTFOUND
- SQL%ROWCOUNT
2. 묵시적 커서란?
2-1. DML이 실행되면 자동으로 생성. 관리되는 커서.
2-2. 쿼리가 실행되는 순간 CURSOR의 OPEN과 CLOSE가 실행됨.
2-3. 속성별 정의
- SQL%ISOPEN: 쿼리가 실행되는 순간 CLOSE 되기 때문에 항상 FALSE.
- SQL%FOUND: 조회된 결과가 1개 이상인 경우 TRUE.
- SQL%NOTFOUND: 조회된 결과가 0개인 경우 TRUE.
- SQL%ROWCOUNT: 최근 수행된 쿼리의 결과행 수.
3. 묵시적 커서 예제.
|
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
|
--실행 결과를 출력하도록 설정
SET SERVEROUTPUT ON
--스크립트 경과 시간을 출력하도록 설정
SET TIMING ON
DECLARE
--변수를 정의하는 영역
V_CNT NUMBER := 1;
V_ROWCOUNT NUMBER;
V_ISOPEN VARCHAR2(8);
V_FOUND VARCHAR2(8);
V_NOTFOUND VARCHAR2(8);
BEGIN
--작업 영역
SELECT COUNT(1)
INTO V_CNT
FROM SCOTT.EMP
;
V_ROWCOUNT := SQL%ROWCOUNT;
V_ISOPEN := CASE WHEN SQL%ISOPEN THEN 'TRUE' ELSE 'FALSE' END;
V_FOUND := CASE WHEN SQL%FOUND THEN 'TRUE' ELSE 'FALSE' END;
V_NOTFOUND := CASE WHEN SQL%NOTFOUND THEN 'TRUE' ELSE 'FALSE' END;
DBMS_OUTPUT.PUT_LINE( V_ROWCOUNT ); --1
DBMS_OUTPUT.PUT_LINE( V_ISOPEN ); --FALSE
DBMS_OUTPUT.PUT_LINE( V_FOUND ); --TRUE
DBMS_OUTPUT.PUT_LINE( V_NOTFOUND ); --FALSE
END
;
--작업 종료
|
cs |
4. 명시적 커서란?
4-1. 사용자가 선언하는 CURSOR.
4-2. CURSOR 선언, CURSOR OPEN, FETCH, CURSOR CLOSE를 작성해야함.
4-3. 속성별 정의
- CURSOR명%ISOPEN: CURSOR가 OPEN상태인 경우 TRUE.
- CURSOR명%FOUND: 조회된 결과가 1개 이상인 경우 TRUE.
- CURSOR명%NOTFOUND: 조회된 결과가 0개인 경우 TRUE.
- CURSOR명%ROWCOUNT: 최근 수행된 쿼리의 결과행 수.
* LOOP 내에서 사용할 경우, INDEX처럼 COUNT가 1씩 증가함.
4-4. 명시적 커서를 정의 하는 방법.
- CURSOR ~ IS: CURSOR를 선언하고 직접 OPEN, FETCH, CLOSE 하여 사용.
- IS REF CURSOR: CURSOR를 선언하고 직접 OPEN, FETCH, CLOSE 하여 사용, 쿼리를 동적으로 작성할 수 있음.
- FOR ~ IN: CURSOR를 선언할 필요가 없으며, CURSOR의 OPEN, CLOSE, FETCH가 자동으로 관리됨.
5. 명시적 커서 예제.
5-1. CURSOR ~ IS 에제.
- EMP 테이블에서 사원번호, 사원명, 직업, 급여를 조회하시오.
|
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
|
--실행 결과를 출력하도록 설정
SET SERVEROUTPUT ON
--스크립트 경과 시간을 출력하도록 설정
SET TIMING ON
DECLARE
--변수를 정의하는 영역
V_RESULT_MSG VARCHAR2(500);
CURSOR CUR1 IS (
SELECT T1.EMPNO
, T1.ENAME
, T1.JOB
, T1.SAL
FROM SCOTT.EMP T1
);
V_EMPNO SCOTT.EMP.EMPNO%TYPE;
V_ENAME SCOTT.EMP.ENAME%TYPE;
V_JOB SCOTT.EMP.JOB%TYPE;
V_SAL SCOTT.EMP.SAL%TYPE;
BEGIN
--작업 영역
OPEN CUR1;
LOOP
FETCH CUR1 INTO V_EMPNO, V_ENAME, V_JOB, V_SAL;
EXIT WHEN CUR1%NOTFOUND;
V_RESULT_MSG := V_EMPNO||' / '||V_ENAME||' / '||V_JOB||' / '||V_SAL;
DBMS_OUTPUT.PUT_LINE( V_RESULT_MSG );
END LOOP;
CLOSE CUR1;
END
;
--작업 종료
|
cs |
- 13~19 ln: EMP 테이블을 조회한 결과를 저장하는 CURSOR를 선언,
- 21~24 ln: FETCH된 값을 저장할 변수, EMP 테이블의 컬럼을 참조하여 데이터의 타입을 정의,
- 29 ln: CURSOR를 OPEN하여 FETCH가 가능한 상태로 변경.
- 31 ln: 전체 행을 조회하기 위해 반복문 실행.
- 33 ln: CURSOR를 FETCH하여 한 행의 데이터를 추출하여 변수에 저장.
- 35 ln: CURSOR에 더이상 FETCH할 행이 없는 경우 반복문 종료.
- 37 ~39 ln: 결과값 출력.
- 41 ln: 반복문 종료.
- 43 ln: 커서 종료.
5-2. IS REF CURSOR 에제.
- EMP 테이블에서 사원번호, 사원명, 직업, 급여를 조회하시오.
|
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
|
--실행 결과를 출력하도록 설정
SET SERVEROUTPUT ON
--스크립트 경과 시간을 출력하도록 설정
SET TIMING ON
DECLARE
--변수를 정의하는 영역
V_SQL VARCHAR2(500);
V_RESULT_MSG VARCHAR2(500);
TYPE MY_CURSOR IS REF CURSOR;
CUR1 MY_CURSOR;
V_EMPNO SCOTT.EMP.EMPNO%TYPE;
V_ENAME SCOTT.EMP.ENAME%TYPE;
V_JOB SCOTT.EMP.JOB%TYPE;
V_SAL SCOTT.EMP.SAL%TYPE;
BEGIN
--작업 영역
V_SQL := '
SELECT T1.EMPNO
, T1.ENAME
, T1.JOB
, T1.SAL
FROM SCOTT.EMP T1
';
OPEN CUR1 FOR V_SQL;
LOOP
FETCH CUR1 INTO V_EMPNO, V_ENAME, V_JOB, V_SAL;
EXIT WHEN CUR1%NOTFOUND;
V_RESULT_MSG := V_EMPNO||' / '||V_ENAME||' / '||V_JOB||' / '||V_SAL;
DBMS_OUTPUT.PUT_LINE( V_RESULT_MSG );
END LOOP;
CLOSE CUR1;
END
;
--작업 종료
|
cs |
- 11 ln: 동적 쿼리를 저장할 변수.
- 14~15 ln: REF CURSOR로 MY_CURSOR를 선언하여 사용.
- 25~31 ln: 텍스트로 작성된 쿼리, 텍스트로 작성되었기 때문에 자유롭게 변형이 가능함.
- 33 ln: FOR문을 사용하여 CURSOR를 OPEN함.
- 나머지는 5-1과 동일.
5-3. FOR ~ IN 예제.
- EMP 테이블에서 사원번호, 사원명, 직업, 급여를 조회하시오.
|
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
|
--실행 결과를 출력하도록 설정
SET SERVEROUTPUT ON
--스크립트 경과 시간을 출력하도록 설정
SET TIMING ON
DECLARE
--변수를 정의하는 영역
V_RESULT_MSG VARCHAR2(500);
V_EMPNO SCOTT.EMP.EMPNO%TYPE;
V_ENAME SCOTT.EMP.ENAME%TYPE;
V_JOB SCOTT.EMP.JOB%TYPE;
V_SAL SCOTT.EMP.SAL%TYPE;
BEGIN
--작업 영역
FOR CUR1 IN (
SELECT T1.EMPNO
, T1.ENAME
, T1.JOB
, T1.SAL
FROM SCOTT.EMP T1
)
LOOP
V_RESULT_MSG := CUR1.EMPNO||' / '||CUR1.ENAME||' / '||CUR1.JOB||' / '||CUR1.SAL;
DBMS_OUTPUT.PUT_LINE( V_RESULT_MSG );
END LOOP
;
END
;
--작업 종료
|
cs |
- 13~16 ln: EMP 테이블의 컬럼을 참조하여 데이터의 타입을 정의.
- 21~26 ln: 데이터를 조회하여 CUR1을 생성하고 OPEN함.
- 28~34 ln: 내부적으로 FETCH가 실행되며 FETCH 할 데이터가 없을 경우 CLOSE됨.
6. 마치며
- CURSOR는 PL/SQL을 사용하면 땔래야 땔 수 없는 기능이므로 반드시 숙지해두길 바란다.
- 묵시적 CURSOR는 사용자가 관리하는 영역이 아니기 때문에 크게 신경쓸 필요 없다. 주로 명시적 CURSOR로 FETCH된 데이터를 INSERT, UPDATE, DELETE 등의 데이터를 수정한 결과를 가져올 때 사용된다.
- 명시적 CURSOR는 모두 다 알아두면 좋지만 특별한 경우가 아니라면 'FOR ~ IN' CURSOR만 알고 있어도 충분하다. 실제로도 가장 많이 사용되며, 파티션을 조회하거나 동적으로 컬럼을 변경하여 조회해야하는 경우에 5-2의 'REF CURSOR'를 사용한다.
'DATABASE > ORACLE' 카테고리의 다른 글
| [PL/SQL] 예제 (1) - 1~100의 수를 더하는 과정을 출력 (0) | 2020.01.22 |
|---|---|
| [PL/SQL] 무작정 시작하기 (5) - EXCEPTION 예외 (0) | 2020.01.16 |
| [PL/SQL] 무작성 시작하기 (3) - LOOP, WHILE 반복문 (0) | 2020.01.13 |
| [PL/SQL] 무작정 시작하기 (2) - IF 조건문 (0) | 2020.01.13 |
| [PL/SQL] 무작정 시작하기 (1) - PL/SQL 이란? (0) | 2020.01.13 |
댓글