Mega Code Archive
Pseudocolumn LEVEL and an example of using the levels with an update
<![CDATA[
SQL>
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> 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);
1 row created.
SQL>
SQL>
SQL> ALTER TABLE book
2 ADD position NUMBER(10);
Table altered.
SQL>
SQL>
SQL> SET SERVEROUTPUT ON
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
12 START WITH isbn = l.isbn
13 CONNECT BY PRIOR parent_isbn = isbn;
14
15 UPDATE book SET position = v_level WHERE isbn = l.isbn;
16
17 END LOOP;
18
19
20 COMMIT;
21
22 EXCEPTION
23 WHEN OTHERS
24 THEN
25 DBMS_OUTPUT.PUT_LINE(sqlerrm);
26 END;
27 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> SET PAGES 9999
SQL> SELECT title, position
2 FROM book
3 ORDER BY series, position;
TITLE
--------------------------------------------------------------------------------
POSITION
----------
Java
1
SQL
2
XML
3
Oracle E-Business Suite Financials Handbook
1
4 rows selected.
SQL>
SQL>
SQL> INSERT INTO book (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
2 VALUES ('111111', null, 'Oracle', 'Oracle Server', 'Oracle8.0 PL/SQL Programming', 772, 49.99, 2000);
1 row created.
SQL>
SQL> UPDATE book
2 SET parent_isbn = '111111'
3 WHERE isbn = '2';
1 row updated.
SQL>
SQL> COMMIT;
Commit complete.
SQL>
SQL>
SQL> SELECT title, position
2 FROM book
3 ORDER BY series, position;
TITLE
--------------------------------------------------------------------------------
POSITION
----------
Java
1
SQL
2
XML
3
Oracle8.0 PL/SQL Programming
Oracle E-Business Suite Financials Handbook
1
5 rows selected.
SQL>
]]>