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 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108
| DECLARE
caract1 number(2) := NULL;
caract2 number(2) := NULL;
champNum number(15,6) :=NULL;
nbEnregistrement number(10) := NULL;
tailleChampPk number(3) := NULL;
chaine1 varchar2(10) := NULL;
chaine2 varchar2(10) := NULL;
chaine3 varchar2(10) := NULL;
nomTable varchar2(40) := NULL;
champPK varchar2(40) := NULL;
champModifier varchar2(40) := NULL;
champCaract varchar2(160 BYTE) := NULL;
BEGIN
nbEnregistrementTableGenerique := SELECT COUNT(*) FROM INT_QFS_DONNEES_REF;
WHILE nbEnregistrementTableGenerique != NULL && nbEnregistrementTableGenerique > 0
LOOP
nomTable := SELECT tableName FROM INT_QFS_DONNEES_REF WHERE rownum = 1;
champPK := SELECT recordKey FROM INT_QFS_DONNEES_REF WHERE rownum = 1;
caract1 := instr(champPK, '|', 1);
caract2 := instr(champPK, '|', caract1 + 1);
IF (caract1 > 0) then
chaine1 := substr(champPK, caract1-1);
end IF;
tailleChampPk := length(champPK);
IF (caract2 > 0) THEN
chaine2 := substr(champPK, caract1 + 1, tailleChampPk - caract2 - 1);
chaine3 := substr(champPK, caract2 + 1, tailleChampPk);
ELSE
chaine2 := substr(champPK, caract1 + 1, tailleChampPk);
end IF;
IF (chaine3 = NULL) THEN
DELETE FROM nomTable WHERE colonne1 = chaine1 AND colonne2 = chaine2
AND EXISTS (SELECT 1 FROM INT_QFS_DONNEES_REF WHERE actiontype = 'D' );
ELSE
DELETE FROM nomTable WHERE colonne1 = chaine1 AND colonne2 = chaine2
AND colonne3 = chaine3 AND EXISTS
(SELECT 1 FROM INT_QFS_DONNEES_REF WHERE actiontype = 'D');
end IF;
champModifier :=
(SELECT fildName FROM INT_QFS_DONNEES_REF WHERE rownum := 1 );
champNum :=
(SELECT FIELDNUMBER FROM INT_QFS_DONNEES_REF WHERE rownum := 1 );
champCaract :=
(SELECT FIELDCHARVALUE FROM INT_QFS_DONNEES_REF WHERE rownum := 1);
IF (chaine3 != NULL) THEN
IF (SELECT fildType FROM INT_QFS_DONNEES_REF) = 'N' THEN
IF EXISTS (SELECT * FROM nomTable WHERE colonne1 = chaine1
AND colonne2 = chaine2) THEN
(UPDATE champModifer SET nomTable WHERE colonne1 = chaine1
AND colonne2 = chaine2) := champNum;
ELSE
INSERT INTO nomTable (colonne1, colonne2, champModifier);
VALUES (chaine1, chaine2, champNum);
end IF
ELSIF
(SELECT fildType FROM INT_QFS_DONNEES_REF) = 'C' THEN
IF EXISTS (SELECT * FROM nomTable WHERE colonne1 = chaine1
AND colonne2 = chaine2) THEN
(UPDATE champModifer SET nomTable WHERE colonne1 = chaine1
AND colonne2 = chaine2) := champCaract;
ELSE
INSERT INTO nomTable(colonne1, colonne2, champModifier);
VALUES (chaine1, chaine2, champCaract);
end IF;
end IF
ELSE
IF (SELECT fildType FROM INT_QFS_DONNEES_REF) := 'N' THEN
IF EXISTS (SELECT * FROM nomTable WHERE colonne1 = chaine1
AND colonne2 = chaine2 AND colonne3 = chaine3) THEN
(UPDATE champModifer SET nomTable WHERE colonne1 = chaine1
AND colonne2 = chaine2 AND colonne3 = chaine3) := champNum;
ELSE
INSERT INTO nomTable (colonne1, colonne2, colonne3, champModifier);
VALUES (chaine1, chaine2, chaine3, champNum);
end IF;
ELSIF
(SELECT fildType FROM INT_QFS_DONNEES_REF) = 'C' THEN
IF EXISTS (SELECT * FROM nomTable WHERE colonne1 = chaine1
AND colonne2 = chaine2 AND colonne3 = chaine3) THEN
(UPDATE champModifer SET nomTable WHERE colonne1 = chaine1
AND colonne2 = chaine2 AND colonne3 = chaine3) := champCaract;
ELSE
INSERT INTO nomTable (colonne1, colonne2, colonne3, champModifier);
VALUES (chaine1, chaine2, chaine3, champCaract);
end IF;
end IF
end IF;
DELETE FROM INT_QFS_DONNEES_REF WHERE rownum = 1;
nbEnregistrementTableGenerique := nbEnregistrementTableGenerique - 1;
end loop;
END; |
Partager