oracle 在游标内使用 Merge 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8333657/
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
Using Merge statement inside a cursor
提问by jagamot
We have a requirement to populate a master table which consists of columns from a set of 20 different tables.
我们需要填充一个主表，该主表由一组 20 个不同表中的列组成。
I have written a stored procedure to join some of the tables that return me max number of columns and have them in a cursor.
我编写了一个存储过程来连接一些表，这些表返回我的最大列数并将它们放在游标中。
Now. I am using for loop to iterate through the cursor records so I can insert them into the master table.
现在。我正在使用 for 循环遍历游标记录，以便我可以将它们插入到主表中。
How I can use a merge statement inside the cursor for loop so I can check if I need to update existing row or insert a new row depending if the records already exists or not.
如何在游标 for 循环内使用合并语句，以便我可以检查是否需要更新现有行或插入新行，具体取决于记录是否已存在。
Any ideas if we can use merge statement inside a cursor for loop? Any examples?
如果我们可以在游标 for 循环中使用合并语句，有什么想法吗？有什么例子吗？
回答by Justin Cave
You can do a MERGEby selecting the cursor's data from DUAL. For example
您可以MERGE通过从 中选择光标的数据来完成DUAL。例如
Create a source and destination table with some data
创建包含一些数据的源表和目标表
SQL> create table src ( col1 number, col2 varchar2(10) );
Table created.
SQL> create table dest( col1 number, col2 varchar2(10) );
Table created.
SQL> insert into src values( 1, 'A' );
1 row created.
SQL> insert into src values( 2, 'B' );
1 row created.
SQL> insert into dest values( 1, 'C' );
1 row created.
SQL> commit;
Commit complete.
Run the merge
运行合并
SQL> ed
Wrote file afiedt.buf
1 begin
2 for x in (select * from src)
3 loop
4 merge into dest
5 using( select x.col1 col1, x.col2 col2
6 from dual ) src
7 on( src.col1 = dest.col1 )
8 when matched then
9 update set col2 = src.col2
10 when not matched then
11 insert( col1, col2 )
12 values( src.col1, src.col2 );
13 end loop;
14* end;
SQL> /
PL/SQL procedure successfully completed.
And verify that the merge did what we wanted. Row 1 was updated and row 2 was inserted.
并验证合并是否符合我们的要求。更新了第 1 行并插入了第 2 行。
SQL> select * from dest;
COL1 COL2
---------- ----------
1 A
2 B
However, it generally wouldn't make too much sense to structure the code this way. You'd generally be better off putting the query that you'd use to open the cursor into the MERGE statement directly so that rather than selecting one row of data from DUAL, you're selecting all the data you want to merge from all the tables you're trying to merge the data from. Of course, it may make sense to create a view for this query that the MERGEstatement can query in order to keep the MERGEstatement readable.
但是，以这种方式构建代码通常没有太大意义。您通常最好将用于打开游标的查询直接放入 MERGE 语句中，这样您就不必从 中选择一行数据，而是DUAL从所有表中选择要合并的所有数据您正在尝试合并数据。当然，为这个查询创建一个视图可以让MERGE语句查询以保持MERGE语句的可读性可能是有意义的。
