在游标内使用 Merge 语句

问题描述

我们需要填充一个主表，其中包含来自一组 20 个不同表的列.

We have a requirement to populate a master table which consists of columns from a set of 20 different tables.

我编写了一个存储过程来连接一些表，这些表返回最大列数并将它们放在游标中.

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.

现在.我正在使用 for 循环遍历游标记录，以便将它们插入到主表中.

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?

推荐答案

您可以通过从 DUAL 中选择光标的数据来执行 MERGE.例如

You can do a MERGE by selecting the cursor's data from DUAL. For example

用一些数据创建一个源表和目标表

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.

运行合并

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.

并验证合并是否符合我们的要求.第 1 行已更新，第 2 行已插入.

And verify that the merge did what we wanted. Row 1 was updated and row 2 was inserted.

SQL> select * from dest;

      COL1 COL2
---------- ----------
         1 A
         2 B

但是，以这种方式构建代码通常没有太大意义.通常最好将用于打开游标的查询直接放入 MERGE 语句中，这样您就不必从 DUAL 中选择一行数据，而是选择所有数据你想从你试图合并数据的所有表中合并.当然，为这个查询创建一个 MERGE 语句可以查询的视图以保持 MERGE 语句的可读性可能是有意义的.

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 MERGE statement can query in order to keep the MERGE statement readable.

这篇关于在游标内使用 Merge 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！