oracle 一次更新两个表的简单oracle存储过程示例
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7596593/
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
simple oracle stored procedure example for updating two tables in one go
提问by coder247
Could anyone give a simple example of an Oracle stored procedure for updating two tables in a single go.
任何人都可以举一个简单的示例，说明一次更新两个表的 Oracle 存储过程。
回答by schurik
CREATE OR REPLACE PROCEDURE update_2_tables
IS
begin
update t1 set c1 = 1;
update t2 set c1 = 2;
end;
/
回答by Florin Ghita
I supposed that you update tables a and b with values from c. This is PL/SQL
我假设你用来自 c 的值更新表 a 和 b。这是 PL/SQL
create or replace procedure update_one_scan as
cursor c is
select a.rowid r1, b.rowid r1, c.value_to_get
from a join b on (join conditions)
join c on (join conditions)
where conditions;
begin
for r in c
loop
update a set col_to_update=r.value_to_get where rowid=r1;
update b set col_to_update=r.value_to_get where rowid=r2;
end loop;
end;
You have the advantage of single scan of source tables.
您具有对源表进行单次扫描的优势。
UPDATE:You can do it even in oracle SQL, but is more restrictive(you'll see when you try). But this can be faster.
更新：您甚至可以在 oracle SQL 中执行此操作，但限制性更强（您尝试时会看到）。但这可以更快。
Is a UPDATE SELECT statement:
是一个 UPDATE SELECT 语句：
Create or replace Procedure update_select AS
BEGIN
update
(select a.col_to_update as c1, b.col_to_update as c2, c.value_to_get v1
from a join b on (join conditions)
join c on (join conditions)
where conditions)
set
c1 = v1, c2 = v2;
END;
