oracle 我可以从匿名 PL/SQL 块向 PHP 返回值吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2953566/
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
Can I return values to PHP from an anonymous PL/SQL block?
提问by aw crud
I'm using PHP and OCI8 to execute anonymous Oracle PL/SQL blocks of code. Is there any way for me to bind a variable and get its output upon completion of the block, just as I can when I call stored procedures in a similar way?
我正在使用 PHP 和 OCI8 来执行匿名 Oracle PL/SQL 代码块。有什么方法可以让我绑定一个变量并在块完成时获取它的输出，就像我以类似的方式调用存储过程时一样？
$SQL = "declare
something varchar2 := 'I want this returned';
begin
--How can I return the value of 'something' into a bound PHP variable?
end;";
采纳答案by OMG Ponies
You define an out parameter by using the keyword OUTbetween the name and data type declaration. IE:
您可以通过OUT在名称和数据类型声明之间使用关键字来定义输出参数。IE：
CREATE OR REPLACE PROCEDURE blah (OUT_PARAM_EXAMPLE OUT VARCHAR2) IS ...
If not specified, INis the default. If you want to use a parameter as both in and out, use:
如果未指定，IN则为默认值。如果要同时使用参数作为输入和输出，请使用：
CREATE OR REPLACE PROCEDURE blah (INOUT_PARAM_EXAMPLE IN OUT VARCHAR2) IS ...
The following example creates a procedure with IN and OUT parameters. The procedure is then executed and the results printed out.
下面的示例创建一个带有 IN 和 OUT 参数的过程。然后执行该过程并打印结果。
<?php
// Connect to database...
$c = oci_connect("hr", "hr_password", "localhost/XE");
if (!$c) {
echo "Unable to connect: " . var_dump( oci_error() );
die();
}
// Create database procedure...
$s = oci_parse($c, "create procedure proc1(p1 IN number, p2 OUT number) as " .
"begin" .
" p2 := p1 + 10;" .
"end;");
oci_execute($s, OCI_DEFAULT);
// Call database procedure...
$in_var = 10;
$s = oci_parse($c, "begin proc1(:bind1, :bind2); end;");
oci_bind_by_name($s, ":bind1", $in_var);
oci_bind_by_name($s, ":bind2", $out_var, 32); // 32 is the return length
oci_execute($s, OCI_DEFAULT);
echo "Procedure returned value: " . $out_var;
// Logoff from Oracle...
oci_free_statement($s);
oci_close($c);
?>
Reference:
参考：
回答by Darkhan ZD
Here my decision:
这是我的决定：
function execute_procedure($procedure_name, array $params = array(), &$return_value = ''){
$sql = "
DECLARE
ERROR_CODE VARCHAR2(2000);
ERROR_MSG VARCHAR2(2000);
RETURN_VALUE VARCHAR2(2000);
BEGIN ";
$c = $this->get_connection();
$prms = array();
foreach($params AS $key => $value) $prms[] = ":$key";
$prms = implode(", ", $prms);
$sql .= ":RETURN_VALUE := ".$procedure_name."($prms);";
$sql .= " END;";
$s = oci_parse($c, $sql);
foreach($params AS $key => $value)
{
$type = SQLT_CHR;
if(is_array($value))
{
if(!isset($value['value'])) continue;
if(!empty($value['type'])) $type = $value['type'];
$value = $value['value'];
}
oci_bind_by_name($s, ":$key", $value, -1, $type);
}
oci_bind_by_name($s, ":RETURN_VALUE", $return_value, 2000);
try{
oci_execute($s);
if(!empty($ERROR_MSG))
{
$data['success'] = FALSE;
$this->errors = "Ошибка: $ERROR_CODE $ERROR_MSG";
}
return TRUE;
}
catch(ErrorException $e)
{
$this->errors = $e->getMessage();
return FALSE;
}
}
example:
例子：
execute_procedure('My_procedure', array('code' => 5454215), $return_value);
echo $return_value;
