Using For loop to retrieve multiple rows in Oracle procedure

Question

Hi all,

Im working on stored procedure where I need to retrieve a set of results and process each element individually and then return the entire result.(using 3 different tables)

Im not too familiar with databases, but heres what I was able to come up with..

create or replace procedure GET_EMP_RSLT
  IS

CURSOR ecursor IS select emp_id from temp_employee where 'some condition';

BEGIN

FOR empidset in ecursor  

  LOOP

  Select * from 

    (select * from payroll_info where emp_id = empidset.emp_id) a

    left join 

    (select * from benefit_info where emp_id = empidset.emp_id) b 
     on a.emp_id = b.emp_id    

  END LOOP;

END;

On execution, I get the following error..

an INTO clause is expected in this SELECT statement : "Select * from"

can anyone please explain on how do I correct this error and get the required results?

PS. Im using Oracle 9i & TOAD 9

Thanks,
Tom

Answer 1

You need to add an INTO clause to specify which local variables to place the selected data, eg.

select ID, Name
  into myID, myName
from emp

Answer 2

The SELECT inside of your loop needs to have an INTO clause to process the values - it is not clear from your code what you're trying to do, but I suspect the nested SELECT's/JOIN inside of the cursor loop could be better written as a three table join in the main cursor.

Answer 3

Below is a possible solution, making a fair number of assumptions. As written, it returns the result as a ref cursor containing data from all 3 tables (it would be trivial to make it return a ref cursor for each table, but that would be more work for a dubious result).

However, unless you're really doing something in the PL/SQL that you can't do in SQL, you'd be much better off doing this directly in SQL.

create object EMP_PAYROLL_BENEFIT as object (
   em_id number,
   some_payroll_column number,
   some_benefit_column number);

create type NT_EMP_PAYROLL_BENEFIT as table of EMP_PAYROLL_BENEFIT;

create or replace procedure GET_EMP_RSLT(p_output OUT sys_refcursor)  IS    
CURSOR ecursor IS select emp_id 
                  from temp_employee te 
                       join payroll_info pi 
                       on te.emp_id = pi.emp_id 
                       join benefit_info bi 
                       on te.emp_id = bi.emp_id 
                  where some_column = 'some condition';
v_results NT_EMP_PAYROLL_BENEFIT;
BEGIN
   open ecursor;
   fetch ecursor bulk collect into v_results;
   close ecursor;
   for i = v_results.first..v_results.last loop
      v_results.some_benefit_column := some_payroll_column + i;
   end loop;
   open p_output for select * from table(v_results);
end;

Answer 4

There are too many syntactical and ideological errors in your code. Therefore, read, please, PL/SQL documentation here, especially PL/SQL Architecture section to understand difference between SQL and PL/SQL (generally SQL - query language, PL/SQL - programming language) and sections for your case:

1. "Understanding PL/SQL Procedures" and "Understanding PL/SQL Functions"
2. "Cursor FOR Loops" and "Using cursor FOR Loops"

Full PL/SQL reference for Oracle 9i R2 available at this link.

Set of all Oracle 9i R2 documentation can be found here.