Explicit cursor, implicit cursor, and dynamic ref cursor in PLSQL

Today, we will briefly summarize the usage of cursor in PL/SQL.

Cursor category:

-- Explicit cursor
-- Static cursor |

| -- Implicit cursor
Cursor |
-- Strong type (Restriction), specifying the return type
-- Dynamic Cursor -- ref cursor |
-- Weak type (not limited). No return type is specified. Any result set can be obtained.

1. Explicit cursor

Explicit is relative to implicit cursor, that is, there is a clear declared cursor. The declaration of an explicit cursor is similar to the following:

Cursor cursor_name (parameter list) is select...

The cursor from declare, open, fetch, close is a complete life journey. Of course, such a cursor can be used for multiple open operations. The explicit cursor is a static cursor, and its scope is global, but it must also be understood, static cursor can be used only by PL/SQL code. The following is an example of a simple static explicit cursor:

Declare
Cursor get_gsmno_cur (p_nettype in varchar2) is
Select gsmno from gsm_resource where nettype = p_nettype and status = '0 ';
V_gsmno number;
Begin
Open get_gsmno_cur ('20140901 ');
Loop
Fetch get_gsmno_cur into v_gsmno;
Exit when get_gsmno_cur % notfound;
Dbms_output.put_line (v_gsmno );
End loop;
Close get_gsmno_cur;

Open get_gsmno_cur ('20140901 ');
Loop
Fetch get_gsmno_cur into v_gsmno;
Exit when get_gsmno_cur % notfound;
Dbms_output.put_line (v_gsmno );
End loop;
Close get_gsmno_cur;
End;
/

The above anonymous block is used to implement the number selection function. We explicitly define a get_gsmno_cur, and then output the available mobile phone numbers corresponding to the short number in the current system according to different number segments. Of course, there is no such use in practical applications. I just want to use an explicit cursor usage.

Ii. Implicit cursor

Implicit cursor is of course relative to explicit, that is, there is no clear declare of cursor. In Oracle's PL/SQL, all DML operations are parsed into an implicit cursor named SQL in Oracle, which is transparent to us.

In addition, the pointer for loop in some loop operations we mentioned above are all implicit cursor.

Implicit cursor Example 1:

Create Table zrp (STR varchar2 (10 ));
Insert into zrp values ('abcdefg ');
Insert into zrp values ('abcxefg ');
Insert into zrp values ('abcyefg ');
Insert into zrp values ('abcdefg ');
Insert into zrp values ('abczefg ');
Commit;

Begin
Update zrp set STR = 'updated' where STR like '% d % ';
Ifsql % rowcount = 0 then
Insert into zrp values ('20140901 ');
End if;
End;
/

PL/SQL procedure successfully completed

SQL> select * From zrp;

Str
----------
Updated
Abcxefg
Abcyefg
Updated
Abczefg


Begin
Update zrp set STR = 'updated' where STR like '% S % ';
Ifsql % rowcount = 0 then
Insert into zrp values ('20140901 ');
End if;
End;
/

PL/SQL procedure successfully completed

SQL> select * From zrp;

Str
----------
Updated
Abcxefg
Abcyefg
Updated
Abczefg
0000000
6 rows selected

SQL>

Implicit cursor Example 2:

Begin
For REC in (select gsmno, status from gsm_resource)
Loop
Dbms_output.put_line (Rec. gsmno | '--' | Rec. status );
End loop;
End;
/

Iii. refcursor

Ref cursor is a dynamic cursor (this query is not known until running ).

