请教分段提交的写法!

Question

我有一个大数据量表用inter into的方式 生在动态SQL 写入到另一个表数据量最大的有 8000W请教 怎么对这个插入语句 加入分段提交.

Answer 1

insert语句增加append提示定义个计数用的number变量，达到某一数量就commit

Answer 2

-- 如果有主键，就好办一点：(假设你这个大数据量的表有递增主键 ID 字段)declare
  v_dept number(18,0); --  步长:假设每插入 100000 条记录提交一次!
  v_maxid number(18,0);
begin
  select max(id)+100000 into v_maxid from tb1; 
  -- 从源表中取出最大的ID给变量 v_maxid，用作循环退出条件，
  -- 为什么还要加 一个步长 100000 呢？自己去想想  v_dept := 1;
  while v_dept < v_maxid loop
    insert into tb2(col1, col2, ..., coln)
    select col1, col2, ..., coln
    from tb1
    where id>=v_dept and id<v_dept+100000 ; -- 有其它where 条件就在这里添加好啦!
    commit;
    v_dept := v_dept + 100000;
  end loop;
end;
/

Answer 3

-- 上面pl/sql 块可以加个异常处理，当出现异常的时候，打印出 v_dept变量的值，
-- 这样：你就知道在执行到什么地方的时候出错，
-- 当然：如果你已经将源表的ID字段插入到了目标表的话，
-- 你也可以查目标表的最大ID是多少就知道具体运行到哪个位置啦!

Answer 4

-- 先查出符合每个步长值的 rowid
select t.lines, t.row_id
from 
 ( select row_number() over(order by rowid) as lines, rowid row_id from tb1) t
where mod(lines,100000)=0
order by lines;-- 或者： where to_char(lines) like '%00000';-- 再根据 rowid 去 where 条件!

Answer 5

declare
  v_dept number(18,0); --  步长:假设每插入 100000 条记录提交一次!
  v_maxid number(18,0);
begin
 
  -- 从源表中取出最大的ID给变量 v_maxid，用作循环退出条件，
  -- 为什么还要加 一个步长 100000 呢？自己去想想  v_dept := 1;
  while v_dept < v_maxid loop
    insert into cktspg.history_hg(ym,cpcode,cmcode)
    select ym,cpcode,cmcode
    from gdjz.history_hg
    where rownum >=v_dept and rownum<v_dept+100000 ; -- 有其它where 条件就在这里添加好啦!
    commit;
    v_dept := v_dept + 100000;
  end loop;
end;
我改造好了 存储过程编译成功通过了
但是执行的时候 报错了.这是什么原因  我还有哪改错了吗

Answer 6

-- 用 rwoid ，看下面的语句行不(你去测试一下)：declare
  cursor cur is 
    with a as( select t.lines, t.row_id
                 from ( select row_number() over(order by rowid) as lines, rowid row_id from tb1 ) t
                where mod(lines,100000)=0 )
    select a1.row_id as min_rowid, a2.row_id as max_rowid
    from   a a1 left join a a2 on a1.lines=a2.lines-100000
    order by a1.lines asc;
    v_min_rowid rowid;
    v_max_rowid rowid;
begin
  for i in cur loop
    v_min_rowid := i.min_rowid;
    v_max_rowid := i.max_rowid;    if v_max_rowid is not null then
      insert into tb2(col1, col2, ..., coln)
      select col1, col2, ..., coln
      from tb1
      where rowid>=v_min_rowid  and rowid< v_max_rowid;
    else
      insert into tb2(col1, col2, ..., coln)
      select col1, col2, ..., coln
      from tb1
      where rowid>=v_min_rowid ;
    end if;    commit;  end loop;
end;
/

Answer 7

有点复杂 没看明白....汗 之前用ROWID 是因为里面的值 不是NUMBER型 就改用ROWNUBR了

Answer 8

