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






pseudocolumn LEVEL and an example of using the levels.

 
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.

   
  








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 with an update.
6.Browse Products with three level nested queries