Microsoft Word - 03_SQL_CURSOR.doc

Size: px

Start display at page:

Download "Microsoft Word - 03_SQL_CURSOR.doc"

진수 신
6 years ago
Views:

1 SQL Cursor SQL 커서소개오라클서버에서는 SQL 문을실행할때마다처리 (Parse, Execution) 를위한메모리공간, 즉 SQL 커서를사용하게된다. 이메모리공간은 Private SQL Area 라고도불리우며, 오라클의작업환경이 Dedicated Server 환경이냐또는 MTS(Multi- Threaded Server) 환경이냐에따라서버내에위치되는곳이다르다. PL/SQL 블록이실행될때에도내부에포함된 SQL 문장에대해 SQL 커서가자동적으로생성되며, 필요에따라서는 SQL 커서를사용자가선언하여사용할수도있다. SQL 커서의유형에는크게묵시적커서 (Implicit Cursor) 와명시적커서 (Explicit Cursor) 로나누어진다1. 묵시적커서는오라클이자동적으로선언하여사용한후자동적으로정리 (Clean-up) 해준다. 이에반하여명시적커서는사용자가정의한커서를선언하여사용하고, 커서의사용이끝난후에는별도의정리 (Clean-up) 작업을수행해줘야한다. SQL 커서를사용할때, 커서는임의의커서상수값을제공하는데, 이를 SQL 커서속성 (Cursor Attribute) 이라고한다. 이커서속성은커서의유형에따라조금씩다른의미를지니고있음에유의한다. 커서속성에대한자세한사항은뒷부분에서다루기로한다. 1 보통오라클에서는 묵시적 (Implicit) 이라는단어의의미는 자동적이다 라는의미를내포하고있으며, 이에반하여 명시적 (Explicit) 의의미는 수동적이다 라는의미를내포하고있다. 이두단어의의미는 SQL 커서에서도마찬가지로사용되고있다. Written by AngelaLEE 3-1

2 묵시적커서 (Implicit Cursor) 묵시적커서는오라클에서자동적으로선언해주는 SQL 커서로서, 사용자에게는투명하게제공된다. 기본적으로 PL/SQL 블록내에서의 SELECT 문, DML(INSERT, UPDATE, DELETE) 문이실행될때마다묵시적커서가선언된다. 여기에서주의해야할점은묵시적커서의경우세션내에단한개만이선언되어사용되었다가문장이종료됨과동시에정리된다는것이다. 그러므로동시에여러개의묵시적커서가사용되는것이아니라는점이다. 묵시적커서는 SQL 문의실행된결과를커서속성 (Cursor Attribute) 을통해제공하고있는데, 이는가장마지막에실행된 SQL 문의결과임에주의한다. 커서속성에는 4 가지가있는데, 묵시적커서임을나타내는 SQL% 을접두어 (Prefix) 로사용한다. >> 묵시적커서속성 (Cursor Attribute) 종류 SQL%ROWCOUNT SQL%FOUND SQL%NOTFOUND SQL%ISOPEN 설명 가장마지막에실행된 SQL 문장이처리한데이터행의총개수를가지는속성변수. 가장마지막에실행된 SQL 문장이처리한데이터행이있을경우에는 TRUE( 참 ) 진리값을, 처리한데이터행이없을경우에는 FALSE( 거짓 ) 진리값을가지는속성 Boolean 변수. 가장마지막에실행된 SQL 문장이처리한데이터행이없을경우에는 TRUE( 참 ) 진리값을, 처리한데이터행이있을경우에는 FALSE( 거짓 ) 진리값을가지는속성 Boolean 변수. 현재묵시적커서가메모리에확보되어있을경우에는 TRUE( 참 ) 진리값을, 그렇지않을경우에는 FALSE( 거짓 ) 진리값을가지는속성 Boolean 변수. ( 단, 묵시적커서는문장이실행종료됨과동시에정리 (Clean-up) 되므로이커서속성은항상 FALSE( 거짓 ) 진리값을가지게됨에주의한다.) Written by AngelaLEE 3-2

3 * 참고 : SELECT 문장의경우, 한건의데이터를검색할경우이외에는오라클예외사항 (EXCEPTION) 이발생한다. 그러므로, SELECT 문장에관련된묵시적커서의경우에도마찬가지로한건의데이터를처리하는경우에만정상적으로사용가능하다. 만약, 여러개의행이검색될경우에처리를정상적으로하고자한다면, 명시적커서 (Explicit Cursor) 를사용하면된다. > EX: VARIABLE del_cnt VARCHAR2(50) V_DNO DEPT.DEPTNO%TYPE := &p_dno ; DELETE EMP WHERE DEPTNO = V_DNO ; :del_cnt := SQL%ROWCOUNT 건삭제되었습니다. ; END; PRINT del_cnt Written by AngelaLEE 3-3

4 명시적커서 (Explicit Cursor) Introduction 명시적커서는필요에따라사용자가선언하여사용하는 SQL 커서공간으로, 기본적으로는여러개의행을처리하고할경우사용된다. 여러건을검색하는 SELECT 문장의경우오라클은예외사항 (TOO_MANY_ROWS) 을발생하게된다. 이때, 오라클은사용자가정의한명시적커서영역에 SELECT 문에의해검색된여러건의데이터를임시적으로저장하고, 한건씩처리할수있도록명시적커서기능을제공한다. 명시적커서는동시에여러개가선언되어사용될수있으며, 묵시적커서와마찬가지로커서속성변수를제공한다. 하지만, 의미는묵시적커서와약간씩다르다. 명시적커서는여러개가선언될수있으므로, 커서속성변수는 명시적커서명 (Cursor Name)% 을커서속성변수의접두어 (Prefix) 로붙여서사용한다. >> 명시적커서속성 (Cursor Attribute) 종류 %ROWCOUNT %FOUND %NOTFOUND %ISOPEN 설명 FETCH 문에의해읽혀진데이터의총건수를가지는속성변수. FETCH 문이수행되었을경우, 읽혀진 (Fetch) 행이있을경우에는 TRUE( 참 ) 진리값을, 그렇지않을경우에는 FALSE( 거짓 ) 진리값을가지는속성 Boolean FETCH 문이수행되었을경우, 읽혀진 (Fetch) 행이없을경우에는 TRUE( 참 ) 진리값을, 그렇지않을경우에는 FALSE( 거짓 ) 진리값을가지는속성 Boolean 명시적커서가메모리에확보 ( 선언 ) 되어있을경우에는 TRUE( 참 ) 진리값을, 그렇지않을경우에는 FALSE( 거짓 ) 진리값을가지는속성 Boolean 변수. Written by AngelaLEE 3-4

5 Explicit Cursor 처리단계 명시적커서를사용하기위해서는필수적으로수행해야할 4단계가있다. 1. 명시적커서선언 (Declaration) 2. 명시적커서열기 (Open) 3. 명시적커서로부터처리할데이터행을읽어오기 (Fetch) ( 명시적커서에처리하고자하는데이터행이있다면, 처리가완료될때까지계속읽는다.) 4. 명시적커서의데이터행에처리가완료되었다면, 정리 (Clean-up) 작업을하기위해명시적커서를닫는다.(Close) Written by AngelaLEE 3-5

6 명시적커서선언 (Declaration) 명시적커서는선언부 (declare) 에다른식별자와마찬가지로선언되며, 형식은아래와같다. 주의해야할점은명시적커서가선언부에기술되어도메모리공간이확보되는것이아니라단지정의만이루어진다는것이다. > SYNTAX: CURSOR cursor_name IS select-statement ; cursor_name select-statement 명시적커서의이름으로유일하게명명되어야한다. 기본적으로명시적커서는여러건을검색하는 SELECT 문을처리하기위한것이므로처리하고자하는데이터를검색하는 SELECT 문장을이부분에기술하면된다. 단, 주의해야할사항은정의를위한 SELECT 문이므로 PL/SQL 블록의 SELECT~INTO 절과는구분해야한다. 즉, 명시적커서를정의할때의 SELECT 문은 INTO 절을가지지않는다. > EX: CURSOR emp_cur IS SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = 20; CURSOR dept_emp IS SELECT D.DNAME, E.ENAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.ENAME LIKE %A% ; Written by AngelaLEE 3-6

