达梦 Oracle 生成 insertOrUpdate 插入更新函数模板

达梦 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)

注意问题

• 插入更新数据不能太多，不然会提示嵌套层次太多