Memento Oracle PL/SQL

Sommaire

Présentation ...........................................................................1 Les Packages .........................................................................8

L’utilisation de PL/SQL ...............................................1 Déclaration de Package.....................................................8
Structure d’un bloc PL/SQL ........................................1 Déclaration du corps de package .....................................8
La déclaration des variables .................................................1 Appel des procédures/fonctions d’un package................8
Les types de données ........................................................1 Initialisation d’un package ...............................................9
Définition d’une variable %type..................................1 Suppression d’un package ................................................9
Définition d’une variable %rowtype ...........................1 La surcharge des sous-programmes .................................9
Déclaration d’une variable composée RECORD .......1 Les déclencheurs( triggers)................................................ 10
Déclaration d’une variable de type TABLE ...............2 L'utilisation des déclencheurs ................................... 10
La déclaration des variables en PL/SQL .........................2 La création d'un déclencheur......................................... 10
Initialisation des variables............................................2 Les événements déclencheurs ................................... 10
L’affectation de valeur aux variables ..........................2 La clause REFERENCING ....................................... 10
Les structures de contrôle .....................................................2 Les actions déclenchées................................................. 10
Instruction IF .....................................................................2 Quand exécuter les actions déclenchées................... 10
Instruction LOOP..............................................................2 Déclencheurs de niveaux instruction et ligne........... 11
Instruction WHILE …. LOOP .........................................2 La condition WHEN .................................................. 11
Instruction FOR …. LOOP ..............................................2 Suppression, activation d’un déclencheur .................... 11
Instructions SQL dans un programme PL/SQL ..............3
L’utilisation de PL/SQL sous SQL*PLUS..........................3
La mise en œuvre de PL/SQL ..........................................3
Les entrées-sorties sous SQL*PLUS ...............................3
Saisie de données ..........................................................3
Affichage des résultats .................................................3
Les commentaires dans PL/SQL......................................3
Instruction NULL .............................................................3
Les Curseurs ..........................................................................3
Déclaration du curseur......................................................3
Ouverture du curseur ........................................................3
Recherche des lignes du curseur ......................................3
Fermeture du curseur ........................................................4
Les attributs de curseur.....................................................4
Le curseur de boucle FOR … LOOP...............................4
Le curseur paramètre ........................................................4
La clause ‘current of …..’ ...............................................4
La gestion des exceptions .....................................................5
Les exceptions internes.....................................................5
Les exceptions utilisateur .................................................5
Les fonctions SQLCODE et SQLERRM .......................5
Les sous-programmes ...........................................................6
Déclaration d’une procédure ............................................6
Déclaration d’une fonction...............................................6
Les Procédures et fonctions stockées...................................7
La création d’une procédure stockée ...............................7
Création de la procédure ..............................................7
Exécution de la procédure stockée ..............................7
Recherche d’une procédure stockée ............................7
Consultation d’une procédure stockée.........................7
Suppression d'une procédure stockée ..........................7
La création d’une fonction stockée..................................7
Exécution d'une fonction stockée ................................7
Présentation La déclaration des variables
Le langage PL/SQL est une extension du langage SQL avec des Les types de données
structures de contrôle (while, for, if…then ..else…) et des instructions
propres aux langages procéduraux. Un programme PL/SQL est structuré En plus des types de données SQL, PL/SQL possède des types propres :
en blocs d'instructions.
BOOLEAN variable booléenne qui peut recevoir les valeurs
L’utilisation de PL/SQL constantes : TRUE, FALSE, NULL.
BINARY_INTEGER
Script sql*plus ce type permet de manipuler des entiers signés de
Un script PL/SQL peut inclure des blocs anonymes d’instructions. On –2147483647 à 2147483647.
peut utiliser les variables paramètres de substitution de sql*plus. NATURAL ce type est un sous-ensemble de BINARY_INTEGER
Procédures ou fonctions stockées qui comprend les entiers de 0 à 2147483647.
Une procédure ou fonction stockée représente un sous-programme POSITIVE ce type comprend les entiers de 1 à 2147483647.
PL/SQL qui peut-être appelé par un programme applicatif client
(pro*cobol), un déclencheur de bases de données, un déclencheur %TYPE ce littéral permet de déclarer une variable de même type
applicatif conçu avec un outil de développement Oracle (oracle forms, que la colonne de table correspondante.
oracle report). %ROWTYPE ce littéral permet de déclarer une variable composée qui
Une procédure ou fonction stockée communique avec l’extérieur à a la même structure que la ligne de la table spécifiée.
l’aide de paramètres d’entrée/sortie.
Packages PL/SQL possède deux autres types de variable composée : RECORD et
Un package regroupe un ensemble de procédures, fonctions, curseurs, TABLE.
variables PL/SQL .
Définition d’une variable %type
Structure d’un bloc PL/SQL
Nom_variable nom_table.nomcolonne%type
Un bloc PL/SQL comporte trois parties :
Par exemple, on peut déclarer une variable nomvainqueur ayant le même
une partie déclaration (DECLARE)
une partie exécutable (BEGIN) type que la colonne nomskieur de la table skieur :
une partie optionnelle de gestion des erreurs (EXCEPTION)
nomvainqueur skieur.nomskieur%type ;
DECLARE Définition d’une variable %rowtype
Déclaration des variables
Déclaration de curseurs
Déclaration d’exceptions Nom_variable nom_table%rowtype ;
BEGIN
Corps du bloc PL/SQL (instructions exécutables) Déclaration de la variable composée vainqueur de même structure que la
[EXCEPTION] table skieur :
[gestionnaire d’exceptions]
END ; vainqueur skieur%rowtype ;

