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 76 77 78 79 80
|
CREATE OR REPLACE PROCEDURE P_insertion IS
str_ereur varchar2(200);
sql_str varchar2(32000);
Nbre_upd Number := 0;
Nbre_ins Number := 0;
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
) ;
Nbre_ins := Nbre_ins + SQL%Rowcount;
insert into table_audit (test) values ('Nbre ins: '||Nbre_ins||' '); commit;
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
Nbre_upd := Nbre_upd + SQL%Rowcount;
insert into table_audit (test) values ('Nbreupd: '||Nbre_upd||' '); commit;
END LOOP;
commit;
exception
when others then
rollback;
str_ereur := substr(sqlerrm,1,200);
INSERT INTO table_upd (test ) VALUES (str_ereur);
END P_insertion; |
Partager