Marc Marc - 7 months ago 6
SQL Question

Defining a cursor inside a loop in PL SQL

Can I set the value of a cursor inside of a loop? I'm new to SQL, so I apologize if this is a basic question. The variable in question is c2.

declare
type NumberArray is array(100) of clock_in_out.pr_emp_id%Type;
type DateArray is array(1000) of clock_in_out.time_in_out%TYPE;
emps NumberArray;
times DateArray;

cursor c1 is select unique pr_emp_id from clock_in_out;
cursor c2;

BEGIN
open c1;
fetch c1 bulk collect into emps;
close c1;

FOR i IN emps.FIRST .. emps.LAST
LOOP

c2 is select time_in_out from clock_in_out where pr_emp_id = emps(i) order by time_in_out;

open c2;
fetch c2 bulk collect into times;
close c2;

END LOOP;
END;

MT0 MT0
Answer

Yes, you can use a parameter when you define the cursor:

DECLARE
  TYPE NumberArray IS ARRAY(100)  OF clock_in_out.pr_emp_id%Type;
  TYPE DateArray   IS ARRAY(1000) OF clock_in_out.time_in_out%TYPE;
  emps NumberArray;
  times DateArray;

  CURSOR c2( emp_id clock_in_out.pr_emp_id%TYPE) IS
    select time_in_out
    from clock_in_out
    where pr_emp_id = emp_id
    order by time_in_out;
BEGIN
  SELECT UNIQUE pr_emp_id
  BULK COLLECT INTO emps
  FROM clock_in_out;

  FOR i IN emps.FIRST .. emps.LAST
  LOOP
    open c2 ( emps(i) );
    fetch c2 bulk collect into times;
    close c2;

    FOR j IN 1 .. times.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE( times(i) );
    END LOOP;
  END LOOP;
END;
/

Or you could just not use cursors at all:

declare
  type NumberArray is array(100) of clock_in_out.pr_emp_id%Type;
  type DateArray is array(1000) of clock_in_out.time_in_out%TYPE;
  emps NumberArray;
  times DateArray;
BEGIN
  select unique pr_emp_id
  BULK COLLECT INTO emps
  from clock_in_out;

  FOR i IN emps.FIRST .. emps.LAST
  LOOP
    select time_in_out
    BULK COLLECT INTO times
    from clock_in_out
    where pr_emp_id = emps(i)
    order by time_in_out;

    FOR j IN 1 .. times.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE( times(i) );
    END LOOP;
  END LOOP;
END;
/
Comments