而不是update，尝试merge。下面是一个基于Scott模式的例子;我在这里并没有做任何聪明的事情，只是为了说明如何做到这一点。
在SQL级别创建类型：

SQL> create or replace type table_invoice is object (empno number, job varchar2(20));
  2  /

Type created.

SQL> create or replace type invoice_data_table is table of table_invoice;
  2  /

Type created.

这是初始表内容;我将更新JOB列的值：

SQL> select * from emp where deptno = 20;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17.12.80        840                    20
      7566 JONES      MANAGER         7839 02.04.81       2975                    20
      7788 SCOTT      ANALYST         7566 09.12.82       3000                    20
      7876 ADAMS      CLERK           7788 12.01.83       1100                    20
      7902 FORD       ANALYST         7566 03.12.81       3000                    20

程序如下检查using子句（第10和11行）：

SQL> declare
  2    invoice_data invoice_data_table;
  3  begin
  4    select table_invoice(e.empno, e.job)
  5      bulk collect into invoice_data
  6      from emp e
  7      where e.deptno = 20;
  8
  9    merge into emp a
 10      using (select empno, job
 11             from table(cast(invoice_data as invoice_data_table))
 12            ) x
 13      on (a.empno = x.empno)
 14      when matched then update set
 15        a.job = 'x' || x.job;
 16  end;
 17  /

PL/SQL procedure successfully completed.

结果：

SQL> select * From emp where deptno = 20;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      xCLERK          7902 17.12.80        840                    20
      7566 JONES      xMANAGER        7839 02.04.81       2975                    20
      7788 SCOTT      xANALYST        7566 09.12.82       3000                    20
      7876 ADAMS      xCLERK          7788 12.01.83       1100                    20
      7902 FORD       xANALYST        7566 03.12.81       3000                    20

SQL>

您可以使用FORALL语句从PL/SQL批量DML。它不需要新的模式级对象。对于loop来说，这不是一个方便的快捷方式：它是纯批量装订。
设置：

create table main_table
as
select
  level as id,
  'status' || mod(level, 3) as status,
  trunc(level/5) as ref_id,
  lpad(level, 4, '0') as some_data
from dual
connect by level < 25
/

create table ref_tab
as
select
  level as id,
  mod(level, 2) as type_
from dual
connect by level < 4
/

create table main_table_status (
  dt date default sysdate,
  id int,
  old_ varchar2(20),
  new_ varchar2(20)
)

（批量）收集将更新的ID和before值，然后执行forall进行更新和插入：

declare
  type tr_main_table is record (
    id main_table.id%type,
    status main_table.status%type
  );
  type tt_main_table is table of tr_main_table;
  lt_main_table tt_main_table;
  c_status constant varchar2(5) := 'upd';
begin
  /*id to update*/
  select
    id, status

    bulk collect into lt_main_table
  from main_table m
  where m.ref_id in (
    select flt.id
    from ref_tab flt
    where flt.type_ = 1
  );

  dbms_output.put_line(lt_main_table.count());

  /*update*/
  forall i in lt_main_table.first..lt_main_table.last
    update main_table
    set status = c_status
    where id = lt_main_table(i).id;

  /*log status*/
  forall i in lt_main_table.first..lt_main_table.last
    insert into main_table_status(id, old_, new_)
    values(
      lt_main_table(i).id,
      lt_main_table(i).status,
      c_status
    );
end;/

这导致：

select *
from main_table_status
order by id

select *
from main_table
where status = 'upd'
order by 1

但您可以优化访问。由于您使用额外的表只是为了过滤，因此值得通过where中的子查询显式地进行过滤：in或exists。Join是用来添加新信息的，过滤是它的一个副作用：当唯一的要求是过滤时，您显然不想关心键列和处理可能由联接产生的重复行。
因此，将筛选移动到where中，您可以直接在具有returning子句的update语句中使用它，并且您也可以bulk collect this。它将保存对同一表的一个额外访问：从update而不是初步select检索值。
然后在附加表中执行相同的forall插入操作。

declare
  type tr_main_table is record (
    id main_table.id%type,
    status main_table.status%type
  );
  type tt_main_table is table of tr_main_table;
  lt_main_table tt_main_table;
  c_status constant varchar2(5) := 'upd_2';
begin
  dbms_session.sleep(2);

  /*update*/
  update (
    /*subquery to generate a column
    with before value*/
    select
      m.*,
      /*scalar subquery to unlink the value
      from the original column name*/
      (select m.status from dual) as old_status
    from main_table m
    where m.ref_id in (
      select flt.id
      from ref_tab flt
      where flt.type_ = 1
    )
  )
  set status = c_status
  returning id, old_status

    bulk collect into lt_main_table
  ;

  dbms_output.put_line(lt_main_table.count());

  /*log status*/
  forall i in lt_main_table.first..lt_main_table.last
    insert into main_table_status(id, old_, new_)
    values(
      lt_main_table(i).id,
      lt_main_table(i).status,
      c_status
    );
end;/

这导致日志记录表的内容如下：

select *
from main_table_status
order by dt, id

fiddle