Plsql display cursor, hidden cursor, dynamic REF CURSOR Difference

I. EXPLICIT cursor

Explicit is relative to the implicit cursor, that is, there is a clear declaration of the cursor. The declaration of an explicit cursor is similar to the following (specific syntax for Plsql ref doc):

Cursor cursor_name (parameter list) is select ...

Cursors from declare, open, fetch, and close are a complete journey of life.

Of course, a cursor of this kind can be used several times open, the explicit cursor is a static cursor, her scope is global, but also must be clear, static cursor also only have PL/SQL code talent enough to use her. Here is a sample demonstration 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 according to the different number segment output of the current system of the number of the corresponding available mobile phone number. Of course, no one in the actual application, I just used to say that should be an explicit cursor use method.

Two, the implicit cursor

The implicit cursor is, of course, relative to the explicit, declare of the cursor that is not understood.

In Oracle PL/SQL, all DML operations are parsed internally by Oracle into a cursor named SQL implicit cursor, just transparent to us.

In addition, we mentioned some of the loop operations in the pointer for loop. is an implicit cursor.

An implicit cursor Demo sample 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 Demo sample 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 dynamic cursor (this query is not known until execution).

Technically speaking. At the most primary level static cursor and REF CURSOR are the same.

A typical PL/SQL cursor is static by definition.

The REF cursor is just the opposite, capable of opening dynamically, or using a set of SQL static statements to open, choosing which method is logically determined (a IF/THEN/ELSE code block will open one or another query). For example, the following code block shows a typical static SQL cursor, cursor c.

In addition, it shows how to open a query with the REF CURSOR (in this case, l_cursor) by using dynamic SQL or static SQL:

Declare
Type RC is REF CURSOR;
Cursor C is a 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 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 executes. Cursor C is always select * from dual. Instead, the REF cursor can be whatever result set. Because the "SELECT * from emp" string can be replaced with a variable that actually includes whatever query it is.

In the preceding code, a weakly typed REF CURSOR is declared. The following looks at a strongly typed (limited) REF cursor, such that the type of REF CURSOR is used in the actual application system is also much 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
From Gsm_resource
The 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;
The end;
22/

13905310001#0#200
13905315005#1#500

PL/SQL procedure successfully completed

Sql>

Normal cursor with REF CURSOR Other people should be familiar with the difference, I waste a bit of saliva.

1) PL/SQL static cursor cannot be returned to the client. Only PL/SQL talent could use it. The REF cursor can be returned to the client. This is how the result set is returned from the Oracle stored procedure.

2) PL/SQL static cursor can be global, while REF CURSOR is not. Other words. You cannot define a REF cursor outside a procedure or function in a package description or package body.

It can only be processed during the process of defining 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 does not. In order to share the static cursor. It must be defined as a global cursor in the package description or in the package body. Because using global variables is often not a very good coding habit. It is therefore possible to use the REF CURSOR to share cursors in PL/SQL without mixing global variables.

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

Returns the result set to the client, and
shares the cursor between multiple subroutines (which is actually similar to the one mentioned above);
There is no other effective way to reach your goal, use the REF CURSOR, Just as it must be with dynamic SQL;

In short, consider using static SQL first, using the REF CURSOR only if you absolutely have to use the REF CURSOR, or we recommend using an implicit cursor whenever possible. Avoid writing additional cursor control code (Declaration, open, get, close). There is no need to declare a variable to hold the data obtained from the cursor. This is because of the detailed case of the people to the discretion.

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: There are differences in how no_data_found and%notfound are used. Summary example of the following:
1) SELECT ... The INTO statement triggers No_data_found.
2) Fires%notfound when a WHERE clause of an explicit cursor 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, use%notfound or%found to determine the exit condition of the loop, and do not use No_data_found.

Plsql display cursor, hidden cursor, dynamic REF CURSOR difference