L’imbrication des blocs est possible. Le corps d’un bloc peut contenir la Pour accèder à un élément de la structure, on écrit :
définition d’autres blocs. Les blocs sont imbriqués et les règles de portée
sont classiques ( on cherche la définition d’un objet dans le bloc où il est vainqueur.nomski := ‘TOMBA’ ;
référencé, puis dans le bloc englobant).
Déclaration d’une variable composée RECORD
Exemple de bloc PL/SQL
La déclaration d’une variable RECORD se fait en deux étapes :
DECLARE - déclaration d’un type RECORD
min_rang number(3) ;
BEGIN
Select min(rang) into min_rang TYPE nom_type_record IS RECORD
(nom_champ typ_champ [NOT NULL] [valeur-initiale],
from classement
where nomski=’&nomskieur’ ; ……..
If min_rang<4 then nom_champ typ_champ [NOT NULL] [valeur-initiale]) ;
Insert into bienclasser values (‘&nomskieur’,min_rang) ;
else Exemple :
Insert into malclasser values (‘&nomskieur’,min_rang) ;
End if ; TYPE client_rec_type is record
End ; (nu_client number(6),
nom_client varchar2(30),
Remarque : pre_client varchar2(30),
- les instructions PL/SQL sont séparées par un point virgule ; solde number(8,2) ) ;
- &nomskieur est une variable/paramètre dont la valeur est saisie lors de
l’exécution (utilisable dans un script sql*plus), - déclaration d’une variable ayant le type précédent

nom_variable nom_type_record ;

Exemple :
Client_rec client_rec_type ;

Pour accéder à un champ de la variable record, on écrit

Client_rec.nom_client := ‘DUPONT’ ;

1
Déclaration d’une variable de type TABLE Concurrent skieur%rowtype ;
Pi constant number :=3.14 ;
Une variable de type table est un ensemble d’éléments de même type qui Taux_remise number(4,2) default 0.10 ;
sont classés selon un numéro d’indice (BINARY INTEGER). Type adresse_type is record
Une variable table peut-être considérée comme un tableau, mais dont la ( numero number(3),
taille est dynamique et dont les éléments peuvent être supprimés indivi- rue varchar2(40),
duellement. commune varchar2(30),
Sa déclaration se fait en deux étapes : cod_postal varchar2(5),
pays varchar2(30) ) ;
- déclaration du type table adresse_client adresse_type ;

TYPE nom_type IS TABLE OF

{type_donnée|table.nom_colonne%TYPE|variable%TYPE}
Les structures de contrôle
INDEX BY BINARY_INTEGER;
Différentes instructions PL/SQL permettent de contrôler le flux
- déclaration d’une variable de ce type d’exécution d’un programme PL/SQL.

nom_var_table nom_type; Instruction IF

Exemple : Syntaxe :

DECLARE IF condition THEN

TYPE tab_nom_type IS TABLE OF char(25) ; Instruction ; ….. instruction ;
Tab_nom tab_nom_type; [ELSIF condition THEN
………… instruction ; ….instruction ;]
BEGIN …………………………………..
…………….. …………………………………….
tab_nom(1) := ‘lafleche’; [ELSE
………….. instruction ; ……….. instruction ;]
END ; END IF ;

Remarque : condition : utilise les opérateurs SQL (>,<,>=,<=,=,<>, !=,

- L’indice d’une variable table peut prendre des valeurs négatives. IS [NOT] NULL) avec les variables PL/SQL
- On peut passer une variable table comme argument d’une procédure ou
fonction . Une instruction IF peut contenir plusieurs clauses ELSIF, mais une seule
- Les éléments d’une variable table peuvent être de type record. clause ELSE.
- Le fait de référencer un élément non initialisé entraîne une erreur Les clauses ELSIF et ELSE sont optionnelles.
‘NO_DATA_FOUND’ (Voir les Exceptions).

Instruction LOOP
La déclaration des variables en PL/SQL
Syntaxe :
Une variable reçoit un type et peut-être initialisée lors de sa déclaration.
Une constante est définie comme une variable, mais on ne peut pas LOOP
modifier sa valeur. Instruction ; ………instruction ;
END LOOP ;
Initialisation des variables
Pour sortir de cette boucle, il faut utiliser l’instruction EXIT exprimant la
Il existe deux méthodes pour initialiser une variable à la déclaration : condition de sortie à satisfaire. On exécute l’instruction qui suit END
LOOP.
Nom_variable [constant] type_donnée := valeur_initiale ;
EXIT signifie une sortie inconditionnelle de la boucle
Ou
EXIT WHEN condition
Nom_variable [constant] type_donnée default valeur_initiale ; on quitte la boucle quand la condition est satisfaite.

L’affectation de valeur aux variables Instruction WHILE …. LOOP

Deux possibilités d’affectation de valeur sont possibles : L’instruction WHILE … LOOP indique la condition à satisfaire pour que
la boucle soit exécutée.
- soit en utilisant l’opérateur d’affectation : ‘:=’
WHILE condition LOOP
- soit par l’instruction select …… into ….. Instruction ; …. Instruction ;
Pour que l’affectation soit correcte, l’instruction SELECT ne doit END LOOP ;
retourner qu’une seule ligne, sinon on se trouve dans un cas d’erreur.
Instruction FOR …. LOOP
Exemple :
select specialite into var_specialite On contrôle à l’aide d'un compteur l’exécution de la boucle
from skieur
where nomski=’TOMBA’ ; FOR compteur IN [REVERSE] borne_inf .. borne_sup LOOP
Instruction ; …. Instruction ;
var_specialite est une variable PL/SQL. END LOOP ;

Quelques exemples de déclaration - Compteur est une variable entière incrémentée de 1 à chaque
itération
- Borne_inf est la borne inférieure du compteur
Fax_number varchar2(10) ;
Total number(10,2) ; - Borne_sup est la borne supérieure du compteur
Dare_naissance date ; - REVERSE provoque la décrémentation de borne_sup à borne_inf
Numcli client.numero%type ;

