boucle PL\SQL de mise à jour d'un champ

[marieo]

Bonjour,
J'ai récupéré le code suivant d'une base de données ACCESS et je souhaiterais le mettre à jour pour un base Oracle (dans un bloc PL\SQL ou autre solution).

Merci beaucoup à tous pour vos idées...
MarieO

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
ReqSQL = "SELECT [DMI_FINESS], [DMI_NUMRSA], [DMI_CODE], count(*) FROM [MCO_DMI] " & _
           "GROUP BY [DMI_FINESS], [DMI_NUMRSA], [DMI_CODE] HAVING count(*)<>1;"
  SET rs = CurrentDb.OpenRecordset(ReqSQL)
  While NOT rs.EOF
    ReqSQL1 = "SELECT * FROM [MCO_DMI] WHERE [DMI_NUMDMI] = 1 AND " & _
                 "[DMI_FINESS]=" & Chr(34) & rs.FIELDS(0).Value & Chr(34) & " AND " & _
                 "[DMI_NUMRSA]=" & rs.FIELDS(1).Value & " AND " & _
                 "[DMI_CODE]=" & Chr(34) & rs.FIELDS(2).Value & Chr(34) & ";"
    SET rs1 = CurrentDb.OpenRecordset(ReqSQL1)
    i = 0
    While i <= rs.FIELDS(3).Value AND NOT rs1.EOF
      i = i + 1
      rs1.Edit
      rs1!DMI_NUMDMI = i
      rs1.UPDATE
      rs1.MoveNext
    Wend
    rs1.Close
    rs.MoveNext
  Wend
  rs.Close

[McM]

A priori je dirais un truc comme ça (a vérifier bien sur)

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
BEGIN
	FOR rs IN (SELECT DMI_FINESS, DMI_NUMRSA, DMI_CODE, COUNT(*) nb FROM MCO_DMI
           GROUP BY DMI_FINESS, DMI_NUMRSA, DMI_CODE HAVING COUNT(*) <> 1)
  LOOP         
		UPDATE MCO_DMI
    SET dmi_numdmi = ROWNUM
    WHERE DMI_NUMDMI = 1 
    AND DMI_FINESS = rs.DMI_FINESS
    AND DMI_NUMRSA = rs.DMI_NUMRSA
    AND DMI_CODE = rs.DMI_CODE 
    AND ROWNUM <= rs.nb;
  END LOOP;
  COMMIT;
END;

[boussafi]

je t'ai préparé cette procédure, essaye la et tu me rends la réponse.

Code :

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;