如何从PHP代码中调用复杂的Oracle过程

Question

请帮助我解决以下问题。

简介：我制作了一个自定义MVC框架，其中我制作了将调用"；Oracle存储过程"；。因此，现在我能够与PHP-OCI模块建立连接，能够获取和插入记录，还能够调用一个简单的hello-world过程。但我有一个很长的过程，有输入和输出参数，但我无法调用该过程。

问题：当我在SQL编辑器中直接调用该过程并运行它时，它就工作了。但当我从PHP调用时，它会给我一个错误。

1. 我从SQL编辑器中调用过程，如下所示：

DECLARE
V_HOSPITAL_CODE VARCHAR2(200);
V_SPECIALTY_ID  NUMBER;
V_refcur SYS_REFCURSOR;
lsn1 NUMBER;
lsn2 VARCHAR2(200);
lsn3 VARCHAR2(200);
BEGIN
V_HOSPITAL_CODE := 'ABC';
V_SPECIALTY_ID  := 1;
SP_DOCTORS_LIST( V_HOSPITAL_CODE => V_HOSPITAL_CODE, V_SPECIALTY_ID => V_SPECIALTY_ID, V_REFCUR => V_refcur );
loop
FETCH V_refcur into lsn1,lsn2,lsn3; 
exit when V_refcur%notfound;
DBMS_OUTPUT.PUT_LINE(lsn1 || ' | ' || lsn2 || ' | ' || lsn3);
end loop;
CLOSE V_REFCUR;
END;

注：以上程序成功运行

2. PHP代码调用过程：

$sql = 'BEGIN SP_PATIENT_DATA(:V_HOSPITAL_CODE, :V_PATIENTID, :V_REFCUR); END;';
$stmt = oci_parse($this->conn, $sql);
// Assign a value to the input
$V_HOSPITAL_CODE = 'ABC';
$V_PATIENTID = 24;
oci_bind_by_name($stmt, ':V_HOSPITAL_CODE', $V_HOSPITAL_CODE);
oci_bind_by_name($stmt, ':V_PATIENTID', $V_PATIENTID);
oci_bind_by_name($stmt, ':V_REFCUR', $V_REFCUR);
oci_execute($stmt);
print "$V_REFCURn";

上面的代码给了我下面的错误

Warning: oci_execute(): ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'SP_PATIENT_DATA' ORA-06550: line 1, column 7: PL/SQL: Statement ignored in /var/www/html/project/app/Components/Connection.php on line 72

PHP:7.4操作系统：Ubuntu 20Oracle:11g

感谢和问候Tanmaya

Answer 1

将REF CURSOR绑定更改为类似的内容

$V_REFCUR = oci_new_cursor($conn);
oci_bind_by_name($stmt, ':V_REFCUR', $V_REFCUR, -1, OCI_B_CURSOR);

请参阅PHP OCI8文档https://www.php.net/manual/en/function.oci-new-cursor.php例如。

另一个例子是：

<?php
/*
Example using a REF CURSOR to return query results from a stored
procedure.
Create this package before running this PHP script:
create or replace
procedure myproc(p1 out sys_refcursor) as
begin
open p1 for
select last_name from employees where rownum <= 5;
end;
/
*/
$c = oci_connect('cj', 'cj', 'localhost/orclpdb1');
$stmtarray = array(
"create or replace
procedure myproc(p1 out sys_refcursor) as
begin
open p1 for
select last_name from employees where rownum <= 5;
end;"
);
foreach ($stmtarray as $stmt) {
$s = oci_parse($c, $stmt);
$r = oci_execute($s);
if (!$r) {
$m = oci_error($s);
if ($m['code'] != 942) {  // ignore table or view doesn't exist
echo $m['message'], "n";
}
}
}
// Excute the call to the PL/SQL stored procedure
$stid = oci_parse($c, "call myproc(:rc)");
$refcur = oci_new_cursor($c);
oci_bind_by_name($stid, ':rc', $refcur, -1, OCI_B_CURSOR);
oci_execute($stid);
// Execute and fetch from the cursor
oci_execute($refcur);
echo '<table border="1">';
while($row = oci_fetch_assoc($refcur)) {
echo '<tr>';
foreach ($row as $c) {
echo "<td>$c</td>n";
}
echo '</tr>';
}
echo '</table>';
?>

如果REF CURSOR包含很多行，请不要忘记调整预取大小。

更多信息请参阅The Underground PHP and Oracle Manual