Oracle REF Cursors

Oracle Series: REF Cursor

In the Oracle series above: Cursor (see:http://blog.csdn.net/qfs_v/archive/2008/05/06/2404794.aspx)
Mention a thought: how do I get cursors to be passed as parameters? To solve this problem, you need to use the REF Cursor.

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

What is the role of 2,REF cursors?
Implement the ability to pass result sets between programs, and use REF CURSOR to implement bulk SQL, which improves SQL performance.

3, what is the difference between a static cursor and a REF CURSOR?
① static cursors are statically defined and REF cursors are dynamically correlated;
② the REF CURSOR variable is required to use a REF CURSOR.
③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 references a REF cursor type, pointing to a dynamically associated result set.

5, how do I use a REF CURSOR?
① declares a REF cursor type and determines the REF cursor type;
⑴ strongly typed 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 typed REF CURSOR: Does not specify return type, can match any type of cursor variable, and is used to get any result set.
Syntax: Type REF CURSOR name is REF CURSOR;

② declaring a REF CURSOR type variable;
Syntax: The variable name has declared a REF cursor type;

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

④ get record, operation record;
Syntax: Fatch REF CURSOR name into temporary record type variable or attribute type variable list;

⑤ close the cursor and completely release the resources;
Syntax: Close REF CURSOR name;

Example: strongly-typed ref cursors
/*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 cursors
/*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 = ' clerk ';
End case;
Close vrefcur;
End;

6, how can I pass a REF CURSOR as a parameter? --The original is not here, make up your own:

Write the stored procedure, and the Java call returns all employee information for department Number 10:

1) Stored Procedures

[SQL]View Plaincopy

2. CREATE OR REPLACE PROCEDURE findset_emp (V_deptno in number,
4. C_cursor out sys_refcursor) as
6. BEGIN
8. OPEN c_cursor for
10. SELECT *
12. From EMP
14. WHERE deptno = V_deptno;
16. END;

2) Java call

[Java]View Plaincopy

2. Public static void Main (string[] args) {
4. //TODO auto-generated method stub
6. try{
8. //Load Oracle driver
10. Class.forName ("Oracle.jdbc.driver.OracleDriver");
12. Connection ct = drivermanager.getconnection ("JDBC:ORACLE:THIN:@127.0.0.1:1521:ORCL","Scott","Tiger");
16. CallableStatement cs = Ct.preparecall ("{call Findset_emp (?,?)}");
18. Cs.setint (1, 10);
20. Cs.registeroutparameter (2, Oracle.jdbc.OracleTypes.CURSOR);
24. Cs.execute ();
26. ResultSet rs = (ResultSet) cs.getobject (2);
28. While (Rs.next ()) {
30. System.out.println (Rs.getint (1) +"" +rs.getstring (2));
32. }
34. Cs.close ();
36. Ct.close ();
38. }
40. catch (Exception e) {
42. E.printstacktrace ();
44. }

3) The effect is as follows:

-------------------------

Present by Dylan.

Oracle REF Cursors