SQL%FOUND and SQL%ROWCOUNT

We could get information on the most recent execution of a SQL statement in PL/SQL checking a few implicit cursor attributed made available to us.

Here we see a couple of them, SQL%FOUND and SQL%ROWCOUNT, in action.

In a PL/SQL block we run this UPDATE statement:

update family
set surname = 'Smith Dumblee'
where surname like 'S%';

After that, we could check if we modified anything in the database:

if(sql%found) then
dbms_output.put_line('at least one row affected');
else
dbms_output.put_line('no rows affected');
end if;

If we need more precise information, we could use SQL%ROWCOUNT:

case sql%rowcount
when 0 then
dbms_output.put_line('no rows affected');
when 1 then
dbms_output.put_line('one row affected');
else
dbms_output.put_line('more than one row affected');
end case;

More information on DML in PL/SQL in chapter 14 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein.

No comments:

Post a Comment