## Exemples du support de cours Séquence 3

## NB : lors des tests en direct (sans importation de script), ne pas oublier de positionner delimiter en bas de la fenêtre


###### Procédure diapo 18 ###### 

DELIMITER ;; ## définir un séparateur pour indiquer une fin de procédure

DROP PROCEDURE IF EXISTS ajout_emp;;  ## sans ()

CREATE PROCEDURE ajout_emp()
BEGIN
    DECLARE vNoEmp Int;
    SET vNoEmp = 1 ;
    maboucle : LOOP
        INSERT INTO EMP(EMPNO, ENAME) VALUES (vNoEmp, 'Oracle');
        SET vNoEmp = vNoEmp +1 ;
        IF vNoEmp > 10 THEN
            LEAVE maboucle ;
        END IF;
    END LOOP maboucle ;
END ;;


DELIMITER ;   ## on peut mettre ; car pas d'ambiguité
SELECT * from EMP ;  ## Visualisation avant ; Attention à la casse 
CALL ajout_emp();  ## Exécution de la procédure
SELECT * from EMP ;  ## Visualisation du résultat 

DELETE FROM EMP WHERE ENAME = "Oracle" ; ## rétablir l'état initial

###### Paramètres diapo 28 ###### 
/*

DELIMITER ;;
DROP PROCEDURE IF EXISTS build_job_list ;;

## NB : bien supprimer les lignes avec "Oracle" avant chaque exécution de la procédure sinon résultat erroné

## paramètres INOUT enrée-sortie ; IN entrée ; OUT sortie
CREATE PROCEDURE build_job_list(INOUT job_list varchar(4000))
BEGIN
    DECLARE v_finished INTEGER DEFAULT 0;  ## chaque variable déclarée à part
    DECLARE v_job varchar(100) DEFAULT "";

   ## declare cursor for EMP job
    DEClARE job_cursor CURSOR FOR
        SELECT distinct JOB FROM EMP;
    
    ## declare NOT FOUND handler : gestion d'erreur
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1; ## variable d'exception
    OPEN job_cursor;  ## nécessaire lorsqu'une requête renvoie plusieurs tuples
    get_job: LOOP
        FETCH job_cursor INTO v_job;
        IF v_finished = 1 THEN  ## Exception NOT FOUND : fin du curseur
            LEAVE get_job;
        END IF;
        -- build job list
        SET job_list = CONCAT(v_job,";",job_list);  ## modification du paramètre
    END LOOP get_job;
    CLOSE job_cursor;
END;;

DELIMITER ;;   ## on peut mettre ; car pas d'ambiguité
SET @job_list_res = "";;  ## paramètre de d'entrée-sortie
CALL build_job_list(@job_list_res);;
SELECT @job_list_res;;

*/

###### Fonction pas dans le cours ###### 

DELIMITER ;;

DROP FUNCTION IF EXISTS count_emp ;;

CREATE FUNCTION count_emp(position VARCHAR(255))
RETURNS INTEGER
BEGIN
    DECLARE nba INTEGER ;
    SELECT count(JOB) INTO nba
    FROM EMP
    WHERE JOB LIKE position;
    RETURN (nba) ;
END;;

DELIMITER ;
SELECT count_emp('ANALYST');

###### Déclencheur (Trigger) ###### 

DELIMITER ;;

DROP TRIGGER IF EXISTS change_job ;;

CREATE TRIGGER change_job
BEFORE UPDATE on EMP
FOR EACH ROW
BEGIN
    IF NEW.job <> OLD.job THEN
        SET NEW.sal = OLD.sal+500;
    END IF;
END ;;

### Pour activer et désactiver le déclencheur

DELIMITER ;;

DROP TRIGGER IF EXISTS change_job_bis ;;

CREATE TRIGGER change_job_bis
BEFORE UPDATE on EMP
FOR EACH ROW
BEGIN
IF @emp_trigger IS NULL OR @emp_trigger = 1 THEN
    IF NEW.job <> OLD.job THEN
        SET NEW.sal = OLD.sal+500;
    END IF;
END IF;    
END ;;


DELIMITER ;

SELECT *
FROM EMP
WHERE ENAME LIKE "SCOTT";

UPDATE EMP SET JOB = "ANALYST"
WHERE ENAME LIKE "SCOTT";

SELECT *
FROM EMP
WHERE ENAME LIKE "SCOTT";

UPDATE EMP SET JOB = "MANAGER"
WHERE ENAME LIKE "SCOTT";

SELECT *
FROM EMP
WHERE ENAME LIKE "SCOTT";

### utiliser la variable globale de change_job_bis si l'on veut désactiver le trigger

SET @emp_trigger = 0 ;

UPDATE EMP SET JOB = "MANAGER", SAL=SAL-1500
WHERE ENAME LIKE "SCOTT";

SELECT *
FROM EMP
WHERE ENAME LIKE "SCOTT";