利用REF CURSOR，可以在程序间传递结果集（一个程序里打开游标变量，在另外的程序里处理数据）。也可以利用REF CURSOR实现BULK SQL，提高SQL性能。REF CURSOR分两种，Strong REF CURSOR 和 Weak REF CURSOR。Strong REF CURSOR:指定retrun type，CURSOR变量的类型必须和return type一致。DECLARETYPE strongcurtyp IS REF CURSOR RETURN emp%ROWTYPE;emp_cv strongcurtyp;Weak REF CURSOR:不指定return type，能和任何类型的CURSOR变量匹配。DECLARETYPE weakcurtyp IS REF CURSOR;weak_cv weakcurtyp;any_cv SYS_REFCURSOR; --使用SYS_REFCURSOR可以使变量any_cv跟任何weak REF CURSOR类型匹配。
使用Strong REF CURSOR例子CREATE OR REPLACE PACKAGE emp_data ASTYPE empcurtyp IS REF CURSOR RETURN emp%rowtype; --定义TYPE as Strong REF CURSOR PROCEDURE open_emp_cv(emp_cv IN OUT empcurtyp, choice INT); --根据不同的choice选择不同的CURSORPROCEDURE retrieve_data(choice INT); --通过调用procedure open_emp_cv，返回指定的结果集。END emp_data;=============================================CREATE OR REPLACE PACKAGE BODY emp_data AS--procedure open_emp_cv-----------------------------PROCEDURE open_emp_cv(emp_cv IN OUT empcurtyp, choice INT) IS --emp_cv作为传入/传出的CURSOR PARAMETERBEGINIF choice = 1 THENOPEN emp_cv FORSELECT * FROM emp WHERE empno < 7800;ELSIF choice = 2 THENOPEN emp_cv FORSELECT * FROM emp WHERE SAL < 1000;ELSIF choice = 3 THENOPEN emp_cv FORSELECT * FROM emp WHERE ename like 'J%';END IF;END;--procedure retrieve_data----------------------------------PROCEDURE retrieve_data(choice INT) ISreturn_cv empcurtyp; --定义传入open_emp_cv的CURSOR变量return_row emp%ROWTYPE;invalid_choice EXCEPTION;BEGINopen_emp_cv(return_cv, choice); --调用 procedure OPEN_EMP_CVIF choice = 1 THENDBMS_OUTPUT.PUT_LINE('EMPLOYEES with empno less than 7800');ELSIF choice = 2 THENDBMS_OUTPUT.PUT_LINE('EMPLOYEES with salary less than 1000');ELSIF choice = 3 THENDBMS_OUTPUT.PUT_LINE('EMPLOYEES with name starts with ''J''');ELSERAISE invalid_choice;END IF;LOOPFETCH return_cvINTO return_row;EXIT WHEN return_cv%NOTFOUND;DBMS_OUTPUT.PUT_LINE(return_row.empno || '--' || return_row.ename || '--' ||return_row.sal);END LOOP;EXCEPTIONWHEN invalid_choice THENDBMS_OUTPUT.PUT_LINE('The CHOICE should be in one of (1,2,3)!');WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Errors in procedure RETRIEVE_DATA!');END;END emp_data;================================执行：SQL> EXEC emp_data.retrieve_data(1);EMPLOYEES with empno less than 78007369--SMITH--8007499--ALLEN--16007521--WARD--12507566--JONES--29757654--MARTIN--12507698--BLAKE--28507782--CLARK--24507788--SCOTT--3000PL/SQL procedure successfully completedSQL> EXEC emp_data.retrieve_data(2);EMPLOYEES with salary less than 10007369--SMITH--8007900--JAMES--950PL/SQL procedure successfully completedSQL> EXEC emp_data.retrieve_data(3);EMPLOYEES with name starts with 'J'7566--JONES--29757900--JAMES--950PL/SQL procedure successfully completedSQL> EXEC emp_data.retrieve_data(34);The CHOICE should be in one of (1,2,3)!PL/SQL procedure successfully completed
使用Weak REF CURSOR例子
--procedure open_cv---------------------------------------create or replace procedure open_cv(choice IN INT,return_cv OUT SYS_REFCURSOR) is--参数return_cv为weak REF CURSOR,利用SYS_CURSOR来定义beginif choice = 1 thenopen return_cv for 'select * from emp';elsif choice = 2 thenopen return_cv for 'select * from dept';end if;end open_cv;--procedure retrieve_data------------------------------------create or replace procedure retrieve_data(choice IN INT) isemp_rec emp%rowtype;dept_rec dept%rowtype;return_cv SYS_REFCURSOR;invalid_choice exception;beginif choice=1 thendbms_output.put_line('employee information');open_cv(1,return_cv); --调用procedure open_cv;loopfetch return_cv into emp_rec;exit when return_cv%notfound;dbms_output.put_line(emp_rec.empno||'-'||emp_rec.ename||'-'||emp_rec.sal);end loop;elsif choice=2 thendbms_output.put_line('department information');open_cv(2,return_cv);loopfetch return_cv into dept_rec;exit when return_cv%notfound;dbms_output.put_line(dept_rec.deptno||'-'||dept_rec.dname||'-'||dept_rec.loc);end loop;elseraise invalid_choice;end if;exceptionwhen invalid_choice thendbms_output.put_line('The CHOICE should be one of 1 and 2!');when others thendbms_output.put_line('Errors in procedure retrieve_data');end retrieve_data;-----------------------------------------------------------------执行：SQL> exec retrieve_data(1);employee information7369-SMITH-8007499-ALLEN-16007521-WARD-12507566-JONES-29757654-MARTIN-12507698-BLAKE-2850......PL/SQL procedure successfully completedSQL> exec retrieve_data(2);department information10-ACCOUNTING-NEW YORK20-RESEARCH-DALLAS30-SALES-CHICAGO40-OPERATIONS-BOSTONPL/SQL procedure successfully completed
用REF CURSOR实现BULK功能
1. To speed up INSERT, UPDATE, and DELETE statements, enclose the SQL statement within a PL/SQL FORALL statement instead of a loop construct.2. To speed up SELECT statements, include the BULK COLLECT INTO clause in the SELECT statement instead of using INTO.SQL> create table tab2 as select empno ID, ename NAME, sal SALARY from emp where 1=2;Table created-------------------------------------------------------create or replace procedure REF_BULK istype empcurtyp is ref cursor;type idlist is table of emp.empno%type;type namelist is table of emp.ename%type;type sallist is table of emp.sal%type;emp_cv empcurtyp;ids idlist;names namelist;sals sallist;row_cnt number;beginopen emp_cv forselect empno, ename, sal from emp;fetch emp_cv BULK COLLECTINTO ids, names, sals; --BULK COLLECT INTO instead of INTOclose emp_cv;for i in ids.first .. ids.last loopdbms_output.put_line('id=' || ids(i) || ' name=' || names(i) ||' salary=' || sals(i));end loop;forall i in ids.first .. ids.last --FORALL instead of FOR ...LOOPinsert into tab2 values (ids(i), names(i), sals(i));commit;select count(*) into row_cnt from tab2;dbms_output.put_line('-----------------------------------');dbms_output.put_line('The row number of tab2 is ' || row_cnt);end REF_BULK;------------------------------------------------------------执行：SQL> exec ref_bulk;id=7369 name=SMITH salary=800id=7499 name=ALLEN salary=1600id=7521 name=WARD salary=1250id=7566 name=JONES salary=2975id=7654 name=MARTIN salary=1250id=7698 name=BLAKE salary=2850id=7782 name=CLARK salary=2450id=7788 name=SCOTT salary=3000id=7839 name=KING salary=5000id=7844 name=TURNER salary=1500id=7876 name=ADAMS salary=1100id=7900 name=JAMES salary=950id=7902 name=FORD salary=3000id=7934 name=MILLER salary=1300-----------------------------------The row number of tab2 is 14PL/SQL procedure successfully completed
__________________i have a good job and hope to have a great career,i have a nice girl friend and hope to have a great marriage,i have a wonderful parents and hope to be a good parent too,i have a rented house and i hope to have a home of my own,i travel in taxi and i hope to have and drive my own car,i wonder when i have all i want .........what's next in my needs list,i wonder when i have all i want..........will i still be a happy man in the world,i wonder when i have all i want..........will this world be still a happy place to live in....?
