Oracle PL/SQL/Hierarchical Query/Level
Содержание
- 1 Browse Products with three level nested queries
- 2 pseudocolumn LEVEL and an example of using the levels.
- 3 pseudocolumn LEVEL and an example of using the levels with an update.
- 4 sort by LEVEL
- 5 Traversing Upward Through the Tree
- 6 Use the COUNT() function and LEVEL to get the number of levels in the tree
- 7 Using the LEVEL Pseudo-Column:display the level in the tree
Browse Products with three level nested queries
SQL>
SQL> CREATE TABLE Product (
2 ProductID INT NOT NULL PRIMARY KEY,
3 Name VARCHAR(50) NOT NULL,
4 Description VARCHAR(1000) NOT NULL,
5 Price NUMBER NULL,
6 ImagePath VARCHAR(50) NULL,
7 soldout NUMBER(1,0) NULL,
8 Promotion NUMBER(1,0) NULL);
Table created.
SQL>
SQL> CREATE SEQUENCE ProductIDSeq;
Sequence created.
SQL>
SQL> CREATE OR REPLACE TRIGGER ProductAutonumberTrigger
2 BEFORE INSERT ON Product
3 FOR EACH ROW
4 BEGIN
5 SELECT ProductIDSeq.NEXTVAL
6 INTO :NEW.ProductID FROM DUAL;
7 END;
8 /
Trigger created.
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath,soldout, Promotion)
2 VALUES ("Pen", "Ball Pen",5.99, "pen.jpg", 1, 0);
1 row created.
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Ruler", "Long",14.99, "ruler.jpg", 0, 0);
1 row created.
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Desk", "Computer Desk",5.99, "desk.jpg", 0, 1);
1 row created.
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("PC", "Notebook",49.99, "pc.jpg", 0, 1);
1 row created.
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Mouse", "Wireless",9.99, "mouse.jpg", 1, 0);
1 row created.
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Keyboard", "keyboard",3.75, "keyboard.jpg", 0, 0);
1 row created.
SQL>
SQL> SELECT ProductID, Name FROM
2 (SELECT ProductID, Name, ROWNUM AS rn FROM
3 (SELECT ProductID, Name
4 FROM Product
5 ORDER BY ProductID)
6 ) inner
7 WHERE inner.rn BETWEEN 6 AND 10;
PRODUCTID NAME
---------- --------------------------------------------------
6 Keyboard
1 row selected.
SQL>
SQL> drop table product;
Table dropped.
SQL> drop sequence ProductIDSeq;
Sequence dropped.
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.
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>
sort by LEVEL
SQL>
SQL> create table employee(
2 emp_no integer primary key
3 ,lastname varchar2(20) not null
4 ,firstname varchar2(15) not null
5 ,midinit varchar2(1)
6 ,street varchar2(30)
7 ,city varchar2(20)
8 ,state varchar2(2)
9 ,zip varchar2(5)
10 ,zip_4 varchar2(4)
11 ,area_code varchar2(3)
12 ,phone varchar2(8)
13 ,salary number(3)
14 ,birthdate date
15 ,hiredate date
16 ,title varchar2(20)
17 ,dept_no integer
18 ,mgr integer
19 ,region number
20 ,division number
21 ,total_sales number
22 );
Table created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (1,"Anderson","Nancy","N","33 Ave","London","NY","11111","1111","212","234-1111",3.75,"21-mar-1927","1-feb-1947","Sales Manager",2,null,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (2,"Last","First","F","12 Ave","Paris","CA","22222","2222","221","867-2222",7.75,"14-feb-1976","15-mar-1985","Sales Clerk",2,1,100,10,10000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (3,"Wash","Georgia","G","1 Street14","Barton","NJ","33333","3333","214","340-3333",11.50,"2-jul-1977","21-apr-2004","Designer",1,2,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (4,"Bush","Dave","D","56 Street","Island","RI","44444","4444","215","777-4444",21.65,"15-may-1945","2-aug-1975","Designer",1,2,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (5,"Will","Robin","W","56 Street","Island","MA","55555","5555","216","777-5555",24.65,"10-dec-1980","2-aug-2007","Designer",1,5,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (6,"Pete","Mona","M","13 Ave","York","MO","66666","6666","217","111-6666",9,"14-feb-1966","15-mar-1985","Sales Clerk",2,5,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (7,"Roke","John","J","67 Ave","New York","BC","77777","7777","218","122-7777",10.00,"14-jun-1955","15-mar-1975","Accountant",3,2,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (8,"Horry","Tedi","T","1236 Lane","Newton","NY","88888","8888","219","222-8888",13.00,"10-jun-1955","15-aug-1985","Sales Representative",3,2,100,10,50000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (9,"Bar","Candi","C","400 East Street","Yorken","NY","99999","9999","220","321-9999",12.00,"10-oct-1933","15-jan-1969","Sales Representative",3,5,100,10,35000);
1 row created.
SQL>
SQL> select * from employee;
EMP_NO LASTNAME FIRSTNAME M STREET CITY ST ZIP ZIP_ ARE PHONE SALARY
---------- -------------------- --------------- - ------------------------------ -------------------- -- ----- ---- --- -------- ----------
BIRTHDATE HIREDATE TITLE DEPT_NO MGR REGION DIVISION TOTAL_SALES
-------------------- -------------------- -------------------- ---------- ---------- ---------- ---------- -----------
1 Anderson Nancy N 33 Ave London NY 11111 1111 212 234-1111 4
21-MAR-1927 00:00:00 01-FEB-1947 00:00:00 Sales Manager 2 100 10 40000
2 Last First F 12 Ave Paris CA 22222 2222 221 867-2222 8
14-FEB-1976 00:00:00 15-MAR-1985 00:00:00 Sales Clerk 2 1 100 10 10000
3 Wash Georgia G 1 Street14 Barton NJ 33333 3333 214 340-3333 12
02-JUL-1977 00:00:00 21-APR-2004 00:00:00 Designer 1 2 100 10 40000
4 Bush Dave D 56 Street Island RI 44444 4444 215 777-4444 22
15-MAY-1945 00:00:00 02-AUG-1975 00:00:00 Designer 1 2 100 10 40000
5 Will Robin W 56 Street Island MA 55555 5555 216 777-5555 25
10-DEC-1980 00:00:00 02-AUG-2007 00:00:00 Designer 1 5 100 10 40000
6 Pete Mona M 13 Ave York MO 66666 6666 217 111-6666 9
14-FEB-1966 00:00:00 15-MAR-1985 00:00:00 Sales Clerk 2 5 100 10 40000
7 Roke John J 67 Ave New York BC 77777 7777 218 122-7777 10
14-JUN-1955 00:00:00 15-MAR-1975 00:00:00 Accountant 3 2 100 10 40000
8 Horry Tedi T 1236 Lane Newton NY 88888 8888 219 222-8888 13
10-JUN-1955 00:00:00 15-AUG-1985 00:00:00 Sales Representative 3 2 100 10 50000
9 Bar Candi C 400 East Street Yorken NY 99999 9999 220 321-9999 12
10-OCT-1933 00:00:00 15-JAN-1969 00:00:00 Sales Representative 3 5 100 10 35000
9 rows selected.
SQL>
SQL> select lpad(" ",2*level-2) || lastname as name
2 from employee
3 start with emp_no = 2
4 connect by prior emp_no = mgr
5 ORDER BY LEVEL;
NAME
--------------------
Last
Horry
Roke
Wash
Bush
5 rows selected.
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL> --
Traversing Upward Through the Tree
SQL> CREATE TABLE employee (
2 employee_id INTEGER,
3 manager_id INTEGER,
4 first_name VARCHAR2(10) NOT NULL,
5 last_name VARCHAR2(10) NOT NULL,
6 title VARCHAR2(20),
7 salary NUMBER(6, 0)
8 );
Table created.
SQL>
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 1 ,0 , "James" ,"Smith" ,"CEO",800000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 2 , 1 ,"Ron" ,"Johnson","Sales Manager",600000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 3 , 2 ,"Fred" ,"Hobbs" ,"Sales Person",200000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 4 , 1 ,"Susan" ,"Jones" ,"Support Manager",500000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 5 , 2 ,"Rob" ,"Green" ,"Sales Person", 40000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 6 , 4 ,"Jane" ,"Brown" ,"Support Person",45000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 7 , 4 ,"John" ,"Grey" ,"Support Manager",30000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 8 , 7 ,"Jean" ,"Blue" ,"Support Person",29000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 9 , 6 ,"Henry" ,"Heyson" ,"Support Person",30000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 10 , 1 ,"Kevin" ,"Black" ,"Ops Manager",100000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 11 , 10 ,"Keith" ,"Long" ,"Ops Person",50000);
1 row created.
SQL>
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 12 , 10 ,"Frank" ,"Howard" ,"Ops Person",45000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 13 , 10 ,"Doreen" ,"Penn" ,"Ops Person",47000);
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> select * from employee;
EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME TITLE SALARY
----------- ---------- ---------- ---------- -------------------- ----------
1 0 James Smith CEO 800000
2 1 Ron Johnson Sales Manager 600000
3 2 Fred Hobbs Sales Person 200000
4 1 Susan Jones Support Manager 500000
5 2 Rob Green Sales Person 40000
6 4 Jane Brown Support Person 45000
7 4 John Grey Support Manager 30000
8 7 Jean Blue Support Person 29000
9 6 Henry Heyson Support Person 30000
10 1 Kevin Black Ops Manager 100000
11 10 Keith Long Ops Person 50000
12 10 Frank Howard Ops Person 45000
13 10 Doreen Penn Ops Person 47000
13 rows selected.
SQL>
SQL> -- Traversing Upward Through the Tree
SQL>
SQL> SELECT LEVEL,
2 LPAD(" ", 2 * LEVEL - 1) || first_name || " " ||
3 last_name AS employee
4 FROM employee
5 START WITH last_name = "Blue"
6 CONNECT BY PRIOR manager_id = employee_id;
LEVEL EMPLOYEE
---------- -------------------------
1 Jean Blue
2 John Grey
3 Susan Jones
4 James Smith
SQL>
SQL>
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>
Use the COUNT() function and LEVEL to get the number of levels in the tree
SQL>
SQL> CREATE TABLE employee (
2 employee_id INTEGER,
3 manager_id INTEGER,
4 first_name VARCHAR2(10) NOT NULL,
5 last_name VARCHAR2(10) NOT NULL,
6 title VARCHAR2(20),
7 salary NUMBER(6, 0)
8 );
Table created.
SQL>
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 1 ,0 , "James" ,"Smith" ,"CEO",800000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 2 , 1 ,"Ron" ,"Johnson","Sales Manager",600000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 3 , 2 ,"Fred" ,"Hobbs" ,"Sales Person",200000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 4 , 1 ,"Susan" ,"Jones" ,"Support Manager",500000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 5 , 2 ,"Rob" ,"Green" ,"Sales Person", 40000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 6 , 4 ,"Jane" ,"Brown" ,"Support Person",45000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 7 , 4 ,"John" ,"Grey" ,"Support Manager",30000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 8 , 7 ,"Jean" ,"Blue" ,"Support Person",29000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 9 , 6 ,"Henry" ,"Heyson" ,"Support Person",30000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 10 , 1 ,"Kevin" ,"Black" ,"Ops Manager",100000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 11 , 10 ,"Keith" ,"Long" ,"Ops Person",50000);
1 row created.
SQL>
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 12 , 10 ,"Frank" ,"Howard" ,"Ops Person",45000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 13 , 10 ,"Doreen" ,"Penn" ,"Ops Person",47000);
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> select * from employee;
EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME TITLE SALARY
----------- ---------- ---------- ---------- -------------------- ----------
1 0 James Smith CEO 800000
2 1 Ron Johnson Sales Manager 600000
3 2 Fred Hobbs Sales Person 200000
4 1 Susan Jones Support Manager 500000
5 2 Rob Green Sales Person 40000
6 4 Jane Brown Support Person 45000
7 4 John Grey Support Manager 30000
8 7 Jean Blue Support Person 29000
9 6 Henry Heyson Support Person 30000
10 1 Kevin Black Ops Manager 100000
11 10 Keith Long Ops Person 50000
12 10 Frank Howard Ops Person 45000
13 10 Doreen Penn Ops Person 47000
13 rows selected.
SQL>
SQL> --Use the COUNT() function and LEVEL to get the number of levels in the tree
SQL>
SQL> SELECT COUNT(DISTINCT LEVEL)
2 FROM employee
3 START WITH employee_id = 1
4 CONNECT BY PRIOR employee_id = manager_id;
COUNT(DISTINCTLEVEL)
--------------------
4
SQL>
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>
Using the LEVEL Pseudo-Column:display the level in the tree
SQL>
SQL> CREATE TABLE employee (
2 employee_id INTEGER,
3 manager_id INTEGER,
4 first_name VARCHAR2(10) NOT NULL,
5 last_name VARCHAR2(10) NOT NULL,
6 title VARCHAR2(20),
7 salary NUMBER(6, 0)
8 );
Table created.
SQL>
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 1 ,0 , "James" ,"Smith" ,"CEO",800000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 2 , 1 ,"Ron" ,"Johnson","Sales Manager",600000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 3 , 2 ,"Fred" ,"Hobbs" ,"Sales Person",200000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 4 , 1 ,"Susan" ,"Jones" ,"Support Manager",500000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 5 , 2 ,"Rob" ,"Green" ,"Sales Person", 40000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 6 , 4 ,"Jane" ,"Brown" ,"Support Person",45000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 7 , 4 ,"John" ,"Grey" ,"Support Manager",30000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 8 , 7 ,"Jean" ,"Blue" ,"Support Person",29000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 9 , 6 ,"Henry" ,"Heyson" ,"Support Person",30000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 10 , 1 ,"Kevin" ,"Black" ,"Ops Manager",100000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 11 , 10 ,"Keith" ,"Long" ,"Ops Person",50000);
1 row created.
SQL>
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 12 , 10 ,"Frank" ,"Howard" ,"Ops Person",45000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 13 , 10 ,"Doreen" ,"Penn" ,"Ops Person",47000);
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> select * from employee;
EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME TITLE SALARY
----------- ---------- ---------- ---------- -------------------- ----------
1 0 James Smith CEO 800000
2 1 Ron Johnson Sales Manager 600000
3 2 Fred Hobbs Sales Person 200000
4 1 Susan Jones Support Manager 500000
5 2 Rob Green Sales Person 40000
6 4 Jane Brown Support Person 45000
7 4 John Grey Support Manager 30000
8 7 Jean Blue Support Person 29000
9 6 Henry Heyson Support Person 30000
10 1 Kevin Black Ops Manager 100000
11 10 Keith Long Ops Person 50000
12 10 Frank Howard Ops Person 45000
13 10 Doreen Penn Ops Person 47000
13 rows selected.
SQL>
SQL>
SQL> --Using the LEVEL Pseudo-Column:display the level in the tree
SQL>
SQL> SELECT LEVEL, employee_id, manager_id, first_name, last_name
2 FROM employee
3 START WITH employee_id = 1
4 CONNECT BY PRIOR employee_id = manager_id
5 ORDER BY LEVEL;
LEVEL EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME
---------- ----------- ---------- ---------- ----------
1 1 0 James Smith
2 10 1 Kevin Black
2 2 1 Ron Johnson
2 4 1 Susan Jones
3 13 10 Doreen Penn
3 7 4 John Grey
3 11 10 Keith Long
3 5 2 Rob Green
3 3 2 Fred Hobbs
3 12 10 Frank Howard
3 6 4 Jane Brown
4 8 7 Jean Blue
4 9 6 Henry Heyson
13 rows selected.
SQL>
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>