Oracle PL/SQL/Cursor/Nested Cursor

Cursor within a cursor

 
SQL> -- Cursor within a cursor
SQL>
SQL>     create or replace procedure MULTIPLE_CURSORS_PROC is
  2          v_owner varchar2(40);
  3          v_table_name varchar2(40);
  4          v_column_name varchar2(100);
  5
  6          
  7          cursor firstCursor is
  8          select distinct tbl.owner, tbl.table_name
  9          from all_tables tbl
 10          where tbl.owner = "SYSTEM";
 11
 12          
 13          cursor secondCursor is
 14          select distinct col.column_name
 15          from all_tab_columns col
 16          where col.owner = v_owner
 17          and col.table_name = v_table_name;
 18
 19         begin
 20
 21            
 22            open firstCursor;
 23            loop
 24                fetch firstCursor into v_owner, v_table_name;
 25
 26                dbms_output.put_line("v_owner: "||v_owner);
 27                dbms_output.put_line("v_table_name: "||v_table_name);
 28
 29                open secondCursor;
 30                loop
 31                    fetch secondCursor into v_column_name;
 32                    dbms_output.put_line("v_column_name: "||v_column_name);
 33                end loop;
 34                close secondCursor;
 35
 36             end loop;
 37             close firstCursor;
 38
 39      EXCEPTION
 40      WHEN OTHERS THEN
 41            raise_application_error(-20001,"An error was encountered - "||SQLCODE||" -ERROR- "||SQLERRM);
 42      end MULTIPLE_CURSORS_PROC;
 43      /
Procedure created.
SQL>
SQL>     exec MULTIPLE_CURSORS_PROC();

Nested cursor demo

  
SQL>
SQL> CREATE TABLE emp (
  2    id         NUMBER PRIMARY KEY,
  3    fname VARCHAR2(50),
  4    lname  VARCHAR2(50)
  5  );
Table created.
SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (1, "A", "B");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (2, "C", "D");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (3, "Enn", "F");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (4, "G", "H");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (5, "G", "Z");
1 row created.
SQL>
SQL>
SQL> CREATE TABLE books (
  2    isbn      CHAR(10) PRIMARY KEY,
  3    category  VARCHAR2(20),
  4    title     VARCHAR2(100),
  5    num_pages NUMBER,
  6    price     NUMBER,
  7    copyright NUMBER(4),
  8    emp1   NUMBER,
  9    emp2   NUMBER,
 10    emp3   NUMBER
 11  );
Table created.
SQL>
SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
  2             VALUES ("1", "Database", "Oracle", 563, 39.99, 2009, 1, 2, 3);
1 row created.
SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2)
  2             VALUES ("2", "Database", "MySQL", 765, 44.99, 2009, 4, 5);
1 row created.
SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
  2             VALUES ("3", "Database", "SQL Server", 404, 39.99, 2001, 6, 7, 8);
1 row created.
SQL>
SQL> SET SERVEROUTPUT ON ESCAPE OFF
SQL>
SQL> DECLARE
  2
  3     cv_emp SYS_REFCURSOR;
  4     v_title BOOKS.TITLE%TYPE;
  5     v_emp emp%ROWTYPE;
  6     v_counter PLS_INTEGER := 0;
  7
  8     CURSOR book_cur IS SELECT b.title,CURSOR (SELECT * FROM emp a WHERE a.id = b.emp1 OR a.id = b.emp2 OR a.id = b.emp3)
  9        FROM books b
 10        WHERE isbn = "1";
 11
 12  BEGIN
 13
 14     DBMS_OUTPUT.ENABLE(1000000);
 15
 16     OPEN book_cur;
 17
 18     LOOP
 19        FETCH book_cur INTO v_title, cv_emp;
 20        EXIT WHEN book_cur%NOTFOUND;
 21
 22        v_counter := 0;
 23
 24        DBMS_OUTPUT.PUT_LINE("Title from the main cursor: "||v_title);
 25
 26        LOOP
 27           FETCH cv_emp INTO v_emp;
 28           EXIT WHEN cv_emp%NOTFOUND;
 29
 30           v_counter := v_counter + 1;
 31
 32           DBMS_OUTPUT.PUT_LINE("emp"||v_counter||": "||v_emp.fname||" "||v_emp.lname);
 33        END LOOP;
 34     END LOOP;
 35
 36     CLOSE book_cur;
 37
 38  END;
 39  /
Title from the main cursor: Oracle
emp1: Enn F
emp2: C D
emp3: A B
PL/SQL procedure successfully completed.
SQL> drop table emp;
Table dropped.
SQL> drop table books;
Table dropped.