CODEWITHSUNDEEP
oracle-databaseplsqlcursor
jalal rasooly
jalal rasooly

Reputation: 705

how inner cursor use a field of outer cursor in oracle?

I have two nested cursors. I mean one cursor Is Inside of another one.

I want to use a field of outer cursor inside the inner one. something like this:

Inner_cursor.outer_cursor.outer_cursor_column;

but It does not work even I use like this:

Inner_cursor.(outer_cursor.outer_cursor_column);

Is there any way I could do this?

EDIT:

This Is My Code:

CREATE OR REPLACE PROCEDURE TEST1                                            
AS
    CURSOR loop_relation IS
        SELECT * FROM RELATION_table;
    relation_rec loop_relation%rowtype;
    CURSOR loop_BIG_TABLE IS
        SELECT * FROM BIG_TABLE;
    BIG_TABLE_rec loop_BIG_TABLE%rowtype;   
BEGIN
    FOR RELATION_REC IN LOOP_RELATION
    LOOP
        FOR BIG_TABLE_rec in loop_BIG_TABLE
        LOOP
            IF (BIG_TABLE_REC.RELATION_REC.DESTINATION_PK IS NULL) THEN
                UPDATE BIG_TABLE
                SET BIG_TABLE.RELATION_REC.DESTINATION_PK = (
                    SELECT RELATION_REC.SOURCE_FK FROM RELATION_REC.SOURCE_TABLE
                    WHERE RELATION_REC.SOURCE_PK = BIG_TABLE_REC.RELATION_REC.SOURCE_PK)
                WHERE BIG_TABLE_REC.ID = BIG_TABLE.ID;
            END IF;
        END LOOP;
    END LOOP;   
END TEST1;
/

my problem is in the lines that i use three dot(.) to use a value of outer cursor in inner cursor.

Upvotes: 2

Views: 1896

Answers (2)

Avrajit
Avrajit

Reputation: 230

For reference, I created a procedure to display how to use outer cursor value inside inner cursor value. I hope this resolves your query. 

    CREATE OR REPLACE PROCEDURE cur_inside_cur(my_cur OUT sys_refcursor)
    AS 
    CURSOR roy_cur IS
    SELECT name FROM avrajit;
    roy_cur1 roy_cur%ROWTYPE;
    BEGIN
    OPEN roy_cur;
    LOOP
    FETCH roy_cur INTO roy_cur1;
    EXIT WHEN roy_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(roy_cur1.name);
    OPEN my_cur FOR
    SELECT department FROM avrajit
    WHERE name=roy_cur1.name;
    END LOOP;
    END cur_inside_cur;

OUTPUT

var c refcursor;
begin
cur_inside_cur(:c);
end;
print c;

Upvotes: 1

Alen Oblak
Alen Oblak

Reputation: 3325

Here's an example of two nested cursors and variables from the outer one used in the inner one. I hope it helps you.

BEGIN
   FOR r_outer in (
      select tab1.field1
      from   table1 tab1 )
   LOOP
      FOR r_inner in (
         select tab2.field2
         from   table2 tab2
         where  tab2.field2 = r_outer.field1 )
      LOOP
         dbms_output.put_line(r_outer.field1);
         dbms_output.put_line(r_inner.field2);
      END LOOP;
   END LOOP;
END;

Upvotes: 1

Related Questions