7 명시적커서열기 (Open) 명시적커서가사용되기위해서는메모리공간이확보되어야하는데, 바로커서열기 (Open) 를통해이루어진다. 커서를열게되면, 선언부에서정의된커서의 SELECT 문이실행되면서해당데이터들 (Active Set) 이명시적커서영역에자리잡게된다. 즉 SELECT 문에의해메모리공간크기가결정되는것이다. 이때, 명시적커서영역에자리잡은데이터의첫번째행에커서포인터 (pointer) 가위치한다. 바로이포인터위치의데이터행을 FETCH 문이읽게된다. > SYNTAX: OPEN cursor_name ; cursor_name 열고자하는명시적커서의이름으로선언부에선언된커서명중에하나이어야한다. QUIZ: 명시적커서가열릴때, SELECT 문의결과행이한건도없었다면정상적으로명시적커서가열리는가? Written by AngelaLEE 3-7

8 > EX: END; CURSOR emp_cur IS SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = 20; CURSOR dept_emp IS SELECT D.DNAME, E.ENAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.ENAME LIKE %A% ; OPEN emp_cur ; OPEN dept_emp ; Written by AngelaLEE 3-8

9 명시적커서로부터데이터읽기 (Fetch) 명시적커서의데이터들 (Active Set) 로부터데이터를한건씩읽어오기위해서 FETCH 문을사용한다. 이때읽게되는데이터행은포인터 (Pointer) 에의해서지정된다. FETCH 가되면자동적으로포인터는다음행에위치하게된다. FETCH 후데이터가정상적으로검색되었는지를판단하기위해 %FOUND, %NOTFOUND 와같은커서속성변수를사용한다. 데이터들을읽어처리하기위해서 FETCH 문은반복문과함께사용한다. SELECT~INTO 절과마찬가지로 FETCH 문도 INTO 절을갖는다. > SYNTAX: FETCH cursor_name INTO variable ; cursor_name Variable 읽어 (Fetch) 오고자하는명시적커서의이름으로반드시 open 되어야한다. 명시적커서로부터읽어온 (Fetch) 데이터행 ( 레코드 ) 을 PL/SQL 블록내에서처리하기위해서는변수에저장해야한다. 이때 variable 에는단순변수또는복합변수가올수있으며, 기본적으로선언부에선언되어야만한다. 만약단순변수를사용한다면, 커서에서정의된 SELECT 리스트의개수만큼선언하고 SELECT 리스트의위치대로 FETCH 의 INTO 절에차례대로기술한다. 만약복합변수를사용한다면, 커서레코드변수 ( 커서명 %ROWTYPE) 를선언하여사용한다. Written by AngelaLEE 3-9

10 > EX: END ; CURSOR emp_cur IS SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = 20; CURSOR dept_emp IS SELECT D.DNAME, E.ENAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.ENAME LIKE %A% ; e_cur emp_cur%rowtype ; --커서레코드변수 v_dname dept.dname%type ; v_ename emp.ename%type ; FETCH emp_cur INTO e_cur ; FETCH dept_emp INTO v_dname, v_ename ; Written by AngelaLEE

11 명시적커서닫기 (Close) 명시적커서의정리 (Clean-up) 작업을하는명령으로, 명시적커서가다사용된후에는반드시 닫기 를강력히권고한다. 만약 PL/SQL 블록이명시적커서를닫지않고종료한다고해도치명적인오류는발생하지않는다. 하지만, 어느 PL/SQL 블록에서동일명의커서가 Open 될때, 오라클은예외 (EXCEPTION) 를발생한다. > SYNTAX: CLOSE cursor_name ; cursor_name 닫고자하는명시적커서의이름으로 Close 된명시적커서는더이상접근될수없다. 다시말해 PL/SQL 블록내에서 Close 된커서를접근하게되면오라클은예외 (INVALID_CURSOR) 를발생하게된다. Written by AngelaLEE

