CODEWITHSUNDEEP
oracle-databaseplsql
Dev1
Dev1

Reputation: 87

Select Query in cursor FOR LOOP

Pretty new to Oracle, need help on this Procedure, using Oracle 11g

Sample table data (TAB1):

ID Amount Currency
10 300 GBP
15 500 GBP
20 100 GBP
  1. Requirement is to select all the ID's from TAB1 based on currency and store it in a variable and later use these ID's in other select Queries within the same Stored Procedure.
CREATE OR REPLACE PROCEDURE myproc (i_id IN VARCHAR, i_curr IN VARCHAR)
AS
   CURSOR GET_I IS
      SELECT ID, CURRENCY
        FROM TAB1
       WHERE CURRENCY = 'GBP';

   -- This will give me 3 ID's (10, 15 & 20) which I am storing in variable r_1 below.
   r_1  VARCHAR (5)       : NULL;
BEGIN
   OPEN GET_I;

   LOOP
      FETCH GET_I INTO r_1;

      IF GET_I%NOTFOUND
      THEN
         EXIT;
      ---In the below ELSE PART can we run a select query using the value stored in r_1??
      --ELSE
      --Data stored in r_1 to be used in further select queries in later part and output of the below
      --be returned as SYS_REFCURSOR;
      --BELOW two lines gives error
      --FOR I in r_1 (
      --SELECT ID FROM TAB2 WHERE TAB2.ID=r_1);

      END IF;
   END LOOP;

   CLOSE GET_ID;
END;

Upvotes: 0

Views: 8390

Answers (1)

Littlefoot
Littlefoot

Reputation: 142713

It looks you want to use a nested FOR loop.

I'd suggest you to use cursor FOR loops - they are easier to maintain as you don't have to declare cursor variable (by the way, in your case it wouldn't work anyway as you'd like to store both ID and CURRENCY into a scalar R_1 variable), open the cursor, pay attention about exiting the loop and closing the cursor. In a cursor FOR loop, Oracle does all that for you.

Here's an example:

Sample table:

SQL> select * from tab1;

        ID     AMOUNT CUR
---------- ---------- ---
        10        300 GBP
        15        500 GBP
        20        100 GBP

Procedure:

SQL> create or replace procedure myproc as
  2  begin
  3    for cur_id in (select id from tab1) loop
  4      dbms_output.put_line('ID = ' || cur_id.id);
  5      for cur_other in (select amount, currency
  6                        from tab1
  7                        where id = cur_id.id    --> use ID fetched in outer loop
  8                       )
  9      loop
 10        dbms_output.put_line(cur_other.amount ||' - '|| cur_other.currency);
 11      end loop;
 12    end loop;
 13  end;
 14  /

Procedure created.

Testing:

SQL> set serveroutput on
SQL> exec myproc;
ID = 10
300 - GBP
ID = 15
500 - GBP
ID = 20
100 - GBP

PL/SQL procedure successfully completed.

SQL>

How to return a refcursor?

SQL> create or replace procedure myproc (par_id  in tab1.id%type,
  2                                      par_rc out sys_refcursor) as
  3  begin
  4    for cur_id in (select id
  5                   from tab1
  6                   where id = par_id
  7                  ) loop
  8
  9      open par_rc for select amount, currency
 10                      from tab1
 11                      where id = cur_id.id;
 12    end loop;
 13  end;
 14  /

Procedure created.

SQL> var l_rc refcursor
SQL>
SQL> exec myproc(10, :l_rc);

PL/SQL procedure successfully completed.

SQL> print l_rc

    AMOUNT CUR
---------- ---
       300 GBP

SQL>

Upvotes: 1

Related Questions