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);
}
Posté le 1 septembre 2009