oracle pl/sql 中验证用户名和密码的函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9337956/
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
Function to verify username and password in pl/sql
提问by Posidon
I've got a table called BANKCUSTOMER with the following columns:
我有一个名为 BANKCUSTOMER 的表，其中包含以下列：
USERNAME NOT NULL VARCHAR2(11)
FAMILY_NAME NOT NULL VARCHAR2(25)
NAME NOT NULL VARCHAR2(25)
PASSWD NOT NULL VARCHAR2(6)
I want to make a function which checks in the database if the users USERNAME and PASSWORD matches the data in the database. If the login succeeds then it should print out "Login successful!" otherwise "Wrong username or password!"
如果用户 USERNAME 和 PASSWORD 与数据库中的数据匹配，我想创建一个函数来检查数据库。如果登录成功，那么它应该打印出“登录成功！” 否则“错误的用户名或密码！”
I visited a pl/sql tutorial site and came over the following code which i modified a bit so it can work with my database, but there is something I don't understand and that is what z numberdoes and what begin select 1 into zdoes. Could someone please explain that for me.
我访问了一个 pl/sql 教程站点并找到了以下代码，我对其进行了一些修改，以便它可以与我的数据库一起使用，但是有一些我不明白的东西，那就是z number它的作用和begin select 1 into z作用。有人可以为我解释一下。
create or replace function log_in(x in varchar2, y in varchar2)
return varchar2
as
z number;
begin
select 1
into z
from bankcustomer
where username=x
and passwd=y;
dbms_output.put_line('Login successful!');
exception
when no_data_found then
dbms_output.put_line('Wrong username or password!');
end;
I would like to test the function by writing SELECT log_in() FROM dual;to see if it works. When I write SELECT log_in() FROM dual;I get an error message saying:
我想通过写作来测试该功能，SELECT log_in() FROM dual;看看它是否有效。当我写SELECT log_in() FROM dual;我收到一条错误消息说：
Error starting at line 1 in command: SELECT log_in() FROM dual Error at Command Line:1 Column:7 Error report: SQL Error: ORA-06553: PLS-306: wrong number or types of arguments in call to 'LOG_IN' 06553. 00000 - "PLS-%s: %s" *Cause:
*Action:
从命令中的第 1 行开始出错：SELECT log_in() FROM dual Error at Command Line:1 Column:7 错误报告：SQL 错误：ORA-06553：PLS-306：调用 'LOG_IN' 06553 的参数数量或类型错误. 00000 - "PLS-%s: %s" *原因：
*操作：
How can this be resolved?
如何解决这个问题？
回答by a_horse_with_no_name
You have defined a function but do not return a value from it. Given the fact that you "select" the function there is no need to use dbms_output:
您已经定义了一个函数，但没有从中返回值。鉴于您“选择”该函数，因此无需使用 dbms_output：
create or replace function log_in(x in varchar2, y in varchar2)
return varchar2
as
match_count number;
begin
select count(*)
into match_count
from bankcustomer
where username=x
and passwd=y;
if match_count = 0 then
return 'Wrong username or password!';
elsif match_count = 1 then
return 'Login successful!';
else
return 'Too many matches, this should never happen!';
end if;
end;
/
Additionally your call to the function does not provide the username and password parameters, that's why you get the error message. Assuming you have changed the function to actually return something, you need to use
此外，您对该函数的调用不提供用户名和密码参数，这就是您收到错误消息的原因。假设您已更改函数以实际返回某些内容，则需要使用
SELECT log_in('username', 'secretpassword') FROM dual;
回答by Irfy
Have you actually passed any arguments to the log_infunction? And what is logga_in()? Is the latter a typo on your side?
你真的给log_in函数传递了任何参数吗？什么是logga_in()？后者是你的错字吗？
Anyway, the select 1 into zonly forces an exception in case no match is found. Nothing more.
无论如何，select 1 into z只有在找不到匹配项的情况下才会强制异常。而已。
In other words, you could write the code without it, for example with select count(*) into authenticated ...and then you could check if authenticated != 0and do the appropriate action. I don't have an Oracle instance so this code is written blindly, you'll need to test it:
换句话说，您可以在没有它的情况下编写代码，例如使用select count(*) into authenticated ...然后您可以检查if authenticated != 0并执行适当的操作。我没有Oracle实例，所以这段代码是盲目编写的，您需要对其进行测试：
create or replace function log_in(x in varchar2, y in varchar2)
return varchar2
as
match_count number;
begin
select count(*)
into match_count
from bankcustomer
where username=x
and passwd=y;
if match_count = 0 then
dbms_output.put_line('Wrong username or password!');
elsif match_count = 1 then
dbms_output.put_line('Login successful!');
else
dbms_output.put_line('Too many matches, this should never happen!');
end;
回答by Beverly
Just to add more information to what's already been provided, the BEGINkeyword indicates the beginning of the execution block; what's above that is the function header and any declaration statements.
只是为了给已经提供的内容添加更多信息，BEGIN关键字表示执行块的开始；上面是函数头和任何声明语句。
The statement z number;is a variable declaration statement declaring a variable that is named z and is of the datatype number. The SELECT 1 INTO z WHERE...statement is checking the BANKCUSTOMERtable for a row where the username matches what's passed to the function in the first parameter, and a password that matches what's passed to the function in the second parameter.
声明z号；是一个变量声明语句，声明一个名为 z 且数据类型为 number 的变量。该SELECT 1 INTO z WHERE...语句正在检查BANKCUSTOMER表中用户名与第一个参数中传递给函数的内容相匹配的行，以及与第二个参数中传递给函数的内容相匹配的密码。
If there is a row where the username and password match what's passed to the function, then the variable z will contain the number 1. If there isn't, the Oracle NO_ROWS_FOUND exceptionwill be raised, because SELECT...INTOstatements must always select one and only one row, or else they will raise an exception (the NO_ROWS_FOUND exceptionfor no rows, and the TOO_MANY_ROWS exceptionfor more than one row).
如果有一行的用户名和密码与传递给函数的内容相匹配，则变量 z 将包含数字 1。如果没有，Oracle NO_ROWS_FOUND exception则将引发 ，因为SELECT...INTO语句必须始终选择一行，或者否则他们将引发异常（NO_ROWS_FOUND exception无行和TOO_MANY_ROWS exception多行）。
Hope that's helpful! Don't hesitate to ask if you have more questions.
希望这有帮助！如果您有更多问题，请随时提出。
