iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - SQL 

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 |

 

Lesson 16

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