Question

Oracle 批量插入数据怎么做

Answer 1

一个例子你看看

create or replace procedure p_test is
    v_day                varchar2(20);
    nextday              varchar2(20);
    cur_value            number;
    yes_value            number;
    thismonth_totalvalue number;
    lastmonth_totalvalue number;
    i                    number;
    tempday              varchar2(20);
    day_count            integer;
begin
    execute immediate 'truncate table DM_KPID_IW_AREA';
    v_day := '20070101';
    -- lastmonth_totalvalue := 0;
    for k in 0 .. 11 loop
        tempday              := to_char(add_months(to_date(v_day,
                                                           'yyyymmdd'),
                                                   k),
                                        'yyyymmdd');
        yes_value            := 0;
        thismonth_totalvalue := 0;
    
        -- 这里好像有一个比较方便的函数吧，记不清了
        day_count := to_char(last_day(to_date(tempday, 'yyyymmdd')), 'dd');
    
        for i in 0 .. day_count - 1 loop
            nextday              := to_char(to_date(tempday, 'yyyymmdd') + i,
                                            'yyyymmdd');
            cur_value            := trunc(dbms_random.value(1, 100), 0);
            thismonth_totalvalue := thismonth_totalvalue + cur_value;
        
            begin
                select t.thismonth_totalvalue
                  into lastmonth_totalvalue
                  from dm_kpid_iw_area t
                 where t.day_id =
                      --考虑如3月30日之类这样的情况，前一月没有对应的日子的情况，
                      --如果此时上月值为0的话，则应该为：
                      --to_char(add_months(to_date(nextday, 'yyyymmdd'), -1),
                      --'yyyymm') || substr(nextday, 7, 2);                      
                       to_char(add_months(to_date(nextday, 'yyyymmdd'), -1),
                               'yyyymmdd');
            exception
                when no_data_found then
                    lastmonth_totalvalue := 0;
            end;
        
            insert into dm_kpid_iw_area
                (day_id,
                 cur_value,
                 yes_value,
                 thismonth_totalvalue,
                 lastmonth_totalvalue)
            values
                (nextday,
                 cur_value,
                 yes_value,
                 thismonth_totalvalue,
                 lastmonth_totalvalue);
            yes_value := cur_value;
            commit;
        end loop;
    end loop;
end;

Answer 2

Oracle 批量插入数据的方法：
1 创建表
create table emp (id number(4),name varchar2(10));

2 创建序列
create sequence my_seq increment by 1 start with 1 maxvalue 1000;

3 创建获取序列的函数
create or replace function get_seq return number
as
seq number;
begin
select my_seq.nextval into seq from dual;
return seq;
end get_seq;
/

4 创建匿名块
DECLARE
i INT;
testid number(4);
BEGIN
i := 0;
WHILE(i < 1000)
LOOP
i := i + 1;
testid := get_seq;
INSERT INTO EMP(ID,NAME) VALUES(testid, 'Zheng');
END LOOP;
COMMIT;
END;
/

注：第4步也可以使用存储过程
create or replace procedure my_proc as
BEGIN
DECLARE
i INT;
testid number(4);
BEGIN
i := 0;
WHILE(i < 1000)
LOOP
i := i + 1;
testid := get_seq;
INSERT INTO EMP(ID,NAME) VALUES(testid, 'Zheng');
END LOOP;
COMMIT;
END;
END;
/

exec my_proc;
5 检验结果：
select count(*) from emp;

COUNT(*)
----------
1000