PL/SQL est un nouveau langage de programmation fourni avec le SGBD Oracle à partir de la version 6. L'objectif de PL/SQL est double. D'une part améliorer les performances d'Oracle (notamment en transactionnel) en permettant une interface par blocs d'instructions SQL (et non pas une seule instruction) entre le noyau du SGBD et les outils Oracle (sqlplus, sqlforms et pro* notamment). D'autre part fournir un meilleur outil de programmation avec une bonne intégration entre SQL et le langage PL/SQL (le système de types est très proche).
Ce document décrit juste les principes de PL/SQL, le détail de la syntaxe des commandes PL/SQL est donné ici.
1- Principes de PL/SQL :
PL/SQL définit des blocs de commandes SQL associé à un mini-langage de programmation impératif style Pascal. Un bloc de commandes se définit comme suit :
DECLARE
déclarations de variables
déclarations de curseurs
déclarations d'exceptions
BEGIN
corps du bloc PL/SQL
[EXCEPTION]
[gestionnaire d'exceptions]
END;
Un bloc comprend donc une partie déclarations (notamment des variables utilisées dans le corps du bloc), un corps et éventuellement un gestionnaire d'exceptions qui permet de gérer les exceptions déclenchées dans le corps du bloc.
En plus des variables, la partie déclaration comprend la déclaration des curseurs utilisés dans le bloc (on rappelle qu'un curseur est une structure séquentielle qui permet de traiter l'ensemble de n-uplets résultat d'une requête), ainsi que la déclaration d'exceptions.
Le corps d'un bloc se compose des commandes SQL standard (select, insert, delete, update, commit, rollback, ...) ainsi que d'instructions de contrôle (if, while, loop, for) et l'affectation.
PL/SQL offre une gestion explicite des exceptions. Celles ci comprennent les exceptions pré-définies (division par zéro par exemple), mais aussi des exceptions définies par l'utilisateur. Un gestionnaire d'exception ("handler") permet d'associer un traitement à une exception. Les exceptions utilisateurs sont générées explicitement.
Il faut noter que l'imbrication de blocs est possible. Le corps d'un bloc peut contenir la définition d'autres blocs et récursivement. Les blocs sont imbriqués et les règles de portée sont classiques (on cherche la définition d'un objet d'abord dans le bloc où il est référencé, puis dans le bloc englobant et récursivement).
2- Déclarations de variables :
On doit déclarer toutes les variables utilisées dans les blocs PL/SQL et SQL. L'intérêt ici est que le modèle de type de PL/SQL est très proche de celui de SQL (à la différence de celui de C par rapport à SQL). On trouve donc les types suivants :
- CHAR(longueur)
- DATE
- NUMBER(p,q)
- BOOLEAN
Seul le type BOOLEAN n'est pas un type SQL. Deux autres "types" sont proposés %TYPE et %ROWTYPE. Ces deux types définissent des types en référence à des objets SQL :
- relation.attribut%TYPE, définit une variable du type de l'attribut de la relation citée. L'intérêt de cette définition est que le programmeur n'a pas à connaître le type de cet attribut et que ce type peut changer sans avoir à modifier le programme,
- relation%ROWTYPE, définit une variable nuplet du type du n-uplet de la relation citée. Les différents attributs de ce nuplet sont désignés par une notation pointée (nuplet.attribut).
On peut également, comme en Pascal, se définir des constantes.
Exemples :
nom char(20);
augmentation CONSTANT NUMBER(3,2) := 0.10;
nomcinema cinema.nomcine%TYPE;
tcinema cinema%ROWTYPE;
3- Instructions du bloc PL/SQL :
On retrouve les instructions classiques de langages de programmation.
- affectation : ":="
variable := constante / variable / expression
Les opérandes gauche et droit doivent être de types compatibles.
Exemples :
nomcinema := `gaumont';
nomcinema1 := nomcinema2;
recettenette := (nbplace * prixunitaire) - depense;
On peut utiliser les expressions arithmétiques, logiques (les opérateurs de comparaison sont définis sur les nombres, les chaines de caractères, les dates), sur les chaines (avec les opérateurs sur les chaines comme la concaténation).
- conditionnelle : "IF"
On retrouve des expressions conditionnelles classiques (IF THEN END IF; IF THEN ELSE ENDIF; IF THEN ELSIF ELSIF ... END IF;)
Exemples :
IF nomcinema = `Gaumont' THEN
traitement
END IF;
IF nomcinema = `Gaumont' THEN
traitement IF
ELSE
traitement ELSE
END IF;
IF nomcinema = `Gaumont' THEN
traitement 1
ELSIF nomcinema = `Royal' THEN
traitement 2
ELSIF nomcinema = `Nef' THEN
traitement 3
ELSE
traitement ELSE
END IF;
- itérations :
On trouve les itérations FOR, WHILE, LOOP.
FOR variable IN expr-arithmétique..expr-arithmétique LOOP
corps
END LOOP;
FOR variable IN curseur LOOP
corps
END LOOP;
WHILE condition LOOP
corps
END LOOP;
LOOP
corps
END LOOP;
L'instruction LOOP définit une boucle infinie dont on sort soit par une exception, soit par un EXIT explicite. L'EXIT s'utilise soit dans une conditionnelle, soit au moyen de la notation :
EXIT WHEN expression-logique
La sortie de la boucle s'effectue alors si l'expression logique est vraie.
4- Instructions SQL :
On peut utiliser dans un bloc tout le langage SQL. On retrouve principalement :
- SELECT ... INTO var1, ... FROM ... WHERE
- UPDATE, DELETE
- INSERT
- GRANT
- CREATE, DROP
- COMMIT, ROLLBACK, SAVEPOINTS
La gestion des commandes SQL dynamiques depuis PRO*C est possible. C'est tout le bloc PL/SQL qui est considéré comme dynamique au lieu d'une simple commande SQL (voir documentation PRO*C pour plus de détails).
La gestion d'une sélection renvoyant un ensemble de nuplets pose le même problème qu'en PRO*C et donc la solution du curseur se retrouve dans PL/SQL. La gestion du curseur est la même, mais sa manipulation est simplifiée.
Un curseur PL/SQL se déclare dans la partie déclaration du bloc :
CURSOR C1 is requête-SQL;
Les opérations de manipulation sont les suivantes :
- open nom-curseur;
- close nom-curseur;
- fetch nom-curseur into var1, ..., varn;
La nouveauté concerne le FOR sur un curseur qui permet d'itérer sur les nuplets d'un curseur :
FOR variable IN curseur LOOP
traitement
END LOOP;
De plus, quatre variables sont attachées à un curseur et permettent de connaître son statut :
- nom-curseur%NOTFOUND : vrai quand le curseur est vide,
- nom-curseur%FOUND : vrai quand le curseur n'est pas vide,
- nom-curseur%ROWCOUNT : nombre de n-uplets déjà lus,
- nom-curseur%ISOPEN : vrai si le curseur est ouvert.
Exemple :
DECLARE
tfilm film%ROWTYPE;
cursor C1 is select titre, annee, pays, realisateur
from film where annee > 1984;
BEGIN
open C1;
FOR tfilm IN C1 LOOP
traitement
END LOOP;
close C1;
commit;
END ;
Autre solution :
DECLARE
tfilm film%ROWTYPE;
cursor C1 is select titre, annee, pays, realisateur
from film where annee > 1984;
BEGIN
open C1;
LOOP
fetch C1 into tfilm;
exit when C1%NOTFOUND;
traitement
END LOOP;
close C1;
commit;
END ;
5- Gestion des exceptions :
La programmation par exceptions permet de mieux diviser la partie du code nécessaire pour traiter les situations exceptionnelles de celle nécessaire à traiter les cas généraux. C'est une technique assez puissante que l'on rencontre dans de nombreux langages de programmation récents. PL/SQL offre un outil de gestion des exceptions qui permet un support minimum de celles-ci.
PL/SQL prend en compte deux types d'exceptions. Les exceptions pré-définies (ou systèmes), par exemple la division par zéro, un curseur vide, ... Et les exceptions définies par le programmeur (dans la partie DECLARE du bloc). La définition a les mêmes règles de portée que celles des variables (une définition locale surcharge une définition globale).
La gestion des exceptions comprend trois parties :
- la déclaration de l'exception : consiste à nommer l'exception. Les exceptions pré-définies sont définies globalement mais le programmeur peut éventuellement les renommer. Les exceptions définies par le programmeur sont dans la partie DECLARE,
- le déclenchement de l'exception : les exceptions pré-définies sont déclenchées automatiquement ou explicitement par le programmeur, alors que les exceptions définies par le programmeur sont déclenchées explicitement. Le déclenchement explicite se fait au moyen de l'instruction RAISE nom-exception.
- le traitement de l'exception : les exceptions sont traitées dans le gestionnaire d'exceptions (partie EXCEPTION du bloc PL/SQL). A chaque nom d'exception on associe un traitement (séquence d'opérations PL/SQL). On dispose également du mot clé OTHERS qui permet de récupérer les autres exceptions.
Lorsqu'une exception est déclenchée dans un bloc, on cherche le traitement associé dans le bloc. S'il n'existe pas on va voir l'exception OTHERS et si elle n'est pas présente on cherche dans les blocs englobants (et récursivement). Le traitement d'une exception ne peut revenir dans le bloc qui l'a déclenchée, on revient toujours au bloc englobant. Si le traitement d'une exception déclenche une autre exception, elle est propagée dans le bloc englobant (et récursivement).
Exemple :
DECLARE
...
film-recent EXCEPTION;
...
BEGIN
FOR tfilm in C1 LOOP
IF annee > 1990 THEN
RAISE film-recent;
END IF;
...
END LOOP;
...
EXCEPTION
WHEN film-recent THEN
traitement film recent
WHEN OTHERS THEN
traitement des autres
END;
6- Liens avec PRO*C :
On peut utiliser un bloc PL/SQL à la place d'une commande SQL. L'insertion d'un bloc SQL se fait de la manière suivante :
INSERT SQL EXECUTE
bloc PL/SQL
END-EXEC ;
Les variables de communication entre PL/SQL et C doivent être déclarées dans la DECLARE SECTION et sont ensuite manipulées en PL/SQL préfixés par ":" (comme en PRO*C).
Attention, une variable définie comme VARCHAR doit être obligatoirement initialisée avant d'entrer dans un bloc PL/SQL (sa longueur doit être initialisée, y compris à 0 si elle n'a pas été affectée).
De manière générale, on a intérêt dans un programme PRO*C à faire le maximum de traitements en PL/SQL et le reste en C. PL/SQL est très adapté pour les manipulations SQL et notamment pour la gestion des curseurs (le typage est aussi beaucoup plus homogène qu'avec C). Par contre, aucune interaction avec l'utilisateur n'est possible, par conséquent cela doit être fait en C.
Exemple de programme PRO*C avec utilisation de bloc PL/SQL :
/*******************************************************
* DEMO PL/SQL et PRO/C TRANSACTION DEBIT COMPTE *
* *
* Ce programme demande un numéro de compte et un montant à *
* débiter sur ce compte. Il vérifie que le numéro de compte est *
* valide et qu'il y a suffisamment d'argent pour entreprendre le débit *
* Si oui le débit est effectué *
* *
* Copyright (c) 1989 by Oracle Corporation. *
*******************************************************/
#include <stdio.h>
char buffer[20];
EXEC SQL BEGIN DECLARE SECTION;
int acct; /* numéro de compte à débiter. */
double debit; /* montant à débiter */
double new_bal; /* nouveau solde après transaction */
varchar status[65]; /* résultat de la transaction. */
varchar uid[20];
varchar pwd[20];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
main()
{
extern double atof();
/*connection à ORACLE comme plsqa/supersecret */
strcpy (uid.arr,"plsqa");
uid.len=strlen(uid.arr);
strcpy (pwd.arr,"supersecret");
pwd.len=strlen(pwd.arr);
printf("\n\n\tPL/SQL - PROC Débit Transaction Demo\n\n");
printf("Essai de connection...");
EXEC SQL WHENEVER SQLERROR GOTO errprint;
EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;
printf(" connecte.\n");
for (;;) /* boucle infinie */
{
printf("\n** Numero de compte a debiter? (-1 pour sortir) ");
gets(buffer);
acct = atoi(buffer);
if (acct == -1) /* sortie du programme */
{
EXEC SQL COMMIT WORK RELEASE; /* fin de transaction */
exit(0); /* sortie du programme */
}
printf(" Montant a debiter? ");
gets(buffer);
debit = atof(buffer);
/* ------------------------------------------ */
/* ----- Debut du bloc PL/SQL --------- */
/* ------------------------------------------ */
EXEC SQL EXECUTE
DECLARE
insufficient_funds EXCEPTION;
old_bal NUMBER;
min_bal CONSTANT NUMBER := 500;
BEGIN
SELECT bal INTO old_bal FROM accounts
WHERE account_id = :acct;
-- si le compte n'existe pas , la NO_DATA_FOUND
-- exception est générée automatiquement
:new_bal := old_bal - :debit;
IF :new_bal >= min_bal THEN
UPDATE accounts SET bal = :new_bal WHERE account_id = :acct;
INSERT INTO journal VALUES (:acct, 'Debit', :debit, sysdate);
:status := 'Transaction reussie.';
ELSE
RAISE insufficient_funds;
END IF;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
:status := 'compte inexistant.';
:new_bal := -1;
WHEN insufficient_funds THEN
:status := 'fonds insuffisants. Solde ne peut etre inferieur a $500.';
:new_bal := old_bal;
WHEN OTHERS THEN
ROLLBACK;
:status := 'Error: ' || SQLERRM; -- recherche du msg erreur complet
:new_bal := -1;
END;
END-EXEC;
/* ------------------------------------------ */
/* ----- Fin du bloc PL/SQL ----------- */
/* ------------------------------------------ */
status.arr[status.len] = '\0';
printf("\n\n Status: %s\n", status.arr);
if (new_bal >= 0)
printf(" nouveau solde: $%.2f\n", new_bal);
} /* End of loop */
errprint:
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\n\n>>>>> Erreur a l'execution:\n");
printf("%s\n",sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK RELEASE;
exit(1);
}