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
| create or replace PROCEDURE IMPORT_TRANSFERT
IS
CURSOR CUR
IS
SELECT
UPP_NAM,UPP_FNA,UPP_LOG,UPP_PWD,UPP_STA,UPP_INM,UPP_END,UPP_MEL,UPP_IPA,UPP_SST,UPP_PSS,TIT_KEY,CIV_KEY,AUD_KEY,UPP_PPW,
UPP_DPW,UPP_TIC,UPP_SUD,UPP_INN,CPS_KEY,MAU_KEY,UPP_WWI,UPP_EXT,UPP_UPD,UPP_SGN,UPP_DAD,UPP_DAF,UPP_AMS
FROM PPP_UPP;
BEGIN
FOR CUR_REC IN CUR
LOOP
BEGIN
UPDATE PPP_MAIN_TRANSFERT
SET PER_NAM = CUR_REC.UPP_NAM, PER_FNA = CUR_REC.UPP_FNA, UPP_LOG = CUR_REC.UPP_LOG, UPP_PWD = CUR_REC.UPP_PWD,
UPP_STA = CUR_REC.UPP_STA, UPP_INM = CUR_REC.UPP_INM, UPP_END = CUR_REC.UPP_END, UPP_MEL = CUR_REC.UPP_MEL,
UPP_IPA = CUR_REC.UPP_IPA, UPP_SST = CUR_REC.UPP_SST, UPP_PSS = CUR_REC.UPP_PSS, TIT_KEY = CUR_REC.TIT_KEY,
CIV_KEY = CUR_REC.CIV_KEY, AUD_KEY = CUR_REC.AUD_KEY, UPP_PPW = CUR_REC.UPP_PPW, UPP_DPW = CUR_REC.UPP_DPW,
UPP_TIC = CUR_REC.UPP_TIC, UPP_SUD = CUR_REC.UPP_SUD, UPP_INN = CUR_REC.UPP_INN, CPS_KEY = CUR_REC.CPS_KEY,
MAU_KEY = CUR_REC.MAU_KEY, UPP_WWI = CUR_REC.UPP_WWI, UPP_EXT = CUR_REC.UPP_EXT, UPP_UPD = CUR_REC.UPP_UPD,
UPP_SGN = CUR_REC.UPP_SGN, UPP_DAD = CUR_REC.UPP_DAD, UPP_DAF = CUR_REC.UPP_DAF
WHERE PER_NAM = CUR_REC.UPP_NAM
AND PER_FNA = CUR_REC.UPP_FNA
AND UPP_LOG = CUR_REC.UPP_LOG;
EXCEPTION
-- SI L'INSERTION ECHOUE, ON FAIT UN UPDATE
WHEN NO_DATA_FOUND THEN
BEGIN
INSERT INTO PPP_MAIN_TRANSFERT
(
PER_NAM,PER_FNA,UPP_LOG,UPP_PWD,UPP_STA,UPP_INM,UPP_END,UPP_MEL,UPP_IPA,UPP_SST,UPP_PSS,TIT_KEY,CIV_KEY,AUD_KEY,
UPP_PPW,UPP_DPW,UPP_TIC,UPP_SUD,UPP_INN,CPS_KEY,MAU_KEY,UPP_WWI,UPP_EXT,UPP_UPD,UPP_SGN,UPP_DAD,UPP_DAF,UPP_AMS
)
VALUES
(
CUR_REC.UPP_NAM,CUR_REC.UPP_FNA,CUR_REC.UPP_LOG,CUR_REC.UPP_PWD,CUR_REC.UPP_STA,CUR_REC.UPP_INM,CUR_REC.UPP_END,
CUR_REC.UPP_MEL,CUR_REC.UPP_IPA,CUR_REC.UPP_SST,CUR_REC.UPP_PSS,CUR_REC.TIT_KEY,CUR_REC.CIV_KEY,CUR_REC.AUD_KEY,
CUR_REC.UPP_PPW,CUR_REC.UPP_DPW,CUR_REC.UPP_TIC,CUR_REC.UPP_SUD,CUR_REC.UPP_INN,CUR_REC.CPS_KEY,CUR_REC.MAU_KEY,
CUR_REC.UPP_WWI,CUR_REC.UPP_EXT,CUR_REC.UPP_UPD,CUR_REC.UPP_SGN,CUR_REC.UPP_DAD,CUR_REC.UPP_DAF,CUR_REC.UPP_AMS
);
END;
END;
COMMIT;
END LOOP; -- END EXCEPTION
END; -- END PROGRAM |