oracle - 何时在 Oracle 中的查询中评估用户定义的函数？

Question

我在 SELECT 语句中使用用户定义的函数时遇到了一些有趣的行为。

我有几个存储过程可以从单个表中读取和清除数据。这些存储过程由多个源使用。

在我的观察中，似乎用户定义的函数有时会被任意评估，而不总是在使用它的 SELECT 语句执行之后或期间立即执行。

例如，在存储过程中，我有一个 select 语句可能如下所示:

SELECT Something, MyFunction(Something) FROM Somewhere;

随后调用另一个存储过程，该过程从表中清除数据。清除的数据量由另一个表控制，该表存储最大 ID 读取。这样，清除操作不应删除任何尚未被另一个正在执行的存储过程实例读取的数据。

在我的测试代码中，MyFunction 只返回表 Somewhere 中的行数。因此，我认为它应该始终等于 SELECT 语句返回的行数。但是，在我运行此存储过程的两个实例的情况下，我会得到如下结果:

第一个查询实例:

Something  MyFunction(Something)
---------  ---------------------
A          3
B          3
C          3

第二个查询实例:

Something  MyFunction(Something)
---------  ---------------------
A          0
B          0
C          0    

为什么第二个查询返回所有行，但对同一张表进行操作的用户定义函数却报告表中没有更多行了？

无论如何我可以确保第二个查询实例是一致的，因为用户定义的函数仍然看到父存储过程看到的相同数据？

Answer 1

一般来说，您看到的问题是由于 Oracle 的多版本读一致性确保单个 SQL 语句始终会看到一致的数据 View ，但相同的一致性并不意味着每个 SQL 语句都由一个原始 SQL 语句调用的函数将看到与原始语句相同的数据集。

实际上，这意味着类似

SELECT something,
       COUNT(*) OVER ()
  FROM table_name

将始终返回正确答案(如果查询返回 3 行，则为 3)，如果您在函数中放置完全相同的逻辑

CREATE OR REPLACE FUNCTION count_table_name
  RETURN NUMBER
AS
  l_cnt INTEGER;
BEGIN
  SELECT COUNT(*)
    INTO l_cnt
    FROM table_name;
  RETURN l_cnt;
END;

那个 SQL 语句

SELECT something,
       count_table_name
  FROM table_name

不一定会返回与表中行数匹配的值(也不一定会为每一行返回相同的结果)。如果您在函数中设置延迟，以便您可以在单独的 session 中修改数据，则您可以看到这一点。例如

SQL> create table foo( col1 number );

Table created.

SQL> insert into foo select level from dual connect by level <= 3;

3 rows created.

创建一个每行增加 10 秒延迟的函数

SQL> ed
Wrote file afiedt.buf

  1  create or replace function fn_count_foo
  2    return number
  3  is
  4    l_cnt  integer;
  5  begin
  6    select count(*)
  7      into l_cnt
  8      from foo;
  9    dbms_lock.sleep(10);
 10    return l_cnt;
 11* end;
 12  /

Function created.

现在，如果在 session 1 中，我开始声明

select col1, fn_count_foo
  from foo;

然后切换到 session 2，在那里我插入一个新行

SQL> insert into foo values( 4 );

1 row created.

SQL> commit;

Commit complete.

您可以看到该函数在第二次执行期间看到了新提交的行，尽管 SQL 语句本身只看到 3 行

SQL> select col1, fn_count_foo
  2    from foo;

      COL1 FN_COUNT_FOO
---------- ------------
         1            3
         2            4
         3            4

您可以通过在执行 SQL 语句之前让 session 使用可序列化事务隔离级别来避免该问题。所以，例如，

在 session 1 中，将事务隔离级别设置为可序列化并启动查询

SQL> set transaction isolation level serializable;

Transaction set.

SQL> select col1, fn_count_foo
  2    from foo;

在 session 2 中，插入一个新行

SQL> insert into foo values( 5 );

1 row created.

SQL> commit;

Commit complete.

当 session 1 在 40 秒后返回时，一切都是一致的

SQL> select col1, fn_count_foo
  2    from foo;

      COL1 FN_COUNT_FOO
---------- ------------
         1            4
         2            4
         3            4
         4            4