/*
|| Oracle 10g SQL Enhancements - Listing 1
||
|| Contains examples of new Oracle 10g SQL features, including:
||
|| - Expanded MERGE capabilities
|| - New features for hierarchical queries
|| - Enhancements to PL/SQL Collection handling
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10g's 
|| new SQL features and should be carefully proofread before executing it 
|| against any existing Oracle database to insure that no potential damage 
|| can occur.
||
*/
 
-----
-- Listing 1.1: Using the new MERGE features
-----
-- Create a new table to hold employee 401(k) contribution information
DROP TABLE hr.emp_401k_participation;
CREATE TABLE hr.emp_401k_participation (
     employee_id            NUMBER(5)
    ,plan_status            CHAR(1)
    ,plan_start_date        DATE
    ,plan_change_date       DATE
    ,plan_total_amount      NUMBER(15,2)
    ,last_contrib_amount    NUMBER(15,2)
    ,last_contrib_date      DATE)
;

-- Create an external table definition for processing employee 401(k) 
-- contribution detail data
DROP TABLE hr.xt_401k_feed;
CREATE TABLE hr.xt_401k_feed (
     employee_id            NUMBER(5)
    ,plan_status            CHAR(1)
    ,plan_change_date       DATE
    ,contribution_amount    NUMBER(15,2)
    ,contribution_date      DATE)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
     DEFAULT DIRECTORY exttabs
     ACCESS PARAMETERS (
         RECORDS DELIMITED BY newline
         BADFILE      'emp_401k_2005Q3.bad'
         DISCARDFILE  'emp_401k_2005Q3.dis'
         LOGFILE      'emp_401k_2005Q3.log'
         FIELDS TERMINATED BY ","
         OPTIONALLY ENCLOSED BY '"' (
              employee_id INTEGER EXTERNAL(5),
             ,plan_status CHAR(1)
             ,plan_change_date CHAR(8)
             ,contribution_amount INTEGER EXTERNAL (10)
             ,contribution_date CHAR(8))
         )
    LOCATION ('emp_401k_2005Q3.txt'))
    REJECT LIMIT UNLIMITED;

-- DML to merge data from 401(k) transaction file into table 
MERGE INTO hr.emp_401k_participation P
    USING (SELECT 
             employee_id
            ,contribution_date
            ,contribution_amount
            ,plan_status
            ,plan_change_date
             FROM hr.xt_401k_feed) F
       ON (P.employee_id = F.employee_id)
     -- If an employee entry exists:
     WHEN MATCHED THEN
        -- If the employee is not marked as (T)erminated, then accumulate the
        -- contribution amount
        UPDATE
           SET P.contrib_amount = (P.contrib_amount + F.contribution_amount)
        -- If the employee is marked as (T)erminated, remove the entries
        DELETE WHERE F.plan_status = 'T'
     -- If no employee entry exists, then add a new entry
     WHEN NOT MATCHED THEN
        INSERT (
             P.employee_id
            ,P.contrib_amount
            ,P.contrib_date
            ,P.status
        )
        VALUES (
             F.employee_id
            ,F.contribution_amount 
            ,F.contribution_date
            ,F.plan_status
        );

----- 
-- Listing 1.2: Getting information directly from the root node with
--              CONNECT_BY_ROOT and SYS_CONNECT_BY_PATH
-----
COL emp_name            FORMAT A24          HEADING 'Employee Name'
COL top_node_name       FORMAT A12          HEADING 'Top Of Node'
COL lvl_ind             FORMAT 999          HEADING 'Lvl'
COL node_path           FORMAT A24          HEADING 'Node Path'

-- Show all employees starting at the top of the hierarchy 
SELECT 
     (last_name || ', ' || first_name) emp_name
    ,CONNECT_BY_ROOT last_name top_node_name
    ,LEVEL lvl_ind
    ,SYS_CONNECT_BY_PATH (last_name, '->') node_path
  FROM 
     hr.employees
 START WITH employee_id = 100
 CONNECT BY PRIOR employee_id = manager_id
 ORDER SIBLINGS BY employee_id;

>>> Results:

