Embed Size (px)
Citation preview
08/12/2009
1
Vues: définition Table virtuelle de la base de données dont le contenu
est défini par un requête
La vue n’existe pas en tant qu’ensemble de données stockées sur le disque
seule la requête décrivant la vue est stockée
2
Vues: avantages indépendance logique
exemple:
infoDepartement_vue(idDept, nomDept, Loc, nbEmp, SalTot);
simplification de requêtes
département de plus de 10 employés et dont la masse salariale
est inférieure à 20 K€ ?
isolation des modifications- - emp sans indiquer le salaire
emp_vue(idEmp, nom, prénom, job, idDept)
sécurité
intégrité des données
3
Vues: inconvénients performance
la traduction de la requête peut être longue
restriction des mises à jour possibilité de mise à jour qu’à partir de vues simples
4
Vues: syntaxeCREATE [OR REPLACE]
VIEW nomvue [col,…]
AS SELECT syntaxe select
[WITH CHECK OPTION [CONSTRAINT] nom];
DROP VIEW nomvue;
RENAME ancien_nom TO nouveau_nom;
5
Vues: exempleCREATE VIEW emp_recherche
AS
SELECT * FROM emp, dpt
WHERE emp.idEmp = dpt.idDept
AND nomDept=’Recherche’ ;
SELECT * FROM emp_recherche ;
6
08/12/2009
2
Vues: mise à jour Conditions construite sur une seule table possible sur vue comportant une jointure si les
modifications/suppressions/insertions ne concernent qu’une table de la jointure
pas de GROUP BY les colonnes résultats doivent être des colonnes réelles de la
table (non calculées) la vue contient toutes les colonnes NOT NULL de la table
Permet d’insérer à travers la vue de nouveaux n-uplets de supprimer des n-uplets de la table à partir de la vue de mettre à jour la table par l’intermédiaire de la vue
7
Vues : contrôle d’intégritéCHECK OPTION :
cette clause permet d’interdire d’insérer ou de modifier àtravers la vue des n-uplets qui ne satisferaient pas à ladéfinition de la vue.
8
Check Option: exemple> CREATE OR REPLACE VIEW empInge AS
SELECT * from emp
WHERE job= ‘Ingenieur’
WITH CHECK OPTION
CONSTRAINT Check_inge;
> UPDATE empInge
SET job=’manager’
WHERE idEmp=’1123’ ;
> ORA-01402: view WITH CHECK OPTION
clause violation
9
Vue matérialisée
Vue physique d'une table (SNAPSHOT)
Duplication des données
=> nécessité de synchroniser les données
=> à n’utiliser que pour des requêtes particulièrement lourdes
La fréquence des mises à jour de la vue matérialisée est à préciser
10
Vue matérialisée: syntaxe
CREATE MATERIALIZED VIEW <nomVue>
REFRESH <précisions mise à jour>
AS
<requête>;
Les données de la table sont copiées dans la vue matérialisée à sa création
11
Vue matérialisée: mode de rafraichissement
Modes de refresh:
sur commit : clause ON COMMIT
à la demande (asynchrone, mode par defaut): clause ON DEMAND (utilise des procédures du package standard DBMS_MVIEW).
execute DBMS_REFRESH.MAKE('group_vue’, ‘vueMat1’);
execute DBMS_REFRESH.REFRESH('group_vue');
périodique : clauses START WITH et NEXT, qui précise une date de début et une période de temps intermédiaire
CREATE MATERIALIZED VIEW vueMat1
REFRESH START WITH SYSDATE NEXT SYSDATE + 1
AS
SELECT * FROM emp
WHERE job = ‘ANALYST’;
12
08/12/2009
3
13
Embedded SQL Les manques de SQL:
structures de programme structures de contrôle structures de données facilités pour les entrées/sortie problème pour développer des applications
Approche « Embedded SQL » Insertion de SQL dans un langage de programmation technique de pré-compilation normalisation (Pascal, C, …)
Pro*C/C++ pour Oracle
Inconvénients deux systèmes de types correspondance ensembliste/unaire deux styles de programmation
14
// Exemple de programme JAVA qui utilise le pilote JDBC OCI8 d'Oracle
// pour insérer une ligne dans la table Client
// Il faut importer le paquetage java.sql pour utiliser JDBC
import java.sql.*;
class ClientInsertJDBC
{
public static void main (String args [])
throws SQLException, ClassNotFoundException, java.io.IOException
{
// Charger le pilote JDBC d'Oracle
Class.forName ("oracle.jdbc.driver.OracleDriver");
// Connexion à une BD
Connection uneConnection =
DriverManager.getConnection ("jdbc:oracle:oci8:@", "Scott", "Tiger");
// Création d'un énoncé associé à la Connection
Statement unEnoncéSQL = uneConnection.createStatement ();
// Insertion d'une ligne dans la table Client
int n = unEnoncéSQL.executeUpdate
("INSERT INTO CLIENT " +
"VALUES (100, 'Smith', '911')");
System.out.println ("Nombre de lignes inserees:" + n);
// Fermeture de l'énoncé et de la connexion
unEnoncéSQL.close();
uneConnection.close();
}
}
Exemple: JDBC
15
// Définition de la classe IteratorClient avec liaison par nom
#sql iterator IteratorClient(int noClient, String nomClient);
// Création d'un objet itérateur
IteratorClient unIteratorClient;
// Liaison de l'énoncé SELECT de l'itérateur
#sql unIteratorClient =
{ SELECT idClient, nomClient
FROM Client WHERE idClient > 40};
// Accès au résultat du SELECT par itération sur les lignes
while (unIteratorClient.next()){
System.out.println("Numéro du client : " + unIteratorClient.idClient());
System.out.println("Nom du client : " + unIteratorClient.nomClient());
}
// Fermer l'itérateur
unIteratorClient.close();
Exemple: SQLJ (préprocesseur SQL)
16
var ensemblePays = new Pays[]{
new Pays("France", 60000000, 550000),
new Pays("Turquie", 70000000, 750000),
new Pays("Chine", 1300000000, 9600000),
new Pays("USA", 300000000, 9000000),
new Pays("Maroc", 31000000, 450000),
new Pays("Suisse", 7500000, 40000) };
var grosPays = from p in ensemblePays
where p.Superficie > 1000000
select p;
…
foreach (var p in grosPays)
{
Console.WriteLine(p);
}
Annexe: SQL intégré dans un langage de programmation=> LINQ (.net)
17
Extensions procédurales SQL
Principe:
ajout à SQL de structures de contrôle
=> SQL + outils procéduraux
=> notion de « procédures stockées »
Standardisation: PSM (Persistent Stored Modules)
Exemples:
MySQL: SQL/PSM
Oracle: PL/SQL
PostgreSQL: PL/PSM et PLpgSQL
18
08/12/2009
4
19
• Extension de SQL
• Langage de programmation procédural (PSM)
• Clauses SQL intégrées dans le code procédural
• PL/SQL est donc un langage de programmation propre à Oracle (PL/SQLg existe
aussi pour postgreSQL) :
• intégre directement les clauses SQL d’interrogation, de manipulation (généralement pas de
définition des données)
• permet l’encapsulation des données dans du code
• gestion des exceptions
PL/SQL
20
Application SGBD
SQL
SQL
SQL
SQL
Environnement PL/SQL
ApplicationOracle
PL/SQL
SQL
IF . . . THEN
SQL
ELSE
SQL
END IF ;
SQL
Bloc PL/SQL
21
Bénéfices de PL/SQL
• PL/SQL regroupe les requêtes SQL en un seul bloc qui estenvoyé au serveur en un seul appel
=> amélioration des performances (moins de communications sur le réseau)
• Permet de créer des bibliothèques de code réutilisable
=> programmation de fonctions, procédures, packages
22
Caractéristiques de PL/SQL• Gestion des variables
– variables locales
– variables d’environnementextérieures à PL/SQL
• Structures de contrôle– traitements conditionnels
– traitements répétitifs
• Utilisation des curseurs– définition
– utilisation de curseurs explicites
– attributs des curseurs– modification des valeurs d’un curseur
• Gestion des erreurs– anomalies programme utilisateur– erreurs Oracle
– erreurs prédéfinies
23
Environnement PL/SQL• Bloc anonyme :
bloc PL/SQL imbriqué dans une application ou crééinteractivement
• Procédure enregistrée :bloc nommé enregistré dans le serveur Oracle et qui peut êtreinvoqué par son nom
• Package :module PL/SQL qui regroupe un ensemble de procédures
• Trigger :bloc associé à une table et déclenché automatiquement lorsd’une requête
24
08/12/2009
5
DECLARE (optionel)
• variables, constantes, curseurs
BEGIN (obligatoire)
• clauses SQL
• instructions PL/SQL
EXCEPTION (optionel)
• actions à réaliser quand une exception est levée
END; (obligatoire)
1
2
3
Un bloc peut contenir un ou plusieurs autres blocs
Bloc PL/SQL Tout bloc PL/SQL est composé de 3 sections
25
DECLARE
salaire_v NUMBER(5);
BEGIN
SELECT salaire
INTO salaire_v
FROM emp ;
EXCEPTION
WHEN exception_e THEN
. . .
END;
Bloc PL/SQL
Exemple de bloc PL/SQL:
26
BEGIN
dbms_output.put_line(‘Hello World’) ;
END ;
/
SQL> set serveroutput on ;
SQL> @C:\Mes Documents\monfichier.sql
SQL > Hello World
Fichier monfichier.sql
Pour que le bloc
soit exécuté dans SQLPlus
Bloc anonyme
Charge le contenu
de monfichier.sql
Exécution Bloc PL/SQL
Permet l’affichage
sur la console
(dbms_output)
27
Variables dans PL/SQLUtiliser les variables pour :• L’enregistrement temporaire des donnéees comme dans tout langage
de programmation procédural.
• La manipulation de données enregistrées afin de réaliser des calculs etautres manipulations sans accéder à la base de données
• La réutilisabilité: une fois déclarée, une variable peut être utiliséeplusieurs fois dans une application.
• La facilité de maintenance: en utilisant %TYPE et %ROWTYPE(présenté plus tard), on déclare des variables ayant le même type quedes attributs de tables, si la définition de la table change, le type de lavariable change de même.=> réduction des coûts de maintenance
28
Types de variables
• Scalairesvaleurs simples, les principaux types sont ceux de SQL
• Compositesles enregistrements permettent de définir des donnéesstructurées
29
Déclaration de variables• Syntaxe :
<nom> [CONSTANT] <type> [NOT NULL][ := | DEFAULT <expression> ] ;
• Exemple :
DECLAREdateEmprunt_v DATE ;noDept _v NUMBER(2) NOT NULL := 10 ;lieu_v VARCHAR2(13) := ‘Paris’ ;taux_c CONSTANT NUMBER := 20 ;
• Note : constantes et variables NOT NULL doivent êtreimmédiatement affectées
30
08/12/2009
6
Convention• Toute variable doit être déclarée pour pouvoir être utilisée
• Une variable au maximum déclarée par ligne
• Les noms de variable doivent commencer par une lettre et ont30 caractères au maximum
• Portée de bloc pour les variables, masquage de variables dans les sous-blocs
• Nom des tables, attributes, ..., objets de la base ont la priorité sur les noms de variables
• Par convention, utiliser les suffixes :‘_c’ pour les constantes‘_v’ pour les variables‘_g’ pour les variables globales
31
Affectation de valeurs dans une variable
• Affectation d'une valeur à une variable :
<nom_de_variable> := <expression>
• Stockage du résulat d'une requête :
SELECT . . . INTO <nom_de_variable> FROM . . . WHERE . . .
32
Types scalaires PL/SQL • CHAR [(<taille_max>)]
chaînes de caractères de longueur fixe (max 32767)
• VARCHAR2 (<taille_max>) chaînes de caractères de longueur variable (max 32767)
• NUMBER [(<p>, <s>)]
nombres réels, p chiffres en tout, s après la virgule
• PLS_INTEGER
prennent moins de place et sont plus rapides que les valeurs de type number et binary_integer
• DATE
• BOOLEAN
trois valeurs possibles : TRUE, FALSE et NULL
33
Types définis par l’utilisateur• Syntaxe :
TYPE <nom_type> IS RECORD (<nomchamps> <type> [ [NOT NULL]
[ := <expression> ] ], ... ) ;
• Exemple :SQL> DECLARE
TYPE client_t IS RECORD (numero NUMBER(4),nom CHAR(20) ,adresse CHAR(20) ) ;
client1_v client_t ;BEGIN
client1_v.numero := 2516 ;
...END ;/
34
Type tableau• Syntaxe :
TYPE <nom_type> IS VARRAY (<size>) OF <type>
• Exemple :
DECLARETYPE tab_emp IS VARRAY(10) OF VARCHAR2(64);
tab1 tab_emp;BEGIN
tab1 := tab_emp();
tab1.EXTEND(3);
tab1(1) := ‘CLARK’;
tab1(2) := ‘STONE’;
...
DBMS_OUTPUT.PUT_LINE( tab1(1) ) ;END ;/
35
L’attribut %TYPE et %ROWTYPE• %TYPE : permet d’identifier dynamiquement le type d’un variable à partir de :
– la définition d’un attribut de table
– la définition d’une autre variable déclarée précédemment
Remarque: les contraintes NOT NULL de la définition des attributs de tables ne sont pas conservéesavec %TYPE
Exemple :nomEmploye_v employe.nomEmp%TYPE ;
solde_v NUMBER(7, 2) ;
soldeMinimal_v solde_v%TYPE := 2000 ;
• %ROWTYPE: identifie dynamiquement le type (structuré) d’un n-uplet d’une table
Exemple : dateCommande_v Commande.dateCommande%TYPE ;
commande_v Commande%ROWTYPE ;
36
08/12/2009
7
Opérateurs• Arithmétique: +, -, *, /, **
• Concaténation de chaîne: ||
• Parenthèse pour contrôler les priorités des opérations
• Affectation :=
• Comparaison=, <>, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN
• Logique: AND, OR, NOT
37
Exemples• Incrémenter l’index d’une bouclecompte_v := compte_v + 1;
• Affectation de la valeur d’un drapeau booléenegales_v := (n1_v = n2_v);
• Rechercher si une variable a une valeuraffecte_v := (nemp_v IS NOT NULL)
38
Structures de contrôle• Branchements conditionnels
– IF - THEN - END IF
– IF - THEN - ELSE - END IF
– IF - THEN - ELSIF - END IF
– CASE
• Boucles– LOOP - END LOOP
– FOR - END LOOP
– WHILE - END LOOP
– Note : la commande EXIT permet de sortir de tout type de boucle
39
Branchement conditionnelSyntaxe :IF <condition> THEN
commandes ;[ELSIF <condition> THEN
commandes ; ]*
[ELSEcommandes ; ]
END IF ;
Note :vous pouvez utiliser l’expression IS NULL dans les conditions
40
Branchement conditionnelExemple:
IF nomEmploye_v = ‘Tiger’ THEN
salaire_v := salaire_v * 3 ;
ELSIF nomEmploye_v = ‘Scott’ THEN
salaire_v := salaire_v * 2.5 ;
ELSE
salaire_v := salaire_v * 2 ;
END IF;
41
CASESyntaxe:
CASE expression
WHEN expr1 THEN instruction 1;
WHEN expr2 THEN instruction 2;
…
ELSE instructionsN;
END CASE;
42
08/12/2009
8
Boucle LOOP
Syntaxe :LOOP
commandes ;
. . .
EXIT [WHEN <condition>] ;
END LOOP ;
Note :Sans commande EXIT, les boucles LOOP sont infinies
43
Boucle LOOPExemple :
DECLARE
numEmp_v NUMBER (3) := 1;
BEGIN
LOOP
INSERT INTO Emp (numEmp, nomEmp, jobEmp, numDpt)
VALUES (numEmp_v, ‘Analyste’ || to_char(numEmp), ‘Analyste’, 10) ;
numEmp_v := numEmp_v +1 ;
EXIT WHEN numEmp >= 10 ;
END LOOP ;
END ;
44
Boucle FORSyntaxe :
FOR <compteur> IN [REVERSE] <limite_inf> .. <limite_sup>LOOP
commandes ;. . .
END LOOP
Exemple :
DECLARE
numEmp_v NUMBER (3);
BEGIN
FOR numEmp_v IN 1 .. 10
LOOP
INSERT INTO Employe (numEmp, nomEmp, jobEmp, numDpt)
VALUES (numEmp_v, ‘Analyste’ || to_char(numEmp), ‘Analyste’, 10) ;
END LOOP ;
END ;
45
Boucle WHILESyntaxe :
WHILE <condition> LOOPcommandes ;. . .
END LOOP ;
Exemple :
DECLARE
numEmp_v NUMBER (3);
BEGIN
numEmp_v := 1;
WHILE noEmp_v <= 10
LOOP
INSERT INTO Employe (numEmp, nomEmp, jobEmp, numDpt)
VALUES (numEmp_v, ‘Analyste’|| to_char(numEmp), ‘Analyste’, 10) ;
numEmp_v := numEmp_v +1 ;
END LOOP ;
END ;
46
Inclure une requête SELECT dans PL/SQL
DECLARE
numDept_v NUMBER(2) ;
loc_v VARCHAR2(15) ;
BEGIN
SELECT numDept, lieu
INTO numDept_v, loc_v
FROM Dept
WHERE numDept = ‘Rech’ ;
. . .
END ;
ATTENTION :La requête doit retourner un et un seul n-upletSi ce n’est pas le cas, les exceptions NO_DATA_FOUNDou TOO_MANY_ROWS sont levées
47
ExempleDECLARE
sommeSalaires_v Employe.salaire%TYPE ;
numDept_v NUMBER NOT NULL := 10 ;
BEGIN
SELECT SUM(salaire)
INTO sommeSalaires_v
FROM Employe
WHERE numDept = noDept_v ;
...
END ;
48
08/12/2009
9
Contrôler les transactions dans PL/SQL• La première commande INSERT/UPDATE/DELETE/CREATE/DROPd’un bloc
entame une nouvelle transaction
• La fin du bloc ne termine pas la transaction
• Pour terminer explicitement une transaction, utiliser les commandes SQL :– COMMIT
=> valide les modifications faites depuis le début de la transaction en cours, et entameune nouvelle transaction
– ROLLBACK
=> annule toutes les modifications faites depuis le début de la transaction en cours , et entame une nouvelle transaction
• Note :Une transaction doit être un ensemble homogène de manipulations de la base de données
=> il faut réflechir à tous les endroits où il est légitime de mettre un COMMIT
49
Contrôler les transactions dans PL/SQLDECLARE
numDpt_v emp.numDept%TYPE := 10 ;
majorationSalaire_v emp.salaire%TYPE := 2000 ;
BEGIN
COMMIT ;
DELETE FROM Emp
WHERE numDpt = numDpt_v ;
UPDATE Emp
SET salaire = salaire + majorationSalaire_v
WHERE job = ‘Analyste’ ;
COMMIT ;
END ;
50
Requête retournant plus d’une ligne résulat
• Nécessite l’utilisation de curseur
=> les curseurs sont des zones de travail privées, utilisées pour stocker le résultat d’une requête et qu’ilest possible de parcourir
51
Qu’est-ce qu’un curseur• Jusqu’à présent, l’utilisation de l’instruction SELECT est
limitée aux requêtes renvoyant une et une seule valeurToute requête renvoyant un résultat de cardinalitédifférente de 1 aboutit à un échec
• Définition : un curseur est un mécanisme permettantd’utiliser une instruction SELECT renvoyant un nombrequelconque de n-uplets
• Note : à toute requête SQL exécutée par le serveur Oracle est associé un curseur
52
• Un curseur peut être considéré comme une fenêtre sur l’ensemble des
résultats d’une requête
NumVille Date TempératureA512 25-MAR-1997 26
A512 26-MAR-1997 28
A512 27-MAR-1997 29
A512 28-MAR-1997 40
A512 30-MAR-1997 27
A512 01-APR-1997 25
• On distingue deux types de curseurs :
– curseurs implicites : déclarés implicitement par PL/SQL lors de tout SELECT
– curseurs explicites : créés par le programmeur
Curseur
Qu’est-ce qu’un curseur
53
Utilisation du curseur• Les curseurs doivent être utilisés lorsque l’on désire
parcourir la totalité du résultat d’une requête SELECT
• En particulier si cette requête renvoie un résultat de cardinalité > 1
• Le curseur pointe toujours sur une valeur du résultat de la requête à laquelle il est associé
• Un curseur permet :– de garder trace d’une requête SELECT
– de parcourir n-uplet par n-uplet le résultat d’un SELECT
– d’accéder à la totalité du résultat d’un SELECT
54
08/12/2009
10
• Protocole d’utilisation :
– Déclaration
– Ouverture
– Utilisation
– Fermeture
• Attributs associés aux curseurs
• Fonctions associées aux curseurs
• Parcours de curseurs
Comment utiliser un curseur
55
Déclaration d’un curseur• Syntaxe :CURSOR <nom de curseur> IS
<instruction SELECT> ;
• Notes :– ne pas utiliser de clause INTO dans l’instruction SELECT
– si vous voulez manipuler les n-uplets dans un ordre spécifique, utilisez la clause ORDER BY dans la requête
– on peut déclarer autant de curseurs que l’on souhaite
– le contenu du curseur n’est pas calculé au moment de sa déclaration, maisau moment de son ouverture
56
Attributs associés aux curseurs• Exemple :
BEGIN
DELETE FROM emp
WHERE nomEmp='Tiger' ;
IF (SQL%NOTFOUND)
THEN
dbms_output.put_line('Pas d’employé de ce nom');
END IF;
END;
/
• Attributs des curseursen utilisant les attributs de curseurs, vous pouvez tester le résultat de vos requêtes SQL
– SQL%ROWCOUNT
nombre de n-uplets affectés par la dernière requête SQL (entier)
– SQL%FOUND
booléen, vaut TRUE si la dernière requête SQL a affecté au moins un tuple et faux sinon
– SQL%NOTFOUND
booléen, vaut TRUE si la dernière requête SQL n’a affecté aucun tuple et faux sinon
– SQL%ISOPEN
booléen indiquant si le curseur est ouvert ou fermé (par défaut, les curseurs implicites sont toujoursfermés à la fin de la requête)
• Note : à la place de ‘SQL’, utilisez le nom de votre curseur pour identifier l’état d’un curseur explicite
57
Exemplesupprimer des n-uplets de la table Employe, imprimer le nombre
de n-uplets supprimés
DECLARE
nbreNupletsSupp_v VARCHAR2(64);
numDept_v Employe.noDept%TYPE := 10 ;
BEGIN
DELETE FROM emp
WHERE numDept = numDept_v ;
nbreNupletsSupp_v := TO_CHAR(SQL%ROWCOUNT) || ‘ n-uplets supprimés’ ;
DBMS_OUTPUT.PUT_LINE(nbreNupletsSupp_v) ;
END ;58
Fonctions associées aux curseursExemple :
DECLARE
CURSOR departementVentes_curs IS
SELECT * FROM dept WHERE nomDept = ‘Ventes’;
unDepartement_row departementVentes_curs %ROWTYPE;
BEGIN
OPEN departementVentes_curs;
LOOP
FETCH departementVentes_curs INTO unDepartement_row;
DBMS_OUTPUT.PUT_LINE(unDepartement_row.numDept);
…
END LOOP
CLOSE departementVentes_curs;
END;
• Utiliser les fonctions associées aux curseurs pour accéder au contenu du résultat– OPEN <nom de curseur>
ouverturedu curseur, exécution la requête associéepositionnement du pointeur juste avant le premier n-uplet du résultatsi le résultat de la requête est vide, aucune erreur n’est levée
– FETCH ...déplacement du pointeur vers le prochain n-uplet du curseuraffectation du nouveau n-uplet pointé à une variable
– CLOSE <nom de curseur>fermeturedu curseurdésallocation de la mémoire associéeperte des données associées au curseur(=> fermer systématiquement les curseurs après utilisation)
59
Fonctions associées aux curseurs - FETCH
• Syntaxe :FETCH <nom de curseur>
INTO [[<varible1>, <varible2>, . . .] | <nom de record> ] ;
• Notes : – seulement pour les curseurs explicites– inclure le même nombre de variables dans la clause INTO qu’il y a d’attributs dans
le SELECT associé au curseur– mettre les variables dans le bon ordre– Il est aussi possible d’utiliser un record (type correspondant à un n-uplet) ayant le
type adapté– utiliser le test %FOUND ou %NOTFOUND pour voir si la fonction FETCH a
permit d’atteindre un nouveau n-uplet ou si l’on est à la fin du curseur– avant le premier appel à la fonction FETCH, l’attribut %NOTFOUND du curseur
vaut NULL => a prendre en compte pour éviter des boucles infinies
60
08/12/2009
11
Parcours d’un curseur• Syntaxe :FOR <nom de record> IN <nom de curseur>
LOOP
<instructions>
. . .
END LOOP ;
• Notes :– Il n’est pas nécessaire de déclarer le record, il est implicitement déclaré
– ne pas ouvrir ni fermer le curseur, les fonctions OPEN, FETCH et CLOSE sontappelés implicitement (au premier passage dans la boucle, à chaque passage, à la sortie de la boucle)
– il est possible de mettre directement la requête à la place du nom de curseur, dans ce cas, il n’est plus utile de déclarer de curseur
61
Parcours d’un curseur - ExempleDECLARE
CURSOR departementVentes_curs IS
SELECT *
FROM Departement
WHERE nomDept = ‘VENTE’ ;
compteur_v number := 0 ;
BEGIN
FOR chaqueDepartement_row IN departementVentes_curs
LOOP
...
DBMS_OUTPUT.PUT_LINE(chaqueDepartement_row.numDept);
compteur_v := compteur_v +1 ;
...
END LOOP ;
...
END ;
62
Parcours d’un curseur - ExempleDECLARE
compteur_v number := 0 ;
BEGIN
FOR chaqueDepartement_v IN ( SELECT *
FROM Departement
WHERE nomDept = ‘VENTES’
)
LOOP
...
compteur_v := compteur_v +1 ;
...
END LOOP ;
...
END ;
63
Curseurs paramétrésSyntaxe :
– DéclarationCURSOR <nom de curseur>
( [<nom de paramètre> [IN] <type>
[:= <valeur>] ]* )IS
<instruction SELECT utilisant les paramètres> ;
– OuvertureOPEN <nom de curseur> (<valeur1>, <valeur2>, . . . )
– BoucleFOR <nom de record> IN <nom de curseur> (<valeur1>, <valeur2>, . . . ) LOOP
<instructions>. . . END LOOP ;
– FermetureCLOSE <nom de curseur>
64
Curseurs paramétrés - ExempleDECLARE
CURSOR departement_curs (nomDept_p varchar2) IS
SELECT *
FROM Departement
WHERE nomDept = nomDept_p ;
compteur_v number := 0 ;
BEGIN
FOR chaqueDepartement_row IN
departement_curs (‘VENTE’)
LOOP
...
compteur_v := compteur_v +1 ;
...
END LOOP ;
...
END ;
65
SELECT … FOR UPDATE• SELECT classique : non verrouillé
• Problème: modification des n-uplets possible entre lemoment de l’interrogation (SELECT) et celui desopérations de modification (UPDATE)
• Solution : SELECT … FOR UPDATE permet de verrouiller tous les n-uplets jusqu’auprochain commit (et donc jusqu’à leur modification) permet de garantir qu’aucun autre utilisateurmodifiera les données entre le SELECT et l’UPDATE/DELETE/INSERT correspondant
66
08/12/2009
12
Modification à partir d’un curseurExemple :
DECLARE
CURSOR departements_curs (nomDept_p varchar2) IS
SELECT *
FROMDepartement
WHERE nomDept = nomDept_p
FOR UPDATE departement;
BEGIN
FOR chaqueDepartement_v IN departements_curs (‘VENTE’)
LOOP
UPDATE departement SET nomDept = ‘SALES’
WHERE CURRENT OF departements_curs ;
END LOOP ;
END ;
• Notes :– ne pas oublier le ‘FOR UPDATE’ pour verrouiller les données lues– utiliser la clause ‘UPDATE . . . WHERE’ pour effectuer la modification
67
PROCEDURE <nom>
IS
. . .
BEGIN
. . .
[EXCEPTION]
. . .
END;
Procédure
[DECLARE]
BEGIN
. . .
[EXCEPTION]
. . .
END;
FUNCTION <nom>
RETURN <type>
IS
. . .
BEGIN
. . .
RETURN <valeur> ;
[EXCEPTION]
. . .
END;
Anonyme Fonction
Commentaires :
-- commentaires sur une ligne
/* commentaires sur plusieurs lignes */
Les différents types de blocs
68
APPEL D’UNE PROCEDURE OU D’UNE FONCTION :
EXECUTE Nom_Procédure(...) ;
SELECT ..., Nom_Fonction(...), ... FROM ...;
Variable := Nom_Fonction(...) ;
Types paramètresCreate or replace NomProcedureOufunction[(paramètre1 IN | OUT | IN OUT type
[,paramètre2 IN | OUT | IN OUT type]*, ...
)]
IN (valeur par défaut) indique que le paramètre transmis par le programme appelant n'est pas modifiable par la fonction
OUT indique que le paramètre est modifiable par la procédure
IN OUT indique que le paramètre est transmis par le programme appelantet renseigné par la fonction
69
Procédure stockéeSQL> CREATE PROCEDURE modifierQuantitéObjet
2 ( un_produit Produit.idProduit%TYPE,
3 nouvelleQuantite Produit.quantite%TYPE ) IS
4 BEGIN
5 UPDATE Produit
6 SET quantité = nouvelleQuantite
7 WHERE idProduit = un_produit;
8 END;
9 /
Procedure created.
SQL> EXECUTE modifierQuantitéObjet(10,20);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM produit WHERE idProduit = 20;
IDPRODUIT DESCRIPTION PRIXUNITAIRE QUANTITÉ
---------- -------------------- ------------ ---------------
20 vis35 2.25 20
70
Procédures et fonctions
Suppression de procédures ou de fonctions
– DROP PROCEDURE nom_procedure
– DROP FUNCTION nom_fonction
71
Déboguage du code PL/SQL• Par défaut, seul le résultat de la compilation de la procédure/fonction est affiché
(échec ou réussite)• SHOW ERRORS sous SQL*pluspermet de demander l’affichage des erreurs de
compilation• Package DBMS_OUTPUT• Table USER_PROCEDURES, USER_SOURCE dans le dictionnaire
SQL> SELECT text
2 FROM USER_SOURCE
3 WHERE name = 'FQUANTITÉENSTOCK' AND type = 'FUNCTION'
4 ORDER BY line;
TEXT
--------------------------------------------------------------------------------
FUNCTION fQuantitéEnStock
(unNoArticle Article.noArticle%TYPE)
RETURN Article. quantitéEnStock%TYPE IS
uneQuantitéEnStock Article.quantitéEnStock%TYPE;
BEGIN
SELECT quantitéEnStock
INTO uneQuantitéEnStock
FROM Article
WHERE noArticle = unNoArticle;
RETURN uneQuantitéEnStock;
TEXT
--------------------------------------------------------------------------------
END fQuantitéEnStock;
13 rows selected 72
08/12/2009
13
• Déclarer:
nomException EXCEPTION;
• Soulever:
RAISE nomException;
• Capturer (dans le bloc exception):
WHEN nomException THEN ...
Traitement d'exceptions (EXCEPTION, RAISE)
73
Les exceptions et erreurs• STORAGE_ERROR
Dépassement de capacité mémoire
• ZERO_DIVIDE
Division par zéro
• NO_DATA_FOUND
SELECT INTO ne retourne aucune ligne
• TOO_MANY_ROWS
SELECT INTO ne retourne plusieurs lignes
• Fonction RAISE_APPLICATION_ERROR
– Permet de retourner un message d’erreur
– Numéro d’erreur entre -20000 et -20999
74
Déclencheur (trigger)• Automatiser des actions lors de certains événements du
type :
(AFTER | BEFORE)
(INSERT | DELETE | UPDATE)
• Syntaxe :
CREATE OR REPLACE TRIGGER nom_trigger
Evénement [OF liste colonne] ON nom_table
WHEN (condition) [FOR EACH ROW]
Instructions PL/SQL
75
Accès aux valeurs modifiées• Utilisation des variables new et old
• Si nous ajoutons/modifions un client nous pouvonsaccéder/modifier le nom de ce client à partir de la variable :new.nom
• Dans le cas de suppression ou modification, les anciennes valeurs sont dans la variable :old.nom
76
77
Package PL/SQL Un package est constitué d’une signature/spécification (partie publique) et d’un corps
(implémentation du package)
La signature du package contient des éléments (prototypes de fonctions, variables) accessiblesà tous les utilisateurs du paquetage.
Le corps du package contient l’implémentation des fonctions et la partie privée du package (fonctions et variables locales).
Droit d’exécution d'un package: accès à la spécification mais pas au corps (nécessite droit de modification).
La surcharge de fonction est possible (plusieurs procédures ou fonctions de même nom, avec des signatures différentes).
Chaque session qui utilise le package possède une instance du package (il n’y a donc pas partage des variables)
78
08/12/2009
14
Signature du package
Lasignatured’unpackagecontient:
Prototypes des procédures et des fonctions du package
Constantes et variables
Curseurs
Définitions d'exceptions
79
Corps du package
Lecorpsd’unpackagecontient:
Le corps des procédures et fonctions dont le prototype a été défini dans la signature
D'autre procédure, fonctions, variables, ... privés
Bloc d'initialisation possible
80
CREATE PACKAGE nomPaquetage AS
listeDesSignaturesDesFonctions&Procédures
END nomPaquetage ;
CREATE PACKAGE BODY nomPaquetage AS
délaration [déclaration]…
BEGIN
séquenceÉnoncésInitialisation
END nomPaquetage;
nomPaquetage.nomObjet
Package PL/SQL
81
Exemple: Signature Package PL/SQLcreate or replace package Func_Geom is
PI number := 3.1416;
function Perimetre(rayon in number)
return number;
function Surface(rayon in number)
return number;
...
end; /* package Func_Geom *//
82
Exemple: Corps Package PL/SQLcreate or replace package body Func_Geom is/* Implementation du package */
val_approx number := 4; /* Private variable */
function Perimetre(rayon in number) return number isbeginreturn 2*PI*rayon;
end;
function Surface(rayon in number) return number isbeginreturn PI*rayon*rayon;
end;end Func_Geom;/
83
Utilisation de fonctions du package
Dans une fonction:
...
BEGIN
val = Func_Geom.surface(2);
...
END;
Dans une requête SQL:SELECT Func_Geom.surface(rayon)
FROM Information_Place;
84
08/12/2009
15
DBMS_OUTPUTEcriture de texte dans un tampon.Utilisé pour le debugage, suivi du déroulement d'un programme.
Exemples de fonctions:
Ecriture sur le tampon
DBMS_OUTPUT.PUT(val [NUMBER|VARCHAR2|DATE]);DBMS_OUTPUT.NEW_LINE() DBMS_OUTPUT.PUT_LINE(val[NUMBER|VARCHAR2|DATE]);
Lecture sur le tampon
DBMS_OUPUT.GET_LINE(line varchar2, status out integer)
85
UTL_FILELecture et écriture dans des fichiers coté serveur (TEXT_IO coté client)
Les fichiers ne peuvent être créés que dans les répertoires déclarés dans labase. Pour ajouter un répertoire, le droit create directory estnécessaire)
Exemples de fonctions:
Manipulation fichierFOPEN(location varchar2, filename varchar2, open_mode varchar2 /*rwa*/) return UTL_FILE.FILE_TYPE /* desc fich */;
IS_OPEN (file FILE_TYPE) FCLOSE(file FILE_TYPE)
EcritureUTL_FILE.PUT(file FILE_TYPE, buffer VARCHAR2);
LectureUTL_FILE.GET_LINE(file FILE_TYPE, buffer out VARCHAR2)
86
DBMS_LOBDBMS_LOB permet la manipulation de grands objets, en particulierleur lecture et écriture
BLOB: Binary LOBBFILE: Binary file (stocké en dehors de la base, read-only) CLOB: Character LOBNCLOB: CLOB Multibyte character
Exemples de fonctions:
APPEND (dest BLOB, src BLOB) COPYCOMPAREINSTR...READWRITE
87
Autres package
Plus de 150 packages:
DBMS_FLASHBACK: restauration de la base vers uneversion précédente
DBMS_SCHEDULER: ordonnancement de tache (tachepériodique)
DBMS_SQL: SQL généré dynamiquement
DBMS_STATS: statistiques sur la base
UTL_HTTP, UTL_MAIL (définition mail), UTL_SMTP (envoie de mail)
88
SQL dynamiqueLe SQL dynamique permet de construire dans un programme une requêteSQL avant de l’exécuter.
CREATE OR REPLACE PROCEDURE del(tableName_p varchar2, condition_p varchar2)
ISwhere_clause varchar2(256);BEGINIF condition_p IS NOT NULL THENwhere_clause= ' where ' || condition_p;
END IF;
execute immediate 'delete from' || table_name_p || where_clause;
END/
exec del(emp);exec del(dpt, 'idDpt >= 20');
89
SQL dynamique/* Tableaux *//* Using pour passer les valeurs d'une chainedynamique*/
DECLARETYPE NumList IS TABLE OF NUMBER;empids NumList;
BEGINempids := NumList(101,102,103,104,105);FORALL i IN 1..5
EXECUTE IMMEDIATE'UPDATE employees SET salary = salary * 1.04 WHERE employee_id = :1'USING empids(i);
...END;/
90
