oracle 自动生成merge into语句
1.在数据库写一个函数：
create or replace function dev_merge(p_table_name varchar2, v_java number:=0) return clob
as
v_is_pk pls_integer:=0;
v_sql_total clob;
v_sql_join clob;
v_sql_param clob;
v_sql_update clob;
v_sql_query clob;
v_sql_insert clob;
begin
select count(*) into v_is_pk
from user_cons_columns cu
inner join user_constraints au on cu.constraint_name = au.constraint_name and au.constraint_type = 'P'
where au.table_name = upper(p_table_name);
if v_is_pk=0 then
return '此表没有主键，无法生成！';
end if;
--循环所有字段
for dj in (select lower(a.column_name) column_name from user_tab_columns a where a.table_name=upper(p_table_name) order by a.column_id) loop
--获取主键
select count(*) into v_is_pk
from user_cons_columns cu
inner join user_constraints au on cu.constraint_name = au.constraint_name and au.constraint_type = 'P'
where au.table_name = upper(p_table_name) and cu.column_name=upper(dj.column_name);
--关联语句
if v_is_pk=1 then
v_sql_join:=v_sql_join||' and a.'||dj.column_name||'=b.'||dj.column_name;
end if;
--参数语句
if v_java=0 then
v_sql_param:=v_sql_param||', :'||dj.column_name||' as '||dj.column_name;
else
v_sql_param:=v_sql_param||', ? as '||dj.column_name;
end if;
if v_is_pk=0 then
--更新语句
v_sql_update:=v_sql_update||' ,a.'||dj.column_name||'=b.'||dj.column_name||chr(10);
end if;
--插入语句
v_sql_query:=v_sql_query||' ,'||dj.column_name;
v_sql_insert:=v_sql_insert||' ,b.'||dj.column_name;
end loop;
dbms_output.put_line(v_sql_param);
v_sql_total:=v_sql_total||chr(10)||chr(10);
v_sql_total:=v_sql_total||'merge into '|| p_table_name ||' a '||chr(10);
v_sql_total:=v_sql_total||'using (select '|| substr(v_sql_param,3) ||' from dual) b '||chr(10);
v_sql_total:=v_sql_total||'on ('||substr(v_sql_join,6)||')'||chr(10);
v_sql_total:=v_sql_total||'when matched then update set'||chr(10);
v_sql_total:=v_sql_total||' '||substr(v_sql_update,4);
v_sql_total:=v_sql_total||'when not matched then'||chr(10);
v_sql_total:=v_sql_total||' insert('||substr(v_sql_query,3)||')'||chr(10);
v_sql_total:=v_sql_total||' values('||substr(v_sql_insert,3)||')'||chr(10)||chr(10);
return v_sql_total;
end;
2.查询出生成的merge into 语句
select dev_merge('表名','1') from dual
非常实用的一个技巧，查询生成的merge into语句，直接拿来使用即可！
