Plan du cours – Partie 1
Installation et configuration d’un serveur SQL
(Pratique)
Gestion des utilisateurs
Stratégie de Sauvegarde et de restauration
Importation et exportation des données
1
Plan du cours – Partie 2
Langage SQL
Extension du langage (Environnement Client-Serveur)
Gestion des vues
Gestion des procédures stockées
Gestion des déclencheurs (Triggers)
Gestion des transactions
2
PL/SQL
• Pourquoi PL/SQL?
• Variables, structures de contrôle,
• Curseurs, interaction avec la base,
• Sous-programmes, paquetages,
• Exceptions,
• Déclencheurs (triggers)
3
Procédural Language/SQL
• PL/SQL est un langage procédural qui intègre des
ordres SQL,
• Initialement typiquement Oracle ; apparaît dans la
norme SQL3,
• Il permet l’utilisation des blocs SQL et d’exprimer des
règles de gestion complexes sous forme de
procédures stockées et de triggers,
• Il permet de lier plusieurs requêtes SQL avec des
variables et dans des structures de programmation
habituelles.
4
Procédural Language/SQL
• Programme PL/SQL = bloc (procédure anonyme,
procédure nommée, fonction nommée) :
• PL/SQL n’interprète pas une commande, mais un
ensemble de commandes contenues dans un bloc
PL/SQL. Ce bloc peut comporter plusieurs sous
blocs,
5
Procédural Language/SQL
6
Structure d’un bloc PL/SQL
DECLARE
-- section de déclarations
-- section optionnelle …
BEGIN
-- traitement, avec d’éventuelles directives SQL
-- section obligatoire …
EXCEPTION
-- gestion des erreurs
-- section optionnelle …
END;
7
Blocs imbriqués
-- BLOC A
DECLARE
BEGIN
-- BLOC B
DECLARE
BEGIN
-- BLOC C
DECLARE
BEGIN
EXCEPTION
END;
EXCEPTION
END;
EXCEPTION
END;
8
Les variables PL/SQL
Types de variables PL/SQL :
• Scalaires : par exemple NUMBER (5,2), VARCHAR2,
DATE, BOOLEAN, CHAR, <Attribut>%TYPE…
• Composites : %ROWTYPE, RECORD, TABLE
Remarques :
• Jusqu’à 30 caractères,
• Insensible à la casse ! ,
9
Les variables PL/SQL
• On peut documenter un code PL/SQL. Les différentes
façons d’introduire des commentaires :
– -- Commentaire sur une seule ligne
– /* Commentaire sur plusieurs
lignes */
• Toute variable PL/SQL doit obligatoirement être déclarée
avant utilisation. Cette déclaration s’effectue dans la zone
DECLRAE.
10
10
Les variables PL/SQL
nomvariable [CONSTANT]
{type | variable%TYPE | table.%ROWTYPE }
[NOT NULL]
[{:= | DEFAULT } expression PL/SQL]
Exemples de déclaration :
vNumCli NUMBER(5) ;
vNomCli VARCHAR2(12) ;
vDateCde DATE ;
vPoids NUMBER(5,2) ;
vSexe CHAR(1) ;
11
11
Les variables PL/SQL
vNomClient [Link]%TYPE;
vAdresseClient [Link]%TYPE;
Déclaration %ROWTYPE
PL/SQL permet de déclarer une variable composite de même
type que les tuples d’une table.
vLignesClients clients%ROWTYPE;
Les composantes de la variable composite, identifiées par
[Link], sont du même type que les
colonnes correspondantes de la table.
12
12
Affectation des valeurs aux variables PL/SQL
On peut affecter des valeurs aux variables PL/SQL soit :
• En utilisant l’opérateur d’assignation :=
vAge := SYSDATE – vDateNaissance ;
vNomClient := UPPER ('Nom') ;
• A partir d’un ordre SELECT
…
SELECT clinom, cliadresse
INTO vNom, vAdresse
FROM clients
WHERE clinum = 1200 ;
…
13
13
Affectation des valeurs aux variables PL/SQL
Remarque:
L’ordre SELECT doit retourner une seule ligne, dans le cas
contraire une erreur est générée.
14
14
Structures de contrôle
1. Traitement conditionnel
Syntaxe
IF condition_1 THEN
Instructions_1;
ELSIF condition_2
Instructions_2;
ELSE
Instructions_3;
END IF;
Les opérateurs relationnels sont: =, <, >, !=, >=, <=, IS NULL, IS NOT
NULL, BETWEEN, LIKE, AND, OR
15
15
Structures répétitives
La boucle de base
PL/SQL permet d’effectuer des traitements répétitifs grâce à la
clause LOOP.
BEGIN
…
LOOP
Instructions
END LOOP;
…
END;
16
16
Affectation des valeurs aux variables PL/SQL
Remarque :
La boucle ci-dessus s’exécute indéfiniment,
l’instruction EXIT permet de stopper l’exécution de la
boucle LOOP.
BEGIN BEGIN
… …
LOOP LOOP
Instructions Instructions
EXIT WHEN condition; IF condition THEN
END LOOP; EXIT;
… END IF;
END; END LOOP;
…
END;
17
17
Structures répétitives
La boucle FOR : La boucle WHILE :
Syntaxe Syntaxe
FOR compteur IN WHILE condition
val_1..val_2 LOOP
LOOP instructions;
instructions; END LOOP;
END LOOP;
18
18
Curseurs
Un curseur est une zone mémoire de taille fixe capable de
stocker plusieurs enregistrements et de gérer l’accès à ces
enregistrements.
Types de curseurs :
– Curseurs implicites :
Déclarés implicitement et manipulés par SQL pour toute
requête SQL et pour les interrogations qui retournent un seul
enregistrement
19
19
Curseurs
– Curseurs explicites :
Déclarés et manipulés par l’utilisateur pour les
interrogations qui retournent plus d’un enregistrement.
Etapes d’utilisation d’un curseur :
– Déclaration
– Ouverture
– Traitement des lignes
– Fermeture
20
20
Déclaration d’un curseur
Syntaxe :
CURSOR nom_curseur IS requête ;
Cette déclaration se fait dans la section DECLARE.
Exemple :
DECLARE
CURSOR c_clients IS
SELECT clinum, clinom, cliadresse
FROM clients;
BEGIN
…
END;
21
21
Déclaration d’un curseur
L’ensemble de lignes renvoyées par une interrogation multi lignes s’appelle
un ResultSet (jeu de résultats).
ResultSet
1100 VAUDAN 31, Rue Tanger 75015 Paris
curseur
1200 HOMEYER 1, Av Foch 67000 Strasbourg
1300 BUELLET 27, Rue Pierre Avia 75015 Paris
22
22
Ouverture d’un curseur
Elle permet :
• L’allocation mémoire du curseur,
• L’analyse syntaxique et sémantique de la requête,
Elle se fait dans la section BEGIN.
Syntaxe :
OPEN c_clients ;
23
23
Ouverture d’un curseur
Exemple :
DECLARE
CURSOR c_clients IS
SELECT clinum, clinom, cliadresse
FROM clients;
BEGIN
OPEN c_clients;
…
END;
24
24
Traitement des lignes
Les lignes retournées par l’ordre SELECT sont traitées une par
une. La valeur de chaque colonne doit être stockée dans une
variable réceptrice.
Syntaxe :
FETCH c_salaries INTO variables_receptrices ;
La clause FETCH ne récupère qu’un seul enregistrement. Pour
accéder à l’ensemble des lignes de l’ordre SELECT, il faut
prévoir une boucle.
Le traitement des lignes se fait dans le corps du bloc PL/SQL.
25
25
Exemple d’utilisation d’un curseur
DECLARE IF vSalaire < 1000 THEN
vNumero UPDATE emp
[Link]%TYPE; SET sal = sal*1.1
vSalaire [Link]%TYPE; WHERE empno = vNumero;
-- Déclaration du curseur ELSIF vSalaire BETWEEN 1000 AND 3000
CURSOR c_emp IS THEN
SELECT empno, sal UPDATE emp
FROM emp SET sal = sal*1.05
ORDER BY empno; WHERE sal = vNumero;
BEGIN ELSE
OPEN c_emp; UPDATE emp
LOOP SET comm = comm + 300
FETCH c_emp WHERE sal = vNumero;
INTO vNumero, vSalaire; END IF;
EXIT WHEN vSalaire > 3000
END LOOP;
END;
26
26
Fermeture d’un curseur
Syntaxe :
CLOSE c_emp;
Libération de la place mémoire allouée.
Attributs d’un curseur:
Les attributs permettent de connaître l’état d’un curseur.
%FOUND et %NOTFOUND déterminent si toutes les lignes
retournées par le curseur ont étés traitées.
%ISOPEN précise si le curseur est ouvert.
%ROWCOUNT indique le nombre de lignes déjà traitées.
27
27
Curseurs explicites paramétrés
Objectif : paramétrer la requête associée à un curseur pour éviter de
multiplier les curseurs similaires dans le même bloc PL/SQL.
Syntaxe :
CURSOR nom_curseur (param1 type1, param2 type2, …]) IS …;
Les valeurs des paramètres sont transmises à l’ouverture du curseur :
OPEN nom_curseur (valeurPar1, valeurPar2, …);
Il faut évidemment fermer le curseur avant de l’appeler avec d’autres
valeurs pour les paramètres.
28
28
Simplification d’écriture des curseurs : Exemple
DECLARE UPDATE emp
CURSOR c_emp IS SET sal = sal*1.1
SELECT empno, sal WHERE empno = enreg_emp.empno;
FROM emp ELSIF enreg_emp.salBETWEEN 1000 AND
ORDER BY sal; 3000 THEN
--Déclaration d’une structure UPDATE emp
enreg_emp c_emp%ROWTYPE; SET sal = sal*1.05
BEGIN WHERE empno = enreg_emp.empno;
OPEN c_emp; END IF;
LOOP EXIT WHEN c_emp%NOTFOUND ;
FETCH c_emp INTO enreg_emp; END LOOP;
IF enreg_emp.sal < 1000 THEN CLOSE c_emp;
END;
29
29
Simplification d’écriture des curseurs : Exemple
DECLARE
BEGIN
FOR enreg_emp IN ( SELECT empno, sal
FROM emp
ORDER BY sal
)
LOOP
IF enreg_emp.sal < 1000 THEN
UPDATE emp
SET sal = sal*1.1
WHERE empno = enreg_empno.empno;
ELSIF enreg_emp.sal BETWEEN 1000 AND 3000 THEN
UPDATE emp
SET sal = sal*1.05
WHERE empno = enreg_emp.empno;
END IF;
END LOOP;
END;
30
30
Procédures et fonctions
Définition :
Ce sont les sous programmes hébergés par la base de
données : ils peuvent être appelés à partir d’une autre
procédure, fonction ou encore depuis un programme
exécutable extérieur à la base de données.
Un sous programme est un bloc PL/SQL nommé qui peut
accepter des paramètres et être appelé.
31
31
Procédures et fonctions
• Les performances sont assurées par les facteurs suivants :
– Les procédures stockées n’ont pas besoin d'être analysées
une seconde fois à l'exécution (gain de temps : pas de
recompilation ).
– Un gain de place en mémoire contribue à cette amélioration
de performances car la procédure chargée en mémoire pour
son exécution est partagée par tous les objets qui la
demandent (applications) . De plus la procédure s’exécute
immédiatement si elle en mémoire (réduction des accès
disque),
– Réduction du trafic sur le réseau (soumission d'un bloc
PL/SQL au moteur au lieu d'une commande Sql),
32
32
Procédures PL/SQL
Syntaxe :
PROCEDURE laProcedure (param1 IN | OUT | IN type 1, param2…) IS
BEGIN
…
EXCEPTION
END;
Une procédure se termine à la fin du bloc ou par une instruction RETURN.
33
33
Fonctions PL/SQL
Syntaxe :
FUNCTION laFonction (param1 IN | OUT | IN type 1,
param2…)RETURN type_variable IS
BEGIN
…
EXCEPTION
END;
Une fonction se termine par l’instruction RETURN qui doit renvoyer un
résultat.
34
34
Procédures et fonctions PL/SQL
Remarque :
Comme tout objet SQL, une procédure ou une fonction peut
être créée, modifiée ou supprimée.
Création CREATE PROCEDURE… CREATE FUNCTION…
Modification CREATE OR REPLACE CREATE OR REPLACE
PROCEDURE… FUNCTION…
Suppression DROP PROCEDURE… DROP FUNCTION…
35
35
Procédures et fonctions PL/SQL
Exécution et utilisation de procédures et de fonctions
SQL> CREATE OR REPLACE FUNCTION leDouble (p_telNombre IN
NUMBER)
RETURN NUMBER IS
le_double NUMBER;
BEGIN
le_double := p_telNombre*2;
RETURN le_double;
END ;
/
36
36
Procédures et fonctions PL/SQL
SQL> DECLARE
v_leNombre NUMBER := 12;
v_ledouble NUMBER;
BEGIN
le_double := leDouble(v_telNombre);
END ;
Remarques :
Pour repérer les erreurs, on dispose de la commande SHOW ERRORS.
Pour afficher les variables contenues dans un bloc PL/SQL, il faut inclure le
package (définit plus loin) standard dbms_output.put_line.
37
37
Paquetages
Un paquetage PL/SQL permet de regrouper un ensemble de
procédures, de fonctions, de variables et de curseurs au sein
d’un ensemble cohérent de services.
Distinction entre ce qui est accessible depuis l’extérieur et ce
qui n’est accessible qu’à l’intérieur du paquetage :
encapsulation.
38
38
Paquetages
Structure :
– Section de spécification : déclarations des variables,
curseurs, sous-programmes accessibles depuis l’extérieur,
– Section d’implémentation : code des sous-programmes
accessibles depuis l’extérieur + sous-programmes
accessibles en interne (privés).
39
39
Paquetages
Packages prédéfinis :
Parmi eux, le package DBMS_OUTPUT : affichage pour la
mise au point des programmes PL/SQL. Il est utilisé pour
envoyer des messages contrôlant l’exécution d’un programme
PL/SQL ou en encore lors du débogage.
Exceptions
PL/SQL permet de définir dans une zone particulière (de
gestion d’exception), l’attitude que le programme doit avoir
lorsque certaines erreurs définies ou prédéfinies se produisent.
40
40
Paquetages
Un certain nombre d’exceptions sont prédéfinies sous Oracle.
Citons, pour les plus fréquentes : NO_DATA_FOUND (devient
vrai dès qu’une requête renvoie un résultat vide),
TOO_MANY_ROWS (requête renvoie plus de lignes
qu’escompté), CURSOR_ALREADY_OPEN (curseur déjà
ouvert), INVALID_CURSOR (curseur invalide)...
41
41
Paquetages
Le développeur peut définir ses propres exceptions. Dans ce
cas, il doit définir celles ci dans la zone de déclaration.
Exemple :
v_exception EXCEPTION ;
Puis, cette exception est levée quelque part dans le
programme (après un test non concluant, par exemple), par
l’instruction :
RAISE v_exception ;
Enfin, dans la zone d’exception un traitement est affecté à
chaque exception possible (définie ou prédéfinie) :
42
42
Paquetages
EXCEPTION
WHEN <exception1> [OR <exception2> OR ...] THEN
<instructions>
WHEN <exception3> [OR <exception2> OR ...] THEN
<instructions>
WHEN OTHERS THEN <instructions>
END;
Evidemment, un seul traitement d’exception peut se produire
avant la sortie du bloc.
43
43
Les exceptions internes
Une erreur interne est produite quand un bloc PL/SQL viole une
règle d'Oracle ou dépasse une limite dépendant du système
d'exploitation.
Les erreurs Oracle générées par le noyau sont numérotées, or le
gestionnaire des exceptions de PL/SQL, ne sait que gérer des
erreurs nommées.
Pour cela PL/SQL a redéfini quelques erreurs Oracle comme des
exceptions. Ainsi, pour gérer d'autres erreurs Oracle, l'utilisateur doit
utiliser le gestionnaire OTHERS ou EXCEPTION_INIT pour nommer
ces erreurs.
Les exceptions fournies par Oracle sont regroupées dans ce tableau
44
44
Paquetages
Valeur Sql Erreur Valeur Erreur
Nom d'exception Nom d'exception
Code Oracle Sql Code Oracle
CURSOR_ALREADY_OPEN -6511 ORA-06511 NOT_LOGGED_ON -1012 ORA-01012
DUP_VAL_ON_INDEX -1 ORA-00001 PROGRAM_ERROR -6501 ORA-06501
INVALID_CURSOR -1001 ORA-01001 STORAGE_ERROR -6500 ORA-06500
INVALID_NUMBER -1722 ORA-01722 TIMEOUT_ON_RESOURCE -51 ORA-00051
LOGIN_DENIED -1017 ORA-01717 TOO_MANY_ROWS -1422 ORA-01422
NO_DATA_FOUND -1403 ORA-01413 TRANSACTION_BACKED_OUT -61 ORA-00061
VALUE_ERROR -6502 ORA-06502 ZERO_DIVIDE -1476 ORA-01476
45
45
Paquetages
OTHERS : toutes les autres erreurs non explicitement nommées.
Pour gérer les exceptions, le développeur doit écrire un gestionnaire
des exceptions qui prend le contrôle du déroulement du bloc
PL/SQL en présence d'une exception.
Chaque gestion d'exception consiste à spécifier son nom d'erreur
après la clause WHEN et la séquence de la commande à exécuter
après le mot clé THEN.
46
46
Les déclencheurs (“triggers”)
Les déclencheurs ou ”triggers” sont des séquences d’actions définis
par le programmeur qui se déclenchent, sur des actions modifiant
les données de la table sur laquelle porte le trigger.
Un trigger sera un objet stocké (comme une table ou une
procédure).
Syntaxe :
CREATE [OR REPLACE] TRIGGER <nomTrg>
{BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON <nomTable>
[FOR EACH ROW [WHEN (<condition>)]]
<corps du trigger>
47
47
Les déclencheurs (“triggers”)
Le type d’action qui déclenche le trigger détermine son moment
d’exécution : il est précisé au début de l’écriture du code de trigger.
(INSERT|DELETE|UPDATE)
Le trigger peut être déclenché avant ou après cette action
(BEFORE|AFTER)
L’option FOR EACH ROW [WHEN (<condition>)] fait exécuter
le trigger à chaque modification d’une ligne de la table
spécifiée (on dit que le trigger est de ”niveau ligne”). En
l’absence de cette option, le trigger est exécuté une seule fois
(”niveau table”).
48
48
Les déclencheurs (“triggers”)
Caractéristiques
• Un déclencheur a un nom.
• Il est associé à une table et une seule.
• Il peut être actif ou inactif.
• Il est opérationnel jusqu’à sa suppression ou la suppression
de la table à laquelle il est associé.
49
49
Les déclencheurs (“triggers”)
Référence aux colonnes d’une table
• Dans le code associé aux déclencheurs de niveau ligne, on
peut accéder aux valeurs des attributs de la ligne modifiée
par les variables.:old et :new
• Pour un déclencheur sur INSERT, les nouvelles valeurs sont
dans :new.<nom attribut>
• Pour un déclencheur sur UPDATE, les anciennes valeurs
sont dans :old.<nom d’attribut>. Les nouvelles valeurs
sont dans :new.<nom d’attribut>
• Pour un déclencheur sur DELETE, les anciennes valeurs
sont dans :old.<nom d’attribut>
50
50
Les déclencheurs (“triggers”)
Remarques
• N’utilisez pas les triggers pour effectuer des contrôles
d’intégrité.
• Pour éviter de supprimer et recréer un trigger : create or
replace trigger
• Pour un trigger relatif à plusieurs événements, utiliser les
prédicats inserting, updating, deleting
if inserting then …end if;
if updating then …end if;
if deleting then …end if;
51
51
Les déclencheurs (“triggers”)
Activer/désactiver
Un trigger est crée par la clause CREATE TRIGGER ou CREATE
OR REPLACE TRIGGER. Un trigger existant peut être détruit par
DROP TRIGGER.
ALTER TRIGGER nomTrigger ENABLE; -- active le trigger
ALTER TRIGGER nomTrigger DISABLE; -- désactive le trigger
ALTER TABLE nomTable ENABLE ALL TRIGGERS;
ALTER TABLE nomTable DISABLE ALL TRIGGERS;
52
52
Exercice
Soit une base de données dont le schéma est le suivant :
CLIENTS(ci,civilité,nom,prénom,sexe,adr_lib,adr_cp,adr_ville)
COMMANDES(ref, datec, montant, ci#)
LIGNES_CDES(numéro, qte, remise, ref#, cp#)
PRODUITS(cp, libelle, prix_u, catégorie)
53
53
Exercice
• InsertClient -> ajout d’un nouveau client
• InsertProduit -> ajout d’un nouveau produit
• InsertClient -> création d’une entête de commande
• InsertLignesCde -> ajout d’une ligne de commande
• Les procédures de suppresion
• Les procédurs de MAJ…
54
54
?
55
55