Oracle PL/SQL Tutorial/Query Select/START WITH

START WITH

SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    EMPNO         NUMBER(3),
  3    ENAME         VARCHAR2(15 BYTE),
  4    HIREDATE      DATE,
  5    ORIG_SALARY   NUMBER(6),
  6    CURR_SALARY   NUMBER(6),
  7    REGION        VARCHAR2(1 BYTE),
  8    MANAGER_ID    NUMBER(3)
  9  )
 10  /
Table created.
SQL>
SQL> create table job (
  2    EMPNO         NUMBER(3),
  3    jobtitle      VARCHAR2(20 BYTE)
  4  )
  5  /
Table created.
SQL>
SQL> insert into job (EMPNO, Jobtitle) values (1,"Tester");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (2,"Accountant");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (3,"Developer");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (4,"COder");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (5,"Director");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (6,"Mediator");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (7,"Proffessor");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (8,"Programmer");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (9,"Developer");
1 row created.
SQL>
SQL>
SQL> -- prepare data
SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION, MANAGER_ID)
  2               values (1,      "Jason", to_date("19960725","YYYYMMDD"), 1234,              8767,         "E",    2)
  3  /
1 row created.
SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION, MANAGER_ID)
  2               values (2,      "John",  to_date("19970715","YYYYMMDD"), 2341,              3456,         "W",    3)
  3  /
1 row created.
SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION, MANAGER_ID)
  2               values (3,      "Joe",   to_date("19860125","YYYYMMDD"), 4321,              5654,         "E",    3)
  3  /
1 row created.
SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION, MANAGER_ID)
  2               values (4,      "Tom",   to_date("20060913","YYYYMMDD"), 2413,              6787,         "W",    4)
  3  /
1 row created.
SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION, MANAGER_ID)
  2               values (5,      "Jane",  to_date("20050417","YYYYMMDD"), 7654,              4345,         "E",    4)
  3  /
1 row created.
SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION, MANAGER_ID)
  2               values (6,      "James", to_date("20040718","YYYYMMDD"), 5679,              6546,         "W",    5)
  3  /
1 row created.
SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION, MANAGER_ID)
  2               values (7,      "Jodd",  to_date("20030720","YYYYMMDD"), 5438,              7658,         "E",    6)
  3  /
1 row created.
SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION)
  2               values (8,      "Joke",  to_date("20020101","YYYYMMDD"), 8765,              4543,         "W")
  3  /
1 row created.
SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION)
  2               values (9,      "Jack",  to_date("20010829","YYYYMMDD"), 7896,              1232,         "E")
  3  /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /
     EMPNO ENAME           HIREDATE  ORIG_SALARY CURR_SALARY R MANAGER_ID
---------- --------------- --------- ----------- ----------- - ----------
         1 Jason           25-JUL-96        1234        8767 E          2
         2 John            15-JUL-97        2341        3456 W          3
         3 Joe             25-JAN-86        4321        5654 E          3
         4 Tom             13-SEP-06        2413        6787 W          4
         5 Jane            17-APR-05        7654        4345 E          4
         6 James           18-JUL-04        5679        6546 W          5
         7 Jodd            20-JUL-03        5438        7658 E          6
         8 Joke            01-JAN-02        8765        4543 W
         9 Jack            29-AUG-01        7896        1232 E
9 rows selected.
SQL> select * from job
  2  /
     EMPNO JOBTITLE
---------- --------------------
         1 Tester
         2 Accountant
         3 Developer
         4 COder
         5 Director
         6 Mediator
         7 Proffessor
         8 Programmer
         9 Developer
9 rows selected.
SQL>
SQL> SELECT LEVEL,
  2   LPAD(" ", 2 * LEVEL - 1) || ename
  3  FROM employee
  4  START WITH empno = 1
  5  CONNECT BY PRIOR empno = manager_id
  6  AND ename != "James";
     LEVEL         LPAD("",2*LEVEL-1)||ENAME
         1         Jason

SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /
Table dropped.
SQL> drop table job
  2  /
Table dropped.
SQL>
SQL>

