PL/SQL Programming _ Cursors

A cursor is a private workspace that holds the results of the execution of an SQL statement.
When executing an SQL statement, the database server opens a workspace where the results of the SQL statement are saved.
There are two forms of cursors in the Oracle database: implicit and explicit cursors.
Implicit cursors are defined by the database server, and explicit cursors are defined by the user as needed.

An implicit cursor
An implicit cursor is a type of cursor defined by the database server.
When executing a DML statement or SELECT statement, the database server automatically opens an implicit cursor that holds the execution result of the statement.
There may be multiple DML or SELECT statements in a PL/SQL block, and an implicit cursor always holds the result of the execution of the most recent statement.
Implicit cursors have several useful properties that can help us understand cursor information.
Several common properties for implicit cursors are listed below.
Sql%isopen determines whether the current cursor is open. True if the property value is turned on, otherwise false
Sql%rowcount for DML statements, the property value is the number of data rows affected. For a SELECT statement, if no exception occurs, its value is 1
Sql%found for DML statements, this property indicates whether any data in the table is affected. If the DML statement does not affect any data, the property value is false, otherwise true. For a SELECT statement, the value is true if no exception occurs
Sql%notfound is just the opposite of attribute Sql%found
Where the Sql%found property value is a Boolean value that indicates whether the data that satisfies the condition is found, if the corresponding data is found, the value is true, otherwise false.
The Sql%rowcount property represents the number of data rows affected by an operation, the number of rows modified for an UPDATE statement, and for an INSERT statement, the number of rows inserted, and for a DELETE statement, the number of rows that were deleted.
For example, you can output the number of rows affected by the operation after each DML statement.
BEGIN
UPDATE EMP Set sal=sal+100;
If Sql%found Then
Dbms_output.put_line (' Number of rows modified: ' | | Sql%rowcount);
END if;
DELETE from EMP;
If Sql%found Then
Dbms_output.put_line (' Number of rows deleted: ' | | Sql%rowcount);
END if;
INSERT into Dept VALUES (+, ' aaa ', ' aaaa ');
INSERT into Dept VALUES (+, ' BBB ', ' bbbb ');
If Sql%found Then
Dbms_output.put_line (' recently inserted rows: ' | | Sql%rowcount);
END if;
ROLLBACK;
END;
As you can see from this example, when there are multiple DML statements, the implicit cursor only records the execution of the most recent DML statement.
If it is a SELECT statement, the case is more special.
Because a No_data_found exception is thrown when the SELECT statement does not retrieve data that satisfies the criteria, the Too_many rows exception is thrown when data that satisfies the condition is retrieved for multiple rows.
Therefore, these properties of an implicit cursor can be used only if the SELECT statement is retrieving exactly one row of data.
If you want to handle both of these special cases, you need to use an explicit cursor.

An explicit cursor
For a SELECT statement in a PL/SQL block, you can use an explicit label to handle it.
An explicit cursor is an open workspace,
The execution result of the SELECT statement is saved in this workspace.
An explicit cursor can handle various situations that return 0 rows, one row, multiple rows, and, in the special case of returning 0 rows or more rows of data, no no_data_found and Too_many rows exceptions are thrown.
The steps for using a cursor to process a SELECT statement are as follows:
1) Declaring cursors
2) Open cursor
3) Row-by-row removal of rows in the cursor and processing separately
4) Close the cursor
The working procedure for cursors is as follows.
DECLARE OPEN FETCH CLOSE
Declaring a cursor open cursor fetching data from a cursor close a cursor
Note: When fetching data from a cursor, make a decision to determine if and whether it is finished, and if so, the close cursor, or, if no, continue to fetch the cursor.
The declaration of a cursor is made in the Declarations section of the PL/SQL block.
The syntax format for the declaration is:
DECLARE
Cursor cursor name is
SELECT statement;
For example, the following statement declares a cursor named cur_1:
DECLARE
CURSOR cur_1 is
SELECT * from EMP;
Because the execution result of the SELECT statement is stored in the workspace, you do not need to use the INTO clause to assign the returned data to a variable.
To work with the data in the cursor, you first open the cursor.
Opening a cursor means giving the specified SELECT statement to the database server for execution and storing the returned results in the workspace.
The command to open a cursor is open, and its syntax is in the following format:
OPEN cursor Name
For example, the statement that opens the cursor cur_l is:
OPEN cur_1;
Once the cursor is open, the data in the travel target can be taken and processed.
The command to extract data from a cursor is fetch.
The fetch command extracts one row of data at a time and assigns it to the specified variable.
The format of the FETCH command is:
FETCH cursor into variable 1, variable 2, ...
The data in the cursor can only be processed after it is removed.
To do this, you need to specify multiple variables in a FETCH statement, one for each column in a row of data, with an into clause.
The FETCH command assigns the values of each column in a row of data to the specified variable in turn.
It is important to note that the type and number of variables corresponds to the columns of the cursor has an row.
When you use the FETCH command to remove data from a cursor, you can envision a pointer to a row of data in the cursor.
When the cursor is just opened, the pointer points to the first row, and each time a row is fetched, the pointer automatically points to the next line until all data is removed.
After the cursor is used, it should be closed in time to free up the memory space it occupies.
The command to close the cursor is close, which is in the syntax format:
CLOSE cursor name;
When the cursor is closed, the data can no longer be fetched from the cursor.
If necessary, you can open the cursor again.
Consider the following cursor.
Retrieves the name, salary, and working time of employee 7902 from the EMP table.
Because the SELECT command returns only one row of data, the process is straightforward.

PL/SQL Programming _ Cursors