CODEWITHSUNDEEP
oracle-databaseplsqlcursor
user1639485
user1639485

Reputation: 808

Creating a result set (using a select statement ) within a loop

I have created a cursor which returns me a set of rows. While iterating through each of the row, I want to get another result set (by forming a SELECT statement by with a WHERE clause having value from the processed row) from another table. I am a newbie in PLSQL. Can you please guide me on how this could be done? (Can we have a Cursor defined inside the loop while looping for the resultset of the cursor)?

Please excuse me if I am not able to make myself clear.

Thanks in advance

DECLARE
  CURSOR receipts IS
                  SELECT CREATED_T, ACCT_NO, AMT FROM receipt_t
                  WHERE OBJ_TYPE='misc';   
  receipts_rec receipts%ROWTYPE;
  BEGIN
  -- Open the cursor for processing
  IF NOT receipts%ISOPEN THEN
        OPEN receipts;
  END IF;

  LOOP
     FETCH receipts INTO receipts_rec;
     EXIT WHEN receipts%NOTFOUND;
     /* Loop through each of row and get the result set from another table */
        newQuery := 'SELECT * FROM ageing_data WHERE ACCT_NO = ' || receipts_rec.ACCT_NO;
        -- Execute the above query and get the result set, say RS
     LOOP
        -- For above result set-RS
     END LOOP;
  END LOOP;
  CLOSE receipts;
END;

Upvotes: 0

Views: 1208

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Yes, you can do that, but there is absolutely no reason to. Try the following:

BEGIN
  FOR aRow IN (SELECT rt.CREATED_T, rt.ACCT_NO, rt.AMT, ad.*
                 FROM RECEIPT_T rt
                 INNER JOIN AGEING_DATA ad
                   ON (ad.ACCT_NO = rt.ACCT_NO)
                 WHERE rt.OBJ_TYPE='misc')
  LOOP
    -- Process the data in aRow here
  END LOOP;
END;

This does exactly the same work as the original "loop-in-a-loop" structure but uses the database to join the tables together on the common criteria instead of opening and closing cursors multiple times.

Share and enjoy.

Upvotes: 1

pablomatico
pablomatico

Reputation: 2242

Yes, you can define a cursor that takes a set of parameters and use those values in the WHERE clause.

 DECLARE
   CURSOR c_cursor1 IS
     SELECT field1, field2, ... , fieldN
     FROM table1
     WHERE conditions;

   CURSOR c_cursor2 (p_parameter NUMBER) IS
     SELECT field1, field2, ..., fieldN
     FROM table2
     WHERE table2.field1 = p_parameter;

BEGIN
  FOR record1 IN c_cursor1 LOOP
    FOR record2 IN c_cursor2(record1.field1) LOOP
      dbms_output.put_line('cursor 2: ' || record2.field1);
    END LOOP
  END LOOP;
END;

Upvotes: 1

Navjot Singh
Navjot Singh

Reputation: 497

Something like this can be done in the following manner:

DECLARE
CURSOR cursor1 IS
   SELECT *
     FROM table1;

CURSOR cursor2 IS
   SELECT *
     FROM table2
    WHERE column1 = I_input_param;
BEGIN


FOR table_1_rec in cursor1 LOOP
   I_input_param := table_1_rec.column_1;
   FOR table_2_rec in cursor2 LOOP

    ....
    ....
   END LOOP;
END LOOP; 
END;

I have used an implicit open/fetch here. I hope you get the idea.

Upvotes: 0

Related Questions