生成merge into 脚本

背景介绍：由于单表字段数较多，生成的脚本长度超过varchar2类型存储上限，需要使用clob字段存储脚本，且Dbms_Lob.Append不能用于sql语句中，需要创建临时表处理

表命名规则：

源表tb,临时表temp_tb

结果输出到中间表test.temp_merge_sql

create table test.temp_merge_sql(
owner varchar2(128),    ----表属主
table_name varchar2(128),    ----表名
key_col1 varchar2(256),        ----表主键
key_col2 varchar2(256),        ----merge条件
part1 varchar2(4000),        ----中间输出
part2 varchar2(4000),        ----中间输出
ouput_sql clob,           -----merge语句输出
flag char(1));            ----处理情况

步骤：

1、向中间表test.temp_merge_sql插入待生成脚本的表清单（插入字段owner,table_name）

Select *
  From test.temp_Merge_Sql
   For Update;

2、更新主键,merge条件

Update test.temp_Merge_Sql a
   Set a.Key_Col1 =
       (Select b.Pri_Key
          From (Select c.Owner Owner,
                       a.Constraint_Name,
                       c.Table_Name Table_Name,
                       Listagg(c.Column_Name, ',') Within Group(Order By c.Position) Pri_Key
                  From Dba_Constraints    a,
                       test.temp_Merge_Sql b,
                       Dba_Cons_Columns   c
                 Where a.Owner = b.Owner
                   And a.Table_Name = b.Table_Name
                   And c.Owner = b.Owner
                   And c.Table_Name = b.Table_Name
                   And c.Constraint_Name = a.Constraint_Name
                   And a.Constraint_Type = 'P'
                 Group By c.Owner, a.Constraint_Name, c.Table_Name) b
         Where a.Owner = b.Owner
           And a.Table_Name = b.Table_Name),
       a.Key_Col2 =
       (Select b.Pri_Key
          From (Select c.Owner Owner,
                       a.Constraint_Name,
                       c.Table_Name Table_Name,
                       Listagg('a.' || c.Column_Name || '=c.' ||
                               c.Column_Name,
                               ' and ') Within Group(Order By c.Position) Pri_Key
                  From Dba_Constraints    a,
                       test.temp_Merge_Sql b,
                       Dba_Cons_Columns   c
                 Where a.Owner = b.Owner
                   And a.Table_Name = b.Table_Name
                   And c.Owner = b.Owner
                   And c.Table_Name = b.Table_Name
                   And c.Constraint_Name = a.Constraint_Name
                   And a.Constraint_Type = 'P'
                 Group By c.Owner, a.Constraint_Name, c.Table_Name) b
         Where a.Owner = b.Owner
           And a.Table_Name = b.Table_Name)
 Where a.Flag Is Null
   And a.Key_Col1 Is Null;

3、生成merge脚本

Declare
  v_Sql   Clob;
  v_Part1 Varchar2(4000);
  v_Part2 Varchar2(4000);
Begin
  For Cur In (Select a.Owner, a.Table_Name, a.Key_Col1, a.Key_Col2
                From test.temp_Merge_Sql a
               Where a.Flag Is Null
                 And a.Key_Col1 Is Not Null
                 And a.Key_Col2 Is Not Null)
  Loop
    Dbms_Lob.Createtemporary(v_Sql, True); ----初始化clob对象
    Select 'merge into ' || Cur.Owner || '.' || Cur.Table_Name ||
           ' a using (select * from ' || Cur.Owner || '.temp_' ||
           Cur.Table_Name || ')c on (' || Cur.Key_Col2 ||
           ') when matched then update set ' || Listagg(a.Col, ',') Within Group(Order By Column_Id)
      Into v_Part1
      From (Select 'a.' || Column_Name || '=c.' || Column_Name As Col,
                   'a.' || Column_Name As Col2,
                   'c.' || Column_Name As Col3,
                   Column_Id,
                   Table_Name
              From Dba_Tab_Cols
             Where Table_Name = Cur.Table_Name
               And Owner = Cur.Owner
               And Column_Name Not In
                   (Select Substr(Cur.Key_Col1 || ',',
                                  Decode(Level,
                                         1,
                                         0,
                                         Instr(Cur.Key_Col1 || ',',
                                               ',',
                                               1,
                                               Level - 1) + 1),
                                  Instr(Cur.Key_Col1 || ',', ',', 1, Level) -
                                  Decode(Level,
                                         1,
                                         0,
                                         Instr(Cur.Key_Col1 || ',',
                                               ',',
                                               1,
                                               Level - 1)) - 1)
                      From Dual
                    Connect By Level <=
                               Length(Cur.Key_Col1 || ',') -
                               Length(Replace(Cur.Key_Col1 || ',', ',', '')))
             Order By Column_Id) a
     Group By a.Table_Name;
    Select ' when not matched then insert(' || Listagg(a.Col2, ',') Within Group(Order By Column_Id) || ') values (' || Listagg(a.Col3, ',') Within Group(Order By Column_Id) || ');'
      Into v_Part2
      From (Select 'a.' || Column_Name || '=c.' || Column_Name As Col,
                   'a.' || Column_Name As Col2,
                   'c.' || Column_Name As Col3,
                   Column_Id,
                   Table_Name
              From Dba_Tab_Cols
             Where Table_Name = Cur.Table_Name
               And Owner = Cur.Owner
             Order By Column_Id) a
     Group By a.Table_Name;
    Dbms_Lob.Append(v_Sql, v_Part1);
    Dbms_Lob.Append(v_Sql, v_Part2);
    Update test.temp_Merge_Sql a
       Set (Part1, Part2, Ouput_Sql) =
           (Select v_Part1, v_Part2, v_Sql
              From Dual)
     Where a.Owner = Cur.Owner
       And a.Table_Name = Cur.Table_Name;
    Update test.temp_Merge_Sql a
       Set a.Flag = '1'
     Where a.Owner = Cur.Owner
       And a.Table_Name = Cur.Table_Name;
    Commit;
  End Loop;
End;
/

4、查看输出merge语句

 Select *
    From test.temp_Merge_Sql;