存储过程代码
create or replace procedure add_readers(i_rname in readers.rname%type,
i_ridcard in readers.ridcard%type,
i_rsex in readers.rsex%type,
i_runit in readers.runit%type,
i_radd in readers.radd%type,
i_rphone in readers.rphone%type,
o_result out number)
as
o_readers number;
begin
o_result:=0;
select count(*)
into o_readers
from readers a
where rname = i_rname
and ridcard = i_ridcard;
if o_readers < 1 then
insert into readers
(rid, rname, ridcard, rsex, runit, radd, rphone, rregdate)
values
(SEQ_READERS.NEXTVAL,
i_rname,
i_ridcard,
i_rsex,
i_runit,
i_radd,
i_rphone,
sysdate);
commit;
/* dbms_output.put_line('插入成功!');*/
o_result:=1;
else
update readers a
set a.rstate=''
where a.rname = i_rname
and a.ridcard = i_ridcard;
commit;
/* dbms_output.put_line('该读者已存在!并重新激活');*/
o_result:=2;
end if;
dbms_output.put_line(o_result);
end add_readers;
c#代码:
private void but_submit_Click(object sender, EventArgs e)
{
string sex;
decimal result = 0;
if (radio_man.Checked == true)
{
sex = "男";
}
else
{
sex = "女";
}
if (name_box.Text.Length > 0 && idcard_box.Text.Length > 0 && adress_box.Text.Length > 0 && uint_box.Text.Length > 0 && phone_box.Text.Length > 0)
{
OracleConnection conn = new OracleConnection("Data Source=ORCL;User ID=HJR;Password=HJR;Unicode=True");
OracleCommand cmd = new OracleCommand("add_readers", conn);
cmd.CommandType = CommandType.StoredProcedure;//指明是执行存储过程
OracleParameter[] parm = new OracleParameter[7];
//in
parm[0] = new OracleParameter("i_rname", OracleType.VarChar,255); //与SQL区别,sql存储过程需要在定义与此处,在其参数前加"@"符号;
parm[1] = new OracleParameter("i_ridcard", OracleType.VarChar,255);
parm[2] = new OracleParameter("i_rsex", OracleType.VarChar, 255);
parm[3] = new OracleParameter("i_runit", OracleType.VarChar,255);
parm[4] = new OracleParameter("i_radd", OracleType.VarChar,255);
parm[5] = new OracleParameter("i_rphone", OracleType.VarChar,255);
//out
parm[6] = new OracleParameter("o_result", OracleType.Number);
//指明参数是输入还是输出型
parm[0].Direction = ParameterDirection.Input;
parm[1].Direction = ParameterDirection.Input;
parm[2].Direction = ParameterDirection.Input;
parm[3].Direction = ParameterDirection.Input;
parm[4].Direction = ParameterDirection.Input;
parm[5].Direction = ParameterDirection.Input;
parm[6].Direction = ParameterDirection.Output;
parm[0].Value = name_box.Text.Trim();
parm[1].Value = sex;
parm[2].Value = idcard_box.Text.Trim();
parm[3].Value = uint_box.Text.Trim();
parm[4].Value = adress_box.Text.Trim();
parm[5].Value = phone_box.Text.Trim();
try
{
conn.Open();
int i = cmd.ExecuteNonQuery();
/*result = (decimal)cmd.Parameters["o_result"].Value;*/
result = Convert.ToDecimal(parm[6].Value);
}
catch (OracleException ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
cmd.Connection.Dispose();
cmd.Parameters.Clear();
cmd.Dispose();
}
//return result;
}
if (result == 1)
{
MessageBox.Show("新增读者成功!");
}
else if (result == 2)
{
MessageBox.Show("该读者已存在并激活!");
}
else
{
MessageBox.Show("新增读者失败!");
}
}
else
{
MessageBox.Show("请填写完整信息!");
}
}
各位大大能帮忙看下是那里的问题吗,本人刚接触oracle存储OracleC#错误
create or replace procedure add_readers(i_rname in readers.rname%type,
i_ridcard in readers.ridcard%type,
i_rsex in readers.rsex%type,
i_runit in readers.runit%type,
i_radd in readers.radd%type,
i_rphone in readers.rphone%type,
o_result out number)
as
o_readers number;
begin
o_result:=0;
select count(*)
into o_readers
from readers a
where rname = i_rname
and ridcard = i_ridcard;
if o_readers < 1 then
insert into readers
(rid, rname, ridcard, rsex, runit, radd, rphone, rregdate)
values
(SEQ_READERS.NEXTVAL,
i_rname,
i_ridcard,
i_rsex,
i_runit,
i_radd,
i_rphone,
sysdate);
commit;
/* dbms_output.put_line('插入成功!');*/
o_result:=1;
else
update readers a
set a.rstate=''
where a.rname = i_rname
and a.ridcard = i_ridcard;
commit;
/* dbms_output.put_line('该读者已存在!并重新激活');*/
o_result:=2;
end if;
dbms_output.put_line(o_result);
end add_readers;
c#代码:
private void but_submit_Click(object sender, EventArgs e)
{
string sex;
decimal result = 0;
if (radio_man.Checked == true)
{
sex = "男";
}
else
{
sex = "女";
}
if (name_box.Text.Length > 0 && idcard_box.Text.Length > 0 && adress_box.Text.Length > 0 && uint_box.Text.Length > 0 && phone_box.Text.Length > 0)
{
OracleConnection conn = new OracleConnection("Data Source=ORCL;User ID=HJR;Password=HJR;Unicode=True");
OracleCommand cmd = new OracleCommand("add_readers", conn);
cmd.CommandType = CommandType.StoredProcedure;//指明是执行存储过程
OracleParameter[] parm = new OracleParameter[7];
//in
parm[0] = new OracleParameter("i_rname", OracleType.VarChar,255); //与SQL区别,sql存储过程需要在定义与此处,在其参数前加"@"符号;
parm[1] = new OracleParameter("i_ridcard", OracleType.VarChar,255);
parm[2] = new OracleParameter("i_rsex", OracleType.VarChar, 255);
parm[3] = new OracleParameter("i_runit", OracleType.VarChar,255);
parm[4] = new OracleParameter("i_radd", OracleType.VarChar,255);
parm[5] = new OracleParameter("i_rphone", OracleType.VarChar,255);
//out
parm[6] = new OracleParameter("o_result", OracleType.Number);
//指明参数是输入还是输出型
parm[0].Direction = ParameterDirection.Input;
parm[1].Direction = ParameterDirection.Input;
parm[2].Direction = ParameterDirection.Input;
parm[3].Direction = ParameterDirection.Input;
parm[4].Direction = ParameterDirection.Input;
parm[5].Direction = ParameterDirection.Input;
parm[6].Direction = ParameterDirection.Output;
parm[0].Value = name_box.Text.Trim();
parm[1].Value = sex;
parm[2].Value = idcard_box.Text.Trim();
parm[3].Value = uint_box.Text.Trim();
parm[4].Value = adress_box.Text.Trim();
parm[5].Value = phone_box.Text.Trim();
try
{
conn.Open();
int i = cmd.ExecuteNonQuery();
/*result = (decimal)cmd.Parameters["o_result"].Value;*/
result = Convert.ToDecimal(parm[6].Value);
}
catch (OracleException ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
cmd.Connection.Dispose();
cmd.Parameters.Clear();
cmd.Dispose();
}
//return result;
}
if (result == 1)
{
MessageBox.Show("新增读者成功!");
}
else if (result == 2)
{
MessageBox.Show("该读者已存在并激活!");
}
else
{
MessageBox.Show("新增读者失败!");
}
}
else
{
MessageBox.Show("请填写完整信息!");
}
}
各位大大能帮忙看下是那里的问题吗,本人刚接触oracle存储OracleC#错误
{
cmd.Parameters.Add(parameter);
};
希望这个低级错误供部分初学参考