Oracle PL/SQL test function return SYS_REFCURSOR

在PL/SQL block測試function回傳的SYS_REFCURSOR。

EMPLOYEE資料表現有資料如下。

EMPLOYEE

+----+------+-----+
| ID | NAME | AGE |
+----+------+-----+
| 1  | John | 33  |
+----+------+-----+
| 2  | Mary | 27  |
+----+------+-----+

function get_employee如下，依據傳入的i_id查詢EMPLOYEE。

CREATE OR REPLACE FUNCTION get_employee(
    i_id IN EMPLOYEE.ID%TYPE
) RETURN SYS_REFCURSOR
AS
    o_rc SYS_REFCURSOR;
BEGIN
    
    OPEN o_rc FOR
    SELECT e.ID, e.NAME, e.AGE
    FROM EMPLOYEE e
    WHERE e.ID = i_id;
    
    RETURN o_rc;
    
END;

PL/SQL測試。

DECLARE
    v_rc SYS_REFCURSOR;
    v_id EMPLOYEE.ID%TYPE;
    v_name EMPLOYEE.NAME%TYPE;
    v_age EMPLOYEE.AGE%TYPE;
BEGIN

    v_rc := get_employee(1);
    LOOP
        FETCH v_rc INTO
            v_id, 
            v_name, 
            v_age;
        EXIT WHEN v_rc%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_id);
    END LOOP;
    
END;