达梦 Oracle 生成 insertOrUpdate 插入更新函数模板
[MySQL] ON DUPLICATE KEY UPDATE
- 达梦 和 Oracle 不支持 ON DUPLICATE KEY UPDATE，手写相关插入更新方法十分繁琐，以下就是生成模板的相关代码
自定义函数
CREATE OR REPLACE FUNCTION insertOnUpdate(
p_table_name VARCHAR2,
v_java NUMBER := 1)
RETURN CLOB
AS
v_is_pk PLS_INTEGER := 0;
v_tf_c CLOB;
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)
GROUP BY a.column_name,
a.column_id
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;
-- tf
SELECT LOWER(SUBSTR(dj.column_name, 1, 1)) || SUBSTR(REPLACE(INITCAP(dj.column_name), '_', ''), 2)
INTO v_tf_c
FROM dual;
--参数语句
v_sql_param := v_sql_param || ', #{item.' || v_tf_c || '} as ' || dj.column_name;
IF v_is_pk = 0
THEN
--更新语句
v_sql_update := v_sql_update || ' ,m.' || dj.column_name || '=t.' || dj.column_name || CHR(10);
END IF;
--插入语句
v_sql_query := v_sql_query || ' ,' || dj.column_name;
v_sql_insert := v_sql_insert || ' ,t.' || 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 || ' m ' || CHR(10);
v_sql_total := v_sql_total || 'using (<foreach collection="list" item="item" separator="UNION ALL"> select ' ||
SUBSTR(v_sql_param, 3) || ' from dual </foreach>) t ' || 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 || ' m' || 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;
使用函数生成代码
SELECT insertOnUpdate('healthy_config_detail') FROM dual;
生成案例
merge into healthy_config_detail m
using (
<foreach collection="list" item="item" separator="UNION ALL">select #{item.id} as id, #{item.type} as type,
#{item.dataId} as data_id, #{item.dataName} as data_name, #{item.name} as name, #{item.createTime} as create_time,
#{item.score} as score, #{item.description} as description, #{item.delFlag} as del_flag, #{item.updateTime} as
update_time, #{item.status} as status from dual
</foreach>) t
on (t.id=m.id)
when matched then update set
m.type=t.type
,m.data_id=t.data_id
,m.data_name=t.data_name
,m.name=t.name
,m.create_time=t.create_time
,m.score=t.score
,m.description=t.description
,m.del_flag=t.del_flag
,m.update_time=t.update_time
,m.status=t.status
when not matched then
insert(id ,type ,data_id ,data_name ,name ,create_time ,score ,description ,del_flag ,update_time ,status)
values(t.id ,t.type ,t.data_id ,t.data_name ,t.name ,t.create_time ,t.score ,t.description ,t.del_flag ,t.update_time ,t.status)
注意问题
