oracle 无法调用oracle存储过程和函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7736447/
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
Cannot call oracle stored procedure and function
提问by Kumar Kush
Might be too simple question to ask, but I do need help.
问的问题可能太简单了，但我确实需要帮助。
I am creating a stored procedure in Oracle 10g, but I cannot call it. I am using SQL Developer to manage the database.
我正在 Oracle 10g 中创建一个存储过程，但我无法调用它。我正在使用 SQL Developer 来管理数据库。
CREATE OR REPLACE
FUNCTION check_login
(username IN VARCHAR2, pwd IN VARCHAR2)
RETURN VARCHAR2
IS
isUserValid INTEGER;
BEGIN
SELECT Count(*) INTO isUserValid
FROM users
WHERE Username = username AND PASS_WORD = pwd;
return isUserValid;
END;
I have tried this also:
我也试过这个：
CREATE OR REPLACE
PROCEDURE check_login
(username IN VARCHAR2, pwd IN VARCHAR2, RESULT OUT INTEGER)
IS
isUserValid INTEGER;
BEGIN
SELECT Count(*) INTO isUserValid
FROM users
WHERE Username = username AND PASS_WORD = pwd;
RESULT := isUserValid;
END;
Parsing both does not give any error message. I used following syntax to call them:
解析两者都不会给出任何错误消息。我使用以下语法来调用它们：
BEGIN
check_login('admin', 'admin');
END;
AND
和
EXECUTE check_login('admin', 'admin');
I get this error message....
我收到此错误消息....
PLS-00221: 'CHECK_LOGIN' is not a procedure or is undefined
PL/SQL: Statement ignored
PLS-00221：'CHECK_LOGIN' 不是过程或未定义
PL/SQL：语句被忽略
The SELECT statement inside both works fine if run directly.
如果直接运行，两者中的 SELECT 语句都可以正常工作。
Am I doing something wrong?
难道我做错了什么？
回答by josephj1989
If you want to execute a function you have to collect the return value into a variable.
如果你想执行一个函数，你必须将返回值收集到一个变量中。
So you need to define a variable and execute function to return into the variable as below
所以你需要定义一个变量并执行函数来返回到变量中，如下所示
and run it using the run Scriptoption not the Run Statementoption.
并使用运行脚本选项而不是运行语句选项运行它。
variable ret varchar2(20);
execute :ret:=check_login(dd,dd);
select :ret from dual
Or if you do it from plsql
或者，如果您从 plsql 执行此操作
declare v_ret varchar2(100);
begin
v_ret:=check_login(a,b);
end;
回答by Steve Ronaldson Ewing
I find the easiest way to call a function is just selecting the function from dual eg-
我发现调用函数的最简单方法就是从双例如中选择函数-
select check_login('admin', 'admin') from dual;
Note the error message said "No procedure' :).
请注意错误消息说“没有过程' :)。
