sql - 多次调用 oracle 内联函数

Question

当通过内联 select 语句调用函数时，当函数返回自定义类型时，Oracle 似乎执行等于参数数量 +1 的函数。 This seems to happen when the select is included as a CTAS or an insert/select.

有没有人见过这个？这是 Oracle 错误吗？我希望该函数在表中的每一行调用一次。

--Inline function gets called for the number of arguments +1

--drop table t
create table t(
  id number,
  l_blob blob
);


insert into t values(1, utl_raw.cast_to_raw('SampleString'));
COMMIT;

create table tmp_ts (c1 timestamp); 

create or replace type test_type as object(
              c1         varchar2(32)
              ,c2  varchar2(32)
        );
/

create or replace FUNCTION test_function (p_blob blob, p_date date)
RETURN test_type
IS
BEGIN

--This could also be a DBMS_OUTPUT.PUT_LINE statement
insert into tmp_ts VALUES (systimestamp);

return test_type(null,null);

END test_function;
/

--0
select count(*) from tmp_ts;

--Call function on 1 row table - function should just insert 1 row into tmp_ts
create table tst_table as
select test_function(l_blob, '25-JAN-09') as c1
from t;

--it actually inserts 3
select count(*) from tmp_ts;

增加类型的参数调用会增加函数执行次数的示例

--同样的例子，有更多的争论 - 这里有 6 个争论

--Inline function gets called for the number of arguments +1

--drop table t
create table t2(
  id number,
  l_blob blob
);


insert into t2 values(1, utl_raw.cast_to_raw('SampleString'));
COMMIT;

create table tmp_ts2 (c1 timestamp); 

create or replace type test_type2 as object(
              c1         varchar2(32)
              ,c2  varchar2(32)
              ,c3  varchar2(32)
              ,c4  varchar2(32)
              ,c5  varchar2(32)
              ,c6  varchar2(32)
        );
/

create or replace FUNCTION test_function2 (p_blob blob, p_date date)
RETURN test_type2
IS
BEGIN

insert into tmp_ts2 VALUES (systimestamp);

return test_type2(null,null,null,null,null,null);

END test_function2;
/

--0
select count(*) from tmp_ts2;

--Call function on 1 row table - function should just insert 1 row into tmp_ts
create table tst_table2 as
select test_function2(l_blob, '25-JAN-09') as c1
from t;

--it actually inserts 7
select count(*) from tmp_ts2;

非常感谢任何帮助/反馈。

Answer 1

第一 :这是一个错误，您甚至可以在 SELECT 语句中调用的函数内执行 DML。这应该会引发异常。

否则 Oracle 绝对不保证 SQL-Select 中的函数执行的频率，它可能每行一次，每行十次或整个查询只执行一次(带缓存) - 所以无论它被调用的频率如何，这都符合规范。

在这种特殊情况下，它将为返回类型的每个属性调用该函数，因为 oracle 不会将对象类型作为一个内存结构插入，而是像使用具有多列的表一样使用该函数，并像这样单独读取每一列:

INSERT VALUES ( myFunc(x).attribute1, myFunc(x).attribute2 );

重要部分:在 SQL 语句中使用 FUNCTION 时，永远不要对它的调用频率做出任何假设!!!优化器可以随时再次调用该函数，可能用于采样或缓存...

首选方案:流水线函数 - 流水线函数可以像表一样被调用，并且只会被调用一次。您可以传入函数用于输入处理的游标，并执行整个数据转换和日志记录以及函数中的所有内容。