20 장. PL/SQL 커서
주요내용 암시적커서 명시적커서선언 명시적커서열기및닫기 명시적커서에서데이터 Fetch 커서의속성 (%ISOPEN, %ROWCOUNT, %FOUND, %NOTFOUND) 커서 FOR 루프
PL/SQL 의커서 (Cursor) 커서 SQL 문과프로그램실행과정에서결과를저장할수있는오라클메모리구조 ( 개별 SQL 작업영역 ) 암시적커서 (Implicit Cursor) - 프로그램의실행섹션에포함된 SELECT 문 - 모든 DML 문장 (INSERT, UPDATE, DELETE) 과단일행질의를위해사용 - 오라클이모든것을자동으로처리 명시적커서 (Explicit Cursor) - 프로그래머가프로그램의선언섹션에커서선언 - 하나이상의행을반환하는다중행 SELECT 문장에의해반환되는각행을개별적으로처리하기위해사용
커서의속성 커서속성을사용하여 SQL 문장의결과를테스트할수있음. 암시적커서가가장최근에사용된때일어난일을평가함. 커서속성 SQL%ROWCOUNT SQL%FOUND SQL%NOTFOUND SQL%ISOPEN 설명가장최근의 SQL 문장에의해영향을받은행의수를반환. 가장최근의 SQL 문장이하나또는그이상의행에영향을미친다면 TRUE로평가되는부울속성. 가장최근의 SQL 문장이어떤행에도영향을미치지않았다면 TRUE가되는부울속성. PL/SQL이실행된후에즉시암시적커서를닫기때문에항상 FALSE로평가되는부울속성.
커서의속성활용 ITEM 테이블에서주문번호가 520 인모든주문을삭제하고, 삭제된주문의수를출력. SQL> VARIABLE orders_deleted NUMBER SQL> DECLARE 2 v_ordid NUMBER := 520; 3 BEGIN 4 DELETE FROM item 5 WHERE ordid = v_ordid; 6 :orders_deleted := SQL%ROWCOUNT 7 orders deleted. ); 8 END; SQL> PRINT orders_deleted; 25 orders deleted
명시적커서 질의에의해반환된행의집합인 result set 에서첫번째행부터한행씩처리함. 현재처리되는행의트랙을유지함. 프로그래머가 PL/SQL 블록에서수동으로제어할수있음. Result Set Cursor 7369 SMITH CLERK 7566 JONES MANAGER 7876 ADAMS CLERK 7902 FORD ANALYST Current Row
명시적커서제어 DECLARE OPEN FETCH EMPTY? Yes CLOSE No - 이름있는 SQL 영역 ( 커서 ) 생성 - 커서의활성화 - 질의실행 - 현재행을변수로로드 - 활성셋에서다음행으로포인터이동 - 행이남아있는지검사 - 커서닫기 - 활성셋해제 활성셋 (Active Set) 질의에의해식별된행들
명시적커서선언 형식 : CURSOR cursor_name IS select_statement; DECLARE 섹션에서선언 커서선언의 SELECT 문에서는 INTO 절을사용하지않는다. 특정순서대로행을처리하려면, 실행섹션의질의에서 ORDER BY 절사용.
명시적커서선언예 DECLARE v_ename emp.ename%type; v_detpno dept.deptno%type; CURSOR emp_cursor IS SELECT empno, ename FROM emp; CURSOR dept_cursor IS SELECT * FROM dept WHERE deptno = 20; BEGIN
명시적커서열기 형식 : OPEN cursor_name; 질의를실행하고, 질의의검색조건을만족하는모든행들로구성된활성셋을생성하기위해커서를연다. -> 커서는활성셋의첫번째행을포인터함. 질의가아무행도반환하지않아도예외를발생하지않는다. 인출 (Fetch) 후에결과를테스트하기위해커서속성을사용한다.
명시적커서에서데이터 Fetch 형식 : FETCH cursor_name INTO [variable1, variable2, ] record_name]; - variable : 결과를저장할출력변수 - record_name : 레코드이름 (%ROWTYPE 속성이용하여선언 ) 현재행의값을출력변수로읽어들임. 활성셋에서다음행으로포인터이동. 열의수와동일한수의변수를포함해야함. 열의위치에대응하도록각변수를맞춘다. 커서가행을갖고있는지검사한다.
명시적커서에서데이터 Fetch SQL> RUN 2 DECLARE 3 v_empno emp.empno%type;; 4 v_ename emp.ename%type; 5 CURSOR emp_cursor IS 6 SELECT empno, ename 7 FROM emp; 8 BEGIN 9 OPEN emp_cursor; 10 FOR i IN 1..10 LOOP 11 FETCH emp_cursor INTO v_empno, v_ename; 15 END LOOP; 16 CLOSE emp_cursor; 17 END; 검색결과활성셋에 10 개이상이생성되면, 10 개만인출 (fetch). 10 개미만검색되면, 오류발생가능
명시적커서닫기 형식 : CLOSE cursor_name; 행을다처리한후커서를닫는다. Result set 의정의가해제됨. 생략해도되지만, 메모리효율성을위해기술권장. 닫은커서에서데이터를인출할수없음. 닫은커서를다시 OPEN 할수있음.
명시적커서사용예 SQL> RUN 2 DECLARE 3 v_emp_count NUMBER; 4 -- SELECT 커서 ( 명시적 ) 선언 5 CURSOR get_emp_cursor IS 6 SELECT COUNT(*) 7 FROM emp; 8 BEGIN 9 -- 커서를연다. 즉, 커서를사용해서데이터를읽어들일것임 10 OPEN get_emp_cursor; 11 -- SQL 문을실행하여변수에값할당 12 FETCH get_emp_cursor INTO v_emp_count; 13 FOR v_count IN 1..v_emp_count LOOP 14 DBMS_OUTPUT.PUT_LINE( Employee v_count); 15 END LOOP; 16 CLOSE get_emp_cursor; -- 사용된메모리반환 17 END;
명시적커서의속성 커서에대한상태정보제공 커서속성 SQL%ROWCOUNT SQL%FOUND SQL%NOTFOUND SQL%ISOPEN 설명지금까지인출 (fetch) 한데이터 ( 행 ) 의총수 가장최근의인출이행을반환하면, TRUE 로평가되는부울속성. 가장최근의인출이행을반환하지않으면, TRUE 로평가되는부울속성. ( 인출전에는 NULL 로평가됨.) 커서가열려있으면 TRUE 를반환하는부울속성
%ISOPEN 속성 커서가열려있을때만행을 fetch 할수있음. -> 커서가열려있는지테스트하기위해, fetch 를수행하기전에 %ISOPEN 커서속성을사용하여확인. 예 : CURSOR c1_cursor IS IF NOT c1_cursor%isopen THEN OPEN c1_cursor; END IF; LOOP FETCH c1_cursor;
%NOTFOUND 속성 예 : 처음 10 명의종업원만읽어들이되, 10 명미만이면종료. DECLARE v_empno emp.empno%type;; v_ename emp.ename%type; CURSOR emp_cursor IS SELECT empno, ename FROM emp; BEGIN OPEN emp_cursor; FOR i IN 1..10 LOOP FETCH emp_corsor INTO v_empno, v_ename; EXIT WHEN emp_cursor%notfound; END LOOP; CLOSE emp_cursor; END; 검색결과 10 개미만이검색되면, FOR 문을벗어남.
%ROWCOUNT 속성 반복루프를빠져나갈때를결정하기위해사용. 예 : 처음 10 명의종업원만읽어들임. DECLARE v_empno emp.empno%type;; v_ename emp.ename%type; CURSOR emp_cursor IS SELECT empno, ename FROM emp; BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO v_empno, v_ename; EXIT WHEN emp_cursor%rowcount > 10 OR emp_cursor%notfound; END LOOP; CLOSE emp_cursor; END;
커서와레코드 존재하는테이블의열구조를사용하여레코드정의 명시적커서에서열목록을기초로레코드정의가능 PL/SQL 레코드의필드안으로행값을한꺼번에인출 예 : CURSOR c1_cursor IS SELECT empno, ename FROM emp; emp_record c1_cursor%rowtype; BEGIN OPEN c1_cursor; FETCH c1_cursor INTO emp_record;
커서 FOR 루프 형식 : FOR record_name IN cursor_name LOOP statement1; statement2; END LOOP; 명시적커서를처리하는편리한방법 OPEN, FETCH, CLOSE 가자동으로수행됨. 마지막행에도달하면루프가자동종료됨. 레코드 (record_name) 는자동으로선언됨. 레코드는 Read-Only.
커서 FOR 루프예 예 : 모든종업원을하나씩읽어들임. DECLARE CURSOR emp_cursor IS SELECT empno, ename FROM emp; BEGIN FOR emp_record IN emp_cursor LOOP IF emp_record.empno = 7833 THEN END LOOP; END; implicit close implicit open & implicit fetch
커서 FOR 루프예 SQL> RUN 2 DECLARE 3 v_emp_count NUMBER; 4 v_count NUMBER; 5 -- SELECT 커서 ( 명시적 ) 선언 6 CURSOR get_emp_cursor IS 7 SELECT ename, sal 8 FROM emp 9 ORDER BY ename; 10 BEGIN 11 FOR c1_rec IN get_emp_cursor; 12 LOOP 13 DBMS_OUTPUT.PUT_LINE( Employee : c1_rec.ename 14 Salary: $ c1_rec.sal); 15 END LOOP; 16 END; Employee : JONE Salary : $50000 Employee : SMITH Salary : $65000 오라클이커서를열고닫는것등루프내의모든것을처리하므로 OPEN, CLOSE 불필요. 레코드참조 : record_name.column_name
Subquery 를이용한커서 FOR 루프 커서를따로선언할필요가없음. 예 : BEGIN FOR emp_record IN ( SELECT empno, ename FROM emp ) LOOP IF emp_record.empno = 7833 THEN END LOOP; END; 커서에이름이없으므로이후다시사용할필요가있을 때는불편함.
실습문제 1. SQL* Plus 치환변수를통해서사용자가입력한수만큼, 급여를많이받는종업원순으로 top_emp 테이블에저장하는프로그램을명시적커서를이용하여작성하고, top_emp_ print. sql 파일에저장하시오. Enter the number of employees: 3 2. top_emp 테이블에저장된내용을확인해보시오.