【快速操作】从300万中筛选出适合条件的30万数据表

1、创建一个空表table1,两个字段,用来存放30万数据
create table table1
(
  IDCARD         VARCHAR2(27),
  PHOTO          BLOB
)

2、通过总表t_photo找出适合条件的30万数据出来，存储30万数据的rowid来创造一个临时表temp_phid
create table temp_phid as
select rowid phid from t_photo
where (substr(idcard,7,8) between '19600101' and '19891231') and (idcard like '421202%' or idcard like '422301%')

3、通过temp_phid表分成五个临时表，把rowid分开存到五个临时表中
create table test as
select phid from temp_phid where rownum>0 and rownum<60001
create table test2 as
select phid from temp_phid where rownum>60000 and rownum<120001
create table test3 as
select phid from temp_phid where rownum>120000 and rownum<180001
create table test4 as
select phid from temp_phid where rownum>180000 and rownum<240001
create table test5 as
select phid from temp_phid where rownum>240000 and rownum<296834

4、通过创建五个存储过程来同时向一个table1表中插入数据,单条提交
create or replace procedure insert_pid_image1 AS
  CURSOR cr_owid1 IS
    SELECT owid from test;
  str_owid1 test.owid%TYPE;
BEGIN
  OPEN cr_owid1;
  LOOP
    FETCH cr_owid1
      INTO str_owid1;
    EXIT WHEN cr_owid1%NOTFOUND;
    insert into table1(pid,image) select pid,image from t_photo WHERE rowid = str_owid1;
    DELETE from test WHERE owid = str_owid1;
    COMMIT;
  END LOOP;
  CLOSE cr_owid1;
END;

create or replace procedure insert_pid_image2 AS
  CURSOR cr_owid2 IS
    SELECT owid from test1;
  str_owid2 test1.owid%TYPE;
BEGIN
  OPEN cr_owid2;
  LOOP
    FETCH cr_owid2
      INTO str_owid2;
    EXIT WHEN cr_owid2%NOTFOUND;
    insert into table1(pid,image) select pid,image from t_photo WHERE rowid = str_owid2;
    DELETE from test1 WHERE owid = str_owid2;
    COMMIT;
  END LOOP;
  CLOSE cr_owid2;
END;

create or replace procedure insert_pid_image3 AS
  CURSOR cr_owid3 IS
    SELECT owid from test2;
  str_owid3 test2.owid%TYPE;
BEGIN
  OPEN cr_owid3;
  LOOP
    FETCH cr_owid3
      INTO str_owid3;
    EXIT WHEN cr_owid3%NOTFOUND;
    insert into table1(pid,image) select pid,image from t_photo WHERE rowid = str_owid3;
    DELETE from test2 WHERE owid = str_owid3;
    COMMIT;
  END LOOP;
  CLOSE cr_owid3;
END;

create or replace procedure insert_pid_image4 AS
  CURSOR cr_owid4 IS
    SELECT owid from test3;
  str_owid4 test3.owid%TYPE;
BEGIN
  OPEN cr_owid4;
  LOOP
    FETCH cr_owid4
      INTO str_owid4;
    EXIT WHEN cr_owid4%NOTFOUND;
    insert into table1(pid,image) select pid,image from t_photo WHERE rowid = str_owid4;
    DELETE from test3 WHERE owid = str_owid4;
    COMMIT;
  END LOOP;
  CLOSE cr_owid4;
END;

create or replace procedure insert_pid_image5 AS
  CURSOR cr_owid5 IS
    SELECT owid from test4;
  str_owid5 test4.owid%TYPE;
BEGIN
  OPEN cr_owid5;
  LOOP
    FETCH cr_owid5
      INTO str_owid5;
    EXIT WHEN cr_owid5%NOTFOUND;
    insert into table1(pid,image) select pid,image from t_photo WHERE rowid = str_owid5;
    DELETE from test4 WHERE owid = str_owid5;
    COMMIT;
  END LOOP;
  CLOSE cr_owid5;
END;