Oracle creates a memory area, known as the context area for processing an SQL statements, which contain all the informations needed for procesing the SQL statements.
A Cursor is the pointer to the memory area. PL/SQL controls the memory area through the cursor. It holds the rows (one or more) returned by the SQL statement. The set of rows the cursor holds is referred as the Active set.
These cursors can be named, so that you can rerferred them in a program to fetch and process the rows returned by the SQL statement.
There are two types of cursors,
- Implicit cursor
- Explicit cursor
Implicit cursors are automatically created by the Oracle whenever a DML statement is executed, when there is no explicit cursor for the statement. These cursors cannot be named, and hence they cannot be controlled or referred from another place of the code.
For INSERT operations, the cursor hold the data needs to be insreted. For UPDATE and DELETE the cursor finds the rows that would be affected.
In PL/SQL, the most recent cursor as the SQL cursor, which have the attributes such as %FOUND, %NOTFOUND, %ISOPEN, AND %ROWCOUNT. And additonal attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement.
Returns TRUE if an INSERT, UPDATE, OR DELETE statement affected one or more rows or a SELECT INTO statement returned one or two. Otherwisem it returns FALSE.
Opposite of %FOUND. Returns TRUE if an INSERT, UPDATE, OR DELETE statement affected no rows or a SELECT INTO statement returned no rows. Otherwisem it returns FALSE.
Always return FALSE for implicit cursor, because Oracle close the cursor automaticlly after executing the SQL statement.
Returs the number of row affected by a DML SQL statemet.
sql%attribute_name is the sytax for acessing the SQL cursor attribute.
SELECT * FROM CUSTOMERS;
| ID | NAME |
| 1 | BINO |
| 2 | JINO |
The below will update the table and change the name and use the sql%rowcount attribute to find the number of rows affected.
UPDATE CUSTOMERS SET NAME = 'BINO VARGHESE' WHERE NAME = 'BINO';
IF sql%notfound THEN
dbms_output.put_line('no rows affected.');
ELSIF sql%found THEN
total_rows_affected := sql%rowcount;
dbms_output.put_line( total_rows_affected || ' row(s) affected.');
The output will be as following
PL/SQL procedure successfully completed.
1 row(s) affected.
if the DBMS.OUTPUT.PUT_LINE is not printing you may refer this Stack overflow question.