oracle使用过程存储生成批量插入、更新sql,执行效率很棒 | mysql 技术论坛-江南app体育官方入口

直接上案例

解析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 协议》，转载必须注明作者和本文链接