我正在尝试在 Oracle 中调用存储过程，但收到以下错误：
ORA-01008：并非所有变量都绑定
这是我的PHP代码：
$cur = oci_new_cursor($connApp);
$sql = 'BEGIN ICT_FORMS.P_FORM1(
:in_action,
:in_form1_urn,
:in_cby,
:in_created_dt,
:in_dept_code,
:in_post_code,
:in_budget_code,
:in_reason_req,
:in_oh_adj,
:in_outcome_urn,
:in_serv1,
:in_serv2,
:in_serv3,
:in_serv4,
:in_group_urn,
:in_sup_staffno,
:out_err,
:out_cursor,
:out_cursor2,
:out_cursor3,
:out_newurn);
END;';
$stmt = oci_parse($connApp, $sql);
$action = 'MYA';
$staffno = '245578';
$null = null;
$zero = 0;
// bind the input parameters
oci_bind_by_name($stmt, ':in_action', $action);
oci_bind_by_name($stmt, ':in_cby', $staffno);
// bind the cursor
oci_bind_by_name($stmt,":out_cursor", $cur,-1, OCI_B_CURSOR);
// bind all of the null variables
oci_bind_by_name($stmt, ':in_form1_urn', $zero);
oci_bind_by_name($stmt, ':in_created_dt', $null);
oci_bind_by_name($stmt, ':in_dept_code', $null);
oci_bind_by_name($stmt, ':in_post_code', $null);
oci_bind_by_name($stmt, ':in_budget_code', $null);
oci_bind_by_name($stmt, ':in_reason_req', $null);
oci_bind_by_name($stmt, ':in_oh_adj', $null);
oci_bind_by_name($stmt, ':in_outcome_urn', $zero);
oci_bind_by_name($stmt, ':in_serv1', $null);
oci_bind_by_name($stmt, ':in_serv2', $null);
oci_bind_by_name($stmt, ':in_serv3', $null);
oci_bind_by_name($stmt, ':in_serv4', $null);
oci_bind_by_name($stmt, ':in_group_urn', $zero);
oci_bind_by_name($stmt, ':in_sup_staffno', $zero);
oci_bind_by_name($stmt, ':out_err', $error);
oci_bind_by_name($stmt, ':out_cursor2', $cur2, -1, OCI_B_CURSOR);
oci_bind_by_name($stmt, ':out_cursor3', $cur3, -1, OCI_B_CURSOR);
oci_bind_by_name($stmt, ':out_newurn', $newURN);
oci_execute($stmt);
oci_execute($cur);
while($row = oci_fetch_assoc($cur))
{
var_dump($row);
}
下面是存储过程：
create or replace PROCEDURE P_FORM1 (
in_action IN VARCHAR2,
in_form1_urn IN NUMBER DEFAULT 0,
in_cby IN VARCHAR2 DEFAULT NULL,
in_created_dt IN DATE DEFAULT NULL,
in_dept_code IN VARCHAR2 DEFAULT NULL,
in_post_code IN VARCHAR2 DEFAULT NULL,
in_budget_code IN VARCHAR2 DEFAULT NULL,
in_reason_req IN VARCHAR2 DEFAULT NULL,
in_oh_adj IN VARCHAR2 DEFAULT NULL,
in_outcome_urn IN NUMBER DEFAULT 0,
in_serv1 IN VARCHAR2 DEFAULT 'N',
in_serv2 IN VARCHAR2 DEFAULT 'N',
in_serv3 IN VARCHAR2 DEFAULT 'N',
in_serv4 IN VARCHAR2 DEFAULT 'N',
in_group_urn IN NUMBER DEFAULT 0,
in_sup_staffno IN NUMBER DEFAULT 0,
out_err OUT VARCHAR2,
out_cursor OUT SYS_REFCURSOR,
out_cursor2 OUT SYS_REFCURSOR,
out_cursor3 OUT SYS_REFCURSOR,
out_newurn OUT NUMBER
) as
f1_new_urn NUMBER := 0;
comm_new_urn NUMBER := 0;
cursor NEW_Form1_CURSOR is
select ICT_Forms.s_form_1_seq.nextval from dual;
cursor NEW_Form1_comments_CURSOR is
select ICT_Forms.s_form_1_comments_seq.nextval from dual;
/* --------- Local Variables --------- */
sql_statement VARCHAR2(4000) :=null;
sql_statement2 VARCHAR2(4000) :=null;
sql_statement3 VARCHAR2(4000) :=null;
-- View Attributes [inc foreign key descriptions]
view1_name VARCHAR2(4000) := ' ICT_Forms.v_Form_list T1 ';
view1_cols VARCHAR2(4000) := ' urn, date_created, Form_name, outcome, cby ' ;
view2_name VARCHAR2(4000) := ' ICT_Forms.v_Form1_auth_list T1 ';
view2_cols VARCHAR2(4000) := ' urn, date_created, created_by, form_name, outcome, supervisor_name, sup_staffno ' ;
view3_name VARCHAR2(4000) := ' ICT_Forms.v_form1';
view3_cols VARCHAR2(4000) := ' urn, Date_created, form_name ,department_lpa, Department_name, Description, budget_code, Reason_req, Oh_adj, outcome' ;
view4_name VARCHAR2(4000) := ' ICT_Forms.v_form1_serv';
view4_cols VARCHAR2(4000) := ' service_type, service_desc, cost' ;
view5_name VARCHAR2(4000) := ' ICT_Forms.v_form1_comments';
view5_cols VARCHAR2(4000) := ' Comments_by ,comments_date, auth_type, Comments, Group_name' ;
/* --------- Main Processing Block --------- */
BEGIN
out_err := '0';
-- ******************************************************************************************************************
IF in_action = 'MYA' then -- Select List of Form1 by staff_no
sql_statement := 'SELECT' || view1_cols || ' FROM ' || view1_name ||
'WHERE Cby =:in_cby';
OPEN out_cursor FOR sql_statement using in_cby;
--
******************************************************************************************************************
ELSE
out_err := '[P_FORM1] Action not valid: ' || in_action;
END IF;
-- ******************************************************************************************************************
EXCEPTION
WHEN OTHERS THEN
out_err := '[P_FORM1] An error has occurred - '||SQLCODE||' - ERROR - '||SQLERRM || chr(10) ||
'SQL Statement: ' || sql_statement ;
END;
请问我可能做错了什么吗？我已经检查了 PHP 中的所有 21 个参数是否与过程中的 21 个参数匹配，并且它们确实如此，并且它们似乎都正确绑定。
非常感谢
终于在
同事的帮助下想通了，事实证明，即使我计划使用单个游标，也必须使用 oci_new_cursor 然后oci_execute声明和执行所有 3 个游标：
$cur = oci_new_cursor($connApp);
$cur2 = oci_new_cursor($connApp);
$cur3 = oci_new_cursor($connApp);
oci_bind_by_name($stmt, ":out_cursor", $cur,-1, SQLT_RSET);
oci_bind_by_name($stmt, ":out_cursor2", $cur2, -1, SQLT_RSET);
oci_bind_by_name($stmt, ":out_cursor3", $cur3, -1, SQLT_RSET);
oci_execute($stmt);
oci_execute($cur);
oci_execute($cur2);
oci_execute($cur3);