REFCursor usage in oracle

This article introduces the REFCursor usage in oracle. If you want to use REFCursor, refer to the example.

This article introduces the REF Cursor usage in oracle. If you need it, refer to the example.

This article introduces the REF Cursor usage in oracle. If you need it, refer to the example.

1. What is a REF cursor?
A temporary object that dynamically associates with the result set. That is, the query is dynamically determined during running.

2. What is the function of the REF cursor?
You can use ref cursor to transfer result sets between programs to improve SQL Performance.

3. What is the difference between a static cursor and a REF cursor?
① A static cursor is a static definition, and a REF cursor is a dynamic Association;
② REF cursor requires REF cursor variable.
③ REF cursors can be passed as parameters, but static cursors are impossible.

4. What is a REF cursor variable?
A ref cursor variable is a variable that references the REF cursor type and points to a dynamically correlated result set.

5. How to Use the REF cursor?
1. Declare the REF cursor type and determine the REF cursor type;
(1) strong type REF cursor: Specify the retrun type. The type of the REF cursor variable must be the same as the return type.
Syntax: Type REF Cursor name IS Ref Cursor Return result set Return record Type;
(2) weak type ref cursor: return type is not specified and can match any type of CURSOR variable to obtain any result set.
Syntax: Type REF Cursor name IS Ref Cursor;

② Declare the Ref cursor type variable;
Syntax: The variable name has declared the Ref cursor type;

③ Open the REF cursor and associate the result set;
Syntax: return result set of Open Ref cursor type variable For query statement;

④ Retrieve records and operation records;
Syntax: Fatch REF cursor name InTo temporary record type variable or attribute Type Variable list;

⑤ Close the cursor and completely release the resource;
Syntax: Close REF cursor name;

Example: strong type REF cursor

The Code is as follows:

