Oracle cursor usage of ref

1, what is a REF CURSOR?
A temporary object that dynamically associates a result set. That is, dynamically deciding to execute the query at run time.

What is the effect of 2,ref cursors?
Implements the ability to pass result sets between programs, using REF CURSOR can also implement bulk SQL to improve SQL performance.

3, what is the difference between a static cursor and a REF CURSOR?
① static cursors are statically defined, and REF cursors are dynamic associations;
② using a REF CURSOR requires a REF CURSOR variable.
③REF cursors can be passed as parameters, while static cursors are not possible.

4, what is a REF CURSOR variable?
A REF CURSOR variable is a variable that refers to a REF cursor type, pointing to a dynamically associated result set.

5, how do I use a REF CURSOR?
① declares the REF cursor type and determines the REF cursor type;
⑴ strongly type REF cursor: Specifies that the type of the Retrun TYPE,REF cursor variable must be the same as the return type.
Syntax: type REF CURSOR name is ref Cursor return result set returns record type;
⑵ weakly type REF CURSOR: Does not specify return type, can match any type of cursor variable to get any result set.
Syntax: Type REF CURSOR name is ref Cursor;

② declares a REF cursor type variable;
Syntax: variable name declared REF CURSOR type;

③ opens the REF cursor, associating the result set;
Syntax: Open REF cursor type variable for query statement returns result set;

④ obtain records, Operation Records;
Syntax: Fatch REF CURSOR into temporary record type variable or attribute type variable list;

⑤ closes the cursor, releasing the resource completely;
Syntax: close REF CURSOR name;

Example: strongly-typed ref cursors

The code is as follows

Copy Code

/*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 ('---------------------------------------------------------------------------------------------------- ---'); 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 ('---------------------------------------------------------------------------------------------------- ---'); Open Vrefcura for Select * from emp Where JOB = ' clerk '; 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: Weakly typed REF CURSOR

The code is as follows	Copy Code
/*conn scott/tiger*/ Declare Type Myrefcur is Ref Cursor; Vrefcur myrefcur; Vtemp Vrefcur%rowtype; Begin Case (&n) When 1 Then the Open vrefcur for the Select * from EMP; When 2 Then the Open vrefcur for Select * from dept; Else Open vrefcur for Select Eno, ename from emp Where JOB = ' clerk '; End case; Close vrefcur; End;

6, how do I have a REF CURSOR passed as a parameter?

The code is as follows

Copy Code

--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) as 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 for Select A1,a2 from Test3; Proc_ref_cursor (V_RC); Close V_RC; End /

The ref CURSOR sample includes the following three visual Basic examples that demonstrate how to use REF CURSOR.

Example description
Retrieving REF CURSOR parameters in OracleDataReader
This example executes a pl/sql stored procedure, returns the REF CURSOR parameter, and reads the value as OracleDataReader.

Retrieving data from multiple REF CURSOR using OracleDataReader
This example executes a pl/sql stored procedure, returns two REF CURSOR parameters, and reads the value using OracleDataReader.

Populating a DataSet with one or more REF CURSOR
This example executes a pl/sql stored procedure, returns two REF CURSOR parameters, and populates the DataSet with the returned row.

To use these examples, you may need to create an Oracle table, and you must create PL/SQL packages and package bodies.

To create an Oracle table
These examples use tables defined in the Oracle Scott/tiger schema. Most Oracle installations include Oracle Scott/tiger architectures. If this schema does not exist, you can use the SQL command file in {Oraclehome}rdbmsadminscott.sql to create tables and indexes for use by these samples.

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

The code is as follows

Copy Code

CREATE OR REPLACE PACKAGE body curspkg as 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 for 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 for 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 enables the ability to pass result sets between programs, and can also implement bulk SQL using ref CURSOR to improve SQL performance.

Use the Scott User's EMP table to implement the following test cases:

The code is as follows

Copy Code

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) Obtain result set output using REF CURSOR: 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 for 7 SELECT empno, ename, Job, Mgr, HireDate, Sal, Comm, deptno from EMP; 8 9 FETCH L_REFC BULK COLLECT into L_data; 10 One close L_REFC; 12 The I in 1. L_data. COUNT LOOP Dbms_output.put_line (L_data (i) ename 16 | | ' was hired since ' 17 | | L_data (i) hiredate 18); End LOOP; 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-