Technically, static cursor and ref cursor at the most basic level are the same. A typical PL/SQL cursor is static by definition. The ref cursor is the opposite. It can be opened dynamically or with a set of SQL static statements, the method is determined by logic (one if/then/else code block opens one or other queries ). For example, the following code block shows a typical static SQL cursor, cursor C. In addition, it shows how to open a query by using dynamic or static SQL with the ref cursor (l_cursor in this example:

Declare
Type RC is ref cursor;
Cursor C is select * from dual;

Rochelle cursor RC;
Begin
If (to_char (sysdate, 'dd') = 30) then
-- Ref cursor with dynamic SQL
Open l_cursor for 'select * From emp ';
Elsif (to_char (sysdate, 'dd') = 29) then
-- Ref cursor with static SQL
Open l_cursor for select * from Dept;
Else
-- With ref cursor with static SQL
Open l_cursor for select * from dual;
End if;
-- The "normal" static cursor
Open C;
End;
/

In this Code block, you can see the most obvious difference: no matter how many times the code block is run, the cursor C is always select * from dual. Instead, the ref cursor can be any result set, because the "select * from EMP" string can be replaced by a variable that actually contains any query.

In the above Code, a weak type of ref cursor is declared. Next, let's look at a strong (restricted) ref cursor, this type of ref cursor is also used in practical application systems.

Create Table gsm_resource
(
Gsmno varchar2 (11 ),
Status varchar2 (1 ),
Price number (8, 2 ),
Store_id varchar2 (32)
);
Insert into gsm_resource values ('20170', '0', 13905310001, 'sd. jn.01 ');
Insert into gsm_resource values ('20170', '0', 13905312002, 'sd. jn.02 ');
Insert into gsm_resource values ('20170101', '1', 13905315005, 'sd. jn.01 ');
Insert into gsm_resource values ('20170', '0', 13905316006, 'sd. jn.03 ');
Commit;

Set serveroutput on
Declare
Type gsm_rec is record (
Gsmno varchar2 (11 ),
Status varchar2 (1 ),
Price number (8, 2 ));
My_rec gsm_rec;

Type app_ref_cur_type is ref cursor/* return gsm_rec can be added or not added, without affecting the execution result */;
My_cur app_ref_cur_type;

Begin
Open my_cur for select gsmno, status, price from gsm_resource where store_id = 'sd. jn.01 ';
Fetch my_cur into my_rec;
While my_cur % found Loop
Dbms_output.put_line (my_rec.gsmno | '#' | my_rec.status | '#' | my_rec.price );
Fetch my_cur into my_rec;
End loop;
Close my_cur;
End;
/

13905310001 #0 #200
13905315005 #1 #500

PL/SQL procedure successfully completed

Static cursor and ref cursor have the following differences:

1) PL/SQL static cursor cannot be returned to the client. Only PL/SQL can use it. The ref cursor can be returned to the client, which is the way to return the result set from the Oracle stored procedure.

2) the PL/SQL static cursor can be global, while the ref cursor is not. That is to say, the ref cursor cannot be defined outside the procedure or function in the package description or package body. It can be processed only when the ref cursor is defined, or returned to the client application.

3) The ref cursor can be transferred from the subroutine to the subroutine, but the cursor cannot. To share the static cursor, you must define it as a global cursor in the package description or package body. Because the use of global variables is not a good coding habit, you can use the ref cursor to share the cursor in PL/SQL without the need to mix global variables.

4) using a static cursor-using a static SQL statement (but without a ref cursor)-is more efficient than using a ref cursor, while using a ref cursor is limited to the following situations: return the result set to the client; share the cursor among multiple child routines; when there is no other effective way to achieve your goal, use the ref cursor, as when dynamic SQL is required;

Note: To use static SQL statements, you must use the ref cursor only when the ref cursor is absolutely required. It is also recommended that you use an implicit cursor whenever possible to avoid writing additional cursor control code (Declaration, open, to save the data obtained from the cursor.

Iv. cursor attributes

% Found: bool-true if> 1 row returned
% Notfound: bool-true if 0 rows returned
% Isopen: bool-true If cursor still open
% Rowcount: int-number of rows affected by last SQL statement

Note: The usage of no_data_found and % notfound is different. The following is a summary:
1) The Select... into statement triggers no_data_found;
2) % notfound is triggered when the WHERE clause of an explicit cursor is not found;
3) SQL % notfound is triggered when the WHERE clause of the update or delete statement is not found;
4) % notfound or % found should be used in the fetch loop of the cursor to determine the exit condition of the loop.

* From: **********************************

In Oracle dynamic cursors, a fixed SQL statement is not specified when the cursor variable is defined, and an SQL statement is specified when the SQL statement is opened. Below are some of my practical notes:
[1] different statements in Dynamic Cursor 2
Create or replace procedure pro_set_loop
(
I _id varchar2,
O_result_code out number,
O_result_msg out varchar2
)
As
V_bookname varchar2 (100 );
V_id number;
Type ref_cursor_type is ref cursor;
Cursor_select ref_cursor_type;
Select_cname varchar2 (1000 );
Begin
Select_cname: = 'select bookname from book where id =: 1'; -- 1
Open cursor_select for select_cname using I _id; -- 2
Loop
Fetch cursor_select into v_bookname;
Exit when cursor_select % notfound;
Update book set price = '25' where bookname = v_bookname;
End loop;
Close cursor_select;
End;
Note: The preceding two sentences can also be written as follows:
Select_cname: = 'select bookname from book where id = '| I _id;
Open cursor_select for select_cname;

[2] dynamic cursor returns the result set to the client

Return the result set to the client. This can be achieved through 2. One is the Oracle stored procedure and the other is the Oracle function. Since the Oracle stored procedure does not return values, all of its return values are replaced by the out parameter, and the list is no exception. for the return of a set, common parameters can be used, it must be implemented using pagkage, as is the Oracle function.
 