Employee Name            Top Of Node   Lvl Node Path
------------------------ ------------ ---- ----------------------------------
King, Steven             King            1 ->King
Kochhar, Neena           King            2 ->King->Kochhar
Greenberg, Nancy         King            3 ->King->Kochhar->Greenberg
Faviet, Daniel           King            4 ->King->Kochhar->Greenberg->Faviet
...
<<rows omitted>>
...
Campbell, John           King            2 ->King->Campbell
Asimov, Isaac            King            3 ->King->Campbell->Asimov
Heinlein, Robert         King            3 ->King->Campbell->Heinlein
Bradbury, Ray            King            3 ->King->Campbell->Bradbury
Ellison, Harlan          King            3 ->King->Campbell->Ellison

-- Show all employees starting at Employee #901
SELECT 
     (last_name || ', ' || first_name) emp_name
    ,CONNECT_BY_ROOT last_name top_node_name
    ,LEVEL lvl_ind
    ,SYS_CONNECT_BY_PATH (last_name, '->') node_path
  FROM 
     hr.employees
 START WITH employee_id = 901
 CONNECT BY PRIOR employee_id = manager_id
 ORDER SIBLINGS BY employee_id;

>>> Results:

Employee Name            Top Of Node   Lvl Node Path
------------------------ ------------ ---- --------------------------------
Campbell, John           Campbell        1 ->Campbell
Asimov, Isaac            Campbell        2 ->Campbell->Asimov
Heinlein, Robert         Campbell        2 ->Campbell->Heinlein
Bradbury, Ray            Campbell        2 ->Campbell->Bradbury
Ellison, Harlan          Campbell        2 ->Campbell->Ellison

----- 
-- Listing 1.3: Determine whether a row has no descendant nodes using the
--              new CONNECT_BY_ISLEAF pseudo-column to work
-----
COL emp_id              FORMAT 99999        HEADING 'EmpID'
COL emp_name            FORMAT A24          HEADING 'Employee Name'
COL lvl_ind             FORMAT 999          HEADING 'Lvl'
COL has_kids            FORMAT 9999         HEADING 'Has|Kids'
COL node_path           FORMAT A36          HEADING 'Node Path'

SELECT 
     employee_id emp_id
    ,(last_name || ', ' || first_name) emp_name
    ,LEVEL lvl_ind
    ,DECODE(CONNECT_BY_ISLEAF, 1, 'No', 'Yes') has_kids
    ,SYS_CONNECT_BY_PATH (last_name, '->') node_path
  FROM 
     hr.employees
   START WITH employee_id = 901
 CONNECT BY PRIOR employee_id = manager_id
 ORDER SIBLINGS BY employee_id;

>>> Results:

 EmpID Employee Name             Lvl Kid Node Path
------ ------------------------ ---- --- ------------------------------------   
   901 Campbell, John              1 Yes ->Campbell                             
   902 Asimov, Isaac               2 No  ->Campbell->Asimov                     
   903 Heinlein, Robert            2 No  ->Campbell->Heinlein                   
   904 Bradbury, Ray               2 No  ->Campbell->Bradbury                   
   905 Ellison, Harlan             2 No  ->Campbell->Ellison                    


----- 
-- Listing 1.4: Detect rows in a hierarchy that "loop back" by interrogating
--              the new CONNECT_BY_ISCYCLE pseudo-column
-----
COL emp_id              FORMAT 99999        HEADING 'EmpID'
COL emp_name            FORMAT A24          HEADING 'Employee Name'
COL mgr_id              FORMAT 99999        HEADING 'MgrID'
COL mgr_name            FORMAT A24          HEADING 'Manager Name'
COL loopback            FORMAT 99999        HEADING 'Loop|Back?'
COL lvl_id              FORMAT 999          HEADING 'Lvl'

SELECT 
     employee_id emp_id
    ,(last_name || ', ' || first_name) emp_name
    ,manager_id mgr_id
    ,CONNECT_BY_ROOT last_name mgr_name
    ,CONNECT_BY_ISCYCLE loopback
    ,(LEVEL-1) lvl_id
  FROM 
     hr.employees
   WHERE CONNECT_BY_ISCYCLE > 0
 CONNECT BY NOCYCLE PRIOR employee_id = manager_id
 ORDER SIBLINGS BY employee_id;

