The difference between cursor and refcursor and sys_refcursor

Today's simple summary of the use of cursor (cursor/cursor) in Pl/sql.

I believe that many DBAs who do development or maintenance have encountered similar problems when looking for a job: simply describe the type of cursor, the difference between a normal cursor and a REF cursor, and when to apply it correctly.

This topic, I really puzzled a lot of people, in fact, they are in the specific development of the time, it is still more able to grasp the correct use of the cursor, but that is not to say, of course, this and everyone's own channel flow traffic capacity is related. Some people are not good at speaking, but they are doing well. Pull the pull away, finally nagging: do technology this road, can not say, or will do not packaging, road is not "far".


One, explicit type cursor

Explicit is relative to the implicit cursor, there is a clear statement of the cursor. The declaration of an explicit cursor resembles the following (verbose syntax participates in Plsql ref doc):

Cursor cursor_name (parameter list) is select ...

Cursors are a complete life journey from declare, open, fetch, close. Of course, one such cursor can be used multiple times open, explicit cursor is static cursor, her scope is global, but also must understand that static cursor only Pl/sql code can use her. Here's 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 Gsm_resource.gsmno%type;
Begin
Open Get_gsmno_cur (' 139 ');
Loop
Fetch get_gsmno_cur into V_gsmno;
Exit when Get_gsmno_cur%notfound;
Dbms_output.put_line (V_GSMNO);
End Loop;
Close emp_cur;

Open Get_gsmno_cur (' 138 ');
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 selection function, we explicitly define a get_gsmno_cur, and then output the current system according to the different number of the number of the short corresponding available mobile phone number. Of course, no one in practical use, I just used to say that should be an explicit cursor usage.

Second, implicit type cursor

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

In addition, the pointers for loops in some of the cyclic operations we mentioned earlier are implicit cursor.

Implicit cursor Example one:

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;

Sql> begin
2 Update zrp SET str = ' UpdateD ' where str like '%d% ';
3 Ifsql%rowcount= 0 Then
4 INSERT into ZRP values (' 1111111 ');
5 End If;
6 end;
7/

Pl/sql procedure successfully completed

Sql> select * from ZRP;

Str
----------
UpdateD
Abcxefg
Abcyefg
UpdateD
Abczefg

Sql>
Sql> begin
2 Update zrp SET str = ' UpdateD ' where str like '%s% ';
3 ifsql%rowcount= 0 THEN
4 INSERT into ZRP values (' 0000000 ');
5 End If;
6 end;
7/

Pl/sql procedure successfully completed

Sql> select * from ZRP;

Str
----------
UpdateD
Abcxefg
Abcyefg
UpdateD
Abczefg
0000000
6 Rows selected

Sql>

Implicit cursor Example two:

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

Third, Refcursor

REF cursor belongs to the dynamic cursor (this query is not known until run time).

Technically, at the most basic level static cursor and REF CURSOR are the same. A typical pl/sql cursor is static by definition. The REF cursor, on the contrary, can be opened dynamically, or with a set of SQL static statements to open, and choose which method is determined by logic (a IF/THEN/ELSE code block opens one or more queries). For example, the following code block shows a typical static SQL cursor, the cursor c. It also shows how to open a query with a REF CURSOR (l_cursor in this case) by using dynamic SQL or static SQL:

Declare
Type RC is REF CURSOR;
Cursor C is select * from dual;