2
Instructions SQL dans un programme PL/SQL
Toutes les instructions SQL peuvent être utilisées dans un programme
PL/SQL, avec néanmoins quelques différences par rapport à
SQL*PLUS : Instruction NULL
- chaque instruction SQL doit se terminer par un point-virgule, Permet dans certaines conditions d’indiquer qu’aucune action n’est à
- on peut utiliser des variables PL/SQL au sein des requêtes SQL, entreprendre (IF……THEN….ELSE……)
- Une instruction SELECT qui renvoie plusieurs lignes résultat doit
être traitée avec un curseur (voir plus loin). IF i<100 THEN
i :=i+1;
Exemple : ELSE
NULL ;
DECLARE END IF ;
i number(3) :=1 ;
BEGIN
for i in 1..100 loop
Insert into table_essai
values (i, SYSDATE) ;
Les Curseurs
end loop;
end ; Le curseur est un mécanisme permettant de traiter en PL/SQL les
requêtes SELECT générant un nombre arbitraire de lignes résultat. Le
curseur peut-être considéré comme une fenêtre sur l’ensemble des lignes
Dans cet exemple, l’instruction INSERT insère la valeur de la variable i
résultat d’une requête.
ainsi que la date système (SYSDATE) dans la table table_essai.
Sa mise en œuvre se fait en quatre étapes :
L’utilisation de PL/SQL sous SQL*PLUS
1- Déclaration du curseur
on associe au nom du curseur une instruction SELECT
La mise en œuvre de PL/SQL 2- Ouverture du curseur
la requête associée au curseur est exécutée et les lignes résultat sont
- La saisie du bloc PL/SQL se fait sous l’éditeur de texte disponible sélectionnées
(emacs ou vi sous Unix, bloc-notes sous Windows). Le texte source se 3- Parcours des lignes du curseur
terminera par le caractère ‘/’ sur la dernière ligne. Les lignes du curseur sont retournées une à une au programme
- On sauvegarde le fichier source en lui donnant un nom avec l’extension PL/SQL
.SQL. 4- Fermeture du curseur
- Sous SQL*PLUS, on exécute le bloc PL/SQL en tapant la barre oblique Les ressources allouées au curseur par le système sont libérées ;
/ sur la dernière ligne (comme pour SQL). le curseur n’est plus accessible par le programme
Les erreurs de syntaxe seront signalées à ce moment là .
Pour visualiser la liste des erreurs, il faut taper la commande : Déclaration du curseur
Show errors
(n’oublier pas de modifier auparavant la variable d’environnement Un curseur doit être déclaré dans la section déclaration avant d’être
arraysize et lui donner la valeur : 1) utilisé :
On retourne sous l’éditeur pour les corriger.
CURSOR nom_curseur
Les entrées-sorties sous SQL*PLUS [(param1 type_param1[ :=val_defaut1],
…………
Le langage PL/SQL ne prévoit pas explicitement des instructions d’E/S, paramN type_paramN[ :=val_defautN])]
en effet son utilisation normale se fait sous forme de procédures ou IS instruction_select ;
fonctions avec paramètres d’E/S. Mais sous SQL*PLUS, il est pourtant
possible de saisir des données et d’afficher des résultats.
param1,…..paramN : paramètres transmis au curseur (voir par. Curseurs
paramétrés).
Saisie de données
On utilise les variables/paramètres déjà vues en SQL
Exemple :
&nom_variable ou &&nom_variable
Cursor ski_descente is
A l’exécution du bloc PL/SQL, on demande à l’utilisateur de saisir la
Select nomski , nomstat
valeur qui se substituera à la variable/paramètre.
From skieurs
Si on utilise ‘&&’, on ne saisit qu’une seule fois la variable/paramètre
Where specialite=’descente’ ;
même si elle apparaît plusieurs fois dans le bloc PL/SQL.

Affichage des résultats Ouverture du curseur

Un package public Oracle nommé DBMS_OUTPUT fournit un ensemble
de procédures et de fonctions pour l’affichage de valeurs à partir d’un OPEN nom_curseur [param1 …paramN]
bloc PL/SQL.
La fonction d’affichage d’une liste d’expressions PL/SQL s’écrit : Exemple :
dbms_output.put_line(expression_chaîne) ; Open ski_descente ;

Exemple : Recherche des lignes du curseur

dbms_output.put_line(‘nomskieur :’ || vnomski) ; On récupère les lignes du curseur une à une et on range les valeurs des
champs de la ligne dans des variables PL/SQL réceptrices.
vnomski est une variable déclarée dans le bloc. Cette opération s’effectue en général dans une boucle.
Avant d’utiliser la procédure dbms_output.put_line avec SQL*PLUS, il
FETCH nom_curseur INTO {var1,…varN | var_record}
faut taper la commande : SET SERVEROUTPUT ON.
Exemple :
Les commentaires dans PL/SQL
LOOP
-- Je suis un commentaire qui tient sur une ligne FETCH ski_descente INTO vnomski,vnomstat ;
…………………………. EXIT WHEN ski_descente%NOTFOUND ;
/* je suis un commentaire long long long long long long long …………
long long et j’occupe plusieurs lignes */ END LOOP ;
END ;

3
 Ski_descente_rec est une variable record ayant la même structure que le
