Mega Code Archive
Use the pseudocolumn LEVEL
<![CDATA[
SQL>
SQL> CREATE TABLE books (
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 books (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
2 VALUES ('72191473', '72121467', 'Oracle PL/SQL', 'Oracle Server', 'Oracle9i PL/SQL Programming', 664, 49.99, 2002);
1 row created.
SQL>
SQL> INSERT INTO books (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
2 VALUES ('72121467', null, 'Oracle PL/SQL', 'Oracle Server', 'Oracle8i Advanced PL/SQL Programming', 772, 49.99, 2000);
1 row created.
SQL>
SQL> INSERT INTO books (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
2 VALUES ('72230665', '72191473', 'Oracle PL/SQL', 'Oracle Server', 'Oracle Database 10g PL/SQL Programming', 1008, 54.99, 2004);
1 row created.
SQL>
SQL> INSERT INTO books (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
2 VALUES ('72132302', null, 'Oracle Ebusiness', 'Oracle Ebusiness', 'Oracle E-Business Suite Financials Handbook', 820, 59.99, 2002);
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> PROMPT
SQL> PROMPT ** The following is a demonstration of LEVEL, and the
** The following is a demonstration of LEVEL, and the
SQL> PROMPT ** use of START WITH ... CONNECT BY PRIOR to display
** use of START WITH ... CONNECT BY PRIOR to display
SQL> PROMPT ** parent/child hierarchical relationships.
** parent/child hierarchical relationships.
SQL> PROMPT
SQL>
SQL> DECLARE
2 v_level PLS_INTEGER;
3 v_title BOOKS.TITLE%TYPE;
4
5 CURSOR cur_tree
6 IS
7 SELECT isbn, title, series
8 FROM books;
9 BEGIN
10
11 FOR l IN cur_tree
12 LOOP
13
14 SELECT max(LEVEL)
15 INTO v_level
16 FROM books
17 START WITH isbn = l.isbn
18 CONNECT BY PRIOR parent_isbn = isbn;
19
20 DBMS_OUTPUT.PUT_LINE(l.title||' is book '
21 ||v_level||' in the '||l.series||' series');
22
23 END LOOP;
24
25 CLOSE cur_tree;
26
27 EXCEPTION
28 WHEN OTHERS
29 THEN
30 DBMS_OUTPUT.PUT_LINE(sqlerrm);
31 END;
32 /
Oracle9i PL/SQL Programming is book 2 in the Oracle PL/SQL series
Oracle8i Advanced PL/SQL Programming is book 1 in the Oracle PL/SQL series
Oracle Database 10g PL/SQL Programming is book 3 in the Oracle PL/SQL series
Oracle E-Business Suite Financials Handbook is book 1 in the Oracle Ebusiness series
ORA-01001: invalid cursor
PL/SQL procedure successfully completed.
SQL>
SQL> drop table books;
Table dropped.
]]>