oracle PL/SQL FUNCTION 中的 INSERT 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39408681/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
INSERT statement in PL/SQL FUNCTION
提问by Joffrey Schmitz
I wrote a function in PL/SQL that calls a webservice with some parameters, parses the answer, and returns a value. It works quite well.
我在 PL/SQL 中编写了一个函数，它使用一些参数调用 web 服务，解析答案并返回一个值。它运作良好。
However, the response could be slow sometimes. As the parameters are usually in a very small subset of all possible values, I had the idea to cache the answers in a table. The function looks like the following
但是，有时响应可能会很慢。由于参数通常位于所有可能值的一个非常小的子集中，因此我想到将答案缓存在表中。该函数如下所示
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 ;
When I tried this function :
当我尝试这个功能时：
SELECT myfct('ABC', 'DEF') FROM DUAL ;
I got the error :
我收到错误：
ORA-14551: cannot perform a DML operation inside a query
Trying to wrap the DML part in a procedure and call this procedure in the function does not help
试图将 DML 部分包装在一个过程中并在函数中调用这个过程无济于事
I found a work-around with PRAGMA AUTONOMOUS_TRANSACTION and COMMIT :
我找到了 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 ;
But I wonder if it is really a good idea. People that mention this workaround said that could be dangerous, without saying exactly why.
但我想知道这是否真的是个好主意。提到此解决方法的人说这可能很危险，但没有确切说明原因。
Is my function an example of good use for PRAGMA AUTONOMOUS_TRANSACTION, or is there a better and safer way to do what I want ?
我的函数是 PRAGMA AUTONOMOUS_TRANSACTION 很好用的例子，还是有更好更安全的方法来做我想做的事？
回答by user272735
There is limitations what kind of PL/SQL functions can be called from SQL context. Don't call your PL/SQL function in SQL context but instead in PL/SQL context and all should be good:
可以从 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;
However before implementing you own cache please consider Oracle native PL/SQL Function Result Cache-feature:
但是，在实现您自己的缓存之前，请考虑 Oracle 本机PL/SQL 函数结果缓存功能：
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.
PL/SQL 函数结果缓存机制提供了一种语言支持和系统管理的方式来将 PL/SQL 函数的结果缓存在共享全局区域 (SGA) 中，该区域可用于运行您的应用程序的每个会话。缓存机制既高效又易于使用，可以减轻您设计和开发自己的缓存和缓存管理策略的负担。
当调用结果缓存函数时，系统会检查缓存。如果缓存包含具有相同参数值的先前函数调用的结果，则系统将缓存的结果返回给调用者并且不会重新执行函数体。如果缓存中不包含结果，系统会运行函数体并将结果（对于这些参数值）添加到缓存中，然后再将控制权返回给调用者。
回答by Joe
By convention and good software design, functions should not make changes to database and should only return values. Use a procedure instead. This is why there are both in PLSQL vs just functions like in C. Granted, this is mainly considered a stylistic thing by many people. But I strongly suggest you keep their usages separate.
按照惯例和良好的软件设计，函数不应更改数据库，而应仅返回值。请改用程序。这就是为什么在 PLSQL 中有两者，而在 C 中只有函数。当然，这主要被许多人认为是一种风格。但我强烈建议您将它们的用法分开。
回答by Chetan namdev
Just call that function in anonymous block. Don't select it. Like this
只需在匿名块中调用该函数即可。不要选择它。像这样
Declare
V_result varchar2(20);
Begin
V_result := myfct('ABC', 'DEF');
DBMS_OUTPUT.PUT_LINE(V_RESULT);
End;
