|
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 |
