Use the cursor subquery : Cursor Declaration « Cursor « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Cursor
3. Cursor Declaration

SQL>
SQL>
SQL> CREATE TABLE authors (
  2    id         NUMBER PRIMARY KEY,
  3    first_name VARCHAR2(50),
  4    last_name  VARCHAR2(50)
  5  );

Table created.

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    author1   NUMBER CONSTRAINT books_author1
  9              REFERENCES authors(id),
 10    author2   NUMBER CONSTRAINT books_author2
 11              REFERENCES authors(id),
 12    author3   NUMBER CONSTRAINT books_author3
 13              REFERENCES authors(id)
 14  );

Table created.

SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
  2    VALUES (1, 'Marlene', 'Theriault');

1 row created.

SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
  2    VALUES (2, 'Rachel', 'Carmichael');

1 row created.

SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
  2    VALUES (3, 'James', 'Viscusi');

1 row created.

SQL>
SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1, author2, author3)
  2    VALUES ('72121203', 'Oracle Basics', 'Oracle DBA 101', 563, 39.99, 1999, 1, 2, 3);

1 row created.

SQL>
SQL> SET SERVEROUTPUT ON ESCAPE OFF
SQL>
SQL> DECLARE
  2
  3     cv_author SYS_REFCURSOR;
  4     v_title BOOKS.TITLE%TYPE;
  5     v_author AUTHORS%ROWTYPE;
  6     v_counter PLS_INTEGER := 0;
  7
  8     CURSOR book_cur
  9     IS
 10        SELECT b.title,
 11           CURSOR (SELECT *
 12                   FROM authors a
 13                   WHERE a.id = b.author1
 14                   OR a.id = b.author2
 15                   OR a.id = b.author3)
 16        FROM books b
 17        WHERE isbn = '78824389';
 18
 19  BEGIN
 20
 21     DBMS_OUTPUT.ENABLE(1000000);
 22
 23     OPEN book_cur;
 24
 25     LOOP
 26        FETCH book_cur INTO v_title, cv_author;
 27        EXIT WHEN book_cur%NOTFOUND;
 28
 29        v_counter := 0;
 30
 31        DBMS_OUTPUT.PUT_LINE('Title from the main cursor: '||v_title);
 32
 33        LOOP
 34           FETCH cv_author INTO v_author;
 35           EXIT WHEN cv_author%NOTFOUND;
 36
 37           v_counter := v_counter + 1;
 38
 39           DBMS_OUTPUT.PUT_LINE('Author'||v_counter||': '
 40                                ||v_author.first_name||' '
 41                                ||v_author.last_name);
 42        END LOOP;
 43     END LOOP;
 44
 45     CLOSE book_cur;
 46
 47  END;
 48  /

PL/SQL procedure successfully completed.

SQL>
SQL> drop table books;

Table dropped.

SQL>
SQL> drop table authors;

Table dropped.

SQL>