SQL>
SQL> CREATE TABLE book (
2 isbn VARCHAR2(10) PRIMARY KEY,
3 parent_isbn VARCHAR2(10),
4 series VARCHAR2(20),
5 category VARCHAR2(20),
6 title VARCHAR2(100),
7 num_pages NUMBER,
8 price NUMBER,
9 copyright NUMBER(4));
Table created.
SQL>
SQL> INSERT INTO book (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
2 VALUES ('1', '2', 'Oracle', 'Oracle Server', 'SQL', 664, 49.99, 2002);
1 row created.
SQL>
SQL> INSERT INTO book (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
2 VALUES ('2', null, 'Oracle', 'Oracle Server', 'Java', 772, 49.99, 2000);
1 row created.
SQL>
SQL> INSERT INTO book (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
2 VALUES ('3', '1', 'Oracle', 'Oracle Server', 'XML', 1008, 54.99, 2004);
1 row created.
SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 v_level PLS_INTEGER;
3 v_title book.TITLE%TYPE;
4 CURSOR cur_tree IS SELECT isbn, title, series FROM book;
5 BEGIN
6 FOR l IN cur_tree
7 LOOP
8 SELECT max(LEVEL) INTO v_level FROM book
9 START WITH isbn = l.isbn
10 CONNECT BY PRIOR parent_isbn = isbn;
11
12 DBMS_OUTPUT.PUT_LINE(l.title||' is book '||v_level||' in the '||l.series||' series');
13
14 END LOOP;
15 CLOSE cur_tree;
16 EXCEPTION
17 WHEN OTHERS
18 THEN
19 DBMS_OUTPUT.PUT_LINE(sqlerrm);
20 END;
21 /
SQL is book 2 in the Oracle series
Java is book 1 in the Oracle series
XML is book 3 in the Oracle series
ORA-01001: invalid cursor
PL/SQL procedure successfully completed.
SQL>
SQL> drop table book;
Table dropped.