Oracle 中的存储过程，其中包含来自不同查询的三个 OUT 参数

Question

我在创建具有一个 IN 和三个 OUT 参数的存储过程时遇到问题。基本上，我有三个查询，我想将它们放在存储过程中。

如何将 OUT 参数与每个查询相关联？

CREATE OR REPLACE PROCEDURE 'SP_NAME'(
 @phone_number IN VARCHAR2
 REG OUT INTEGER
 EMAIL OUT VARCHAR2
 VALID OUT INTEGER )
AS
BEGIN 
--I just need 1 or 0 to valid if exists on the table
SELECT COUNT (phone_number) FROM users
  WHERE phone_number = @phone_number

-- this should bring me the email a different table
SELECT email FROM details_users
  WHERE phone_number = @phone_number
-- and this bring if exists in oooother table 
SELECT count(phone_number) FROM suscribers
  WHERE phone_number = @phone_number
END;

Answer 1

修改后的业务逻辑包含三个表。在没有任何迹象表明表内容相关的情况下，最好将它们保留为单独的查询。

这是我的解决方案(具有有效的 Oracle 语法(。

CREATE OR REPLACE PROCEDURE SP_NAME (
  p_phone_number IN VARCHAR2
  , p_REG OUT INTEGER
  , p_EMAIL OUT VARCHAR2
  , p_VALID OUT INTEGER )    
AS
  l_usr_cnt pls_integer;
  l_email details_users.email%type;
  l_sub_cnt pls_integer;
BEGIN 
  SELECT COUNT (*) into l_usr_cnt
  FROM users u
  WHERE u.phone_number = p_phone_number;
  begin
      select du.email into l_email
      from details_users du
      where du.phone_number = p_phone_number;
  exception
      when others then
        l_email := null;
  end;
  SELECT COUNT (*) into l_sub_cnt
  FROM suscribers s -- probably a typo :-/
  WHERE s.phone_number = p_phone_number;
  if l_usr_cnt != 0 then
    p_reg := 1; -- "true"
  else
    p_reg := 0; -- "false"
  end if;
  p_email := l_email;
  if l_sub_cnt != 0 then
    p_valid := 1;
  else
    p_valid := 0;
  end if;
END;

请注意，允许直接从查询填充 OUT 参数，如下所示：

  SELECT COUNT (*) 
  into p_reg
  FROM users u
  WHERE u.phone_number = p_phone_number;

但是，使用局部变量并仅在过程结束时分配 OUT 参数是公认的良好做法。这是为了确保将一致的状态传递给调用程序(如果被调用过程引发异常，这一点尤其重要(。

另外，我为所有参数名称加上前缀 p_ .这也是可选的，但很好的做法。拥有不同的命名空间总是更安全，但当参数名称与表列名称匹配时尤其重要(即 phone_number (。

Answer 2

您可以使用

以下查询，然后将结果分配给每个 out 参数。我注意到第一个和第三个查询是相同的。是复制/粘贴错误吗？或者你的意思是电话号码是否有效？

SELECT COUNT (phone_number) INTO REG FROM users
WHERE phone_number = @phone_number;
SELECT email INTO EMAIL FROM users
WHERE phone_number = @phone_number;
REG := VALID;