使用 Oracle 和 PHP 创建在线报表
作者：Mikhail Seliverstov
了解使用 Oracle 和 PHP 创建在线报表的各种技术
2006 年 7 月发布
在大多数基于 Web 的应用程序中，能够生成数据库驱动的报表是一个“必需功能”。为了创建复杂报表，大多数开发人员都借助于使用组合数据集，这些数据集无法通过运行单个 SQL 查询来检索，而是需要在循环中执行一组较小的查询。
虽然这个方法通常很适用，但还有其他替代方法。在本文中，您将了解依赖变量绑定和 Oracle CURSOR 表达式的其他技术，并针对传统方法来评估这些技术。这里提供了所有代码，因此您应该能够复制测试条件，并进行自己的评估。
设置数据结构
对于本文，我将重用“使用 PHP 和 Oracle 集合通过数据库调用来插入多行”一文中的示例，该文演示了如何使用 Oracle 游标加快批量数据的插入速度。在上述文章中，您创建了一个简单的数据库结构，可用于支持虚拟的电子商务 Web 应用程序。这里，您将添加另一个表，可用于追踪每个定单的状态更改 (Order received -> Order processed -> Order packed -> Order shipped)。换言之，通过查看这个表，您将能够了解任何给定定单的当前状态以及状态更改历史。
以下是经过重新散列的数据结构：
以下是创建以上这些表的 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 表包含了 15 个项（ITEM_ID 从 1 到 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.",'http://www.oracle.com/technology/pub/articles/oracle_php_cookbook/".$status."',SYSDATE)";
$stmt = oci_parse($db,$sql);
oci_execute($stmt);
}
}
此时，ORDERS 表应该填充有客户 1 放置的 200 个定单。每个定单都包含 ORDER_ITEMS 表中的 15 个项和 ORDER_TRACKING 表中的 4 个状态。
报表需求
首先，为您唯一的客户 (CUSTOMER_ID = 1) 创建一个定单历史报表。该报表将通过两个子部分显示客户定单的完整列表：一个部分显示每个给定定单所包含的项，另一个部分列出该定单的状态更新历史。在以下部分中，您将使用三种不同的技术创建该报表：即：在循环中执行一系列独立的数据库调用，通过变量绑定执行相同的独立查询，以及在单个查询中使用 Oracle 的 CURSOR 表达式。
简单的子查询。毫无疑问，该方法是构建复杂报表的最传统方法。在本例中，您将执行一个简单查询来检索给定客户 id 的定单列表，然后循环遍历该定单列表并执行两个子查询 — 一个用于获取状态更改历史，另一个用于获取项列表。结果将存储在关联数组 $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;
}
带有变量绑定的简单子查询。该方法类似于前一个技术，也依赖于在循环中执行独立的数据库查询。但是，这次您将使用变量绑定。
从理论上讲，使用变量绑定应该能够加快执行查询的速度，因为 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 子查询的单个查询。第三个方法涉及到在单个数据库调用中使用 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 表的形式呈现数据以便于查看。在我们的示例中，由于所有定单都是相同的，因此该片段将仅显示前两个定单。
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>";
}
评估
最后，查看结果并决定哪个方法是为系统生成报表的最有效方法。但是请记住，没有完全相同的两个系统，并且在针对最佳性能进行优化时需要考虑许多因素。有些因素可能与数据库相关（如微调索引），有些可能涉及 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 编程小组的领导人。在过去 3 年中，他作为首席应用程序架构师和开发人员，帮助领导了一个大型的 Oracle+PHP 开发项目。
将您的意见发送给我们
|
文章
Printer View