Oracle nvl2无法在存储过程PLS-00201中工作：必须声明标识符'NVL2'

Question

作为标题，我正在Oracle上编写一些存储过程，首先我检查了版本

SELECT * FROM v$version;

有结果

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0  Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

...

并尝试NVL2与常规SQL查询

select 'Test: ' || nvl2('...', 'things', 'nothing') from dual;
select 'Test: ' || nvl2('', 'things', 'nothing') from dual;

结果似乎是对的

Test: things
Test: nothing

...

所以我确认11g确实支持NLV2功能，我现在可以开始编写我的存储过程，如下所示：

create or replace procedure my_schema.SP_READ_MEMBER(noP in varchar2, nameP in varchar2, idNoP in varchar2, birthdayP in varchar2, resultP out sys_refcursor)
is
v_prg_name varchar2(20) := 'SP_READ_MEMBER';
sys_sql    varchar2(1000);

begin
  Insertlog(SYSDATE, v_prg_name, '1.0 Start');
  sys_sql :=  sys_sql || 'select a.no, a.name, a.id_no, to_char(a.birthday, ''yyyy/MM/dd'') as birthday, ''REGISTERED'' as type, email, mobile from rep where 1=1 ';
  sys_sql :=  sys_sql || nvl2(noP,'and no='''|| noP ||'''', ''); --PLS-00201

  open resultP for sys_sql;
  Insertlog(SYSDATE, v_prg_name, '2.0 Finished w/o error');

  exception
  when others then
    declare
      error_time VARCHAR2(30) := RTRIM(TO_CHAR(SYSDATE, 'YYYY/MM/DD, HH24:MI:SS'));
      error_code NUMBER := SQLCODE;
      error_msg  VARCHAR2(300) := SQLERRM;
    begin
      rollback;
      DBMS_OUTPUT.PUT_LINE(error_time || ',' || TO_CHAR(error_code) || ',' || error_msg);
      Insertlog(SYSDATE, v_prg_name,  error_msg || ', 3.0 ERROR, sql:' || sys_sql);
    end;
end;
/

Oracle告诉我它编译有错误，即

PLS-00201：必须声明标识符“NVL2”

为什么函数在常规查询中工作，但在存储过程中变得未声明？

Answer 1

NVL2可以在SQL中工作，但不能在PL/SQL中工作。

改变：

sys_sql :=  sys_sql || nvl2(noP,'and no='''|| noP ||'''', '');

收件人：

select sys_sql || nvl2(noP,'and no='''|| noP ||'''', '') into sys_sql from dual;

Answer 2

除非您使用的是21c或更高版本，否则NVL2可以在SQL中工作，但不能在PL/SQL中工作。

并不是每个SQL关键字都能在PL/SQL中工作，这很愚蠢，但以前已经发生过几次了。Oracle一直在逐步统一SQL和PL/SQL，很可能他们最终会实现这个功能。目前，我认为我的Oracle支持文档《注释359506.1 Pls-00201将NVL2（）的结果赋给PLSQL块中的变量》中已经涵盖了这个问题。尽管该文档不可用，甚至对具有支持访问权限的人员也不可用。

目前，我建议使用不同的语法。就我个人而言，我发现案例更清晰，即使它有点冗长。

改变：

sys_sql :=  sys_sql || nvl2(noP,'and no='''|| noP ||'''', '');

收件人：

sys_sql :=  sys_sql || case when nop is not null then 'and no='''|| noP ||'''' else '' end;