>> Results:
                                                      Loop          
 EmpID Employee Name             MgrID Manager Name              Back?  Lvl     
------ ------------------------ ------ ------------------------ ------ ----     
   901 Campbell, John              902 Heinlein                      1    2     
   902 Asimov, Isaac               903 Campbell                      1    2     
   903 Heinlein, Robert            901 Asimov                        1    2     


----- 
-- Listing 1.5: Set up tables and objects to demonstrate the new Oracle 10g
-- COLLECTION functions
----- 

-- Create a table to store the phone number components in "normal" columns
CREATE TABLE hr.telephone_nbrs (
     comm_id        NUMBER(5)    PRIMARY KEY
    ,country        VARCHAR2(3)
    ,areacode       VARCHAR2(3)
    ,prefix         VARCHAR2(3)
    ,suffix         VARCHAR2(4)
    ,full_phone_nbr VARCHAR2(13)
    ,description    VARCHAR2(64)
);

-- Load the table with test data
INSERT INTO hr.telephone_nbrs 
VALUES (1, '001', '312', '555', '1212', '0013125551212', 'Chicago City Information');
INSERT INTO hr.telephone_nbrs 
VALUES (2, '001', '630', '555', '1212', '0013125551212', 'Chicago Western Suburbs Information');
INSERT INTO hr.telephone_nbrs 
VALUES (3, '001', '708', '555', '1212', '0013125551212', 'Chicago Collar Suburbs Information');
INSERT INTO hr.telephone_nbrs 
VALUES (4, '001', '773', '555', '1212', '0013125551212', 'Chicago Loop Information');
INSERT INTO hr.telephone_nbrs 
VALUES (5, '001', '847', '555', '1212', '0013125551212', 'Chicago Northwest Suburbs Information');

COMMIT;

-- Create a new TYPE for storing telephone numbers in a nested table column
CREATE OR REPLACE TYPE hr.phones
    AS TABLE OF VARCHAR2(13);

-- Create a table with a nested table column to store multiple phone numbers 
-- for a single location
CREATE TABLE hr.location_comm_ids (
     location_id    NUMBER(5)   PRIMARY KEY
    ,phone_nbrs     hr.phones
) NESTED TABLE phone_nbrs 
   STORE AS nst_phone_nbrs;

-- Load the table with test data
INSERT INTO hr.location_comm_ids (location_id, phone_nbrs)
VALUES (1500, hr.phones('014155551212','014155551313','014158574309'));
INSERT INTO hr.location_comm_ids (location_id, phone_nbrs)
VALUES (1600, hr.phones('019085551212','019085551313','019088574309'));
INSERT INTO hr.location_comm_ids (location_id, phone_nbrs)
VALUES (2200, hr.phones('1102536548121','1102536548121','1102536548121'));

COMMIT;

----- 
-- Listing 1.6: Illustrate the COLLECT, CARDINALITY, and SET functions
-----

-- Illustrate the use of the COLLECT function to return a nested
-- table collection for all rows returned when a "normal" column
-- is queried

SELECT
    CAST(COLLECT(full_phone_nbr) 
              AS hr.phone_list_typ)
  FROM hr.telephone_nbrs;

>>> Results:

CAST(COLLECT(FULL_PHONE_NBR)ASHR.PHONE_LIST_TYP)
-----------------------------------------------------------------
PHONE_LIST_TYP('0013125551212', '0013125551212', '0013125551212',
'0013125551212', '0013125551212')

-- Use the CARDINALITY function to return the number of entries in
-- a nested table
SELECT
     location_id "Location"
    ,CARDINALITY(phone_nbrs) "Cardinality"
  FROM hr.location_comm_ids;

>>> Results:

Location   Cardinality
---------- -----------
      1500           3
      1600           3
      2200           3

-- Demonstrate the use of the SET function to return only the
-- distinct values in a nested table
SELECT
     location_id        "Location"
    ,SET(phone_nbrs)    "Unique Values"
  FROM hr.location_comm_ids
 WHERE location_id = 2200;

>>> Results:

Location   Unique Values
---------- ---------------------------
      2200 PHONES('1102536548121')