Oracle PLSQL_OPTIMIZE_LEVEL 3产生错误的函数结果

我们研究了优化我们的PL/SQL代码库，并使用使用PLSQL_OPTIMIZE_LEVEL 3编译的所有软件包进行了测试。

乍一看，一切正常，但后来我们的代码开始表现得很奇怪。经过一些调查，我们发现PLSQL_OPTIMIZE_LEVEL = 3损坏了一些函数。

查看一个简单的示例，其中内联函数产生错误的结果:https://livesql.oracle.com/apex/livesql/s/ga8hkmbh6mxq5rpuw0tp5qnph

似乎l_test正在被优化器删除，因为它没有被使用。

一旦我们使用l_test，相同的函数开始产生正确的结果，正如在这个脚本中可以看到的:https://livesql.oracle.com/apex/livesql/s/ga8hkmbhafmqu4ilznduare5x

不幸的是，我无法找到任何关于优化级别3的功能的良好文档。所以我们不知道是否还有其他副作用。

这是已知的行为吗？我们还需要注意哪些事情？

这里的问题与子程序内联有关。

通过优化级别3，PL/SQL编译器将内联子程序。这开启了进一步的优化。在这种情况下，它似乎导致它排除分配给l_test:

ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL';
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 3;
ALTER SESSION SET PLSQL_CODE_TYPE      = 'NATIVE';

CREATE OR REPLACE PROCEDURE RAGTEST 
AS 
    FUNCTION is_nls_date 
      ( p_string        IN VARCHAR2 
      , p_format_mask   IN VARCHAR2 
      ) 
    RETURN NUMBER 
    AS 
        l_test   DATE; 
        l_return NUMBER := 0; 
    BEGIN 
        BEGIN 
            l_test   := TO_DATE(p_string, ''''||p_format_mask||''''); 
            l_return := 1; 
        EXCEPTION 
            WHEN OTHERS THEN 
                l_return := 0; 
        END; 
        -- 
        RETURN l_return; 
    END is_nls_date; 
BEGIN 
    DBMS_OUTPUT.PUT_LINE('is_date 01.01.xxxx = '||is_nls_date('01.01.xxxx','DD.MM.YYYY')); 
    DBMS_OUTPUT.PUT_LINE('is_date 01.01.2001 = '||is_nls_date('01.01.2001','DD.MM.YYYY')); 
END; 
/
sho err

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1      PLW-05018: unit RAGTEST omitted optional AUTHID clause; default value DEFINER used
3/5      PLW-06006: uncalled procedure "IS_NLS_DATE" is removed.
16/18    PLW-06009: procedure "IS_NLS_DATE" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR
23/5     PLW-06005: inlining of call of procedure 'IS_NLS_DATE' was done
24/5     PLW-06005: inlining of call of procedure 'IS_NLS_DATE' was done

除了降低优化级别外，您还可以使用内联pragma停止此操作。将is_nls_date的内联设置为NO

CREATE OR REPLACE PROCEDURE RAGTEST 
AS 
    FUNCTION is_nls_date 
      ( p_string        IN VARCHAR2 
      , p_format_mask   IN VARCHAR2 
      ) 
    RETURN NUMBER 
    AS 
        l_test   DATE; 
        l_return NUMBER := 0; 
    BEGIN 
        BEGIN 
            l_test   := TO_DATE(p_string, ''''||p_format_mask||''''); 
            l_return := 1; 
        EXCEPTION 
            WHEN OTHERS THEN 
                l_return := 0; 
        END; 
        -- 
        RETURN l_return; 
    END is_nls_date; 
BEGIN 
    pragma inline (is_nls_date, 'NO');
    DBMS_OUTPUT.PUT_LINE('is_date 01.01.xxxx = '||is_nls_date('01.01.xxxx','DD.MM.YYYY')); 
    DBMS_OUTPUT.PUT_LINE('is_date 01.01.2001 = '||is_nls_date('01.01.2001','DD.MM.YYYY')); 
END; 
/
sho err

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1      PLW-05018: unit RAGTEST omitted optional AUTHID clause; default value DEFINER used
16/18    PLW-06009: procedure "IS_NLS_DATE" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR
24/5     PLW-06008: call of procedure 'IS_NLS_DATE' will not be inlined
25/5     PLW-06005: inlining of call of procedure 'IS_NLS_DATE' was done

exec ragtest;

is_date 01.01.xxxx = 0
is_date 01.01.2001 = 1

select plsql_optimize_level 
from   user_plsql_object_settings
where  name = 'RAGTEST';

PLSQL_OPTIMIZE_LEVEL   
                     3

或者你可以停止使用当其他处理程序返回失败值;)