Usage of display cursor

DisplayThe cursor is a select clause clearly defined in the Declaration unit, and a name is specified at the same time.
Use this name to reference the cursor attribute canonicalizedly, and use the name to reference and display the cursor in the open, fetch, and close statements.
Insert, update, delete, or merge do not display the cursor.

(I) declared display cursor

Syntax:
Cursor cursor_name [([parameter [, parameter...])] [Return return_specification]
Is
Select_statement
We recommend that you put the cursor in the package for definition.
This is a declaration. It is used everywhere.
In the future, it will become easier to maintain this query and minimize the number of resolutions to the query statement, which can also benefit from performance.
But pay attention to the fact that, at the packet level, the lifecycle of a cursor is sustained throughout the session.
This also means that a package-level cursor will remain open until we show that the cursor is closed or the session is killed.
The optional parameters and return parameters are described in detail below.

(1) Why should I use the return statement?
First, using the return statement is actually equivalent to publicly declaring the data structure returned by each fetch operation.
That is, the records returned by the cursor, the returned order, the columns included, and other information are all in the dark.
This leads to a clever place, that is, we can separate the cursor header and the cursor body, for example:
Package emp_info
Is
Cursor emp_cur (name_in in EMP. name % Type) return EMP % rowtype;
End;

Package body emp_info
Is
Cursor emp_cur (name_in in EMP. name % Type) return EMP % rowtype
Is
Select * from EMP where name like name_in;
End;
Apparently, the package body cursor is actually a Black Box
This brings two benefits:
-- Hiding information makes it mysterious to implement the package body.
-- Minimize re-compilation. We can modify the implementation of the packet body cursor as needed without affecting the packet header's cursor specification.
This also means that all programs dependent on this package will not be set to invalid state, and naturally do not need to be re-compiled.
The Return Statement of the cursor can be composed of any of the following data types:
▲Table_name % rowtype: record type defined based on a database table
▲Cursor_name % rowtype: The record type defined based on a defined cursor
▲Record_type % rowtype: The record type customized by the programmer
Note that the return records of the select list and return are as follows:
-- The number of columns must match
-- Record Data Types must also be matched

(2) When do we need to parameterize our cursor?
If we want to use a cursor in multiple places, each time the WHERE clause value is different, we can create a cursor with a parameter, for example:
Declare
Cursor cursor_name (par_in in varchar2)
Is
Select emp_id, emp_name from EMPs
Where emp_name = upper (par_in );
The most common parameter used in a cursor is the WHERE clause. However, parameters can also be used anywhere in a select statement, for example:
Declare
Cursor cursor_name (par_in in varchar2)
Is
Select emp_name, par_in, job from EMPs
Where emp_name = upper (par_in );
The scope of the cursor parameter is limited by the cursor. We cannot reference the cursor parameter beyond the SELECT statement associated with the cursor. For example, if the cursor is referenced at the execution unit, it cannot be compiled
The cursor parameter can only be an in parameter. The cursor cannot pass out the value through the parameter list.
There are two advantages of cursor parameterization:
① Avoid where filtering conditions being hard-coded to make the cursor more reusable.
② To avoid the scope of the cursor, we can define the cursor in the outer block, and then call the cursor with local variables in the inner Block.

(Ii) Open the display cursor

Syntax:
Open cursor_name [(parameter [, parameter...])];
Think believes that the most important thing here is to pay attention to the read consistency of oracle.
When we open a cursor, PL/SQL will execute the query statement for this cursor and identify the active dataset-records that match the where Filter
However, open does not actually extract any row of data, which is completed by fetch.
However, no matter when we start fetch data for the first time, Oracle will ensure that all fetch reflects the data status at the moment when the cursor is opened.
That is, from the moment the cursor is opened until the cursor is closed, the data obtained through the cursor will be automatically ignored after the cursor is opened
Insert, update, delete, and other operations performed by other active sessions
If we use the for update clause, all active datasets will be locked when the cursor is opened.
This is the read consistency of oracle.
Oracle uses SCN to implement this theory. when starting the query, a select SCN will be determined, which ensures that all the SCN values in the transaction slot are smaller than the select SCN values.

(3) extract data from the display cursor

In a PL/SQL program, a cursor can be simply understood as a table.
Row: determined by the WHERE clause. If no clause exists, all rows are returned.
Column: select list
Syntax:
Fetch cursor_name into record_or_variable_list;
Here, think suggests that we always retrieve the data to the records defined by % rowtype of the cursor, for example:
Declare
Cursor cur is select * from ...;
Think_cur cur % rowtype;
Begin
Open cur;
Fetch cur into think_cur
Check whether the fetch operation is successful. Use the % found or % notfound attribute value of the cursor instead of the variable value in.
The reason is simple: when all the active datasets are fetch complete, we can still fetch down without throwing an exception.

(Iv) display the column alias in the cursor

There are two areas that need to involve column aliases
● Extract data to the record declared with the % rowtype attribute of the cursor
● We want to use this column in the program
For example:
Declare
Cursor cur is select sum (salary) total_sum from ...;
Think_cur cur % rowtype;
Begin
Open cur;
Fetch cur into think_cur;
Or
If think_cur.total_sum then...


(V) Close the display cursor

After a cursor is used up, make sure that the cursor is closed.
Syntax:
Close cursor_name;
Once we use a package-level cursor, we need to keep up with a close statement immediately. This is especially important!
For example:
Begin
Open my_package.cursor_name;
...
...
Close my_package.cursor_name;
Exception
When others
Then
If my_package.cursor_name % isopen
Then
Close my_package.cursor_name;
End if;
End;
We can only close the currently open cursor. You can use % isopen to determine whether:
If cursor_name % isopen
Then
Close cursor_name;
End if;
If the number of opened cursors exceeds the number set at the session level (open_cussors), the following throw will be thrown:
ORA-01000: maximum open cursors exceeded
If this exception occurs, think considers that the usage of the packet-level cursor should be located first.