Blog ENI : Toute la veille numérique !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
  1. Livres et vidéos
  2. SQL
  3. La programmation
Extrait - SQL Les fondamentaux du langage (avec exercices et corrigés) - (5e édition)
Extraits du livre
SQL Les fondamentaux du langage (avec exercices et corrigés) - (5e édition)
1 avis
Revenir à la page d'achat du livre

La programmation

Introduction

La programmation permet de créer des procédures stockées, des fonctions et des déclencheurs ou même de réaliser des applications plus ou moins complexes.

Oracle a créé son propre langage structuré : le PL/SQL. Il permet d’associer des ordres SQL avec des commandes d’un langage procédural.

Les éléments créés en PL/SQL doivent être compilés avant d’être exécutés.

Toutes les instructions SQL sont utilisables dans un bloc PL/SQL. Un « bloc » est un morceau de code PL/SQL, équivalent à une fonction ou une procédure dans un autre langage.

PostgreSQL propose le PL/pgSQL, SQL Server, le Transact SQL.

Syntaxe générale

Un programme peut se décomposer en trois parties :

  • une partie déclarative,

  • une partie traitement,

  • une partie gestion des erreurs.

La partie déclarative permet de déclarer et d’initialiser toutes les variables utilisées dans la partie traitement. Dans un programme PL/SQL, on peut utiliser les types Oracle pour les variables mais également créer ses propres types.

La partie gestion des erreurs permet d’indiquer les instructions à appliquer lorsqu’une erreur est rencontrée dans la partie traitement.

Ces deux sections (déclarative et erreur) sont facultatives.

La syntaxe d’un programme est la suivante :

[DECLARE 
...] 
BEGIN 
... 
... 
[EXCEPTION 
...] 
END; 

Dans MySQL et PostgreSQL, ces blocs ne peuvent être utilisés seuls. Ils doivent être inclus dans une fonction ou un déclencheur, et pour MySQL une procédure.

Exemple de script SQL Server à exécuter

DECLARE @Hotel int 
BEGIN 
SET @Hotel = 2 
SELECT NumChambre, Description 
FROM Chambres INNER JOIN TypesChambre ON TypesChambre.idTypeChambre = 
Chambres.TypeChambre 
WHERE Hotel = @Hotel; 
END; 

Résultat

NumChambre

Description

1

1 lit simple avec douche

2

2 lits simples avec douche

3

3 lits simples avec douche et WC séparés

4

1 lit double avec douche

5

1 lit double avec douche et WC séparés

6

1 lit double avec bain et WC séparés

7

1 lit double large...

Les curseurs

Un curseur est un élément qui permet de stocker le résultat d’une requête contenant plusieurs lignes. Cela permet de parcourir chaque ligne pour les utiliser.

Il faut le déclarer dans la section déclarative.

Il faut l’ouvrir par OPEN, l’exécuter par FETCH et le fermer par CLOSE.

Dans l’exemple, le type de lit recherché est passé en paramètre au curseur : CURSOR C_chambres_par_type_lit (TypLit IN VARCHAR2) IS.

TypLit est renseigné lors de l’OPEN CURSOR avec la variable qui contient le libellé du type de lit : OPEN C_chambres_par_type_litC(TypLit_recherche).

Exemple avec la même requête que précédemment :

DECLARE 
-- Déclaration du curseur C_chambres_par_type_lit 
CURSOR C_chambres_par_type_lit (TypLit in varchar2) IS 
SELECT Hotels.Libelle, Chambres.NumChambre, TypesChambre.NombreLit,  
TypesChambre.Description 
FROM Chambres INNER JOIN 
Hotels ON Chambres.Hotel = Hotels.idHotel INNER JOIN 
TypesChambre ON Chambres.TypeChambre = TypesChambre.idTypeChambre 
WHERE TypeLit = typlit and Etoile = '**'; 
 
