【存储过程】：删除表中历史数据记录

说明：数据表中如下结构：

photo_id           VARCHAR2(40)           ---照片ID
PID                    VARCHAR2(40)           ---身份证号

image                 blob                          ---照片

 

此表中的数据照片ID是主键，数据增加生成，身份证号相同的记录（照片ID不同）

要求取最新的照片记录，删除相同身份证的历史记录，留下最新的photo_id号的记录。具体实现如下：

（【找出要删除的所有记录】）--查询出多余的photo_id，并将结果插入到一张临时表temp_photo中
create table temp_photo
as
select photo_id from t_photo
minus
select phid from
(select pid,max(photo_id) phid from t_photo group by pid) table_id;

 

（单条提交【存储过程】）--利用过程删除table_name中的多余记录，并删除temp_photo中相同记录 ------
create or replace procedure del_photoid AS
  CURSOR cr_tempphotoid IS
    SELECT PHOTO_ID from temp_photo;
  str_photoid temp_photo.PHOTO_ID%TYPE;
BEGIN
  OPEN cr_tempphotoid;
  LOOP
    FETCH cr_tempphotoid
      INTO str_photoid;
    EXIT WHEN cr_tempphotoid%NOTFOUND;
    DELETE from t_photo WHERE PHOTO_ID = str_photoid;
    DELETE from temp_photo WHERE PHOTO_ID = str_photoid;
    COMMIT;
  END LOOP;
  CLOSE cr_tempphotoid;
END;

 

 

 

（多条提交【存储过程】）--利用过程删除table_name中的多余记录，并删除temp_photo中相同记录 ------

create or replace procedure del_photoid AS
  CURSOR cr_tempphotoid IS
    SELECT PHOTO_ID from temp_photo;
  type t_photoid is table of temp_photo.PHOTO_ID%TYPE;
  str_photoid t_photoid;
BEGIN
  OPEN cr_tempphotoid;
  LOOP
    FETCH cr_tempphotoid bulk collect  INTO str_photoid limit 1000;
    for i in 1 .. str_photoid.count
      loop
        DELETE from temp WHERE PHOTO_ID = str_photoid(i);
        DELETE from temp_photo WHERE PHOTO_ID = str_photoid(i);
      end loop;
    exit when str_photoid.count < 1000;
    COMMIT;
  END LOOP;
  CLOSE cr_tempphotoid;
END;