-- 例如：我的实践操作：drop table emp2 purge;
drop table emp3 purge;create table emp2 as select * from emp;create table emp3 as select * from emp where 1=2;insert into emp2 select * from emp;
insert into emp2 select * from emp;
insert into emp2 select * from emp;
insert into emp2 select * from emp;
insert into emp2 select * from emp;
insert into emp2 select * from emp;
insert into emp2 select * from emp;
insert into emp2 select * from emp;
commit;select count(*) from emp2;
select count(*) from emp3;-- 将 emp2 表中的数据 插入 emp3 表，每插入10条记录提交一次!declare
  cursor cur is 
    with a as( select t.lines, t.row_id
                 from ( select row_number() over(order by rowid asc)-1 as lines, rowid row_id from emp2 ) t
                where to_char(lines) like '%0' )
    select a1.lines, a1.row_id as min_rowid, a2.lines as lines2, a2.row_id as max_rowid
    from   a a1 left join a a2 on a1.lines=a2.lines-10
    order by a1.lines asc;
    v_min_rowid rowid;
    v_max_rowid rowid;
    v_last_rowid rowid;
begin
  select max(rowid) into v_last_rowid from emp2;
  for i in cur loop
    v_min_rowid := i.min_rowid;
    v_max_rowid := nvl(i.max_rowid,v_last_rowid);    insert into emp3(empno, ename, job, mgr, hiredate, sal, comm, deptno)
    select empno, ename, job, mgr, hiredate, sal, comm, deptno
    from emp2
    where rowid>=v_min_rowid  and rowid< v_max_rowid;
    commit;
    dbms_output.put_line('v_min_rowid: '||v_min_rowid||'    v_max_rowid: '||v_max_rowid);
  end loop;    -- 别忘记插入最后一行：
    insert into emp3(empno, ename, job, mgr, hiredate, sal, comm, deptno)
    select empno, ename, job, mgr, hiredate, sal, comm, deptno
    from emp2
    where rowid=v_max_rowid;
    commit;
end;
/select count(*) from emp3;

Answer 9

-- 假设你的源表是 tb1 有 8000万条数据，且没有主键字段，你的目标表是 tb2，现在你要将 tb1表中的所有数据插入到 tb2，由于 tb1 表中的数据记录太多，
-- 我们采用分段提交(分段步长随便你定啦，我这里举例：每次插入10万条数据行)declare
  cursor cur is 
    with a as( select t.lines, t.row_id
                 from ( select row_number() over(order by rowid asc)-1 as lines, rowid row_id from tb1 ) t
                where to_char(lines) like '%00000' )
    select a1.lines, a1.row_id as min_rowid, a2.lines as lines2, a2.row_id as max_rowid
    from   a a1 left join a a2 on a1.lines=a2.lines-10
    order by a1.lines asc;
    v_min_rowid rowid;  -- 记录步长开始位置的 rowid
    v_max_rowid rowid;  -- 记录步长结束位置的 rwoid
    v_last_rowid rowid; -- 记录 tb1表中最大的 rowid 
begin
  select max(rowid) into v_last_rowid from tb1;
  for i in cur loop
    v_min_rowid := i.min_rowid;
    v_max_rowid := nvl(i.max_rowid,v_last_rowid);    insert into tb2(col1, col2, ..., coln)
    select col1, col2, ..., coln
    from tb1
    where rowid >= v_min_rowid  
      and rowid< v_max_rowid;
    commit;    -- dbms_output.put_line('v_min_rowid: '||v_min_rowid||'    v_max_rowid: '||v_max_rowid);
  end loop;  -- 别忘记插入最后一行：
  insert into tb2(col1, col2, ..., coln)
  select col1, col2, ..., coln
  from tb1
  where rowid = v_max_rowid;
  commit;end;
/select count(*) from tb1;
select count(*) from tb2;

Answer 10

encountered the symbol "tab" when expecting one of the following immediate  the symbol   was substituted for  "tab" to comtinue报这些错误  执行的时候

Answer 11

你的执行代码？你不会是在 sql*developer 工具里执行吧？最好: 在 sql*plus 中执行!

Answer 12

我创建成存储过程 
执行的存储过程 报这个错在测试窗里执行 没错误  但在PSQL里 调用存储过程的时候 就报这个错了

Answer 13

