Your organization is going to gather information about all the employees’ families in order to keep track of the number of the employee kids, kids’ names and their date of birth. You have been assigned to use the nested object tables for the employees’ family. You should use the “CONSTRUCTOR” operator to refer to the nested table with the “THE” syntax. Your client should be able to insert a record into the nested table directly or update the nested table, and be able to get individual elements from a nested object table using the object columns.

-- Hands-On 09 (Defining Objects and Collection Types) 

SET ECHO ON 
CLEAR SCR
-- Connect to SQLPLUS as the iself user.
--
pause

CONNECT iself/schooling
pause

CLEAR SCR
-- Start your session by setting the pagesize to 55 and
-- the linesize to 100.

-- Also, change the object_name column format to only 
-- 20 character long.

pause

SET PAGESIZE 55 LINESIZE 100
COLUMN object_name FORMAT a20

pause

CLEAR SCR
-- Create an object type with two columns to hold the
-- employee's child name and date of brith. 

-- Name it employee_kids.
--
pause

CREATE TYPE employee_kids AS OBJECT ( 
NAME VARCHAR2(30), 
dob DATE 
) 
/
pause

CLEAR SCR
-- Create a table type using employee_kids and 

-- name it employee_kids_table.
--
pause

CREATE TYPE employee_kids_table IS TABLE OF employee_kids
/
pause 

CLEAR SCR
-- Query all of the object types that you created.
--
pause

SELECT object_name FROM user_objects 
WHERE object_type = 'TYPE'
/ 
pause

CLEAR SCR 
-- Create the emp_family table containing the kids column
-- with a type of employee_kids_table. 
--
pause

CREATE TABLE emp_family 
(empno NUMBER, 
kids employee_kids_table) 
NESTED TABLE kids STORE AS nested_employee_kids_table 
/ 
-- This is an example of a nested table column.

-- In this column, you will store the name and
-- birth of an employees' child.
--
pause


CLEAR SCR 
-- Insert two rows into the emp_family table.
--
pause 

INSERT INTO emp_family VALUES (7900, employee_kids_table()) 
/ 
INSERT INTO emp_family VALUES (7788, employee_kids_table()) 
/ 
COMMIT;
-- Note that the constructor creates an empty 
-- nested table as opposed to leaving it null.

-- Notice that without using the constructor, it is not 
-- possible to refer to the nested table with the "THE" clause.
--
pause 

CLEAR SCR
-- Insert another row into the emp_family table,
-- while specifying three employee kids for the 
-- nested table at the same time. 
--
pause

INSERT INTO emp_family VALUES 
(7902, 
employee_kids_table( employee_kids('David','08-AUG-01'), 
employee_kids('Peter','10-JUN-88'), 
employee_kids('Mark','30-OCT-92') 
) 
) 
/ 
pause

CLEAR SCR
-- Now, query the emp_family table. 
--
pause

SELECT * FROM emp_family
/
-- Notice that the names David, Peter, and Mark were added.
pause

CLEAR SCR
-- Describe the emp_family table 
-- 
pause

desc emp_family
pause

CLEAR SCR
-- Now insert directly into the nested table. 
-- The "THE" sub-query is used to identify the
-- nested table to INSERT INTO. 
--
pause

INSERT INTO THE(SELECT kids FROM emp_family 
WHERE empno = 7900) 
VALUES ('Fred','10-SEP-89') 
/ 
INSERT INTO THE(SELECT kids FROM emp_family 
WHERE empno = 7900) 
VALUES ('Sue','10-DEC-99') 
/ 
commit
/
-- Note that when using this method only one row may be
-- inserted into the nested table at a time - as 
-- would also be the case if you were inserting
-- rows into any table. 
--
pause

CLEAR SCR
-- Set off the record separator and then query the emp_family table. 
-- 
pause

SET RECSEP OFF 
COLUMN KIDS FORMAT A55 WORD


SELECT * FROM emp_family 
/ 
-- Notice that the names FRED and SUE were added to the KIDS nested table.
pause


CLEAR SCR
-- Now query directly from the nested table. 

-- The "THE" sub-query is used to identify the
-- nested table to query.

-- Query the childern names of employee number 7788.
--
pause

SELECT name 
FROM THE(SELECT kids FROM emp_family WHERE empno = 7788) 
/
-- Notice that this employee does not have any kids.

pause

CLEAR SCR 
-- Use an UPDATE statement, to change the whole 
-- nested table for a given row, in the 
--"emp_family" table.
-- 
pause 

UPDATE emp_family 
SET kids = employee_kids_table( 
employee_kids('Sara','08-OCT-88')) 
WHERE empno = 7788 
/ 
COMMIT
/
pause 


CLEAR SCR
-- Now, query the emp_family table.
--
pause

select * from emp_family 
/
-- Notice that the KIDS nested table was replaced and 
-- Sara was inserted into the kids table.
pause 

CLEAR SCR
-- Now, let's update a single element of the nested table.
-- Update the David name to Dana where the employee 
-- number is 7902. 

pause

UPDATE THE(SELECT kids FROM emp_family WHERE empno=7902) 
SET name = 'Dana' 
WHERE name = 'David' 
/ 
COMMIT;
-- The "THE" sub-query is used to identify the KIDS
-- nested table. 

pause


CLEAR SCR
-- Query the emp_family table to see the changes.
--
pause

SELECT * FROM emp_family 
/
-- David was changed to Dana.
pause


CLEAR SCR
-- Add a unique index to the nested table. 

-- Note that we must include the nested_table_id column
-- in this case. 
--
pause

CREATE UNIQUE INDEX i_nested_employee_kids_table 
ON nested_employee_kids_table(nested_table_id,name) 
/ 
pause 


CLEAR SCR
-- Insert the duplicated record into the nested table.
-- 
pause

INSERT INTO THE(SELECT kids FROM emp_family WHERE empno = 7900) 
VALUES ('Sue','10-DEC-99')
/

-- As you can see, you get a UNIQUE CONSTRAINT VIOLATION
-- message.
--
Pause

CLEAR SCR
-- Drop the emp_family, employee_kids_table, and employee_kids tables.
--
pause

DROP TABLE emp_family 
/ 
DROP TYPE employee_kids_table 
/ 
DROP TYPE employee_kids 
/ 

-- You have dropped the objects so that you can practice this Hands-On
-- over and over again.
pause



CLEAR SCR
-- Now, you should practice this excercise over and over
-- until you become a master at it.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck!
pause