SQL

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

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 an object type with two columns to hold the employee's child name and date of birth and name it employee_kids.

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

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

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
 

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.
 

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.
 

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.
 

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

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?