12 > EX: END; CURSOR emp_cur IS SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = 20; CURSOR dept_emp IS SELECT D.DNAME, E.ENAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.ENAME LIKE %A% ; e_cur emp_cur%rowtype ; v_dname dept.dname%type ; v_ename emp.ename%type ; FETCH emp_cur INTO e_cur ; FETCH dept_emp INTO v_dname, v_ename ; CLOSE emp_cur ; CLOSE dept_emp ; Written by AngelaLEE

13 Cursor FOR Loop PL/SQL 블록은데이터트랜잭션을절차적으로처리하기위한효율적인방법을제공한다. 더불어여러건의데이터를처리하고자할때, 명시적커서는필수적인요소이다. 앞부분에설명한것과같이명시적커서를처리하기위해서는기본적으로 4단계를수행해야만가능하다. 이에오라클은명시적커서의좀더편리한사용을위해 FOR 반복문과커서를결합한 CURSOR FOR 루프를제공한다. 이는묵시적인커서변수의선언, 커서열기, 커서내의레코드를자동적으로읽기, 커서닫기를수행한다. 이는별도의변수선언, OPEN, FETCH, CLOSE 문을수행하지않아도됨을의미한다. FOR 문의반복횟수는명시적커서 (Active Set) 내의전체행수와동일하다. > SYNTAX: FOR record_name IN cursor_name LOOP -- 명시적커서의 OPEN, FETCH 가자동적으로수행됨. statement1 ; statement2 ; END LOOP ; -- 루프문을빠져나갈때자동적으로 CLOSE 됨. record_name 이는커서로부터 FETCH 된하나의레코드를저장하기위한커서레코드변수로, 묵시적인선언이이루어진다. 만약선언부에서선언된다면, cursor_name%rowtype 형으로선언되었을것이다. 커서변수는복합변수 record 처럼사용된다. 레코드를구성하는필드를접근할때는레코드변수명을접두어로사용한다. cursor_name 처리하고자하는명시적커서명으로, 선언부에서정의되어야한다. FOR 문이실행되면 IN 다음에기술 Written by AngelaLEE

14 된명시적커서가자동적으로열어지며, 한건씩 FETCH 가되며, 커서영역에데이터가모두처리되면루프를종료하게되는데이때자동적으로명시적커서는 CLOSE 된다. > EX: CURSOR emp_cur IS SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = 20; -- e_cur emp_cur%rowtype ; 선언될필요가없다. END; FOR e_cur IN emp_cur LOOP END LOOP ; IF e_cur.sal >= THEN QUIZ: 만약 FOR 루프안에서 COMMIT 문을수행하게된다면어떤결과가생기는가? Written by AngelaLEE

15 파라미터 Explicit Cursor 명시적커서를사용할때, 정적인데이터들 (Active Set) 을사용할수도있지만, 필요에따라서는동적인데이터들을처리해야할경우도있다. 이때사용되는것이파라미터를가진명시적커서이다. 파라미터를통해명시적커서가열려질때마다다른데이터들 (Active Set) 을처리하도록할수있다. 파라미터를가진명시적커서를오픈할때에는파라미터에값을전달해야한다. > SYNTAX: CURSOR cursor_name IS [ (parameter_name datatype, ) ] select-statement ; parameter_name 파라미터변수명으로변수선언과동일하다. 여러개의파라미터를지정할수있다. > EX: CURSOR emp_cur (p_dno emp.deptno%type)) IS SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = p_dno; OPEN emp_cur(20) ; CLOSE emp_cur ; OPEN emp_cur(30) ; CLOSE emp_cur ; END; Written by AngelaLEE

16 Explicit Cursor 와 FOR UPDATE 명시적커서영역에있는행들에대해잠금 (LOCK) 이필요한경우, 커서를정의할때 SELECT ~ FOR UPDATE; 를사용한다. 만약다른세션에서동일한행에대한잠금이설정된경우, 대기 (Waiting) 를방지하기위해커서정의시 NO WAIT 옵션을사용할수도있다. 만약 FOR UPDATE 절을사용하여명시적커서가선언되었다면, 그커서에 UPDATE 또는 DELETE 하고자한다면, 문장끝에 WHERE CURRENT OF 커서명을붙여서접근한다. > EX: END; CURSOR emp_cur IS SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = 20 FOR UPDATE NO WAIT ; OPEN emp_cur ; UPDATE emp SET sal = sal * 2 WHERE CURRENT OF emp_cur CLOSE emp_cur ; * 참고적으로커서를정의할때, 부질의 (Subquery) 를포함한 SELECT 문으로도가능하다. Written by AngelaLEE

