【快速操作】从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;

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值