Cours Pl SQL

Le langage PL/SQL

LE LANGAGE PL/SQL

1 / INTRODUCTION :SQL est un langage complet pour travailler sur une base de donnée relationnelle, mais il necomporte pas des instructions procédurales. PL/SQL comprend quant à lui :

v La partie LID (Langage d’Interrogation des données) de SQL (Select),v la partie LMD (Langage de Manipulation des Données) de SQL (Update, Insert,…)v la gestion des transaction (Commit, Rollback…)v les fonctions standard de SQLv plus une partie procédurale (IF, WHILE,…)

PL/SQL est donc un langage algorithmique complet

REMARQUE : PL/SQL ne comporte pas d’instructions de LDD (Create, Alter…)

2/ STRUCTURE D’UN BLOC PL/SQL

PL/SQL n'interprète pas une commande, mais un ensemble de commandes contenues dans unprogramme ou bloc PL/SQL.La structure d’un bloc est la suivante:

DECLAREDéclarations de variables, constantes, exception;BEGINSection obligatoire contenant des instructions SQL et PL/SQL ou des blocs fils (Possibilitésd’imbrication de blocs)EXCEPTIONTraitement des exceptions (gestion des erreurs)END;

REMARQUES :Les sections Declare et Exception sont optionnellesChaque instruction de n’importe quelle section doit se terminer par un ‘ ;’

MH Page 1 sur 20 UIMA

Le langage PL/SQL

Exemple d’un bloc PL/SQL :

DECLAREqte_stock number(5);

BEGINSelect quantite into qte_stock from PRODUITSwhere codprod= ‘p1’;

-- contrôle du stock suffisantIf qte_stock > 0

then update produits set quantite=quantite-1where codprod= ‘p1’;

