|
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 oracle user.
--
pause
CONNECT oracle/learning
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 |
