在一个 SQL 查询 (Oracle) 中插入临时表并更新另一个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5111565/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert into a temporary table and update another table in one SQL query (Oracle)
提问by Dirk
Here's what I'm trying to do:
这是我想要做的：
1) Insert into a temp table some values from an original table
1) 将原始表中的一些值插入到临时表中
INSERT INTO temp_table SELECT id FROM original WHERE status='t'
2) Update the original table
2）更新原表
UPDATE original SET valid='t' WHERE status='t'
3) Select based on a join between the two tables
3）基于两个表之间的连接进行选择
SELECT * FROM original WHERE temp_table.id = original.id
Is there a way to combine steps 1 and 2?
有没有办法结合步骤1和2？
回答by Jeffrey Kemp
You can combine the steps by doing the update in PL/SQL and using the RETURNING clause to get the updated ids into a PL/SQL table.
您可以通过在 PL/SQL 中执行更新并使用 RETURNING 子句将更新的 id 获取到 PL/SQL 表中来组合这些步骤。
EDIT:
编辑：
If you still need to do the final query, you can still use this method to insert into the temp_table; although depending on what that last query is for, there may be other ways of achieving what you want. To illustrate:
如果还需要做最后的查询，仍然可以用这个方法插入到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;
回答by OMG Ponies
No, DML statements can not be mixed.
不，DML 语句不能混合使用。
There's a MERGE statement, but it's only for operations on a single table.
回答by V-Light
回答by Michael Ballent
Create a cursor holding the values from insert and then loop through the cursor updating the table. No need to create temp table in the first place.
创建一个包含插入值的游标，然后遍历游标更新表。无需首先创建临时表。
回答by Jon Heller
You can combine steps 1 and 2 using a MERGE statement and DML error logging. Select twice as many rows, update half of them, and force the other half to fail and then be inserted into an error log that you can use as your temporary table.
您可以使用 MERGE 语句和 DML 错误日志来组合步骤 1 和 2。选择两倍多的行，更新其中一半，并强制另一半失败，然后插入到错误日志中，您可以将其用作临时表。
The solution below assumes that you have a primary key constraint on ID, but there are other ways you could force a failure.
下面的解决方案假设您对 ID 有主键约束，但还有其他方法可以强制失败。
Although I think this is pretty cool, I would recommend you not use it. It looks very weird, has some strange issues (the inserts into TEMP_TABLE are auto-committed), and is probably very slow.
虽然我认为这很酷，但我建议你不要使用它。它看起来很奇怪，有一些奇怪的问题（插入到 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;
