[Oracle] cursor 할당

[Oracle] cursor 할당

1. 선언부 

1) PROCEDURE 선언부

CREATE OR REPLACE PROCEDURE where_current IS CURSOR emp_list IS SELECT empno,empname FROM emp BEGIN FOR rec in emp_list LOOP END LOOP OR LOOP .. EXIT WHEN emp_cv%notfound; .. END LOOP ....

2) 변수

DECLARE CURSOR c1 IS SELECT empno,empname FROM emp ORDER BY empno BEGIN ....

3) 매개변수 전달

DECLARE -- 매개변수 받기, 부서코드 CURSOR c1 ( p_deptid emp.deptid%TYPE ) IS SELECT empno,empname FROM emp WHERE deptid = p_deptid; BEGIN OPEN c1 ('총무1'); OR FOR rec IN c1('총무1') ....

2. 실행부

1) Cursor 커서명 IS ~ : 변수선언

V_COL1 VARCHAR2(10 CHAR); CURSOR Cursor명 IS SELECT Col1 FROM 테이블명; BEGIN OPEN Cursor명; LOOP FETCH Cursor명 INTO V_COL1; EXIT WHEN Cursor명%NOTFOUND; <<로직처리>> END LOOP; CLOSE Cursor명; END;

2) OPEN ~ FOR ~ : 기본

CREATE PACKAGE emp_data AS TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, choice IN INT); END emp_data; CREATE PACKAGE BODY emp_data AS PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, choice IN INT) IS BEGIN IF choice = 1 THEN OPEN emp_cv FOR SELECT * FROM emp WHERE comm IS NOT NULL; ELSIF choice = 2 THEN OPEN emp_cv FOR SELECT * FROM emp WHERE sal > 2500; ELSIF choice = 3 THEN OPEN emp_cv FOR SELECT * FROM emp WHERE deptno = 20; END IF; END; END emp_data;

3) 동적쿼리로 할당

DECLARE TYPE EmpCurTyp IS REF CURSOR; -- define weak REF CURSOR type emp_cv EmpCurTyp; -- declare cursor variable my_ename VARCHAR2(15); my_sal NUMBER := 1000; BEGIN OPEN emp_cv FOR -- open cursor variable 'SELECT ename, sal FROM emp WHERE sal > :s' USING my_sal; loop fetch emp_cv into my_ename,my_sal; exit when emp_cv%notfound; -- Exit the loop if there is nothing to fetch. -- process fetched data end loop; END;

4) 동적할당

DECLARE l_vc_name VARCHAR2(100); BEGIN FOR loop_emp IN ( SELECT last_name ||', '||first_name name INTO l_vc_name FROM employees WHERE employee_id=123 ) LOOP dbms_output.put_line('Cursor Result: '||loop_emp.name); dbms_output.put_line('INTO clause Result: '||NVL(l_vc_name,'Null')); END LOOP loop_emp; END;