CODEWITHSUNDEEP
sqloracle-databaseplsql
user6119513
user6119513

Reputation:

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;

Upvotes: 2

Views: 9086

Answers (1)

MT0
MT0

Reputation: 168751

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;
/

Upvotes: 1

Related Questions