在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;
沒有留言:
張貼留言