Oracle + PHP Cookbook

PHPおよびOracleコレクションを使用した、1回のデータベース・コールによる複数行の挿入

Oracleコレクションを使用して、複数行を挿入するPHPスクリプトの効率を上げる方法

関連するダウンロード・リンク Oracle Database 10g Express Edition Zend Core for Oracle

2006年2月公開

PHP Web開発者にとって、1つのスクリプトの実行中に、データの複数の行を1つまたは複数のデータベース表に挿入するスクリプトの作成が必要なことは多々あります。 たとえば、顧客のショッピング・カートからすべての品目をデータベースの適切な表に挿入するために、スクリプトでカート内のすべての品目に対してINSERT文を実行しなければならない場合があります。 挿入するデータの量によっては、この操作がアプリケーションの動作を遅くし、ユーザー・エクスペリエンスを低下させるボトルネックとなる可能性があります。

このOracle+PHP Cookbookレシピでは、Oracleのストアド・プロシージャおよびコレクションと、PHPのOCI8拡張を使用して、複数挿入スクリプトの効率を上げる方法を学びます。

従来のアプローチ

Customers、Orders、Items、およびOrder_Itemsの各Oracle表に依存する、仮想の電子商取引アプリケーションを考えてみましょう。 Customers表には顧客のリストが、Items表には在庫が、Orders表には各顧客の注文の詳細情報が、Order_Items表には注文ごとの品目リストが含まれています。 詳細については、次のERDを参照してください。

次に、上記の表を作成するSQLスクリプトを示します。

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

CREATE SEQUENCE ORDER_LINE_SQ
INCREMENT BY 1;
/

<?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);
}

Oracleコレクション

コレクションとは、PHPで使用される配列と同じ方法で、Oracle Databaseで使用できるPL/SQL構造です。 Oracle Databaseでは、3種類のコレクションをサポートします。 索引付き表、ネストした表、およびVARRAYです。

索引付き表はOracleコレクションのもっとも基本的なタイプであり、PL/SQL表とも呼ばれます。 索引付き表には上限がなく、PL/SQL内にのみ存在することができ、データベースには存在できません。 新しい要素は、コレクション内にまだ存在していないキーに値を割り当てることで追加することができます。

ネストした表は索引付き表と似ていますが、データベース表内に、たとえば表内の列として存在できる点が異なります。

最後に、VARRAYはOracleコレクションの3番目のタイプです。 ネストした表と同様に、VARRAYはPL/SQLとデータベースの両方に存在することができますが、作成時に上限の指定が必要となります。 索引付きおよびネストした表とは異なり、VARRAYは要素の索引を保存し、削除は許可されません。 ここでは、コレクション内のデータを操作する（つまり、コレクションの作成後に要素を追加、削除、または移動する）必要がないため、VARRAYを使用します。

コレクションとプロシージャを作成します。データベースにすべての作業を処理させるためには、次の2つのことをする必要があります。 表およびプロシージャで参照できるように、コレクション・タイプを定義します。さらにコレクションのコンテンツを読み取り、そのデータをOrder_Items表に挿入するプロシージャを作成します。

Oracleでコレクション・タイプを定義する方法を示します。

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

次のステップでは、プロシージャを作成します。

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

PHPからプロシージャを実行します。最後に、上記のプロシージャをコールして実行するようにPHPコードを変更する必要があります。 別の顧客が新しい注文をorder id = 2で作成したと仮定します。

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

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

コレクション・オブジェクトを作成した後、値を移入する必要があります。

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

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

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

最後に、oci_execute関数をコールしてステートメントを実行します。

oci_execute($stmt);

OCI8では、Oracleコレクションを操作するさまざまな有用な関数が提供されることに注意してください。 たとえば、assignElem関数は、コレクション内の特定の要素に値を割り当てるのに使用することができます。getElemはコレクション内の要素の現在の値を返します。maxは要素の最大数を返し、sizeはコレクション内の要素の現在の数を返します。 最後に、trim関数はコレクションの最後から特定の数の要素を取り除くのに使用することができ、free関数はコレクション・オブジェクトに関連するリソースを解放します。 コレクション関数の完全なリストはPHPマニュアルを参照してください。

PHP 5.1.2を使用している場合、OCI8の新しい関数oci_bind_array_by_nameを使用することによって、コレクション・オブジェクトを作成せずに、上記の機能を実装することができます （ただし、執筆時点では、この関数はドキュメント化されていないので、自己責任で使用してください）。 次に、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);

結論

このレシピで説明している手法は、複数の INSERT文を実行する必要のあるPHPスクリプトの効率を上げる簡単な方法を提供しています。 もちろん、比較的小さなデータ・セットの場合、この記事で示した2つの手法の違いは、あったとしても最小限でしょう。 次に、両方のスクリプトをそれぞれ連続して10回実行することにより取得された実際のベンチマーク結果を示します。

ご覧のとおり、コレクションを使用した方がわずかにパフォーマンスがよい結果になっています。 ただし、挿入するレコード数とパフォーマンスとの間の依存関係は比例ではありません。したがって、比較的大きなデータ・セットを操作するときに、この手法がより有用であることが証明されるでしょう。

最後に、本番環境でこの手法を採用することを検討している場合、トランザクションを使用することを強く推奨します。 大量の挿入をさらに高速にする場合には、バルク・バインドの使用を検討してください。

ご意見ご感想をお寄せください。