Oracle 为什么一个PL/SQL函数在一个简单的更新语句中每个记录被调用8次

我正在使用Oracle云数据库。

我有一个表与列-位置SDO_GEOMETRY

我有一个函数，它生成一个随机值来填充列，generate_random_location()。

当我运行下面的update语句时，每更新一行调用该函数8次。

update dd_members_t
   set location = generate_random_location(v_island)
 where member_id = 3;

为了解决问题，我从函数中删除了所有代码，除了单个dbms_output.putline('Start')，将in参数设置为默认null并将其设置为返回null。它仍然被称为8次。

update dd_members_t
   set location = generate_random_location()
 where member_id = 3;

我将函数设置为deterministic，当然，它只被调用一次，但这违背了返回随机值的目的。

我已经通过使用批量收集解决了该问题 (该函数仅在select语句中每行调用一次)

  select member_id,
             generate_random_location(v_dino_island)
  bulk collect into l_member_ids
  from dd_members
  where.....

然后

     forall indx in 1 .. l_member_ids.count
       update dd_members_t
          set location = l_member_ids (indx).new_location
        where member_id = l_member_ids (indx).member_id;

但这仍然留下了一个问题，为什么在一个简单的更新语句中每个记录调用8次函数，有没有办法阻止它？

它似乎与对象类型的复杂性有关。

例如

SQL> drop table t purge;

Table dropped.

SQL> drop type complex_obj;

Type dropped.

SQL>
SQL> create or replace
  2  type complex_obj as object ( a int, b int, c int, d int, e int );
  3  /

Type created.

SQL>
SQL> create table t (
  2    member_id int,
  3    location complex_obj );

Table created.

SQL>
SQL> insert into t values (1,null);

1 row created.

SQL>
SQL> exec dbms_application_info.set_client_info('0');

PL/SQL procedure successfully completed.

SQL>
SQL> create or replace
  2  function generate_random_location return complex_obj is
  3  begin
 4    dbms_application_info.set_client_info(nvl(userenv('client_info'),0)+1 );
  5    return null;
  6  end;
  7  /

Function created.

SQL>
SQL> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
0

1 row selected.

SQL>
SQL> update t
  2  set location = generate_random_location
  3  where member_id = 1;

1 row updated.

SQL>
SQL> select userenv('client_info') executions from dual;

EXECUTIONS
----------------------------------------------------------------
6

1 row selected.

SQL>
SQL>

现在我们用10个元素重复

SQL> drop table t purge;

Table dropped.

SQL> drop type complex_obj;

Type dropped.

SQL>
SQL> create or replace
  2  type complex_obj as object ( a int, b int, c int, d int, e int ,
  3                               a1 int, b1 int, c1 int, d1 int, e1 int );
  4  /

Type created.

SQL>
SQL> create table t (
  2    member_id int,
  3    location complex_obj );

Table created.

SQL>
SQL> insert into t values (1,null);

1 row created.

SQL>
SQL> exec dbms_application_info.set_client_info('0');

PL/SQL procedure successfully completed.

SQL>
SQL> create or replace
  2  function generate_random_location return complex_obj is
  3  begin
  4    dbms_application_info.set_client_info(nvl(userenv('client_info'),0)+1 );
  5    return null;
  6  end;
  7  /

Function created.

SQL>
SQL> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
0

1 row selected.

SQL>
SQL> update t
  2  set location = generate_random_location
  3  where member_id = 1;

1 row updated.

SQL>
SQL> select userenv('client_info') executions from dual;

EXECUTIONS
----------------------------------------------------------------
11

1 row selected.

SQL>
SQL>

我的假设是，该陈述是在封面下处理的 * 类似于 *:

update t
set 
  location.a = generate_random_location.a,
  location.b = generate_random_location.b,
  location.c = generate_random_location.c,
  location.d = generate_random_location.d,
  location.e = generate_random_location.e,
  ...
where member_id = 1;

一个简单的解决方法是使用标量选择

SQL> drop table t purge;

Table dropped.

SQL> drop type complex_obj;

Type dropped.

SQL>
SQL> create or replace
  2  type complex_obj as object ( a int, b int, c int, d int, e int ,
  3                               a1 int, b1 int, c1 int, d1 int, e1 int );
  4  /

Type created.

SQL>
SQL> create table t (
  2    member_id int,
  3    location complex_obj );

Table created.

SQL>
SQL> insert into t values (1,null);

1 row created.

SQL>
SQL> exec dbms_application_info.set_client_info('0');

PL/SQL procedure successfully completed.

SQL>
SQL> create or replace
  2  function generate_random_location return complex_obj is
  3  begin
 4    dbms_application_info.set_client_info(nvl(userenv('client_info'),0)+1 );
  5    return null;
  6  end;
  7  /

Function created.

SQL>
SQL> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
0

1 row selected.

SQL>
SQL> update t
  2  set location = ( select generate_random_location from dual )
  3  where member_id = 1;

1 row updated.

SQL>
SQL> select userenv('client_info') executions from dual;

EXECUTIONS
----------------------------------------------------------------
1

1 row selected.

SQL>
SQL>