Usage of Oracle display cursor

The Oracle display cursor is a SELECT clause clearly defined in the Declaration unit, and a name is specified to reference the cursor attribute using this name.

The Oracle display cursor is a SELECT clause clearly defined in the Declaration unit, and a name is specified to reference the cursor attribute using this name.

The Oracle display 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.