Warm tip: This article is reproduced from stackoverflow.com, please click
cursor oracle plsql select

cursor plsql using select

发布于 2020-04-10 10:18:35

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;
/
Questioner
HICHEM
Viewed
40
分享
Littlefoot 2020-02-02 00:05

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
HICHEM 2020-02-02 00:35:30

i used cursor juste for learning; thanks ;

Related issues

1
How do I pivot in Oracle SQL 19
2
dbms_output.put_line not working for type variable
3
How to extract words from space in oracle?
4
Oracle 19c
5
How can I add condition for total sales per month for specific category
6
Dynamic pivot in oracle sql
7
How to group by month and year?
8
Procedure is not running
9
How do I update CLOB column on oracle with PHP
10
A trigger inserting a row (not new) after update