Reference
I. EXPLICIT cursor
Explicit is relative to the implicit cursor, which is the cursor with a clear declaration. The declaration of an explicit cursor is similar to the following (the detailed syntax participates in 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 static cursor, her scope is global, but also must understand, static cursor also only PL/SQL code can use her. Here 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 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 the current system of the number of the short corresponding to the available mobile phone number. Of course, no one in the actual application, I just used to say should be an explicit cursor usage.
Reference two, implicit cursor
The implicit cursor is of course relative to the explicit, that is, the declare of the cursor is not clear. 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.
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>
Example of an implicit cursor two:
Begin
For rec in (select Gsmno,status from Gsm_resource) loop
Dbms_output.put_line (rec.gsmno| | ' --' | | Rec.status);
End Loop;
End
/
Reference Three, Refcursor
REF cursor belongs to dynamic cursor (this query is not known until run time).
Technically speaking, static cursor and REF CURSOR are the same at the most basic level. A typical PL/SQL cursor is static by definition. The REF cursor is the opposite, can be opened dynamically, or with 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). 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: The cursor C is always select * from dual no matter how many times the code block is run. 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 (limited) REF CURSOR is shown below, and this type of REF CURSOR is used more in the actual 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
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 and REF CURSOR There are some differences that everyone should be familiar with, and I'll waste a bit of spit.
1) PL/SQL static cursor cannot be returned to the client, only PL/SQL can take advantage of 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 a static cursor, it must be defined as the global cursor in the package description or package body. Because using global variables is often not a good coding habit, you can 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, and the use of the REF CURSOR is limited to the following situations:
Return the result set to the client;
Share the cursor between multiple subroutines (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, first consider using static SQL, only use the REF CURSOR when you absolutely must use the REF CURSOR, or we recommend using an implicit cursor as much as possible, avoiding the need to write additional cursor control code (declaring, opening, fetching, closing), or declaring 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 usage of no_data_found and%notfound is different, the summary is as follows:
1) SELECT ... INTO statement trigger 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, do not use No_data_found
Citation five, Sys_refcursor
Sys_refcursor is a system-defined refcursor after oracle9i, mainly used to return the result set in the process.
Cases:
Sql> Conn Scott/[email protected]
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,
' Error in getempbydept ' | | SQLCODE);
End Getempbydept;
13/
A procedure has been established.
sql> var rset refcursor;
sql> exec getempbydept (10,:rset);
The PL/SQL procedure is completed smoothly.
sql> print RSet;
The difference between cursor and refcursor and Sys_refcursor (reproduced)
