php oracle 游标,使用 PHP 调用 Oracle 存储过程

使用 PHP 调用 Oracle 存储过程

作者：Harry Fuecks

使用 Oracle 和 PHP 演练存储过程

2005 年 11 月发表

存储过程是实际位于 Oracle 中的程序。大多数存储过程都是用 PL/SQL 编写的，在 Oracle 数据库 10g 第 2 版和更高版本中，您可以用 Java、.NET 或其他语言将它们编写为外部过程。

存储过程通常将一系列相关操作组成一个 API。存储过程执行的操作包括由 SQL 语句以及 PL/SQL 语句执行的操作，SQL 语句用于获取和修改数据，PL/SQL 语句将对这些数据进行相应操作，如执行某些数学运算、对值进行详细验证值以及处理错误条件。它们降低了调用程序与数据库之间的“往返”次数并简化了客户端中的数据管理逻辑，从而有利于提高性能。

如果考虑一下管理表之间的多对多关系通常需要的代码，则会发现对现有数据执行更新通常涉及三个不同的查询。通过将该进程封装在单个存储过程中，将减少客户端与数据库之间的通信量，而通常需要在客户端代码分多个步骤执行的操作将减化为一个数据库调用。

PHP OCI8 扩展支持对存储过程的调用，您可以将参数绑定到过程语句(与将参数绑定到普通的 SQL 语句方法相同)，并可以访问结果游标和 Oracle 集合。本方法文档中提供了存储过程的常见操作示例。

存储过程输入和输出

调用 Oracle 存储过程时，所有输入和输出数据均以参数形式传递给过程。如果您习惯于使用某些参数调用 PHP 函数并让它返回一个值的过程，那么起初您可能对此感到有些迷惑不解，但通过示例却可以一目了然。假设有以下存储过程签名：

sayHello (name IN VARCHAR2, greeting OUT VARCHAR2)

调用此过程时，第一个参数名将包含一个在调用时提供的输入值，而 greeting 将由该过程填充，作为一个“返回”值，在该过程完成后使用。

阅读规范

PL/SQL 编程不是本方法文档的范畴，但您需要对存储过程有一个大致的了解并能够阅读接口规范，但不必深究。

对于存储过程的源代码，开始都需要先定义接受的参数，例如：

