Embed Size (px)
DESCRIPTION
PLSQL
Citation preview
PL-SQL-Support-TP
Partie1:PL-SQL
Support de TP
PL-SQL-Support-TP
I. Le langage PL/SQL
Introduction
PL-SQL-Support-TP
1.1 Présentation
-Le langage PL/SQL (Procedural language/SQL) est une extension du langage SQL
qui offre un environnement procédural au langage SQL.
-Dans l’environnement PL/SQL, les ordres SQL et PL/SQL sont regroupés en blocs. Un
bloc ne demande qu’un seul transfert vers le moteur PL/SQL qui interprète en une seule
fois l’ensemble des commandes contenues dans le bloc.
PL-SQL-Support-TP
1.2 Structure de Bloc
Un programme ou une procédure 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 description des structures et des variables utilisées
dans le bloc. Cette section est facultative ;
• La section corps du bloc, introduite par le mot clé BEGIN,
contient les instructions du programme ainsi que la section de
traitement des erreurs. La section corps du bloc est obligatoire et
se termine par le mot clé END ;
• La section traitement des erreurs, introduite par le mot clé
EXCEPTION, contient les instructions de gestion des erreurs.
Cette section est facultative.
PL-SQL-Support-TP
1.2 Structure de Bloc
PL-SQL-Support-TP
2.Gestion des données
• Pour réaliser des traitements, nous avons besoin d’utiliser des
variables.
• Ces variables servent notamment au stockage des données
obtenues depuis la base par exécution d’une requête SQL ou
utilisées comme paramètres dans les ordres de LMD.
• Toute variable utilisée dans un bloc doit être déclarée dans une
section DECLARE.
• Deux classes de types de données existent dans le langage
PL/SQL : composés et scalaire.
PL-SQL-Support-TP
2.1 Types scalaires
Outre les types CHAR, VARCHAR2, NUMBER et DATE, disponibles dans le langage SQL, le langage PL/SQL offre les types supplémentaires suivants :
– BOOLEAN
– BINARY_INTEGER
– DECIMAL
– FLOAT
– INTEGER
– REAL.
PL-SQL-Support-TP
2.1 Types scalaires
• La déclaration d’une variable se fait par association du nom de
la variable à un type sous la forme :
nom_variable type;
Exemple: v_nompilote varchar2(25);
• Il est aussi possible de déclarer une variable par référence à une
colonne d’une table, par la notation %type :
nom_variable nom_table.nom_colonne%type;
Exemple: v_nompilote pilote.nom%type;
PL-SQL-Support-TP
2.2 Types composés
PL/SQL offre deux types composés :
*enregistrement (RECORD)
*et table (TABLE).
PL-SQL-Support-TP
2.2 Types composés
(1) Enregistrement
Déclaration : La déclaration de variable de ce type se fait:
· Soit par référence à une structure de table, en utilisant la notion
%ROWTYPE.
Nom_varibale nom_table%rowtype;
Exemple:info_pilote pilote%rowtype;
PL-SQL-Support-TP
Soit par énumération des rubriques qui la composent. Dans ce cas, ladéclaration se fait en 2 étapes :
1-Déclaration du type enregistrement :
TYPE nom_type IS RECORD (nom_champ type_champ…) ;
Exemple: TYPE t_pilote IS RECORD(nom_pilote pilote.nom%type; revenu_pilote number(8,2)) ;
2- Déclaration de la variable de type enregistrement :
nom_variable nom_type ;
Exemple : TYPE t_pilote IS RECORD (nom_pilote pilote.nom%type ; revenu_pilote number(8,2)) ;
Employe t_pilote ;
2.2 Types composés
(1) Enregistrement
PL-SQL-Support-TP
2.2 Types composés
(2) Table
– Le type table représente une structure composée d’éléments d’un
même type scalaire.
– L’accès à un élément de la table s’effectue grâce à un indice, ou clé
primaire déclarée de type BINARY_INTEGER.
– La déclaration de variable de ce type se fait en 2 étapes :
1-Déclaration du type de l’élément de la table
2-Déclaration de la variable de type table
PL-SQL-Support-TP
1-Déclaration du type de l’élément de la table :
TYPE nom_type IS TABLE OF type_champ INDEX BYBINARY_INTEGER;
2-Déclaration de la variable de type table :
nom_variable nom_type ;
Exemple :
TYPE t_nom IS TABLE OF CHAR(35) INDEX BY BINARY_INTEGER ;
Table_nom t_nom ;
2.2 Types composés
(2) Table
PL-SQL-Support-TP
2.3 Initialisation des variables
Il est possible:
• d’attribuer une valeur initiale à une variable au moment de sadéclaration :
nom_variable type := valeur ;
Exemple: salaire pilote.sal%type :=250.00;
• de définir une valeur constante par :
nom_variable type DEFALUT valeur ;
Expl: v_comm pilote.comm%type DEFAULT 0;
ou bien
nom_variable CONSTANT type := valeur ;
Expl: v_comm CONSTANT pilote.comm%type := 0.25;
PL-SQL-Support-TP
3. Affectation d‟une valeur à une
variable
• Trois moyens existent pour affecter une valeur à
une variable :
– L’opérateur d’affectation
– Valeur résultat d’une requête
– L’ordre FETCH (Voir Cours prochain)
PL-SQL-Support-TP
3.1 L‟opérateur d‟affectation
• Pour les types simples l’affectation est faite de la façon suivante
nom_variable := valeur ;
• Pour les types composés l’affectation diffère selon le type de cettevariable record ou table.
*Record : nom_variable.nom_champ
Exemple: employe.nom_pilote:=’tarkani’;
employe.revenu_pilote :=3000 ;
*Table : nom_variable(valeur clé primaire)
Exemple : Table_nom(3) :=’tarkani’ ;
PL-SQL-Support-TP
3.2 Valeur résultat d’une requête
• L’utilisation de la clause INTO de l’ordre SELECT permetd’affecter à une variable le résultat d’une requête.
• Il est à noter que cet ordre SELECT n’est utilisable que si l’onest sûr qu’il retourne un seul tuple ; sinon il faut utiliser lescurseurs (Voir cours prochain).
• Syntaxe : SELECT liste d’expressions
INTO liste de variables
FROM….
Exemple :
PL-SQL-Support-TP
4. Instructions de contrôle
• Comme tout langage de programmation, le
PL/SQL offre des structures de contrôle:
– Alternatives
– Répétitives.
PL-SQL-Support-TP
4.1. Structure alternative
• Cette structure permet l’exécution d’une séquenced’instructions sous le contrôle d’une condition. 3 formesexistent :
PL-SQL-Support-TP
4.2 Structures répétitives
• LOOP répète indéfiniment une séquence d’instructions
• FOR contrôle le nombre d’exécutions des instructions de la structure répétitive par
incrémentation et test d’une variable indice. Le pas ne peut être égal qu’à 1.
FOR variable_indice IN [REVERSE] valeur_debut .. valeur_fin
LOOP
Instructions;
END LOOP;
•WHILE répète les instructions de la structure répétitive tant que la condition
a la valeur VRAI.WHILE condition
LOOP
Instructions;
END LOOP;
PL-SQL-Support-TP
Remarques• Pour afficher un commentaire:
DBMS_OUTPUT.PUT_LINE(‘texte_mess’);
• Pour afficher le contenu d’une variable on utilise:
DBMS_OUTPUT.PUT_LINE(nom_var);
• L’affichage d’un commentaire suivi de l’affichage du contenu d’une variable:
DBMS_OUTPUT.PUT_LINE(‘texte_mess’ || nom_var);
• L’affichage ne sera visualisé que lorsque l’ordre SQL suivant est exécuté dans l’environnement SQL+:
SQL> SET SERVEROUTPUT ON;
PL-SQL-Support-TP
II. Le langage PL/SQL
Les curseurs
PL-SQL-Support-TP
1. Présentation
-Dans le cas ou l’ordre SELECT renvoie plusieurs lignes, on est amené àdéfinir des curseurs.
-Exemple: Écrire un bloc PL-SQL permettant d‟afficher les nomsdes pilotes dont les salaires sont > à 1200DT.
Select nom
Into v_nom
Where sal>1200;
-L’utilisation d’un curseur pour traiter un ordre SELECT renvoyantplusieurs lignes nécessite les 4 étapes suivantes :
1. Déclaration du curseur
2. Ouverture du curseur
3. Traitement des lignes
4. Fermeture
Cette solution n‟est plus valable
parce qu‟on peut pas affecter
plusieurs Vals à une seule variable.
PL-SQL-Support-TP
2. Déclaration d‟un curseur
Cette étape permet de définir la requête SELECT et de l’associer à un
curseur
Syntaxe :
CURSOR nom_curseur IS requête ;
Exemple :
CURSOR C1 IS SELECT nom FROM pilote WHERE sal<1230 ;
PL-SQL-Support-TP
2. Déclaration d‟un curseur
• Le curseur peut être aussi défini à l’aide de paramètres. Ces paramètres peuvent être utilisé
• dans une condition de sélection, dans une expression et comme critère de la clause ORDER BY
Syntaxe :
CURSOR nom_curseur (nom_paramètre type [ :=valeur par défaut] [,…]) IS requête ;
Exemple :
DECLARE
CURSOR C2(psal NUMBER(8,2), pcom NUMBER(8,2))
IS SELECT nom
FROM pilote
WHERE sal<psal AND comm> pcomm ;
PL-SQL-Support-TP
3. Ouverture et fermeture d‟un curseur
• L’ordre OPEN permet d’allouerun espace mémoire au curseur :
Syntaxe :
OPEN nom_curseur ;
OPEN nom_curseur (paramètres effectifs);
Exemple :
OPEN C1;
OPEN C2(1200,2500);
OPEN C2(pcomm=>2500, psal=>1200);
L’ordre CLOSE libère la
place mémoire.
Syntaxe :
CLOSE nom_curseur ;
Exemple:
CLOSE C1;
PL-SQL-Support-TP
4. Traitement des lignes
• Les lignes obtenues par l’exécution de la requête SQL sontdistribuées une à une, par l’exécution d’un ordre FETCH inclusdans une structure répétitive.
• Pour chaque ligne, cette instruction transfère les valeurs desattributs projetés par l’ordre SELECT dans les variables PL/SQL.
Syntaxe :
FETCH nom_curseur INTO liste des variables;
Ou bien
FETCH nom_curseur INTO nom_enregistrement;
PL-SQL-Support-TP
5.Exemple:
DECLARE
CURSOR C3 IS SELECT nom, sal FROM pilote where sal>22000;
V_nom pilote.nom%type;
V_sal pilote.sal%type;
BEGIN
OPEN C3;
LOOP
FETCH C3 INTO v_nom, v_sal;
EXIT WHEN C3%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Nom: ' || v_nom ||' Salaire: ' || v_sal);
END LOOP;
CLOSE C3;
END;
Écrire un bloc PLSQL permettant d’afficher les noms et les salaires
de tous les pilotes, dont les salaire dépassent 22000.
Methode1: Utilisation d‟un type scalaire
PL-SQL-Support-TP
DECLARE
TYPE t_pil IS RECORD
(v_nom pilote.nom%type,
v_sal pilote.sal%type);
r_pil t_pil;
CURSOR C3 IS SELECT nom,sal FROM pilote where sal>22000;
BEGIN
OPEN C3;
LOOP
FETCH C3 INTO r_pil;
EXIT WHEN C3%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Nom: ' || r_pil.v_nom ||' Salaire: ' ||r_pil.v_sal);
END LOOP;
CLOSE C3;
END;
5.Exemple:Methode2: Utilisation d‟un type composé enregistrement
PL-SQL-Support-TP
DECLARE
CURSOR C3 IS SELECT nom, sal FROM pilote wheresal>22000;
r_pil C3%ROWTYPE;
BEGIN
OPEN C3;
LOOP
FETCH C3 INTO r_pil;
EXIT WHEN C3%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Nom: ' || r_pil.nom ||' Salaire:' || r_pil.sal);
END LOOP;
CLOSE C3;
END;
5.ExempleMethode3: Utilisation d‟un type composé faisant réf à une structure d‟une table
PL-SQL-Support-TP
6. Forme syntaxique condensée
• Il existe une autre façon qui permet d’ouvrir un curseur,traiter les lignes renvoyées par ce curseur, ainsi que lefermer.
• Cette façon s’appelle la forme condensée et utilise lastructure FOR.
• Cependant, la déclaration se fait de la même façon queprécédemment.
PL-SQL-Support-TP
6. Forme syntaxique condenséeExemple:
DECLARE
CURSOR C3 IS SELECT nom, sal FROM pilote where sal>22000;
V_nom pilote.nom%type;
V_sal pilote.sal%type;
BEGIN
FOR i IN C3
LOOP
FETCH C3 INTO V_nom,V_sal;
DBMS_OUTPUT.PUT_LINE('Nom: ' || v_nom ||' Salaire: ' || v_sal);
END LOOP;
END;
PL-SQL-Support-TP
7. Statut d‟un curseur
PL-SQL-Support-TP
III. Le langage PL/SQL
La Gestion Des Erreurs
PL-SQL-Support-TP
1. Présentation• Une exception est une erreur qui survient durant une exécution.
• 2 types d’exceptions:
– Prédéfinies par ORACLE
– Définie par le programmeur
Syntaxe:
PL-SQL-Support-TP
1.1 Prédéfinies par ORACLE
Exemple d’exception prédéfinies par ORACLE:
PL-SQL-Support-TP
1.1 Prédéfinies par ORACLE
• Exemple:
PL-SQL-Support-TP
1.2 Définies par l’utilisateur
Exemple:
PL-SQL-Support-TP
IV. Le langage PL/SQLFonction-Procédure
PL-SQL-Support-TP
1. Présentation
• L’utilisation des procédures ou de fonctionsstockées permet d’enregistrer et d’exécuter destraitements fréquemment utilisés au niveau dunoyau du SGBDR plutôt que dans chaqueapplication.
• Un seul exemplaire du traitement est donc définiet stocké dans la base et il est exécutable, en modepartagé, par toutes les applications qui y fontréférence.
PL-SQL-Support-TP
2. Développement d‟une procédure
stockéeDéclaration d’une procédure-Syntaxe:
-Le mode définit si le paramètre est en entrée (IN), en sortie (OUT)
ou en entré
sortie (IN OUT).
-Le mode par défaut est IN.
-Le Bloc est le corps de la procédure.
PL-SQL-Support-TP
2. Développement d‟une procédure
stockée-Exemple*Créer une procédure stockée permettant d’insérer l’enregistrement
suivant à la table pilote: (‘1331’, ‘Tunis’)
CREATE OR REPLACE PROCEDURE nv_pilote
(x_nopilot IN pilote.nopilot%type,
x_adresse IN pilote.adresse%type)
IS
Begin
INSERT INTO pilote(nopilot, adresse)
VALUES(x_nopilot, x_adresse);
COMMIT WORK;
END nv_pilote;
/
PL-SQL-Support-TP
Appel d‟une procédure stockée à
partir de l‟environnement SQL*PLUS
• SQL> EXECUTE nom_procédure[(liste des
paramètres effectifs)] ;
• Exemple:
• SQL> EXECUTE nv_pilote(‘1331’, ‘Tunis’) ;
PL-SQL-Support-TP
• RETURN type_retour définit le type de la valeur
retournée par la fonction.
• Bloc est le corps de la fonction. Il doit contenir une
instruction RETURN(variable_résultat).
3. Développement d‟une fonction
stockéeDéclaration d’une Fonction-Syntaxe:
PL-SQL-Support-TP
3. Développement d‟une fonction
stockée-ExempleCréer une fonction stockée permettant de calculer et de retourner la
moyenne des nombres des heures de vols par code type
CREATE OR REPLACE FUNCTION
moy_h_vol(x_codetype IN appareil.codetype%type)
RETURN NUMBER
IS
Nbhvol_avg NUMBER(8,2):=0;
BEGIN
SELECT AVG(nbhvol)
INTO Nbhvol_avg
FROM avion
WHERE type = x_codetype;
RETURN(Nbhvol_avg);
END moy_h_vol;
PL-SQL-Support-TP
Appel d‟une fonction stockée à
partir de l‟environnement
SQL*PLUS
• SQL> EXECUTE :variable_locale:=nom_fonction[(liste
des paramètres effectifs)] ;
• Pour déclarer une variable locale, il suffit d’utiliser la
commande VARIABLE
• SQL> VARIABLE moyenne NUMBER
• SQL> EXECUTE :moyenne:=moy_h_vol(‘AB3’);
• SQL> PRINT moyenne
PL-SQL-Support-TP
Compilation
• Pour compiler la fonction ou la procédure stockée, il suffitd’exécuter le fichier contenant ces dernières. En cas de détectiond’une erreur de syntaxe pendant la phase de compilation, lemessage d’erreur suivant est affiché :
“ Procedure created with compilation errors”
• Pour connaître le diagnostic des erreurs, il suffit d’utiliser les vuessuivantes du dictionnaire de données:
-USER_ERRORS
-ALL_ERRORS
-DBA_ERRORS.
PL-SQL-Support-TP
Compilation
• Des informations générales sur la procédure ou sur lafonction sont stockées dans les vues suivantes dudictionnaire de données :
– USER_OBJECTS
– ALL_ OBJECTS
– DBA_ OBJECTS.
• Le texte source des procédures et des fonctions stockées setrouve dans les vues suivantes du dictionnaire de données :
– USER_SOURCE
– ALL_ SOURCE
– DBA_ SOURCE.
PL-SQL-Support-TP
Suppression d‟une procédure
ou d‟une fonction :
Syntaxe:
PL-SQL-Support-TP
Utilisation d‟une procédure ou
d‟une fonction stockée
• Le mode d’appel est différent selon l’environnement àpartir duquel la procédure ou la fonction est employée.
PL-SQL-Support-TP
V. Le langage PL/SQLPackage
PL-SQL-Support-TP
Structure d‟un package
• Un package est similaire à la notion de classe dansl’orienté objet.
• Il regroupe des procédures, des fonctions, des variables,des constantes, des curseurs et des traitements d’exceptionsqui ont un lien logique entre eux, sous une seule entité.
• Les procédures et les fonctions peuvent être:
– Publiques: PUBLIC: c’est à dire appelées depuis l’extérieur dupackage.
– Privées: PRIVATE: qui sont invisibles à l’extérieur etaccessibles uniquement à des procédures du même package.
PL-SQL-Support-TP
Création d’un package
• La création d’un package est différente de
celle des procédures et des fonctions. En
effet, elle s’étale sur 2 étapes :
– Création de la partie spécification
– Création de la partie BODY
PL-SQL-Support-TP
Création de la partie
spécification
• la création et la compilation s’effectuent par lacommande suivante :
PL-SQL-Support-TP
Création de la partie BODY• la création et la compilation s’effectuent par la
commande suivante:
• Il est à noter que dans le corps du package, l’ordre de déclaration doit être tel que les éléments référencés par un autre élément doivent être déclarés avant lui.
PL-SQL-Support-TP
Exemple-Partie Spécification
CREATE OR REPLACE PACKAGE package_Série3 AS
PROCEDURE supprimer_pilote(v_pilote pilote.nopilot%type);
PROCEDURE comm_pilote(v_pilot pilote.nopilot%type,
txcomm float);
FUNCTION max_h_v_type (v_type appareil.codetype%type)
RETURN NUMBER;
FUNCTION pilote_avion_piloté (v_nopilot pilote.nopilot%type)
RETURN NUMBER;
END package_Série3;
/
PL-SQL-Support-TP
Exemple:Partie BodyCREATE OR REPLACE PACKAGE BODY pilote_work AS
PROCEDURE nv_pilote
(x_nopilot IN pilote.nopilot%type, x_adresse IN pilote.adresse%type)IS
Begin
INSERT INTO pilote(nopilot, adresse)
VALUES(x_nopilot, x_adresse);
COMMIT WORK;
END nv_pilote;
FUNCTION moy_h_vol(x_codetype IN appareil.codetype%type)RETURN NUMBER IS
Nbhvol_avg NUMBER(8,2):=0;
BEGIN
SELECT AVG(nbhvol)
INTO Nbhvol_avg
FROM avion
WHERE type = x_codetype;
RETURN(Nbhvol_avg);
END moy_h_vol;
END pilote_work;
PL-SQL-Support-TP
VI. Le langage PL/SQLTriggers
PL-SQL-Support-TP
Présentation
• On appelle déclencheur ou trigger: un traitement déclenché par unévénement.
• Les triggers peuvent être applicatifs ou de base de données.
• Nous ne nous intéresserons qu’aux déclencheurs de BD puisqu’ilspermettent d’implémenter des règles de gestion complexes etcomplètent les règles d’intégrité référentielle associées à ladéclaration des tables.
• Par exemple, un déclencheur peut être défini pour vérifier, lors dechaque affectation d’un avion à un vol, que l’avion n’est pas déjàrequis pour une autre affectation pendant la durée de vol.
PL-SQL-Support-TP
Présentation• Les triggers de BD sont associés à une et une seule table, il
est opérationnel jusqu’à la suppression de la table à laquelle ilest lié.
• Le traitement associé au trigger peut être exécuté:
– Soit une fois: suite à l’événement qui l’a déclenché(trigger par ordre)
– Soit pour chaque ligne de la table concernée parl’événement (trigger ligne).
• Mais comment peut-on déclencher un trigger BD ?
L’événement déclencheur est une action de mise à jour surla table
(INSERT, DELETE, UPDATE).
PL-SQL-Support-TP
Syntaxe de trigger par ordre
Dans le cas de l’option UPDATING, il est possible de préciser le nom de la colonne
sur laquelle porte la modification par la syntaxe:
IF UPDATING („nom_colonne‟) THEN traitements END IF;
PL-SQL-Support-TP
Syntaxe de trigger par ordreExemple
PL-SQL-Support-TP
Syntaxe de trigger ligne
• Un déclencheur ligne est exécuté pour chacune des lignesconcernées par l’exécution de l’événement.
PL-SQL-Support-TP
Syntaxe de trigger ligne
Exemple
VALUES
PL-SQL-Support-TP
Trigger ligne
Suite
• Un trigger ligne avec l’option BEFORE peut servir àeffectuer des traitements d’initialisation avant exécutiondes modifications sur la table.
• Un trigger ligne avec l’option AFTER permet de propagerles modifications ou de gérer des historiques. Il est souventutilisé avec référence aux anciennes valeurs et/ou auxnouvelles valeurs des colonnes.
• Des informations générales sur les triggers sont stockéesdans les vues suivantes du dictionnaire de données :
– USER_TRIGGERS
– ALL_ TRIGGERS
– DBA_ TRIGGERS.
