以下示例演示了游标变量用法。
从函数返回 REF CURSOR
在以下示例中，将使用一个查询打开游标变量，该查询选择具有给定工作的员工。请注意，在此函数的 RETURN 语句中指定了游标变量，因此结果集可供函数的调用方使用。
CREATE OR REPLACE FUNCTION emp_by_job (p_job VARCHAR2)
RETURN SYS_REFCURSOR
IS
emp_refcur SYS_REFCURSOR;
BEGIN
OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE job = p_job;
RETURN emp_refcur;
END;通过将函数的返回值赋给在以下匿名块的声明部分中声明的游标变量，可以在匿名块中调用此函数。使用此游标变量获取结果集，然后关闭结果集。
DECLARE
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
v_job emp.job%TYPE := 'SALESMAN';
v_emp_refcur SYS_REFCURSOR;
BEGIN
DBMS_OUTPUT.PUT_LINE('EMPLOYEES WITH JOB ' || v_job);
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
DBMS_OUTPUT.PUT_LINE('----- -------');
v_emp_refcur := emp_by_job(v_job);
LOOP
FETCH v_emp_refcur INTO v_empno, v_ename;
EXIT WHEN v_emp_refcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
END LOOP;
CLOSE v_emp_refcur;
END;以下是执行匿名块时的输出。
EMPLOYEES WITH JOB SALESMAN
EMPNO ENAME
----- -------
7499 ALLEN
7521 WARD
7654 MARTIN
7844 TURNER模块化游标操作
以下示例说明了如何将对游标变量的各种操作模块化为单独的程序。
以下存储过程使用 SELECT 命令打开给定的游标变量，该命令检索所有行。
CREATE OR REPLACE PROCEDURE open_all_emp (
p_emp_refcur IN OUT SYS_REFCURSOR
)
IS
BEGIN
OPEN p_emp_refcur FOR SELECT empno, ename FROM emp;
END;此变体使用 SELECT 命令打开给定的游标变量，该命令检索除给定部门外的所有行。
CREATE OR REPLACE PROCEDURE open_emp_by_dept (
p_emp_refcur IN OUT SYS_REFCURSOR,
p_deptno emp.deptno%TYPE
)
IS
BEGIN
OPEN p_emp_refcur FOR SELECT empno, ename FROM emp
WHERE deptno = p_deptno;
END;这第三个变体使用 SELECT 命令打开给定的游标变量，该命令检索所有行，但是从另一个表中检索。另请注意，函数的返回值是打开的游标变量。
CREATE OR REPLACE FUNCTION open_dept (
p_dept_refcur IN OUT SYS_REFCURSOR
) RETURN SYS_REFCURSOR
IS
v_dept_refcur SYS_REFCURSOR;
BEGIN
v_dept_refcur := p_dept_refcur;
OPEN v_dept_refcur FOR SELECT deptno, dname FROM dept;
RETURN v_dept_refcur;
END;此存储过程获取并显示由员工编号和姓名组成的游标变量结果集。
CREATE OR REPLACE PROCEDURE fetch_emp (
p_emp_refcur IN OUT SYS_REFCURSOR
)
IS
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
DBMS_OUTPUT.PUT_LINE('----- -------');
LOOP
FETCH p_emp_refcur INTO v_empno, v_ename;
EXIT WHEN p_emp_refcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
END LOOP;
END;此存储过程获取并显示由部门编号和名称组成的游标变量结果集。
CREATE OR REPLACE PROCEDURE fetch_dept (
p_dept_refcur IN SYS_REFCURSOR
)
IS
v_deptno dept.deptno%TYPE;
v_dname dept.dname%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('DEPT DNAME');
DBMS_OUTPUT.PUT_LINE('---- ---------');
LOOP
FETCH p_dept_refcur INTO v_deptno, v_dname;
EXIT WHEN p_dept_refcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_deptno || ' ' || v_dname);
END LOOP;
END;此存储过程关闭给定的游标变量。
CREATE OR REPLACE PROCEDURE close_refcur (
p_refcur IN OUT SYS_REFCURSOR
)
IS
BEGIN
CLOSE p_refcur;
END;以下匿名块执行所有先前描述的程序。
DECLARE
gen_refcur SYS_REFCURSOR;
BEGIN
DBMS_OUTPUT.PUT_LINE('ALL EMPLOYEES');
open_all_emp(gen_refcur);
fetch_emp(gen_refcur);
DBMS_OUTPUT.PUT_LINE('****************');
DBMS_OUTPUT.PUT_LINE('EMPLOYEES IN DEPT #10');
open_emp_by_dept(gen_refcur, 10);
fetch_emp(gen_refcur);
DBMS_OUTPUT.PUT_LINE('****************');
DBMS_OUTPUT.PUT_LINE('DEPARTMENTS');
fetch_dept(open_dept(gen_refcur));
DBMS_OUTPUT.PUT_LINE('*****************');
close_refcur(gen_refcur);
END;以下是匿名块的输出。
ALL EMPLOYEES
EMPNO ENAME
----- -------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
****************
EMPLOYEES IN DEPT #10
EMPNO ENAME
----- -------
7782 CLARK
7839 KING
7934 MILLER
****************
DEPARTMENTS
DEPT DNAME
---- ---------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
*****************