PROCEDURE edit_entry(

status_out OUT NUMBER,

status_msg_out OUT VARCHAR2,

id_inout IN OUT INTEGER,

title_in IN VARCHAR2,

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 变量绑定到的参数的数目和类型来决定要调用哪个过程。

复杂类型

存储过程使用的参数并不只局限于 VARCHAR2 和 INTEGER 等标量类型。也可以传递并接收复杂的数据类型，如值列表或与从表中选择的行集相对应的结果游标。

一般说来，如果存在要迭代的数据行，则您将通常会收到从存储过程返回的游标，而如果您需要传入值列表，则通常将使用集合。以下示例通过 PHP 演示了这些复杂类型。

调用方与定义方权限。Oracle 对“调用方”(执行存储过程的用户)和定义方(以其身份执行 CREATE PROCEDURE 语句的用户)进行了区分。

默认情况下，存储过程是以定义方的权限执行的，即使调用方是不同的用户。这意味着表的所有访问权限(例如，在过程中的访问权限)将由定义方的权限控制，因此调用方只需要执行过程的权限而非它使用的表的权限。

可以在过程定义中用关键字 AUTHID CURRENT_USER 更改此模型。设置该指令后，执行存储过程时所需的权限将在运行时依据执行该过程的当前用户来决定。

该方法的一个用途是测试一个修改表数据但实际上不修改实时数据的过程。这种情况下，调用方在他们自己的模式中定义一个表(该表与从他们需要执行的过程中访问的表同名)，而过程依据本地表而非提供给定义方的表执行。

从 PHP 中调用存储过程

对于要从 PHP 中执行以调用过程的 SQL 语句而言，您将通常在 Oracle BEGIN ...END; 块(称作 匿名块)中嵌入调用。例如：

// etc.

$sql = 'BEGIN sayHello(:name, :message); END;';

然后，通过调用 oci_bind_by_name() 将参数绑定到 PHP 变量。

如果使用以下 DDL 语句定义了 sayHello：

CREATE OR REPLACE PROCEDURE

sayHello (name IN VARCHAR2, greeting OUT VARCHAR2)

AS

BEGIN

greeting := 'Hello ' || name;

END;

/

注意，您可以使用 SQL*Plus 命令行运行上面的语句。将该语句保存到文件 (SAYHELLO.SQL)。接下来，使用 SQL*Plus 登录：

$ sqlplus username@SID

然后，使用 START 命令创建该过程：

SQL> START /home/username/SAYHELLO.SQL

以下 PHP 脚本调用该过程：

$conn = oci_connect('SCOTT','TIGER') or die;

$sql = 'BEGIN sayHello(:name, :message); END;';

$stmt = oci_parse($conn,$sql);

// Bind the input parameter

oci_bind_by_name($stmt,':name',$name,32);

// Bind the output parameter

oci_bind_by_name($stmt,':message',$message,32);

// 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 entry.If id_inout is NULL, results in an INSERT, otherwise

attempts to UPDATE the existing blog entry. status_out will have the value

1 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(50) OF NUMBER;

*/

PROCEDURE edit_entry(

status_out OUT NUMBER,

status_msg_out OUT VARCHAR2,

id_inout IN OUT INTEGER,

title_in IN VARCHAR2,

text_out OUT CLOB,

categories_in IN list_of_numbers

);

END blog;

/

该程序包提供了两个过程： blog.latest(返回包含最新 num_entries 网志条目的结果游标)和 blog.edit_entry(允许插入新的网志条目以及修改现有的网志条目)。如果为 id_inout 参数提供值，则该过程将尝试更新具有该 id 的相应网志条目。否则，它将插入一个新的网志条目并使用新行的主键填充 id_inout。该过程还接受与网志条目的主体相对应的 CLOB 对象以及与该条目归档到的类别列表相对应的集合对象。此处引用的集合类型 list_of_numbers 由以下语句定义：

CREATE OR REPLACE TYPE list_of_numbers AS VARRAY(50) 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 VARCHAR2,

id_inout IN OUT INTEGER,

title_in IN VARCHAR2,

text_out OUT CLOB,

categories_in IN list_of_numbers

AS

ENTRY_NOT_FOUND EXCEPTION;

entry_found INTEGER := 0;

BEGIN

/* Default status to success */

status_out := 1;

/* 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 b.id = id_inout;

IF entry_found != 1 THEN RAISE ENTRY_NOT_FOUND; END IF;

/* Update the blogs table returning the CLOB field */

UPDATE blogs b SET b.title = title_in, b.text = EMPTY_CLOB()

WHERE b.id = id_inout RETURNING b.text 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 ( b.id, b.title, b.date_published, b.text )

VALUES ( blog_id_seq.nextval, title_in, SYSDATE, EMPTY_CLOB() )

RETURNING b.id, b.text 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 1 .. categories_in.count

LOOP

INSERT INTO blogs_to_categories (blog_id,category_id)

VALUES (id_inout,categories_in(i));

END LOOP;

status_msg_out := status_msg_out || ' - added to '

|| categories_in.count || ' categories';

EXCEPTION

/* Catch the exception when id_inout not found */

WHEN ENTRY_NOT_FOUND THEN

status_out := -1001;

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 := -1;

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

/

CREATE TABLE blogs (

id NUMBER PRIMARY KEY,

title VARCHAR2(200),

date_published DATE,

text CLOB

);

/

CREATE SEQUENCE category_id_seq

INCREMENT BY 1;

CREATE TABLE categories (

id NUMBER PRIMARY KEY,

name VARCHAR2(30) 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)

);

/

存储过程和引用游标

看一下 blog.latest 过程，您将看到它返回一个用于迭代 blogs 表行的引用游标。

与直接从 SELECT 语句中访问行相比，在 PHP 中使用游标需要两个额外的步骤。第一步是使用 oci_new_cursor() 函数(该函数随后用于绑定到相应的参数)在 PHP 中准备一个游标资源。执行 SQL 语句后，第二步是对游标资源调用 oci_execute()。

以下 PHP 脚本演示了该过程：

$conn = oci_connect('SCOTT','TIGER') or die;

$sql = 'BEGIN blog.latest(:num_entries, :blog_entries); END;';

$stmt = oci_parse($conn, $sql);

// Bind the input num_entries argument to the $max_entries PHP variable

$max_entries = 5;

oci_bind_by_name($stmt,":num_entries",$max_entries,32);

// 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_entries,-1,OCI_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 调用 blog.edit_entry 过程。该示例未向 id 参数分配值，因此它相当于插入一个新的网志条目：

$conn = oci_connect('SCOTT','TIGER') or die;

$sql = 'BEGIN blog.edit_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,32);

oci_bind_by_name($stmt,":status_msg",$status_msg,500);

oci_bind_by_name($stmt,":id",$id,32);

oci_bind_by_name($stmt,":title",$title,200);

// Explained in the next example...(use an empty value for now)

$Categories = oci_new_collection($conn,'LIST_OF_NUMBERS');

oci_bind_by_name($stmt,':categories',$Categories,32,OCI_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, -1, 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(50) OF NUMBER;

该定义允许您一次最多向 50 个类别分配一个网志条目，方法是将该类型的实例传递给 blog.edit_entry 过程。

在 PHP 中，集合由预定义的 PHP 类 OCI-Collection 表示。可以通过调用 oci_new_collection() 函数创建此类实例。OCI-Collection 对象提供了以下方法：

append：将元素添加到集合末尾

assign：从现有集合中将元素添加到某个集合

assignElem：将值分配给集合，并标识应将该元素置于的集合中的索引位置

free：释放与集合句柄关联的资源

getElem：从集合中的特殊索引位置检索元素

max：返回集合中的最大元素数

size：返回集合的当前大小

trim：从集合末尾删除一些元素

此处，您只希望使用 append 方法，因此可以将类别 ID 列表附加到过程调用。在以下示例中，您将更新在前一个示例中创建的现有网志条目，方法是将它的 ID 传递给 blog.edit_entry 过程以及类别 id 列表：

$conn = oci_connect('SCOTT','TIGER') or die;

$sql = 'BEGIN blog.edit_entry(:status, :status_msg, :id, :title, :text, :categories); END;';

$stmt = oci_parse($conn, $sql);

$id = 1; // ID of the new entry

$title = 'This is a test entry (v2)';

oci_bind_by_name($stmt,":status",$status,32);

oci_bind_by_name($stmt,":status_msg",$status_msg,500);

oci_bind_by_name($stmt,":id",$id,32);

oci_bind_by_name($stmt,":title",$title,200);

$textLob = oci_new_descriptor($conn, OCI_D_LOB);

oci_bind_by_name($stmt, ":text", $textLob, -1, OCI_B_CLOB);

// Create an OCI-Collection object

$Categories = oci_new_collection($conn,'LIST_OF_NUMBERS');

// Append some category IDs to the collection;

$Categories->append(2);

$Categories->append(4);

$Categories->append(5);

// Bind the collection to the parameter

oci_bind_by_name($stmt,':categories',$Categories,-1,OCI_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 [v2]') ) {

oci_rollback($conn);

die ("Error saving lob\n");

}

oci_commit($conn);

print $status_msg."\n";

?>

结论

您现在已经了解了有关如何从 PHP 中调用存储过程(既包括只涉及标量数据类型的简单过程，也包含更复杂的使用 LOB、游标和集合的过程)的示例。还对存储过程的定义进行了足够的了解，能读懂它们的 PL/SQL 规范，这样您就可以从 PHP 中正确地调用它们并绑定相应的类型。

Harry Fuecks [ http://www.phppatterns.com] 于 1999 年接触 PHP，此后作为 PHP 开发人员和馔稿人而声名鹊起。他通过 Sitepoint 开发人员网络发布了大量初级和中级 PHP 文章，著有 The PHP Anthology (SitePoint) 一书。