在一个 SQL 查询中插入临时表并更新另一个表 (Oracle)

Question

这是我想要做的：

1) 将原始表中的一些值插入到临时表中

INSERT INTO temp_table SELECT id FROM original WHERE status='t'

2) 更新原始表

UPDATE original SET valid='t' WHERE status='t'

3) 基于两个表之间的联接进行选择

SELECT * FROM original WHERE temp_table.id = original.id

有没有办法结合步骤 1 和 2？

Answer 1

您可以通过在 PL/SQL 中执行更新并使用 RETURNING 子句将更新的 id 获取到 PL/SQL 表中来组合这些步骤。

编辑：

如果您仍然需要进行最终查询，您仍然可以使用此方法插入到 temp_table 中；尽管取决于最后一个查询的目的，但可能还有其他方法可以实现您想要的目的。举例说明：

DECLARE
  id_table_t IS TABLE OF original.id%TYPE INDEX BY PLS_INTEGER;
  id_table id_table_t;
BEGIN
  UPDATE original SET valid='t' WHERE status='t'
  RETURNING id INTO id_table;
  FORALL i IN 1..id_table.COUNT
    INSERT INTO temp_table
    VALUES (id_table(i));
END;
/

SELECT * FROM original WHERE temp_table.id = original.id;

Answer 2

不可以，DML 语句不能混合。

有一个 MERGE 语句，但它仅适用于单个表的操作。

Answer 3

也许创建一个 TRIGGER ，在插入后触发temp_table 并更新原始表

Answer 4

创建一个游标来保存插入中的值，然后循环更新表。首先不需要创建临时表。

Answer 5

您可以使用 MERGE 语句和 DML 错误日志记录来组合步骤 1 和 2。选择两倍数量的行，更新其中一半，并强制另一半失败，然后将其插入到可用作临时表的错误日志中。

下面的解决方案假设您对 ID 有主键约束，但还有其他方法可以强制失败。

虽然我认为这很酷，但我建议您不要使用它。它看起来很奇怪，有一些奇怪的问题（对 TEMP_TABLE 的插入是自动提交的），并且可能非常慢。

--Create ORIGINAL table for testing.
--Primary key will be intentionally violated later.
create table original (id number, status varchar2(10), valid varchar2(10)
    ,primary key (id));

--Create TEMP_TABLE as error log.  There will be some extra columns generated.
begin
  dbms_errlog.create_error_log(dml_table_name => 'ORIGINAL'
    ,err_log_table_name => 'TEMP_TABLE');
end;
/

--Test data
insert into original values(1, 't', null);
insert into original values(2, 't', null);
insert into original values(3, 's', null);
commit;


--Update rows in ORIGINAL and also insert those updated rows to TEMP_TABLE.
merge into original original1
using 
(
  --Duplicate the rows.  Only choose rows with the relevant status.
  select id, status, valid, rownumber
  from original
    cross join
    (select 1 rownumber from dual union all select 2 rownumber from dual)
  where status = 't'
) original2
  on (original1.id = original2.id and original2.rownumber = 1)
--Only math half the rows, those with rownumber = 1.
when matched then update set valid = 't'
--The other half will be inserted.  Inserting ID causes a PK error and will
--insert the data into the error table, TEMP_TABLE.
when not matched then insert(original1.id, original1.status, original1.valid)
  values(original2.id, original2.status, original2.valid)
log errors into temp_table reject limit 999999999;


--Expected: ORIGINAL rows 1 and 2 have VALID = 't'.
--TEMP_TABLE has the two original values for ID 1 and 2.
select * from original;
select * from temp_table;