L_cursor RC;
Begin
if (To_char (sysdate, ' dd ') = Then
--REF CURSOR with dynamic SQL
Open l_cursor for ' select * from EMP ';
elsif (To_char (sysdate, ' dd ') =) Then
--REF CURSOR with static SQL
Open L_cursor for SELECT * FROM dept;
Else
--with a REF cursor with static SQL
Open L_cursor for SELECT * from dual;
End If;
--the "normal" static cursor
Open C;
End
/

In this block of code, you can see the most obvious difference: no matter how many times the code block is run, 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 with a variable that actually contains any query.

In the above code, a weakly typed REF cursor is declared, and a strongly typed (restricted) REF CURSOR is shown below, and this type of REF CURSOR is also used in the actual application system more.

CREATE TABLE Gsm_resource
(
Gsmno VARCHAR2 (11),
Status Varchar2 (1),
Price number (8,2),
store_id VARCHAR2 (32)
);
INSERT into Gsm_resource values (' 13905310001 ', ' 0 ', 200.00, ' SD. jn.01 ');
INSERT into Gsm_resource values (' 13905312002 ', ' 0 ', 800.00, ' SD. jn.02 ');
INSERT into Gsm_resource values (' 13905315005 ', ' 1 ', 500.00, ' SD. jn.01 ');
INSERT into Gsm_resource values (' 13905316006 ', ' 0 ', 900.00, ' SD. jn.03 ');
Commit

Sql> Declare
2 type Gsm_rec is record (
3 Gsmno varchar2 (11),
4 status Varchar2 (1),
5 Price Number (8,2));
6
7 type App_ref_cur_type is REF CURSOR return GSM_REC;
8 My_cur App_ref_cur_type;
9 My_rec Gsm_rec;
10
Begin
Open My_cur for Select Gsmno,status,price
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;
22/

13905310001#0#200
13905315005#1#500

Pl/sql procedure successfully completed

Sql>

Common cursor and REF CURSOR There are some differences that we should all be familiar with, and I'll waste some saliva.

1 pl/sql static cursor can not be returned to the client, only pl/sql can use it. The REF cursor can be returned to the client, which is how the result set is returned from the Oracle stored procedure.

2 Pl/sql static cursors can be global, while ref cursors are not. That is, you cannot define a REF cursor outside of a procedure or function in a package description or package body. It can only be handled during the definition of the REF cursor, or returned to the client application.

3 The REF CURSOR can be passed from the subroutine to the subroutine, while the cursor cannot. In order to share a static cursor, it must be defined as a global cursor in the package description or in the package body. Because using global variables is usually not a good coding habit, you can use a REF CURSOR to share the cursor in the pl/sql without mixing the global variables.

Finally, using a static cursor--through static SQL (but not a REF CURSOR)--is more efficient than using a REF cursor, and the use of a REF CURSOR is limited to the following:

Returning the result set to the client;
Share the cursor between multiple subroutines (actually very similar to the one mentioned above);
When there is no other effective way to achieve your goal, use a REF cursor, just as you would have to use dynamic SQL;

In short, the first consideration is to use static SQL, which uses a REF CURSOR only when it is absolutely necessary to use a REF cursor, as well as using implicit cursors to avoid writing additional cursor control codes (declaring, opening, fetching, closing), or declaring variables to hold data obtained from the cursor. This is because of the specific case of people to the discretion bar.

Four, cursor properties

%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 use of No_data_found and%notfound is different, summarized as follows:
1) SELECT ... INTO statement triggers no_data_found;
2 triggers the%notfound when an explicit cursor's WHERE clause is not found;
3 triggers sql%notfound when the WHERE clause of the UPDATE or DELETE statement is not found;
4 in the cursor extraction (FETCH) loop to use%notfound or%found to determine the loop exit conditions, do not use No_data_found.

Five, Sys_refcursor

Sys_refcursor is a refcursor that is defined later in the oracle9i, primarily in the process of returning result sets.

Cases:

Sql> Conn Scott/tiger@vongates
Connected.
sql> Create or Replace procedure getempbydept (In_deptno in Emp.deptno%type,
2 out_curemp out sys_refcursor) as
3
4 begin
5 Open Out_curemp for
6 SELECT * from emp WHERE deptno = In_deptno;
7 EXCEPTION
8 when others THEN
9 Raise_application_error (-20101,
Ten ' Error in getempbydept ' | | SQLCODE);
End Getempbydept;
13/

The program has been established.

sql> var rset refcursor;
sql> exec getempbydept (10,:rset);

The Pl/sql procedure completes smoothly.

sql> print RSet;