After an UPDATE or DELETE in PL/SQL, code usually needs to know how many rows it changed, or whether it changed any at all. Every SQL statement in PL/SQL runs through an implicit cursor named SQL, and its attributes answer those questions without another query.
Code for This Guide
The main examples are in the examples/cursors folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
The Attributes
| Attribute | Returns |
|---|---|
| SQL%ROWCOUNT | The number of rows the last statement affected |
| SQL%FOUND | TRUE if it affected at least one row |
| SQL%NOTFOUND | TRUE if it affected no rows |
| SQL%ISOPEN | Always FALSE, since the implicit cursor closes after each statement |
The attributes describe the most recent SQL statement run by the block, so read them immediately after the statement they refer to.
Rows Updated and Nothing Deleted
Example:
begin
update employees set salary = salary * 1.02 where department_id = 90;
dbms_output.put_line(sql%rowcount || ' rows updated');
delete from payments where booking_id = -1;
if sql%notfound then
dbms_output.put_line('No payment deleted');
end if;
rollback;
end;
/Output:
4 rows updated No payment deleted PL/SQL procedure successfully completed.
SQL%ROWCOUNT reports the four raises, and SQL%NOTFOUND detects that the DELETE found nothing, which raises no error by itself. The block rolls back.
After Other Statements
Example:
declare
v_city airports.city%type;
begin
select city into v_city from airports where airport_code = 'NRT';
dbms_output.put_line('after SELECT INTO: rowcount ' || sql%rowcount);
update routes set block_minutes = block_minutes where origin = 'DXB';
dbms_output.put_line('after UPDATE: rowcount ' || sql%rowcount ||
', found ' || case when sql%found then 'yes' else 'no' end);
rollback;
dbms_output.put_line('after ROLLBACK: rowcount ' || nvl(to_char(sql%rowcount), 'NULL'));
end;
/Output:
after SELECT INTO: rowcount 1 after UPDATE: rowcount 20, found yes after ROLLBACK: rowcount 0 PL/SQL procedure successfully completed.
A SELECT INTO that succeeds sets SQL%ROWCOUNT to 1. The UPDATE reports its 20 rows. After ROLLBACK, the attributes refer to the rollback, and SQL%ROWCOUNT is 0, so save the count in a variable if you need it later.
Things to Know
- A DELETE or UPDATE that matches no rows is not an error; test SQL%ROWCOUNT or SQL%NOTFOUND when it should have matched.
- A SELECT INTO that finds nothing raises NO_DATA_FOUND, so SQL%NOTFOUND is not useful after it.
- After FORALL, SQL%BULK_ROWCOUNT(i) gives the rows affected by each iteration.
Related Guides
Conclusion
The implicit cursor attributes SQL%ROWCOUNT, SQL%FOUND, and SQL%NOTFOUND tell PL/SQL how the last SQL statement went. Read them right after the statement, save them if needed later, and use them to catch updates and deletes that matched nothing.
