在PL/SQL中建立的Oracle存储过程。如下:
CREATE OR REPLACE PACKAGE BODY PS_GTOPS_HANDSET_PAK IS
PROCEDURE logon(i_userid VARCHAR2,
i_password IN VARCHAR2,
o_true OUT VARCHAR2) IS
BEGIN
DECLARE
CURSOR c1 IS
SELECT USR_PASSWD FROM PS_USERS WHERE USR_USERID = i_userid;
CURSOR c2 IS
SELECT SYE_CTRMSHRTNM TER_TERMCD FROM PS_SYSTEM_ENVIRONMENTS;
c2_rec c2%rowtype;
c1_rec c1%rowtype;
BEGIN
OPEN c1;
FETCH c1
INTO c1_rec;
IF c1%found THEN
IF c1_rec.USR_PASSWD <> i_password THEN
o_true := '对不起,口令不对';
ELSE
OPEN c2;
FETCH c2
INTO c2_rec;
IF c2%found THEN
o_true := c2_rec.TER_TERMCD;
END IF;
END IF;
ELSE
o_true := '对不起,工号不对';
END IF;
IF C1%ISOPEN THEN
CLOSE C1;
END IF;
END;
END;
C#方面调用程序如下:
string con = "Server = gtops;User Id=gtops;Password=gtops";
OracleConnection conn = new OracleConnection(con) OracleCommand cmd = new OracleCommand("ps_gtops_handset_pak.logon", conn);
cmd.CommandType = CommandType.StoredProcedure; OracleParameter p1 = new OracleParameter("i_userid", OracleType.VarChar, 10);
p1.Direction = System.Data.ParameterDirection.Input;
p1.Value = "22058"; OracleParameter p2 = new OracleParameter("i_password", OracleType.VarChar, 10);
p2.Direction = System.Data.ParameterDirection.Input;
p2.Value = "18184"; OracleParameter p3 = new OracleParameter("o_true", OracleType.VarChar, 100);
p3.Direction = System.Data.ParameterDirection.ReturnValue; cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3); conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
string d = p3.Value.ToString();
程序执行到cmd.ExecuteNonQuery();时,报错,报错信息如下:ORA-06550: 第 1 行, 第 18 列:
PLS-00306: 调用 'LOGON' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored
此程序我都是从网上的例上找到的,然后写出来的,但是还是报错,我很不解,请大家帮我看看
很急!答对马上结贴给分。
CREATE OR REPLACE PACKAGE BODY PS_GTOPS_HANDSET_PAK IS
PROCEDURE logon(i_userid VARCHAR2,
i_password IN VARCHAR2,
o_true OUT VARCHAR2) IS
BEGIN
DECLARE
CURSOR c1 IS
SELECT USR_PASSWD FROM PS_USERS WHERE USR_USERID = i_userid;
CURSOR c2 IS
SELECT SYE_CTRMSHRTNM TER_TERMCD FROM PS_SYSTEM_ENVIRONMENTS;
c2_rec c2%rowtype;
c1_rec c1%rowtype;
BEGIN
OPEN c1;
FETCH c1
INTO c1_rec;
IF c1%found THEN
IF c1_rec.USR_PASSWD <> i_password THEN
o_true := '对不起,口令不对';
ELSE
OPEN c2;
FETCH c2
INTO c2_rec;
IF c2%found THEN
o_true := c2_rec.TER_TERMCD;
END IF;
END IF;
ELSE
o_true := '对不起,工号不对';
END IF;
IF C1%ISOPEN THEN
CLOSE C1;
END IF;
END;
END;
C#方面调用程序如下:
string con = "Server = gtops;User Id=gtops;Password=gtops";
OracleConnection conn = new OracleConnection(con) OracleCommand cmd = new OracleCommand("ps_gtops_handset_pak.logon", conn);
cmd.CommandType = CommandType.StoredProcedure; OracleParameter p1 = new OracleParameter("i_userid", OracleType.VarChar, 10);
p1.Direction = System.Data.ParameterDirection.Input;
p1.Value = "22058"; OracleParameter p2 = new OracleParameter("i_password", OracleType.VarChar, 10);
p2.Direction = System.Data.ParameterDirection.Input;
p2.Value = "18184"; OracleParameter p3 = new OracleParameter("o_true", OracleType.VarChar, 100);
p3.Direction = System.Data.ParameterDirection.ReturnValue; cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3); conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
string d = p3.Value.ToString();
程序执行到cmd.ExecuteNonQuery();时,报错,报错信息如下:ORA-06550: 第 1 行, 第 18 列:
PLS-00306: 调用 'LOGON' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored
此程序我都是从网上的例上找到的,然后写出来的,但是还是报错,我很不解,请大家帮我看看
很急!答对马上结贴给分。
p3.Direction = System.Data.ParameterDirection.ReturnValue;--------------
out的,不是ReturnValue
new OracleParameter("i_userid", OracleType.VarChar, 10);
这里都应该是 OracleType.VarChar2嘛