OracleおよびPHPを使用したオンライン・レポートの作成
著者：Mikhail Seliverstov
OracleおよびPHPを使用してオンライン・レポートを作成するためのさまざまな技術
2006年7月公開
データベースによるレポート生成機能は、ほとんどのWebベース・アプリケーションにおいて"必須"の機能です。 複雑なレポートを作成する場合、多くの開発者は、複合的なデータ・セットを使用するという手段をとっていますが、こうしたデータ・セットは、単一のSQL問合せを実行するだけでは取得できないため、いくつかの細かい問合せをループ内で実行する必要があります。
通常はこの手法が適用されますが、ほかにも代わりとなる方法があります。 このレシピでは、変数バインディングとOracleのCURSOR式を使用した代替技術について学ぶとともに、従来の手法と比較した場合の評価もおこなっていきます。 コードはすべて記載されているため、テスト条件を複製したり、独自のベンチマークを実施したりすることもできます。
データ構造の設定
このレシピでは、OracleのCURSOR式を使用して一括データ挿入を迅速化する方法について示した『
PHPおよびOracleコレクションを使用した、1回のデータベース・コールによる複数行の挿入』レシピのサンプルを再使用します。 そこでは、仮想E-Commerce Webアプリケーションのサポートに使用するための単純なデータベース構造を作成しました。 今回のレシピでは、各注文に関するステータス変更（受注済→処理済→梱包済→出荷済）の追跡に使用できる別の表を追加します。 言い換えれば、追加するこの表を確認することで、特定の注文に関する現在のステータスとステータスの変更履歴を把握できるようになります。
次の図は、上記のレシピで作成したデータ構造です。
以下は、上記の表を作成する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 TABLE ORDER_TRACKING (
RECORD_ID NUMBER NOT NULL,
CURRENT_STATUS VARCHAR2 (100),
ORDER_ID NUMBER,
RECORD_DATE DATE,
CONSTRAINT PK_ORDER_TRACKING
PRIMARY KEY ( RECORD_ID ) ) ;
ALTER TABLE ORDER_TRACKING ADD CONSTRAINT FK_ORDER_TRACKING
FOREIGN KEY (ORDER_ID)
REFERENCES ORDERS (ORDER_ID) ;
CREATE SEQUENCE ORDER_LINE_SQ
INCREMENT BY 1;
/
CREATE SEQUENCE ORDER_STATUS_SQ
INCREMENT BY 1;
/
以下のシーケンスにより、Order_Items表に行が挿入されるたびに、一意の主キーを簡単に設定できます。
CREATE SEQUENCE ORDER_LINE_SQ
INCREMENT BY 1;
/
さらに、テスト・データを移入するためのコードを以下に示します。 ここでは、CUSTOMERS表には顧客CUSTOMER_ID = 1が事前に移入されており、ITEMS表には1から15までのITEM_IDをもつ15のアイテムが含まれていると仮定します。
//connect to the database using your login name and password
$schema = "my_schema";
$ password = "my_password";
$db = oci_connect('$schema, $password) or die;
$arr_order_items = array(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15); //all items on the list
$arr_status = array("Received","Processed","Packed","Shipped");
//create 200 orders for CUSTOMER_ID = 1
for ($order_id = 1;$order_id<=200;$order_id++) {
$sql = "INSERT INTO ORDERS (order_id,customer_id,order_date) VALUES (".$order_id.",1,SYSDATE)";
$stmt = oci_parse($db,$sql);
oci_execute($stmt);
//add 15 items to each order
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);
}
//populate status for each order
foreach ($arr_status as $status) {
$sql = "INSERT INTO ORDER_TRACKING (order_id,current_status,record_date) VALUES
(".$order_id.",'".$status."',SYSDATE)";
$stmt = oci_parse($db,$sql);
oci_execute($stmt);
}
}
この時点で、ORDERS表には顧客1によって200の注文が移入されます。各注文に対して、ORDER_ITEMS表内に15のアイテムがあり、ORDER_TRACKING表に4つのステータスが用意されています。
レポート要件
まず、現在の唯一の顧客（CUSTOMER_ID = 1）のみについて、受注履歴レポートを作成します。 レポートでは、顧客の注文に関する完全なリストが、2つのサブセクションに分けて表示されます。このうち1つのサブセクションには特定の注文に含まれるアイテムが示され、もう1つにはその注文に関するステータス更新履歴が一覧で表示されます。 以下のセクションでは、3種類の手法を使用して、このレポートを作成します。1つめは、ループ内で一連の独立したデータベース・コールを実行する方法、次に、同じ独立した問合せを変数バインディングにより実行する方法、そして最後に、単一の問合せでOracleのCURSOR式を使用する方法です。
単純な副問合せ： この手法は、複雑なレポートを作成する場合に従来もっともよく使用されている方法であるといえるでしょう。 このサンプルでは、特定の顧客IDに関する注文のリストを取得するための簡単な問合せを実行してから、注文リストをループし、ステータス変更履歴取得とアイテム・リスト取得の、2つの副問合せを実行します。 結果は連想配列$arr_ordersに格納され、あとでHTML表として表示されます。
$stmt = oci_parse($db,"SELECT order_id,order_date,customer_name FROM CUSTOMERS,ORDERS WHERE
ORDERS.customer_id = 1 AND ORDERS.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID");
oci_execute($stmt);
while ($orders = oci_fetch_assoc($stmt)) {
$arr_order_status = array(); // to temporarily store status history for given order id
$arr_order_items = array(); // to temporarily store item list for given order id
$arr_order_line = array(); // to temporarily store complete order information for given order id
$arr_order_line = array("ORDER_ID"=>$orders["ORDER_ID"],"ORDER_DATE"=>$orders["ORDER_DATE"],
"CUSTOMER_NAME"=>$orders["CUSTOMER_NAME"]);
//first "sub-query"
$stmt2 = oci_parse($db,"SELECT current_status,record_date FROM ORDER_TRACKING WHERE order_id = ".$orders["ORDER_ID"]);
oci_execute($stmt2);
while ($status = oci_fetch_assoc($stmt2)) { //note that this function is PHP5 only, use OCIFetchInto for PHP4
$arr_order_status[] = array("CURRENT_STATUS"=>$status["CURRENT_STATUS"],
"RECORD_DATE"=>$status[ "RECORD_DATE" ]);
}
$arr_order_line["STATUS"] = $arr_order_status;
//second "sub-query"
$stmt3 = Oci_parse($db,"SELECT item_description,item_price FROM ITEMS,ORDER_ITEMS WHERE
ORDER_ITEMS.ITEM_ID = ITEMS.ITEM_ID AND ORDER_ITEMS.ORDER_ID = ".$orders["ORDER_ID"]);
oci_execute($stmt3);
while ($items = oci_fetch_assoc($stmt3)) {
$arr_order_items[] = array("ITEM_DESCRIPTION"=>$items["ITEM_DESCRIPTION"],
"ITEM_PRICE"=>$items["ITEM_PRICE"]);
}
$arr_order_line["ITEMS"] = $arr_order_items;
//add new line to the orders array
$arr_orders[] = $arr_order_line;
}
変数バインディングによる単純な副問合せ： この手法は1つめの方法と似ており、ここでもループ内で独立したデータベース問合せを実行します。 ただし、この場合は変数バインディングを使用します。
理論的には、変数バインディングを使用すると、Oracleで新しい文を個々に解析する必要がなくなるため、問合せがより早く実行されることになります。 また、バインディングを使用する場合、SQLインジェクション攻撃のリスクが軽減されるため、一般的にはより安全だといえます。
$stmt = oci_parse($db,"SELECT order_id,order_date,customer_name FROM CUSTOMERS,
ORDERS WHERE ORDERS.customer_id = 1 AND ORDERS.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID");
//parse the statement to get status update history and bind it to the variable
$stmt2 = oci_parse($db,"SELECT current_status,record_date FROM ORDER_TRACKING WHERE order_id = :var");
oci_bind_by_name($stmt2, ":var", $order_id,32);
//parse the statement to get item list and bind it to the variable
$stmt3 = oci_parse($db,"SELECT item_description,item_price FROM ITEMS,
ORDER_ITEMS WHERE ORDER_ITEMS.ITEM_ID = ITEMS.ITEM_ID AND ORDER_ITEMS.ORDER_ID = :var");
oci_bind_by_name ($stmt3, ":var", $order_id,32);
oci_execute($stmt); //execute order list statement
while ($orders = oci_fetch_assoc ($stmt)) {
$arr_order_status = array();
$arr_order_items = array();
$arr_order_line = array();
$arr_order_line = array("ORDER_ID"=>$orders["ORDER_ID"],"ORDER_DATE"=>$orders["ORDER_DATE"],
"CUSTOMER_NAME"=>$orders["CUSTOMER_NAME"]);
$order_id = $orders["ORDER_ID"]; //current order id
//first "sub-query"
oci_execute($stmt2);
while ($status = oci_fetch_assoc($stmt2)) {
$arr_order_status[] = array("CURRENT_STATUS"=>$status["CURRENT_STATUS"],
"RECORD_DATE"=>$status[ "RECORD_DATE" ]);
}
$arr_order_line["STATUS"] = $arr_order_status;
//second "sub-query"
oci_execute($stmt3);
while ($items = oci_fetch_assoc($stmt3)) {
$arr_order_items[] = array("ITEM_DESCRIPTION"=>$items["ITEM_DESCRIPTION"],
"ITEM_PRICE"=>$items["ITEM_PRICE"]);
}
$arr_order_line["ITEMS"] = $arr_order_items;
//add new line to the orders array
$arr_orders[] = $arr_order_line;
}
Oracle CURSOR副問合せによる単純な問合せ： 3つめの手法では、単一のデータベース・コールでOracleのCURSOR式を使用します。 ループも使用しますが、ここでは元のデータベース表に問い合わせる代わりに、最初の呼出しに含まれているコレクションの問合せをおこないます。
$stmt = oci_parse($db,"SELECT order_id,order_date,customer_name,
CURSOR(SELECT current_status,record_date FROM ORDER_TRACKING WHERE order_id = ORDERS.order_id) as status,
CURSOR(SELECT item_description,item_price FROM ITEMS,
ORDER_ITEMS WHERE ORDER_ITEMS.ITEM_ID = ITEMS.ITEM_ID AND ORDER_ITEMS.ORDER_ID = ORDERS.order_id) AS items
FROM CUSTOMERS, ORDERS WHERE ORDERS.customer_id = 1
AND ORDERS.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID");
oci_execute($stmt);
while ($orders = oci_fetch_assoc ($stmt)) {
$arr_order_status = array();
$arr_order_items = array();
$arr_order_line = array();
$arr_order_line = array("ORDER_ID"=>$orders["ORDER_ID"],"ORDER_DATE"=>$orders["ORDER_DATE"],
"CUSTOMER_NAME"=>$orders["CUSTOMER_NAME"]);
//process cursors
oci_execute($orders["STATUS"]); //get data from the nested collection
while ($status = oci_fetch_assoc($orders["STATUS"])) {
$arr_order_status[] = array("CURRENT_STATUS"=>$status["CURRENT_STATUS"],
"RECORD_DATE"=>$status["RECORD_DATE"]);
}
$arr_order_line["STATUS"] = $arr_order_status;
oci_execute($orders["ITEMS"]);
while ($items = oci_fetch_assoc($orders["ITEMS"])) {
$arr_order_items[] = array("ITEM_DESCRIPTION"=>$items["ITEM_DESCRIPTION"],
"ITEM_PRICE"=>$items["ITEM_PRICE"]);
}
$arr_order_line["ITEMS"] = $arr_order_items;
//add new line to the orders array
$arr_orders[] = $arr_order_line;
}
レポートの表示
この時点では、レポートのデータは$arr_orders配列に格納されています。 以下は、簡単な表示のために、配列内をループしてHTML表としてデータをレンダリングするコードを示すスニペットです。 このサンプルではすべての注文が同一であるため、このスニペットでは最初の2つの注文のみ示します。
for($i=0;$i<=1;$i++) { //show only the first two orders
echo "<table cellpadding=\"2\" cellspacing=\"2\" width=\"500\" border=\"1\">";
echo "<tr><td colspan=\"2\">Order # ".($i+1)." (".$arr_orders[$i]["ORDER_ID"]." "
.$arr_orders[$i]["ORDER_DATE"].",".$arr_orders[$i]["CUSTOMER_NAME"].")</td></tr>";
echo "<tr><td valign=\"top\">";
foreach($arr_orders[$i]["STATUS"] as $status) {
echo $status["RECORD_DATE"]." - ".$status["CURRENT_STATUS"]."<br />";
}
echo "</td><td valign=\"top\">";
foreach($arr_orders[$i]["ITEMS"] as $item) {
echo $item["ITEM_DESCRIPTION"]." - ".$item["ITEM_PRICE"]."<br />";
}
echo "</td></tr></table>";
}
ベンチマーク
最後に、結果を確認し、使用しているシステムでレポートを生成するもっとも効率のよい方法はどの手法であるかを決定します。 ただし、まったく同一のシステムは2つとないこと、そして最高のパフォーマンスを得られるように最適化する際には多くの要素を考慮する必要があることに注意してください。 考慮すべき要素は、データベース関連の場合（索引の調整など）もあれば、PHPまたはネットワークの最適化に関係がある場合も考えられます。 ここで取り上げた簡単なシステムに関しては、以下の表を参照して、任意で決定してください。
注文数200
| 試行番号
|
手法1、時間（秒）
|
手法2、時間（秒）
|
手法3、時間（秒）
|
| 1
|
4.54 |
4.31 |
5.48 |
| 2
|
4.09 |
4.33 |
5.40 |
| 3
|
4.46 |
4.40 |
5.36 |
| 4
|
4.39 |
4.41 |
5.38 |
| 5
|
4.12 |
4.50 |
5.70 |
注文数1,000
| 試行番号
|
手法1、時間（秒）
|
手法2、時間（秒）
|
手法3、時間（秒）
|
| 1 |
33.51 |
37.99 |
34.15 |
| 2 |
29.16 |
37.76 |
33.73 |
| 3 |
29.11 |
38.19 |
35.60 |
| 4 |
28.84 |
37.79 |
44.18 |
| 5 |
29.13 |
38.67 |
37.29 |
結論
同じ機能を実現する方法は、当然、ほかにも数多くあります。たとえば、REF CURSORを含むOracleストアド・プロシージャを使用してデータを出力する方法がその一例として挙げられます。 ここでおこなったテストでは、独立した副問合せを使用することで、より高いパフォーマンスが提供されるという結果が出ました。 ただし、このテストは決して包括的なものではありません。そのため、このレシピで示したコードを使用して独自のテストを作成し、それぞれの要件に合わせた最善の手法を特定することをお勧めします。
Mikhail Seliverstovは開発部門のWebプログラミング・チーム・リーダーで、モントリオールのMcGill大学のAlumni Relationsです。 過去3年間、主席アプリケーション・アーキテクトおよび開発者として、大規模なOracle+PHP開発プロジェクトを主導してきました。
ご意見ご感想をお寄せください。
|
Articles
Printer View