Oracle Database SQL % found, SQL % notfound, SQL % rowcount

SQL % found, SQL % notfound, SQL % rowcount
When executing the DML (insert, update, delete) statement, you can use the following three implicit cursors (the cursor is an area in the memory that maintains the query results, which is opened when DML is run, when it is finished, use SQL % isopen to check whether it is enabled ):

SQL % found (boolean type, default value: null)

SQL % notfound (boolean type, default value: null)

SQL % rowcount (default value: 0)

SQL % isopen (boolean type)

After a DML statement is executed, the results of the DML statement are stored in four cursor attributes, which are used to control the program flow or understand the program status. When running a DML statement, PL/SQL opens a built-in cursor and processes the result. The cursor is an area in the memory that maintains the query result. The cursor is opened when running the DML statement and closed after completion. Only SQL % FOUND, SQL % NOTFOUND, and SQL % ROWCOUNT attributes are used for implicit cursors. SQL % FOUND, SQL % NOTFOUND is a Boolean value, and SQL % ROWCOUNT is an integer.
SQL % FOUND and SQL % NOTFOUND
Before executing any DML statement, the values of SQL % FOUND and SQL % NOTFOUND are NULL. After executing the DML statement, the attribute values of SQL % FOUND will be:
. TRUE: INSERT
. TRUE: DELETE and UPDATE. At least one row is deleted or updated.
. TRUE: select into returns at least one row.
When SQL % FOUND is TRUE, SQL % NOTFOUND is FALSE.
SQL % ROWCOUNT
Before executing any DML statement, the SQL % ROWCOUNT value is NULL. If the select into statement is executed successfully, the SQL % ROWCOUNT value is 1, if no operation is successful or no operation is performed (for example, the values of update, insert, and delete are 0), the value of SQL % ROWCOUNT is 0.
SQL % ISOPEN
SQL % ISOPEN is a Boolean value. If the cursor is opened, it is TRUE. If the cursor is closed, it is FALSE. for implicit cursors, SQL % ISOPEN is always FALSE. This is because the implicit cursors are opened when DML statements are executed and are immediately closed at the end.

 

Differences between no_data_found, SQL % notfound, and SQL % rowcount:

NO_DATA_FOUND: this exception can occur in two different cases: SELECT .... When the WHERE clause of INTO does not match any data rows, the second method tries to reference PL/SQL index-by table elements that have not been assigned a value.

SQL % NOTFOUND: indicates the attribute for hiding the cursor. If no data can be retrieved, this attribute is TRUE. It is often used as a condition for loop exit of search.

If the WHERE clause of an UPDATE or DELETE statement does not match any data row, the attribute is TRUE, but no NO_DATA_FOUND exception occurs.

SQL % ROWCOUNT: This numeric attribute returns the number of database rows retrieved by the cursor so far.