-- Déclaration des variables réceptrices 
Libelle_hotel varchar2(50); 
Num_Chambre varchar2(6); 
NbLit number(38,0); 
Descript varchar2(255); 
 
-- Déclaration des autres variables 
TypLit_recherche varchar2(20)...

Le contrôle des flux

1. La boucle WHILE

Le WHILE permet de répéter un bout de code tant que la condition testée au début est vraie. Si la condition est fausse, on sort directement de la boucle sans exécuter le code.

Exemple Oracle

DECLARE  
-- Déclaration du curseur C_chambres_par_type_lit  
CURSOR C_chambres_par_type_lit (TypLit in varchar2) IS  
SELECT Chambres.idChambre, Hotels.Libelle, Chambres.NumChambre, 
TypesChambre.NombreLit, TypesChambre.Description  
FROM Chambres INNER JOIN  
Hotels ON Chambres.Hotel = Hotels.idHotel INNER JOIN  
TypesChambre ON Chambres.TypeChambre = TypesChambre.idTypeChambre 
WHERE TypeLit = typlit and Etoile = '**'; 
 
-- Déclaration des variables réceptrices 
id_chambre number :=0; 
Libelle_hotel varchar2(50); 
Num_Chambre varchar2(6); 
NbLit number(38,0); 
Descript varchar2(255); 
 
-- Déclaration des autres variables 
TypLit_recherche varchar2(20) := 'lit simple'; 
 
BEGIN 
-- Ouverture 
OPEN C_chambres_par_type_lit(typlit_recherche); 
-- Lecture du premier élément 
FETCH C_chambres_par_type_lit  
INTO id_chambre, Libelle_hotel, Num_Chambre, NbLit, descript; 
-- Boucle de lecture tant que l'identifiant de la chambre est < 10 
WHILE id_chambre < 10 
LOOP 
-- Affichage des éléments récupérés 
DBMS_OUTPUT.PUT_LINE('Id Chambre : '||id_chambre); 
DBMS_OUTPUT.PUT_LINE('Nom de l''hôtel : '||Libelle_hotel); 
DBMS_OUTPUT.PUT_LINE('Numéro de chambre : '||num_chambre); 
DBMS_OUTPUT.PUT_LINE('Nombre de lits : '||nblit); 
DBMS_OUTPUT.PUT_LINE('Type de lit : '||typlit_recherche); 
DBMS_OUTPUT.PUT_LINE('Description : '||descript); 
-- Lecture de l'élément suivant 
FETCH C_chambres_par_type_lit 
INTO id_chambre, Libelle_hotel, Num_Chambre, NbLit, descript; 
EXIT WHEN C_chambres_par_type_lit%NOTFOUND; 
END LOOP; 
-- Fermeture du curseur (libération mémoire) 
CLOSE C_chambres_par_type_lit; 
END; 

Résultat

Id Chambre : 8 
Nom de l'hôtel : Art Hotel 
Numéro de chambre : 1 
Nombre de lits : 1 
Type de lit : lit simple 
Description...

Les exceptions Oracle les plus utilisées

En dehors de l’exception « NOT_DATA_FOUND » que nous avons vue dans les exemples précédents, il existe une multitude d’autres exceptions. Nous n’allons pas les citer toutes dans ce livre mais en voici quelques-unes qui peuvent être utiles.

CURSOR_ALREADY_OPEN : le curseur est déjà ouvert. Il faut le fermer avant de le réouvrir (SQLCODE --> 06511)

INVALID_NUMBER : la variable utilisée ne contient pas un numéro valide (SQLCODE --> 01722)

NOT_LOGGED_ON : l’utilisateur n’est pas connecté à la base de données (SQLCODE --> 01012)

TOO_MANY_ROWS : la sélection ramène plusieurs lignes alors que le select ne prévoit qu’une seule occurrence, faire un curseur (SQLCODE --> 01422)

