CODEWITHSUNDEEP
plsqloracle11g
Sid133
Sid133

Reputation: 364

Modifying condition for PL/SQL procedure

I have a PL/SQL procedure for deleting records corresponding to a field having NULL value. Am able to achieve this by the below query.

set serveroutput on;

begin
    dbms_output.put_line('Execution started');

    for rec in (
        select name from employee where emp_id is null
        and location = 'SITE_A'
    )
    loop
        delete from employeedetails@sitea where name = rec.name;
        dbms_output.put_line('name '|| rec.name ||' deleted');
    end loop;

    dbms_output.put_line('Execution completed');
end;
/

set serveroutput off;

I am running the for loop query from one database and deleting the records in another database ( sitea ) using a database link.

I need to add a condition like, if the name=rec.name is not returning any records to be deleted,then dbms_output.put_line('No records to be deleted');

Is there a comfortable way to achieve it ?

Upvotes: 0

Views: 56

Answers (1)

Alex Poole
Alex Poole

Reputation: 191425

You can use the sql%rowcount implicit cursor attribute to see how many rows were affected by a DML statement; that works across a database link as well as locally:

if sql%rowcount = 0 then
  dbms_output.put_line('No records to be deleted');
else
  dbms_output.put_line('name '|| rec.name ||' deleted');
end if;

You can include it in the messages too:

if sql%rowcount = 0 then
  dbms_output.put_line('name '|| rec.name ||': no records to be deleted');
else
  dbms_output.put_line('name '|| rec.name ||': '|| sql%rowcount ||' record(s) deleted');
end if;

Then you'll see something like:

Execution started
name B: 1 record(s) deleted
name C: no records to be deleted
Execution completed


PL/SQL procedure successfully completed.

As name probably isn't unique you could encounter the same value twice as you go through your loop; in which case the first delete will find multiple rows and the second delete will find none. You could avoid the second one by adding distinct to your cursor query.

And if you didn't want to see which names did and did not have remote data to delete then you could use a much simpler single delete, with no loop or PL/SQL, but it seems like this is an exercise anyway...

Upvotes: 3

Related Questions