Salut à tous,

soit la procedure suivante j'aimerais savoir comment connaitre le nombre de lignes qui ont été aktualisées respectivement inserer. Car mon encadreur aimerai avoir les informations pour la table d'audit.

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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
 
CREATE OR REPLACE PROCEDURE  P_insertion IS
 
    str_ereur           varchar2(200);
    sql_str          varchar2(32000);
 
    CURSOR C1 IS 
                      SELECT *
                      FROM table_1 t1,  table_2 t2
                      WHERE NOT EXISTS (
                                        SELECT 1 FROM table_3 t3
                                        WHERE   t1.id_medecin    =   t3.id_medecin  
                                        AND     t1.id_s    =   t3.id_s
                                        AND     t1.id_seq    =   t3.id_seq  
                                        AND     t1.id_an    =   t3.id_an
                                        AND     t1.id_ste    =   t3.id_ste
					)
                       AND     t1.id_statut  = 'new'
                       AND     t1.id_employe   = t2.id_employe;
 
	CURSOR C2_upd IS SELECT 
                          t1.id_medecin,
                          t1.id_section,
                          t1.id_produit,
                          t1.id_reseau
    FROM    table_1 t1
    WHERE EXISTS ( SELECT 1 FROM table_3 t3
    		WHERE   t1.id_medecin =   t3.id_medecin  
    		AND     t1.id_s    =   t3.id_s
    		AND     t1.id_seq  =   t3.id_seq  
    		AND     t1.id_an   =   t3.id_an
    		AND     t1.id_ste  =   t3.id_ste
    		AND     t1.row_id  !=   t3.row_id
		)
    AND     STATUS = 0;
 
BEGIN  
   FOR C1_rec IN C1 LOOP 
                INSERT INTO table_3  t3
                   ( t3.id_medecin, 
                      t3.id_section, 
                      t3.id_produit,
                      t3.id_reseau
                    )
                      VALUES 
                      (
                       C1_rec.id_medecin, 
                       C1_rec.id_section, 
                       C1_rec.id_produit,
                       C1_rec.t2.id_reseau_change
                      ) ;  
	end loop;
 
 
	FOR C2_u IN C2_upd LOOP
		--> Update
 
		UPDATE table_3 t3
        	SET (  t3.id_medecin = C2_u.id_medecin,
                	t3.id_section = C2_u.id_section,
                	t3.id_produit = C2_u.id_produit,
                	t3.id_reseau = C2_u.id_reseau
            	) 
		WHERE t3.id_medecin = C2_u.id_medecin
		AND ....;
	END LOOP;
 
commit; 
 
exception            
    when others then			
            rollback;
              str_ereur := substr(sqlerrm,1,200);
          INSERT INTO test (test ) VALUES (str_ereur);
END P_insertion;
Merci
Fiona