La programmation

Syntaxe générale

Un programme peut se décomposer en trois parties :

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; 

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

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

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; 

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...

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

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> 
    ...

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]  ...

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) ...