读书人

C#调用存储过程反回值的有关问题请

发布时间: 2011-12-17 23:37:33 作者: rapoo

C#调用存储过程反回值的问题,请各位帮帮忙!
调用函数:
public string mp_node(string in_busi_id, string in_bureau_id, string in_worker_no, string in_notes)
{
OracleParameter p1 = new OracleParameter( "in_busi_id ", OracleType.VarChar, 3);
p1.Value = in_busi_id;
p1.Direction = ParameterDirection.Input;

OracleParameter p2 = new OracleParameter( "in_bureau_id ", OracleType.VarChar, 6);
p2.Value = in_bureau_id;
p2.Direction = ParameterDirection.Input;

OracleParameter p3 = new OracleParameter( "in_worker_no ", OracleType.VarChar, 6);
p3.Value = in_worker_no;
p3.Direction = ParameterDirection.Input;

OracleParameter p4 = new OracleParameter( "in_notes ", OracleType.VarChar, 50);
p4.Value = in_notes;
p4.Direction = ParameterDirection.Input;

OracleParameter p5 = new OracleParameter( "out_flag ", OracleType.VarChar, 4);
p5.Direction = ParameterDirection.Output;
OracleParameter p6 = new OracleParameter( "out_mess ", OracleType.VarChar, 200);
p6.Direction = ParameterDirection.Output;

OracleConnection conn = DB.CreateHnmater();
conn.Open();
OracleCommand cmd = new OracleCommand( "mp_node_create ",conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cmd.Parameters.Add(p4);
cmd.ExecuteNonQuery();
return p6.Value.ToString();
}

调用:
protected void Page_Load(object sender, EventArgs e)
{
Label1.Text = mp_node( "PA ", "hys ", "001 ", "text ");


}

存储过程:
create or replace procedure mp_node_create
(in_busi_id in varchar2,
in_bureau_id in varchar2,
in_worker_no in varchar2,
in_notes in varchar2,
out_flag out varchar2, -- 0 成功 1 失败
out_mess out varchar2) --如果 则返回 list_id
is
ls_list_id varchar2(12);
ls_re_mess varchar2(40);
ls_bureau_id varchar2(6);
ls_worker_no varchar2(6);
ls_cur_node_id varchar2(3);
ls_cur_action_id varchar2(3);
ls_cur_seq integer;
ls_exe_seq integer;
begin
ls_bureau_id := in_bureau_id;
ls_worker_no := in_worker_no;
begin
if in_busi_id is null or in_busi_id= ' ' then
out_flag := '1 ';
out_mess := '输入的参数有误 ';
else
begin
select node_id,action_id
into ls_cur_node_id,ls_cur_action_id
from m_busi_control
where busi_id = in_busi_id and order_seq = 1;
begin
mp_get_listid(in_bureau_id,in_busi_id,ls_list_id,ls_re_mess);
if ls_list_id= '@ ' then
out_flag := '1 ';
out_mess := ls_re_mess;
else
begin


insert into m_exec_node
(list_id,exe_seq,cur_seq,busi_id,cur_node_id,cur_action_id,cur_option_id,create_date,finish_date,note,bureau_id,create_worker_no)
values (ls_list_id,1,1,in_busi_id,ls_cur_node_id,ls_cur_action_id, '0 ',sysdate,null,in_notes,ls_bureau_id,in_worker_no);
begin
insert into m_exec_log
(list_id,exe_seq,cur_seq,busi_id,cur_node_id,cur_action_id,cur_option_id,create_date,finish_date,note,bureau_id,create_worker_no)
select list_id,exe_seq,cur_seq,busi_id,cur_node_id,cur_action_id,cur_option_id,create_date,finish_date,note,bureau_id,create_worker_no
from m_exec_node where list_id = ls_list_id and bureau_id =ls_bureau_id;
out_flag := '0 ';
out_mess := ls_list_id;
exception
when others then
out_flag := '1 ';


out_mess := '写入m_exec_log表出错 '||substr(sqlerrm,1,100);
end;
exception
when others then
out_flag := '1 ';
out_mess := '写入m_exec_node表出错 '||substr(sqlerrm,1,100);
end;
end if;
end;
exception
when no_data_found then
out_flag := '1 ';
out_mess := '无此业务流程配置 - m_busi_control '||substr(sqlerrm,1,100);
when too_many_rows then
out_flag := '1 ';
out_mess := '此业务流程配置有误 - m_busi_control '||substr(sqlerrm,1,100);
when others then
out_flag := '1 ';
out_mess := '取此业务流程配置出错 - m_busi_control '||substr(sqlerrm,1,100);
end;
end if;
end;
end mp_node_create;


报错:
ORA-06550: line 1, column 7:


PLS-00306: wrong number or types of arguments in call to 'MP_NODE_CREATE '
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

[解决办法]
你存储过程包括4个输入参数,2个输出参数
而你在添加参数时,只添加了4个输入参数,未添加2个输出参数
输出参数也需要添加进去,否则,系统怎么知道输出到哪里呢?
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cmd.Parameters.Add(p4);
cmd.Parameters.Add(p5);
cmd.Parameters.Add(p6);
cmd.ExecuteNonQuery();

这样试试吧

读书人网 >C#

热点推荐