sql – 由Oracle 11引发的突变表

这是一个会导致错误的情况：

create table mutate (
    x NUMBER,y NUMBER
);

insert into mutate (x,y)
values (1,2);

insert into mutate (x,y)
values (3,4);

我创建了两行.现在我通过调用这个语句来加倍我的行：

insert into mutate (x,y)
select x + 1,y + 1 
from mutate;

复制错误并不是绝对必要的,但稍后会对我的演示有所帮助.所以表格的内容如下所示：

X,Y
1,2
3,4
2,3
4,5

一切都很好.现在有趣的部分：

create or replace function mutate_count
return PLS_INTEGER
is
    v_dummy PLS_INTEGER;
begin
    select count(*) 
    into v_dummy
    from mutate;

    return v_dummy;
end mutate_count;
/

我创建了一个函数来查询我的表并返回一个计数.现在,我将结合INSERT语句：

insert into mutate (x,y)
select x + 2,y + 2
from mutate
where mutate_count() = 4;

结果？这个错误：

ORA-04091: table MUTATE is mutating,trigger/function may not see it
ORA-06512: at "MUTATE_COUNT",line 6

所以我知道是什么原因造成的错误,但我很好奇为什么. Oracle不执行SELECT,检索结果集,然后执行这些结果的批量插入？如果在查询完成之前已经插入了记录,我只会期待一个突变表的错误.但是如果Oracle做到了,那么以前的语句不会

insert into mutate (x,y + 1 
from mutate;

启动无限循环？

更新：

通过Jeffrey的链接,我发现在the Oracle docs年：

By default,Oracle guarantees statement-level read consistency. The
set of data returned by a single query is consistent with respect to a
single point in time.

his post年的作者也有评论：

One could argue why Oracle doesn’t ensure this ‘statement-level read
consistency’ for repeated function calls that appear inside a SQL
statement. It could be considered a bug as far as I’m concerned. But
this is the way it currently works.

我假设在Oracle 10和11版本之间这种行为发生了变化吗？

解决方法

insert into mutate (x,y + 1 
from mutate;

不会启动无限循环,因为查询将不会看到插入的数据 – 只有在语句开头存在的数据.新行将只对后续语句可见.

This说得很好：

When Oracle steps out of the SQL-engine that’s currently executing the update statement,and invokes the function,then this function — just like an after row update trigger would — sees the intermediate states of EMP as they exist during execution of the update statement. This implies that the return value of our function invocations heavily depend on the order in which the rows happen to be updated.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!