Reputation: 73
I wrote simple program in PL/SQL to reduce price. When I call procedure I intentionally pass arguments which are not in database, so cursor doesn't return any data.
Here is problem: my exception not working. Expression like kursor%notfound
check if kursor not returns any data or is not declared?
I am confused, because while I was doing research some people said that kursor%notfound
returns true when there is no data found, but in my program it doesn't work. When it comes to this:
if (kursor%notfound) then
raise no_data_found;
end if;
It doesn't raise exception. What am I doing wrong? PS Sorry for inconsistency according to language(mixing polish and english) , but I have database in polish.
My whole program:
set serveroutput on
create or replace procedure reduce_price(surname_p varchar2,
name_p varchar2, percents number default 5)is
cursor kursor is
select n.id_mech,cena from naprawa n
join mechanik m on m.id_mech = n.id_mech
where m.imie = name_p and m.nazwisko = surname_p
for update;
nc number;
begin
for k in kursor
loop
if (kursor%notfound) then
raise NO_DATA_FOUND;
end if;
begin
nc := k.cena *(1-percents/100);
dbms_output.put_line(k.cena ||' ' ||nc);
update naprawa set cena =nc
where id_mech = k.id_mech;
exception
when NO_DATA_FOUND then
dbms_output.put_line('no rows found');
end;
end loop;
end;
/
begin
reduce_price('aaa', 'XYZ',1);
end;
Thanks for your time.
Upvotes: 1
Views: 2130
Reputation: 12179
That's not going to work. in your cursor for loop, if your code enters the loop, then that implies that one or more records where found, and %notfound cursor attribute will never be true. You have a couple of options.
keep a counter in the loop and check after exit
create or replace procedure reduce_price(surname_p varchar2, name_p varchar2, percents number default 5)is
nc number;
cnt number := 0;
begin
for k in kursor
loop
nc := k.cena *(1-percents/100);
dbms_output.put_line(k.cena ||' ' ||nc);
update naprawa set cena =nc
where id_mech = k.id_mech;
cnt := cnt + 1;
end loop;
if cnt = 0 then
raise NO_DATA_FOUND;
end if;
.. etc..
check for a existing data before entering the loop
select count(*)
into cnt
from naprawa n
join mechanik m on m.id_mech = n.id_mech
where m.imie = name_p and m.nazwisko = surname_p;
if cnt = 0 then
raise NO_DATA_FOUND;
end if;
...
for k in kursor
loop
Upvotes: 4