Discussion :

[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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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;

[islamov2000]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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;