1. 정의
* DB 서버의 SGA(System Global Area)에 저장되어 있는 특정 쿼리를 실행하기 위한 정보를 저장하는 PGA(Program Global Area) 내부의 Private SQL Area에 대한 포인터
2. 묵시적 커서와 명시적 커서
| 묵시적 커서(Implicit Cursor) | 명시적 커서(Explicit Cursor) | |
| 커서 선언 | 없음 | 있음 |
| 사용 시 복잡도 | 단순 | 복잡 |
| 커서 제어 | 불가능 | 가능 |
| 유연성 | 낮음 | 높음 |
| 사용 가능한 SQL 유형 | SELECT, INSERT, UPDATE, DELETE, MERGE | SELECT |
| 커서 속성 참조 | SQL%커서속성 | 커서명%커서속성 |
| 전형적인 사용 방법 | 즉시 실행 | OPEN, FETCH, CLOSE |
3. 명시적 커서
* 명시적 커서는 DECLARE절에 CURSOR 선언을 가지는 커서로, SELECT문에 대해서만 사용 가능하다.
- OPEN문을 사용하여 커서를 열고, FETCH문을 사용하여 결과를 추출하고, CLOSE문을 사용하여 커서를 닫는다.
- FOR LOOP문에서 사용한다.
커서가 닫힌 후에는 전체 결과를 다 FETCH했는지 여부와 관계 없이 더 이상 결과를 추출할 수 없고, 상태를 조회할 수 없다.
* 명시적 커서의 경우
DECLARE
v_name emp.ename%TYPE;
-- 선언
CURSOR ename_cursor IS
SELECT ENAME
FROM EMP
WHERE EMPNO = 7788;
BEGIN
-- OPEN
OPEN ename_cursor;
-- FETCH
FETCH ename_cursor
INTO v_name;
DBMS_OUTPUT.PUT_LINE('ENAME = '|| v_name);
-- CLOSE
CLOSE ename_cursor;
END;1) OPEN문
OPEN 커서명;커서를 열 때 DB서버는 IS 뒤에 지정한 쿼리문을 파싱하고, 쿼리 수행을 위한 자원을 할당하며, 쿼리를 실행하여 커서를 결과 집합의 첫 번째 로우에 위치시킨다.
커서가 여러 번 실행될 때 쿼리의 파싱은 최초 OPEN 시에 한 번만 실행되고 이후에는 재사용된다.
2) FETCH문
FETCH 커서명 INTO 칼럼명;커서에서 결과를 가져와 PL/SQL 변수에 담는다. BULK COLLECT문을 사용하면 배열 처리로 한 번에 여러 로우를 가져오며, 그렇지 않은 경우 반복문으로 한 로우씩 가져온다.
3) CLOSE문
CLOSE 커서명;커서가 닫히면 DB 서버는 OPEN 시에 할당된 자원을 해제한다.
* 선언
CURSOR 커서명 [ (매개변수 목록) ] [반환형] IS
SELECT 문;- 반환형: 커서의 SELECT문 없이 선언만 하는 전방 선언에 사용
(예시)
DECLARE
v_empno NUMBER;
v_ename emp.ename%TYPE;
CURSOR ename_cursor IS
SELECT empno, ename
FROM emp
ORDER BY empno;
BEGIN
OPEN ename_cursor;
LOOP
FETCH ename_cursor INTO v_empno, v_ename;
EXIT WHEN ename_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPNO = '||v_empno ||' ENAME = '||v_ename);
END LOOP;
CLOSE ename_cursor;
END;
(예시2)
DECLARE
TYPE empno_arr IS TABLE OF NUMBER;
TYPE ename_arr IS TABLE OF emp.ename%TYPE;
v_empno empno_arr;
v_ename ename_arr;
CURSOR ename_cursor IS
SELECT empno, ename
FROM emp;
BEGIN
OPEN ename_cursor;
FETCH ename_cursor
BULK COLLECT INTO v_empno, v_ename;
DBMS_OUTPUT.PUT_LINE('사원 수 = ' || v_empno.COUNT);
CLOSE ename_cursor;
END;
* 속성
| 속성명 | 의미 |
| ISOPEN | - TRUE: 커서가 열려 있는 경우 - FALSE: 커서가 닫혀 있는 경우 |
| FOUND | 결과가 FETCH되었는지를 나타내는 값 - NULL: 명시적 커서가 OPEN되었으나 FETCH 전일 경우 - TRUE: 최근의 FETCH가 ROW를 반환한 경우 - FALSE: 이외의 경우 |
| NOTFOUND | FETCH된 ROW가 없는지를 나타내는 값 - NULL: (FOUND와 동일) - FALSE: 최근의 FETCH가 ROW를 반환한 경우 - TRUE: 이외의 경우 |
| ROWCOUNT | 현 시점까지 FETCH된 로우의 누적 건수 |
4. 묵시적 커서
* 묵시적 커서의 경우
DECLARE
v_name emp.ename%TYPE;
BEGIN
SELECT ename
INTO v_name
FROM emp
WHERE empno = 7788;
DBMS_OUTPUT.PUT_LINE('ENAME = '||v_name);
END;* 속성
| 속성명 | 의미 |
| ISOPEN | 항상 FALSE |
| FOUND | 결과가 FETCH되었는지를 나타내는 값 - NULL: SELECT 또는 DML이 수행되지 않은 경우 - TRUE: 쿼리문이 결과를 반환한 경우 - FALSE: 이외의 경우 |
| NOTFOUND | FETCH된 ROW가 없는지를 나타내는 값 - NULL: (FOUND와 동일) - FALSE: 쿼리문이 결과를 반환한 경우 - TRUE: 이외의 경우 |
| ROWCOUNT | 현 시점까지 FETCH된 로우의 누적 건수 - NULL: SELECT 또는 DML이 수행되지 않은 경우 - 값: FETCH된 누전 로우의 수 또는 영향받은 로우의 수 트랜잭션이 롤백되거나 종료되는 경우에 SQL%ROWCOUNT가 이전 값으로 복구되지 않는다. |
| BULK_ROWCOUNT | FORALL문과 같이 사용되는 속성으로, 실행 결과를 저장하는 Associative Array다. i번째 항목을 실행했을 때 영향받은 로우의 수 ex) SQL%BULK_ROWCOUNT(i) |
| BULK_EXCEPTIONS | FORALL문과 같이 사용되는 속성으로, SQL 실행 시 발생한 Exception을 저장하는 Associative Array다. Exception을 발생시킨 항목의 수만큼 저장 ex) BULK_EXCEPTIONS(i) - SQL%BULK_EXCEPTIONS(i).ERROR_INDEX - SQL%BULK_EXCEPTIONS(i).ERROR_CODE |
5. 커서 FOR LOOP
* 묵시적 커서 FOR LOOP: DECLARE절에 커서를 선언하지 않고, FOR LOOP문의 IN절에 SELECT문을 바로 사용하는 형태
DECLARE
v_total_pay NUMBER := 0;
BEGIN
FOR t IN ( SELECT ename, hiredate, deptno, NVL(sal, 0)+NVL(comm, 0) total_pay
FROM emp
WHERE deptno = 10)
LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(t.ename, 6, ' ') || ', 급여일자 = '||
TO_CHAR(t.hiredate, 'YYYY-MM-DD')||', 급여 = '||t.total_pay);
v_total_pay := v_total_pay + NVL(t.total_pay, 0);
END LOOP;
DBMS_OUTPUT.PUT_LINE('------------------');
DBMS_OUTPUT.PUT_LINE('급여합계 = '||v_total_pay);
END;
* 명시적 커서 FOR LOOP
DECLARE
v_total_pay NUMBER := 0;
CURSOR emp_cursor IS
SELECT ename, hiredate, deptno, NVL(sal, 0)+NVL(comm, 0) total_pay
FROM emp
WHERE deptno = 10;
BEGIN
DBMS_OUTPUT.PUT_LINE('-----명시적 커서 변경------');
FOR t IN emp_cursor
LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(t.ename, 6, ' ') || ', 급여일자 = '||
TO_CHAR(t.hiredate, 'YYYY-MM-DD')||', 급여 = '||t.total_pay);
v_total_pay := v_total_pay + NVL(t.total_pay, 0);
END LOOP;
DBMS_OUTPUT.PUT_LINE('------------------');
DBMS_OUTPUT.PUT_LINE('급여합계 = '||v_total_pay);
END;
6. 커서 매개변수
커서를 OPEN할 때 매개변수로 값을 넘겨받아서 바인드 변수로 사용한다.
(예시)
DECLARE
v_name emp.ename%TYPE;
v_empno NUMBER := 7788;
CURSOR ename_cursor(a_empno NUMBER) IS
SELECT ename
FROM EMP
WHERE empno = a_empno;
BEGIN
OPEN ename_cursor(v_empno);
FETCH ename_cursor INTO v_name;
DBMS_OUTPUT.PUT_LINE('이름 = '||v_name);
CLOSE ename_cursor;
END;
7. 커서 변수(REF CURSOR)
TYPE 타입명 IS REF CURSOR [ RETURN 반환데이터타입 ];
변수명 타입명;- Strong Type: Return 타입을 지정하는 REF CURSOR 타입
- Weak Type: Return 타입을 지정하지 않는 REF CURSOR 타입
cf) sys_refcursor의 경우 오라클 PL/SQL에 사전 정의된 약한 커서 타입이다.
* 커서 변수는 하나의 쿼리에만 국한되어 사용되지 않는다. 다른 쿼리에 대해 OPEN, FETCH, CLOSE한 후 다른 쿼리에서 사용할 수 있다.
(예시)
DECLARE
TYPE emp_rec IS RECORD (
empno emp.empno%TYPE,
ename emp.ename%TYPE,
sal emp.sal%TYPE
);
v_emprec emp_rec;
TYPE emp_cursor_type IS REF CURSOR RETURN emp_rec;
v_empcur emp_cursor_type;
BEGIN
-- 첫번째 SQL문 커서 OPEN
OPEN v_empcur FOR SELECT empno, ename, sal FROM emp WHERE deptno=10;
LOOP
FETCH v_empcur INTO v_emprec;
EXIT WHEN v_empcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPNO='||v_emprec.empno||' , ENAME='||v_emprec.ename||
', SAL='||v_emprec.sal);
END LOOP;
CLOSE v_empcur;
DBMS_OUTPUT.PUT_LINE(' ');
-- 두번째 SQL문 커서 OPEN
OPEN v_empcur FOR SELECT empno, ename, sal+NVL(comm, 0) FROM emp WHERE deptno=20;
LOOP
FETCH v_empcur INTO v_emprec;
EXIT WHEN v_empcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPNO='||v_emprec.empno||' , ENAME='||v_emprec.ename||
', SAL='||v_emprec.sal);
END LOOP;
CLOSE v_empcur;
END;
* 서브프로그램의 매개변수로 사용할 수 있다.
DECLARE
TYPE emp_rec IS RECORD (
empno emp.empno%TYPE,
ename emp.ename%TYPE
); -- 레코드 타입 정의
TYPE emp_cursor_type IS REF CURSOR RETURN emp_rec; -- 레코드 타입의 커서 변수
v_empcur emp_cursor_type; -- 커서 변수
-- 프로시저 시작
PROCEDURE print_emp(a_empcur emp_cursor_type) IS -- 커서 변수를 매개변수로 사용
v_emprec emp_rec;
BEGIN
LOOP
FETCH a_empcur INTO v_emprec;
EXIT WHEN a_empcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPNO='||v_emprec.empno||' , ENAME='||v_emprec.ename);
END LOOP;
END;
-- 프로시저 종료
BEGIN
OPEN v_empcur FOR SELECT empno, ename FROM emp;
print_emp(v_empcur);
CLOSE v_empcur;
END;
* 다른 프로그래밍 언어의 호스트 변수로 선언할 수 있다.
8. SELECT FOR UPDATE
SELECT FOR UPDATE를 실행하면 일반 SELECT문과 같이 조회할 수 있고, UPDATE를 실행할 수 있다. UPDATE를 하는 경우 중간에 다른 트랜잭션이 간섭하는 것을 막기 위해 LOCK을 걸어준다.
변경을 원하는 로우나 원하지 않는 로우에 상관없이 LOCK을 걸어 프로그램의 동시성을 저해하지만, 간섭을 막기 때문에 동시성 제어를 위해 유용하게 사용할 수 있다.
사용할 때, 현재 커서가 위치한 로우를 지정하기 위해 WHERE절에 "CURRENT OF" 키워드를 사용한다.
(예시) SELECT FOR UPDATE 사용 예제: EMP 테이블에서 급여가 1500 미만인 직원을 조회하여 그 중에서 업무가 'SALESMAN'인 직원의 커미션을 10% 올려준다.
DECLARE
CURSOR emp_cursor IS
SELECT empno, ename, job, sal
FROM emp
WHERE sal < 1500
FOR UPDATE; -- WHERE 절에 FOR UPDATE 사용
BEGIN
FOR e in emp_cursor
LOOP
IF e.job = 'SALESMAN' THEN
UPDATE emp
SET comm = comm*1.1
WHERE CURRENT OF emp_cursor; -- 현재 커서가 위치한 로우만을 UPDATE
END IF;
END LOOP;
END;
※ 출처: 개발자를 위한 PL/SQL 프로그래밍
'프로그래밍' 카테고리의 다른 글
| Oracle PL/SQL - Stored Subprogram (0) | 2021.09.28 |
|---|---|
| Oracle PL/SQL - 동적 SQL (0) | 2021.09.24 |
| Oracle PL/SQL - Record (0) | 2021.09.23 |
| Oracle PL/SQL - Collection (0) | 2021.09.23 |
| Eclipse Spring Package - Git 연동 (0) | 2021.08.14 |