Upload
alphormcom
View
3.316
Download
135
Embed Size (px)
Citation preview
Le langage PL/SQL
Prsentation de la formation
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Le langage PL/SQL
Noureddine DRISSI Expert consultant bases de donnes
Plan Le formateur
Mes formations sur Alphorm
Les objectifs de la formation
Le plan de formation
Publics concerns
Le langage PL/SQL alphorm.com
Publics concerns
Connaissances requises
Liens utiles
Le formateur
Noureddine DRISSI
Expertise dans le domaine des bases de donnes
15 annes dexprience sur tous les SGBDR du march (SQL Server, Oracle, Postgresql, Mysql, DB2, MongoDB, SQL, PL/SQL, Transact-SQL)
Issue de lenvironnement bancaire
Le langage PL/SQL alphorm.com
Mes formations sur alphorm.com
MySQL, Administration (1Z0-883)
Oracle Database 11g DBA 1 (1Z0-052)
Formation PostgreSQL, La haute disponibilit
Le langage PL/SQL alphorm.com
(1Z0-883)
MongoDB, administration
(1Z0-052)
Formation Administration PostgreSQL
Administration SQL Server 2012 (70-462)
Le langage SQL
La haute disponibilit
Objectifs
Matriser le langage PL/SQL
Utiliser des traitements stocks et des triggers dans les applications
Le langage PL/SQL alphorm.com
Plan de cours
Module 1: Prsentation du PL/SQL
Module 2: Les variables
Module 3: Accs la base de donnes et ordres SQL
Module 4: Les structures de contrle
Module 5: Les curseurs
Le langage PL/SQL alphorm.com
Module 5: Les curseurs
Module 6: Gestion des erreurs
Module 7: Les programmes stocks
Module 8: Les triggers
Module 9: Les packages
Publics concerns
Concepteurs
Dveloppeurs
Toutes les personnes impliques dans des dveloppements utilisant la base de donnes Oracle.
Le langage PL/SQL alphorm.com
Connaissances requises
Connaissance du language SQL
Notions de programmation
Ressource :
Formation Le langage SQL sur Alphorm
http://www.alphorm.com/formation/formation-langage-sql
Le langage PL/SQL alphorm.com
http://www.alphorm.com/formation/formation-langage-sql
Liens utiles
Documentation PL/SQL
http://docs.oracle.com/cd/B10501_01/appdev.920/a96624/toc.htm
http://oracle.developpez.com/cours/#SQL-et-PL-SQL
Tlchargement Oracle 11g Express Edition
http://www.oracle.com/technetwork/database/database-technologies/express-
Le langage PL/SQL alphorm.com
http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html
Tlchargement Oracle SQL Developer
http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
Prsentation de lenvironnement
Le langage PL/SQL alphorm.com
Lenvironnement
Prsentation de la formation
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Lenvironnement
Noureddine DRISSI Expert consultant bases de donnes
Objectifs
Configurer lenvironnement de travail
Vrifier les pr-requis
Installer le SGBDR Oracle Database 11g Express Edition
Installer Oracle SQL Developer
Se connecter au moteur de base de donnes Oracle
Le langage PL/SQL alphorm.com
Se connecter au moteur de base de donnes Oracle
Crer le shma facsys (facsysv2.sql)
Les pr-requis
Matriel Mmoire
Minimum :
- ditions Express : 512 MO
Processeur
Minimum :
Le langage PL/SQL alphorm.com
Minimum :
- Processeur x86 : 1,0 GHz, Processeur x64 : 1,4 GHz
- Recommand : 2,0 GHz ou plus
Disque
Minimum :
- 5,0 GO disponible
Les pr-requis Logiciels
OS
Windows 8
SGBDR et outil
Oracle Database 11g Express Edition
Le langage PL/SQL alphorm.com
Oracle Database 11g Express Edition
http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html
Oracle SQL Developer
http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
Le modle de donnes (facsys)
Le langage PL/SQL alphorm.com
Atelier
Installation du SGBDR Oracle Database 11g Express Edition
Installation de Oracle SQL Developper
Connexion la base de donnes
Cration du schma facsys et test de connexion
Le langage PL/SQL alphorm.com
Prsentation du langage PL/SQL
Le langage PL/SQL alphorm.com
Les outils de
Prsentation du langage PL/SQL
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Les outils de dveloppement
Noureddine DRISSI Expert consultant bases de donnes
Objectifs
Utiliser Oracle SQL Developer pour dvelopper en PL/SQL
Excuter un programme PL/SQL partir de sqlplus
Le langage PL/SQL alphorm.com
Oracle SQL Developer
Caractristiques
Auto-formatage des instructions PL/SQL et SQL
Inclut un dbogueur PL/SQL
Permet de naviger dans les objets de bases de donnes
Contient des modles de code
Le langage PL/SQL alphorm.com
Contient des modles de code
Permet lexcution de scripts
Le langage PL/SQL
Le langage PL/SQL alphorm.com
Le langage PL/SQL
Prsentation du langage PL/SQL
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Le langage PL/SQL
Noureddine DRISSI Expert consultant bases de donnes
Objectifs
Connatre la syntaxe du langage PL/SQL
Ecrire et excuter un bloc PL/SQL
Exercices pratiques
Le langage PL/SQL alphorm.com
Le langage PL/SQL
PL/SQL = Procedural Language extensions to SQL
Langage procdural
Les variables
Les structures de contrle et les boucles
Les procdures, les fonctions, les triggers et les packages
Le langage PL/SQL alphorm.com
Les procdures, les fonctions, les triggers et les packages
Langage structur en blocs (ensemble dinstructions)
Externe (anonyme) ou stock en base (procdure, fonctions ou triggers)
Sintgre dans les outils SQL*FORMS, SQL*PLUS, PRO*C, etc.
Client ORACLE
Moteur PL/SQL
Architecture PL/SQL
BLOC PL/SQL
BLOC PL/SQL
Instructions procdurales
Procdurale
Le langage PL/SQL alphorm.com
Moteur SQLS
QL
Instructions SQL
Structure dun programme PL/SQL
Se dcompose en trois parties
[DECLARE]....................BEGIN..........
La zone DECLARE sert la dclaration des variables, des constantes, ou des curseurs
La zone BEGIN constitue le corps du programme
La zone EXCEPTION permet de prciser les actions
Le langage PL/SQL alphorm.com
..........
..........[EXCEPTION]....................END ;/
La zone EXCEPTION permet de prciser les actions entreprendre lorsque des erreurs sont rencontres (pas de rfrence article trouve pour une insertion, ...).
Le END rpond au BEGIN prcdent, il marque la fin du script.
Le / permet de terminer le bloc PL/SQL
DBMS_OUTPUT
Permet dafficher une chane ou le contenu dune variable
SET SERVEROUTPUT ON;BEGIN
DBMS_OUTPUT.PUTLINE();END ;/
Le langage PL/SQL alphorm.com
/
Bloc PL/SQL: exercice 1 (sol_exercice1.txt)
Crez un bloc PL/SQL qui affiche le texte suivant
Crer un programme PL/SQL qui insre une nouvelle catgories (table CATEGORIES) de produit avec les valeurs suivantes: CODE_CAT = 5 et LIBELLE_CAT = "Composants"
Bonjour facys: vous tes sur la plateforme Alphorm le
Le langage PL/SQL alphorm.com
Un peu plus loin dans lutilisation de PL/SQL
Le langage PL/SQL alphorm.com
PL/SQL
Un peu plus loin dans
Prsentation du langage PL/SQL
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Un peu plus loin dans lutilisation du PL/SQL
Noureddine DRISSI Expert consultant bases de donnes
Objectifs
Connatres les principales caractristiques du PL/SQL
Connatre les directives de compilation dun bloc PL/SQL
Connatre la notion de bloc imbrique
Le langage PL/SQL alphorm.com
Rappel: structure dun bloc PL/SQL
[DECLARE]....................BEGIN....................[EXCEPTION]
Le langage PL/SQL alphorm.com
[EXCEPTION]....................END ;/
Limites et fonctionnement
Contenu dun bloc PL/SQL
LMD (langage de manipulation de donnes)
LDD (langage de dfinition des donnes) est interdit
Gestion des transactions
Identique SQL
Le langage PL/SQL alphorm.com
Identique SQL
Directive PRAGMA
Le mot cl PRAGMA
Instruction de compilation (non excutable)
Les valeurs possibles
EXCEPTION_INIT
RESTRICT_REFERENCES
Le langage PL/SQL alphorm.com
RESTRICT_REFERENCES
SERIALLY_REUSABLE
AUTONOMOUS_TRANSACTION
Les blocs imbriqus
[DECLARE]..........BEGIN
..........
[DECLARE]..........BEGIN..........
Le langage PL/SQL alphorm.com
[EXCEPTION]....................END ;/
..........[EXCEPTION]..........END ;/
Affichage lcran
[DECLARE]..........BEGIN
dbms_output.put_line ...
[EXCEPTION]
Le langage PL/SQL alphorm.com
[EXCEPTION]....................END ;/
Les variables
Le langage PL/SQL alphorm.com
Introduction aux variables
Les variables
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Introduction aux variables PL/SQL
Noureddine DRISSI Expert consultant bases de donnes
Objectifs
Connatre les types de donnes des variables
Dclarer et manipuler des variables en PL/SQL
Le langage PL/SQL alphorm.com
Dclaration des variables
DECLARE....................BEGIN....................[EXCEPTION]
ICI
Nommage dune variable
Longueur
Les types de donnes PL/SQL
Types scalaires
BINARY_INTEGER
INTEGER
NUMBER
POSITIVE/NATURAL
CHAR
LONG
VARCHAR ou VARCHAR2
BOOLEAN
RAW
LONG RAW
CLOB
BLOB
Types composs
RECORD
TABLE
Types numriques
Types caractres
Types RAW
Le langage PL/SQL alphorm.com
DECIMAL
FLOAT
INTEGER
REAL
SMALLLINT
BOOLEAN
DATE
ROWID
CLOB
UROWID
BFILE
Types dates
Types ROWID
Types boolens
Types LOB
Conversion de types de donnes
Les types de conversion
EXPLICITE
Utilisent des fonctions comme TO_DATE, TO_CHAR, etc.
IMPLICITE
Ralises automatiquement par le moteur PL/SQL
Le langage PL/SQL alphorm.com
Ralises automatiquement par le moteur PL/SQL
- Evaluation dexpressions
- Affectation des variables
Conversion de types de donnes
Evaluation dexpressions
NUMBER
DATE
ROWID
Le langage PL/SQL alphorm.com
BINARY_INTEGER
CHAR
VARCHAR2
RAW
Conversion de types de donnes
Affectation de variables
NUMBER
DATE
ROWID
Le langage PL/SQL alphorm.com
BINARY_INTEGER
CHAR
VARCHAR2
RAW
Dclaration de variables
Syntaxe
[CONSTANT] TYPE [NOT NULL] [{DEFAULT] | :=} VALEUR];
Le langage PL/SQL alphorm.com
Les variables de liaison
Le langage PL/SQL alphorm.com
Les variables de liaison
Les variables
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Les variables de liaison
Noureddine DRISSI Expert consultant bases de donnes
Objectif
Savoir utiliser les variables de liaison
Le langage PL/SQL alphorm.com
Quest-ce quune variable de liaison ?
Permet de socker les entres utilisateurs
De visualiser le contenu dune variable travers plusieurs excution successives de bloc PL/SQL
DECLARE..........BEGIN
variable ...
Le langage PL/SQL alphorm.com
BEGIN:variable .............[EXCEPTION]....................END ;/
La porte des variables
Le langage PL/SQL alphorm.com
La porte des variables
Les variables
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
La porte des variables
Noureddine DRISSI Expert consultant bases de donnes
Objectif
Connatre la porte des variables dans un programme PL/SQL
Le langage PL/SQL alphorm.com
La porte des variables PL/SQL
Une variable est accessible dans le bloc dans lequel elle a t dclare et dans les blocs secondaires (imbriqus)
Si le nom dune variables dclare dans le bloc principal est rutilis dans un bloc secondaire, cest une nouvelle variable qui est cre
Le langage PL/SQL alphorm.com
Les autres types de donnes PL/SQL
Le langage PL/SQL alphorm.com
Les autres types de donnes PL/SQL
Les variables
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Les autres types de donnes PL/SQL
Noureddine DRISSI Expert consultant bases de donnes
Objectifs
Connatre les autres types de donnes PL/SQL
Types dfinis par les utilisateurs
Types composs
Les enregistrements ou structure
Les tableaux
Le langage PL/SQL alphorm.com
Les tableaux
Les variables bases
Crer et utiliser les autres types de donnes
Les types dfinis par les utilisateurs
Deux types drivs possibles:
Les types borns
Les types non borns (alias ou synonym dun type de base)
Syntaxe
Le langage PL/SQL alphorm.com
Syntaxe
SUBTYPE IS TYPE [ (CONSTRAINT) ] [NOT NULL]
Les structures
Une structure cest quoi ?
Type compos
Permet de stocker des donnes structures = enregistrement
Syntaxe de dclaration
Le langage PL/SQL alphorm.com
Syntaxe de dclaration
TYPE IS RECORD ( TYPE [NOT NULL] [:=EXPRESSION1], [, . . . ] );
Syntaxe dutilisation
Les tableaux (type compos TABLE)
Les tableaux associatifs
Syntaxe de dclaration
TYPE IS TABLE OF [NOT NULL] INDEX BY { PLS_INTEGER | BINARY_INTEGER | VARCHAR2(TAILLE) };
Syntaxe dutilisation
Le langage PL/SQL alphorm.com
Syntaxe dutilisation
Les tableaux (type compos TABLE)
Les tableaux pr-dimensionns
Syntaxe de dclaration
TYPE IS VARRAY(TAILLE) OF [NOT NULL];
Syntaxe dutilisation
Le langage PL/SQL alphorm.com
Syntaxe dutilisation
Les tableaux (type compos TABLE)
Les actions possibles sur un tableau en PL/SQL
EXISTS(n)
COUNT
FIRST et LAST
PRIOR et NEXT (n)
Le langage PL/SQL alphorm.com
PRIOR et NEXT (n)
TRIM (n)
DELETE (n)
Les variables bases
Attribut %TYPE
Permet de rfrencer: une colonne dune table ou une variable dj dfinie
Syntaxe de dclaration
{
Rcapitulatif sur les variables PL/SQL et exercices
Le langage PL/SQL alphorm.com
et exercices
Le SELECT dans un bloc PL/SQL
Les variables
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Le SELECT dans un bloc PL/SQL
Noureddine DRISSI Expert consultant bases de donnes
Objectif
Interroger une bases de donnes Oracle avec le PL/SQL
Le langage PL/SQL alphorm.com
Le SELECT INTO
Syntaxe
SELECT EXPRESSION1 [ , . . . ] INTO VARIABLE1 [ , . . . ] FROM TABLE [ WHERE PREDICAT ] ;
Le langage PL/SQL alphorm.com
BULK COLLECT
Syntaxe
SELECT EXPRESSION1 [ , . . . ] BULK COLLECT INTO TABLEAU1 [ , . . . ] FROM TABLE [ WHERE PREDICAT ] ;
Le langage PL/SQL alphorm.com
Les ordres INSERT, UPDATE et DELETE
Le langage PL/SQL alphorm.com
DELETE
Les ordres INSERT, UPDATE et DELETE
Accs la base de donnes et ordres SQL
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Les ordres INSERT, UPDATE et DELETE
Noureddine DRISSI Expert consultant bases de donnes
Objectif
Mettre jour les donnes dune base avec PL/SQL
Le langage PL/SQL alphorm.com
La commande INSERT
Syntaxe
INSERT INTO VALUES ;
Le langage PL/SQL alphorm.com
La commande UPDATE
Syntaxe
UPDATE SET = [ WHERE PREDICAT ];
Le langage PL/SQL alphorm.com
La commande DELETE
Syntaxe
DELETE [ WHERE PREDICAT ];
Le langage PL/SQL alphorm.com
Attributs des ordres LMD
Le langage PL/SQL alphorm.com
Attributs des ordres
Accs la base de donnes et ordres SQL
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Attributs des ordres INSERT, UPDATE et DELETE
Noureddine DRISSI Expert consultant bases de donnes
Objectif
Obtenir des informations sur lexcution des ordres LMD
Le langage PL/SQL alphorm.com
Les diffrents attributs
SQL%FOUND
Type BOOLEAN
Renvoie TRUE si la prcdente excution (INSERT, UPDATE ou DELETE) a modifi des enregistrements
SQL%NOTFOUND
Le langage PL/SQL alphorm.com
SQL%NOTFOUND
Type BOOLEAN
Renvoie TRUE si la prcdente excution (INSERT, UPDATE ou DELETE) na modifi aucun enregistrement
SQL%ROWCOUNT
Renvoie le nombre de ligne modifis par la prcdente excution
Clause SQL RETURNING
Fonction
Permet de renvoyer les valeurs de champs des enregistrements affects par les ordres INSERT, UPDATE ou DELETE
Limites
Inutilisable avec un ordre INSERT qui insre plusieurs enregistrement partir
Le langage PL/SQL alphorm.com
Inutilisable avec un ordre INSERT qui insre plusieurs enregistrement partir dune sous-requte
Impossible dutiliser * pour retourner lensemble des champs insres dans lenregistrement
Rcapitulatif sur le LMD avec le langage PL/SQL et exercices
Le langage PL/SQL alphorm.com
langage PL/SQL et exercices
Rcapitulatif sur le LMD
Accs la base de donnes et ordres SQL
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Rcapitulatif sur le LMD avec le langage PL/SQL et
exercices
Noureddine DRISSI Expert consultant bases de donnes
Objectifs
Rappel sur le LMD avec le PL/SQL
Exercices pratiques
Le langage PL/SQL alphorm.com
Ce que nous avons tudis
SELECT INTO
BULK COLLECT INTO
Insert, update et delete dans un programme PL/SQL
Les attributs SQL%FOUND, SQL%NOTFOUND et SQL%ROWCOUNT
Le langage PL/SQL alphorm.com
La clause RETURNING
Le LMD dans PL/SQL: exercice 4 (sol_exercice4.txt)
Crez le bloc PL/SQL qui permet deffectuer les oprations suivantes:
Affichez le nom, la fonction, le salaire et la commission du salari dont la fonction est Directeur
Affichez le nom, la fonction, le salaire et la comission de tous les salaris
Mettez jour la commission du salari Orlando ( comission = 5000) et affichez la nouvelle valeur insere
Le langage PL/SQL alphorm.com
Mettez jour la commission du salari Orlando ( comission = 5000) et affichez la nouvelle valeur insere
Inserez une nouvele categorie (code_cat=77 et libelle_cat= Outillage ) puis affichez les valeurs inseres pour la colonne code_cat et libelle_cat
Instructions de contrle
Le langage PL/SQL alphorm.com
Traitements conditionnels
Les structures de contrles
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Traitements conditionnels
Noureddine DRISSI Expert consultant bases de donnes
Objectifs
Dcrire les instructions de contrle
Apprendre utiliser les traitements conditionnels
Le langage PL/SQL alphorm.com
IF THEN ELSIF ELSE
Syntaxe
IF THEN
[ ELSEIF THEN
[ ELSEIF THEN
[ ELSE ]
Le langage PL/SQL alphorm.com
[ ELSE ]
END IF;
Instruction CASE
Case simple
Syntaxe
CASE
WHEN THEN
[ ELSE ]
Le langage PL/SQL alphorm.com
[ ELSE ]
END CASE;
Instruction CASE
Case avec recherche
Syntaxe
CASE
WHEN THEN
[ ELSE ]
Le langage PL/SQL alphorm.com
[ ELSE ]
END CASE;
Traitements itratifs
Le langage PL/SQL alphorm.com
Traitements itratifs
Les structures de contrles
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Traitements itratifs
Noureddine DRISSI Expert consultant bases de donnes
Objectifs
Dcrire les instructions de contrle
Apprendre utiliser les traitements itratifs
Le langage PL/SQL alphorm.com
Linstruction LOOP
Permet de rpter une squence de commandes
Syntaxe
[ ]
LOOP
;
Le langage PL/SQL alphorm.com
;
EXIT [ ] | EXIT WHEN ;
END LOOP;
Linstruction WHILE
Permet de rpter une squence de commandes tant que la condition reste vraie
Syntaxe
[ ]
WHILE LOOP
Le langage PL/SQL alphorm.com
WHILE LOOP
;
END LOOP [ ];
Linstruction FOR
Permet de rpter une squence de commandes une fois pour chaque lment dans lintervalle dlimit par deux expressions
Syntaxe
[ ]
FOR IN [ REVERSE ] . . LOOP
Le langage PL/SQL alphorm.com
FOR IN [ REVERSE ] . . LOOP
;
END LOOP [ ];
Rcapitulatif sur les instructions de contrle et exercices
Le langage PL/SQL alphorm.com
contrle et exercices
Rcapitulatif sur les
Les structures de contrles
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Rcapitulatif sur les instructions de contrle et
exercices
Noureddine DRISSI Expert consultant bases de donnes
Objectifs
Rappel sur les instructions de contrle
Exercices
Le langage PL/SQL alphorm.com
Ce que nous avons tudis
IF THEN ELSIF ELSE
Linstruction CASE
La boucle LOOP
La boucle WHILE
Le langage PL/SQL alphorm.com
La boucle FOR
Instructions de contrle: exercice 5 (sol_exercice5.txt)
Crez le bloc PL/SQL qui permet deffectuer les oprations suivantes:
Simuler une augmentation de salaire des salaris de 10% si leur salaire est infrieur la moyenne des salaires
Affichez la liste de tous les articles en utilisant la boucle WHILE et la boucle FOR
Le langage PL/SQL alphorm.com
Les curseurs
Le langage PL/SQL alphorm.com
Introduction au curseur
Les curseurs
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Introduction au curseur
Noureddine DRISSI Expert consultant bases de donnes
Objectifs
Connatre le rle dun curseur
Connatre les types de curseurs
Savoir comment fonctionne un curseur
Le langage PL/SQL alphorm.com
Un curseur cest quoi ?
Permet de manipuler les donnes ligne par ligne
Permet de contrler le traitement PL/SQL
Le langage PL/SQL alphorm.com
Un curseur cest quoi ?
Permet de manipuler les donnes ligne par ligne
Fonctionnement
Crer une zone de contexte pour excuter la commande et stocker les informations
Le curseur permet de nommer cette zone de contexte, daccder aux
Le langage PL/SQL alphorm.com
Le curseur permet de nommer cette zone de contexte, daccder aux informations et ventuellement de contrler le traitement PL/SQL
Les types de curseurs
Curseurs explicites
Pour traiter les requtes multilignes
Curseurs implicites
Utiliss pour les autres commandes SQL (lorsquune requte ne ramne quune ligne)
Le langage PL/SQL alphorm.com
quune ligne)
Utilisation dun curseur
Les tapes de la vie dun curseur
DECLARATION (DECLARE)
OUVERTURE (OPEN)
DEFILEMENT DES DONNEES (FETCH)
FERMETURE (CLOSE)
Le langage PL/SQL alphorm.com
FERMETURE (CLOSE)
Dclaration, ouverture, traitement des lignes et fermeture dun curseur
Le langage PL/SQL alphorm.com
des lignes et fermeture dun curseur
Dclaration, ouverture,
Les curseurs
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Dclaration, ouverture, traitement des lignes et fermeture dun curseur
Noureddine DRISSI Expert consultant bases de donnes
Objectifs
Savoir dclarer des curseurs
Savoir grer les curseurs et utiliser les curseurs
Le langage PL/SQL alphorm.com
Dclaration
Consite nommer un curseur et lui associer une requte
Syntaxe
CURSOR [ ( TYPE [ , . . . ] IS ;
Le langage PL/SQL alphorm.com
Ouverture
Ds que le curseur est ouvert lordre SQL est excut
Syntaxe
OPEN [ (
Traitement des lignes
Pour trouver la ligne suivante dans lensemble actif de donnes, on doit dfiler le curseur par la commande FETCH
Syntaxe
FETCH { INTO { | [ , . . . ] } | BULK COLLECT INTO };
Le langage PL/SQL alphorm.com
Dclaration, ouverture, traitement des lignes et fermeture dun curseur
Le langage PL/SQL alphorm.com
des lignes et fermeture dun curseur
Ouverture et fermeture
Les curseurs
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Ouverture et fermeture dun curseur
Noureddine DRISSI Expert consultant bases de donnes
Objectifs
Savoir dclarer des curseurs
Savoir ouvrir et fermer un curseur
Le langage PL/SQL alphorm.com
Ouverture
Ds que le curseur est ouvert lordre SQL est excut
Syntaxe
OPEN [ ( [ , . . . ] ) ];
Le langage PL/SQL alphorm.com
Fermeture
Chaque curseur explicite ouvert doit tre ferm
Syntaxe
CLOSE ;
Le langage PL/SQL alphorm.com
Traitement des lignes dun curseur
Le langage PL/SQL alphorm.com
Traitement des lignes dun
Les curseurs
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Traitement des lignes dun curseur
Noureddine DRISSI Expert consultant bases de donnes
Objectif
Savoir traiter les lignes dun curseur
Le langage PL/SQL alphorm.com
Traitement des lignes
Pour trouver la ligne suivante dans lensemble actif de donnes, on doit dfiler le curseur par la commande FETCH
Syntaxe
FETCH { INTO { | [ , . . . ] } | BULK COLLECT INTO };
Le langage PL/SQL alphorm.com
Etat dun curseur
Le langage PL/SQL alphorm.com
Etat dun curseur
Les curseurs
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Etat dun curseur
Noureddine DRISSI Expert consultant bases de donnes
Objectif
Connatre les diffrents attributs de statut dun curseur
Le langage PL/SQL alphorm.com
Etat dun curseur
Les attributs des curseurs explicites:
%FOUND
%NOTFOUND
%ISOPEN
%ROWCOUNT
Le langage PL/SQL alphorm.com
%ROWCOUNT
Mise jour des lignes dun curseur
Le langage PL/SQL alphorm.com
Mise jour des lignes dun
Les curseurs
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Mise jour des lignes dun curseur
Noureddine DRISSI Expert consultant bases de donnes
Objectif
Savoir mettre jour les lignes dun curseur
Le langage PL/SQL alphorm.com
Curseurs FOR UPDATE
Option FOR UPDATE
Permet de verrouiller les enregistrements dun curseur pour modification
Syntaxe
CURSOR [ ( TYPE := [ , . . . ] ) ] IS FOR UPDATE [ OF [ , . . . ] ] [ {
Le langage PL/SQL alphorm.com
, . . . ] ) ] IS FOR UPDATE [ OF [ , . . . ] ] [ { NOWAIT | WAIT } ];
Utilisation des curseurs avec une boucle
Le langage PL/SQL alphorm.com
boucle
Utilisation des curseurs
Les curseurs
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Utilisation des curseurs avec une boucle
Noureddine DRISSI Expert consultant bases de donnes
Objectif
Utiliser un curseur avec une boucle
Le langage PL/SQL alphorm.com
Utilisation dun curseur avec une boucle
Syntaxe
FOR IN LOOP ;
END LOOP;
Le langage PL/SQL alphorm.com
Les exceptions
Le langage PL/SQL alphorm.com
Rcapitulatif sur les
Les curseurs
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Rcapitulatif sur les curseurs et exercices
Noureddine DRISSI Expert consultant bases de donnes
Objectifs
Rappel sur les curseurs
Exercices
Le langage PL/SQL alphorm.com
Ce que nous avons tudis
DECLARATION (CURSOR) des curseurs
OUVERTURE (OPEN) et FERMETURE (CLOSE) des CURSEURS
TRAITEMENT DES LIGNES (FETCH) des curseurs
Les attributs de statut des curseurs (%FOUND, %NOTFOUND, %ISOPEN et %ROWCOUNT)
Le langage PL/SQL alphorm.com
et %ROWCOUNT)
La mise jour des lignes dun curseur (FOR UPDATE)
Les curseurs et la variante de la boucle FOR
Les curseurs: exercice 6 (sol_exercice6.txt)
Crez un bloc PL/SQL qui permet dafficher toutes les commandes de la table commandes laide dun curseur utilisant la boucle FOR.
Crez un bloc PL/SQL qui permet dafficher le nombre de commandes par client. Le pays de rsidence du client sera pass en paramtre (argument) au curseur avec une valeur par dfaut. Utilisez la boucle FOR avec votre curseur.
Le langage PL/SQL alphorm.com
avec votre curseur.
Crez un bloc PL/SQL qui permet de mettre jour ( laide dun curseur) la date denvoi des commandes la date du jour et ltat de la commande TRAITEE pour les commandes dont la date denvoi est nul.
Les exceptions
Le langage PL/SQL alphorm.com
Les types dexceptions
Les exceptions
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Les types dexceptions
Noureddine DRISSI Expert consultant bases de donnes
Objectif
Connatre les types dexceptions
Le langage PL/SQL alphorm.com
Les quatre types dexception
Les exceptions systmes nommes
Un nom est attribu aux exceptions par Oracle
Dclenches la suite dune erreur PL/SQL ou Oracle
Les exceptions utilisateurs nommes
Nommes lors de leur dclaration par le dveloppeur
Le langage PL/SQL alphorm.com
Nommes lors de leur dclaration par le dveloppeur
Dclenches la suite derreurs dans le code applicatif
Les exceptions systme anonymes
Non nommes
Dclenches la suite dune erreur PL/SQL mais non nommes
Les quatre types dexception
Les exceptions utilisateur anonymes
Dfinies et dclenches par le dveloppeur
Code compris entre - 20 000 et 20 999
Le langage PL/SQL alphorm.com
Cration dune exception dans un bloc PL/SQL
Le langage PL/SQL alphorm.com
bloc PL/SQL
Cration dune exception
Les exceptions
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Cration dune exception dans un bloc PL/SQL
Noureddine DRISSI Expert consultant bases de donnes
Objectif
Grer une exception dans un bloc PL/SQL
Le langage PL/SQL alphorm.com
Cration dune exception
SyntaxeDECLARE. . .
BEGIN. . .
EXCEPTION
Le langage PL/SQL alphorm.com
WHEN [ OR . . . ]
THEN ;
. . .
[ WHEN OTHERS THEN ; ]
END;
Les exceptions prdfinies
Se trouve dans le package STANDARD de PL/SQL
Quelques exceptions prdfinies
NO_DATA_FOUND (ORA-01403 SQLCODE= +100)
CURSOR_ALREADY_OPEN (ORA-6511 SQLCODE= -6511)
ZERO_DIVIDE (ORA-01476 SQLCODE = -1476)
Le langage PL/SQL alphorm.com
ZERO_DIVIDE (ORA-01476 SQLCODE = -1476)
CASE _NOT_FOUND (ORA-6592 SQLCODE = -6592)
. . .
Les exceptions anonymes
Le langage PL/SQL alphorm.com
Les exceptions anonymes
Les exceptions
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Les exceptions anonymes
Noureddine DRISSI Expert consultant bases de donnes
Objectif
Savoir utiliser les exceptions anonymes
Le langage PL/SQL alphorm.com
Les fonctions SQLCODE et SQLERRM
SQLCODE
Permet de retrouver le code des exceptions Oracle
SQLERRM
Permet de retrouver le message des exceptions Oracle
Le langage PL/SQL alphorm.com
PRAGMA EXCEPTION_INIT
Fonction
Permet dassocier une exception utilisateur un code derreur Oracle
Syntaxe dutilisationDECLARE
EXCEPTION;
Le langage PL/SQL alphorm.com
EXCEPTION;
PRAGMA EXCEPTION_INIT (, );BEGIN
. . .
EXCEPTION
WHEN THEN
;END;
Les exceptions utilisateurs
Le langage PL/SQL alphorm.com
Les exceptions utilisateurs
Les exceptions
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Les exceptions utilisateurs
Noureddine DRISSI Expert consultant bases de donnes
Objectif
Savoir crer et utiliser des exceptions utilisateur
Le langage PL/SQL alphorm.com
Rle des exceptions utilisateur
Permettent de grer les erreurs applicatives
Le langage PL/SQL alphorm.com
SyntaxeDECLARE
EXCEPTION;
PRAGMA EXCEPTION_INIT (, );
BEGIN
. . .
Le langage PL/SQL alphorm.com
. . . RAISE
. . .
EXCEPTION
WHEN THEN
;
END;
RAISE_APPLICATION_ERROR
Permet de faire grer une erreur survenue sur le serveur par un client
Le langage PL/SQL alphorm.com
La porte des exceptions
Quitte le bloc dans lequel il est dclenche et recherche une exception dans les blocs suprieurs
Le langage PL/SQL alphorm.com
Rcapitulatif sur les exceptions et exercices
Le langage PL/SQL alphorm.com
exercices
Rcapitulatif sur les
Les exceptions
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Rcapitulatif sur les exceptions et exercices
Noureddine DRISSI Expert consultant bases de donnes
Objectifs
Rcapitulatif sur les exceptions
Exercices
Le langage PL/SQL alphorm.com
Ce que nous avons tudis
Les types dexceptions
La cration et le dclenchement dune exception dans un bloc PL/SQL
Les exceptions anonymes
Les exceptions utilisateurs
Le langage PL/SQL alphorm.com
Les exceptions: exercice 7 (sol_exercice7.txt)
Crez un bloc PL/SQL qui permet de modifier la comission dun salari et qui dclenche une exception si la comission est suprieur au salaire
Crez un bloc PL/SQL qui permet de dclencher une exception ORA-0292: violation de contrainte (.) dintgrit ... lorsque lon essaye de supprimer un salari qui a trait des commandes
Le langage PL/SQL alphorm.com
Les programmes stocks
Le langage PL/SQL alphorm.com
Les procdures stockes
Les programmes stocks
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Les procdures stockes
Noureddine DRISSI Expert consultant bases de donnes
Objectif
Crer des procdures
Le langage PL/SQL alphorm.com
Quest-ce quune procdure stocke ?
Un sous-programme
Stoke dans la base de donnes sous format compil
Effectue un traitement
Peut recevoir des arguments
Le langage PL/SQL alphorm.com
Cration dune procdure stocke
Syntaxe
[ CREATE [ OR REPLACE ] ] PROCEDURE [ ( [ { IN | OUT | IN OUT } ] TYPE [ , . . . ] ) ]
{ IS | AS }
BEGIN
Le langage PL/SQL alphorm.com
BEGIN
. . .
EXCEPTION THEN ;
END [ ];
Appel dune procdure
Syntaxes
[ (, . . . ] ) ] ;
CALL [ (, . . . ] ) ] ;
EXEC | EXECUTE [ (, . . . ] ) ] ;
Le langage PL/SQL alphorm.com
Suppression dune procdure
Syntaxe
DROP PROCEDURE ;
Le langage PL/SQL alphorm.com
Les fonctions
Le langage PL/SQL alphorm.com
Les fonctions
Les programmes stocks
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Les fonctions
Noureddine DRISSI Expert consultant bases de donnes
Objectifs
Connatre les caractristiques dune fonction
Crer et utiliser des fonctions
Le langage PL/SQL alphorm.com
Quest-ce quune fonction ?
Identique une procdure la diffrence qu'elle retourne une valeur
Stoke dans la base de donnes sous format compil
Peut recevoir des arguments (paramtres)
Le langage PL/SQL alphorm.com
Cration dune fonction
Syntaxe
[ CREATE [ OR REPLACE ] ] FONCTION [ ( [ { IN | OUT | IN OUT } ] TYPE [ , . . . ] ) ] RETURN
{ IS | AS }
BEGIN
Le langage PL/SQL alphorm.com
BEGIN
. . .
RETURN EXPRESSION;
EXCEPTION THEN ;
END [ ];
Appel dune fonction
Syntaxes
En PL/SQL
BEGIN
:= [ (, . . . ] ) ] ;
END;
Le langage PL/SQL alphorm.com
END;
En SQL
SELECT [ (, . . . ] ) ] ;
Suppression dune fonction
Syntaxe
DROP FONCTION ;
Le langage PL/SQL alphorm.com
Les arguments
Le langage PL/SQL alphorm.com
Les arguments
Les programmes stocks
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Les arguments
Noureddine DRISSI Expert consultant bases de donnes
Objectif
Savoir dclarer et utiliser des arguments dans une procdure ou une fonction
Le langage PL/SQL alphorm.com
Dclaration des arguments
Syntaxe
[ { IN | OUT | IN OUT } ] [ NOCOPY ] TYPE [ , . . . ]
IN indique que le paramtre transmis par le programme appelant n'est pas modifiable par la fonction
OUT indique que le paramtre est modifiable par la procdure
IN OUT indique que le paramtre est transmis par le programme appelant et renseign par la
Le langage PL/SQL alphorm.com
IN OUT indique que le paramtre est transmis par le programme appelant et renseign par la fonction
NOCOPY indique que le paramtre est transmis par rfrence (pointeur) et non par copie de la valeur
TYPE reprsente le type SQL ou PL/SQL du paramtre
:= reprsente le symbole d'assignation d'une valeur par dfaut
DEFAULT identique :=
Maintenance des programmes stocks
Le langage PL/SQL alphorm.com
stocks
Maintenance des
Les programmes stocks
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Maintenance des programmes stocks
Noureddine DRISSI Expert consultant bases de donnes
Objectif
Savoir maintenir les programmes stocks
Le langage PL/SQL alphorm.com
La compilation
Syntaxe
ALTER PROCEDURE COMPILE;
ALTER FUNCTION COMPILE;
Le langage PL/SQL alphorm.com
Les meta-donnes
Les vues
USER_SOURCE pour les objets appartenant au schma
ALL_SOURCE pour les objets appartenant aux schmas accessibles
DBA_SOURCE pour les objets appartenant tous les schmas
Le langage PL/SQL alphorm.com
Rcapitulatif sur les programmes stocks et exercices
Le langage PL/SQL alphorm.com
stocks et exercices
Rcapitulatif sur les
Les programmes stocks
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Rcapitulatif sur les programmes stocks et
exercices
Noureddine DRISSI Expert consultant bases de donnes
Objectifs
Rappel sur les programmes stocks
Exercices
Le langage PL/SQL alphorm.com
Ce que nous avons tudis
Les procdures stocks avec et sans argument
Les fonctions avec et sans arguments
Les fonctions renvoyant une valeur scalaire
Les fonctions renvoyant une table
Les arguments avec le mode daffection (IN, OUT, IN OUT, NOCOPY)
Le langage PL/SQL alphorm.com
Les arguments avec le mode daffection (IN, OUT, IN OUT, NOCOPY)
Les programmes stocks: exercice 8 (sol_exercice8.txt)
Crez une procdure stocke nomme updatequantite qui met jour la quantit en stock dun article. La rfrence de larticle et la quantit seront passs en paramtres (arguments) la procdure stocke.
Crez une fonction nomme valeurstock qui retourne la valeur des articles en stock.
Le langage PL/SQL alphorm.com
articles en stock.
Crez une fonction nomme articleparcategorie non stocke en base qui retourne le nombre darticle par catgorie pour toutes les catgories et qui les affichent.
Les packages
Le langage PL/SQL alphorm.com
Introduction aux packages
Les packages
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Introduction aux packages
Noureddine DRISSI Expert consultant bases de donnes
Objectifs
Dcrire la structure dun package
Comprendre le fonctionnement des packages
Le langage PL/SQL alphorm.com
Quest-ce quun package ?
Un regroupement de procdures ou de fonctions
Contient deux sections
Une section de dclaration
Une section de traitement
Le langage PL/SQL alphorm.com
Les avantages de lutilisation dun package
Protection des donnes
Amlioration des performances
Persistance des objets
Le langage PL/SQL alphorm.com
PACKAGE
Structure dun package
Dclaration:PROCEDURES / FONCTIONSVARIABLES / TYPE VARIABLECURSEURS
Spcification de package
Elments publics
Le langage PL/SQL alphorm.com
Corps de package
PROCEDUREBEGIN. . .END;
FONCTIONBEGIN. . .END;
VARIABLES
CURSEURS
Elments privs
Syntaxe de cration dun package
CREATE [ OR REPLACE ] PACKAGE
{ IS | AS }
[ Dclaration des variables et des types ]
[ Dclaration des curseurs ]
Le langage PL/SQL alphorm.com
[ Dclaration des procdures et des fonctions ]
END ;
Syntaxe de cration dun corps de package
CREATE [ OR REPLACE ] PACKAGE BODY { IS | AS }
[ Dclaration des variables et des types ][ Dclaration des curseurs ][ Dclaration des procdures et des fonctions ]
Le langage PL/SQL alphorm.com
[ Dclaration des procdures et des fonctions ][ BEGIN
;[ EXCEPTIONS
END ;
Utilisation des curseurs dans les packages
Le langage PL/SQL alphorm.com
packages
Utilisation des curseurs
Les packages
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Utilisation des curseurs dans les packages
Noureddine DRISSI Expert consultant bases de donnes
Objectif
Utiliser des curseurs dans les packages
Le langage PL/SQL alphorm.com
Spcification du curseur
Obligation dutiliser la clause RETURN avec le curseur associ
Un enregistrement %ROWTYPE
Un enregistrement dfini par lutilisateur
Le langage PL/SQL alphorm.com
Maintenance des packages
Le langage PL/SQL alphorm.com
Maintenance des
Les packages
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Maintenance des packages
Noureddine DRISSI Expert consultant bases de donnes
Objectif
Modifier et supprimer un package
Le langage PL/SQL alphorm.com
Syntaxe de modification dun package
ALTER PACKAGE COMPILE [ PACKAGE | BODY ] ;
Le langage PL/SQL alphorm.com
Syntaxe de suppression dun package
DROP [ PACKAGE | BODY ] ;
Le langage PL/SQL alphorm.com
Rcapitulatif sur les packages et exercices
Le langage PL/SQL alphorm.com
exercices
Rcapitulatif sur les
Les packages
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Rcapitulatif sur les packages et exercices
Noureddine DRISSI Expert consultant bases de donnes
Objectifs
Rappel sur les packages
Exercices
Le langage PL/SQL alphorm.com
Ce que nous avons tudis
Ce quest un package
La structure dun package
Spcification de package
Corps de package
Utilisation des fonctions, procdures et des curseurs dans un package
Le langage PL/SQL alphorm.com
Utilisation des fonctions, procdures et des curseurs dans un package
Utiliser les objets dun package
Maintenir un package
Les packages: exercice 9 (sol_exercice9.txt)
Crez un package nomm gestEmp avec les objets suivants:
Un fonction qui contrle lexistence dun salari partir de son numro.
Une procdure qui permet de supprimer un salari partir de son numro.
Une procdure qui permet daugmenter le salaire dun salari. La procdure doit reevoir deux arguments: le premier est le numro du salari, le deuxime le pourcentage daugmention).
Le langage PL/SQL alphorm.com
doit reevoir deux arguments: le premier est le numro du salari, le deuxime le pourcentage daugmention).
Une procdure qui affiche tous les salaris dont le salaire est suprieur la moyenne des salaires (cette procdure devra sappuyer sur un curseur nomm cur_sal )
Les triggers (dclencheurs)
Le langage PL/SQL alphorm.com
Introduction
Les dclencheurs
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Introduction
Noureddine DRISSI Expert consultant bases de donnes
Objectifs Dcrire un dclencheur
Connatre les caractristiques dun dclencheur
Le langage PL/SQL alphorm.com
Un dclencheur cest quoi ? Dfinit une action qui doit se dclencher lorsquun vnement survient
sur la base de donnes
Objet stock en base
Associ une table ou une vue
Pas de COMMIT/ROLLBACK dans un dclencheur
Le langage PL/SQL alphorm.com
Pas de COMMIT/ROLLBACK dans un dclencheur
Avantage du dclencheur Ajouter des contraintes sur les valeurs des colonnes dune table
Raliser un audit des changements sur les donnes (logs)
Ajout des rgles de gestion
Publier des informations concernant divers vnements
Le langage PL/SQL alphorm.com
Les niveaux dexcutions De niveau table (STATEMENT)
Excut une seule fois quelque soit le nombre de ligne modifi
De niveau enregistrement (ROW)
Excut sparment pour chaque ligne modifie
FOR EACH ROW
Le langage PL/SQL alphorm.com
FOR EACH ROW
Les types de dclencheurs ? Les dclencheurs LMD
Avant ou aprs
INSERT, UPDATE ou DELETE
Les dclencheurs INSTEAD OF
De niveau ligne seulement
Le langage PL/SQL alphorm.com
De niveau ligne seulement
Sexcute la place de linstruction qui a dclench le trigger
Les dclencheurs SYSTEME
Dmarrage ou arrt dune base
A la cration, suppression ou modification dun objet (DDL)
A la connexion ou dconnexion dun utilisateur
Syntaxe de crationCREATE [ OR REPLACE ] TRIGGER
{ BEFORE | AFTER | INSTEAD OF } [ ]
[ CLAUSE_REFERENCING] [ WHEN CONDITION ] [ FOR EACH ROW ]
[ DECLARE . . . ]
BEGIN
Le langage PL/SQL alphorm.com
. . .
[ EXCEPTION . . . ]
END [ ] ;
Les dclencheurs LMD
Le langage PL/SQL alphorm.com
Les dclencheurs LMD
Les dclencheurs
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Les dclencheurs LMD
Noureddine DRISSI Expert consultant bases de donnes
Objectif Crer des dclencheurs LMD
Le langage PL/SQL alphorm.com
Rappel: syntaxe de crationCREATE [ OR REPLACE ] TRIGGER
{ BEFORE | AFTER | INSTEAD OF } [ OR ]
[ CLAUSE_REFERENCING] [ WHEN CONDITION ] [ FOR EACH ROW ]
[ DECLARE . . . ]
Le langage PL/SQL alphorm.com
BEGIN
. . .
[ EXCEPTION . . . ]
END [ ] ;
Dclenchement Ce qui dclenche le dclencheur
INSERT
AVANT ou APRES insertion
UPDATE
Le langage PL/SQL alphorm.com
AVANT ou APRES mise jour
Possibilit UPDATE TO [ , ]
DELETE
AVANT ou APRES suppression
Caractristiques Doit possder un nom unique
La dfinition du trigger porte sur:
une est une seule table
pas une vue.
Le langage PL/SQL alphorm.com
Nombre de trigger par table Possibilit de crer un nombre illimit de dclencheurs
Possibilit de dfinir plusieurs dclencheurs dun mme type
Excution squentielle des dclencheurs sil en existe plusieurs
Le langage PL/SQL alphorm.com
Le niveau dexcution
Le langage PL/SQL alphorm.com
Le niveau dexcution
Les dclencheurs
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Le niveau dexcution
Noureddine DRISSI Expert consultant bases de donnes
Objectif Comprendre le niveau dexcution dun dclencheur
Le langage PL/SQL alphorm.com
Rappel: syntaxe de crationCREATE [ OR REPLACE ] TRIGGER
{ BEFORE | AFTER | INSTEAD OF } [ ]
[ CLAUSE_REFERENCING] [ WHEN CONDITION ] [ FOR EACH ROW ]
[ DECLARE . . . ]
BEGIN
Le langage PL/SQL alphorm.com
BEGIN
. . .
[ EXCEPTION . . . ]
END [ ] ;
Niveaux dexcution De niveau table (STATEMENT)
Excut une seule fois quelque soit le nombre de ligne modifi
De niveau enregistrement (ROW)
Excut sparment pour chaque ligne modifie
FOR EACH ROW
Le langage PL/SQL alphorm.com
FOR EACH ROW
Accs aux valeurs via :OLD et :NEW
Le langage PL/SQL alphorm.com
Accs aux valeurs via :OLD
Les dclencheurs
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Accs aux valeurs via :OLD et :NEW
Noureddine DRISSI Expert consultant bases de donnes
Objectif Crer des dclencheurs en utilisant les valeurs des attributs :OLD et
:NEW
Le langage PL/SQL alphorm.com
Valeur des attributs Possibilit daccder la valeur des attributs avant et aprs modification
:OLD.nom_attribut (permet daccder la valeur avant modification)
:NEW.nom_attribut (permet daccder la valeur aprs modification)
Le langage PL/SQL alphorm.com
Prcisions INSERT
Pas daccs llment OLD (nexiste pas)
UPDATE
Accs possible llment OLD et NEW
Le langage PL/SQL alphorm.com
DELETE
Pas daccs llment NEW (nexiste plus)
Utilisable uniquement avec un dclencheur de niveau enregistrement
La clause REFERENCING Fonction
Permet de crer un alias pour :OLD et :NEW
SYNTAXE
REFERENCING [ OLD AS ] [ NEW AS ]
Le langage PL/SQL alphorm.com
Dclenchement conditionnel et les prdicats
Le langage PL/SQL alphorm.com
prdicats
Dclenchement
Les dclencheurs
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Dclenchement conditionnel et les prdicats
Noureddine DRISSI Expert consultant bases de donnes
Objectifs Crer un dclencheur avec un dclenchement conditionnel (WHEN)
Crer un dclencheur avec les prdicats
Le langage PL/SQL alphorm.com
Clause WHEN Ne sapplique quaux dclencheurs de niveau enregistrement
Syntaxe
WHEN
Le langage PL/SQL alphorm.com
Les fonctions prdicats INSERTING
Retourne TRUE si linstruction LMD est un INSERT
UPDATING
Retourne TRUE si linstruction LMD est un UPDATE
Le langage PL/SQL alphorm.com
DELETING
Retourne TRUE si linstruction LMD est un DELETE
Complment sur les dclencheurs
Le langage PL/SQL alphorm.com
Complment sur les
Les dclencheurs
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Complment sur les dclencheurs
Noureddine DRISSI Expert consultant bases de donnes
Objectifs Connatre lutilisation des dclencheurs INSTEAD OF
Utilisation de lordre PRAGMA AUTONOMOUS_TRANSACTION
Raliser les tches dexploitation des dclencheurs
Le langage PL/SQL alphorm.com
Les dclencheurs INSTEAD OF Dfinis uniquement sur des objets vues
Sexcute la place de lordre LMD qui a dclench le dclencheur
Le langage PL/SQL alphorm.com
Transaction autonome Les dclencheurs de niveau ligne, ne peuvent pas lire ou modifier la
table (table en mutation) sur laquelle porte le dclencheur
Les dclencheurs de niveau table peuvent lire et modifier la table sur laquelle porte le dclencheur
Possibilit dutiliser la directive PRAGMA AUTONOMOUS_TRANSACTION
Le langage PL/SQL alphorm.com
AUTONOMOUS_TRANSACTION
Possibilit dutiliser les commandes COMMIT et ROLLBACK
Autres caractristiques Triggers en cascade
Un trigger peut provoquer le dclenchement d'un autre trigger.
ORACLE autorise jusqu' 32 triggers en cascade un moment donn.
Limite
Un trigger ligne ne peut pas lire et/ou modifier la table concerne (appele
Le langage PL/SQL alphorm.com
Un trigger ligne ne peut pas lire et/ou modifier la table concerne (appele table mutante) par l'instruction (INSERT, UPDATE ou DELETE) qui a dclench le trigger.
Cration de dclencheurs Conditions ncessaires pour crer un trigger
Privilge CREATE TRIGGER
Possder (ou privilege ALTER, ALTER ANY) la table sur laquelle on veut dfinir le dclencheur
Modification de triggers
Le langage PL/SQL alphorm.com
Modification de triggers
CREATE TRIGGER suivie de OR REPLACE ou bien on supprime le trigger (DROP TRIGGER nomtrigger) et on le cre nouveau.
Activation et dsactivation Un trigger peut tre activ ou dsactiv.
Si dsactiv, ORACLE le stocke mais lignore.
Possibilit de dsactiver un trigger si :
il rfrence un objet non disponible
on veut charger rapidement un volume de donnes important ou recharger
Le langage PL/SQL alphorm.com
on veut charger rapidement un volume de donnes important ou recharger des donnes dj contrles.
Par dfaut, un trigger est activ ds sa cration.
Activation et dsactivation Pour dsactiver ou activer un trigger , on utilise linstruction ALTER
ALTER TRIGGER DISABLE;
ALTER TABLE DISABLE ALL TRIGGERS;
ALTER TRIGGER ENABLE;
ALTER TABLE ENABLE ALL TRIGGERS;
Le langage PL/SQL alphorm.com
ALTER TABLE ENABLE ALL TRIGGERS;
Gestion des exceptions La procdure RAISE_APPLICATION_ERROR
(error_number,error_message)
error_number doit tre un entier compris entre -20000 et -20999
error_message doit tre une chane de 500 caractres maximum.
Quand cette procdure est appele, elle termine le trigger, dfait la transaction (ROLLBACK), renvoie un numro d'erreur dfini par l'utilisateur et
Le langage PL/SQL alphorm.com
Quand cette procdure est appele, elle termine le trigger, dfait la transaction (ROLLBACK), renvoie un numro d'erreur dfini par l'utilisateur et un message l'application.
Rcapitulatif sur les dclencheurs et exercices
Le langage PL/SQL alphorm.com
exercices
Rcapitulatif sur les
Les dclencheurs
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Rcapitulatif sur les dclencheurs et exercices
Noureddine DRISSI Expert consultant bases de donnes
Objectifs
Rappel sur les dclencheurs
Exercices
Le langage PL/SQL alphorm.com
Ce que nous avons tudis
Les dclencheurs LMD
Le niveau dexcution des dclencheurs (INSTRUCTION ou LIGNE)
Comment accder aux valeurs via OLD et NEW
Dclenchement conditionnel (WHEN)
Le langage PL/SQL alphorm.com
Les prdicats (INSERTING, UPDATING et DELETING)
Les dclencheurs INSTEAD OF
La directive PRAGMA AUTONOMOUS_TRANSACTION
Les tches dexploitation des dclencheurs
Les dclencheurs: exercice 10 (sol_exercice10.txt)
Crez un dclencheur LMD nomm trigLogClients qui se dclenche aprs toute instruction INSERT, UPDATE ou DELETE sur la table clients . Le trigger doit tracer les informartions ci-dessous dans une table que vous aurez cre au pralable:
La date dexcution et le user ayant effectu linstruction
La valeur AVANT et APRES pour les colonnes NUMERO_CLI, NOM, ADRESSE,
Le langage PL/SQL alphorm.com
La valeur AVANT et APRES pour les colonnes NUMERO_CLI, NOM, ADRESSE, CODE_POSTAL, VILLE et TELEPHONE
Linstruction qui dclenche le dclencheur (INSERT, UPDATE ou DELETE)
Conclusion
Le langage PL/SQL alphorm.com
Le mot de la fin
Conclusion
Le langage PL/SQL alphorm.com
Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum
Le mot de la fin
Noureddine DRISSI Expert consultant bases de donnes
Objectifs
Matriser le langage PL/SQL
Utiliser des traitements stocks et des triggers dans les applications
Le langage PL/SQL alphorm.com
Plan de cours Module 1: Prsentation du PL/SQL
Module 2: Les variables
Module 3: Accs la base de donnes et ordres SQL
Module 4: Les structures de contrle
Module 5: Les curseurs
Le langage PL/SQL alphorm.com
Module 5: Les curseurs
Module 6: Gestion des erreurs
Module 7: Les programmes stocks
Module 8: Les triggers
Module 9: Les packages
Module 10: Les dclencheurs
Conclusion
A venir:
Le transact SQL
La modlisation au sens MERISE
Le langage PL/SQL alphorm.com
FIN
Merci pour votre attention
Conclusion
Le langage PL/SQL alphorm.com
Merci pour votre attention