Write SQL to insert Rows using a Sub Query

In the case of object tables that have references to other object tables, the INSERT statement is based on a query to the referencing object. For example, within the SALE_HEADER table, the CUST_REF column is associated with CUSTOMER_TYPE, which is maintained within CUSTOMER_OBJ_TABLE. To insert a record into the SALE_HEADER table, you must query CUSTOMER_OBJ_TABLE for values within the CUST_REF column. An example of such an INSERT statement is:

INSERT INTO sale_header 
VALUES 
(
 500,(SELECT REF(C) FROM customer_obj_table 
 c WHERE cust_id = 1),89.45, 3.58, 15, 108.03, '01-AUG-00', 
 DETAIL_TABLE()
);

In this example, the INSERT statement has a sub query, which retrieves the reference information from the CUSTOMER_OBJ_TABLE table that is maintained within the CUST_REF column in the SALE_HEADER table. Let us look at another example that inserts a record into the PET_CARE_LOG_OBJ_TABLE table. The PRODUCT_ID column has a reference to PRODUCT_OBJ_TABLE. One example of an INSERT statement for inserting a record into this table looks like the following:

INSERT INTO pet_care_log_obj_table VALUES(
 (SELECT REF(p) FROM product_obj_table p 
    WHERE product_id = 34),
    to_date('05-JAN-00 12:30PM','dd-mon-yy hh:miPM'), 
    'JANET', 'New puppy needs vaccination.’, 
    to_date('16-JAN-00 01:30PM','dd-mon-yy hh:miPM')
);

This example is just another way of inserting into the PET_CARE_LOG_OBJ_TABLE table. Here, we have a query to select a reference for the PRODUCT_ID column from the PRODUCT_OBJ_TABLE table. This example differs from the example in the previous lesson, because this query is for getting the reference information only. Take a look at the diagram below for the syntax and an example of inserting records into object tables by using a sub query:

Location 1	The INSERT statement specifying the table
Location 2	The values of the columns in the order of the definition of columns during object table creation
Location 3	The sub query to select reference data
Location 4	The other column data
Location 5	The end of the INSERT statement

The following diagram describes inserting rows using the PL/SQL Programming language.

INSERT INTO sale_header
VALUES (500, 
(SELECT REF(C) FROM customer_obj_table 
c where CUST_ID = 1),
89.45,3.58,15,108.03,'01-aug-00',
detail_table()
);

Location 1	The INSERT statement specifying the table
Location 2	The values of the columns in the order of the definition of columns during object table creation
Location 3	The sub query to select reference data
Location 4	The other column data
Location 5	The end of the INSERT statement

A compound DML trigger is useful for accumulating rows destined for a second table so that you can periodically bulk-insert them. To get the performance benefit from the compound trigger, you must specify BULK COLLECT INTO in the FORALL statement (otherwise, the FORALL statement does a single-row DML operation multiple times). The following section contains more information about using the BULK COLLECT clause using the FORALL statement.

Here is a working example of a bulk insert. It compares a traditional insert (for i in) to the bulk insert (forall i):

-- create a test table for the bulk insert
drop table test_objects;

create table  
test_objects  
tablespace users as 
select object_name, object_type from dba_objects;

-- Populate the table into a array using bulk collect
DECLARE
TYPE t_tab IS TABLE OF test_objects%ROWTYPE;
objects_tab t_tab := t_tab();
start_time number; end_time number;
BEGIN
-- Populate a collection  
SELECT *  
BULK COLLECT  
INTO  
objects_tab  
FROM test_objects;

-- Time the population of the table with a bulk insert
EXECUTE IMMEDIATE 'TRUNCATE TABLE test_objects';
Start_time := DBMS_UTILITY.get_time;
FORALL i in objects_tab.first .. objects_tab.last
INSERT INTO test_objects VALUES objects_tab(i);
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE('Bulk Insert: '||to_char(end_time-start_time));

-- Populate the table without a bulk insert
EXECUTE IMMEDIATE 'TRUNCATE TABLE test_objects';
Start_time := DBMS_UTILITY.get_time;
FOR i in objects_tab.first .. objects_tab.last LOOP
INSERT INTO test_objects (object_name, object_type)
VALUES (objects_tab(i).object_name, objects_tab(i).object_type);
END LOOP;
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE('Conventional Insert: '||to_char(end_time-start_time));
COMMIT;
END; 
/

In the next lesson, we will look at writing DML statements to update the records within object tables.