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 |