Insert into achat values (‘p1', SYSDATE)else

Insert into acheter values ('Plus de p1',SYSDATE);end If, commit, END;

3/. LES DECLARATIONS PL/SQL

La partie déclarative dans un bloc PL/SQL, peut comporter trois types de déclarations. Elleest délimitée par les mots-clé :v DECLARE, qui spécifie le début de la déclaration des variables etv BEGIN, qui signifie la fin de la déclaration et le début de la partie des commandes.v Les types de déclarations possibles dans cette partie sont les suivants :

ü déclaration des variables et des constantes,ü déclaration de curseurs,ü déclaration des exceptions.

3.1 Types de donnéesChaque variable ou constante utilisée dans un bloc PL/SQL, possède un type de données.PL/SQL offre deux variétés de types de données prédéfinies : scalaire et composé.

Les types scalaires :Binary_integer : entiers entre –231 et 231 -1Number : un numériquePositive : entiers positifsNatural : entiers naturelsDecimal , float, integer, real sont des sous-types de numberChar : chaîne de caractère à taille fixe allant jusqu’à 32 767 caractère (au lieu de 255 dans ladéfinition des colonnes des tables)Varchar2 : est une chaîne de caractère de longueur variable allant jusqu’à 32 767 (au lieu de2000 dans la définition des colonnes des tables)Boolean : type booléen ses valeurs possibles sont TRUE, FALSE, NULLDate : type date d’OracleRowid : type interne à Oracle composé de 6 octets qui permet d’identifier une ligne uniqued’une table et d’offrir un accès rapide aux données.

Les types Composés :Record : c’est le type enregistrement


http://www.go2pdf.com

Le langage PL/SQL

Table : c’est le type tableau

3.2 Variables et constantes

La déclaration d'une variable consiste à allouer un espace pour stocker et modifier une valeur.elle est typée et peut recevoir une valeur par défaut et/ou un statut NOT NULL.Une constante est définie comme une variable, mais l'utilisateur ne peut pas modifier soncontenu.

3.2.1. La définition des variables en PL/SQL

Les variables se définissent dans la partie DECLARE, du bloc PL/SQL en utilisant la syntaxesuivante :

nomvariable [CONSTANT]{type | variable%TYPE | colonne_de_table.%ROWTYPE}[NOT NULL][{ :=|DEFAULT} expression PL/SQL]

Remarques :L'attribut CONSTANT permet de figer l'affectation d'une variable.L'attribut Not NULL rend obligatoire d'initialiser la variable lors de sa définition.On peut faire référence à une colonne d'une table par la déclaration :♦ nom_variable TABLE.COLONNE%TYPE,

On peut faire référence à une ligne d'une table par la déclaration♦ Nom_variable TABLE%ROWTYPE,

On peut faire référence à une variable précédemment définie par l'instruction♦ Nom_variable Pnom variable%TYPE♦ L'initialisation d'une variable se fait par l'opérateur ‘ :=’ suivi

ü d'une constante,ü d'une expression PL/SQL,ü d'une fonction PL/SQL.

Les variables peuvent également être définies dans l'environnement extérieur au Bloc PL/SQLpar exemple comme des champs de l'écran en Form Builder. Ces variables seront utiliséespréfixées de':'.

Exemples de déclaration de variables :Total NUMBER(9,3);Nom CHAR(4) := ‘ISET’;Longeur NUMBER NOT NULL := LENGTH (Nom)*2;Date_Création DATE;Numéro EMPLOYE.EMPNO%TYPE;Dpt DEPARTEMENT%ROWTYPE;Prénom Nom%TYPE;Pi CONSTANT NUMBER:= 3.14;



Le langage PL/SQL

3.2.2 L’affectation des variables PL/SOL

Deux possibilités d’affectation ou d’assignement sont disponibles:par l'opérateur d'affectation:':=',par la clause Select ... Into…

La difficulté dans l'utilisation de la clause Select résulte du nombre de lignes ou d'occurrencesretourné.Si le Select retourne une et une seule valeur l'affectation s'effectue correctement. Par contreSi le SELECT ne retourne aucune ligne, l’erreur PL/SQL NO_DATA_FOUND sera générée.Si le SELECT retourne plusieurs lignes : l’erreur TOO_MANY_ROWS sera générée.

3.3. Les tableaux en PL/SQL

Nous avons vu précédemment que le langage PL/SQL fournit deux types d'objets composés:• les tableaux (TABLE),• les enregistrements (RECORD).Les tableaux sont conçus comme les tables de la base de données. Ils possèdent une cléprimaire(index) pour accéder aux lignes du tableau.Un tableau, comme une table, ne possède pas de limite de taille. De cette façon, le nombred'éléments d'un tableau va croître dynamiquement.

3.3.1 La déclaration d'un tableau

Les tableaux PL/SQL doivent être déclarés en deux étapes.1.Déclaration du type de la TABLE2.Déclaration d’une table de ce type.

On peut déclarer un type TABLE dans la partie déclarative d'un bloc ou d'un sous-programmeen utilisant la syntaxe suivante :

TYPE nom_type IS TABLE OF{typecolonne | variable%TYPE | table.colonne%TYPE} [NOT NULL]INDEX BY BINARY_INTEGER ;

Norn_type : utilisé ultérieurement dans la déclaration des tables PL/SQL.typecolonne : type de données comme CHAR, DATE, ou NUMBER.



Le langage PL/SQL

Lorsque le type est déclaré, on peut déclarer des tableaux de ce type de la façon suivante :Nom_tab nom_type ;

3.3.2 L'accès aux éléments d'un tableauPour accéder à un élément du tableau, on doit spécifier une valeur de clé primaire enrespectant la syntaxe suivante :

Nom_tab(valeur_clé_primaire) ;

Valeur_clé_primaire : doit être du type BINARY_INTEGER

Pour affecter la valeur d'une expression PL/SQL à un élément du tableau, on doit utiliser lasyntaxe suivante :

nom_tab(valeur_clé_primaire) := expression_Pl/sql ;

Exemple :

DECLARE

TYPE nom_tab_type IS TABLE OF CHAR(25) INDEX BY BINARY-INTEGER;…

tab_nom nom_tab_type ;

BEGIN…tab_nom(l):='TUNISIE';…END;

3.4. Les enregistrements prédéfinis (record PL/SOL)

La restriction posée par l'utilisation du type %ROWTYPE pour déclarer un enregistrementréside dans le manque de spécification des types de données au niveau de l'enregistrement.

L'implémentation du nouveau type composé nommé RECORD a permis de lever cetterestriction.

3.4.1 La déclaration d'un enregistrement

Comme les tableaux PL/SQL, la déclaration d'un enregistrement se fait en deux étape



Le langage PL/SQL

1. Déclaration du type de l'enregistrement2. Déclaration de la variable du type défini.

On peut déclarer un Type RECORD dans la partie déclarative d'un bloc ou d'un sous-programme en utilisant la syntaxe suivante:

TYPE nom_type IS RECORD

(champ {type_champ | table.colonne%TYPE [NOT NULL],champ (type_champ | table.colonne%TYPE [NOT NULL],...)

nomtype : utilisé ensuite dans la déclaration des enregistrements PL/SQL.Type-champ : type de données comme CHAR, DATE ou NUMBER.

Lorsque le type est déclaré,On peut déclarer des enregistrements de ce type de la façonsuivante :

Nom_erg nom_type ;

3.4.2 L'accès aux champs d'un enregistrementPour accéder à un élément d'une variable de type record, il suffit d'utiliser la syntaxe suivante

Nom_erg.nom_champ

Pour affecter la valeur d'une expression PL/SQL à un élément de l'enregistrement, on doitutiliser la syntaxe suivante :

Nom_erg.nom_champ := expression_pl/sql;

Exemple :

DECLARETYPE ADRESSE IS RECORD(Numero positive,Rue varchar2(35),CodePost char(5),Ville varchar2(25),Pays varchar2(30) );TYPE CLIENT IS RECORD(NumCIi positive,NomCli varchar2(40),Adrcli ADRESSE,CA number(12,3));

monclient CLIENT ;BEGINmonclient.NumCIi := 1234;monclient.NomCIi := ‘Ben HUSSEIN’;monclient.AdrCli.Numero := 10;END ;



Le langage PL/SQL

4 STRUCTRES DE CONTROLE4.1. Les traitements Conditionnels

IF condition_plsqlTHEN commandes

[ELSE commandes][ELSIF condition_plsql

THEN commandes[ELSE commandes]]

END IF;

La condition peut utiliser les variables définies ainsi que tous les opérateurs présents dansSQL =,<,>,<=,>=,<>, IS NULL, IS NOT NULL.Exemple :

DECLAREvjob CHAR(10);vnom employés.ename%type := ‘BEN SALAH’ ;msg CHAR(30) ;

BEGINSelect job into vjob from employés where ename = vnom;

--contrôle de la valeur de vjobif vjob is NULL

then msg := vnom || ‘pas de travail';elsif vjob = ‘Vendeur’ then

UPDATE employés set comm = 100 where ename=vnom ;Msg := vnom || ‘a 100 dinars de commission’ ;

ElseUPDATE employés set comm = 0 where ename=vnom ;Msg := vnom || ‘n’a pas de commission’ ;

End if ;DBMS_OUTPUT.PUT_LINE(msg) ;

commit;END ;4.2 Les Traitements.Répétitifs4.2.1. l’instruction LOOP

LOOP permet de répéter une séquence de commandes. Cette séquence est comprise entre lemot-clé LOOP, indiquant le début d’une boucle et END LOOP, spécifiant sa fin.

Syntaxe :

LOOPinstructions

END LOOP ;



Le langage PL/SQL

Les commandes EXIT,EXIT WHEN condition permettent de quitter la boucle.Exemple :

DECLAREsomme number(10) := 0 ;nombre number(5);BEGINnombre:=0;LOOP

Nombre:=nombre+1 ;somme := somme + nombre ;if nombre>=10then EXIT;end if ;

END LOOP;DBMS_OUTPUT.PUT_LINE(‘la somme des 10 premiers entiers est ‘ || to_char(somme));END;

4.2.2. L'instruction FOR …LOOP

FOR compteur IN [REVERSE] var_debut.. var_fin LOOP

instructions

END LOOP ;

compteur. est une variable de type entier, locale à la boucle. Sa valeur de départ est égale pardéfaut à la valeur de l'expression entière de gauche (var_debut). Elle s'incrémente de 1, aprèschaque traitement du contenu de la boucle, jusqu'à ce qu'elle atteigne la valeur de droite(var_fin).

5. LES CURSEURS EN PL/SQLPour traiter une commande SQL, PL/SQL ouvre une zone de contexte pour exécuter lescommandes et stocker les informations.5.1. DéfinitionLe curseur permet de nommer cette zone de contexte, d'accéder aux informations etéventuellement de contrôler le traitement. Cette zone de contexte est une mémoire de taillefixe, utilisée par le noyau pour analyser et interpréter tout ordre SQL.

5.2. Les types de curseursv Le curseur expliciteIl est créé et géré par l'utilisateur pour traiter un ordre Select qui ramène plusieurs lignes.Le traitement du select se fera ligne par ligne.

v Le curseur impliciteIl est généré et géré par le noyau pour les autres commandes SQL.



Le langage PL/SQL

5.3. Les étapes d'utilisation d'un curseur explicitePour traiter une requête qui retourne plusieurs lignes, l'utilisateur doit définir un curseur quilui permet d’extraire la totalité des lignes sélectionnées.

L’utilisation d'un curseur pour traiter un ordre Select ramenant plusieurs lignes, nécessite 4étapes:

1. Déclaration du curseur2. Ouverture du curseur3. Traitement des lignes4. Fermeture du curseur.

5.3.1. La déclaration d'un curseurLa déclaration du curseur permet de stocker l'ordre Select dans le curseur.La Symtaxe de définition..

Le curseur se définit dans la partie DECALRE d'un bloc PL/SQL.Cursor nomcurseur [(nomparam type [,nomparam type, ...)]IS Commande_SELECT

Exemple :

DeclareCursor DEPT_10 is

select ename, sal from emp where depno = 10;

5.3.2. L'ouverture et la fermeture d’un curseurL'étape d'ouverture permet d'effectuer:1. l'allocation mémoire du curseur,2. l'analyse sémantique et syntaxique de l'ordre3. le positionnement de verrous éventuels (si select for update...)

L'étape de fermeture permet de libérer la place mémoire réservée.

La syntaxe :

OPEN nomcurseur [(nomparam1[,nomparam2 , ...)]/* traitement des lignes */

CLOSE nomcurseur

Exemple :Begin

…OPEN DEPT_10

/* traitement des lignes*/CLOSE DEPT_10



Le langage PL/SQL

5.3.3. Le traitement des lignesIl faut traiter les lignes une par une et renseigner les variables réceptrices définies dans lapartie Declare du bloc.

La syntaxe :

Dans la partie traitement du bloc.PL/Sql, il faut commencer par ouvrir le curseur puis

FETCH nomcurseur INTO {nomvariable [,nomvariable] | nomrecord}

L’ordre fetch ne ramène qu’une seule ligne à la fois. De ce fait il faut recommencer l’ordrepour traiter la ligne suivante.

Exemple :Declare

Cursor DEPT_10 isselect ename, sal from emp where depno = 10;

Vnom emp.name%TYPE ;Vsalaire emp.sal%TYPE ;

BeginOPEN DEPT_10;LOOP

FETCH DEPT10 into vnom,vsalaire ;--Traitement ligne

END LOOP ;CLOSE DEPT_10;

End;5.4 Les attributs d’un curseurLes attributs d’un curseur nous fournissent des informations sur l’exécution d’un ordre. Ilssont conservés par PL/SQL après l’exécution du curseur (implicite ou explicite)

Ces attributs permettent de tester directement le résultat de l’exécution. Ils sont résumés dansle tableau suivant :



Le langage PL/SQL

Curseurs implicites Curseurs explicitesSql%Found Nomcurseur%FoundSql%Notfound Nomcurseur %NotfoundSql%Isopen Nomcurseur %IsopenSql%Rowcount Nomcurseur %RowcountSql%Rowtype Nomcurseur %Rowtype

5.4.1 l’attribut %Found

cet attribut est de type booléen (vrai ou faux)Pour les curseurs implicites, cet attribut est vrai si les instructions insert, update, delete traitentau moins une ligne. Pour les curseurs explicites, il est vrai si le fetch ramène au moins uneligne.Exemple :

DeclareCURSOR num_cur1 IS SELECT num from tab1 ;CURSOR num_cur2 IS SELECT num from tab2 ;Num1 number ;Num2 number ;somme number := 0;

BeginOPEN num_cur1 ;OPEN num_cur2 ;

LOOPFFTCH num_cur1 INTO num1 ;FFTCH num_cur2 INTO num2;IF (num_cur1%Found) AND (num_cur2%Found) THEN

somme:= num1 + num2 ;INSERT INTO sum_tab VALUES (somme);

ELSEExit ;

END IF;END LOOP;

CLOSE numl_cur1 ;CLOSE mun_cur2 ;COMMIT;END;

5.4.2. L'attribut %NotFound

cet attribut est de type booléen (vrai ou faux)Pour les curseurs implicites, cet attribut est vrai si les instructions insert, update, delete netraitent aucune ligne. Pour les curseurs explicites, il est vrai si le fetch ne ramène plus deligne.



Le langage PL/SQL

Exemple :

DeclareCURSOR num_cur1 IS SELECT num from tab1 ;CURSOR num_cur2 IS SELECT num from tab2 ;Num1 number ;Num2 number ;somme number := 0;

BeginOPEN num_cur1 ;OPEN num_cur2 ;

LOOPFFTCH num_cur1 INTO num1 ;FFTCH num_cur2 INTO num2;EXIT WHEN (num_cur1%NotFound) OR (num_cur2%NotFound) ;somme:= num1 + num2 ;INSERT INTO sum_tab VALUES (somme);END LOOP;CLOSE numl_cur1 ;CLOSE mun_cur2 ;END;

5.4.3. L'attribut %IsOpenCet attribut est de type booléen soit vrai, soit faux.Le curseur implicite est toujours faux car Oracle referme toujours les curseurs qu'il ouvreaprès chaque utilisation.Le curseur explicite est vrai si le curseur est ouvert.Exemple :

DeclareCursor Dept10 is

Select ename, sal from emp where deptno=10;BeginIf not (Dept10%lsopen) then

open Dept10 ;End if,Fetch Dept10 into …--traitementEnd ;

5.4.4.'L'attribut %RowCount

Cet attribut est de type numérique. Le curseur implicite indique le nombre de lignes traités parles ordres insert, update, delete.Le curseur explicite est incrémenté à chaque ordre fetch, donc cet attribut traduit la nièmeligne traitée.


Le langage PL/SQL

Exemple

Declarecursor C1 isselect ename, empno, sal from emp order by sal desc ;nom char(10);numero number(4);salaire number(7,2);BeginOpen Cl;Loop .Fetch cl into nom, numéro, salaire ;exit when (c1%rowcount > 25) or (cl%notfound);insert into temp values (salaire, numéro, nom);

End Loop;Close C1 ;commit ;End;

5.4.5. L'attribut %Rowtype

Cet attribut permet la déclaration implicite d'une structure dont les éléments sont d'un typeidentique aux colonnes ramenées par le curseur.

Syntaxe :Dans la partie déclarative du bloc.

CURSOR nomcurseur IS ordre select;nomrecord nomcurseur%Rowtype;

Les éléments de la structure sont identifiés par:nomrecord.nomcolonneLa structure est renseignée par le Fetch : Fetch nomcurseur into nomrecord;

Exemple

Declare/* la fonction nvl permet de tester la valeur Null d'une colonne si comm=NULL alors nvlretourne 0 sinon nvl retourne la valeur de comm */

cursor c1 is select sal + nvl(comm) saltot, ename from emp;/* l'enregistrement comporte deux colonnes: saltot, enamec1_record c1%Rowtype;

Beginopen c1;LoopFetch c1 into c1_record;

Exit when c1 %notfound ;



Le langage PL/SQL

if c1_record.saltot > 2000 theninsert into temp values (c1_record.saltot, c1_record.ename);

end if,End Loop;close c1;End;

5.5. Les boucles et les curseursL'objectif est de fournir au programmeur une structure simple et efficace pour utiliser lesstructures de boucle et les curseurs.

DeclareCursor nomcurseur is ordre_select ;BeginFor nomrecord in nomcurseur Loop

/* traitementEnd Loop;End;

Le bloc PL/SQL ci-dessus permet d'obtenir une génération implicite de la structure suivante :

DeclareCursor nomcurseur is ordre_select;nomrecord nomcurseur%rowtype;BeginOpen nomcurseur ;LoopFetch nomcurseur into nomrecord ;Exit when nomcurseur%notfound ;/* traitementEnd Loop;Close nomcurseur;End;

5.6. Le curseur paramètreIl permet d'utiliser des variables dans le curseur. Principalement dans la clause where.Il faut pour cela spécifier les noms et les types des paramètres dans la déclaration du curseur.

Cursor nomcurseur (paraml type, param2 type,...)is select ordre_select ,

L'ordre_select utilise les paramètres.Les types possibles sont : char, number, date, boolean sans spécifier la longueur.

BeginOpen nomcurseur (valeurl , vaieur2, ....) ;



Le langage PL/SQL

Exemple :

Declarecursor c1 (depart number)is select sal, nvl(comm,0) commifrom emp where deptno=depart ;

total number(11,2) := 0 ;sal_sup number(4):=0 ;comm_sup number(4):=0;

Beginfor c1_rec in c1(20) Loop total:= total+ c1_rec.sal + c1_rec.commi ; if c1_rec.sal > 4000then sal_sup := sal_sup + 1 ;end if ;if c1_rec.commi > 3000then commi_sup:=commi_sup+1 ;end if;End Loop;insert into tempvalues (sal_Sup, comm_sup, 'total salaire’||to_Char(total));commit ;End;

5.7. La clause "current of…"Cette clause permet d'accéder directement à la ligne ramenée par l'ordre Fetch afin de latraiter (update, delete).

v Il faut se réserver la ligne lors de la déclaration du curseur par le positionnement d'unverrou d'intention : (For update of nom_colonne)

v Il faut spécifier que l'on veut traiter la ligne courante au Fetch par la clause :(Wherecurrent of nom_curseur)

Exemple :

DeclareCursor cl is select ename, sal from emp

for update of sal ;BeginFor c1_record in c1 Loop

If c1_ record.sal > 1500 theninsert into resultat values (c1_record.sal, c1_record.sal*1.3, c1_record.ename);update emp set sal = sal * 1.3 where current of c1 ;

end if,End loop ;Commit;End ;


Le langage PL/SQL

6 Utilisation de sous-programmesPL/SQL accepte aussi l’utilisation de sous-programmes, nommés procédures et fonctions.Une procédure PL/SQL exécute certaines actions et peut accepter des paramètres optionnels.Une fonction PL/SQL retourne une valeur d’un certain type et peut aussi accepter desparamètres optionnels.

6.1 Déclaration d’une procédureLa syntaxe de déclaration d’une procédure est :

PROCEDURE nom_procédure [(paramètre1 … [, pramètreN ] IS[déclaration des variables locales]

BEGINInstructions PL/SQL[section_exception ]END [nom_procédure]

• Paramètre1 à paramètreN représentent la déclaration des paramètres de la procédure sousla forme : nom_paramètre [IN|OUT|IN OUT] type_données [ { :=|default } valeur].

• déclaration des variables locales représentent les déclarations optionnelles de variables,de constantes, de fonctions et de procédures locales à la procédure.

• section_exception est la section optionnelle de gestion des exceptions de la procédure.

Exemple :

DeclareVpatient_ID Patient.Patient_ID%type ;

Fièvre_elevée constant real := 42 ;

Procedure enregistrer_Temp_Deg_C_Patient (patient_ID varchar2,Temp_Corps_Deg_C real) is

Temp_Deg_F real ;Begin

Temp_Deg_F := (9/5)* Temp_Corps_Deg_C + 32 ;Insert into Patient (Patient_ID, Temp_Corps_Deg_F)Values(patient_ID, Tem_Deg_F) ;Commit ;

End ;Begin

Vpatient_ID := ‘ES8888’ ;enregistrer_Temp_Deg_C_Patient (Vpatient_ID, Fièvre_elevée) ;

End ;



Le langage PL/SQL

6.2 Déclaration d’une fonctionLa syntaxe de déclaration d’une fonction est :

FUNCTION nom_fonction[(paramètre1 … [, pramètreN ]RETURN type_données_fonction IS

[déclaration des variables locales]BEGINInstructions PL/SQLRETURN val_à_retournée[section_exception ]END [nom_fonction]

Exemple :

Soit l’extrait suivant d’une base de données relationnelle d’une application de gestion descolarité d’une université :Etudiant(codetu, nometu,adretu)Matière(codmat, description_mat, coefmat)Moyenne(codetu,codmat, moyenne_mat)

La fonction meilleur_étu_mat retourne le code du meilleur étudiant d’une matière donnée.

DeclareCodmatière Matière.codmat%type;Vcode_etu etudiant.codetu%type;

FUNCTION meilleur_étu_mat (pcodmat varchar2) RETURN varchar2 ISVcodetu etudiant.codeetu%type ;BEGIN

Select codetu into VcodetuFrom Moyenne MWhere M.moyenne_mat in (Select max(moyenne_mat from Moyenne M

Where M.codmat=pcodmat);Return Vcodetu;

End ;Begin

Codmatière := ‘M001’;Vcode_etu := meilleur_étu_mat(Codmatière);DBMS_OUTPUT.PUT_LINE(‘Le meilleur étudiant de la matière’ || Codmatière || ‘est

: ‘ || Vcode_etu);End ;


Le langage PL/SQL

6.3 Paramètres de procédure et fonctionUn ou plusieurs paramètres peuvent être passés à une procédure ou à une fonction, de troisfaçons : IN, OUT ou IN OUTExemple :

DeclareParam1 number ;Param2 number ;Param3 number ;

Procedure Différents_Param(P1 IN number, P2 OUT number, P3 IN OUT number) IsBegin

P2 := P1 ;P3 := P3 + 1 ;

END ;Begin

Param1 := 3.14 ;Param3 := 15 ;Différents_Param(Param1, Param2, Param3) ;DBMS_OUTPUT.PUT_LINE('param1 = ' || to_char(param1) || 'param2 = ' ||

to_char(param2) ||'param3 = ' || to_char(param3) );

End ;

7. GESTION DES ERREURSLe mécanisme de gestion d'erreurs dans PL/SQL est appelé gestionnaire des exceptions.Il permet au programmeur de planifier sa gestion et d'abandonner ou de continuer letraitement en présence d'une erreur.Il faut affecter un traitement approprié aux erreurs apparues dans un bloc PL/SQL.C'est pourquoi on distingue 2 types d'erreurs ou d'exceptions :

1. Erreur interne Oracle (Sqlcode <= 0) : dans ce cas la main est rendue directement ausystème environnant.

2. Anomalie déterminée par 1'utilisateur.

La solution :1. Donner un nom à l'erreur (si elle n'est pas déjà prédéfinie),2. Définir les anomalies utilisateurs, leur associer un nom,3. Définir le traitement à effectuer.

7.1. Les exceptions internesUne erreur interne est produite quand un bloc PL/SQL viole une règle d'Oracle ou dépasseune limite dépendant du système d'exploitation. Les erreurs Oracle générées par le noyau sontnumérotées, or le gestionnaire des exceptions de PL/SQL, ne sait que gérer des erreursnommées.Pour cela PL/SQL a redéfini quelques erreurs Oracle comme des exceptions. Ainsi, pour gérerd'autres erreurs Oracle, 1'utilisateur doit utiliser le gestionnaire OTHERS ouEXCEPTION_INIT pour nommer ces erreurs.



Le langage PL/SQL

Les exceptions fournies par Oracle sont regroupées dans ce tableau :

Nom d'exception Valeur SqlCode Erreur OracleCURSOR_ALREADY_OPEN -6511 ORA-06511

DUP_VAL_ON_INDEX -1 ORA-00001INVALID_CURSOR -1001 ORA-01001INVALID_NUMBER -1722 ORA-01722

LOGIN_DENIED -1017 ORA-01717NO_DATA_FOUND -1403 ORA-01413NOT_LOGGED_ON -1012 ORA-01012PROGRAM_ERROR -6501 ORA-06501STORAGE_ERROR -6500 ORA-06500

TIMEOUT_ON_RESOURCE -51 ORA-00051TOO_MANY_ROWS -1422 ORA-01422

TR.ANSACTION_BACKED_OUT -61 ORA-00061VALUE_ERROR -6502 ORA-06502ZERO_DIVIDE -1476 ORA-01476

OTHERS : toutes les autres erreurs non explicitement nommées.

Pour gérer les exceptions le programmeurdoit écrire un gestionnaire des exceptions qui prendle contrôle du déroulement du bloc PL/SQL en présence d'une exception.Le gestionnaire d'exception fait partie du bloc PL/SQL et se trouve après les commandes Ilcommence par le mot clé EXCEPTION et se termine avec le même END du bloc.Chaque gestion d'exception consiste à spécifier son nom d'erreur après la clause WHEN et laséquence de la commande à exécuter après le mot clé THEN, comme le montre l'exemplesuivant:

DECLAREWsal emp.sal%type;BEGINselect sal into wsal from emp;

EXCEPTIONWHEN TOO_MANY_ROWS then…

--gérer erreur trop de lignesWHEN NO_DATA_FOUND then…

--gérer erreur pas de ligneWHEN OTHERS then…

--gérer toutes les autres erreursEND ;

Remarques :

ü L'exception optionnelle OTHERS est toujours située à la fin des exceptions.ü Pour rattacher une séquence de commandes à plus d'une exception, l'utilisateur peut

utiliser l'opérateur booléen OR comme suit :WHEN erreurl OR erreur2 THEN-- gérer erreurl2



Le langage PL/SQL

7.2. Les exceptions utilisateur (externes)PL/SQL permet à l'utilisateur de définir ses propres exceptions.La gestion des anomalies utilisateur peut se faire dans un bloc PL/SQL en effectuant lesopérations suivantes:

1. Nommer l'anomalie (type exception) dans la partie Déclare du bloc.

DECLARENom_ano Exception;

2. Déterminer l’erreur et passer la main au traitement approprié par la commande Raise.

BEGIN…If (condition_anomalie) then raise Nom_ano3. Effectuer Ie traitement défini dans la partie EXCEPTION du Bloc.

EXCEPTIONWHEN (Nom_ano) then (traitement);

Exemple :

DECLAREwsal emp.sal%type ;sal_zero Exception ;BEGINSelect sal into wsal from emp where empno=5;If wsal=0 then

Raise sal_zero;

EXCEPTIONWHEN sal_zero then…

-- gérer erreur salaireWHEN TOO_MANY_ROWS then...

--gérer erreur trop de lignesWHEN_NO_DATA_FOUND then ...

-- gérer erreur pas de ligneWHEN OTRERS then ...

--gérer toutes les autres erreursEND;

Le programmeur Peut utiliser les fonctions Sqlcode et Sqlerrm pour coder les erreurs Oracleen Exception.sqlcode est une fonction propre à PL/SQL qui retourne le numéro (généralement négatif)de l'erreur courante.Sqlerrm reçoit en entrée le numéro de l'erreur et renvoie en sortie le message de l'erreur codésur196 octets.



Documents

Cours Pl SQL