Defining a cursor inside a loop in PL SQL

Question

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;

Seems like Oracle allows parameters to cursors. So define c2 to take a parameter at the top. And then open with different parameters in the loop. I don't have the syntax handy otherwise I'd write an answer. — Shannon Severance
– Shannon Severance, Commented Apr 15, 2016 at 22:58

MT0 · Accepted Answer · 2021-11-05 21:30:00Z

1

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(j) );
    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(j) );
    END LOOP;
  END LOOP;
END;
/

edited Nov 5, 2021 at 21:30

answered Apr 15, 2016 at 23:14

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mina F. Beshay Over a year ago

You can add EXIT WHEN TIMES(i) IS NULL;

MT0 Over a year ago

@MinaF.Beshay I am failing to see where that would be necessary. The collections will not be sparse and looping from 1 .. times.COUNT is sufficient.

Mina F. Beshay Over a year ago

I got you. You are right.

Collectives™ on Stack Overflow

Defining a cursor inside a loop in PL SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related