PL/SQL   «Prev  Next»

Lesson 3Inserting rows into a Nested Table
ObjectiveWrite an insert command for a Nested Table

Inserting Rows into Nested Table

In this lesson, we will look at the different ways of inserting data into a nested table. Data can be inserted into a nested statement in two ways:
  1. Use a single Data Manipulation Language (DML) statement to insert data into the parent table and the child table (nested table).
  2. Write two DML statements, the first to insert data into the parent table with an empty record into the nested table, and the second to insert data into the nested table.

With the first technique, the INSERT statement for a nested table consists of specifying the nested table column name and the values for it. For example, let us insert a record into the SALE_HEADER table, which has the DETAIL_NEST column defined as a nested table. The INSERT statement looks like this: 

INSERT INTO SALE_HEADER 
SELECT 35, REF(C), 55, 1.65, 61.9, 5.25, 
to_date('12-dec-99 04:15PM','dd-mon-yy hh:mipm'),
DETAIL_TABLE(NULL, 12, 35.5) 
FROM CUSTOMER_OBJ_TABLE C 
WHERE CUST_ID = 1;

The statement above inserts NULL, 12, and 35.5 into PRODUCT_ID, ITEM_QTY, and DETAIL_AMOUNT columns within the nested table.
With the second technique, you can also insert data into a nested table by creating an empty nested table. Note that the constructor creates an empty nested table as opposed to leaving it NULL. We know this because when the table is queried, the result set shows that the nested table is initialized. Without using the constructor, you cannot refer to the nested table with the THE or TABLE syntax because the nested table will be NULL. For example, let’s use the INSERT statement and create an empty nested table record: 

INSERT INTO SALE_HEADER 
SELECT 35, REF(C), 55, 1.65, 61.9, 5.25, 
to_date('12-dec-99 04:15PM','dd-mon-yy hh:mipm'), 
  DETAIL_TABLE() 
FROM CUSTOMER_OBJ_TABLE C 
WHERE CUST_ID = 1;

Now that an empty nested table has been created, let us insert the data into the nested table by using the INSERT statement defined below: 
INSERT INTO TABLE (SELECT s.detail_nest FROM 
  SALE_HEADER S 
            WHERE s.sale_id = 35) 
SELECT REF(P), 1, 55
FROM PRODUCT_OBJ_TABLE P
WHERE product_id = 20;

The above statement first selects the particular record from the SALE_HEADER table, then inserts the data into the nested table by using a SELECT statement.
Each row within a nested table has a "hidden" column called nested_table_id. The value of this column is the same for each row within the nested table that corresponds to a single row within the main table. We can view this column by selecting it from the table: 

SELECT nested_table_id
FROM THE(select detail_nest FROM SALE_HEADER 
  where sale_id = 35);
The results of the query are: 
NESTED_TABLE_ID
----------------------------------------
DEE8F406C4B911D39B07000000000000

The following MouseOver describes the syntax for inserting data into a nested table:
insert-table-syntax Oracle PL/SQL
  1. The INSERT statement with the TABLE keyword
  2. The SELECT statement for selecting a particular record from the parent table
  3. The sub query to select reference and other column data
INSERT INTO TABLE (SELECT <nested table> FROM 
  <parent table name> <table alias>
  WHERE <clause>)

SELECT <column values>
FROM <reference table name> <reference table alias>
WHERE <clause> ;
The INSERT statement with the TABLE keyword

Syntax Inserting Data into Nested Table The following MouseOver shows an example of inserting data into a nested table:
Insert table Example
  1. The INSERT statement with the TABLE keyword
  2. The SELECT statement for selecting a particular record from the parent table
  3. The sub query to select reference and other column data
  4. The sub query to select reference and other column data
  5. The sub query to select reference and other column data

INSERT INTO TABLE (SELECT s.detail_nest FROM 
  SALE_HEADER s
  WHERE s.sale_id =36)
SELECT REF(P), 200, 56
FROM PRODUCT_OBJ_TABLE p
WHERE product_id =21;
The SELECT statement for selecting a particular record from the parent table


Constructor: Every time a user-defined data type is created, Oracle generates a method called the constructor method, which is a system-defined method. When invoked, the constructor method creates a new object based on the specification of the object type. Oracle names the new constructor method the same name as the new object type. The parameters are the same as the attributes named within the object type definition.
In the next lesson, you will learn more about inserting data into a varray.