C# 使用 OracleCommand 执行 pl/sql 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13662497/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Execute a pl/sql function with OracleCommand
提问by Kristian Sthefan Cortes Prieto
i have this pl/sql function, the only thing it does is validate that the user exist in the database, if the user exists this returns "Y" but if the user dont exist this return "N", what I want is get the value that I return in pl/sql in c #.
我有这个 pl/sql 函数，它唯一做的就是验证用户是否存在于数据库中，如果用户存在则返回“Y”但如果用户不存在则返回“N”，我想要的是获取我在 c# 中的 pl/sql 中返回的值。
I am using oracle 10g
我正在使用 oracle 10g
CREATE OR REPLACE FUNCTION KRIST.f_Login (userName IN VARCHAR2,
password IN VARCHAR2)
RETURN VARCHAR2
IS
CURSOR USERFINDER IS
SELECT IdEmpleado
FROM EMPLEADO
WHERE Usuario=userName
AND Clave=password;
id number;
returnVal VARCHAR2(1);
BEGIN
OPEN USERFINDER;
FETCH USERFINDER INTO id;
IF(id IS NULL) THEN
returnVal:='Y';
RETURN returnVal;
END IF;
returnVal:='N';
RETURN returnVal;
CLOSE USERFINDER;
END;
/
how I can perform this function and get the result in a variable... i have thos code but dont works
我如何执行此功能并在变量中获得结果...我有代码但不起作用
OracleCommand cmd = new OracleCommand("krist.p_login",conn);
cmd.CommandType = CommandType.StoredProcedure; // use StoredProcedure with Functions as well
OracleParameter returnVal = new OracleParameter("returnVal",null);
OracleParameter p_one = new OracleParameter("userName","kristian");
OracleParameter p_two = new OracleParameter("password", "kristian");
returnVal.OracleType = OracleType.VarChar;
returnVal.Size = 1;
p_one.OracleType = OracleType.VarChar;
p_two.OracleType = OracleType.VarChar;
p_one.DbType = DbType.String;
p_two.DbType = DbType.String;
returnVal.DbType = DbType.String;
returnVal.Direction = ParameterDirection.ReturnValue;
p_one.Direction = ParameterDirection.Input;
p_two.Direction = ParameterDirection.Input;
cmd.Parameters.Add(p_one);
cmd.Parameters.Add(p_two);
cmd.Parameters.Add(returnVal);
cmd.ExecuteNonQuery();
String bval = Convert.ToString(returnVal.Value);
return bval;
采纳答案by SurfingSanta
The following code works for me.
NB: Your pl/sql code called the function KRIST.f_Login, but your c# called it krist.p_login
NB2: Your pl/sql code used Varchar2, but your c# used varchar
NB3: I am using Oracle.DataAccess.dll
NB4: I assume your return value buffer size could be 1, but try different sizes.
以下代码对我有用。
注意：您的 pl/sql 代码称为函数 KRIST.f_Login，但您的 c# 将其称为 krist.p_login
NB2：您的 pl/sql 代码使用了 Varchar2，但您的 c# 使用了 varchar
NB3：我使用的是 Oracle.DataAccess.dll
NB4：我假设您的返回值缓冲区大小可能为 1，但请尝试不同的大小。
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
int RETURN_VALUE_BUFFER_SIZE = 32767;
OracleCommand cmd = new OracleCommand();
try {
cmd.Connection = conn;
cmd.CommandText = "KRIST.f_Login";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("returnVal", OracleDbType.Varchar2, RETURN_VALUE_BUFFER_SIZE);
cmd.Parameters["returnVal"].Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add("userName", OracleDbType.Varchar2);
cmd.Parameters["userName"].Value = "kristian";
cmd.Parameters.Add("password", OracleDbType.Varchar2);
cmd.Parameters["password"].Value = "kristian";
cmd.ExecuteNonQuery();
string bval = cmd.Parameters["returnVal"].Value.ToString();
return bval;
} catch (Exception e) {
// deal with exception
} finally {
command.Dispose();
connection.Close();
connection.Dispose();
}
回答by Gumowy Kaczak
As far as I remember If you are using ODP.NET you need to provide retVal parameter as first.
据我所知，如果您使用的是 ODP.NET，则首先需要提供 retVal 参数。
Something is wrong with ODP.NET and it dosn't bind parameters with provided parameter names but with order of parameters.
ODP.NET 有问题，它不使用提供的参数名称绑定参数，而是使用参数的顺序。
So simply change order to:
因此，只需将顺序更改为：
cmd.Parameters.Add(returnVal);
cmd.Parameters.Add(p_one);
cmd.Parameters.Add(p_two);
And in my sources I found that return parameter i called "RETURN" (not sure if it counts):
在我的消息来源中，我发现我称之为“RETURN”的返回参数（不确定它是否重要）：
OracleParameter returnVal = new OracleParameter("RETURN",null);
A ha and one more thing. It will never reach last line - cuase return would terminate execute. Close it as soon as you don't need it anymore.
哈哈还有一件事。它永远不会到达最后一行 - cuase return 将终止执行。一旦您不再需要它，请立即关闭它。
RETURN returnVal;
CLOSE USERFINDER; --<<-- won't close this cursor
回答by Mark Ainsworth
ODP.net binds by order by default. This behavior can be modified with: cmd.BindByName = true
ODP.net 默认按顺序绑定。此行为可以修改为： cmd.BindByName = true
