pseudocolumn LEVEL and an example of using the levels with an update.

 
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>

Related examples in the same category

1.	Using the LEVEL Pseudo-Column:display the level in the tree
2.	Use the COUNT() function and LEVEL to get the number of levels in the tree
3.	Traversing Upward Through the Tree
4.	sort by LEVEL
5.	pseudocolumn LEVEL and an example of using the levels.
6.	Browse Products with three level nested queries

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

Related examples in the same category