The Oracle + PHP Cookbook

How to use Oracle collections to improve the efficiency of PHP scripts that feature multi-row inserts

Downloads for this article:

Oracle Database 10g Express Edition

Zend Core for Oracle

PHP Web developers often need to create scripts that require inserting multiple rows of data to one or more database tables during the execution of one script. For example, in order to insert all the items from a customer's shopping cart into the appropriate table in the database, the script would have to execute one INSERT statement for each item in the cart. Depending on the amount of data inserted, this operation may become a bottleneck that slows the application and degrades the user's experience.

In this Oracle+PHP Cookbook recipe, you'll learn how to increase the efficiency of multi-insert scripts by using Oracle stored procedures and collections with PHP's OCI8 extension.

Traditional Approach

Let's consider a hypothetical e-commerce application that relies on the following Oracle tables: Customers, Orders, Items, and Order_Items. The Customers table holds the list of customers, the Items table holds the inventory, the Orders table lists each customer's order details, and the Order_Items table contains the list of items per order. Refer to the ERD below for more details:

Here is the SQL script to create the tables above:

CREATE TABLE CUSTOMERS ( 
 CUSTOMER_ID NUMBER NOT NULL, 
 CUSTOMER_NAME VARCHAR2 (100) NOT NULL, 
 CONSTRAINT PK_CUSTOMERS
 PRIMARY KEY ( CUSTOMER_ID ) ) ; 
 /
 CREATE TABLE ITEMS ( 
 ITEM_ID NUMBER NOT NULL, 
 ITEM_DESCRIPTION VARCHAR2 (50) NOT NULL, 
 ITEM_PRICE NUMBER NOT NULL, 
 CONSTRAINT PK_ITEMS
 PRIMARY KEY ( ITEM_ID ) ) ; 
 /
 CREATE TABLE ORDERS ( 
 ORDER_ID NUMBER NOT NULL, 
 ORDER_DATE DATE NOT NULL, 
 CUSTOMER_ID NUMBER, 
 CONSTRAINT PK_ORDERS
 PRIMARY KEY ( ORDER_ID ) ) ; 
 
 ALTER TABLE ORDERS ADD CONSTRAINT FK_CUSTOMER
 FOREIGN KEY (CUSTOMER_ID) 
 REFERENCES CUSTOMERS (CUSTOMER_ID) ;
 /
 CREATE TABLE ORDER_ITEMS ( 
 ORDER_LINE_ID NUMBER NOT NULL, 
 ORDER_ID NUMBER NOT NULL, 
 ITEM_ID NUMBER NOT NULL, 
 CONSTRAINT PK_ORDER_ITEMS
 PRIMARY KEY ( ORDER_LINE_ID ) ) ; 
 
 ALTER TABLE ORDER_ITEMS ADD CONSTRAINT FK_ITEM
 FOREIGN KEY (ITEM_ID) 
 REFERENCES ITEMS (ITEM_ID) ;
 
 ALTER TABLE ORDER_ITEMS ADD CONSTRAINT FK_ORDER
 FOREIGN KEY (ORDER_ID) 
 REFERENCES ORDERS (ORDER_ID) ;
 /

And a sequence to make it simple to come up with a unique primary key whenever a row is inserted in the Order_Items table:

CREATE SEQUENCE ORDER_LINE_SQ
 INCREMENT BY 1;
 /

As you can see, every time the customer finalizes the order we have to insert order details into the Orders table and insert a row for every item in the shopping cart into the Order_Items table. Typically, a multi-insert operation like this would be handled by executing theINSERT statement in a loop:

<?php 
 $db = oci_connect('SCOTT','TIGER') or die; 
 //let's assume that the Items table contains 15 items referenced by the ids from 1 to 15 and 
 //the customer has purchased every item 
 $arr_order_items = array(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15); 
 //for simplicity the code that populates the Orders table is omitted 
 $order_id = 1; //order id from the Orders table 
 foreach ($arr_order_items as $item) 
 { 
 $sql = "INSERT INTO ORDER_ITEMS (order_line_id,order_id,item_id) 
 VALUES (ORDER_LINE_SQ.nextval,".$order_id.",".$item.")"; 
 $stmt = oci_parse($db,$sql); 
 oci_execute($stmt); 
 }

While this approach is a perfectly fine way to handle multiple inserts (except for starting a transaction would help to ensure the integrity of the data being inserted), executing every SQL statement through the OCI8 wrapper would require a lot of repeated communication between the database and the Web server, which may get expensive in terms of computing resources. A better approach would be to call the database once and let Oracle handle all the inserts internally. Let's see how this can be done with Oracle collections.

Oracle Collections

Collections are PL/SQL structures that can be used in Oracle in the same manner that arrays can be used in PHP. Oracle supports three types of collections: Index-by tables, nested tables, and VARRAYs.

Index-by tables are the most basic type of Oracle collections and are also called PL/SQL tables. Index-by tables have no upper bounds and can only exist in PL/SQL and not in the database. New elements can be added by assigning the value to the key that does not yet exist in the collection.

Nested tables are similar to Index-by tables, except can exist in the database table, for example, as a column in the table.

