pdo调用oracle数据库返回参数为sys_refcursor类型游标,为何取不到数据?
0
悬赏园豆：200
[待解决问题]
Oracle存储过程如下:
create or replace procedure GetOutpatientJFlist
(start_date in date,
end_date in date,
channel in varchar2,
content out sys_refcursor)
is
begin
if channel is null then
open content for select * from v_yyzz_dz WHERE happen_date>=start_date and happen_date<=end_date;
else
open content for select * from v_yyzz_dz WHERE happen_date>=start_date and happen_date<=end_date and cheque_qudao=channel;
end if ;
end GetOutpatientJFlist;
PHP调用过程如下:
$start_date = iconv('UTF-8', 'GBK', $startDate);
$end_date = iconv('UTF-8', 'GBK', $endDate);
$channel = iconv('UTF-8', 'GBK', $canal);
$sql_sp = "BEGIN GetOutpatientJFlist(to_date(:start_date,'yyyy-MM-dd HH24:mi:ss'),to_date(:end_date,'yyyy-MM-dd HH24:mi:ss'),:channel,:content); END;";
$temp = $conn->prepare($sql_sp);
$temp->bindParam(':start_date', $start_date);
$temp->bindParam(':end_date', $end_date);
$temp->bindParam(':channel', $channel);
$temp->bindParam(':content', $content, PDO::PARAM_LOB);
$temp->execute();
打印errorInfo,报错为:
array(3) {
[0]=>
string(5) "HY000"
[1]=>
int(6550)
[2]=>
string(195) "OCIStmtExecute: ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 'GETOUTPATIENTJFLIST' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored
(ext\pdo_oci\oci_statement.c:157)"
}
所有回答(1)
0
时间处理错误了，
直接php格式化时间后，传递过去
$sql_sp = "BEGIN GetOutpatientJFlist(to_date(:start_date,'yyyy-MM-dd HH24:mi:ss'),to_date(:end_date,'yyyy-MM-dd HH24:mi:ss'),:channel,:content); END;"; 改成 $sql_sp = "BEGIN GetOutpatientJFlist(:start_date,:end_date,:channel,:content); END;";
$temp->bindParam(':start_date', $start_date); $temp->bindParam(':end_date', $end_date); 改成 $temp->bindParam(':start_date', date('Y-m-d H:i:s',$start_date)); $temp->bindParam(':end_date', date('Y-m-d H:i:s',$end_date));
现在报错:
br />
<b>Notice</b>: A non well formed numeric value encountered in <b>C:\WWW\unipay\application\controllers\TestController.php</b> on line <b>834</b><br />
<br />
<b>Notice</b>: Only variables should be passed by reference in <b>C:\WWW\unipay\application\controllers\TestController.php</b> on line <b>834</b><br />
<br />
<b>Notice</b>: A non well formed numeric value encountered in <b>C:\WWW\unipay\application\controllers\TestController.php</b> on line <b>835</b><br />
<br />
<b>Notice</b>: Only variables should be passed by reference in <b>C:\WWW\unipay\application\controllers\TestController.php</b> on line <b>835</b><br />
array(3) {
[0]=>
string(5) "HY000"
[1]=>
int(6550)
[2]=>
string(195) "OCIStmtExecute: ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 'GETOUTPATIENTJFLIST' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored
(ext\pdo_oci\oci_statement.c:157)"
}
string(4) "[[]]"
。。。。。
虽然让dba把存储过程改成了视图，但是我还是纠结于这个问题。。。
$sql_sp = "BEGIN GetOutpatientJFlist(to_date(:start_date,'yyyy-MM-dd HH24:mi:ss'),to_date(:end_date,'yyyy-MM-dd HH24:mi:ss'),:channel,:content); END;";
这个写法是我一直在用的，只是这次出参变成游标，就开始报错了。
兄弟还有别的解决方案吗？
@王水饺的技术栈:
$sql_sp = "BEGIN GetOutpatientJFlist(to_date(:start_date,'yyyy-MM-dd HH24:mi:ss'),to_date(:end_date,'yyyy-MM-dd HH24:mi:ss'),:channel,:content); END;"; 换成 $sql_sp = "CALL GetOutpatientJFlist(:start_date,:end_date,:channel,:content); "; 可以参考这个例子： sp_getpricedetailforhp (p_pbook in NVARCHAR2,p_plevel in NVARCHAR2,p_item in NVARCHAR2,p_qty in NUMBER,p_date in date,p_customer in NVARCHAR2,c_data OUT sys_refcursor) $dbh->query("CALL SomeStoredProcedure($someInParameter1, $someInParameter2, @someOutParameter)"); $dbh->query("SELECT @someOutParameter"); $stmt = $dbh->prepare("CALL SomeStoredProcedure(?, ?)"); $stmt ->execute(array($someInParameter1, $someInParameter2));