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));
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);
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);
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);
SQL>
SQL> INSERT INTO book (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
2 VALUES ('4', null, 'Oracle Ebusiness', 'Oracle Ebusiness', 'Oracle E-Business Suite Financials Handbook', 820, 59.99, 2002);
SQL>
SQL>
SQL> ALTER TABLE book
2 ADD position NUMBER(10);
SQL>
SQL> PROMPT
SQL>
SQL> DECLARE
2 v_level PLS_INTEGER;
3 v_title book.TITLE%TYPE;
4
5 CURSOR cur_tree IS SELECT isbn, title, series FROM book;
6 BEGIN
7
8 FOR l IN cur_tree
9 LOOP
10
11 SELECT max(LEVEL) INTO v_level FROM book START WITH isbn = l.isbn CONNECT BY PRIOR parent_isbn = isbn;
12
13 UPDATE book SET position = v_level WHERE isbn = l.isbn;
14
15 END LOOP;
16
17 COMMIT;
18
19 EXCEPTION
20 WHEN OTHERS
21 THEN
22 DBMS_OUTPUT.PUT_LINE(sqlerrm);
23 END;
24 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT title, position FROM book ORDER BY series, position;
TITLE
--------------------------------------------------------------------------------
POSITION
----------
Oracle8.0 PL/SQL Programming
1
Java
2
SQL
3
XML
4
Oracle E-Business Suite Financials Handbook
1
5 rows selected.
SQL> drop table book;
Table dropped.