в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:08:32Z

1 версия

Новая страница

== Use the pseudocolumn LEVEL==



<source lang="sql">
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>
SQL>
SQL> drop table books;
Table dropped.</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/PL SQL Programming/LEVEL - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия