Oracle使用过程存储生成批量插入、更新SQL,执行效率很棒

直接上案例

解析json文件并生成对应的入库sql

-- 生成批量SQL
DECLARE 
    V_RULES VARCHAR2(4000) := '[{"NAME":"F00008_2","ALIAS":"限制准入名单","SUMMER":"行内黑名单","SOURCE":"bank"},{"NAME":"F00022_2","ALIAS":"反洗钱监管报送黑名单","SUMMER":"行内黑名单","SOURCE":"bank"}]';
    CURSOR ITEMS IS SELECT * FROM JSON_TABLE(V_RULES, '$[*]' COLUMNS (
                            NAME VARCHAR2(255) PATH '$.NAME',
                            ALIAS VARCHAR2(255) PATH '$.ALIAS',
                            SUMMER VARCHAR2(255) PATH '$.SUMMER',
                            SOURCE VARCHAR2(255) PATH '$.SOURCE'
                    ));
v_count NUMBER := 0;
v_index NUMBER := 0;
BEGIN
FOR item IN ITEMS LOOP
    v_count := v_index + 1020;

    dbms_output.put_line('INSERT INTO "T_CONFIG" ( "ID", "NAME", "ALIAS", "TYPE", "STATUS", "CREATE_AT", "UPDATE_AT", "SOURCE", "LEVEL", "SUMMER" )
VALUES
    (' || v_count || ',''' || item.NAME || ''',''' || item.ALIAS || ''',''1'',''1'',TO_DATE ( CURRENT_DATE, ''SYYYY-MM-DD HH24:MI:SS'' ),TO_DATE ( CURRENT_DATE, ''SYYYY-MM-DD HH24:MI:SS'' ),''' || item.SOURCE || ''',
    NULL,
    ' || item.SUMMER ||' 
    );');
    v_index := v_index + 1;
END LOOP;
END;

或者直接执行插入：(执行效率依然很高)

DECLARE 
    V_RULES VARCHAR2(4000) := '[{"NAME":"F00008_2","ALIAS":"限制准入名单","SUMMER":"行内黑名单","SOURCE":"bank"},{"NAME":"F00022_2","ALIAS":"反洗钱监管报送黑名单","SUMMER":"行内黑名单","SOURCE":"bank"}]';
    CURSOR ITEMS IS SELECT * FROM JSON_TABLE(V_RULES, '$[*]' COLUMNS (
                            NAME VARCHAR2(255) PATH '$.NAME',
                            ALIAS VARCHAR2(255) PATH '$.ALIAS',
                            SUMMER VARCHAR2(255) PATH '$.SUMMER',
                            SOURCE VARCHAR2(255) PATH '$.SOURCE'
                    ));
v_count NUMBER := 0;
v_index NUMBER := 0;
BEGIN
FOR item IN ITEMS LOOP
    v_count := v_index + 1020;

    INSERT INTO "T_RULE_CONFIG" ( "ID", "RULE_NAME", "RULE_ALIAS", "RULE_TYPE", "STATUS", "CREATE_AT", "UPDATE_AT", "RULE_SOURCE", "RULE_LEVEL", "RULE_SUMMER" )
VALUES
    (
    v_count,
    item.NAME,
    item.ALIAS,
    '1',
    '1',
    TO_DATE ( CURRENT_DATE, 'SYYYY-MM-DD HH24:MI:SS' ),
    TO_DATE ( CURRENT_DATE, 'SYYYY-MM-DD HH24:MI:SS' ),
    item.SOURCE,
    NULL,
    item.SUMMER,
    );
    v_index := v_index + 1;
END LOOP;
END;

本作品采用《CC 协议》，转载必须注明作者和本文链接