PL/SQL (Oracle 11g)
Hakim Benameurlaine
Décembre 2012
Table of Contents
1 PRÉSENTATION DU PL/SQL . 5
1.1 Bloc PL/SQL .. 5
1.2 Les variables .. 7
1.3 Les variables d’environnements 8
1.4 Les opérateurs . 13
1.5 Utilisation des tables temporaires .. 15
1.6 Type défini par l’utilisateur .. 18
1.7 Les commentaires .. 19 1.8 Les curseurs . 21
1.9 Les scripts . 23
2 INSTRUCTIONS CONDITIONELLES . 25
2.1 .. 25
2.2 INSTRUCTION IF 26
2.2.1 .. 26 2.2.2 ..ELSEIF . 27
2.2.3 .. 28
2.3 INSTRUCTION CASE .. 29
3 INSTRUCTIONS RÉPÉTITIVES . 31
3.1 INSTRUCTION LOOP .. 31
3.2 INSTRUCTION FOR .. 32
3.3 INSTRUCTION WHILE 33
4 LES PROCÉDURES STOCKÉES . 34 4.1 Création d’une procédure .. 36
4.2 Appel d’une procédure 37 4.3 Utilisation des paramètres . 38
4.4 Modification d’une procédure . 45
4.5 Recompiler une procédure . 46 4.6 Supression d’une procédure .. 47
5 LES FONCTIONS . 48 5.1 Création d’une fonction sclalaire 48
5.2 Appel d’une fonction scalaire .. 50 5.3 Modification d’une fonction . 57
5.4 Suppresion d’une fonction . 58
5.5 Les fonctions prédéfinis . 59
6 LES PACKAGE PL/SQL .. 69
6.1 PACKAGE SPECIFICATION 69 6.2 PACKAGE BODY 69
6.3 EXEMPLE PRATIQUE . 70
7 LES VUES 75 7.1 Création d’une vue 75
7.2 Utiliser une vue .. 80
7.3 Suppression d’une vue . 82
7.4 Modifier une vue 83
7.5 Recompiler une vue .. 85
8 GESTION DES TRANSACTIONS .. 86
8.1 Introduction .. 86 8.2 Quand Commence et Finit une Transaction ? 86
8.3 Ordres de Contrôle Explicite des Transactions . 86 8.4 Traitement Implicite des Transactions . 87
8.5 État des Données Avant COMMIT ou ROLLBACK 87
8.6 État des Données APRES COMMIT 87
9 LES TRIGGERS 98
9.1 Creation d'un trigger . 99
9.2 Fonction UPDATING .. 100 9.3 Trigger AFTER INSERT . 103
9.4 Trigger BEFORE DELETE 105 9.5 Trigger BEFORE UPDATE .. 107
9.6 Auditer les modifications sur des colonnes spécifiques . 109 9.7 Trigger de base de données . 112
9.8 Supprimer un trigger . 115 9.9 Recompiler un trigger 116 9.10 Modifier la définition d’un trigger .. 116
10 GESTION DES ERREURS 117
10.1 Traitement des execptions .. 117
10.2 Définir une execption personalisée . 120
11 Script de création de la base de données (Oracle 11g) 122
NOTE IMPORTANTE
Rapporter des erreurs ou des suggestions est une façon très utile de m’aider à améliorer cette documentation.
Merci d’avance
Hakim Benameurlaine
Le langage PL/SQL permet de normaliser le développement d’applications liées aux
Bases de Données. C’est une extension du langage SQL développé par IBM
(International Business Machine) dans les années 1970. C’est un langage procédural par opposition à SQL qui est un langage déclaratif.
Un bloc PL/SQL typique est constitué par les trois partie suivantes :
• Partie DECLARE dans laquelle on déclare les variables.
• Partie traitement entre BEGIN et END.
• Partie EXCEPTION pour le traitement des exceptions.
DECLARE
--Déclaration des variables (partie optionnelle)
BEGIN
--Code PL/SQL (si aucun code alors mettre null;)
EXCEPTION
--Traitement des exceptions (partie optionnelle) END;
Voici un bloc PL/SQL minimal :
BEGIN
NULL;
END
Éléments permettant de stocker une valeur en vue de son utilisation future.
? une variable locale est définie par l'utilisateur par l’instruction DECLARE, ? se voit affecter une valeur initiale par l’opérateur d’assignation := , ? elle est utilisée dans l'instruction ou le lot.
Il possible de déclarer une variable et lui assigner une valeur sur la même ligne.
1.3 Les variables d’environnements
Il est possible de lire les variables d’environnements suivantes :
SELECT SYS_CONTEXT('USERENV','MODULE') FROM DUAL;
SELECT SYS_CONTEXT('USERENV','OS_USER') FROM DUAL;
SELECT SYS_CONTEXT('USERENV','SESSION_USER') FROM DUAL;
SELECT SYS_CONTEXT('USERENV','DB_NAME') FROM DUAL;
SELECT SYS_CONTEXT('USERENV','HOST') FROM DUAL;
Permettent de créer des expressions complexes à partir d’expressions simples. Parmi les opérateurs, nous avons :
? opérateurs arithmétiques : + , - , / , * , modulo
Exécution du bloc PL/SQL sur la ligne de commande:
| SQL> @test x+y=9 x-y=1 x*y=20 x/y=1.25 x mod y=1 PL/SQL procedure successfully completed. |
Une table temporaire est créée avec la commande :
CREATE GLOBAL TEMPORARY TABLE nom_table_temporaire
(
<Définitions des colonnes de la table>
)
[ON COMMIT DELETE ROWS;|ON COMMIT PRESERVE ROWS;]
Par défaut c’est l’option ONCOMMITDELETEROWS; qui est utilisé. Ce qui signifie que lorsque la transaction courante est complétée alors les données sont supprimées de la table.
Insérer des données dans la table temporaire :
Consulter la table temporaire :
Essayer d’accéder aux données de la table temporaire à partir d’une autre session.
Pratique2
Valider les trois INSERT (COMMIT) et vérifier si les données sont dans la table.
Il est possible de définir un nouveau type de données complexe avec la commande CREATE TYPE :
| CREATE OR REPLACE TYPE typeProduits AS OBJECT ( no_produit int, nom_produit varchar2(20), no_categorie int, no_fournisseur int, prix_unitaire number, unites_commandees int, unites_en_stock int ); |
Ce sont des parties de texte insérées dans un jeu d’instruction pour en expliquer le but. Les commentaires ne sont pas exécutés.
• Commenter une ligne
• Commenter un bloc
En PL/SQL on utilise l’instruction SELECT pour retrouver les données.
Ils existent deux facons pour récuperer les données d’un SELECT :
En utilisant l’option INTO de la commande SELECT, un curseur implicite est créé.
DECLARE
v_salaire NUMBER;
BEGIN
SELECT SALAIRE INTO v_salaire
FROM EMPLOYES
WHERE NO_EMP=3;
END;
Un curseur explicite doit etre declarer avant d’etre utilisé.
DECLARE prix PRODUITS.PRIX_UNITAIRE%TYPE;
CURSOR curseur_produits IS
SELECT PRIX_UNITAIRE
FROM PRODUITS;
BEGIN
OPEN curseur_produits;
FETCH curseur_produits INTO prix;
CLOSE curseur_produits; END;
C’est un ensemble d’instructions PL/SQL :
Script :
Exécution du script sur la ligne de commande :
| SQL> 1 1 ---------- | NUMERO IMMATRIC DATEARRIVEE --------------- 04-jan-13 04-jan-13 | |
| Table dropped. Table created. row created. row created. -------- 10 1001 20 1002 |
Syntaxe :
IF <condition> THEN
<Code PL/SQL si condition est vraie>
END IF;
Syntaxe :
IF <condition> THEN
ELSE
<Code PL/SQL si condition est fausse> END IF;
Syntaxe :
IF <condition1> THEN
<Code PL/SQL si condition1 est vraie>
ELSIF <condition2> THEN
<Code PL/SQL si condition1 est fausse et condition2 est vraie >
END IF;
Syntaxe :
| IF <condition1> THEN <Code PL/SQL si condition1 est vraie> ELSIF <condition2> THEN <Code PL/SQL si condition1 est fausse et condition2 est vraie > ELSE <Code PL/SQL si condition1 et condition2 sont fausses> END IF; |
Syntaxe :
CASE [ expression ]
WHEN condition1 THEN résultat1
WHEN condition2 THEN résultat2
WHEN conditionn THEN résultatn
ELSE résultat
END
SELECT NOM_PRODUIT,
CASE NO_CATEGORIE
WHEN 100 THEN 'Tablette'
WHEN 200 THEN 'Cellulaire'
ELSE 'Autre'
END
FROM PRODUITS;
Syntaxe :
LOOP
<Code PL/SQL>
EXIT [ WHEN condition ]; END LOOP;
On utilise la commande EXIT pour sortir de la boucle.
Syntaxe :
FOR compteur_boucle IN [REVERSE] valeur_debut..valeur_fin
LOOP
<Code PL/SQL> END LOOP;
Le compteur de la boucle n’a pas besoin d’être déclarée.
Dans ce qui suit, on ne déclare pas la variable compteur.
| DECLARE somme NUMBER:=0; BEGIN FOR compteur IN 1..20 LOOP somme := somme+ compteur; END LOOP; END; |
Syntaxe :
WHILE condition
LOOP
<Code PL/SQL> END LOOP;
| DECLARE somme NUMBER:=0; compteur NUMBER:=0; BEGIN WHILE compteur <= 100 LOOP somme := somme+ compteur; compteur := compteur +1; END LOOP; END; Elles prennent en charge les variables déclarées par l'utilisateur, le contrôle de flux et d'autres fonctionnalités de programmation avancées. Ce sont de véritables programmes pouvant : • recevoir des paramètres • renvoyer des valeurs • être exécutés à distance • ayant leurs propres droits d’accès (privilège EXECUTE). De plus, les procédures stockées sont stockées dans le cache mémoire de la base de données sous forme compilée lors de leur première exécution, ce qui accroît les performances pour les exécutions suivantes. Une procédure stockée peut être définie comme une suite d’instructions PL/SQL, stockée dans la base de données et identifié par son nom. Pour permettre à cette suite d’instructions de s’adapter au plus grand nombre de cas, certaines valeurs du code sont paramétrables lors de l’appel de la procédure. Avantage des procédures stockées Les procédures stockées offrent de nombreux avantages : 1) Performance Chaque fois qu’une requête PL/SQL est exécutée, le serveur détermine si la syntaxe est correcte puis il construit un plan d’exécution avant d’exécuter la requête. Les procédures stockées sont plus performantes parce que le serveur vérifie la syntaxe à la création. La première fois que la procédure stockée est exécutée, le serveur créé le plan d’exécution et compile la procédure. Les exécutions ultérieures de cette procédure stockée seront plus rapides parce que le serveur ne fera pas de nouvelles vérifications sur la syntaxe et la construction du plan d’exécution ; 2) Réutilisabilité Une fois que la procédure stockée est créée, vous pouvez l’appeler à n’importe quel moment. Ceci permet une modularité et encourage la réutilisation de votre code ; 3) Simplification Elles peuvent encapsuler une fonctionnalité d'entreprise. Les règles et politiques de fonctionnement encapsulées dans les procédures stockées peuvent être modifiées au même endroit. Tous les clients peuvent utiliser les mêmes procédures stockées afin de garantir la cohérence des accès aux données et de leurs modifications ; 4) Accès Réseau Elles contribuent à la réduction du trafic sur le réseau. Au lieu d'envoyer des centaines d'instructions PL/SQL sur le réseau, les utilisateurs peuvent effectuer une opération complexe à l'aide d'une seule instruction, réduisant ainsi le nombre de demandes échangées entre le client et le serveur. 4.1 Création d’une procédureLa création d’une procédure stockée se fait par l’instruction CREATE PROCEDURE. La syntaxe de l’instruction CREATE PROCEDURE est la suivante : CREATE [OR REPLACE] PROCEDURE nom_procédure [ (paramètre [,paramètre]) ] IS [<Section déclaration des variables>] BEGIN <CODE PL/SQL> [EXCEPTION <Section gestion des exceptions>] END [nom_procédure]; EXEMPLEÉcrire une procédure maj_prix3p qui permet d’augmenter le prix de chaque produit de 3% : 4.2 Appel d’une procédureLes procédures stockées peuvent être exécutées par simple appel de leur nom ou par l’instruction EXECUTE. EXEMPLEExécuter la procédure maj_prix3p :
4.3 Utilisation des paramètres Pour permettre à une procédure de s’adapter au plus grand nombre de cas, certaines valeurs du code sont paramétrables lors de l’appel de la procédure. Les paramètres peuvent être en entré (INPUT) ou sortie (OUTPUT). EXEMPLE1 (INPUT)Écrire une procédure maj_prix qui permet de modifier le prix d’un produit donné. Valeur du prix avant l’exécution de la procédure maj_prix : Exécuter la procédure maj_prix :
Valeur du prix après l’exécution de la procédure maj_prix : EXEMPLE2(INPUT et OUTPUT) Écrire une procédure valeur_produit calcul la valeur en stock d’un produit donné. Le numéro de commande est fourni en paramètre IN à la procédure. La valeur du stock est un paramètre OUT. Exécution de la procédure valeur_produit : EXEMPLE3(INPUT et OUTPUT) Écrire une procédure qui calcul le montant à payer sachant le prix et la quantité. Exécuter la procédure : 4.4 Modification d’une procédureLa modification d’une procédure stockée se fait par l’instruction REPLACE PROCEDURE. 4.5 Recompiler une procédureUne procédure peut devenir invalide. Par exemple si elle est basée sur une table dont la structure a changé. Il est possible dans ce cas de recompiler une procédure avec la commande ALTER PROCEDURE RECOMPILE : 4.6 Supression d’une procédureLa modification d’une procédure stockée se fait par l’instruction DROP PROCEDURE. EXEMPLESupprimer la procédure valeur_produit : 5 LES FONCTIONSUne fonction est semblable à une procédure stockée à la différence qu’elle retourne toujours une valeur. 5.1 Création d’une fonction sclalaireLes fonctions de type scalaire retournent, à l’aide du mot réservé RETURN, une valeur scalaire. La syntaxe de l’instruction de création d’une fonction scalaire est la suivante : CREATE [OR REPLACE] FUNCTION nom_fonction [ (paramètre [,paramètre]) ] RETURN type_de_données_de_la_valeur_retournée IS | AS [Section déclaration] BEGIN <CODE PL/SQL> [EXCEPTION Section gestion des exceptions] END [nom_fonction]; EXEMPLECréer une fonction qui retourne le prix moyen par catégorie de produits. 5.2 Appel d’une fonction scalaire Une fonction s’utilise comme une procédure stockée, mais aussi comme une table. L’appel d’une fonction peut se faire avec l’instruction SELECT : EXEMPLEÉcrire une fonction prend en paramètre une date et un séparateur et retourne la date dans le format jour mois année, séparés par le séparateur fourni en paramètre.
Appel de la fonction FormatageDate avec la date du jour (28 décembre 2012) et comme séparateur ‘-‘ : Appel de la fonction FormatageDate avec la date du jour (28 décembre 2012) et comme séparateur ‘.‘ : EXEMPLEÉcrire une fonction Rabais qui calcul le rabais accordé. Un rabais est accordé selon la catégorie du produit comme suit : • 10% de rabais sur les Tablettes • 20% de rabais sur les Ordinateurs portables ? 30% de rabais sur les Cellulaires. Appel de la fonction Rabais avec le produit dont le numéro est 4 : 5.3 Modification d’une fonctionL’instruction ALTER FUNCTION permet de modifier une fonction. EXEMPLEOn va modifier la fonction Rabais pour changer la valeur du rabais : 5.4 Suppresion d’une fonctionL’instruction DROP FUNCTION permet de supprimer une fonction. EXEMPLE5.5 Les fonctions prédéfinisFonction SYS_EXTRACT_UTC CHARINDEX Fonction TO_CHAR LENGTH SUBSTR ISNULL CAST Fonction ROUND Fonction CAST La création d’un PACKAGE PL/SQL se fait en deux étapes : 6.1 PACKAGE SPECIFICATIONDans le PACKAGE SPCIFICATION on déclare toutes les procédures et fonctions qui font partie du package ainsi que leurs paramètres. La création du PACKAGE SPCIFICATION se fait comme suit :
6.2 PACKAGE BODYDans le PACKAGE BODY on retrouve la définition de chaque procédure et fonction du package. REMARQUE Lors de l’exécution d’une procédure ou d’une fonction d’un package, on doit préfixer la procédure ou le package par le nom du package. Nom_package.nom_procedure Nom_package.nom_fonction 6.3 EXEMPLE PRATIQUEÉcrire un package PL/SQL nommé OPERATION qui permet de réaliser les opérations arithmétiques : 1) En premier il faut créer le PACKAGE en déclarant toutes les procédures et les fonctions :
2) Ensuite on doit créer le PACKAGE BODY dans lequel on retrouve la définition de chaque procédure et fonction :
Le package est bien créé dans la base de données : 3) Et pour terminer, exécuter une fonction ou procédure du PACKAGE: SELECT OPERATION.ADDITION(20,30) FROM DUAL; 7 LES VUESUne vue peut être considérée comme une requête enregistrée. Ainsi vous pouvez réutiliser une instruction sans avoir à la redéfinir. Une vue se comporte comme table à quelques restrictions près, mais n’occupant pas d’espace disque pour les données. Une vue ne contient que la définition de la requête SQL. Avantage des vuesUne vue vous permet d'effectuer les tâches suivantes : • limiter l'accès d'un utilisateur à certaines lignes d'une table ; • limiter l'accès d'un utilisateur à certaines colonnes d'une table ; • joindre les colonnes de différentes tables ; • une vue peut être utilisée partout où on peut utiliser une table ; 7.1 Création d’une vueOn peut créer une vue en utilisant le langage de définition de données . La commande CREATE VIEW permet de créer une vue en spécifiant le SELECT constituant la définition de la vue : CREATE VIEW nom_vue [(nom_col1, )] [WITH SCHEMABINDING] AS Instruction SELECT [WITH CHECK OPTION]; La spécification des noms de colonnes de la vue est facultative. Par défaut, les noms des colonnes de la vue sont les mêmes que les noms des colonnes du SELECT. Si certaines colonnes du SELECT sont des expressions, il faut renommer ces colonnes dans le SELECT, ou spécifier les noms de colonne de la vue. Le CHECK OPTION permet de vérifier que la mise à jour ou l'insertion faite à travers la vue ne produisent que des lignes qui font partie de la sélection de la vue. La clause WITH SCHEMABINDING permet d'empêcher la modification ou la suppression des objets référencés par la vue. EXEMPLE1 Créer une vue sans renommer les colonnes : EXEMPLE2 On peut aussi créer une vue en renommant les colonnes : EXEMPLE3 Création d'une vue constituant une restriction de la table Employes aux employés de la ville de Waterloo: L’ordre SQL suivant, qui tente de mettre à jour la ville des employés de Waterloo. 7.2 Utiliser une vueUne vue s’utilise comme un table. 7.3 Suppression d’une vueL’instruction DROP VIEW permet de supprimer une vue. DROP VIEW nom_vue; EXEMPLE 7.4 Modifier une vueL’instruction REPLACE VIEW permet de modifier la définition de la vue. EXEMPLE Utilisation de la vue : 7.5 Recompiler une vueIl est possible de recompiler une vue avec la ALTER VIEW COMPILE : 8 GESTION DES TRANSACTIONS 8.1 IntroductionSQL garantit la cohérence des données par le biais des transactions. Les transactions vous offrent davantage de souplesse et un meilleur contrôle lors de la modification de données. Elles garantissent la cohérence des données en cas d'échec du processus utilisateur ou de panne du système. Les transactions consistent en un ensemble d'ordres du LMD qui réalisent une modification cohérente des données. Par exemple, un transfert de fonds entre deux comptes implique de débiter un compte et d'en créditer un autre du même montant. Les deux actions doivent, soit réussir, soit échouer en même temps : un crédit ne peut pas être validé sans le débit correspondant. 8.2 Quand Commence et Finit une Transaction ? Une transaction commence dès le premier ordre SQL exécutable rencontré et se termine lorsque l'un des événements suivants se produit : • L'utilisateur quitte la session Base de données • Il se produit une panne de machine ou du système d'exploitation Lorsqu'une transaction prend fin, le prochain ordre SQL exécutable démarrera automatiquement la transaction suivante. 8.3 Ordres de Contrôle Explicite des TransactionsVous avez la possibilité de contrôler la logique des transactions au moyen des ordres COMMIT, SAVEPOINT et ROLLBACK.
8.4 Traitement Implicite des Transactions
8.5 État des Données Avant COMMIT ou ROLLBACK• Les opérations de manipulation des données se déroulant principalement dans le buffer de la base de données, il est possible de restaurer l'état précédent des données. • L'utilisateur courant peut afficher le résultat de ses opérations de manipulation de données en interrogeant les tables. • Les autres utilisateurs ne peuvent pas voir le résultat des opérations de manipulation de données réalisées par l'utilisateur courant. SQL met en œuvre un principe de lecture cohérente qui garantit que l'utilisateur voit les données telles qu'elles se présentaient lors de la dernière validation. 8.6 État des Données APRES COMMITPour enregistrer définitivement les modifications en instance, utilisez l'ordre COMMIT. Après l’exécution d'un ordre COMMIT : • Les modifications de données sont écrites définitivement dans la base de données. • L'état précédent des données est irrémédiablement perdu. • Tous les utilisateurs peuvent voir les résultats de la transaction. • Les lignes qui étaient verrouillées sont libérées et redeviennent accessibles à d'autres utilisateurs pour modification. • Tous les points de sauvegarde sont effacés. Faire la transaction de mise à jour suivante : Sans quitter la session courante et à partir d’une nouvelle session faire l’interrogation suivante : CONCLUSIONLes transactions non validées ne sont pas visibles dans les autres sessions tant qu’elles ne sont pas validées. AVEC COMMIT Ajouter un nouveau fournisseur dans la table FOURNISSEURS. Ensuite valider la transaction. Ouvrir une nouvelle session et vérifier que la transaction a bien été validée : CONCLUSIONLes transactions validées sont visibles dans les autres sessions. AVEC ROLLBACK Faire la transaction suivante : Interroger les données dans la même session : On voit bien que la transaction est visible même si elle n’a pas encore été validée. Annuler la transaction : Interroger les données de nouveau : La transaction a bien été annulée. ROLLBACK AVEC SAVEPOINTIl est possible d’annuler une transaction et de retourner à un point spécifique en utilisant la commande : ROLLBACK [To] NOM_SAVEPOINT;
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
