본문 바로가기

Database/PL SQL

PL/SQL Tutorial - Variables with REF CURSOR

cursor variable

DECLARE
    TYPE customer_data_t IS REF CURSOR RETURN customers%ROWTYPE;
    customer_cur customer_data_t;

example로 이해해보자.

-- function
CREATE OR REPLACE FUNCTION get_direct_reports(
      p_manager_id IN employees.manager_id%TYPE)
   RETURN SYS_REFCURSOR
AS
   c_direct_reports SYS_REFCURSOR;
BEGIN
 
   OPEN c_direct_reports FOR 
   SELECT 
      employee_id, 
      first_name, 
      last_name, 
      email
   FROM 
      employees 
   WHERE 
      manager_id = p_manager_id 
   ORDER BY 
         first_name,   
         last_name;
 
   RETURN c_direct_reports;
END;

(1) get_direct_reports 라는 procedure를 만든다.

(2) sys_refCURSOR 이라는 커서를 정의한다.

(3) 얘는 p_manager_id가 manager_id인 employee의 employee_id, first_name, last_name, email을 가지고 있다.

DECLARE
   c_direct_reports SYS_REFCURSOR;
   v_employee_id employees.employee_id%type;
   v_first_name employees.first_name%type;
   v_last_name employees.last_name%type;
   v_email employees.email%type;
BEGIN
   c_direct_reports := get_direct_reports(46); -- get the ref cursor from
   -- function
   LOOP
      FETCH
         c_direct_reports
      INTO
         v_employee_id,
         v_first_name,
         v_last_name,
         v_email;
      EXIT
   WHEN c_direct_reports%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_first_name || ' ' || v_last_name || ' - ' ||    v_email );
   END LOOP;
END;
/

익명 블록이 완료되었습니다.
Evie Harrison - evie.harrison@example.com
Grace Ellis - grace.ellis@example.com
Lily Fisher - lily.fisher@example.com
Poppy Jordan - poppy.jordan@example.com
Sophia Reynolds - sophia.reynolds@example.com
Sophie Owens - sophie.owens@example.com

이게 무슨 말일까? 해답은 SELECT를 해보면 나온다.

SELECT employee_id, first_name, last_name, email FROM employees WHERE manager_id = 46;

def CURSOR() 형식과 비슷하게 cursor 함수를 만들어 원하는 값을 return 받고 이를 사용한 것이다.

'Database > PL SQL' 카테고리의 다른 글

PL/SQL Tutorial - Record  (0) 2019.05.08
PL/SQL Tutorial - Parameters  (0) 2019.05.08
PL/SQL Tutorial - Cursor FOR LOOP  (0) 2019.05.08
PL/SQL Tutorial - Cursor  (0) 2019.05.08
PL/SQL Tutorial - SELECT INTO  (0) 2019.05.08