在oracle中执行存储过程 [英] Execute a stored procedure in oracle

问题描述

我需要根据通过提示传递的值在uu中获取输出

I need to get the output in uu in accordance with value passed through the prompt

create or replace procedure chklg( uu out logn.username%TYPE
                                 , pass in logn.password%TYPE)
is
begin
select username into uu from logn where password=pass;
end; 

我尝试以这种方式执行上述过程:

I tried executing the above procedure this way:

begin 

chklg(:pass);

end

推荐答案

根据定义，过程不返回任何内容.您正在寻找 function .

By definition a procedure doesn't return anything. You're looking for a function.

create or replace function chklg ( p_pass in logn.password%TYPE
          ) return varchar2 is -- assuming that logn.username%TYP is a varchar2

   l_uu logn.username%type;

begin
   select username into l_uu from logn where password = p_pass;
   return l_uu;
-- If there-s no username that matches the password return null.
exception when no_data_found then
   return null;
end; 

我对此有些担心，因为好像您将密码存储为纯文本一样.这不是最佳做法.

I'm slightly worried by this as it appears as though you're storing a password as plain text. This is not best practice.

您应该在用户名旁边存储一个加盐和胡椒的密码哈希，然后对密码应用相同的加盐，胡椒和哈希，并从数据库中选择 hash .

You should be storing a salted and peppered hash of your password next to the username, then apply the same salting, peppering and hashing to the password and select the hash from the database.

您可以通过以下两种方式之一执行该功能:

You can execute the function either of the following two ways:

select chklg(:pass) from dual

或

declare
   l_pass logn.password%type;
begin
   l_pass := chklg(:pass);
end;
/

---

为完整起见，弗兰克·施密特(Frank Schmitt)在评论中提出了非常有效点.除了以非常危险的方式存储密码之外，如果两个用户使用相同的密码会发生什么?

---

To be complete Frank Schmitt has raised a very valid point in the comments. In addition to you storing the passwords in a very dangerous manner what happens if two users have the same password?

您将在SELECT INTO ...中引发TOO_MANY_ROWS异常.这意味着太多行返回给变量.最好也输入用户名.

You will get a TOO_MANY_ROWS exception raised in your SELECT INTO .... This means that too many rows are returned to the variable. It would be better if you passed the username in as well.

这可能会使您的函数看起来像下面的

This could make your function look something like the following

create or replace function chklg ( 
         p_password_hash in logn.password%type
       , p_username in logn.username%type
          ) return number

   /* Authenticate a user, return 1/0 depending on whether they have
      entered the correct password.
      */

   l_yes number := 0;

begin

   -- Assumes that username is unique.
   select 1 into l_yes 
     from logn
    where password_hash = p_password_hash
      and username = p_username;

   return l_yes;

-- If there-s no username that matches the password return 0.
exception when no_data_found then
   return 0;
end; 

---

如果您只想使用一个过程(没有真正的理由这样做，因为它不必要地限制了您；您没有执行任何DML)，则可以获取输出参数，但必须给处理可以填充的参数.

---

If you're looking to only use a procedure (there's no real reason to do this at all as it unnecessarily restricts you; you're not doing any DML) then you can get the output parameter but you have to give the procedure a parameter that it can populate.

在您的情况下，它看起来像这样.

In your case it would look something like this.

declare
   l_uu logn.username%type;
begin 
   chklg(l_uu, :pass);
   dbms_output.put_line(l_uu);
end;

这篇关于在oracle中执行存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！