-- 封装成存储过程(注意：原来的 declare 关键字就不要啦)
create or replace procedure emp3_insert_proc
is
  cursor cur is 
    with a as( select t.lines, t.row_id
                 from ( select row_number() over(order by rowid asc)-1 as lines, rowid row_id from emp2 ) t
                where to_char(lines) like '%0' )
    select a1.lines, a1.row_id as min_rowid, a2.lines as lines2, a2.row_id as max_rowid
    from   a a1 left join a a2 on a1.lines=a2.lines-10
    order by a1.lines asc;
    v_min_rowid rowid;
    v_max_rowid rowid;
    v_last_rowid rowid;
begin
  select max(rowid) into v_last_rowid from emp2;
  for i in cur loop
    v_min_rowid := i.min_rowid;
    v_max_rowid := nvl(i.max_rowid,v_last_rowid);    insert into emp3(empno, ename, job, mgr, hiredate, sal, comm, deptno)
    select empno, ename, job, mgr, hiredate, sal, comm, deptno
    from emp2
    where rowid>=v_min_rowid  and rowid< v_max_rowid;
    commit;
    -- dbms_output.put_line('v_min_rowid: '||v_min_rowid||'    v_max_rowid: '||v_max_rowid);
  end loop;    -- 别忘记插入最后一行：
    insert into emp3(empno, ename, job, mgr, hiredate, sal, comm, deptno)
    select empno, ename, job, mgr, hiredate, sal, comm, deptno
    from emp2
    where rowid=v_max_rowid;
    commit;
end;
/

Answer 14

-- 我这用存储过程执行是没有问题的!scott@TBWORA> exec emp3_insert_proc;PL/SQL 过程已成功完成。scott@TBWORA> select count(*) from emp3;  COUNT(*)
----------
       126scott@TBWORA> exec emp3_insert_proc;PL/SQL 过程已成功完成。scott@TBWORA> select count(*) from emp3;  COUNT(*)
----------
       252scott@TBWORA> exec emp3_insert_proc;PL/SQL 过程已成功完成。scott@TBWORA> select count(*) from emp3;  COUNT(*)
----------
       378

Answer 15

在原表加多一个flag字段行不行，插入过的做个标记为1，然后做个循环

Answer 16

-- 完善一下：加个异常处理!-- 封装成存储过程(注意：原来的 declare 关键字就不要啦)
create or replace procedure emp3_insert_proc
is
  cursor cur is 
    with a as( select t.lines, t.row_id
                 from ( select row_number() over(order by rowid asc)-1 as lines, rowid row_id from emp2 ) t
                where to_char(lines) like '%0' )
    select a1.lines, a1.row_id as min_rowid, a2.lines as lines2, a2.row_id as max_rowid
    from   a a1 left join a a2 on a1.lines=a2.lines-10
    order by a1.lines asc;
    v_min_rowid rowid;
    v_max_rowid rowid;
    v_last_rowid rowid;
begin
  select max(rowid) into v_last_rowid from emp2;
  for i in cur loop
    v_min_rowid := i.min_rowid;
    v_max_rowid := nvl(i.max_rowid,v_last_rowid);    insert into emp3(empno, ename, job, mgr, hiredate, sal, comm, deptno)
    select empno, ename, job, mgr, hiredate, sal, comm, deptno
    from emp2
    where rowid>=v_min_rowid  and rowid< v_max_rowid;
    commit;
    -- dbms_output.put_line('v_min_rowid: '||v_min_rowid||'    v_max_rowid: '||v_max_rowid);
  end loop;    -- 别忘记插入最后一行：
    insert into emp3(empno, ename, job, mgr, hiredate, sal, comm, deptno)
    select empno, ename, job, mgr, hiredate, sal, comm, deptno
    from emp2
    where rowid=v_max_rowid;
    commit;
exception when others then
  -- 如果出错，打印 v_min_rowid 值，下次可以接着 v_min_rowid 值处继续执行!
  dbms_output.put_line('v_min_rowid: '||v_min_rowid);
end;
/

Answer 17

能不能 就我这个表名 帮我创建一个存储过程试试源表名gdjz.history_hg目标表:
cktspg.history_hg字段就只有 ym,cpcode,cmcode搞定再加分..急急..