ZERO_DIVIDE : division par zéro (SQLCODE --> 01476)

Pour traiter tout type d’erreur, il est préférable d’ajouter systématiquement un test de ce type afin d’afficher l’erreur au moindre problème.

WHEN OTHERS THEN 
    DBMS_OUTPUT.PUT_LINE( 'Le numéro de l erreur est : '|| 
TO_CHAR( SQLCODE )) ; 
    DBMS_OUTPUT.PUT_LINE( 'correspondant à : '|| 
TO_CHAR( SQLERRM )) ; 

La gestion des erreurs en Transact SQL

La gestion des erreurs permet d’anticiper les problèmes qui peuvent se dérouler au cours de l’exécution d’un programme.

Le principe est de tester le code dans un premier bloc avec BEGIN TRY … END TRY puis d’intercepter l’exception avec BEGIN CATCH … END CATCH.

Syntaxe

BEGIN  
  
... ...  
  
[BEGIN TRY  
... ...  
END TRY]  
[BEGIN CATCH  
... ...  
END CATCH]  
END; 

Exemple

DECLARE @i int 
BEGIN 
BEGIN TRY 
SET @i = 2 
SET @i = @i / 0 
END TRY 
BEGIN CATCH 
SELECT ERROR_NUMBER() AS ErrorNumber 
    , ERROR_MESSAGE() AS ErrorMessage 
    , ERROR_LINE() AS ErrorLine; 
END CATCH 
END; 

Résultat

ErrorNumber

ErrorMessage

ErrorLine

8134

Division par zéro.

5

Il est aussi possible de traiter l’exception.

DECLARE @i int 
BEGIN 
BEGIN TRY 
SET @i = 2 
SET @i = @i / 0 
END TRY 
BEGIN CATCH 
    IF @@ERROR = 8134 
        SET @i = @i / 1 
print @i 
END CATCH 
END; 

Résultat

2