Using a Subquery in a START WITH Clause

SQL>
SQL>
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    EMPNO         NUMBER(3),
  3    ENAME         VARCHAR2(15 BYTE),
  4    HIREDATE      DATE,
  5    ORIG_SALARY   NUMBER(6),
  6    CURR_SALARY   NUMBER(6),
  7    REGION        VARCHAR2(1 BYTE),
  8    MANAGER_ID    NUMBER(3)
  9  )
 10  /
Table created.
SQL>
SQL> create table job (
  2    EMPNO         NUMBER(3),
  3    jobtitle      VARCHAR2(20 BYTE)
  4  )
  5  /
Table created.
SQL>
SQL> insert into job (EMPNO, Jobtitle) values (1,"Tester");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (2,"Accountant");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (3,"Developer");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (4,"COder");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (5,"Director");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (6,"Mediator");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (7,"Proffessor");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (8,"Programmer");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (9,"Developer");
1 row created.
SQL>
SQL>
SQL> -- prepare data
SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION, MANAGER_ID)
  2               values (1,      "Jason", to_date("19960725","YYYYMMDD"), 1234,              8767,         "E",    2)
  3  /
1 row created.
SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION, MANAGER_ID)
  2               values (2,      "John",  to_date("19970715","YYYYMMDD"), 2341,              3456,         "W",    3)
  3  /
1 row created.
SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION, MANAGER_ID)
  2               values (3,      "Joe",   to_date("19860125","YYYYMMDD"), 4321,              5654,         "E",    3)
  3  /
1 row created.
SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION, MANAGER_ID)
  2               values (4,      "Tom",   to_date("20060913","YYYYMMDD"), 2413,              6787,         "W",    4)
  3  /
1 row created.
SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION, MANAGER_ID)
  2               values (5,      "Jane",  to_date("20050417","YYYYMMDD"), 7654,              4345,         "E",    4)
  3  /
1 row created.
SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION, MANAGER_ID)
  2               values (6,      "James", to_date("20040718","YYYYMMDD"), 5679,              6546,         "W",    5)
  3  /
1 row created.
SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION, MANAGER_ID)
  2               values (7,      "Jodd",  to_date("20030720","YYYYMMDD"), 5438,              7658,         "E",    6)
  3  /
1 row created.
SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION)
  2               values (8,      "Joke",  to_date("20020101","YYYYMMDD"), 8765,              4543,         "W")
  3  /
1 row created.
SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION)
  2               values (9,      "Jack",  to_date("20010829","YYYYMMDD"), 7896,              1232,         "E")
  3  /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /
     EMPNO ENAME           HIREDATE  ORIG_SALARY CURR_SALARY R MANAGER_ID
---------- --------------- --------- ----------- ----------- - ----------
         1 Jason           25-JUL-96        1234        8767 E          2
         2 John            15-JUL-97        2341        3456 W          3
         3 Joe             25-JAN-86        4321        5654 E          3
         4 Tom             13-SEP-06        2413        6787 W          4
         5 Jane            17-APR-05        7654        4345 E          4
         6 James           18-JUL-04        5679        6546 W          5
         7 Jodd            20-JUL-03        5438        7658 E          6
         8 Joke            01-JAN-02        8765        4543 W
         9 Jack            29-AUG-01        7896        1232 E
9 rows selected.
SQL> select * from job
  2  /
     EMPNO JOBTITLE
---------- --------------------
         1 Tester
         2 Accountant
         3 Developer
         4 COder
         5 Director
         6 Mediator
         7 Proffessor
         8 Programmer
         9 Developer
9 rows selected.
SQL> SELECT LEVEL,
  2   LPAD(" ", 2 * LEVEL - 1) || ename
  3  FROM employee
  4  START WITH empno = (
  5    SELECT empno
  6    FROM employee
  7    WHERE ename = "James"
  8  )
  9  CONNECT BY PRIOR empno = manager_id;
     LEVEL        LPAD("",2*LEVEL-1)||ENAME
         1        James
         2        Jodd

SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /
Table dropped.
SQL> drop table job
  2  /
Table dropped.
SQL>