PL/SQL cursor usage

PL/SQL cursor usage

Use of cursors

① Cursor Concept
To process SQL statements, Oracle must allocate a region called context area to process required information,
This includes the number of rows to be processed, a pointer to the expression after the statement is analyzed, and the queried active set ).
A cursor is a handle or pointer pointing to the context. Through the cursor, PL/SQL can control the context area and what will happen in the context area when processing statements.

② Explicit cursor Processing

1. Explicit cursor processing requires four PL/SQL steps:

Define a cursor: defines a cursor name and the SELECT statement corresponding to it.
Format:
CURSOR cursor_name [(parameter [, parameter]…)] IS select_statement;
The cursor parameter can only be an input parameter, in the following format:
Parameter_name [IN] datatype [{: = | DEFAULT} expression]
Length constraints are not allowed when you specify a data type. For example, NUMBER (4) and CHAR (10) are all incorrect.

Open cursor: Execute the SELECT statement corresponding to the cursor, put the query result into the workspace, and point the pointer to the first of the workspace to identify the cursor result set.
If the cursor query statement contains the for update option, the OPEN statement also locks the data rows corresponding to the cursor result set in the database table.
Format:
OPEN cursor_name [([parameter =>] value [, [parameter =>] value]…)];
When passing parameters to a cursor, you can use the same value passing method as the function parameter, that is, location representation and name representation. The PL/SQL program cannot OPEN a cursor again with an OPEN statement.

Extract cursor data: it is the data row in the search result set and placed in the specified output variable.
Format:
FETCH cursor_name INTO {variable_list | record_variable };
Process the record;
Continue to process until there are no records in the activity set;

Close cursor: After the cursor result set data is extracted and processed, the cursor should be closed in time to release the system resources occupied by the cursor and make the workspace of the cursor invalid,
You cannot use the FETCH statement to retrieve the data. The closed cursor can be reopened using the OPEN statement.
Format:
CLOSE cursor_name;
Note: The Defined cursor cannot have an INTO clause.

-------------------------------------- Split line --------------------------------------

Rlwrap

SQLPLUS spool to dynamic Log File Name

Oracle SQLPLUS prompt settings

Accelerate SQL return by setting SQLPLUS ARRAYSIZE (row prefetch)

PL/SQL Developer Practical Skills

-------------------------------------- Split line --------------------------------------

2. cursor attributes
% FOUND Boolean attribute. The value is TRUE if the last read record is returned successfully;
% NOTFOUND Boolean attribute, opposite to % FOUND;
% ISOPEN Boolean attribute. TRUE is returned when the cursor is opened;
% ROWCOUNT numeric attribute, returns the number of records read from the cursor.

3. FOR loop of the cursor
The PL/SQL language provides the OPEN, FETCH, CLOSE, and loop statements FOR the cursor to automatically execute. When the cursor enters the loop, the cursor FOR loop statement automatically opens the cursor, and extract the first row of cursor data. When the program enters the next cycle after processing the extracted data, the cursor FOR loop statement automatically extracts the next row of data FOR processing by the program, when all data rows in the result set are extracted, the cycle ends and the cursor is automatically closed.
Format:
FOR index_variable IN cursor_name [value [, value]…] LOOP
-- Cursor Data Processing code
End loop;
Where:
Index_variable is the index variable implicitly declared by the cursor FOR loop statement. This variable is the record variable, and its structure is the same as that of the structure set returned by the cursor query statement. In the program, you can reference the index record variable element to read the extracted cursor data,
The names of each element in index_variable are the same as the column names in the selection list of the cursor query statement.
If a computed Column exists in the selection list of a cursor query statement, you must specify an alias FOR these calculated columns before using the index variable in the cursor FOR loop statement to access the column data.
Note: Do not manually operate the cursor in the program; do not define records in the program to control the FOR loop.

③ Handle implicit cursors
Explicit cursors are mainly used to process query statements, especially when multiple records are queried;
For non-query statements, such as modification and deletion operations, the ORACLE system automatically sets a cursor for these operations and creates its workspace,
These System-implicitly created cursors are called implicit cursors. The implicit cursors are named SQL, which is defined by the ORACLE system. For implicit cursor operations,
For example, the definition, open, value, and close operations are all automatically completed by the ORACLE system, and no user needs to process them. You can perform operations only through the properties of implicit cursors.
In the workspace of an implicit cursor, the stored data is the data contained in an SQL statement that is not related to the custom display cursor.

Format call: SQL %
Implicit cursor property
SQL % FOUND: Boolean attribute. The value is TRUE if the last read record is returned successfully;
SQL % NOTFOUND Boolean attribute, opposite to % FOUND;
SQL % ROWCOUNT numeric attribute, returns the number of records read from the cursor;
SQL % ISOPEN Boolean attribute. The value is always FALSE. Close the implicit cursor immediately after the SQL command is executed.

④ Differences between NO_DATA_FOUND and % NOTFOUND
SELECT... The INTO statement triggers NO_DATA_FOUND; (EXCEPTION when NO_DATA_FOUND then ......)
% NOTFOUND is triggered when the WHERE clause of an explicit cursor is not found;
SQL % NOTFOUND is triggered when the WHERE clause of the UPDATE or DELETE statement is not found;
% NOTFOUND or % FOUND should be used in the extraction loop to determine the exit condition of the loop. Do not use NO_DATA_FOUND.

⑤ Cursor modification and deletion operations
The cursor modification and deletion operations refer to modifying or deleting the specified data rows in the table under the cursor positioning.
In this case, The for update option must be used in the cursor query statement to lock all the columns and some columns of the corresponding data row in the table when the cursor is opened.
To prevent rows being processed (queried) from being modified by another user, ORACLE provides a for update clause to lock the selected rows.
This requirement forces ORACLE to lock rows in the cursor result set to prevent other transactions from updating or deleting the same rows until your transaction is committed or rolled back.
Syntax:
SELECT... FROM... For update [OF column [, column]…] [NOWAIT]
If another session has applied a lock to the row in the active set, the select for update operation will not continue until other sessions release these locks,
In this case, when the NOWAIT clause is added, if these rows are actually locked by another session, OPEN returns immediately and gives:
ORA-0054: resource busy and acquire with nowait specified.
If you use for update to declare a cursor, you can use the where current of cursor_name clause in the DELETE and UPDATE statements,
Modify or delete the data rows in the database table corresponding to the current row of the cursor result set

For more details, please continue to read the highlights on the next page:

1 2 Next Page