Oracle merge into 命令用法

SQL #oracle2012-09-01 10:54

作用：merge into 解决用B表跟新A表数据，如果A表中没有，则把B表的数据插入A表；当处理大数据量是，该方法的效率很高。

语法：
MERGE INTO [your table-name] [rename your table here]
USING ( [write your query here] )[rename your query-sql and using just like a table]
ON ([conditional expression here] AND [...]...)
WHEN MATHED THEN [here you can execute some update sql or something else ]
WHEN NOT MATHED THEN [execute something else here ! ]

实例：

merge into data_complaint_day_test2 s
  using (select seq_id,
               handle_date,
               V_COMPLAINT_TYPE, 
               STATISTICS_LEVEL, 
               feedback_date,
               node,
               done_date,
               finished_date,
               handle_workgroup,
               v_dept_name,
               v_area,
               n_sh_complaint
          from data_complaint_day_test t
          where t.rowid  in (select max(b.rowid) from data_complaint_day_test b group by (b.seq_id)))  t
  on (s.seq_id = t.seq_id )
  when matched then 
    update set 
      s.handle_date = t.handle_date,
      s.service_req_type = t.v_complaint_type,
      s.user_level = t.STATISTICS_LEVEL,
      s.feedback_date = t.feedback_date,
      s.node = t.node,
      s.done_date = t.done_date,
      s.finished_date = t.finished_date,
      s.handle_workgroup = t.handle_workgroup,
      s.v_dept_name = t.v_dept_name,
      s.v_area = t.v_area
  when not matched then 
    insert ( s.seq_id,
             s.handle_date,
             s.service_req_type,
             s.user_level,
             s.feedback_date,
             s.node,
             s.done_date,
             s.finished_date,
             s.handle_workgroup,
             s.v_dept_name,
             s.v_area,
             s.n_sh_complaint) 
    values ( t.seq_id,
             t.handle_date,
             t.V_COMPLAINT_TYPE,
             t.STATISTICS_LEVEL,
             t.feedback_date,
             t.node,
             t.done_date,
             t.finished_date,
             t.handle_workgroup,
             t.v_dept_name,
             t.v_area,
             t.n_sh_complaint);

where t.rowid in (select max(b.rowid) from data_complaint_day_test b group by (b.seq_id))
因为在data_complaint_day_test 表中会出现seq_id字段相同的记录，这里只取一条

上面代码是一个存储过程的部分代码。

测试效果：10万条数据，15s完成

SQL 基础

SQL 高级

SQL 函数

SQL 总结

Oracle merge into 命令用法

相关文章