PHP 调用 Oracle 存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3211652/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Stored Procedure call by PHP
提问by Claudio
Here the code:
这里的代码：
<?php
include_once 'config.php';
// Connect to database
$conn = oci_connect($dbuser, $dbpasswd, $dbhost."/".$dbname);
if (!$conn) {
exit ("Connection failed.");
}
$id = isset($_GET['id']) ? (int)$_GET['id'] : false;
$type = isset($_GET['type']) ? strtoupper($_GET['type']) : "BLOG";
$stmt = oci_parse($conn,
"begin
PKG_LIKE.get_LikeId(
:I_N_Id,
:I_S_Type,
:O_N_grade,
:O_N_exitFlag,
:O_S_exitMsg);
end;");
oci_bind_by_name($stmt, "I_N_Id", $id);
oci_bind_by_name($stmt, "I_S_Type", $type);
oci_bind_by_name($stmt, "O_N_grade", $total);
oci_bind_by_name($stmt, "O_N_exitFlag", $flag);
oci_bind_by_name($stmt, "O_S_exitMsg", $message);
if (!oci_execute($stmt)) {
exit("Procedure Failed.");
}
if ($message == 'OK') {
$response = array('likeit' => $total);
$toReturn = "var response=".json_encode($response)."; showTotalLikeit(response);";
} else {
$response = array('likeit' => 'NaN', 'exitFlag' => $flag, 'exitMsg' => $message);
$toReturn = "var response=".json_encode($response)."; showTotalLikeit(response);";
}
print $toReturn;
Result is "Procedure Failed". Where am I failing? I've just used a stored procedure call (but with cursors as output) till now and all was fine.
结果是“程序失败”。我哪里失败了？到目前为止，我刚刚使用了存储过程调用（但使用游标作为输出），一切都很好。
Launching the SP on Oracle works fine so it's a php problem.
在 Oracle 上启动 SP 工作正常，所以这是一个 php 问题。
回答by Narcis Radu
if (oci_execute($stmt)) {
exit("Procedure Failed.");
}
So, your logic is: if the execute is successful, then the procedure failed?
所以，你的逻辑是：如果执行成功，那么程序失败？
Just replace with:
只需替换为：
if (!oci_execute($stmt)) {
exit("Procedure Failed.");
}
回答by Claudio
Magically it works while i was debugging using some echo to print the content of some variables.
神奇的是，它在我调试时使用一些 echo 来打印一些变量的内容。
I'm sure i've to kill a SysAdmin for these days wasted.
我确定我必须杀死一个系统管理员，因为这些天浪费了。
回答by Luis Morales
This example work for me:
这个例子对我有用：
Reqirements: Pear MDB2 with oracle support
要求：支持 oracle 的 Pear MDB2
This sample show how to run an oracle package from php using MDB2 framework, using an store procedure with parameters IN, OUT, IN OUT and return the result over php variables.
此示例展示了如何使用 MDB2 框架从 php 运行 oracle 包，使用带有参数 IN、OUT、IN OUT 的存储过程，并通过 php 变量返回结果。
References:
参考：
Php code:
代码：
$in = "IN";
$out = "OUT";
$io = "INOUT";
// to show vars after
var_dump("{$in}::{$out}::{$io}");
//
$sql = "BEGIN PKG_TEST.MyProcedure(:iparm, :oparm, :ioparm); END;";
$sth = $mdb2->prepare($sql);
//
$sth->bindParam('iparm', $in, 'text', 20);
$sth->bindParam('oparm', $out, 'text', 20 );
$sth->bindParam('ioparm', $io, 'text', 20);
//
$res = $sth->execute();
//
if (PEAR::isError($res)) {
var_dump($res->userinfo);
}else{
$sth->free();
}
// to show vars before
var_dump("{$in}::{$out}::{$io}");
Oracle package definition
Oracle 包定义
CREATE OR REPLACE PACKAGE PKG_TEST AS
PROCEDURE MyProcedure(P1 IN VARCHAR2, P2 OUT VARCHAR2, P3 IN OUT VARCHAR2);
END PKG_TEST;
CREATE OR REPLACE PACKAGE BODY PKG_TEST IS
PROCEDURE MyProcedure(P1 IN VARCHAR2, P2 OUT VARCHAR2, P3 IN OUT VARCHAR2)
IS
BEGIN
P2 := P1 || '---- OUT ----';
P3 := P1 || '---- IN OUT ----';
END MyProcedure;
END PKG_TEST;
The screen shot return:
屏幕截图返回：
string(14) "IN::OUT::INOUT"
string(39) "IN::IN---- OUT ----::IN---- IN OUT ----"
Tested over:
经过测试：
- Oracle 10g, 11g
- Linux (Ubuntu server, Amazon EC2) & windows xammp 1.7.4
- php 5.3.x
- 甲骨文 10g、11g
- Linux（Ubuntu 服务器、Amazon EC2）和 windows xammp 1.7.4
- php 5.3.x
