Problème boucle WHILE

**kouette** · 26/05/2011, 10h40

Bonjour, je rencontre un problème sur mon programme car je n'arrive pas a la faire rentrer dans une boucle WHILE

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
nbEnregistrementTableGenerique := SELECT COUNT(*) FROM INT_QFS_DONNEES_REF;
WHILE nbEnregistrementTableGenerique != NULL && nbEnregistrementTableGenerique > 0 
LOOP
...
end loop;

Le message d'erreur est :
ORA-06550: line 15, column 37:
PLS-00103: Encountered the symbol "SELECT"
ORA-06550: line 18, column 3:
PLS-00103: Encountered the symbol "LOOP"

Merci pour votre attention.

**mnitu** · 26/05/2011, 10h51

Et si vous lisez le tutoriel PL/SQL ?

**kouette** · 26/05/2011, 10h57

Il est bien évident que dans mon programme la syntaxe est respectée...

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
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;

**mnitu** · 26/05/2011, 11h15

Envoyé par ImO-StOnE

Il est bien évident que dans mon programme la syntaxe est respectée...

Oui bien sûr. Mais, la question est la syntaxe de quel langage ?

**kouette** · 26/05/2011, 11h20

Je pense et j'espère avoir respecté la syntaxe du langage pl/sql. Mais si vous me le demandez je pense que ça ne doit pas être ça ...

**Yanika_bzh** · 26/05/2011, 11h24

Petit indice ici par exemple

bon courage

**kouette** · 26/05/2011, 12h32

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
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
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
  SELECT COUNT(*) INTO nbEnregistrementTableGenerique FROM INT_QFS_DONNEES_REF;
  WHILE nbEnregistrementTableGenerique != NULL && nbEnregistrementTableGenerique > 0 
  LOOP
    SELECT tableName INTO nomTable FROM INT_QFS_DONNEES_REF WHERE rownum = 1;
    SELECT recordKey INTO champPK 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; 
 
 
    SELECT fildName INTO champModifier FROM INT_QFS_DONNEES_REF WHERE rownum := 1;  
    SELECT FIELDNUMBER INTO champNum FROM INT_QFS_DONNEES_REF WHERE rownum := 1 ;
    SELECT FIELDCHARVALUE INTO champCaract 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 = champNum WHERE colonne1 = chaine1
        AND colonne2   = chaine2;
      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 = champCaract WHERE colonne1 = chaine1
          AND colonne2   = chaine2;
      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 = champNum WHERE colonne1 = chaine1
          AND colonne2   = chaine2 AND colonne3   = chaine3;
      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 = champCaract WHERE colonne1 = chaine1
         AND colonne2   = chaine2 AND colonne3   = chaine3;
      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;

Vous parait-il plus juste ?

**AbouZaid** · 26/05/2011, 12h48

Et bien c'est à toi de nous dire si cela ne génère aucune erreur!!

**kouette** · 26/05/2011, 13h32

Voici les erreurs qu'il me génère :

ORA-06550: line 16, column 48:
PLS-00103: Encountered the symbol "6" when expecting one of the following:

ORA-06550: line 47, column 78:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
ORA-06550: line 48, column 78:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
ORA-06550: line 49, column 78:
PLS-00103: Encountered the symbol "=" when expecting one of the following:

ORA-06550: line 52, column 8:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
ORA-06550: line 52, column 49:
PLS-00103: Encountered the symbol "=" when expecting one of the following:

**webfranc** · 26/05/2011, 13h59

ce message d'erreur est généré après la modification de la requête?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT COUNT(*) INTO nbEnregistrementTableGenerique FROM...

il nous faut la dernière version du code qui génère cette erreur

**kouette** · 26/05/2011, 14h27

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
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
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
  SELECT COUNT(*) INTO nbEnregistrementTableGenerique FROM INT_QFS_DONNEES_REF;
  WHILE nbEnregistrementTableGenerique != NULL && nbEnregistrementTableGenerique > '0'
  LOOP
    SELECT tableName INTO nomTable FROM INT_QFS_DONNEES_REF WHERE rownum = 1;
    SELECT recordKey INTO champPK 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; 
 
 
    SELECT fildName INTO champModifier FROM INT_QFS_DONNEES_REF WHERE rownum = 1;  
    SELECT FIELDNUMBER INTO champNum FROM INT_QFS_DONNEES_REF WHERE rownum = 1 ;
    SELECT FIELDCHARVALUE INTO champCaract FROM INT_QFS_DONNEES_REF WHERE rownum = 1;
 
  IF (chaine3 != NULL) THEN
    IF SELECT fildType FROM INT_QFS_DONNEES_REF WHERE rownum =1 = 'N' THEN
      IF EXISTS (SELECT * FROM nomTable WHERE colonne1 = chaine1 
      AND colonne2 = chaine2) THEN
 
        UPDATE champModifer SET nomTable = champNum WHERE colonne1 = chaine1
        AND colonne2   = chaine2;
      ELSE
        INSERT INTO nomTable (colonne1, colonne2, champModifier)
        VALUES (chaine1, chaine2, champNum);
      end IF
    ELSE
      IF (SELECT fildType FROM INT_QFS_DONNEES_REF WHERE rownum = 1) = 'C' THEN
      IF EXISTS (SELECT * FROM nomTable WHERE colonne1 = chaine1 
      AND colonne2 = chaine2) THEN
        UPDATE champModifer SET nomTable = champCaract WHERE colonne1 = chaine1
          AND colonne2   = chaine2;
      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 = champNum WHERE colonne1 = chaine1
          AND colonne2   = chaine2 AND colonne3   = chaine3;
      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 = champCaract WHERE colonne1 = chaine1
         AND colonne2   = chaine2 AND colonne3   = chaine3;
      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;

voila le code

Problème boucle WHILE

PL/SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager