Ref Cursor / sys_refcursor

REF CURSOR

OPEN-FOR、FETCH和CLOSE语句被用于处理多行查询或者游标。这个概念非常类似于静态的游标处理。对于静态游标，首先把游标变量与查询建立关联。接下来，打开游标变量，以便于指向结果集的第一行数据。然后，从结果集中每次检索一行数据。最后，当处理完所有数据行是，关闭游标

对于动态SQL,OPEN-FOR语句有个可选的USING子句，使用它可以在运行时间向绑定变量参数传值。OPEN-FOR语句的通用语法如下所示(方括号中的保留字和短语都是可选的);

 OPEN cursor_variable FOR dynamic_SQL_string

     [USING bind_argument1, bind_argument2, ...]

cursor_variable是一种若类型的REF CURSOR变量，并且dynamic_sql_string是包含多行查询的字符串

   
   
DECLARE
	type student_cur_type is REF CURSOR;
	student_cur student_cur_type;
	type student_type is table of student%rowtype index by pls_integer;
	student_tab student_type;
	v_student_id student.student_id%type := &student_id;
	v_count integer := 0;
BEGIN
	open student_cur for
		'select first_name,last_name from student where student_id < :1'
	using v_student_id;
	LOOP
		FETCH student_cur into student_tab(v_count).first_name,student_tab(v_count).last_name;
		exit when student_cur%notfound;
		v_count := v_count + 1;
	end loop;
	for i in 1..v_count loop
		DBMS_OUTPUT.PUT_LINE('First name :'||student_tab(i).first_name||' '||'Last name :'||student_tab(i).last_name);
	end loop;
	close student_cur;
EXCEPTION
	when others then
		if student_cur%isopen then
			close student_cur;
		end if;
	DBMS_OUTPUT.PUT_LINE('ERROR: '||substr(sqlerrm,1,200));
end;
/

    
    
sys_refcursor是oracle9i以后系统定义的一个refcursor,主要用在过程中返回结果集。 
create or replace procedure getEmpByDept(in_deptNo in emp.deptno%type, 
 out_curEmp out SYS_REFCURSOR) as 
 begin 
 open out_curEmp for 
 SELECT * FROM emp WHERE deptno = in_deptNo ; 
 EXCEPTION 
 WHEN OTHERS THEN 
 RAISE_APPLICATION_ERROR(-20101, 
 'Error in getEmpByDept' || SQLCODE ); 
 end getEmpByDept; 
 / 
scott@ORCL>var rset refcursor; 
scott@ORCL>exec getEmpByDept(10,:rset); 
PL/SQL procedure successfully completed.
scott@ORCL>print rset
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
CREATE OR REPLACE FUNCTION Get_emp
RETURN SYS_REFCURSOR
IS
  return_cursor SYS_REFCURSOR;
BEGIN
  OPEN return_cursor FOR 'SELECT * FROM emp';
  RETURN return_cursor;
END;
select Get_emp() from dual;
GET_EMP()
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
14 rows selected.
-- 存储过程调用， 来获取结果.
DECLARE 
  -- 调用函数的返回值.
  testCursor SYS_REFCURSOR;
  -- 存储单行数据.
  testRec  emp%ROWTYPE;
BEGIN
  -- 调用返回结果集的函数.
  testCursor := Get_emp();
  -- 循环，遍历结果.
  LOOP
    -- 游标向前.
    FETCH testCursor INTO testRec;
    -- 无数据的情况下，退出循环.
 EXIT WHEN testCursor%NOTFOUND;
    -- 输出调试信息.
    dbms_output.put_line( TO_CHAR(testRec.empno) || ' ' || testRec.ename);
  END LOOP;
END;
/
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