Finally, VARRAYs is the third type of Oracle collections. Similarly to nested tables VARRAYs can exist in both PL/SQL and in the database, however, VARRAY's must have their upper bound specified upon creation. Unlike Index-by and nested tables, VARRAY preserves the index of the elements and does not allow deletions. Since you don't need to manipulate the data in the collection (i.e. to add, delete, or move the elements once the collection is created), you'll use VARRAY here.

Creating the collection and procedure. In order to let the database handle all the work, you have to do two things: Define the collection type, so it can be referenced in tables and procedures; and create the procedure that will read collection's content and insert the data in the Order_Items table.

Here's how to define the collection type in Oracle:

CREATE OR REPLACE TYPE v_arr AS VARRAY(100) OF NUMBER;

This code simply defines the type v_arr that can hold up to 100 of numeric elements. Once defined, this type will be available to any procedure or table in the same schema (or a package if defined within the package).

The next step is to create the procedure:

CREATE OR REPLACE PROCEDURE update_order(order_id IN NUMBER,arr_items IN v_arr) IS
 BEGIN
 FOR i IN 1 .. arr_items.count LOOP
 INSERT INTO ORDER_ITEMS (order_line_id,order_id,item_id) VALUES
 (ORDER_LINE_SQ.nextval,order_id,arr_items(i));
 END LOOP;
 END;
 /

As you can see, the procedure above requires two parameters: the order id, which is shared between all the items within the same order, and the collection arr_itmes of type v_arr. This collection will hold the list of ids of all the items that come with given order. When the procedure is being executed it simply loops through the content of the collection and performs the insert statement for every element until it reaches the end of the collection.

Executing the procedure from PHP. Finally, you need to modify the PHP code to make it call and execute the above procedure. Assume that another customer has just created a new order with the order id = 2.

$order_id = 2; //order id from the Orders table

The following statement allocates a new OCI8 collection object:

$collection = oci_new_collection($db,"V_ARR");

Here $db refers to the currently opened connection handle and V_ARR is the named data type of the collection (must be uppercase). oci_new_collection function also features the third optional schema parameter, which must be used if the schema the collection is declared in is different from the default schema of the connection. By default, oci_new_collection will use the name of the current user as the value for the schema.

After the collection object is created, you need to populate it with values:

foreach
($arr_order_items as $item) 
{    
$collection->append($item);
}

The next line simply prepares the procedure for execution:

$stmt = oci_parse($db,"BEGIN UPDATE_ORDER(:order_id,:arr_items); END;");

Then you just bind the PHP variables to Oracle placeholders:

oci_bind_by_name($stmt,':order_id',$order_id);
 oci_bind_by_name($stmt,':arr_items',$collection,-1,OCI8_B_NTY);

Note the second line, which is where you bind the OCI8 collection object to the arr_items parameter of the procedure. Here, -1 instructs the driver to use the current length of the variable as the maximum length. OCI8_B_NTY constant indicates that we are using the named datatype. (If you are using PHP version 4.x or earlier you should use OCI8_B_SQLT_NTY instead.)

Finally, the statement is executed by calling the oci_execute function:

oci_execute($stmt);

Once the procedure is executed you should see 15 new rows inserted into the Order_Items table.

Note that OCI8 offers a variety of other useful functions to manipulate Oracle collections. For example, assignElem function can be used to assign values to particular elements within the collection; getElem returns current value of an element within the collection, max returns the maximum possible number of elements, and size return the current number of elements in the collection. Finally, trim function can be used to trip a specific number of elements from the end of the collection and free function to free the resources associated with the collection object. Refer to the PHP Manual for complete list of collection functions.

If you are using PHP 5.1.2 you can implement the above functionality without creating the collection object by using OCI8 new function oci_bind_array_by_name. (However, as of this writing, this function hasn't been documented, so use it at your own risk.) Here's how the code would like if you used oci_bind_array_by_name:

$stmt = oci_parse($db,"BEGIN UPDATE_ORDER(:order_id,:arr_items); END;");
 oci_bind_by_name($stmt,':order_id',$order_id);
 oci_bind_array_by_name($stmt,':arr_items',$arr_order_items,15,15,SQLT_NUM);
 oci_execute($stmt);

Here SQLT_NUM indicates that we are using the array of numeric elements.

Conclusion

The method described in this recipe offers a simple way of increasing the efficiency of PHP scripts that require to executing multipleINSERT statements. Of course, for relatively small data sets the difference between two methods presented in the article will be minimal if any. Below are the actual benchmarking results that were obtained by running both scripts side by side 10 times in a row:

As you can see, using collections resulted in a slightly better performance. Note, however, that the dependence between the number of records inserted and the performance increase is not linear; therefore, this method will prove much more useful when working with relatively large data sets.

Finally, if you are considering adopting this method for production environment, I highly recommended that you use transactions. If you wish to speed up the heavy inserts even further, consider using bulk binding.

Mikhail Seliverstov is a Web Programming Team Leader for the office of Development and Alumni Relations at McGill University in Montreal. For the past three years, he has helped lead a large Oracle+PHP development project as a principal application architect and developer.