Oracle explicit cursor usage

In Oracle, cursor is the most commonly used and most effective method to operate data.
Oracle cursors can be divided into explicit and implicit Based on the declared methods of Oracle cursors.
Explicit cursors are explicitly declared cursors. The operations of explicit cursors include the following:
 
First, the declaration of the cursor:
When declaring a cursor, use the keyword cursor,
For example, cursor cur_sample is select * From tab;
Here, cursor, is the keyword, cur_sample is the cursor name, And the SELECT statement following it is the query statement associated with the cursor.

Second, parameter transmission:
When declaring a cursor, you can not only use the above method:
Cursor cur_sample is select * From tab;
You can also pass parameters to the cursor,
Cursor cur_sample (par_tablename varchar2 (24) is select * From tab where tname = par_tablename;
The parameters passed to the cursor can be multiple or different types, but they can only be passed in and cannot be passed out.
You can also set the default value when specifying parameters.
For example, cursor cur_sample (par_tablename varchar2 (24) default 'ployees') is select * From tab where tname = par_tablename;

Third, declare the cursor variable
Declare a cursor using the following method:
Cursor cur_sample is select * From tab;
When using a cursor, the cursor variable or other variables must be declared to store the data retrieved from the cursor:
There are two types of cursor variables:

(1) variables defining the cursor type:
C_smaple cur_sample % rowtype;
(2)
Declare a variable list:
M_tname varchar2 (30 );
M_tabtype varchar2 (7 );
M_clusterid number (5 );

Fourth, open the cursor
Before operating on data, open the cursor:
Declare a cursor first:
Cursor cur_sample is select * From tab;
Then open:
Open cur_sample;
When you open a cursor with parameters, you must also pass the parameters:
For example, the cursor declaration is as follows:
Cursor cur_sample (par_tablename varchar2 (24) default 'ployees') is select * From tab where tname = par_tablename;
The method is as follows:
Open cur_sample ('ployees ');
However, if the parameter has a default value and you want to use the default value, you can directly:
Open cur_sample;
In this case, the par_tablename value is equal to 'ployees', which is equivalent
Open cur_sample ('ployees ');

However, if you open an opened cursor, an error is returned:
ORA-06511: PL/SQL: cursor already open;

When a cursor is opened, the cursor automatically points to the first record of the active set.

Fifth, read cursor data:
Before reading data, you must open the cursor first:
If you want to pass a parameter, open it with the parameter. If you do not need the parameter, open it directly.

Reading data means reading data into variables using the fetch method.
For a variable of the cursor type, you can use the following method: (for the definition of the cursor parameter, see Third)
Fetch cur_sample into c_sample;
Yes.
Fetch cur_smale into m_tname, m_tabtype, m_clusterid;
When using the Variable list, the sequence of the Variable list must be the same as that of the fields in the cursor activity result set.

If you use a variable of the cursor type, use the following method to access the value:
C_smaple.tname, c_sample.tabtype, c_sample.clusterid;
You can directly use the variable name to use the Variable list method.

Execute fetch once, And the cursor pointer automatically moves to the next record.

Sixth, close the cursor:
Closing a cursor is simplest and straightforward.
Close the cursor name.
For example, for a cursor:
Cursor cur_sample is select * From tab;
You can:
Close cur_sample;
Whether the cursor has parameters. The closing method is the same.

Using a cursor can greatly facilitate the compilation of PL/SQL, but the cursor is also quite special. For example, the cursor data can only be read using fetch, and the movement of the cursor pointer automatically moves to the next one when fetch data, when a cursor is opened, the cursor pointer automatically points to the first record. In addition, the cursor is opened in a static way, that is, after the cursor is opened, if the related table executes DML (delete, insert, update) operation. The operation result cannot be reflected in the cursor. You can only close the cursor first and then open the cursor to update the data of the cursor activity set.