Iterate over a column in PL/SQL

Question

I have a table Emp with EmpID, Empname, Salary and I am trying to do a calculation for each employee. But I am having problems trying to iterate over each emp to do the calculation. I cant use explicit cursors though.

So right now I am just trying to create the list of empIDs:

Declare
    aRows Number;
    eid emp_ID%TYPE;
Begin
    Select Count(*)
    Into aRows 
    from emp;

    Select emp_ID
    Into eid 
    From emp;

    FOR days IN 1..Tot_Rows
    Loop
        Dbms_Output.Put_Line(eid);
        eid := eid + 1;
    End Loop;
END; 

But I get the error: PLS-00320: the declaration of the type of this expression is incomplete or malformed

Answer 1

The simplest way to iterate over the rows in a table in PL/SQL is to do something like

BEGIN
  FOR employees IN (SELECT emp_id FROM emp)
  LOOP
    dbms_output.put_line( employees.emp_id );
  END LOOP;
END;

Alternately, you could fetch all the EID values into a PL/SQL collection and iterate over the collection, as in this example

DECLARE
  TYPE emp_id_tbl IS TABLE OF emp.emp_id%type;
  l_emp_ids emp_id_tbl ;
BEGIN
  SELECT emp_id
    BULK COLLECT INTO l_emp_ids 
    FROM emp;

  FOR i IN l_emp_ids .FIRST .. l_empnos.LAST
  LOOP
    dbms_output.put_line( l_emp_ids (i) );
  END LOOP;
END;

If your query can return thousands of rows, however, fetching all the data into the collection may use more of the PGA memory than you'd like and you may need to fetch rows in chunks using the LIMIT clause. But that would seem to be getting ahead of ourselves at this point.

Answer 2

Justin Cave has explained how to do it, but to specifically look at the error you got, that was because of this:

eid emp_ID%TYPE;

When using the %TYPE you have to specify the table name as well as the column name:

eid emp.emp_ID%TYPE;

If you were selecting all the columns in the row you could also look at %ROWTYPE.

Your approach was also making two assumptions: that the initial select into eid found the lowest ID, which is by no means guaranteed; and that all the subsequent ID values are sequential. And you're declaring and populating aRows but referring to Tot_Rows.