oracle - PL/SQL FUNCTION 中的 INSERT 语句

Question

我用 PL/SQL 编写了一个函数，它使用一些参数调用 Web 服务、解析答案并返回一个值。效果非常好。

但是，有时响应可能会很慢。由于参数通常位于所有可能值的很小子集中，因此我想到将答案缓存在表中。该函数如下所示

CREATE OR REPLACE FUNCTION myfct(p1 IN VARCHAR2, p2 IN VARCHAR2)
RETURN VARCHAR2
IS
    cache_hit NUMBER ;
    res VARCHAR2(200) ;
BEGIN
    SELECT COUNT(*) INTO cache_hit FROM MYCACHE WHERE param1 = p1 AND param2 = p2 ;

    IF( cache_hit = 1 )
    THEN
        SELECT MYCACHE.result INTO res FROM MYCACHE WHERE param1 = p1 AND param2 = p2 ;

        RETURN res ;
    END IF ;

    -- complex operations 
    res := p1 || p2 ;


    INSERT INTO MYCACHE(param1, param2, result) VALUES(p1, p2, res) ;

    RETURN res ;
END ;

当我尝试这个功能时:

SELECT myfct('ABC', 'DEF') FROM DUAL ;

我收到错误:

ORA-14551: cannot perform a DML operation inside a query 

尝试将 DML 部分包装在过程中并在函数中调用此过程没有帮助

我找到了 PRAGMA AUTONOMOUS_TRANSACTION 和 COMMIT 的解决方法:

CREATE OR REPLACE FUNCTION myfct(p1 IN VARCHAR2, p2 IN VARCHAR2)
RETURN VARCHAR2
IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    cache_hit NUMBER ;
    res VARCHAR2(200) ;
BEGIN
    SELECT COUNT(*) INTO cache_hit FROM MYCACHE WHERE param1 = p1 AND param2 = p2 ;

    IF( cache_hit = 1 )
    THEN
        SELECT MYCACHE.result INTO res FROM MYCACHE WHERE param1 = p1 AND param2 = p2 ;

        RETURN res ;
    END IF ;

    -- complex operations 
    res := p1 || p2 ;


    INSERT INTO MYCACHE(param1, param2, result) VALUES(p1, p2, res) ;

    COMMIT ;
    RETURN res ;
END ;

但我想知道这是否真的是一个好主意。提到此解决方法的人表示这可能很危险，但没有具体说明原因。

我的函数是 PRAGMA AUTONOMOUS_TRANSACTION 的良好使用示例，还是有更好、更安全的方法来执行我想要的操作？

Answer 1

从 SQL 上下文中调用何种 PL/SQL 函数是有限制的。不要在 SQL 上下文中调用 PL/SQL 函数，而是在 PL/SQL 上下文中调用，一切都应该很好:

declare
  v_foo constant varchar2(32767) := myfct('foo', 'bar');
begin
  dbmsn_output.put_line(v_foo);
end;

但是，在实现您自己的缓存之前，请考虑 Oracle native PL/SQL Function Result Cache -特征:

The PL/SQL function result caching mechanism provides a language-supported and system-managed way to cache the results of PL/SQL functions in a shared global area (SGA), which is available to every session that runs your application. The caching mechanism is both efficient and easy to use, and relieves you of the burden of designing and developing your own caches and cache-management policies.

When a result-cached function is invoked, the system checks the cache. If the cache contains the result from a previous invocation of the function with the same parameter values, the system returns the cached result to the invoker and does not reexecute the function body. If the cache does not contain the result, the system runs the function body and adds the result (for these parameter values) to the cache before returning control to the invoker.