%NOTFOUND est un attribut qui teste si l’instruction fetch a retourné curseur ski_descente.
une ligne (prend la valeur ‘vrai’ si aucune ligne n’est retournée).
Fermeture du curseur Le curseur paramètre
Le curseur paramètre permet d’utiliser des variables dans le curseur
CLOSE nom_curseur (essentiellement dans la clause where). On spécifie les paramètres et leur
type dans la déclaration du curseur. Les types autorisés sont : char,
Exemple : number, date, boolean sans indiquer la longueur.
Close ski_descente ;
CURSOR nom_curseur
Les attributs de curseur [(param1 type_param1[ :=val_defaut1],
…………
paramN type_paramN[ :=val_defautN])]
Les attributs de curseur fournissent des informations concernant IS instruction_select ;
l’exécution du curseur
Les paramètres effectifs sont transmis dans l’instruction OPEN.
Attribut Type Objet
OPEN nom_curseur [param1 …paramN]
%found Booléen Indique si l’instruction fetch a retourné une
ligne (vrai)
%notfound Booléen Indique si l’instruction fetch n’a pas retourné Exemple :
de ligne (vrai)
%isopen Booléen Indique si le curseur est ouvert (vrai) DECLARE
cursor ski_special(p_special varchar2) is
%rowcount numérique Indique le nombre de lignes traitées par le
select nomski,nomstat
curseur
from skieurs
where specialite=p_special ;
Exemple :
BEGIN
for ski_special_rec in ski_special(‘slalom’) loop
DECLARE
dbms_output.put_line(ski_special_rec.nomski ||
vnomski skieur.nomski%type ;
ski_special_rec.nomstat) ;
vnomstat skieur.nomstat%type ;
end loop ;
cursor ski_descente is
END ;
select nomski,nomstat
from skieurs
Les curseurs imbriqués nécessitent l’utilisation de curseurs paramètre
where specialite=’descente’ ;
BEGIN
Exemple : on veut afficher pour chaque station, la liste des skieurs
Open ski_descente ;
originaires de cette station.
loop
fetch ski_descente into vnomski,vnomstat ;
DECLARE
exit when ski_descente%NOTFOUND ;
Cursor c1 is select * from stations ;
…………
Cursor c2(p_nomstat varchar2) is
end loop ;
Select * from skieurs
dbms_output.put_line(‘nombre de lignes : ‘ ||
Where nomstat=p_nomstat ;
ski_descente%rowcount) ;
BEGIN
close ski_descente ;
For c1_rec in c1 loop
END ;
dbms_ouput.put_line(c1_rec.nomstat || ’ ‘ || c1_rec.paystat) ;
for c2_rec in c2(c1_rec.nomstat) loop
Le curseur de boucle FOR … LOOP dbms_output.put_line(c2_rec.nomski) ;
end loop ;
Cette structure de contrôle simplifie la mise en œuvre des curseurs. end loop ;
En effet, elle regroupe en une seule instruction les instructions : open, end ;
fetch, close ; De plus la variable record qui récupère les données du
curseur n’a pas besoin d’être déclarée et possède une structure de même
La clause ‘current of …..’
type que celle du curseur.
Cette clause permet de mettre à jour (update, delete) la ligne en cours de
DECLARE
traitement par l’instruction ‘fetch’.
Cursor nom_curs is ordre_select
{update ….|delete ….} where current of nom_curseur
[rec_curs nom_curs%rowtype ;]
BEGIN
Pour utiliser la clause ‘current of ….’, il faut dans la déclaration du
FOR rec_curs IN nom_curs LOOP curseur indiquer son intention par la clause :
/* traitement lignes curseur*/ ……….for update [of nom_colonne1,……..nom_colonneN]
END LOOP;
Exemple :
Rec_curs est une variable record, de même type que les lignes du
curseur, qu'il n'est pas indispensable de déclarer. Declare
Cursor c1 is select ename, sal
from emp
Exemple : for update of sal;
DECLARE Begin
cursor ski_descente is For c1_record in c1 Loop
select nomski,nomstat if c1_record.sal > 1500
from skieurs then
where specialite=’descente’ ; update emp set sal = sal * 1.3
BEGIN where current of c1;
for ski_descente_rec in ski_descente loop end if;
dbms_output.put_line(ski_descente_rec.nomski|| End loop;
ski_descente_rec.nomstat) ; End;
end loop ;
END ;

