erreurs de compilation

[asnaeb]

Bonjour,

je ne suis vraiment pas doué dans le sql et j'essaie de faire un trigger mais j'ai des problèmes de compilation. Pourriez-vous m'aider svp ? Je ne comprend vraiment rien ... :s

Voici les tables qui sont utilisées dans le trigger :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE ARTICLE (
  EAN NUMERIC(13) NOT NULL,
  LIBELLE VARCHAR2(30),
  CATEGORIE VARCHAR2(30),
  EMBALLAGE VARCHAR2(30),
  QUANTITE NUMERIC(5),
  PERISSABLE CHAR(1) CONSTRAINT ARTICLE_PERISSABLE CHECK (PERISSABLE IN('Y', 'N')),
  INITIALISATION CHAR(1) CONSTRAINT ARTICLE_INIT CHECK(INITIALISATION IN ('Y', 'N')), 
  INITQUANTITE NUMERIC(5),
  CONSTRAINT PRIMARY_KEY_ARTICLE PRIMARY KEY (EAN));
 
CREATE TABLE CONCERNER (
  EAN NUMERIC(13) NOT NULL,
  IDCMDSMARCHE NUMERIC(5) NOT NULL,
  CONSTRAINT PRIMARY_KEY_CONCERNER PRIMARY KEY (EAN, IDCMDSMARCHE));
 
CREATE TABLE CMD_SUPERMARCHE (
  IDCMDSMARCHE NUMERIC(5) NOT NULL,
  DATECMD DATE,
  LIVRE CHAR(1) CONSTRAINT CMD_SUPERMARCHE_LIVRE CHECK (LIVRE IN ('Y', 'N')),
  CONSTRAINT PRIMARY_KEY_CMD_SUPERMARCHE PRIMARY KEY (IDCMDSMARCHE));

Voici le trigger :

Code :

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
CREATE OR REPLACE
TRIGGER VentilSuperM
BEFORE INSERT ON ARTICLE
FOR EACH ROW
 
DECLARE
 
EANCONCERNER CONCERNER.EAN%TYPE;
IDCMDSMARCHECONCERNER CONCERNER.IDCMDSMARCHE%TYPE;
 
 
i NUMERIC(5);
quantiteVentilee NUMERIC(5);
quantiteDepart NUMERIC(5);
CURSOR CurseurConcerner
	IS SELECT EAN
	FROM CONCERNER
	WHERE CONCERNER.EAN = :NEW.EAN;
 
CURSOR CurseurInsert
	IS SELECT EAN,IDCMDSMARCHE INTO EANCONCERNER,IDCMDSMARCHECONCERNER
	FROM CONCERNER
	WHERE CONCERNER.EAN = :NEW.EAN;
 
-- Debut Trigger
BEGIN
	i := 0;
 
	OPEN CurseurConcerner;
	LOOP 
	FETCH CurseurConcerner INTO EANARTICLE;
		EXIT WHEN CurseurConcerner%NOTFOUND;
	i = i + 1 ;	
	END LOOP;
	CLOSE CurseurConcerner;
	--on vérifie si il y a un seul supermarche ou plusieurs
 
	IF i = 1 THEN
		--Un seul supermarche
		quantiteVentilee := :NEW.QUANTITE;
		quantiteDepart := :NEW.QUANTITE;
 
		BEGIN
 
			SELECT EAN,IDCMDSMARCHE INTO EANCONCERNER,IDCMDSMARCHECONCERNER
			FROM CONCERNER
			WHERE CONCERNER.EAN = :NEW.EAN;
 
			-- ON INSERE DANS LA TABLE 
 
			INSERT INTO VENTILATION VALUES (EANCONCERNER,IDCMDSMARCHECONCERNER,quantiteVentilee,quantiteDepart);
 
			EXCEPTION
				WHEN NO_DATA_FOUND 
					THEN RAISE_APPLICATION_ERROR (-20202,'Ne fonctionne pas ...');
		END;
 
  END IF;
 
	IF i > 1
		--Plusieurs supermarches
		quantiteVentilee := :NEW.QUANTITE / i ;
		quantiteDepart := :NEW.QUANTITE;
 
		BEGIN
 
			OPEN CurseurInsert;
			LOOP
			FETCH CurseurInsert;
				EXIT WHEN CurseurConcerner%NOTFOUND;
 
				--on insere dans la table
 
				INSERT INTO VENTILATION VALUES (EANCONCERNER,IDCMDSMARCHECONCERNER,quantiteVentilee,quantiteDepart);
 
			END LOOP;
			CLOSE CurseurInsert;
		END;	
 
	END IF;
 
	DEALLOCATE CURSOR CurseurConcerner;
	DEALLOCATE CURSOR CurseurInsert;
 
END;

Et voici les erreurs que j'obtient ... :