Il est possible de lever une erreur grâce à la fonction RAISERROR(). Cette fonction accepte trois arguments (constante ou message ou variable, numéro de la gravité...

Création d’une procédure stockée

Lorsque l’on veut partager un morceau de code réalisé en PL/SQL, on peut l’enregistrer dans la base et ainsi le rendre accessible aux autres développeurs. Une procédure stockée est un bloc de code compilé et stocké par la base de données. Il suffit de l’appeler par son nom pour l’exécuter.

Le principal avantage de la procédure stockée, c’est qu’elle est enregistrée dans un format « exécutable », le serveur de base de données ne va pas interpréter les commandes lors de l’appel mais l’exécuter directement, d’où un gain de temps non négligeable par rapport au lancement multiple de la même requête dans un programme.

Un autre avantage de la procédure stockée est que l’on peut lui passer des paramètres. 

Syntaxe Oracle

CREATE OR REPLACE PROCEDURE <nom procédure> 
 [(<variable entrée 1> IN <format>, 
   <variable entrée 2> IN <format>, 
   ... ... 
   <variable sortie> OUT <format>)] 
 IS 
 
BEGIN 
 
... ... 
 
[EXCEPTION 
... ... 
] 
END; 

Syntaxe SQL Server

CREATE OR ALTER PROCEDURE <nom procédure>  
 [(@<variable 1> <format>,  ...

Création d’une fonction stockée

Dans le même exemple, il est également possible de créer une fonction à la place de la procédure. La différence entre une fonction et une procédure est que la première renvoie une valeur. Il est possible de l’inclure dans une requête.

Syntaxe Oracle

CREATE OR ALTER FUNCTION <nom fonction>  
[(<variable entrée 1> IN <format>,  
   <variable entrée 2> IN <format>,  
   ... ... ]  
   RETURN <format>  
 IS  
<variable sortie> <format>)]  
BEGIN  
  
... ...  
  
[EXCEPTION  
... ...  
]  
END; 

Syntaxe SQL Server

CREATE OR ALTER FUNCTION <nom fonction>  
 [(@<variable 1> <format>,  
  @<variable 2> <format>,  
   ... ... )]  
   RETURNS <format>  
 AS  
 
BEGIN  
  
... ...  
   
END; 

Par exemple, la fonction PL/SQL suivante ramène le prix de la chambre à partir du nom de l’hôtel, du type et nombre de lits et d’une date.

CREATE OR REPLACE FUNCTION PRIX_CHAMBRE  
(vhotel IN VARCHAR2, vtypelit...

Les packages

L’appellation « package » signifie que l’on regroupe sous un même nom toutes les procédures et fonctions sur le même thème, on peut ainsi créer de véritables applications.

Dans un package, on peut avoir des déclarations de variables publiques ou privées ainsi que des fonctions et procédures privées non visibles de l’extérieur.

Dans un package, il faut créer une partie déclaration et une partie contenant les fonctions et procédures.

Dans la partie déclaration, on liste les procédures et fonctions qui sont décrites dans l’autre partie. Toutes les fonctions ou procédures qui sont déclarées à ce niveau sont dites « publiques ». Pour les variables, c’est le même fonctionnement, si elles sont dans la partie déclaration, elles sont « publiques ».

Les packages n’existent pas pour SQL Server.

Syntaxe

CREATE OR REPLACE PACKAGE <nom package> IS 
      PROCEDURE <nom procédure 1>; 
      FUNCTION <nom fonction 1> (<variabl 1> IN <format>) RETURN 
<format>; END; 
/ 
CREATE OR REPLACE PACKAGE BODY <nom package> IS 
 
   FUNCTION <fonction 1> 
    ...

Compilation d’une procédure, d’une fonction ou d’un package

Après avoir créé ou modifié une procédure, une fonction ou un package, il est nécessaire de compiler le code pour pouvoir l’appeler.

Syntaxe

ALTER <'PROCEDURE' ou 'FUNCTION' ou 'PACKAGE'> <Nom procédure ou 
fonction ou package> COMPILE; 

Exemple

ALTER FUNCTION PRIX_CHAMBRE COMPILE;  
ALTER PROCEDURE LISTE_CHAMBRE_HOTEL COMPILE;  
ALTER PACKAGE AFFICHAGE_HOTEL COMPILE PACKAGE;  
ALTER PACKAGE AFFICHAGE_HOTEL COMPILE BODY;  
- compile body et package  
ALTER PACKAGE AFFICHAGE_HOTEL COMPILE; 

Suppression d’une procédure, d’une fonction ou d’un package

Lorsqu’un code est obsolète, il est conseillé de le supprimer avec la syntaxe suivante.

Syntaxe

DROP <'PROCEDURE' ou 'FUNCTION' ou 'PACKAGE'> <Nom procédure ou 
fonction ou package>; 

Exemple

DROP FUNCTION PRIX_CHAMBRE;  
DROP PROCEDURE LISTE_CHAMBRE;  
- suppression de tout le package (corps et déclaration)  
DROP PACKAGE AFFICHAGE_HOTEL;  
- suppression corps de package  
DROP PACKAGE BODY AFFICHAGE_HOTEL; 

Les déclencheurs

Un déclencheur ou trigger en anglais permet de lancer des commandes qui vont s’exécuter à chaque fois qu’un événement se produit sur une table.

Le contenu du code lancé par un trigger est souvent du PL/SQL ou du C ou du Java.

Les déclencheurs sont souvent utilisés pour gérer l’intégrité fonctionnelle d’une application. Ils permettent de réaliser des contrôles sur le contenu des tables en automatique.

Les déclencheurs peuvent également servir à récupérer des informations tout au long d’une journée sur les activités de la base de données, ces données étant traitées ensuite par une autre application.

En général, on code les contrôles dans les programmes applicatifs exécutés côté client. Les déclencheurs permettent d’ajouter d’autres contrôles qui seront exécutés côté serveur.

L’avantage premier du déclencheur est qu’il est lié à une action sur la base (INSERT, UPDATE, DELETE), donc on ne risque pas d’oublier de modifier un programme. En effet, il est souvent compliqué de modifier tous les programmes d’un applicatif pour ajouter un contrôle sur un INSERT par exemple. Il faudra retrouver tous les programmes concernés, les modifier, et tester chacun des programmes modifiés.

Le déclencheur se déclenchant systématiquement, on ne peut pas oublier une mise à jour, et la modification se fait indépendamment des programmes applicatifs.

Un déclencheur peut se déclencher avant ou après l’ordre SQL demandé. On l’indique par AFTER ou BEFORE. Dans SQL Server, il s’exécute après ou à la place (INSTEAD OFF) mais pas avant.

Dans un trigger BEFORE, on peut contrôler avant toute modification de la base certains éléments et empêcher ainsi les mises à jour.

Dans un trigger AFTER, la mise à jour a eu lieu et on déclenche les actions qui en découlent.

Syntaxe générale d’un déclencheur PL/SQL

CREATE OR REPLACE TRIGGER <nom du trigger>  
[AFTER] [INSERT ou DELETE ou UPDATE]  ...

Exercices

Premier exercice

Créer une fonction qui calcule l’âge d’un acteur.

Deuxième exercice

Pour réaliser l’exercice suivant, créer une procédure stockée qui ajoute un nouveau pays avec le libellé ’A COMPLETER’.

Troisième exercice

Créer un déclencheur qui, lors de la création d’un acteur, vérifie l’existence de la nationalité. Si celle-ci est inconnue, le code est créé avec le libellé « A COMPLETER ». 

Solutions des exercices

Premier exercice

Syntaxe Oracle 
CREATE OR REPLACE FUNCTION CALCUL_AGE_ACTEUR  
(DATE_NAISSANCE IN DATE) RETURN NUMBER  
IS   
AGE_ACTEUR NUMBER(5);  
 
BEGIN  
 
SELECT (SYSDATE - DATE_NAISSANCE)/365 INTO AGE_ACTEUR FROM DUAL;  
RETURN  (AGE_ACTEUR);  
 
EXCEPTION  
  WHEN OTHERS THEN   
    DBMS_OUTPUT.PUT_LINE( 'Le numéro de l erreur est : '||  
TO_CHAR( SQLCODE )) ;  
    DBMS_OUTPUT.PUT_LINE( 'correspondant à : '||  
TO_CHAR( SQLERRM )) ;  
END; 
SELECT CALCUL_AGE_ACTEUR(date_naissance) FROM ACTEUR; 
 
Syntaxe SQL Server 
CREATE OR ALTER FUNCTION CALCUL_AGE_ACTEUR (@dateNaissance date) 
RETURNS integer 
AS 
BEGIN 
DECLARE @Age integer 
SELECT @Age= DATEDIFF(YEAR, @dateNaissance, GETDATE()); 
RETURN @Age 
END; 
 
SELECT dbo.CALCUL_AGE_ACTEUR(DATE_NAISSANCE) FROM ACTEUR; 
 
Syntaxe MySQL 
CREATE FUNCTION CALCUL_AGE_ACTEUR (dateNaissance DATE) 
RETURNS INT DETERMINISTIC 
BEGIN 
DECLARE vAge INT; 
SELECT ROUND(DATEDIFF(CURRENT_DATE, dateNaissance) / 365) 
INTO vAge FROM DUAL; 
RETURN vAge; 
END 
 
SELECT CALCUL_AGE_ACTEUR(DATE_NAISSANCE) FROM ACTEUR; 
 
Syntaxe PostgreSQL 
CREATE OR REPLACE FUNCTION public."CALCUL_AGE_ACTEUR" 
(IN "dateNaissance" date) ...