17 실습 SQL CURSOR 1. 부서코드를입력받아해당부서에속한사원들을삭제한후삭제된사원건수를출력하는 PL/SQL 블록. SET ECHO OFF SET SERVEROUTPUT ON V_DNO NUMBER(7,2) := &p_dno ; DELETE EMP WHERE DEPTNO = V_DNO ; DBMS_OUTPUT.PUT_LINE ( 삭제된건수는 SQL%ROWCOUNT ) ; END ; / SET SERVEROUTPUT OFF SET ECHO ON QUIZ: 만약해당부서에속한사원이없었을경우위의 PL/SQL 블록은실패인가? 위의 PL/SQL 블록의출력조건을 - 해당부서에속한사원이없다면 소속사원이없습니다. 라는메시지를, - 그반대의경우라면삭제된건수를출력하도록변경한다면, 위의실행부는어떻게수정해야하는가? Written by AngelaLEE

18 2. 부서코드를입력받아그부서에속한사원들의이름, 급여, 직급을출력하는 PL/SQL 블록. SET ECHO OFF SET SERVEROUTPUT ON CURSOR emp_cur IS SELECT ENAME, SAL, JOB FROM EMP WHERE DEPTNO = &p_dno ; V_ENAME EMP.ENAME%TYPE ; V_SAL EMP.SAL%TYPE ; V_JOB EMP.JOB%TYPE ; OPEN emp_cur ; LOOP END LOOP ; FETCH emp_cur INTO V_ENAME, V_SAL, V_JOB ; EXIT WHEN emp_cur%notfound ; DBMS_OUTPUT.PUT_LINE((RPAD(V_ENAME,16) RPAD(V_SAL,10) V_JOB)) ; CLOSE emp_cur ; END ; / SET SERVEROUTPUT OFF SET ECHO ON Written by AngelaLEE

19 QUIZ: 위의 PL/SQL 블록에서명시적커서를처리할때, FETCH~INTO 절에단순변수가아닌복합변수 (record) 를사용한다면위의블록은어떻게수정해야하는가? ( 단, 레코드변수의이름은 emp_rec 으로한다.) Written by AngelaLEE

20 3. 2 번의 PL/SQL 블록의실행부를 WHILE 문을수정하여재작성하시오. SET ECHO OFF SET SERVEROUTPUT ON CURSOR emp_cur IS SELECT ENAME, SAL, JOB FROM EMP WHERE DEPTNO = &p_dno ; V_ENAME EMP.ENAME%TYPE ; V_SAL EMP.SAL%TYPE ; V_JOB EMP.JOB%TYPE ; OPEN emp_cur ; /* 변경할부분 */ CLOSE emp_cur ; END ; / SET SERVEROUTPUT OFF SET ECHO ON Written by AngelaLEE

21 QUIZ: 위의 PL/SQL 블록에서명시적커서를처리할때, FETCH~INTO 절에단순변수가아닌복합변수 (record) 를사용한다면위의블록은어떻게수정해야하는가? ( 단, 커서레코드변수의이름은 emp_rec 으로한다.) Written by AngelaLEE

22 4. 2번의 PL/SQL 블록을 CURSOR FOR 문으로수정하시오. ( 단, 커서레코드변수의이름은 EMP_REC 으로한다.) SET ECHO OFF SET SERVEROUTPUT ON CURSOR emp_cur IS SELECT ENAME, SAL, JOB FROM EMP WHERE DEPTNO = &p_dno ; END ; / SET SERVEROUTPUT OFF SET ECHO ON Written by AngelaLEE

23 QUIZ: FOR 루프밖에서커서속성 emp_cur%notfound 변수를사용할수있는가? Written by AngelaLEE

