Le Langage PL/SQL est une extension du SQL, qui offre un environnement procédural au langage SQL .Il permet entre autres:

L'utilisation d'un sous ensemble du SQL

La mise en œuvre de structures procédurales

L'optimisation de l'exécution des requêtes.

PL/SQL :

Un programme PL/SQL est constitué d'un ou de plusieurs blocs. Chaque bloc comporte 3 sections :

-La section Déclaration, introduite par le mot clé declare contient la déclaration des structures et des variables utilisés dans le bloc.

- La section Corps du bloc, introduite par le mot clé begin contient les instructions du programme et éventuellement la section de traitement d'erreurs. Elle se termine par le mot clé end.

- La section de traitements d'erreurs , introduite par le mot clé Exception contient les instructions de traitement d'erreurs. Cette section est facultative.

DECLARE Déclarations des variables locales au bloc, constantes, et

curseurs

BEGIN Instructions PL/SQL et SQL Possibilités de blocs imbriqués

EXCEPTION Traitement des erreurs

END;

Gestion des données :

Deux classes de types de données : scalaire et composé

Scalaire : Char, varchar2, Date, number, boolean, smallint,….

Deux manières pour déclarer :

- directe : nom_var type_var ; Ex : i smallint;

- par référence à une colonne de table :

nom_var nom_table.nom_colonne%type;

Ex : f produit.reference%type;

Types composés : enregistrement et table

+ Enregistrement : RECORDDéclaration :

- par référence à une structure de table : Nom_var nom_table%rowtype ;

- par énumération des rubriques, dans ce cas il faut déclarer le type enregistrement :

TYPE nom_type IS RECORD ( nom_champ type_champ ,……..);

puis déclarer la variable :

nom_variable nom_type;

+ Table : structure d'éléments d'un même type scalaire.

L'accès à un élément se fait grâce à un indice déclaré de type BINARY_INTEGER ;

Déclaration en deux étapes :

- déclarer le type :

TYPE nom_type IS TABLE OF type_champ INDEX BY BINARY_INTEGER ;

- puis la variable :

nom_var nom_type;

* Définir une constante : nom_var CONSTANT type := valeur;

Affectation d'une valeur à une variable :

- Affectation : := nom_var := valeur ;

table_nom(i) := 'DRISS' ; revenu.salaire := 10000;

- Valeur résultat d'une requête :

L'utilisation de la clause INTO de l'ordre SELECT permet d'affecter à une variable le résultat d'une requête. Cet ordre ne doit retourner qu'une seule valeur sinon il faut utiliser un curseur.

SELECT liste_expressions into liste_variables from ……….;

Les Instructions de contrôle :

IF condition Then Instructions;

ELSE Instructions;

END IF ;

Instruction conditionnelle :

SELECT liste_expressions into liste_variables from ……….;

Instructions itératives :

LOOP …. IF EXIT ; END IF ; …… END LOOP ;

WHILE condition LOOP …. …. END LOOP;

FOR indice IN valdebut ..Valfin LOOP ….. …..

END LOOP;

Instructions d’affichage :

Pour afficher un résultat à l’écran, on utilise l’instruction suivante :

Dbms_output.put_line ( ‘message ..’||nom_var||…………);

Remarque : l’affichage à l’écran par cette instruction nécessite l’activation du ‘serveur ‘ d’affichage par l’instruction :

set serveroutput on ;

set serveroutput on;declare nombre number;begin nombre := 0; select count(numlicence) into nombre from triathlete

where lower(categorie) = 'junior'; if nombre > 5 then dbms_output.put_line (' les jeunes sont nombreux, ils sont : '||nombre||' !'); else dbms_output.put_line (' les jeunes ne sont que : '||nombre||' !'); end if;end;

Exercice : Ecrire le programme PL/SQL qui permet de déterminer le nombre de triathlètes de la catégorie junior et d’afficher les messages suivants :

Si ce nombre > 5 :afficher les jeunes sont nombreux, ils sont : xx !

sinon afficher les jeunes ne sont que : xx !

Les Curseurs :

Dès l'instant où on exécute une instruction SQL, il y a création d'un curseur. Le curseur est une zone de travail de l'environnement utilisateur qui contient les informations permettant l'exécution d'un ordre SQL :

- texte source de l'ordre SQL- forme "traduite" de l'ordre- tampon correspondant à une ligne résultat- statut- information de travail- information de contrôle

L'utilisation d'un curseur est nécessaire pour traiter un ordre SELECT renvoyant plusieurs lignes.

Une telle utilisation nécessite 4 étapes :1. Déclaration du curseur

2. Ouverture du curseur

3. Traitements des lignes

4. Fermeture du curseur

Exemple : precedent

DECLARE CURSOR C1 is select designation from produit where prix <10 ; CURSOR C2 (Q number(4)) is select designation from produit

where quantité >Q ;

1 - Déclaration : dans la section DECLARE par la clause CURSOR :

DECLARE CURSOR nom_curseur IS requête ;

Un curseur peut être défini à l'aide de paramètres :

CURSOR nom_curseur ( nom_param type,……) IS ……….. ;

2 – Ouverture du curseur :

OPEN nom_curseur;

OPEN nom_curseur ( paramètre );open C2(30);

