The basic principles of cursors:
In Oracle, when a pl/sql block of a SELECT, INSERT, update, and DELETE statement is executed, Oracle assigns a buffer to it in memory, puts the execution result in the buffer, and the cursor is a pointer to it.
Cursor classification:
A static cursor dynamic cursor.
Static cursors are also divided into implicit cursors and display cursors.
To display the use steps for cursors:
1. Declaring a cursor
CURSOR cursor_name [(Parameter[,parameter] ...)]
[Return Return_type] is select_statement;
Cursor_name refers to the name of the cursor
parameter is used to specify input parameters for cursors. You cannot use a length constraint when specifying a data type. For example, number (4), CHAR (10), and so on are all wrong.
Return_type is used to define the type of row that the cursor extracts.
Select_statement refers to the query statement defined by the cursor.
2. Open cursor
OPEN cursor_name[(parameters)];
3. Extract Cursors
FETCH cursor_name into variables;
Name of the cursor_name value cursor
Variables is the variable name.
4. Close cursor
Close cursor_name;
To display the properties of a cursor:
%found: The%found property returns true only if the DML statement affects one or more rows.
The%notfound:%notfound property is just the opposite of the%found property. If the DML statement does not affect any rows, the%notfound property returns True.
The%rowcount:%rowcount property returns the number of rows affected by a DML statement. If the DML statement does not affect any rows, the%rowcount property returns 0.
The%isopen:%isopen property returns whether the cursor is open.
To delete or update using a display cursor:
CURSOR cursor_name is
Select_statement for UPDATE [of columns];
Select for update[of columns] locks the selected row for an update query.
1. When you select a single label update query, you can omit the of clause.
2. When you select multiple tables to update the query, the rows that are locked are derived from the table in which the column is declared after the of clause.