pseudocolumn LEVEL and an example of using the levels with an update. : Hierarchical Queries « Query Select « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Query Select
3. Hierarchical Queries

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>