oracle 存储过程游标-CSDN博客

本文链接：https://blog.csdn.net/timesongjie/article/details/6717774

该博客介绍了如何在Oracle数据库中使用存储过程配合游标来删除重复数据，保留一条记录。具体步骤包括声明游标，查询重复数据，并通过循环遍历游标，删除数据量大于1的任务记录。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

贴出个人工作中写的几个存储过程:

一删除数据库中重复数据，保留一条

create or replace procedure proc5 as
begin

-- 游标cur 查询出记录中主键id 和重复记录列
declare cursor cur is select t.mission_id,t.task_name from sst_mission t where t.project_name='114登录扫描第三轮测试';

    --传入重复记录列参数，查找出重复记录条数
    cursor cur2(tName sst_mission.task_name%type) is select count(*) as c from sst_mission where sst_mission.task_name = tName and sst_mission.project_name='114登录扫描第三轮测试';
    missionId sst_mission.mission_id%type;
    taskName sst_mission.task_name%type;
    begin
     if cur%isopen = false then

   --打开游标
     open cur;
     end if;
       --遍历游标，赋值给变量

loop
fetch cur into missionId,taskName;

     --循环遍历，删除数据量大于1的纪录
          for countNum in cur2(taskName)
          loop
            if countNum.c>1 then
              delete from sst_execute where sst_execute.mission_id = missionId;
              delete from sst_mission where sst_mission.task_name = taskName and sst_mission.mission_id = missionId;
            end if;
          end loop;

        --游标未提取到数据为true退出循环
        exit when cur%notfound;
      end loop;

end;
end proc5;