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
|
COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;
/* Stored procedures */
CREATE PROCEDURE "SP_INS_EMP"
(
"CODE_EMP" INTEGER,
"CODE_TYPE" INTEGER,
"ENOM" VARCHAR(25),
"EPRENOM" VARCHAR(25),
"ELOGIN" VARCHAR(25),
"EPASS" VARCHAR(100),
"EDDV" DATE,
"EACCES" VARCHAR(1)
)
RETURNS
(
"RESULT_CODE" INTEGER
)
AS
BEGIN EXIT; END ^
ALTER PROCEDURE "SP_INS_EMP"
(
"CODE_EMP" INTEGER,
"CODE_TYPE" INTEGER,
"ENOM" VARCHAR(25),
"EPRENOM" VARCHAR(25),
"ELOGIN" VARCHAR(25),
"EPASS" VARCHAR(100),
"EDDV" DATE,
"EACCES" VARCHAR(1)
)
RETURNS
(
"RESULT_CODE" INTEGER
)
AS
BEGIN
SELECT ID_EMP FROM EMPLOYE
WHERE ID_EMP = :CODE_EMP
INTO :CODE_EMP;
/* Le type employé existe, on le met à jour */
IF ((CODE_EMP IS NOT NULL) AND (CODE_EMP <> 0)) THEN BEGIN
UPDATE EMPLOYE
SET ID_TYPEEMP = :CODE_TYPE,
NOM = :ENOM,
PRENOM = :EPRENOM,
LOGIN = :ELOGIN,
PASS = :EPASS,
DDV = :EDDV,
ACCES = :EACCES
WHERE (ID_EMP = :CODE_EMP) ;
END
/* Le type employé à créer */
ELSE BEGIN
/* Initialisation du code grâce à un générateur */
RESULT_CODE = GEN_ID(GEN_EMPLOYE_ID, 1);
INSERT INTO EMPLOYE(
ID_EMP,
ID_TYPEEMP,
NOM,
PRENOM,
LOGIN,
PASS,
DDV,
ACCES)
VALUES(
:RESULT_CODE,
:CODE_TYPE,
:ENOM,
:EPRENOM,
:ELOGIN,
:EPASS,
:EDDV,
:EACCES);
END
SUSPEND;
END
^
SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON; |