24 5. 제시된출력결과에맞도록아래의 PL/SQL 블록을완성하시오. ( 단, 명시적커서는아래에정의된 dept_cur 와 emp_cur 커서만을사용하며, BASIC LOOP 문을사용한다.) [ 출력결과 ] 부서코드 : 10 부서명 : XXXXXXXX ENAME SAL JOB XXXX $30,000 MANAGER XXXXX $2,500 SALES 부서코드 : 20 부서명 : XXXXXXXX ENAME SAL JOB XXX XXXXX Written by AngelaLEE

25 SET ECHO OFF SET SERVEROUTPUT ON CURSOR dept_cur IS SELECT DEPTNO, DNAME FROM DEPT ORDER BY 1 ; CURSOR emp_cur ( p_dno NUMBER) IS SELECT ENAME, SAL, JOB FROM EMP WHERE DEPTNO = p_dno ; Written by AngelaLEE

26 END ; / SET SERVEROUTPUT OFF SET ECHO ON Written by AngelaLEE

27 6. 5 번의 PL/SQL 블록을다중 CURSOR FOR 문으로수정하시오. SET ECHO OFF SET SERVEROUTPUT ON CURSOR dept_cur IS SELECT DEPTNO, DNAME FROM DEPT ORDER BY 1 ; CURSOR emp_cur ( p_dno NUMBER) IS SELECT ENAME, SAL, JOB FROM EMP WHERE DEPTNO = p_dno ; END ; / SET SERVEROUTPUT OFF SET ECHO ON Written by AngelaLEE

28 7. 사원의급여를히스토가로막대로표현하고자하는 PL/SQL 블록. ( 단, 급여를 100 단위로분리하여 100 문자로나타내기로하며, 결과를새로추가한 HISTO 컬럼에도저장한다.) SET ECHO OFF SET SERVEROUTPUT ON CURSOR emp_cur IS SELECT EMPNO, NVL(ROUND(SAL/100),0) CNT FROM EMP ; V_HISTO VARCHAR2(50) := NULL ; FOR emp_rec IN emp_cur LOOP FOR i IN 1.. emp_rec.cnt LOOP V_HISTO := ; END LOOP ; UPDATE EMP SET HISTO = V_HISTO WHERE EMPNO = emp_rec.empno ; V_HISTO := NULL ; END LOOP ; COMMIT ; END ; / SET SERVEROUTPUT OFF SET ECHO ON Written by AngelaLEE

29 HINT : HISTO 컬럼을추가하는명령 > ALTER TABLE EMP ADD (HISTO VARCHAR2(50)) ; SELECT 문을사용하여 HISTO 컬럼에데이터가저장되었는지확인해보자. Written by AngelaLEE

30 8. 7 번의 PL/SQL 블록이실행되는동안, 다른세션에서 EMP 테이블의데이터를변경할수없도록 PL/SQL 블록을수정해보시오. SET ECHO OFF SET SERVEROUTPUT ON CURSOR emp_cur IS SELECT EMPNO, NVL(ROUND(SAL/100),0) CNT FROM EMP ; V_HISTO VARCHAR2(50) := NULL ; FOR emp_rec IN emp_cur LOOP FOR i IN 1.. emp_rec.cnt LOOP V_HISTO := ; END LOOP ; UPDATE EMP SET HISTO = V_HISTO WHERE EMPNO = emp_rec.empno ; V_HISTO := NULL ; END LOOP ; COMMIT ; END ; / SET SERVEROUTPUT OFF SET ECHO ON Written by AngelaLEE

31 HINT : 명시적커서의 SELECT 문에잠금이되도록수정한다. 잠금이수행된명시적커서에접근할때에사용되는절을추가하여변경한다. Written by AngelaLEE

ESQL/C

ESQL/C 20 장. PL/SQL 커서 주요내용 암시적커서 명시적커서선언 명시적커서열기및닫기 명시적커서에서데이터 Fetch 커서의속성 (%ISOPEN, %ROWCOUNT, %FOUND, %NOTFOUND) 커서 FOR 루프 PL/SQL 의커서 (Cursor) 커서 SQL 문과프로그램실행과정에서결과를저장할수있는오라클메모리구조 ( 개별 SQL 작업영역 ) 암시적커서 (Implicit