存储过程如下,就是取最大值。(oracle下调试可以通过)
CREATE OR REPLACE PROCEDURE get_maxsequencenumber
(v_MaxSequenceNumber OUT NUMBER,
P_TableName in varchar2 )
AS
BEGIN
DECLARE
BEGIN
--根据传递的参数“表名”获取表中是否存在初始值
SELECT count(*)
INTO v_MaxSequenceNumber
FROM b_generatecode
where tablename=P_TableName;
--判断初始值是否存在
if v_MaxSequenceNumber > 0 then
SELECT generatecode
INTO v_MaxSequenceNumber
FROM b_generatecode
where tablename=P_TableName;
--取最大值加1
v_MaxSequenceNumber := v_MaxSequenceNumber + 1;
--修改记录
UPDATE b_generatecode
SET generatecode = v_MaxSequenceNumber
WHERE tablename=P_TableName;
COMMIT;
else
--如果没有记录,插入初始记录
INSERT INTO b_generatecode
(TABLENAME,
generatecode)
VALUES
(P_TableName,
'100');
COMMIT;
end if;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20101,sqlerrm||'INSTALLATION - Error in GRANTING EXECUTE ON QUEST_SL_USER_MANAGER');
END;
END;下面是我写的取该返回值得例子
OracleCommand cmd = new OracleCommand();
OracleConnection conn = new OracleConnection(ConnStr); //连接串 cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "get_maxsequencenumber";
OracleParameter p1 = new OracleParameter(":v_MaxSequenceNumber",OracleType.Number);
p1.Direction = ParameterDirection.ReturnValue; OracleParameter p2 = new OracleParameter(":P_Tabl",OracleType.VarChar,30);
p2.Direction = ParameterDirection.Input;
p2.Value = TableName; cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2); if(conn.State != ConnectionState.Open)
conn.Open(); cmd.Connection = conn;
cmd.ExecuteNonQuery();
conn.Close();
MaxCol = Int32.Parse(p2.Value.ToString());
在执行到
cmd.ExecuteNonQuery();后就出现如下提示:{"ORA-01036: 非法的变量名/编号\n" }
请问是怎么回事啊?谢谢
(我的返回参数是在第一个参数阿,c#中的名称就是存储过程中参数的名称阿)
CREATE OR REPLACE PROCEDURE get_maxsequencenumber
(v_MaxSequenceNumber OUT NUMBER,
P_TableName in varchar2 )
AS
BEGIN
DECLARE
BEGIN
--根据传递的参数“表名”获取表中是否存在初始值
SELECT count(*)
INTO v_MaxSequenceNumber
FROM b_generatecode
where tablename=P_TableName;
--判断初始值是否存在
if v_MaxSequenceNumber > 0 then
SELECT generatecode
INTO v_MaxSequenceNumber
FROM b_generatecode
where tablename=P_TableName;
--取最大值加1
v_MaxSequenceNumber := v_MaxSequenceNumber + 1;
--修改记录
UPDATE b_generatecode
SET generatecode = v_MaxSequenceNumber
WHERE tablename=P_TableName;
COMMIT;
else
--如果没有记录,插入初始记录
INSERT INTO b_generatecode
(TABLENAME,
generatecode)
VALUES
(P_TableName,
'100');
COMMIT;
end if;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20101,sqlerrm||'INSTALLATION - Error in GRANTING EXECUTE ON QUEST_SL_USER_MANAGER');
END;
END;下面是我写的取该返回值得例子
OracleCommand cmd = new OracleCommand();
OracleConnection conn = new OracleConnection(ConnStr); //连接串 cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "get_maxsequencenumber";
OracleParameter p1 = new OracleParameter(":v_MaxSequenceNumber",OracleType.Number);
p1.Direction = ParameterDirection.ReturnValue; OracleParameter p2 = new OracleParameter(":P_Tabl",OracleType.VarChar,30);
p2.Direction = ParameterDirection.Input;
p2.Value = TableName; cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2); if(conn.State != ConnectionState.Open)
conn.Open(); cmd.Connection = conn;
cmd.ExecuteNonQuery();
conn.Close();
MaxCol = Int32.Parse(p2.Value.ToString());
在执行到
cmd.ExecuteNonQuery();后就出现如下提示:{"ORA-01036: 非法的变量名/编号\n" }
请问是怎么回事啊?谢谢
(我的返回参数是在第一个参数阿,c#中的名称就是存储过程中参数的名称阿)
这条语句中变量名不需要加":"的,直接写名字就可以了
p1.Direction = ParameterDirection.ReturnValue;
改为:
p1.Direction = ParameterDirection.Output;MaxCol = Int32.Parse(p2.Value.ToString());
改为:
MaxCol = Int32.Parse(p1.Value.ToString());
根据你的提示,目前已经可以取出来了。
3Q
另外说一下,如果在SQL Server中,修改为p1.Direction = ParameterDirection.IntputOutput;
也可以取出存储过程的值。