EXAMPLE FOR SQL%ROWCOUNT (IMPLICIT CURSORS)

declare

rows_affected char(4);

begin

update employee set salary = salary*0.15 where job='programmers';

rows_affected := to_char(sql%rowcount);

if sql%rowcount > 0 then

dbms_output.put_line(rows_affected || 'employee records modified successfully');

else

dbms_output.put_line('There are no employees working as programmers');

end if;

end;

EXAMPLE FOR SQL%NOTFOUND (IMPLICIT CURSORS)

begin

update employee set salary = salary*0.15 where emp_code = &emp_code;

if sql%notfound then

dbms_output.put_line('employee no . does not exist');

else

dbms_output.put_line('employee record modified successfully');

end if;

end;

EXAMPLE OF IMPLICIT CURSORS

Begin

update employee set salary=salary *0.15

Where emp_code = &emp_code;

if sql%found then

dbms_output.put_line('employee record modified successfully');

else  

dbms_output.put_line('employee no does not exist');

end if;

end;

EXAMPLE OF SEQUENTIAL CONTROL GOTO.....

declare

--takes the default datatype of the column of the table price

cost price.minprice%type;

begin

select stdprice into cost from price where prodial in (Select prodid from product where prodese = "shampoo");

if cost > 7000 then

goto Upd;

end if;

<< Upd >>

Update price set minprice = 6999 where prodid=111;

end;

SIMPLE EXAMPLE OF FOR LOOP

Begin

For i in 1..10

Loop

dbms_output.put_line(to_char(i));

End loop;

end;

SIMPLE EXAMPLE OF WHILE LOOP

Declare

i number:=0;

j number:= 0;

begin

while i <=100 loop

j := j+1;

i := i +2;

end loop;

dbms_output.put_line (to_char(i));

end;

SIMPLE EXAMPLE OF LOOP STATEMENT I.E. EXIT WHEN

Declare

a number:= 100;

begin

loop

a := a+25;

exit when a=250;

end loop;

dbms_output.put_line (to_Char(a));

end;