CODEWITHSUNDEEP
plsqlcursorprocedure
ibaneight
ibaneight

Reputation: 1445

Use cursor from a modified table into the same PL/SQL procedure

I'm new in PL/SQL and I want to know how to resolve my problem. I actually want to insert some values into a table and after that insertion get values from this updated table and do some stuff into a new table.

So here's my example, I first insert some values from TABLE_1 into TABLE_2

CREATE or REPLACE PROCEDURE PROC_1 AS
CURSOR C1 IS SELECT DISTINCT COL1 FROM TABLE_1;
BEGIN
   FOR REG IN C1 LOOP
      BEGIN
         INSERT INTO TABLE_2 (COL1) VALUES (REG.COL1);
      END;
   END LOOP;
END;

Now in a second time I want to retrieve all the rows of TABLE_2 and loop over the results to insert some values into a third table (TABLE_3)

How can I use a second CURSOR after the insertion of the values into TABLE_2 with the content of the updated TABLE_2 and then insert values in TABLE_3 into the same PROC_1 procedure?

Upvotes: 0

Views: 256

Answers (2)

D.J.
D.J.

Reputation: 386

You can use global temp table for this purpose.

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  id           NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT PRESERVE ROWS;

https://oracle-base.com/articles/misc/temporary-tables

Upvotes: 0

Aleksej
Aleksej

Reputation: 22949

Assuming that you have something to do on the table before using the values to insert into the third one, you can use exactly te same way you did the first insert:

CREATE or REPLACE PROCEDURE PROC_1 AS
    CURSOR C1 IS SELECT DISTINCT COL1 FROM TABLE_1;
    CURSOR C2 IS SELECT DISTINCT COL1 FROM TABLE_2;
BEGIN
   FOR REG IN C1 LOOP
      BEGIN
         INSERT INTO TABLE_2 (COL1) VALUES (REG.COL1);
      END;
   END LOOP;
   --
   /* ... something ... */
   --
   FOR REG IN C2 LOOP
      BEGIN
         INSERT INTO TABLE_3 (COL1) VALUES (REG.COL1);
      END;
   END LOOP;
END;

For example:

SQL> select count(1) from table_1;

  COUNT(1)
----------
         2

SQL> select count(1) from table_2;

  COUNT(1)
----------
         0

SQL> select count(1) from table_3;

  COUNT(1)
----------
         0

SQL> exec proc_1;

PL/SQL procedure successfully completed.

SQL> select count(1) from table_2;

  COUNT(1)
----------
         2

SQL> select count(1) from table_3;

  COUNT(1)
----------
         2

SQL>

However, you don't need cursors for this and you could write a procedure like:

CREATE or REPLACE PROCEDURE PROC_2 AS
BEGIN
   INSERT INTO TABLE_2 (COL1) SELECT COL1 FROM TABLE_1;
   --
   /* ... something ... */
   --
   INSERT INTO TABLE_3 (COL1) SELECT COL1 FROM TABLE_2;
END;

which gives:

SQL> DELETE TABLE_2;

2 rows deleted.

SQL> DELETE TABLE_3;

2 rows deleted.

SQL> EXEC PROC_2;

PL/SQL procedure successfully completed.

SQL> select count(1) from table_2;

  COUNT(1)
----------
         2

SQL> select count(1) from table_3;

  COUNT(1)
----------
         2

Upvotes: 0

Related Questions