Mega Code Archive
Use the cursor subquery
<![CDATA[
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>
]]>