Oracle SQL:为什么我的函数输出为null? [英] Oracle SQL: Why is my function outputting null?

问题描述

CREATE OR REPLACE FUNCTION get_status_by_member_id
(p_member_id NUMBER) 
RETURN CHAR
AS
  v_status CHAR(1);
BEGIN
  select status
  into v_status
  from members
  where member_id = p_member_id;
  if v_status is null then
    return v_status || 'N';
  else
     return v_status;
  end if;
END get_status_by_member_id;

推荐答案

您的问题是由Oracle有时令人讨厌的怪癖引起的.即，如果在SQL中调用PL/SQL函数并出错，则返回null，而不返回其他任何内容.

Your problem is caused by a, sometimes, annoying quirk of Oracle. Namely that if a PL/SQL function is called in SQL and errors then null is returned, rather than anything else.

如果我创建一个非常简单的表...

If I create a very simple table...

create table a ( b number, c varchar2(1) );
insert into a values (1,'Y');

...和两个功能.一种具有 no 异常处理功能

... and two functions. One with no exception handling

create or replace function tmp_ben_fn (PId number) return char is
   l_status varchar2(1);   
begin  

   select c
     into l_status
     from a
    where b = PId;

   return coalesce(l_status, 'N');

end;
/

和一个具有异常处理功能的

and one with exception handling.

create or replace function tmp_ben_fn2 (PId number) return char is
   l_status varchar2(1);   
begin  

   select c
     into l_status
     from a
    where b = PId;

   return coalesce(l_status, 'N');

   exception when no_data_found then
      return 'A';

end;
/

然后，我们使用这两个功能选择数据.请记住，我们只有一行，因此我们期望只返回Y或N:

Then, we use these two functions to select the data. Remember we only have one row, so we're expecting only Y or N to be returned:

SQL> -- Expected output Y
SQL> select tmp_ben_fn(1) from dual;

TMP_BEN_FN2(1)
-------------------------------------------------

Y

SQL> -- Expected output, Error no_data_found
SQL> -- as row 2 does not exist.
SQL> select tmp_ben_fn(2) from dual;

TMP_BEN_FN(2)
-------------------------------------------------



SQL> -- Expected output, the same as tmp_ben_fn
SQL> select tmp_ben_fn2(2) from dual;

TMP_BEN_FN2(2)
-------------------------------------------------

A

SQL>

正如您在没有错误处理的函数中所看到的那样，当no_data_found异常发生时，将返回null.捕获异常时，将获得预期的A.

As you can see in the function with no error handling, when a no_data_found exception occurs a null is returned. When we capture the exception, we get the expected A instead.

我怀疑您要选择的member_id不存在.

I suspect that the member_id you're trying to select does not exist.

作为一般规则:请始终注意何时可能发生异常，select into...是一个特别令人担忧的示例，有机会选择太多的行和没有的行.如果表在member_id上是唯一的，则应按以下步骤更改函数:

As a general rule: Always be aware when an exception might occur, select into... being a particularly worrisome example with the opportunity to select both too many and no rows. If your table is unique on member_id then you should change your function as follows:

CREATE OR REPLACE FUNCTION get_status_by_member_id (p_member_id NUMBER) 
RETURN CHAR IS
  v_status CHAR(1);
BEGIN
  select status
  into v_status
  from members
  where member_id = p_member_id;

  return v_status;

-- If the member_id does not exist
-- return N
exception when no_data_found then
   return 'N';
END get_status_by_member_id;

这篇关于Oracle SQL:为什么我的函数输出为null?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！