|
"I shall not
waste my days in trying to prolong them." - Ian L. Fleming
(1908-1964) |
Read
first then play the video:
SQL-VIDEO -Defining
Objects and Collection Types
Defining Objects and
Collection Types
Hands-On introduction
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.
Your assignments are:
1- To use nested object
tables,
2- To create a record
object type,
3- To create a table
objects type,
4- To create a table for
employee"s table (EMP_FAMILY),
5- To insert record into
table,
6- To query information
from table,
7- To update information
from table, and
8- To delete information
from table.
Connect to SQLPLUS as the iself user.
SQL> CONNECT iself/schooling
Start your session by
setting the pagesize to 55 and the linesize to 100. Also, change the
object_name column format to only 20 characters long.
SQL> SET PAGESIZE 55 LINESIZE 100
SQL> COLUMN object_name FORMAT a20
This section contains
examples of creating and using user-defined object types and
collection types in the Oracle database.
CREATE TYPE " AS OBJECT
"
Create an object type with
two columns to hold the employee's child name and date of birth and
name it employee_kids.
SQL> CREATE TYPE
employee_kids AS OBJECT (
NAME VARCHAR2(30),
dob DATE
)
SQL> /
CREATE TYPE " IS TABLE OF
"
Create a table type using
employee_kids and name it employee_kids_table.
SQL> CREATE TYPE employee_kids_table IS
TABLE OF employee_kids
SQL> /
Query all of the object
types that you created.
SQL> SELECT object_name FROM user_objects
WHERE object_type = 'TYPE'
SQL> /
CREATE TABLE " NESTED
TABLE "
Create the emp_family table
containing the kids column with a type of employee_kids_table.
SQL> CREATE TABLE emp_family
(empno NUMBER,
kids employee_kids_table)
NESTED TABLE kids STORE AS nested_employee_kids_table
SQL> /
This is an example of a nested table column. In this
column, you will store the name and birth of an employees' child.
INSET INTO " VALUES (object_type(),
")
Insert two rows into the
emp_family table.
SQL> INSERT INTO emp_family VALUES (7900,
employee_kids_table())
SQL> /
SQL> INSERT INTO emp_family VALUES (7788, employee_kids_table())
SQL> /
SQL> 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.
Insert another row into the
emp_family table, while specifying three employee kids for the nested
table at the same time.
SQL> 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')
)
)
SQL> /
Now, query the emp_family
table.
SQL> SELECT * FROM emp_family
SQL> /
Notice that the names David, Peter, and Mark were added.
Describe the emp_family
table
SQL> desc emp_family
INSERT using "THE"
sub-query
Now insert directly into
the nested table. The "THE" sub-query is used to identify
the nested table to INSERT INTO.
SQL> INSERT INTO THE(SELECT kids FROM
emp_family
WHERE empno = 7900)
VALUES ('Fred','10-SEP-89')
SQL> /
SQL> INSERT INTO THE(SELECT kids FROM emp_family
WHERE empno = 7900)
VALUES ('Sue','10-DEC-99')
SQL> /
SQL> commit
SQL> /
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.
Set off the record separator and then query the emp_family table.
SQL> SET RECSEP OFF
SQL> COLUMN KIDS FORMAT A55 WORD
SQL> SELECT * FROM emp_family
SQL> /
Notice that the names FRED and SUE were added to the KIDS
nested table.
Now query directly from the
nested table. The "THE" sub-query is used to identify the
nested table to query. Query the children names of employee number
7788.
SQL> SELECT name
FROM THE(SELECT kids FROM emp_family WHERE empno = 7788)
SQL> /
Notice that this employee does not have any kids.
UPDATE " SET " =
object_type()
Use an UPDATE statement, to
change the whole nested table for a given row, in the "emp_family"
table.
SQL> UPDATE emp_family
SET kids = employee_kids_table(
employee_kids('Sara','08-OCT-88'))
WHERE empno = 7788
SQL> /
SQL> COMMIT
SQL> /
Now, query the emp_family
table.
SQL> SELECT * FROM emp_family
SQL> /
Notice that the KIDS nested table was replaced and Sara was
inserted into the kids table.
UPDATE THE (SELECT ")
Now, let's update a single
element of the nested table. Update the David name to Dana where the
employee number is 7902.
SQL> UPDATE THE(SELECT kids FROM
emp_family WHERE empno=7902)
SET name = 'Dana'
WHERE name = 'David'
SQL> /
SQL> COMMIT;
The "THE" sub-query is used to identify the KIDS
nested table.
Query the emp_family table
to see the changes.
SQL> SELECT * FROM emp_family
SQL> /
David was changed to Dana.
CREATE UNIQUE INDEX "
Add a unique index to the
nested table. Note that we must include the nested_table_id column in
this case.
SQL> CREATE UNIQUE INDEX
i_nested_employee_kids_table
ON nested_employee_kids_table(nested_table_id,name)
SQL> /
Insert the duplicated
record into the nested table.
SQL> INSERT INTO THE(SELECT kids FROM
emp_family
WHERE empno = 7900)
VALUES ('Sue','10-DEC-99')
SQL> /
UNIQUE CONSTRAINT VIOLATION
As you can see, you get a
UNIQUE CONSTRAINT VIOLATION message.
DROP TYPE statement
Drop the emp_family,
employee_kids_table, and employee_kids tables.
SQL> DROP TABLE emp_family
SQL> /
SQL> DROP TYPE employee_kids_table
SQL> /
SQL> DROP TYPE employee_kids
SQL> /
You have dropped the objects so that you can practice this
Hands-On over.
|
"Always bear in
mind that your own resolution to succeed is more important than
any one thing." Abraham Lincoln (1809 - 1865) |
Questions:
Q: What is an object type?
Q: What is a collection
object?
Q: Create an object type
with two columns to hold the employee's child name and date of birth
and name it employee_kids .
Q: Create a table type
using employee_kids and name it employee_kids_table.
Q: Create the emp_family
table containing the kid"s column with a type of employee_kids_table.
Q: How do you insert a
record in the object type?
Q: What is the constructor?
Q: What is the "THE"
sub-query?
Q: How do you query a
record using the "THE" sub-query?
Q: What is a nested table?
Q: How do you insert a
record to a nested table?
Q: How do you update a
record to nested table?
Q: How do you add a unique
index to a nested table?
|