在事务上下文中的Oracle存储过程/函数语义学

本文介绍了在事务上下文中的Oracle存储过程/函数语义学的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我最近已经从关于存储函数犯了一些同事的输入。无论我们使用的过程或函数在Oracle数据库中执行离线/批的逻辑主要是在我们的应用程序喜好问题。在这两种情况下，我们返回一个code无论是作为函数的结果，或者程序 OUT 参数。我们通常要求这些离线/批处理程序从PL被叫做/ SQL，而不是从SQL：

I have recently had some input from a colleague regarding committing in a stored function. Whether we use procedures or functions to execute offline / batch logic in an Oracle database is mostly a matter of taste in our application. In both cases, we return a code either as function result, or as procedure OUT parameter. We usually require those offline / batch routines to be called from PL/SQL, not from SQL:

-- good
declare
  rc number(7);
begin
  rc := our_function(1, 2, 3);
end;

-- less good
select our_function(1, 2, 3) from dual;

之所以说后者是不太好的，因为 our_function 可提交事务性能方面的原因。这是确定一个批处理程序。

The reason why the latter is less good is because our_function may commit the transaction for performance reasons. This is ok for a batch routine.

现在的问题是：有没有围绕这个主题的最佳做法，或者说美元的SQL语句中使用一个编译器级P $ pvent功能等一些特殊的关键字？或者我们应该避免功能批处理操作，只使用手续？

The question is: Are there any best practices around this topic, or some special keywords that prevent such functions from being used in SQL statements on a compiler-level? Or should we avoid functions for batch operations and only use procedures?

推荐答案

您可以使用RESTRICT_REFERENCES以指示一个函数将不会读/写包或数据库状态

You can use RESTRICT_REFERENCES to indicate that a function won't read/write package or database state.

CREATE PACKAGE t_pkg AS
   FUNCTION showup (msg VARCHAR2) RETURN VARCHAR2;
   PRAGMA RESTRICT_REFERENCES(showup, WNDS, RNDS);
END t_pkg;
/
-- create the package body
CREATE OR REPLACE PACKAGE BODY t_pkg AS
   FUNCTION showup (msg VARCHAR2) RETURN VARCHAR2 IS
    v_val varchar2(1);
   BEGIN
      select dummy into v_val from dual;
      RETURN v_val;
   END;
END t_pkg;
/

这曾经是SQL不会让你调用一个函数，除非它做出这样的承诺的情况下，但限制得到了下降。

It used to be the case that SQL wouldn't allow you to call a function unless it made such a promise, but that restriction got dropped.

我preFER使它成为一个过程和函数之间的区别。这是值得铭记，如果PL / SQL函数抛出一个NO_DATA_FOUND异常，呼叫SQL语句不会失败（因为没有找到的数据是不是一个SQL错误）。所以我preFER使用程序，除非对象是专为从SQL调用

I'd prefer to make it a differentiator between a procedure and a function. It's worth bearing in mind that if a PL/SQL function raises a NO_DATA_FOUND exception, a calling SQL statement does not fail (as no data found isn't an SQL error). So I prefer to use procedures unless the object is specifically designed to be called from SQL.

这篇关于在事务上下文中的Oracle存储过程/函数语义学的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

sql