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
| DECLARE
-- Déclaration Curseurs
CURSOR Cur_1 IS
SELECT NUMV,
NUMP,
NUMC
FROM CONT WHERE NUMV IN (A,B,C);
rec_1 Cur_1%ROWTYPE;
CURSOR Cur_2 (NV CONT.NUMV%TYPE, NP CONT.NUMP%TYPE, NC CONT.NUMC%TYPE) IS
select
c.NUMV,
c.NUMP,
c.NUMC,
c.livres
from cont c,
version v,
pers p,
isef f
where
c.NUMV in (81,85,90) and
c.NUMV = NV and
v.NUMV = NV and
p.NUMV = NV and
p.pers = NP and
c.sef = f.sef and
c.actif= 'O' and
f.fct <> 'AG' and
f.fct <> 'PG' and
f.fcti <> 'OP' and
--- and
....;
rec_2 Cur_2%ROWTYPE;
BEGIN
FOR rec_1 IN Cur_1 LOOP
FOR rec_2 IN Cur_2(rec_1.NUMV,rec_1.NUMP,rec_1.NUMC)
LOOP
UPDATE CONT SET LIVRES = 'Y'
WHERE NUMV = rec_1.NUMV AND
NUMP = rec_1.NUMP AND
NUMC = rec_1.NUMC;
IF Cur_2%NOTFOUND
THEN
UPDATE CONT SET LIVRES = 'N';
END IF;
END LOOP;
END LOOP;
COMMIT
END; |
Partager