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.
See my articles more often when you search Google.
