使用 PHP 和 Oracle 集合通过一个数据库调用插入多个行

如何使用 Oracle 集合提高执行多行插入的 PHP 脚本的效率

本文相关下载： Oracle 数据库 10g 特别版 Zend Core for Oracle

2006 年 2 月发表

PHP Web 开发人员经常需要创建要求在某个脚本执行过程中向一个或多个数据库表插入多行数据的脚本。例如，要将客户购物车中的所有商品插入到数据库的相应表中，脚本将必须对购物车中每件商品执行一个 INSERT 语句。根据插入数据的数量，该操作可能会成为降低应用程序速度、搞砸用户体验的瓶颈。

在这篇 Oracle+PHP 简明手册文章中，您将学习如何通过将 Oracle 存储过程和集合与 PHP 的 OCI8 扩展结合使用来提高多行插入脚本的效率。

传统方法

假设有一个依赖以下 Oracle 表的电子商务应用程序：Customers、Orders、Items 和 Order_Items。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) ;
/

创建一个序列，以便在 Order_Items 表中插入行时总有一个唯一的主键：

CREATE SEQUENCE ORDER_LINE_SQ
INCREMENT BY 1;
/

您可以看到，每当客户最终确定了订单后，我们必须将订单详细信息插入到 Orders 表中，并将购物车中每件商品作为一行插入到 Order_Items 表中。通常情况下，可以通过在循环中执行 INSERT 语句来处理类似的多行插入操作：

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

尽管该方法对于处理多行插入很有效（除了启动事务可能会有助于确保所插入数据的完整性），但通过 OCI8 包装器执行每个 SQL 语句需要数据库与 Web 服务器之间进行大量的重复通信，这可能会耗费大量的计算资源。一个更好的方法是调用数据库一次，然后让 Oracle 在内部处理所有插入。下面我们将介绍如何使用 Oracle 集合执行该操作。

Oracle 集合

集合是 PL/SQL 结构，可以像在 PHP 中使用数组那样在 Oracle 中使用集合。Oracle 支持三种集合：索引表、嵌套表和 VARRAY。

索引表是最基本的 Oracle 集合，也称作 PL/SQL 表。索引表没有上限，并只能存在于 PL/SQL 中而不能存在于数据库中。可以通过向集合中尚不存在的键赋值来添加新元素。

嵌套表类似于索引表，区别在于它可以存在于数据库表中（例如，作为表中的一列）。

最后，VARRAY 是第三种 Oracle 集合。与嵌套表相似，VARRAY 既可以存在于 PL/SQL 中也可以存在于数据库中，但在创建时必须为 VARRAY 指定上限。与索引表和嵌套表不同，VARRAY 保留了元素索引并且不允许删除。由于您不需要操作集合中的数据（即创建集合后添加、删除或移动元素），因此您将在本文中使用 VARRAY。

创建集合和过程。要使数据库能够处理所有工作，必须做两件事：定义集合类型，以便可以在表和过程中引用；创建将读取集合内容并在 Order_Items 表中插入数据的过程。

下面是如何在 Oracle 中定义集合类型：

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

此代码只定义了最多可以保存 100 个数值元素的类型 v_arr。定义后，该类型将可用于同一模式中的任何过程或表（如果在程序包中定义，则还可用于该程序包）。

下一步是创建过程：

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

您可以看到，以上过程需要两个参数：order id（为同一订单中的所有商品所共享）和 v_arr 类型的 arr_itmes 集合。该集合将包含给定订单的所有商品的 ID 列表。执行该过程时，它只遍历集合内容并对每个元素执行 insert 语句，直到到达集合结尾。

从 PHP 中执行过程。最后，您需要修改 PHP 代码以使其调用和执行以上过程。假设其他客户已经创建了一个 order ID = 2 的新订单。

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

以下语句分配一个新的 OCI8 集合对象：

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

其中的 $db 引用当前打开的连接句柄，V_ARR 是指定的集合数据类型（必须为大写）。oci_new_collection 函数还提供了第三个可选参数 schema，如果在其中声明了集合的模式与默认的连接模式不同，则必须使用该参数。默认情况下，oci_new_collection 将当前用户的名称作为模式的值。

创建集合对象后，需要使用值填充它：

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

下一行只准备要执行的过程：

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

然后，只需将 PHP 变量绑定到 Oracle 占位符：

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

注意第二行，在该行中您将 OCI8 集合对象绑定到该过程的 arr_items 参数。其中的 -1 指示驱动程序将变量的当前长度用作最大长度。OCI8_B_NTY 常量指示我们使用的是指定的数据类型。（如果使用 PHP 4.x 版或更早版本，则应改用 OCI8_B_SQLT_NTY。）

最后，通过调用 oci_execute 函数执行该语句：

oci_execute($stmt);

执行该过程后，您应看到向 Order_Items 表中插入了 15 个新行。

注意，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);

此处，SQLT_NUM 表示我们使用的是数值元素数组。

结论

本文中介绍的方法是一个可以提高需执行多个 INSERT 语句的 PHP 脚本效率的简单方法。当然，对于相对较小的数据集而言，本文介绍的两种方法之间的差别很小甚至没有。以下是在某一行中同时运行这两个脚本 10 次所获得的基准测试结果：

您可以看到，使用集合可以使性能稍好一些。但应注意，所插入的记录数量与性能提高之间的关系并非是线性的；因此，该方法在处理相对较大数据集时将作用更明显。

最后，如果考虑在生产环境中采用该方法，则强烈建议您使用事务。如果要进一步提高大量插入的速度，请考虑使用批量绑定。

Mikhail Seliverstov 是位于蒙特利尔的麦吉尔大学校友会的 Web 编程小组的领导人。在过去 3 年中，他作为首席应用程序架构师和开发人员，帮助领导了一个大型的 Oracle+PHP 开发项目。

将您的意见发送给我们