/*
|| 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')