存储过程是实际位于 Oracle 中的程序大多数存储过程都是用 PL/SQL 编写的在 Oracle 数据库 g 第 版和更高版本中您可以用 JavaNET 或其他语言将它们编写为外部过程 存储过程通常将一系列相关操作组成一个 API存储过程执行的操作包括由 SQL 语句以及 PL/SQL 语句执行的操作SQL 语句用于获取和修改数据PL/SQL 语句将对这些数据进行相应操作如执行某些数学运算对值进行详细验证值以及处理错误条件它们降低了调用程序与数据库之间的往返次数并简化了客户端 中的数据管理逻辑从而有利于提高性能 如果考虑一下管理表之间的多对多关系通常需要的代码则会发现对现有数据执行更新通常涉及三个不同的查询通过将该进程封装在单个存储过程中将减少客户端与数据库之间的通信量而通常需要在客户端代码分多个步骤执行的操作将减化为一个数据库调用 PHP OCI 扩展支持对存储过程的调用您可以将参数绑定到过程语句（与将参数绑定到普通的 SQL 语句方法相同）并可以访问结果游标和 Oracle 集合本方法文档中提供了存储过程的常见操作示例 存储过程输入和输出 调用 Oracle 存储过程时所有输入和输出数据均以参数形式传递给过程如果您习惯于使用某些参数调用 PHP 函数并让它返回一个值的过程那么起初您可能对此感到有些迷惑不解但通过示例却可以一目了然假设有以下存储过程签名
sayHello (name IN VARCHAR greeting OUT VARCHAR)
调用此过程时第一个参数名将包含一个在调用时提供的输入值而 greeting 将由该过程填充作为一个返回值在该过程完成后使用 阅读规范 PL/SQL 编程不是本方法文档的范畴但您需要对存储过程有一个大致的了解并能够阅读接口规范但不必深究 对于存储过程的源代码开始都需要先定义接受的参数例如
PROCEDURE edit_entry(
status_out OUT NUMBER
status_msg_out OUT VARCHAR
id_inout IN OUT INTEGER
title_in IN VARCHAR
text_out OUT CLOB
categories_in IN list_of_numbers
);
该过程名为 edit_entry圆括号中定义了可以传递给该过程的各参数（由逗号分隔）每个参数中你会看到用于在该过程内部引用其值的名称（不需要在 PHP 脚本中使用同一名称）参数的模式（如下所示）以及该参数的类型 对于该示例中的第一个参数
status_out OUT NUMBER
内部名称为 status_out模式为 OUT类型为 NUMBER（它是一个原生的 Oracle 数据类型） 后面有一个 id_inout 参数
id_inout IN OUT INTEGER
它的模式为 IN OUT类型为 INTEGER 最后是 categories_in 参数
categories_in IN list_of_numbers
此处的类型是由用户定义的（稍后将对该类型进行详细介绍） 参数模式 参数模式描述了数据从调用方到过程的流向
IN – 该模式的参数由调用方提供
OUT – 参数可以由过程分配值并返回至调用方
IN OUT – 参数可以在两个方向使用即调用方可以为该参数提供值而过程也可以修改参数值
参数项是必选项从 PHP 调用过程时必须将 PHP 变量绑定到它定义的所有参数您不必向 PHP 变量分配值即使它们是输入参数 – 如果未向标量类型分配值Oracle 将把它视为 NULL 值 值得注意的是存储过程可以在 Oracle 中重载换言之可以有两个名称相同但参数签名不同的过程将依据 PHP 变量绑定到的参数的数目和类型来决定要调用哪个过程 复杂类型 存储过程使用的参数并不只局限于 VARCHAR 和 INTEGER 等标量类型也可以传递并接收复杂的数据类型如值列表或与从表中选择的行集相对应的结果游标 一般说来如果存在要迭代的数据行则您将通常会收到从存储过程返回的游标而如果您需要传入值列表则通常将使用集合以下示例通过 PHP 演示了这些复杂类型 调用方与定义方权限Oracle 对调用方（执行存储过程的用户）和定义方（以其身份执行 CREATE PROCEDURE 语句的用户）进行了区分 默认情况下存储过程是以定义方的权限执行的即使调用方是不同的用户这意味着表的所有访问权限（例如在过程中的访问权限）将由定义方的权限控制因此调用方只需要执行过程的权限而非它使用的表的权限 可以在过程定义中用关键字 AUTHID CURRENT_USER 更改此模型设置该指令后执行存储过程时所需的权限将在运行时依据执行该过程的当前用户来决定 该方法的一个用途是测试一个修改表数据但实际上不修改实时数据的过程这种情况下调用方在他们自己的模式中定义一个表（该表与从他们需要执行的过程中访问的表同名）而过程依据本地表而非提供给定义方的表执行 从 PHP 中调用存储过程 对于要从 PHP 中执行以调用过程的 SQL 语句而言您将通常在 Oracle BEGIN END; 块（称作匿名块）中嵌入调用例如
<?php
// etc
$sql = BEGIN sayHello(:name :message); END;;
然后通过调用 oci_bind_by_name() 将参数绑定到 PHP 变量 如果使用以下 DDL 语句定义了 sayHello
CREATE OR REPLACE PROCEDURE
sayHello (name IN VARCHAR greeting OUT VARCHAR)
AS
BEGIN
greeting := Hello || name;
END;
/
注意您可以使用 SQL*Plus 命令行运行上面的语句将该语句保存到文件 (SAYHELLOSQL)接下来使用 SQL*Plus 登录
$ sqlplus username@SID
然后使用 START 命令创建该过程
SQL> START /home/username/SAYHELLOSQL
以下 PHP 脚本调用该过程
<?php
$conn = oci_connect(SCOTTTIGER) or die;
$sql = BEGIN sayHello(:name :message); END;;
$stmt = oci_parse($conn$sql);
// Bind the input parameter
oci_bind_by_name($stmt:name$name);
// Bind the output parameter
oci_bind_by_name($stmt:message$message);
// Assign a value to the input
$name = Harry;
oci_execute($stmt);
// $message is now populated with the output value
print $message\n;
?>
Blog 示例程序包为演示调用存储过程方面的某些技巧您将在此处使用以下名为 blog 的程序包该程序包提供了一个 API用于获取和修改假设的网志应用程序中的条目程序包用于通过其自身的作用域将过程函数和数据封装在其自身的命名空间内部并使它们独立于全局数 据库命名空间中的其他过程调用程序包中的过程时将使用句号来分隔程序包名称与过程名称 可以使用以下语句指定 blog 程序包
CREATE OR REPLACE PACKAGE blog AS
TYPE cursorType IS REF CURSOR RETURN blogs%ROWTYPE;
/*
Fetch the latest num_entries_in from the blogs table populating
entries_cursor_out with the result
*/
PROCEDURE latest(
num_entries_in IN NUMBER
entries_cursor_out OUT cursorType
);
/*
Edit a blog entryIf id_inout is NULL results in an INSERT otherwise
attempts to UPDATE the existing blog entry status_out will have the value
on success otherwise a negative number on failure with status_msg_out
containing a description
categories_in is a collection where list_of_numbers is described by
TYPE list_of_numbers AS VARRAY() OF NUMBER;
*/
PROCEDURE edit_entry(
status_out OUT NUMBER
status_msg_out OUT VARCHAR
id_inout IN OUT INTEGER
title_in IN VARCHAR
text_out OUT CLOB
categories_in IN list_of_numbers
);
END blog;
/
该程序包提供了两个过程bloglatest（返回包含最新 num_entries 网志条目的结果游标）和 blogedit_entry（允许插入新的网志条目以及修改现有的网志条目）如果为 id_inout 参数提供值则该过程将尝试更新具有该 id 的相应网志条目否则它将插入一个新的网志条目并使用新行的主键填充 id_inout该过程还接受与网志条目的主体相对应的 CLOB 对象以及与该条目归档到的类别列表相对应的集合对象此处引用的集合类型 list_of_numbers
由以下语句定义
CREATE OR REPLACE TYPE list_of_numbers AS VARRAY() OF NUMBER;
下面显示了该程序包的主体您可以通过其中的注释了解它的功能而不必深入了解 PL/SQL
CREATE OR REPLACE PACKAGE BODY blog AS
/**/
PROCEDURE latest(
num_entries_in IN NUMBER
entries_cursor_out OUT cursorType
) AS
BEGIN
OPEN entries_cursor_out FOR
SELECT * FROM blogs WHERE rownum < num_entries_in
ORDER BY date_published DESC;
END latest;
/**/
PROCEDURE edit_entry(
status_out OUT NUMBER
status_msg_out OUT VARCHAR
id_inout IN OUT INTEGER
title_in IN VARCHAR
text_out OUT CLOB
categories_in IN list_of_numbers
AS
ENTRY_NOT_FOUND EXCEPTION;
entry_found INTEGER := ;
BEGIN
/* Default status to success */
status_out := ;
/* If id_inout has a value then attempt to UPDATE */
IF id_inout IS NOT NULL THEN
/* Check the id exists raise ENTRY_NOT_FOUND if not */
SELECT COUNT(*) INTO entry_found
FROM blogs b WHERE bid = id_inout;
IF entry_found != THEN RAISE ENTRY_NOT_FOUND; END IF;
/* Update the blogs table returning the CLOB field */
UPDATE blogs b SET btitle = title_in btext = EMPTY_CLOB()
WHERE bid = id_inout RETURNING btext INTO text_out;
/* Remove any existing relationships to categories
new categories inserted below */
DELETE FROM blogs_to_categories WHERE blog_id = id_inout;
status_msg_out := Blog entry || id_inout || updated;
/* id_inout was null so INSERT new record */
ELSE
INSERT INTO blogs b ( bid btitle bdate_published btext )
VALUES ( blog_id_seqnextval title_in SYSDATE EMPTY_CLOB() )
RETURNING bid btext INTO id_inout text_out;
status_msg_out := Blog entry || id_inout || inserted;
END IF;
/* Now handle assignment to categories
Loop over the categories_in collection
inserting the new category assignments */
FOR i IN unt
LOOP
INSERT INTO blogs_to_categories (blog_idcategory_id)
VALUES (id_inoutcategories_in(i));
END LOOP;
status_msg_out := status_msg_out || added to
|| unt || categories;
EXCEPTION
/* Catch the exception when id_inout not found */
WHEN ENTRY_NOT_FOUND THEN
status_out := ;
status_msg_out := No entry found in table blogs with id =
|| id_inout;
/* Catch any other exceptions raised by Oracle */
WHEN OTHERS THEN
status_out := ;
status_msg_out := Error: || TO_CHAR (SQLCODE) || SQLERRM;
END edit_entry;
END blog;
/
The underlying table structure the procedures are using is:
CREATE SEQUENCE blog_id_seq
INCREMENT BY ;
/
CREATE TABLE blogs (
id NUMBER PRIMARY KEY
title VARCHAR()
date_published DATE
text CLOB
);
/
CREATE SEQUENCE category_id_seq
INCREMENT BY ;
CREATE TABLE categories (
id NUMBER PRIMARY KEY
name VARCHAR() UNIQUE
);
/
CREATE TABLE blogs_to_categories (
blog_id INTEGER NOT NULL
REFERENCES blogs(id)
category_id INTEGER NOT NULL
REFERENCES categories(id)
PRIMARY KEY (blog_id category_id)
);
/
存储过程和引用游标 看一下 bloglatest 过程您将看到它返回一个用于迭代 blogs 表行的引用游标 与直接从 SELECT 语句中访问行相比在 PHP 中使用游标需要两个额外的步骤第一步是使用 oci_new_cursor() 函数（该函数随后用于绑定到相应的参数）在 PHP 中准备一个游标资源执行 SQL 语句后第二步是对游标资源调用 oci_execute() 以下 PHP 脚本演示了该过程
<?php
$conn = oci_connect(SCOTTTIGER) or die;
$sql = BEGIN bloglatest(:num_entries :blog_entries); END;;
$stmt = oci_parse($conn $sql);
// Bind the input num_entries argument to the $max_entries PHP variable
$max_entries = ;
oci_bind_by_name($stmt:num_entries$max_entries);
// Create a new cursor resource
$blog_entries = oci_new_cursor($conn);
// Bind the cursor resource to the Oracle argument
oci_bind_by_name($stmt:blog_entries$blog_entriesOCI_B_CURSOR);
// Execute the statement
oci_execute($stmt);
// Execute the cursor
oci_execute($blog_entries);
print The $max_entries most recent blog entries\n;
// Use OCIFetchinto in the same way as you would with SELECT
while ($entry = oci_fetch_assoc($blog_entries OCI_RETURN_LOBS )) {
print_r($entry);
}
?>
存储过程和 LOB Oracle Long 对象与存储过程之间可以进行相互传递方法与内部的 SQL 之间进行的相互传递几乎相同 以下示例演示了如何使用 CLOB 调用 blogedit_entry 过程该示例未向 id 参数分配值因此它相当于插入一个新的网志条目
<?php
$conn = oci_connect(SCOTTTIGER) or die;
$sql = BEGIN blogedit_entry(:status :status_msg :id :title :text :categories); END;;
$stmt = oci_parse($conn$sql);
$title = This is a test entry;
oci_bind_by_name($stmt:status$status);
oci_bind_by_name($stmt:status_msg$status_msg);
oci_bind_by_name($stmt:id$id);
oci_bind_by_name($stmt:title$title);
// Explained in the next example(use an empty value for now)
$Categories = oci_new_collection($connLIST_OF_NUMBERS);
oci_bind_by_name($stmt:categories$CategoriesOCI_B_SQLT_NTY);
// Create a new lob descriptor object
$textLob = oci_new_descriptor($conn OCI_D_LOB);
// Bind it to the parameter
oci_bind_by_name($stmt :text $textLob OCI_B_CLOB);
// Execute the statement but do not commit
oci_execute($stmt OCI_DEFAULT);
// The status parameter will be negative if the procedure encountered a problem
if ( !$status ) {
// Rollback the procedure
oci_rollback($conn);
die ($status_msg\n);
}
// Save the body of the blog entry to the CLOB
if ( !$textLob>save(This is the body of the test entry) ) {
// Rollback the procedure
oci_rollback($conn);
die (Error saving lob\n);
}
// Everything OK so commit
oci_commit($conn);
print $status_msg\n;
?>
正如该脚本所演示的关键问题是如何在使用 LOB 时处理事务由于更新 LOB 是一个分为两阶段的过程因此您在此处选择将所有事务处理委托给 PHP 脚本 注意默认情况下Oracle 只允许在任何给定的会话中一次运行一个事务这意味着从 PHP 调用的过程中发出的 commit 或 rollback 语句将覆盖对 oci_commit() 或 oci_rollback() 的调用可以使用匿名事务（使用位于过程定义内部的 pragma PRAGMA AUTONOMOUS_TRANSACTION 启用）更改此行为例如您可以在从其他过程中调用的日志记录程序包中使用匿名事务使用这一方法您可以记录有关存储过程调用的信息而不会干扰正在会话中运行的事务 存储过程和集合 集合是一种用于将复杂数据类型传递到存储过程中的机制在网志应用程序中可以将网志条目归档到多个分类中（与blogs表和categories表之间的多对多关系相对应） 必须在数据库中全局定义 Oracle 中的集合类型在本示例中您将使用以下定义
CREATE OR REPLACE TYPE list_of_numbers AS VARRAY() OF NUMBER;
该定义允许您一次最多向 个类别分配一个网志条目方法是将该类型的实例传递给 blogedit_entry 过程 在 PHP 中集合由预定义的 PHP 类 OCICollection 表示可以通过调用 oci_new_collection() 函数创建此类实例OCICollection 对象提供了以下方法
append将元素添加到集合末尾
assign从现有集合中将元素添加到某个集合
assignElem将值分配给集合并标识应将该元素置于的集合中的索引位置
free释放与集合句柄关联的资源
getElem从集合中的特殊索引位置检索元素
max返回集合中的最大元素数
size返回集合的当前大小
trim从集合末尾删除一些元素
此处您只希望使用 append 方法因此可以将类别 ID 列表附加到过程调用在以下示例中您将更新在前一个示例中创建的现有网志条目方法是将它的 ID 传递给 blogedit_entry 过程以及类别 id 列表
<?php
$conn = oci_connect(SCOTTTIGER) or die;
$sql = BEGIN blogedit_entry(:status :status_msg :id :title :text :categories); END;;
$stmt = oci_parse($conn $sql);
$id = ; // ID of the new entry
$title = This is a test entry (v);
oci_bind_by_name($stmt:status$status);
oci_bind_by_name($stmt:status_msg$status_msg);
oci_bind_by_name($stmt:id$id);
oci_bind_by_name($stmt:title$title);
$textLob = oci_new_descriptor($conn OCI_D_LOB);
oci_bind_by_name($stmt :text $textLob OCI_B_CLOB);
// Create an OCICollection object
$Categories = oci_new_collection($connLIST_OF_NUMBERS);
// Append some category IDs to the collection;
$Categories>append();
$Categories>append();
$Categories>append();
// Bind the collection to the parameter
oci_bind_by_name($stmt:categories$CategoriesOCI_B_SQLT_NTY);
oci_execute($stmt OCI_DEFAULT);
if ( !$status ) {
oci_rollback($conn);
die ($status_msg\n);
}
if ( !$textLob>save(This is the body of the test entry [v]) ) {
oci_rollback($conn);
die (Error saving lob\n);
}
oci_commit($conn);
print $status_msg\n;
?>
结论 您现在已经了解了有关如何从 PHP 中调用存储过程（既包括只涉及标量数据类型的简单过程也包含更复杂的使用 LOB游标和集合的过程）的示例还对存储过程的定义进行了足够的了解能读懂它们的 PL/SQL 规范这样您就可以从 PHP 中正确地调用它们并绑定相应的类型