3 – Fermeture du curseur :

Close nom_curseur ;

4 – Traitement des lignes : Les lignes obtenues par l'exécution de la requête SQL sont

distribuées une à une, par l'exécution d'un ordre FETCH inclus dans une structure répétitive. Pour chaque ligne, cette instruction transfère les valeurs des attributs projetés par l'ordre SELECT dans des variables PL/SQL :

FETCH nom_curseur INTO liste_var ;Ou

FETCH nom_curseur into nom_enreg ;

Exemple : DECLARE CURSOR C1is select nom, sal from employes; V_nom employes.nom%type; V_sal employes.sal%type; BEGIN Open C1; LOOP FETCH c1 into V_nom, V_sal ; Exit when (c1%Notfound); // traitement END LOOP; Close C1; END;

Modification des Données :

PL/SQL offre la possibilité de modifier ou de supprimer la ligne distribuée par la commande FETCH, en utilisant dans une clause : WHERE CURRENT OF nom_curseur

Dans ce cas, la déclaration du curseur doit inclure la clause FOR UPDATE.

Exemple :DECLARE

CURSOR C2 is select nom, sal from employes where n_emp >15 FOR UPDATE;

V_nom employes.nom%type; V_sal employes.sal%type;

BEGIN Open C2; LOOP FETCH c2 into V_nom, V_sal ; Exit when (c2%Notfound); If v_sal <10000 then Update employés set sal = v_sal*1.2 where current of c2; End if; END LOOP;END;

Exercice :Ecrire le prg PL/SQL qui permet de modifier la distance à parcourir pour des

triathlètes dont le numéro de licence est > à 400.

Procédures et fonctions stockées

C’est un programme écrit en PL/SQL , qui peut être appelé :

- en mode interactif

- dans une application

- dans d’autres procédures ou dans des déclencheurs

Structure d’une procédure :

Create or replace procedure nom_proc (argument1 mode type_argument1,……)

[is | as]

bloc ;Remarque : mode définit le type de l’argument :

- IN : argument en entrée

- OUT : argument en sortie

- IN OUT : argument en entrée sortie

Exercice :

Ecrire la procedure PL/SQL qui permet d’afficher, les informations d’un triathlète donné par son numéro mais en les affichant ligne par ligne .

Structure d’une fonction :

Create or replace function nom_func (argument1 mode type_argument1,……)

return type_retourné

[is | as]

bloc ;

Remarque : la fonction comporte une instruction obligatoirement dans le bloc une instruction return qui renvoie la variable résultat de la fonction.

return (nom_var_résultat);

Exemple de procédure : ajouter un triathlète :

create or replace procedure nv_triathlete ( nlic IN triathlète.numlicence%type,

nomat IN triathlète.nomathlète%type,

cat IN triathlète.categorie%type)

is

begin

insert into triathlete values( nlic,nomat,cat);

commit work;

end nv_triathlete;

Exemple de fonction : retourner le nombre de triathlètes d’une catégorie donnée :

create or replace function nb_categorie ( cat IN triathlète.categorie%type) return integeris nb integer; begin

select count(numlicence) into nb from triathlete where categorie =cat;return nb;

end nb_categorie;

Appels et utilisation des procédures et fonctions :

-En mode interactif :

EXECUTE nom_proc(param1, param2,…);

EXECUTE :var_locale := nom_fonction(param1,param2,….);

Exemple :

execute nv_triathlete(450,’hamidi’,’senior’);

execute :nbr := nb_categorie(‘senior’);

-A partie d’un bloc PL/SQL :

nom_proc(param1, param2,…);

nom_fonction(param1,param2,….);

Exemple :

begin

nv_triathlete(458,’hamida’,’junior’);

nbr := nb_categorie(‘senior’); ……..end;

-A partir d’un autre schéma :

SQL>EXECUTE nomschema.nomproc(param1, param2,…);

Exercice :

Ecrire la procedure PL/SQL qui permet d’afficher, les informations d’un triathlète donné par son numéro mais en les affichant ligne par ligne .

Gestion des erreurs :

IL s’agit d’effectuer un traitement approprié aux erreurs qui apparaissent lors de l’exécution d’un bloc PL/SQL.

Les erreurs sont de deux types :

- les erreurs standards détectées par le moteur PL/SQL. Dans ce cas, il y a erreur avec un code ORA XXXXX et le système reprend la main.

- les anomalies générées par l’utilisateur.

Gestion des erreurs standards :

La procédure de traitement se définit dans la section Exception:

……….

Exception

when nom_erreur then

// traitement erreur

end;

LISTE D’ERREURS :

NO_DATA_FOUND

DUP_VAL_ON_INDEX

VALUE_ERROR

INVALID_CURSOR

INVALID_NUMBER

PROGRAM_ERROR

LOGIN_DENIED

TOO_MANY_ROWS

ZERO_DIVIDE

Gestion des erreurs utilisateurs:

-Il faut déclarer l’exception :

DECLARE

NOM_ANOMALIE EXCEPTION.

-Il faut la traiter : idem que les Exceptions standards

-Il faut la déclencher :

Une exception utilisateur doit explicitement être déclenchée dans la procédure PL/SQL par l’ordre RAISE:

if ………..then

RAISE NOM_ANOMALIE ;