Erreur(13,24): PLS-00049: bad bind variable 'NEW.EAN'
Erreur(18,24): PLS-00049: bad bind variable 'NEW.EAN'
Erreur(27,4): PLS-00103: Encountered the symbol "=" when expecting one of the following: := . ( @ % ; The symbol ":= was inserted before "=" to continue.
Erreur(34,23): PLS-00049: bad bind variable 'NEW.QUANTITE'
Erreur(35,21): PLS-00049: bad bind variable 'NEW.QUANTITE'
Erreur(41,26): PLS-00049: bad bind variable 'NEW.EAN'
Erreur(54,3): PLS-00103: Encountered the symbol "QUANTITEVENTILEE" when expecting one of the following: * & - + / at mod remainder rem then <exposant (**)> and or || multiset The symbol "then" was substituted for "QUANTITEVENTILEE" to continue.
Erreur(54,23): PLS-00049: bad bind variable 'NEW.QUANTITE'
Erreur(55,21): PLS-00049: bad bind variable 'NEW.QUANTITE'
Erreur(61,23): PLS-00103: Encountered the symbol ";" when expecting one of the following: . into bulk
Erreur(69,4): PLS-00103: Encountered the symbol "CLOSE" when expecting one of the following: end not pragma final instantiable order overriding static member constructor map
Erreur(74,13): PLS-00103: Encountered the symbol "CURSOR" when expecting one of the following: := . ( @ % ;
Erreur(75,20): PLS-00103: Encountered the symbol "CURSEURINSERT" when expecting one of the following: := . ( @ % ; not null range default character
Erreur(28,4): PLS-00103: Encountered the symbol "=" when expecting one of the following: := . ( @ % ; The symbol ":= was inserted before "=" to continue.
Erreur(55,3): PLS-00103: Encountered the symbol "QUANTITEVENTILEE" when expecting one of the following: * & - + / at mod remainder rem then <exposant (**)> and or || multiset The symbol "then" was substituted for "QUANTITEVENTILEE" to continue.
Erreur(62,23): PLS-00103: Encountered the symbol ";" when expecting one of the following: . into bulk
Erreur(70,4): PLS-00103: Encountered the symbol "CLOSE" when expecting one of the following: end not pragma final instantiable order overriding static member constructor map
Erreur(75,13): PLS-00103: Encountered the symbol "CURSOR" when expecting one of the following: := . ( @ % ;
Erreur(76,20): PLS-00103: Encountered the symbol "CURSEURINSERT" when expecting one of the following: := . ( @ % ; not null range default character
Erreur(35,26): PLS-00103: Encountered the symbol "." when expecting one of the following: mod <identificateur> <identificateur entre guillemets> <variable attachée (bind variable)> continue current sql execute forall merge pipe purge The symbol "<identificateur>" was substituted for "." to continue.
Erreur(36,24): PLS-00103: Encountered the symbol "." when expecting one of the following: mod <identificateur> <identificateur entre guillemets> <variable attachée (bind variable)> continue current sql execute forall merge pipe purge The symbol "<identificateur>" was substituted for "." to continue.
Erreur(42,29): PLS-00103: Encountered the symbol "." when expecting one of the following: mod <identificateur> <identificateur entre guillemets> <variable attachée (bind variable)> continue current sql execute forall merge pipe purge The symbol "<identificateur>" was substituted for "." to continue.
Erreur(55,3): PLS-00103: Encountered the symbol "QUANTITEVENTILEE" when expecting one of the following: * & - + / at mod remainder rem then <exposant (**)> and or || multiset
Erreur(57,3): PLS-00103: Encountered the symbol "QUANTITEVENTILEE" when expecting one of the following: * & - + / at mod remainder rem then <exposant (**)> and or || multiset
Erreur(57,3): PLS-00103: Encountered the symbol "QUANTITEVENTILEE" when expecting one of the following: * & - + / at mod remainder rem then <exposant (**)> and or || multiset The symbol "then" was substituted for "QUANTITEVENTILEE" to continue.
Erreur(64,23): PLS-00103: Encountered the symbol ";" when expecting one of the following: . into bulk
Erreur(72,4): PLS-00103: Encountered the symbol "CLOSE" when expecting one of the following: end not pragma final instantiable order overriding static member constructor map
Erreur(77,13): PLS-00103: Encountered the symbol "CURSOR" when expecting one of the following: := . ( @ % ;
Erreur(78,20): PLS-00103: Encountered the symbol "CURSEURINSERT" when expecting one of the following: := . ( @ % ; not null range default character

[DAB.cz]

C'est le problem de client.
J'ai essayé lancer la création de trigger sous SQL*Plus et voilà l'erreur (en tchèque):

Code :

1
2
3
4
5
LINE/COL ERROR
-------- -----------------------------------------------------------------
28/3     PLS-00103: nalezen symbol "=" v situaci, kdy se předpokládala
         jedna z následujících možností:
         := . ( @ % ;

pour cette ligne:

Code :

i = i + 1 ;

Alors, quel est ton client, quelle est la manière de lancement?