php 调用oracle存储过程代码

/*
速度和效率。使用存储过程的速度显然更快。在效率上，如果应用一次需要做一系列sql操作，则需要往返于php与oracle，不如把该应用直接放到数据库教程方以减少往返次数，增加效率。但是在internet应用上，速度是极度重要的，所以很有必要使用存储过程
*/

//建立一个test表

create table test (

id number(16) not null,

name varchar2(30) not null,

primary key (id)

);

//插入一条数据

insert into test values (5, 'php/index.html>php_book');

//建立一个存储过程

create or replace procedure proc_test (

p_id in out number,

p_name out varchar2

) as

begin

select name into p_name

from test

where id = 5;

end proc_test;

/

--------------------------------------------------------------------------------

php/index.html>php代码:--------------------------------------------------------------------------------

//建立数据库连接

$user = "scott"; //数据库用户名

$password = "tiger"; //密码

$conn_str = "tnsname"; //连接串(cstr : connection_string)

$remote = true //是否远程连接

 

if ($remote) {

$conn = ocilogon($user, $password, $conn_str);

}

else {

$conn = ocilogon($user, $password);

}

//设定绑定

$id = 5; //准备用以绑定的php变量 id

$name = ""; //准备用以绑定的php变量 name

/** 调用存储过程的sql语句(sql_sp : sql_storeprocedure)

* 语法：

* begin 存储过程名([[:]参数]); end;

* 加上冒号表示该参数是一个位置

**/

$sql_sp = "begin proc_test(:id, :name); end;";

//parse

$stmt = ociparse($conn, $sql_sp);

//执行绑定

ocibindbyname($stmt, ":id", $id, 16); //参数说明：绑定php变量$id到位置:id，并设定绑定长度16位

ocibindbyname($stmt, ":name", $name, 30);

//execute

ociexecute($stmt);

//结果

echo "name is : $name
";

//php oralce官方实例

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 ";
?>

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

// 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 ");
}

// 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 ");
}

// everything ok so commit
oci_commit($conn);
print $status_msg." ";
?>

正如该脚本所演示的，关键问题是如何在使用 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 ");
}

if ( !$textlob->save('this is the body of the test entry [v2]') ) {
oci_rollback($conn);
die ("error saving lob ");
}

oci_commit($conn);
print $status_msg." ";
?>

结论

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