cursor plsql using select

Question

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;
/

Answer 1

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