需求：每遍历游标2的记录时分别去遍历游标1的数据，并替换后插入临时表

CREATE OR REPLACE PROCEDURE TESTQ

IS

DownDictate VARCHAR2(200) DEFAULT '';

t_IsExist NUMBER:=0;

use_DownDictate VARCHAR2(200) DEFAULT '';

BEGIN

--判断指令是否会错误        

SELECT DOWNDICTATE INTO DownDictate FROM T_MS_AUTOPLANDICTATE WHERE PROCEDURENAME='PKG_MEDI_IMM.QUERY_MEDI_IMM';          

IF DownDictate IS NULL THEN          

RETURN;        

END IF;        

--开始定义游标

DECLARE   

---定义游标       1

CURSOR C_PARAMETERS IS           

SELECT FUNCTIONNODECODE,FUNCTIONNODENAME FROM T_MS_FUNCTIONNODES WHERE FUNCTIONID =            (SELECT FUNCTIONID FROM T_MS_FUNCTION WHERE FUNCTIONPROCE='PKG_MEDI_IMM.QUERY_MEDI_IMM');        

---定义游标          2

CURSOR C_MEDIIMME IS           

SELECT * FROM (  SELECT              A.GRANTMASTER_BARCODE,A.GRANTMASTER_DATE,B.UNITINFO_NAME,C.USERINFO_REALNAME FROM T_LABEL_GRANTMASTER A              LEFT OUTER JOIN T_ER_UNITINFO B ON A.GRANTMASTER_FARMID=B.UNITINFO_ID              LEFT OUTER JOIN T_SYS_USERINFO C ON A.GRANTMASTER_FARMERSID = C.USERINFO_ID              WHERE C.USERINFO_MOBILE IS NOT NULL OR C.USERINFO_PHONE IS NOT NULL) T;

--定义游标1的行定义

TYPE paraType IS RECORD(           

code     VARCHAR2(20),           

codecn   VARCHAR2(50)         

);         

paraRowType paraType;

---定义游标2的行定义

 TYPE rtype IS RECORD(            

yzpch    VARCHAR2(50),            

g_date   DATE,            

yzc  VARCHAR2(100),            

yznh   VARCHAR2(50)         

);         

c_rtype rtype;

--开始执行         

BEGIN           

OPEN C_MEDIIMME;        --打开游标 2              

LOOP            

FETCH C_MEDIIMME INTO c_rtype;            

EXIT WHEN C_MEDIIMME%NOTFOUND;            

use_DownDictate := DownDictate;            

OPEN C_PARAMETERS;       --打开游标1      (需要每遍历游标2的一条数据时打开一次游标1)

--遍历参数表            

BEGIN              

LOOP                

FETCH C_PARAMETERS INTO paraRowType;                

EXIT WHEN C_PARAMETERS%NOTFOUND;                

IF paraRowType.code = 'yzpch' THEN                   

use_DownDictate := REPLACE(use_DownDictate,paraRowType.codecn||'#',c_rtype.yzpch);                

ELSIF paraRowType.code = 'yznh' THEN                   

use_DownDictate := REPLACE(use_DownDictate,paraRowType.codecn||'#',c_rtype.yznh);                

ELSIF paraRowType.code = 'yzc' THEN                   

use_DownDictate := REPLACE(use_DownDictate,paraRowType.codecn||'#',c_rtype.yzc);                

END IF;              

END LOOP;             

END;             

CLOSE C_PARAMETERS;    --关闭游标1          

--插入临时表              

INSERT INTO T_MEDIIMMETABLE(ReplaceResult,DateResult)              

VALUES(use_DownDictate,c_rtype.g_date);            

END LOOP;                      

CLOSE C_MEDIIMME;  --关闭游标2       

END;

END TESTQ;

本文链接

一:创建表

create table userinfo

(

　　id int,

　　name varchar(10),

　　age int,

　　sex int,

　　address varcahr(50)

)

二:添加数据

insert into userinfo values(1,'admin','20,'1','上海市');

insert into userinfo values(2,'admin','20,'1','上海市');

insert into userinfo values(3,'admin','20,'1','上海市');

insert into userinfo values(4,'admin','20,'1','上海市');

insert into userinfo values(4,'admin','20,'1','上海市');

commit;

三:查询重复的数据

select id, name,age,sex,address from userinfo where (name,age,sex,address) in (select name,age,sex,address from userinfo group by name,age,sex,address having count(1) > 1);

四:保留最小id的一行数据

select id, name,age,sex,address from userinfo where (name,age,sex,address) in (select name,age,sex,address from userinfo group by name,age,sex,address having count(1) > 1) and id not in(select min(id) from userinfo group by name,age,sex,address having count(1) >1);

五:删除重复数据

delete from usrinfo where id in (select id from userinfo where (name,age,sex,address) in (select name,age,sex,address from userinfo group by name,age,sex,address having count(1) > 1) and id not in(select min(id) from userinfo group by name,age,sex,address having count(1) >1));

commit;

注: 如果没有id,可以用oracle中的ROWNUM来添加虚拟的id;

本文链接