Package creation:

Create or replace Package Types
As
Type ref_cursor is ref cursor;
End;

Oracle stored procedure:

Create or replace procedure get_book_pro
(
I _id number,
O_bookname out types. ref_cursor
)
As
Begin
Open o_bookname for select * from book where id = I _id;
End get_book_pro;

Oracle functions:

Create or replace function get_book_func
(
I _id in number
)
Return types. ref_cursor
As
O_bookname types. ref_cursor;
Begin
Open o_bookname for select * from book where id = I _id;
Return o_bookname;
End get_book_func;

Test SQL:

Create Table Book
(
ID number,
Bookname varchar2 (100 ),
Price varchar2 (100)
);
Insert into book (1, 'dephi ', '20140901 ');
Insert into book (2, 'C', '20140901 ');
Insert into book (3, 'c ++ ', '123 ');
Insert into book (4, 'java', '123 ');
Insert into book (5, 'c # ', '123 ');
Insert into book (6, 'shell', '123 ');
Insert into book (7, 'vb ', '123 ');
Insert into book (8, 'plsql', '123 ');

PL/SQL procedure successfully completed

SQL> select * from book;
1 1 dephi 100
2 2 C 200
3 3 C ++ 300
4 4 Java 400
5 5 C #500
6 6 shell 600
7. VB 700
8 8 VJ 800
9 9. PLSQL 900

The following code calls an oracle stored procedure or function and returns the result set:

Package J4;
Import java. SQL. callablestatement;
Import java. SQL. connection;
Import java. SQL. drivermanager;
Import java. SQL. resultset;
Import java. SQL. sqlexception;
Import java. SQL. types;

Public class testoracle {
Public static final string url = "JDBC: oracle: thin: @ 10.40.152.186: 1521: zxin ";

Public static final string driver = "oracle. JDBC. Driver. oracledriver ";

Public static final string user_name = "WAP ";

Public static final string Password = "WAP ";

Private Boolean useoraclequery = false;
 
Private string oraclequery_func = "{? = Call get_book_func (?)} "; -- Oracle function declaration, an input parameter and an output parameter.

Private string oraclequery_pro = "{call get_book_pro (?,?) } "; -- Oracle Stored Procedure statement, one input parameter and one output parameter.

Public void findstored_ref (int id ){
Try {
Class. forname (driver );
Connection conn = drivermanager. getconnection (URL, user_name, password );
String query = useoraclequery? Oraclequery_pro: oraclequery_func;
If (useoraclequery ){
// Use of Oracle dynamic cursor in the Stored Procedure
System. Out. println ("-------- ref cursor In Proc --------");
Callablestatement stmt = conn. preparecall (query );
Stmt. setint (1, ID); -- input parameter of the stored procedure
Stmt. registeroutparameter (2, Oracle. JDBC. oracletypes. cursor); -- output parameter of the stored procedure
Stmt.exe cute ();
Resultset rs = (resultset) stmt. GetObject (2); -- note that GetObject (2)
While (Rs. Next ()){
System. Out. Print (Rs. getstring (1) + "");
System. Out. Print (Rs. getstring (2) + "");
System. Out. println (Rs. getstring (3) + "");
}
Stmt. Close ();
Conn. Close ();
}
Else {
// Use of Oracle dynamic cursor in Functions
System. Out. println ("-------- ref cursor in func --------");
Callablestatement stmt = conn. preparecall (query );
Stmt. registeroutparameter (1, Oracle. JDBC. oracletypes. cursor); -- function output parameter
Stmt. setint (2, ID); -- function input parameter
Stmt.exe cute ();
Resultset rs = (resultset) stmt. GetObject (1); -- note that GetObject (1)
While (Rs. Next ()){
System. Out. Print (Rs. getstring (1) + "");
System. Out. Print (Rs. getstring (2) + "");
System. Out. println (Rs. getstring (3 ));
}
Stmt. Close ();
Conn. Close ();
}
} Catch (classnotfoundexception e ){
E. printstacktrace ();
} Catch (sqlexception e ){
E. printstacktrace ();
}
}
 
Public static void main (string [] ARGs ){
Testoracle test = new testoracle ();
Test. findstored_ref (1); -- the real parameter is id = 1
}

}

Result:
When private Boolean useoraclequery = true;, the program prints:

-------- Ref cursor In Proc --------
1 dephi 100

When private Boolean useoraclequery = false;, the program prints:
------ Ref cursor in func --------
1 dephi 100

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/zhujjcn/archive/2009/03/12/3984140.aspx