Prosedurdan (REF CURSOR) dəyəri necə geri qaytarmaq olar?

Oracle PL/SQL-də, prosedurdan satırların to'plamini qaytarish uchun ko'pincha REF CURSOR ishlatiladi. Bu so'rov natijasiga ishora qiluvchi ko'rsatkich bo'lib, uni protsedura ichida ochish va chaqiruvchi koda qaytarish mumkin.

REF CURSOR turidagi OUT parametr bilan protsedura misoli:

CREATE OR REPLACE PROCEDURE get_employees(p_dept_id IN NUMBER, p_refcur OUT SYS_REFCURSOR) AS
BEGIN
  OPEN p_refcur FOR
    SELECT employee_id, first_name, last_name
    FROM employees
    WHERE department_id = p_dept_id;
END;
/

PL/SQL blokidan chaqirish:

DECLARE
  rc SYS_REFCURSOR;
  emp_id employees.employee_id%TYPE;
  fname employees.first_name%TYPE;
  lname employees.last_name%TYPE;
BEGIN
  get_employees(10, rc);
  LOOP
    FETCH rc INTO emp_id, fname, lname;
    EXIT WHEN rc%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(emp_id || ' ' || fname || ' ' || lname);
  END LOOP;
  CLOSE rc;
END;
/

Shu tarzda, protsedura kerakli so'rov bilan REF CURSOR ochadi va chaqiruvchi kod undan ma'lumotlarni o'qiydi.