Cours SQL ORACLE – 07-06 : PROCEDURE

Les procédures stockées (PROCEDURE) sont des blocs PL/SQL qui son stockés dans la base de donnée ORACLE avec un nom unique. Ces procédures comportent un programme qu’on peut l’appeler dans une autre procédure, d’une fonction, trigger ou d’un programme externe qui a accès à notre Base de données.

La structure d’une PROCEDURE est comme suit:

CREATE [OR REPLACE] PROCEDURE <NOM_PROCEDURE>([VAR1 IN <TYPE_VAR1>],
                    [VAR1 IN <TYPE_VAR1>],
                    ....

                    [VARN OUT <TYPE_VARN>]) AS

[Déclaration variables à utiliser]

BEGIN

   [Corps du programme]

END;
/

Pour déclarer une PROCEDURE:

• CREATE : Créé la procédure stockée <NOM_PROCEDURE>
• OR REPLACE : Facultatif mais vaut mieux l’utiliser. Cela permet de recréer la PROCEDURE <NOM_PROCEDURE> si elle existe déjà.
• PROCEDURE : Mot clé pour définir qu’il s’agit d’une procédure stockée.
• <NOM_PROCEDURE> : Le nom de la PROCEDURE à créer.
• ([VAR1 IN <TYPE_VAR1>], .., [VARN OUT <TYPE_VARN>]) : Les paramètres de notre PROCEDURE, NOM_VARIABLE,  IN ou OUT ou IN OUT et le type du paramètre (VARCHAR2, NUMBER, DATE, etc…).
  • IN : paramètre en entrée.
  • OUT : paramètre en sortie.
  • IN OUT : paramètre en entrée et en sortie.
  • Les paramètres sont facultatifs. Nous pouvons déclarer une PROCEDURE sans paramètres d’entrées ou de sorties.
• AS : Mot clé pour déclarer le début de la PROCEDURE.
• Variables à utiliser : Déclaration des variables qu’on va utiliser dans notre PROCEDURE.
• BEGIN : Début du bloc PL/SQL de la PROCEDURE.
• Corps du programme : Bloc PL/SQL de la PROCEDURE.
• END; : Fin de notre PROCEDURE.
• / : Permet de créer la PROCEDURE.

On commence par un exemple simple de PROCEDURE qui va nous afficher un message “Bonjour Cours SQL.”:

CREATE OR REPLACE PROCEDURE BONJOUR_COURSSQL AS
BEGIN
   DBMS_OUTPUT.PUT_LINE('Bonjour Cours SQL');
END;
/

La sortie de ce code est :

PROCEDURE BONJOUR_COURSSQL Compilé.

Vous pouvez voir que cette procédure a été créé parmi les objets PROCEDURE de votre schéma.

Pour faire appel à cette PROCEDURE, nous allons utiliser le bloc PL/SQL suivant:

DECLARE
BEGIN
   BONJOUR_COURSSQL;
END;

La sortie de ce code est :

Bonjour Cours SQL

Nous allons créer une nouvelle PROCEDURE qui vérifie si le PARAM1 en entrée est un code EMP dans notre table EMP du Schéma SCOTT. Si c’est le cas, la procédure affichera un message en disant que c’est un employé sinon elle affichera un message en disant que ce n’est pas le cas.

CREATE OR REPLACE PROCEDURE VERIF_EMP (PARAM1 IN EMP.EMPNO%TYPE) AS
   I NUMBER;
BEGIN
   SELECT 1 INTO I FROM EMP WHERE EMPNO = PARAM1;
   DBMS_OUTPUT.PUT_LINE(PARAM1 || ' existe bien parmi nos employés');
EXCEPTION WHEN NO_DATA_FOUND THEN
   DBMS_OUTPUT.PUT_LINE(PARAM1 || ' n''existe pas parmi nos employés');
END;
/

On lance notre PROCEDURE avec le paramétré 7788 et 9999:

DECLARE
BEGIN
   VERIF_EMP(7788);
   VERIF_EMP(9999);

END;

Le résultat est :

7788 existe bien parmi nos employés
9999 n'existe pas parmi nos employés

Dans ce nouveau exemple, nous allons créer une PROCEDURE qui compare deux paramètres en entrées de type VARCHAR2 sans tenir compte de la casse est retourne le résultat dans un troisième paramètre sous forme de VRAI ou FAUX:

CREATE OR REPLACE PROCEDURE COMPARE_CHAINE (PARAM1 IN VARCHAR2,
                                            PARAM2 IN VARCHAR2,
                                            RESULTAT OUT VARCHAR2) AS
BEGIN
   RESULTAT := 'FAUX';
   IF UPPER(PARAM1) = UPPER(PARAM2) THEN
      RESULTAT := 'VRAI';
   END IF;
END;
/

On lance notre PROCEDURE avec le paramétré “Bonjour COURS SQL” et “Bonjour Cours Sql”:

DECLARE
   CHAINE1 VARCHAR2(200);
   CHAINE2 VARCHAR2(200);
   RESULT VARCHAR2(200);
BEGIN
   CHAINE1 := 'Bonjour COURS SQL';
   CHAINE2 := 'Bonjour Cours Sql';
   RESULT := NULL;

   COMPARE_CHAINE(CHAINE1, CHAINE2, RESULT);

   DBMS_OUTPUT.PUT_LINE('Le résultat est ' || RESULT);
END;

Le résultat est :

Le résultat est VRAI

Pour finir ce cours, nous allons utiliser un seul paramètre qui est en entrée et en sortie. Dans cet exemple, nous allons créer une PROCEDURE qui transforme le paramètre en entrée en majuscule et enlève les espaces qui existent dans ce paramètre:

CREATE OR REPLACE PROCEDURE MAJUSCULE_SUPR_ESPACE_CHAINE (PARAM1 IN OUT VARCHAR2) AS
BEGIN
   PARAM1 := UPPER(PARAM1);
   PARAM1 := REPLACE(PARAM1, ' ', '');

END;
/

On lance notre PROCEDURE avec le paramétré ”  Bo njour Cours Sq l   “:

DECLARE
   CHAINE1 VARCHAR2(200);
BEGIN
   CHAINE1 := '  Bo njour Cours Sq l   ';
   DBMS_OUTPUT.PUT_LINE('CHAINE1 avant transformation : ' || CHAINE1);

   MAJUSCULE_SUPR_ESPACE_CHAINE(CHAINE1);
   DBMS_OUTPUT.PUT_LINE('CHAINE1 après transformation : ' || CHAINE1);
END;

Le résultat est :

CHAINE1 avant transformation :   Bo njour Cours Sq l   
CHAINE1 après transformation : BONJOURCOURSSQL

Dans notre prochain cours nous allons voir les fonctions (FUNCTION) définies par l’utilisateur.