1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
| create or replace procedure test as
cursor ReqSQL is
SELECT DMI_FINESS, DMI_NUMRSA, DMI_CODE, count(*) compte
FROM MCO_DMI
GROUP BY DMI_FINESS, DMI_NUMRSA, DMI_CODE
HAVING count(*) <> 1;
CURSOR ReqSQL1(p_0 number, p_1 varchar2, p_2 varchar2) IS
SELECT *
FROM MCO_DMI
WHERE DMI_NUMDMI = 1
AND DMI_FINESS = p_0
AND DMI_NUMRSA = p_1
AND DMI_CODE = p_2 ;
i number;
begin
for RS in ReqSQL loop
--
begin
i := 0;
for Rs1 in ReqSQL1(Rs.Dmi_Finess, Rs.Dmi_Numrsa, Rs.Dmi_Code) loop
--
begin
i := i + 1;
UPDATE MCO_DMI set DMI_NUMDMI = i
WHERE DMI_FINESS = Rs.Dmi_Finess
AND DMI_NUMRSA = RS.Dmi_Numrsa
AND DMI_CODE = Rs.Dmi_Code ;
commit
end;
end loop;
end;
end loop;
end test; |
Partager