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, one of these cursors can be used by multiple open. The explicit cursor is a static cursor, and her scope is global, but it must also be understood that static cursor also has only 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 function of number selection, we explicitly define a get_gsmno_cur. Then, according to the different number segment output 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, the pointer for loop in some of the loop operations we mentioned earlier 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 exactly the opposite and can be opened dynamically. Or using a set of SQL static statements to open, choose which method is determined by the logic (a IF/THEN/ELSE code block will open one or another query). Like what. The following code block shows a typical static SQL cursor, cursor c. In addition It also 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. Can see the most obvious difference: no matter how many times the code block is executed, the 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, and the following is a strongly typed (restricted) REF CURSOR. This type of REF CURSOR is also used in a real-world application system.

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
???? ????????? 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>

Common cursor with REF CURSOR Other people should be familiar with the difference. I'll waste some more 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, which is how the result set is returned from the Oracle stored procedure.

2) the PL/SQL static cursor can be global, while the REF CURSOR is 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 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. You can therefore use the REF CURSOR to share the cursors in PL/SQL. There is no need to mix global variables.

Last. 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:

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

In short, consider using static SQL first, only if you absolutely must use the REF CURSOR when using the REF CURSOR, and others recommend using implicit cursors as much as possible, avoiding the need to write additional cursor control code (declarations). Open, get, close). There is no need to declare a variable to hold the data obtained from the cursor. This is because of the specific case of the people to the discretion.

Iv. 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, summary such as the following:
1) SELECT ... The INTO statement triggers No_data_found.
2) trigger%notfound when the WHERE clause of an explicit cursor is not found;
3) trigger 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