/* Conn scott/tiger */ Declare Type MyRefCurA is ref cursor return emp % RowType; Type MyRefCurB is ref cursor return emp. ename % Type; VRefCurA MyRefCurA; VRefCurB MyRefCurB; VTempA vRefCurA % RowType; VTempB vRefCurB. ename % Type; Begin Open vRefCurA For Select * from emp Where SAL> 2000; Loop Fatch vRefCurA InTo vTempA; Exit When vRefCurA % NotFound; DBMS_OUTPUT.PUT_LINE (vRefCurA % RowCount | ''| vTempA. eno |'' | vTempA. ename | ''| vTempA. sal) End Loop; Close vRefCurA; DBMS_OUTPUT.PUT_LINE ('hangzhou ('-------------------------------------------------------------------------------------------------------'); Open vRefCurB For Select ename from emp Where SAL> 2000; Loop Fatch vRefCurB InTo vTempB; Exit When vRefCurB % NotFound; DBMS_OUTPUT.PUT_LINE (vRefCurB % RowCount | ''| vTempB) End Loop; Close vRefCurB; DBMS_OUTPUT.PUT_LINE ('hangzhou ('-------------------------------------------------------------------------------------------------------'); Open vRefCurA For Select * from emp Where JOB = 'cler '; Loop Fatch vRefCurA InTo vTempA; Exit When vRefCurA % NotFound; DBMS_OUTPUT.PUT_LINE (vRefCurA % RowCount | ''| vTempA. eno |'' | vTempA. ename | ''| vTempA. sal) End Loop; Close vRefCurA; End;

Example: weak type REF cursor

The Code is as follows:
/* Conn scott/tiger */ Declare Type MyRefCur IS Ref Cursor; VRefCur MyRefCur; Vtemp vRefCur % RowType; Begin Case (& n) When 1 Then Open vRefCur For Select * from emp; When 2 Then Open vRefCur For Select * from dept; Else Open vRefCur For Select eno, ename from emp Where JOB = 'cler '; End Case; Close vRefCur; End;

6. How can I pass the REF cursor as a parameter?

The Code is as follows:

-- Return value as Function Create or replace function returnacursor return sys_refcursor Is V_csr sys_refcursor; Begin Open v_csr for select a1 from test3; Return v_csr; End; / Declare C sys_refcursor; A1 char (2 ); Begin C: = returnacursor; Loop Fetch c into a1; Exit when c % notfound; Dbms_output.put_line (a1 ); End loop; Close c; End; / -- As a parameter Create or replace procedure proc_ref_cursor (rc in sys_refcursor) V_a number; V_ B varchar2 (10 ); Begin Loop Fetch rc into v_a, v_ B; Exit when rc % notfound; Dbms_output.put_line (v_a | ''| v_ B ); End loop; End; / Declare V_rc sys_refcursor; Begin Open v_rc Select a1, a2 from test3; Proc_ref_cursor (v_rc ); Close v_rc; End; /

The ref cursor example contains the following three Visual Basic examples to demonstrate how to use ref cursor.

Example
Retrieve the ref cursor parameter from OracleDataReader
In this example, a PL/SQL stored procedure is executed, the REF CURSOR parameter is returned, and the value is read as OracleDataReader.

Use OracleDataReader to retrieve data from multiple REF CURSOR
In this example, a PL/SQL stored procedure is executed, two REF CURSOR parameters are returned, and the value is read using OracleDataReader.

Fill DataSet with one or more REF CURSOR
In this example, a PL/SQL stored procedure is executed, two REF CURSOR parameters are returned, and DataSet is filled with the returned rows.

To use these examples, you may need to create an Oracle table and create a PL/SQL package and package body.

Create an Oracle table
These examples use the tables defined in Oracle Scott/Tiger architecture. Most Oracle installations include the Oracle Scott/Tiger architecture. If this architecture does not exist, you can use the SQL command file in {OracleHome} rdbmsadminscott. SQL to create tables and indexes for these examples.

Create an Oracle package and package body
These examples require that the following PL/SQL package and package body exist on the server. Create the following Oracle packages on the Oracle server

The Code is as follows:

CREATE OR REPLACE PACKAGE BODY CURSPKG PROCEDURE OPEN_ONE_CURSOR (N_EMPNO in number, IO_CURSOR in out T_CURSOR) IS V_CURSOR T_CURSOR; BEGIN IF N_EMPNO <> 0 THEN OPEN V_CURSOR Select emp. EMPNO, EMP. ENAME, DEPT. DEPTNO, DEPT. DNAME From emp, DEPT Where emp. DEPTNO = DEPT. DEPTNO And emp. EMPNO = N_EMPNO; ELSE OPEN V_CURSOR Select emp. EMPNO, EMP. ENAME, DEPT. DEPTNO, DEPT. DNAME From emp, DEPT Where emp. DEPTNO = DEPT. DEPTNO; End if; IO_CURSOR: = V_CURSOR; END OPEN_ONE_CURSOR; PROCEDURE OPEN_TWO_CURSORS (empcursor out T_CURSOR, Deptcursor out T_CURSOR) IS V_CURSOR1 T_CURSOR; V_CURSOR2 T_CURSOR; BEGIN OPEN V_CURSOR1 for select * from emp; OPEN V_CURSOR2 for select * from dept; EMPCURSOR: = V_CURSOR1; DEPTCURSOR: = V_CURSOR2; END OPEN_TWO_CURSORS; End curspkg; /

Oracle provides ref cursor, which can be used to transmit result sets between programs. You can also use ref cursor to implement bulk SQL to improve SQL Performance.

Use scott's emp table to implement the following test cases:

The Code is as follows:

SQL> desc emp Name Null? Type ----------------------------------------------------------------------------- Empno not null number (4) ENAME VARCHAR2 (10) JOB VARCHAR2 (9) Mgr number (4) HIREDATE DATE Sal number (7,2) Comm number (7, 2) Deptno number (2) Use ref cursor to obtain the result set output: SQL> set serveroutput on SQL> DECLARE 2 TYPE mytable is table of emp % ROWTYPE; 3 l_data mytable; 4 l_refc sys_refcursor; 5 BEGIN 6 OPEN l_refc 7 SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp; 8 9 FETCH l_refc bulk collect into l_data; 10 11 CLOSE l_refc; 12 13 FOR I IN 1 .. l_data.COUNT 14 LOOP 15 DBMS_OUTPUT.put_line (l_data (I). ename 16 | 'was hired since' 17 | l_data (I). hiredate 18 ); 19 end loop; 20 END; 21/ SMITH was hired since 17-DEC-80 ALLEN was hired since 20-FEB-81 WARD was hired since 22-FEB-81 JONES was hired since 02-APR-81 MARTIN was hired since 28-SEP-81 BLAKE was hired since 01-MAY-81 CLARK was hired since 09-JUN-81 SCOTT was hired since 19-APR-87 KING was hired since 17-NOV-81 TURNER was hired since 08-SEP-81 ADAMS was hired since 23-MAY-87 JAMES was hired since 03-DEC-81 FORD was hired since 03-DEC-81 MILLER was hired since 23-JAN-82 PL/SQL procedure successfully completed. -The End-