4
La gestion des exceptions Les fonctions SQLCODE et SQLERRM
- SQLCODE est une fonction prédéfinie qui renvoie un code d’erreur
La gestion des exceptions permet de gérer les erreurs et exceptions qui après chaque instruction SQL. Elle prend la valeur 0 s’il n’y a pas
surviennent en dehors du traitement normal de l’application. La gestion
d’erreur.
des exceptions se fait dans une section spéciale qui prend le contrôle dès - SQLERRM est une fonction prédéfinie qui renvoie le message d’erreur
qu’une exception se présente. On peut alors abandonner ou continuer le associé au type d’erreur détectée.
traitement.
Exemple :
EXCEPTION
WHEN nom_exception THEN EXCEPTION
Instructions _pl/sql When others then
WHEN nom_exception THEN dbms_output.put_line(‘SQLCODE : ‘ || to_char(SQLCODE) ;
Instructions _pl/sql dbms_output.put_line(SQLERRM);
…….. END ;
WHEN OTHERS THEN
Instructions_pl/sql
END ;

Nom_exception représente le nom des exceptions internes ou définies Envoi de messages d’erreur
par l’utilisateur
OTHERS représente toutes les autres exceptions On dispose de la procédure RAISE_APPLICATION_ERROR dans le
package DBMS_STANDARD. Celle-ci permet d’envoyer des messages
Les exceptions internes d’erreur spécifiques pour des cas d’erreur définis par les utilisateurs.
Une exception interne apparaît quand PL/SQL ne respecte pas une règle Le système réserve les codes d’erreur de –20000 à –20999 pour ces
d’Oracle. Les erreurs Oracle sont codifiées et identifiables par PL/SQL. erreurs.
Voici les principales exceptions internes :
Exemple :
Nom exception Objet exception EXCEPTION
Valeur dupliquée pour un index unique When erreur_nombre_horslimites then
DUP_VAL_ON_INDEX Raise_application_error(-20001, ‘nombre en dehors des limites’) ;
Select ne retourne aucune ligne ………………….
NO_DATA_FOUND
Select simple renvoie plus d’une ligne
TOO_MANY_ROWS
Instruction pl/sql spécifie un nombre
INVALID_NUMBER
invalide
Erreur de troncature ou de conversion
VALUE_ERROR
Autres erreurs
OTHERS

Les exceptions utilisateur

L’utilisateur peut définir ses propres exceptions. Voici la démarche :

1- il faut déclarer le type d’exception dans la partie DECLARE

DECLARE
nom_anomalie EXCEPTION ;

2- appeler le traitement de l’exception lors de son apparition

RAISE nom_anomalie ;

3- décrire le traitement de l’exception dans la section Exception

WHEN nom_anomalie then traitement_anomalie ;

Exemple :

DECLARE
vsal emp.sal%type ;
sal_zero exception ;
BEGIN
Select sal into vsal from emp ;
If vsal=0 then
Raise sal_zero ;
End if ;
EXCEPTION
When sal_zero then
--gérer erreur salaire=0
When too_many_rows then
--gérer erreur trop de lignes retournées par select
When no_data_found then
--gérer erreur pas de ligne retournée par select
When others then
--gérer les autres erreurs
END ;

5
 PROCEDURE classt_extremes(pnomski char, pmin out number, pmax
Les sous-programmes out number) is
begin
PL/SQL permet l’utilisation de sous-programmes : procédures et Select min(rang), max(rang) into pmin,pmax
fonctions. Une procédure exécute un ensemble d’actions, une fonction from classement
retourne une valeur d’un certain type. where nomski=pnomski ;
Toute fonction ou procédure doit être déclarée avant de pouvoir être END classt_extremes ;
appelée dans un bloc PL/SQL.
BEGIN
Déclaration d’une procédure Classt_extremes(&vnomki,vmin,vmax)
Dbms_output.put_line(&vnomski||‘ classt mini : ‘|| vmin||
PROCEDURE nom_proc [param1,…..[,paramN]] IS ‘ classt maxi : ‘||vmax) ;
[declaration_variables_locales] END ;
BEGIN
--instructions exécutables Déclaration d’une fonction
[EXCEPTION]
--gérer les exceptions FUNCTION nom_fonc [param1,…..[,paramN]]
END [nom_proc] ; RETURN type_res IS
[declaration_variables_locales]
- Nom_proc: est le nom de la procédure BEGIN
- Param1 à paramN : représentent la déclaration des paramètres --instructions exécutables
optionnels sous la forme : [EXCEPTION]
--gérer les exceptions
nom_param [IN|OUT| IN OUT] type [{ :=| DEFAULT} valeur] END [nom_fonc] ;

le type ne doit pas contenir d’indication sur la longueur. - Nom_fonc : est le nom de la fonction
- Param1 à paramN : représentent la déclaration des paramètres
Exemple d’utilisation d’une procédure dans un bloc PL/SQL : optionnels sous la forme :
On recherche la spécialité d’un skieur dans la table ‘skieurs’ et on
l’affiche. nom_param [IN|OUT| IN OUT] type [{ :=| DEFAULT} valeur]

DECLARE le type ne doit pas contenir d’indication sur la longueur.

vnomski varchar2(25) ; - RETURN type_res : indique le type du résultat de la fonction.
PROCEDURE specialite(pnomski char) is
vspecial varchar2(25) ; Exemple d’utilisation d’une fonction dans un bloc PL/SQL :
begin On reprend l’exemple précédent, mais la fonction retourne la valeur de la
select specialite into vspecial from skieur spécialité au bloc PL/SQL qui l’affiche.
where nomski=pnomski;
dbms_output.put_line('specialite: '||vspecial); DECLARE
exception vnomski varchar2(25) ;
when NO_DATA_FOUND then FUNCTION specialite(pnomski varchar2)
dbms_output.put_line('skieur non existant :’ RETURN varchar2 is
||pnomski); vspecial varchar2(25) ;
END specialite; begin
select specialite into vspecial from skieur
BEGIN where nomski=pnomski;
vnomski :=’tomba’ ; return vspecial ;
specialite(vnomski) ; Exception
END ; when NO_DATA_FOUND then
dbms_output.put_line('skieur non existant :’
L’appel d’une procédure dans le bloc PL/SQL se fait en tapant le nom de ||pnomski);
la procédure avec les paramètres effectifs entre parenthèses : END specialite;
specialite(vnomski) ;
BEGIN
Une procédure intégrée dans un bloc PL/SQL ne peut être appelée que vnomski :=’tomba’ ;
depuis ce bloc ; d’où l’intérêt d’écrire des procédures stockées. dbms_output.put_line('specialite: '||specialite(vnomski);
END ;
Utilisation des paramètres IN | OUT | IN OUT dans une procédure
L’appel de la fonction specialite(vnomski) figure dans une instruction
IN le paramètre est passé en entrée dbms_output.put_line.
IN OUT le paramètre est lu en entrée et sa valeur peut-être modifiée
par la procédure
OUT le paramètre est valorisé par la procédure

Par défaut c'est l'option IN qui est active.

Exemple:
Ce bloc PL/SQL affiche le meilleur et le plus mauvais résultat d’un
skieur. Il fait appel à la procédure classt_extremes qui a pour argument en
entrée &&vnomski (nom du skieur) et pour arguments de sortie
vmin(meilleur classement) et vmax (plus mauvais classement)

DECLARE
vmin number(3);
vmax number(3);
vnomski skieur.nomski%type ;

6
Les Procédures et fonctions stockées Name : nom de la procédure, fonction, package
Type : type d’objet (procedure, fonction, package)
Une procédure ou fonction stockée est un sous-programme PL/SQL Text : une ligne du code source
conservé dans une base de données Oracle et peut-être appelée à partir de Line : n° de ligne du code source
divers programmes applicatifs : SQL*PLUS, Pro*cobol, pro*C, Oracle
Forms, Oracle Report, autre procédure ou fonction. Exemple :
Select line,text
From user_source
Avantages : Where name =’…..’
- Efficacité dans architecture client/serveur : le code PL/SQL est Order by line ;
stocké sous forme compilée dans la base et n’a pas besoin de
transiter sur le réseau entre le client et le serveur. Seul le résultat est Suppression d'une procédure stockée
renvoyé au client.
- Réutilisabilité : la procédure ou fonction peut-être utilisée à partir Une procédure stockée est supprimée par la commande SQL suivante:
de divers applicatifs qui nécessitent une même fonctionnalité. La
maintenance est assurée de façon centralisée. Des économies sont DROP PROCEDURE nom_proc;
faites dans la réalisation des applications.
- Sécurité/intégrité : certaines opérations délicates sur la base seront
écrites sous forme de fonction ou procédure et mis à la disposition La création d’une fonction stockée
des développeurs ; en particulier les opérations assurant la
maintenance des contraintes d’intégrité portant sur les données. CREATE OR REPLACE FUNCTION nom_fonc
Certaines tables ne seront accessibles que via les fonctions ou [param1,…..[,paramN]]
procédures stockées. RETURN type_res IS
[declaration_variables_locales]
La création d’une procédure stockée BEGIN
--instructions exécutables
[EXCEPTION]
CREATE [OR REPLACE] PROCEDURE nom_proc
--gérer les exceptions
[param1,…..[,paramN]] IS
END [nom_fonc] ;
[declaration_variables_locales]
BEGIN
--instructions exécutables
[EXCEPTION] Exemple :
--gérer les exceptions
END [nom_proc] ; CREATE OR REPLACE FUNCTION specialite(pnomski
char)
Exemple : RETURN varchar2 is
vspecial varchar2(25) ;
begin
CREATE OR REPLACE PROCEDURE specialite(pnomski char)
select specialite into vspecial from skieur
is
where nomski=pnomski;
vspecial varchar2(25) ;
return (vspecial );
begin
Exception
select specialite into vspecial from skieur
when NO_DATA_FOUND then
where nomski=pnomski;
dbms_output.put_line('skieur non existant :’
dbms_output.put_line('specialite: '||vspecial);
||pnomski);
exception
END specialite;
when NO_DATA_FOUND then
dbms_output.put_line('skieur non existant :’
||pnomski); La création, recherche, consultation, suppression d'une fonction stockée
END specialite; utilise les mêmes commandes que celles utilisées par les procédures
stockées.
Création de la procédure
Exécution d'une fonction stockée
L’exécution de l’instruction CREATE PROCEDURE va compiler le code
- à partir de SQL*PLUS
PL/SQL et stocker la procédure dans la base. On visualise les erreurs de
ceci suppose l'utilisation d'une variable liée:
compilation par la commande SQL*PLUS :
var v_special varchar2(30)
SHOW ERRORS
execute :vspecial:=specialite('tomba')
print v_special
Exécution de la procédure stockée
v_special est une variable liée.
- à partir de SQL*PLUS
EXECUTE specialite(‘tomba’) ;
- à partir d'un autre sous-programme ou bloc PL/SQL
- à partir d’un bloc PL/SQL ou d’une autre procédure
…………
specialite(‘tomba’) ;
res:=specialite('tomba');
…………………….
Recherche d’une procédure stockée
Res est une variable PL/SQL préalablement définie.
La table USER_OBJECTS contient la liste des procédures et fonctions
stockées. Elle contient également d’autres objets : index, packages,
tables, vues.

Select object_name from user_objects

Where object_type in (‘FUNCTION’,’PROCEDURE’) ;

Consultation d’une procédure stockée

Si le script SQL utilisé pour créer la procédure n’est pas disponible, on

peut consulter le code source de la procédure en interrogeant une vue du
dictionnaire des données d’Oracle : USER_SOURCE

7
 elsif prang=2 then
point:=3;
Les Packages elsif prang=3 then
point:=1;
Un package est un ensemble de procédures et fonctions PL/SQL else point:=0;
associées, stockées dans une base de données. On regroupe dans un end if;
package les fonctions et procédures qui concernent une même application return (point);
ou qui réalisent une même tâche. end calpoint;
La conception d’un package PL/SQL nécessite la création
- d’une spécification de package ( CREATE PACKAGE), /* fonction publique qui retourne le nom et la spécialité du skieur ayant
- d’un corps de package (CREATE PACKAGE BODY). été classé premier à une compétition donnée (paramètre IN) */
function meilleurski(prefcomp varchar2) return ski_type is
Déclaration de Package tskieur ski_type;
La spécification de package consiste à déclarer les types, variables, begin
curseurs, procédures, fonctions accessibles par tous les utilisateurs select skieur.nomski,specialite into tskieur.nomski,tskieur.specialite
(PUBLIC). from skieur,classement
where skieur.nomski=classement.nomski
CREATE [OR REPLACE] PACKAGE nom_package IS and rang=1 and refcomp=prefcomp;
section_declaration return (tskieur);
END nom_package ; end meilleurski;

- section_declaration consiste en des déclarations de types, de variables, /* fonction qui calcule le cumul des points obtenu par un skieur à partir
de curseurs, de procédures, de fonctions. de son classement aux différentes compétitions. On applle la fonction
privée calpoint() */
Exemple : function cumulpoints(pnomski varchar2) return number is
Le package class_ski regroupe les types, variables, fonctions et cumulpt number(5) :=0;
procédures qui concernent les résultats obtenus par les skieurs aux cursor ski_classer is
différentes compétitions. select rang from classement
On définit un type record de nom ski_type ; ce type est utilisable à where nomski=pnomski;
l’extérieur du package. begin
La fonction meilleurski() retourne le skieur classé premier dans une for rec_ski in ski_classer loop
compétition donnée. La fonction cumulpoints() retourne le cumul des cumulpt:= cumulpt+calpoint(rec_ski.rang);
points obtenu par un skieur aux différentes compétitions auxquelles il a end loop;
participé. La procédure ins_classer() insère une nouvelle ligne dans la return cumulpt;
table classement. end cumulpoints;

CREATE OR REPLACE PACKAGE class_ski IS /* la procédure ins_classer permet d’insérer une nouvelle ligne dans la
type ski_type is record (nomski skieur.nomski%type, table classement. On y incrémente la variable globale nbinsert */
specialite skieur.specialite%type); procedure ins_classer(pcomp varchar2,pnomski varchar2,prang
function meilleurski(prefcomp varchar2) return ski_type; number)is
function cumulpoints(pnomski varchar2) return number; begin
procedure ins_classer(pcomp varchar2,pnomski varchar2,prang insert into classement values(pcomp,pnomski,prang);
number); nbinsert:=nbinsert+1;
END class_ski; text_io.put_line('insertions:'||nbinsert||'nblignes:'||nb_lignes);
end ins_classer;
Déclaration du corps de package /* code pl/sql exécuté une seule fois ; il permet d’initialiser la variable
Le corps d’un package définit les procédures, les fonctions, les curseurs, globale nb_lignes */
les exceptions déclarés dans la spécification du package. Ceci permet de begin
cacher aux utilisateurs les détails d’implémentation. select count(*) into nb_lignes from classement;
Il permet également de définir des objets de même type non déclarés dans END;
les spécifications et qui sont privés (ne peuvent être accédés qu’à
l’intérieur du corps de package).

CREATE OR REPLACE PACKAGE BODY nom_package IS Appel des procédures/fonctions d’un package
Declaration_locales_package
Corps_procedures_fonctions Nom_package.nom_procedure_fonction([parametres.. …])
END nom_package ;
L’appel d’une fonction ou procédure (partie publique) d’un package se
- Déclarations_locales_package consiste en des déclarations de variables, fait comme l’appel d’une fonction ou procédure stockée.
de curseurs, d’exceptions locales au package. Ces objets sont globaux et
peuvent être utilisés dans toutes les fonctions et procédures définies dans Exemples :
le corps du package. - on veut afficher le total des points obtenus par ‘lafleche’ à partir d’un
Ainsi la variable nb_insert est incrémentée dans la procédure ins_classer. programme pl/sql :
- Corps_procedures_fonctions contient la définition des procédures et ……………..
fonctions déclarées dans la spécification du package , mais également des dbms_output.put_line(class_ski.cumulpoints('lafleche'));
fonctions et procédures privées (internes au package). …………………
Par exemple, la fonction calpoint() est une fonction interne utilisée par la
fonction publique cumulpoints(). - on veut afficher le meilleur skieur de la compétition ‘comp2’ ; le
programme pl/sql s’écrit :
Exemple :
declare
CREATE OR REPLACE PACKAGE BODY class_ski IS res class_ski.ski_type;
nbinsert number(5) :=0; --variables globales utilisables begin
nb_lignes number(5); --dans corps du package uniquement res := class_ski.meilleurski('comp2');
dbms_output.put_line(res.nomski||' '||res.specialite);
/* fonction privée interne au package */ end;
function calpoint(prang number) return number is /
point number(1);
begin On déclare une variable res de type ski_type qui a été déclaré comme un
if prang=1 then type public dans la spécification du package class_ski.
point:=5;

8
Remarque : on préfixe le nom du type, le nom de la fonction ou de la
procédure par le nom du package.

Initialisation d’un package

On peut inclure dans le corps du package un bloc pl/sql qui n’est exécuté
qu’une seule fois, lors du premier appel d’un composant du package.
Ce bloc est placé à la fin du corps du package.
Exemple :
on veut initialiser la variable globale nb_lignes avec le nombre de lignes
de la table classement.
……..
begin
select count(*) into nb_lignes from classement;
END;

Suppression d’un package

DROP PACKAGE [BODY] nom_package

permet de supprimer tout un package ou seulement le corps du package

(option BODY).

La surcharge des sous-programmes

On peut utiliser le même nom pour deux sous-programmes (procédure,
fonction) différents pourvu que les paramètres formels se différencient
par leur nombre, leur ordre ou leur type.
On a défini précédemment une fonction cumulpoints(pnomski varchar2)
qui calcule le cumul des points obtenu par un skieur à l’ensemble des
compétitions auxquelles il a participé.
On va maintenant définir une nouvelle fonction cumulpoints(pnomski
varchar2, pmois number) qui calcule le cumul des points obtenu par un
skieur pour les compétitions d’un mois donné (numéro de mois).

function cumulpoints(pnomski char, pmois number) return number is

cumulpt number(5) :=0;
cursor ski_classer is
select rang from classement,competition
where nomski=pnomski
and competition.refcomp=classement.refcomp
and pmois = to_char(datcomp,'mm');
begin
for rec_ski in ski_classer loop
cumulpt:= cumulpt+calpoint(rec_ski.rang);
end loop;
return cumulpt;
end cumulpoints;

Appel de la nouvelle fonction à partir d’un programme pl/sql :

…………………
dbms_output.put_line(class_ski.cumulpoints('lafleche’,2));
…………………

on affiche le cumul des points obtenu par le skieur ‘lafleche’ lors des
compétitions ayant lieu durant le mois 2.

9
Les déclencheurs( triggers) Les événements déclencheurs

Le déclencheur de bases de données est un mécanisme qui déclenche -> UPDATE [ OF col1 [, col2 .........]] ON nomtable
automatiquement une séquence d'actions quand un certain événement a
lieu (insertion, modification, suppression dans une table). On indique 0,1,n colonnes de la table dont la mise à jour déclenchera le
Par exemple, lorsqu'on supprime une commande, on veut automa- déclencheur.
tiquement supprimer toutes les lignes de cette commande.
Les actions déclenchées par un déclencheur sont constituées des -> INSERT ON nomtable
instructions d'un bloc PL/SQL.
Le déclencheur est enregistré dans la base de données et tout utilisateur On indique le nom de la table pour laquelle l'insertion d'une ligne
autorisé peut l'utiliser. déclenchera le déclencheur.
Deux instructions SQL permettent de créer et de supprimer les
déclencheurs: CREATE TRIGGER et DROP TRIGGER. -> DELETE ON nomtable
Pour créer un déclencheur il faut être propriétaire de la table ou avoir le
privilège DBA. On indique le nom de la table dont la suppression d'une ligne déclenchera
La création d'un déclencheur se fait sous SQL*PLUS comme une requête le déclencheur.
SQL.
On peut combiner les trois événements déclencheurs dans un même
L'utilisation des déclencheurs déclencheur :
CREATE TRIGGER maj_client BEFORE
- Vérifier que certaines règles de gestion sont respectées lors de certaines INSERT OR DELETE OR UPDATE ON client
opérations; ainsi, lors de l'enregistrement d'une nouvelle commande ………………
client, on vérifiera que le crédit client est suffisant. Dans le boc PL/SQL, on peut alors utiliser les prédicats suivants :

- Enchaîner des mises à jour automatiquement; lorsqu'on modilie la IF INSERTING THEN……..

quantité commandée d'une ligne de commande, on souhaite recalculer le IF DELETING THEN …….
montant total de la ligne ( prix * quantité). IF UPDATING THEN……

- Appliquer des contraintes d'intégrité; si on supprime un client, on La clause REFERENCING

souhaite supprimer toutes les commandes de ce client.
Elle permet de définir un nom d'alias pour accéder aux données avant ou
- Créer un fichier audit. Dès qu'un utilisateur crée une nouvelle ligne de après exécution de l'événement déclencheur. Les données concernées
commande, on insère une ligne dans un fichier audit qui indique le nom sont les attributs de la table constituant l'événement déclencheur.
de l'utilisateur, la date/heure de l'opération, le n° de commande, le n° de
ligne, la quantité. - Le déclencheur est une insertion dans une table

La création d'un déclencheur REFERENCING NEW AS nomalias

La syntaxe: nomalias permet d'accéder aux valeurs de la ligne insérée dans la

table
CREATE [OR REPLACE] TRIGGER nom_declencheur [BEFORE |
AFTER] - Le déclencheur est une suppression dans une table
Evenement_declencheur ON nom_table
[REFERENCING [OLD AS nomalias1] [NEW AS nomalias2]] REFERENCING OLD AS nomalias
[FOR EACH ROW]
[WHEN (condition)] nomalias permet d'accéder aux valeurs de la ligne supprimée dans
bloc_PL/SQL la table

Exemple: le déclencheur del_cli, déclenché par la suppression d'une ligne - Le déclencheur est une mise à jour dans une table (Update)
de la table client, supprime les commandes de ce client.
REFERENCING [OLD AS nomalias1] [NEW AS nomalias2]
CREATE TRIGGER del_cli BEFORE
DELETE ON client nomalias1 permet d'accéder aux valeurs de la table avant mise à
REFERENCING OLD AS pre_del jour
FOR EACH ROW nomalias2 permet d'accéder aux valeurs de la table après mise à
BEGIN jour.
Delete from commandes
where client_num=:pre_del.client_num; On peut se passer de la clause REFERENCING et accéder aux valeurs
END; des colonnes de la table client (par exemple) en utilisant la notation :
:old.client_num
L'Evénement déclencheur consiste en la suppression d'un client ou :new.client_num
plusieurs client(s) de la table client

Les Actions déclenchées réalisent la suppression de toutes les Les actions déclenchées
commandes du client supprimé. La clause For each row signifie qu'on
exécute les actions autant de fois qu'il y a de clients supprimés. En effet Quand exécuter les actions déclenchées
une requête de suppression peut entraîner la suppression de plusieurs
clients. Les actions déclenchées sont précédées d'une indication concernant le
moment où elles doivent être exécutées. Cette indication peut prendre
La Clause Referencing est utilisée avec la clause for each row et permet l'une des formes suivantes:
de définir un alias pour désigner la table client avant ou après exécution
de l'événement déclencheur: BEFORE
avant exécution de l'événement déclencheur : OLD,
après exécution de l'événement déclencheur : NEW AFTER
Dans notre exemple, la clause REFERENCING OLD AS pre_del
permet d'accéder aux valeurs des colonnes de la table client (dans le bloc Avec BEFORE, les actions déclenchées sont exécutées avant que
PL/SQL) avant exécution de l'événement déclencheur ( la suppression du l'événement déclencheur ait été exécuté.
client).
Avec AFTER, les actions déclenchées sont exécutées après que
l'événement déclencheur ait été exécuté.
10
Déclencheurs de niveaux instruction et ligne Suppression, activation d’un déclencheur
- Un déclencheur de niveau ligne ( FOR EACH ROW), s’exécute Il faut être le propriétaire du déclencheur ou être DBA pour effectuer ces
autant de fois que de lignes concernées par l'événement opérations.
déclencheur. On peut accéder aux anciennes et nouvelles valeurs
des lignes concernées par l’événement. Il est nécessaire d'utiliser la DROP TRIGGER nom_déclencheur
clause REFERENCING si on utilise la clause FOR EACH ROW.
Permet de supprimer un déclencheur
- Un déclencheur de niveau instruction ne s’exécute qu’une seule fois
et ne peut accéder aux valeurs des colonnes des lignes concernées ALTER TRIGGER nom_déclencheur DISABLE
par l’événement déclenchant.
Permet de désactiver un déclencheur

La condition WHEN ALTER TRIGGER nom_déclencheur ENABLE

Une action déclenchée peut comporter une condition d'exécution Permet d’activer un déclencheur précédemment désactivé.
(WHEN) suivie des instructions à réaliser.

When (condition)

Les actions déclenchées ne sont exécutées que si la condition figurant

derrière WHEN est vraie.
Exemple :

CREATE TRIGGER up_prix AFTER

UPDATE OF prix_unit ON stock
REFERENCING OLD AS pre NEW AS post
FOR EACH ROW
WHEN (post.prix_unit > pre.prix_unit*2)
BEGIN
Insert into warn_tab VALUES (pre.stock_num,
pre.prix_unit, post.prix_unit,CURRENT) ;
END ;

Lors d'une mise à jour du prix unitaire (prix_unit), si le nouveau prix est
supérieur au double de l'ancien prix, on insère une ligne dans la table
tab_warn pour garder une trace de l'opération effectuée.

Un exemple de déclencheur faisant appel à une procédure cataloguée:

Lorsqu’on insère une ligne dans la table classement, on active un

déclencheur qui met à jour la table skieur en incrémentant totpoint du
nombre de points obtenus par le skieur en fonction de son classement

CREATE TRIGGER ins_class BEFORE

INSERT ON classement
FOR EACH ROW
WHEN (:new.rang<4)
DECLARE
nbpoint number(2);
BEGIN
nbpoint := fcalpoint( :new.rang) ;
update skieur set tot_points = totpoints + nbpoint
where nomski = :new.nomski ;
END ;

La fonction fcalpoint(:new.rang) calcule le nombre de points obtenus en

fonction du rang occupé dans le classement d’une compétition

CREATE OR REPLACE FUNCTION fcalpoint(prang

number)
return number is
points number(2) ;
BEGIN
if prang = 1 then
points :=5;
elsif prang = 2 then
points := 3;
elsif prang = 3 then
points := 1;
else
points := 0;
end if;
return( points) ;
END fcalpoint;

11