首页 新闻 搜索 专区 学院

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

王水饺的技术栈的主页 王水饺的技术栈 | 初学一级 | 园豆:7
提问于:2019-01-23 17:31
< >
分享
所有回答(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));
悟行 | 园豆:12328 (专家六级) | 2019-01-24 16:54

现在报错:

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;";
这个写法是我一直在用的,只是这次出参变成游标,就开始报错了。
兄弟还有别的解决方案吗?

支持(0) 反对(0) 王水饺的技术栈 | 园豆:7 (初学一级) | 2019-01-24 17:04

@王水饺的技术栈: 

$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));
支持(1) 反对(0) 悟行 | 园豆:12328 (专家六级) | 2019-01-24 17:12
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册