同时更新两个表 - Oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34007216/
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
Updating two Tables Simultaneously - Oracle
提问by jiman
Say I have two tables such as:
假设我有两个表，例如：
Product
prd_id prd_name parent_prd_id ...
123 Foo <null>
456 Bar <null>
789 Baz <null>
Product_Parent_Relation_Batch
prd_id prd_parent_id processed
555 888 T
123 789 F
456 789 F
I can't load the relation data directly into product. There's a chance of the parent not existing before the child. Poor design or not, that's the way it is. To update the product table, I'd do a correlated update like:
我无法将关系数据直接加载到产品中。父母有可能在孩子之前不存在。糟糕的设计与否，就是这样。要更新产品表，我会做一个相关的更新，如：
Oracle SQL: Update a table with data from another table
I want to populate products 123 and 456 with the parent id 789 and set 123 and 456 to processed = 'T' in the product parent relation batch table. Seems like I can only do one.
我想用父 ID 789 填充产品 123 和 456，并在产品父关系批处理表中将 123 和 456 设置为 processing = 'T'。好像只能做一个。
Am I forced to do this in an application, where I query for all non-processed batch records that have a matching prd_id with an existing product record, execute one individual update statement for the product table and another for the relation batch table, for all applicable records?
我是否被迫在应用程序中执行此操作，在其中查询所有具有匹配 prd_id 与现有产品记录的未处理批处理记录，为产品表执行一个单独的更新语句，为所有关系批处理表执行另一个更新语句适用的记录？
CREATE OR REPLACE PROCEDURE sync_prd_with_parent
IS
cursor c1 is
select prd_id, parent_prd_id
from product_parent_relation_batch
inner join product on product_parent_relation_batch.prd_id = product.prd_id
where product_parent_relation_batch.processed = 'F';
BEGIN
FOR rec in c1
LOOP
UPDATE product SET parent_prd_id = rec.parent_prd_id WHERE prd_id = rec.prd_id;
UPDATE product_parent_relation_batch SET processed = 'T' WHERE product_parent_relation_batch.prd_id= rec.prd_id;
END LOOP;
END;
I'm going to settle for the above PL/SQL program unless a better suggestion is posted.
除非发布更好的建议，否则我将接受上述 PL/SQL 程序。
回答by jiman
One is physically restricted from updating multiple tables in the same query.
一种是物理上限制更新同一查询中的多个表。
A working solution for this kind of scenario is to create an application - PL/SQL or otherwise, to grab information for both tables you need to update, iterate through the results, and update the tables in individual statements in each iteration.
对于这种场景，一个可行的解决方案是创建一个应用程序 - PL/SQL 或其他方式，获取需要更新的两个表的信息，迭代结果，并在每次迭代中更新各个语句中的表。
回答by ibre5041
There is no way how to do that in a single statement. Even when using update-able joins, only one table can be subject of the change.
没有办法在单个语句中做到这一点。即使使用可更新的连接，也只有一个表可以被更改。
回答by Francisco Sitja
Reproducing the error caused by concurrent sessions.
重现并发会话导致的错误。
First session executes the Update on Product:
第一个会话执行产品更新：
08/12/2015 17:46:54:SQL> -- session 1
08/12/2015 17:47:12:SQL> BEGIN
2 UPDATE product pr
3 SET parent_prd_id =
4 (SELECT b.prd_parent_id
5 FROM product_parent_relation_batch b
6 INNER JOIN product p ON b.prd_id = p.prd_id
7 WHERE b.processed = 'F'
8 AND pr.prd_id = p.prd_id)
9 WHERE prd_id in (SELECT p.prd_id
10 FROM product_parent_relation_batch b
11 INNER JOIN product p ON b.prd_id = p.prd_id
12 WHERE b.processed = 'F');
13 END;
14 /
Procedimento PL/SQL concluído com sucesso.
Before the 2nd update happens a different session inserts new rows:
在第二次更新发生之前，不同的会话插入新行：
08/12/2015 17:47:31:SQL> -- session 2
08/12/2015 17:47:31:SQL> INSERT INTO product
2 VALUES (990, 'New', null);
1 linha criada.
08/12/2015 17:47:31:SQL> INSERT INTO product_parent_relation_batch
2 VALUES (990, 789, 'F');
1 linha criada.
08/12/2015 17:47:31:SQL>
08/12/2015 17:47:31:SQL> commit;
Commit concluído.
Then, with those new commited rows, our first transaction Updates the Batch table:
然后，使用这些新提交的行，我们的第一个事务更新批处理表：
08/12/2015 17:47:50:SQL> --- continues
08/12/2015 17:47:50:SQL> UPDATE product_parent_relation_batch pb
2 SET processed = 'T'
3 WHERE pb.prd_id IN (SELECT b.prd_id
4 FROM product_parent_relation_batch b
5 INNER JOIN product p ON b.prd_id = p.prd_id
6 WHERE b.processed = 'F'
7 AND pb.prd_id = p.prd_id);
3 linhas atualizadas.
08/12/2015 17:47:50:SQL> SELECT *
2 FROM product_parent_relation_batch b
3 INNER JOIN product p
4 ON b.prd_id = p.prd_id
5 WHERE p.prd_id = 990;
PRD_ID PRD_PARENT_ID P PRD_ID PRD PARENT_PRD_ID
---------- ------------- - ---------- --- -------------
990 789 T 990 New
08/12/2015 17:47:50:SQL> COMMIT;
Commit concluído.
Notice 3 rows are updated. The error you mentioned is displayed by checking that "New" row that now has 'T' instead of 'F'.
注意 3 行已更新。您提到的错误是通过检查现在有“T”而不是“F”的“新”行来显示的。
Now let's try it changing it to Serializable Isolation Level:
现在让我们尝试将其更改为 Serializable Isolation Level：
08/12/2015 17:51:08:SQL> -- session 1
08/12/2015 17:51:24:SQL> BEGIN
2 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
3 UPDATE product pr
4 SET parent_prd_id =
5 (SELECT b.prd_parent_id
6 FROM product_parent_relation_batch b
7 INNER JOIN product p ON b.prd_id = p.prd_id
8 WHERE b.processed = 'F'
9 AND pr.prd_id = p.prd_id)
10 WHERE prd_id in (SELECT p.prd_id
11 FROM product_parent_relation_batch b
12 INNER JOIN product p ON b.prd_id = p.prd_id
13 WHERE b.processed = 'F');
14 END;
15 /
Procedimento PL/SQL concluído com sucesso.
Then concurrent insert:
然后并发插入：
08/12/2015 17:50:59:SQL> -- session 2
08/12/2015 17:51:46:SQL> INSERT INTO product
2 VALUES (990, 'New', null);
1 linha criada.
08/12/2015 17:51:46:SQL> INSERT INTO product_parent_relation_batch
2 VALUES (990, 789, 'F');
1 linha criada.
08/12/2015 17:51:46:SQL>
08/12/2015 17:51:46:SQL> commit;
Commit concluído.
And finally the 2nd update:
最后是第二次更新：
08/12/2015 17:51:24:SQL> --- continues
08/12/2015 17:52:16:SQL> UPDATE product_parent_relation_batch pb
2 SET processed = 'T'
3 WHERE pb.prd_id IN (SELECT b.prd_id
4 FROM product_parent_relation_batch b
5 INNER JOIN product p ON b.prd_id = p.prd_id
6 WHERE b.processed = 'F'
7 AND pb.prd_id = p.prd_id);
2 linhas atualizadas.
08/12/2015 17:52:16:SQL> SELECT *
2 FROM product_parent_relation_batch b
3 INNER JOIN product p
4 ON b.prd_id = p.prd_id
5 WHERE p.prd_id = 990;
n?o há linhas selecionadas
08/12/2015 17:52:16:SQL> COMMIT;
Commit concluído.
08/12/2015 17:52:16:SQL> SELECT *
2 FROM product_parent_relation_batch b
3 INNER JOIN product p
4 ON b.prd_id = p.prd_id
5 WHERE p.prd_id = 990;
PRD_ID PRD_PARENT_ID P PRD_ID PRD PARENT_PRD_ID
---------- ------------- - ---------- --- -------------
990 789 F 990 New
The new row is untouched, because the Serializable isolation level makes it a snapshot at the beginning of the transaction.
新行未受影响，因为 Serializable 隔离级别使其成为事务开始时的快照。
The correct version would be similar to this:
正确的版本类似于：
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE product pr
SET parent_prd_id =
(SELECT b.prd_parent_id
FROM product_parent_relation_batch b
INNER JOIN product p ON b.prd_id = p.prd_id
WHERE b.processed = 'F'
AND pr.prd_id = p.prd_id)
WHERE prd_id in (SELECT p.prd_id
FROM product_parent_relation_batch b
INNER JOIN product p ON b.prd_id = p.prd_id
WHERE b.processed = 'F');
UPDATE product_parent_relation_batch pb
SET processed = 'T'
WHERE pb.prd_id IN (SELECT b.prd_id
FROM product_parent_relation_batch b
INNER JOIN product p ON b.prd_id = p.prd_id
WHERE b.processed = 'F'
AND pb.prd_id = p.prd_id);
COMMIT;
END;
回答by Francisco Sitja
You could forego the cursor and go straight for the 2 updates as follows:
您可以放弃光标并直接进行 2 个更新，如下所示：
SQL> create table product (prd_id,
2 prd_name,
3 parent_prd_id)
4 as
5 select 123, 'Foo', cast(null as number) from dual union all
6 select 456, 'Bar', null from dual union all
7 select 789, 'Baz', null from dual;
Table created
SQL> create table product_parent_relation_batch
2 (prd_id,
3 prd_parent_id,
4 processed)
5 as
6 select 555, 888, 'T' from dual union all
7 select 123, 789, 'F' from dual union all
8 select 456, 789, 'F' from dual;
Table created
SQL> SELECT p.prd_id, b.prd_id, prd_parent_id
2 FROM product_parent_relation_batch b
3 INNER JOIN product p
4 ON b.prd_id = p.prd_id
5 WHERE b.processed = 'F'
SQL> BEGIN
2 UPDATE product pr
3 SET parent_prd_id =
4 (SELECT b.prd_parent_id
5 FROM product_parent_relation_batch b
6 INNER JOIN product p ON b.prd_id = p.prd_id
7 WHERE b.processed = 'F'
8 AND pr.prd_id = p.prd_id)
9 WHERE prd_id in (SELECT p.prd_id
10 FROM product_parent_relation_batch b
11 INNER JOIN product p ON b.prd_id = p.prd_id
12 WHERE b.processed = 'F');
13 UPDATE product_parent_relation_batch pb
14 SET processed = 'T'
15 WHERE pb.prd_id IN (SELECT b.prd_id
16 FROM product_parent_relation_batch b
17 INNER JOIN product p ON b.prd_id = p.prd_id
18 WHERE b.processed = 'F'
19 AND pb.prd_id = p.prd_id);
20 COMMIT;
21 END;
22 /
PL/SQL procedure successfully completed
SQL> SELECT * FROM product_parent_relation_batch;
PRD_ID PRD_PARENT_ID PROCESSED
---------- ------------- ---------
555 888 T
123 789 T
456 789 T
SQL> SELECT * FROM product;
PRD_ID PRD_NAME PARENT_PRD_ID
---------- -------- -------------
123 Foo 789
456 Bar 789
789 Baz
SQL>
