Differences among cursor, hidden cursor, and dynamic ref cursor displayed in PLSQL

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

I believe many DBAs who are engaged in development or maintenance have encountered similar face-to-face problems when looking for a job: Please briefly describe the type of the cursor, let's talk about the difference between a normal cursor and a ref cursor, and when
Which one should be correctly applied?

I really have a lot of difficulties with this question. In fact, they are still able to grasp the correct usage of the cursor during the specific development, but that is to say, they cannot come out, of course, this is in line with everyone's own traffic capabilities
Is related. Some people are not good at speaking, but they do well. If you are a technical engineer, you can't talk about it, or you can do it without packaging. The road is far away.

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 (for detailed syntax, refer to PLSQL ref DOC ):

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
It is a static cursor, and her scope is global, but you must also understand that static cursor can be used only by PL/SQL code. The following describes a simple static explicit cursor
Example:

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 ('20140901 ');
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 ('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
No one is using this in actual applications. I just want to use an explicit cursor.

Ii. Implicit cursor

Implicit cursor is of course relative to explicit, that is, there is no clear declare of cursor. In Oracle PL/SQL, all DML operations are
Oracle parses it into an implicit cursor named SQL by cursor, 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;

SQL> begin
2 update zrp set STR = 'updated' where STR like
'% D % ';
3 ifsql % rowcount = 0 then
4 insert into zrp values ('20140901 ');
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 ('20140901 ');
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 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 speaking, static cursor and ref at the most basic level
Cursor is 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.
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
How to use dynamic or static SQL to open a query 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, we can see the most obvious difference: no matter how many times the code block is run, the cursor C always selects * 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.
Cursor is also used in many 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 ('20140901', '1', 13905315005, 'sd. jn.01 ');
Insert into gsm_resource
Values ('20170', '0', 13905316006, '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
11 begin
12 open my_cur for select gsmno, status, price
13 from gsm_resource
14 where store_id = 'sd. jn.01 ';
15 fetch my_cur into my_rec;
16 while my_cur % found Loop
17
Dbms_output.put_line (my_rec.gsmno | '#' | my_rec.status | '#' | my_rec.price );
18 fetch my_cur into my_rec;
19 end loop;
20 close my_cur;
21 end;
22/

13905310001 #0 #200
13905315005 #1 #500

PL/SQL procedure successfully completed

SQL>

There are also some differences between a common cursor and a ref cursor that everyone should be familiar with, so I will waste a bit of time.

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 side of the result set returned 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.

Finally, using a static cursor-through 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 (actually very similar to the one mentioned above );
When there are no other effective methods to achieve your goal, use the ref cursor, just as when dynamic SQL is required;

In short, you should first consider using static SQL. The ref cursor is used only when the ref cursor is absolutely required. Some people suggest using implicit
Cursor, avoid writing additional cursor control code (Declaration, open, get, and close), and do not need to declare variables to save the data obtained from the cursor. This is because we have to decide the specific case.

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) Use % notfound or % found in the fetch loop of the cursor to determine the exit condition of the loop. Do not use no_data_found.

For more information, see the Oracle PL/SQL development manual.