CODEWITHSUNDEEP
oracle-databaseselectplsqlcursor
HICHEM
HICHEM

Reputation: 73

cursor plsql using select

i want to get one value from table1(etudiant) in order to use this value in table2(employee) using slect i use cursor , but i didn't get the result; can u help me thanks 

    declare
           cle employee.id%type;
           cursor cur_test is select id from etudiant where name ='Hichem';
           no_data_found exception;
    begin

      open cur_test;

      loop
           fetch cur_test into cle;
           exit when cur_test%notfound;
      end loop;

      --dbms_output.put_line('ID IS ' ||cle);
      select * from employee where id=cle;

      if cur_test%rowcount=0 then
        close cur_test;
        raise no_data_found;      
      end if;
      close cur_test;

exception 
      when no_data_found then
        dbms_output.put_line('no data found');

end;
/

Upvotes: 0

Views: 505

Answers (1)

Littlefoot
Littlefoot

Reputation: 143083

Looks like you've overcomplicated it. Why wouldn't you directly join etudiant and employee tables? Something like this:

begin
  for cur_r in (select e.name
                from employee e join etudiant t on t.id = e.id
                where t.name = 'Hichem'
               )
  loop
    dbms_output.put_line(cur_r.name);
  end loop;
end;
/

Perhaps you don't even need a loop; or, you do if there is more than one person with the name you're looking for (Hichem in this case).

As of your code:

  • generally speaking, it is simpler to use a cursor FOR loop than doing everything manually (declaring cursor variable, opening cursor, fetching, exiting the loop, closing cursor) - if you use cursor FOR loop, Oracle does all that for you
  • no need to explicitly declare no_data_found; it is a predefined exception
  • select * from employees is wrong as - in PL/SQL - you have to select the result INTO something. If you expect more than a single row, consider using a collection with bulk collect

Upvotes: 2

Related Questions