CODEWITHSUNDEEP
sqlplsqloracle-sqldeveloper
Ola Duda
Ola Duda

Reputation: 73

Is there any way to check if cursor return no record?

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

Answers (1)

OldProgrammer
OldProgrammer

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.

  1. 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..

  2. 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

Related Questions