RelationalDBDesignRelationalDBDesign


Table Modification   «Prev 

Inserting Rows in a Database Table Example

 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() );

This page contains a diagram that describes inserting rows using the PL/SQL Programming language
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

Using Compound DML Triggers with Bulk Insertion

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). For more information about using the BULK COLLECT clause with the FORALL statement,

Bulk insert Example

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; 
/