admbd .pdf



Nom original: admbd.pdfTitre: Microsoft PowerPoint - admbd2011.pptxAuteur: Ace

Ce document au format PDF 1.5 a été généré par PScript5.dll Version 5.2.2 / Acrobat Distiller 10.0.0 (Windows), et a été envoyé sur fichier-pdf.fr le 15/03/2012 à 23:33, depuis l'adresse IP 197.28.x.x. La présente page de téléchargement du fichier a été vue 1581 fois.
Taille du document: 855 Ko (147 pages).
Confidentialité: fichier public


Aperçu du document


Administration des bases de
données
Mehdi Louizi

1

Administration des bases de données

Pourquoi PL/SQL

2

Administration des bases de données

Pourquoi PL/SQL ?

PL/SQL = PROCEDURAL LANGUAGE/SQL
SQL est un langage non procédural
Les traitements complexes sont parfois difficiles à écrire
si on ne peut utiliser des variables et les structures de
programmation comme les boucles et les alternatives
On ressent vite le besoin d’un langage procédural pour
lier plusieurs requêtes SQL avec des variables et dans les
structures de programmation habituelles







3

Administration des bases de données

Principales caractéristiques de PL/SQL
Extension de SQL : des requêtes SQL cohabitent avec les
structures de contrôle habituelles de la programmation
structurée (blocs, alternatives, boucles)
La syntaxe ressemble au langage Ada ou Pascal
Un programme est constitué de procédures et de
fonctions
Des variables permettent l’échange d’information entre
les requêtes SQL et le reste du programme







4

Administration des bases de données

Utilisation de PL/SQL
PL/SQL peut être utilisé pour l’écriture des procédures
stockées et des triggers





Oracle accepte aussi le langage Java

Il convient aussi pour écrire des fonctions utilisateurs qui
peuvent être utilisées dans les requêtes SQL (en plus des
fonctions prédéfinies)
Il est aussi utilisé dans des outils Oracle







5

Ex : Forms et Report

Administration des bases de données

Normalisation du language
Langage spécifique à Oracle
Tous les SQBG ont un langage procédural








TransacSQL pour SQLServer,
PL/pgSQL pour Postgresql
Procédures stockées pour MySQL depuis 5.0

Tous les langages L4G des différents SGBDs se
ressemblent



6

Administration des bases de données

Utilisation de PL/SQL
Le PL/SQL peut être utilisé sous 3 formes







7

Un bloc de code, exécuté comme une unique commande SQL,
via un interpréteur standard (SQL+ ou iSQL*PLus)
un fichier de commande PL/SQL
un programme stocké(procédure, fonction, package ou trigger)

Administration des bases de données

Le langage PL/SQL

8

Administration des bases de données

Blocs

Un programme est structuré en blocs d’instructions de 3
types :







Procédures anonymes
Procédures nommées
Fonctions nommées

Un bloc peut contenir d’autres blocs



9

Administration des bases de données

Structure d’un programme PL/SQL
DECLARE
-- définition des variables
BEGIN
-- code du programme
EXCEPTION
-- code de gestion des erreurs
END;

10

Administration des bases de données

Déclaration, initialisation des variables



Identificateurs Oracle :








30 caractères au plus
commence par une lettre
Peut contenir lettres, chiffres, _, $ et #
pas sensible à la casse

Portée habituelle des langages à blocs
Doivent être déclarés avant d’être utilisés

11

Administration des bases de données

Déclaration, initialisation des variables






Déclaration et initialisation
Nom_variable
type_variable := valeur;
Initialisation
Nom_variable
:= valeur;
Déclaration multiple interdite
Exemples:





12

age integer;
nom varchar(30);
dateNaissance date;
ok boolean:= true;
Administration des bases de données

Initialisation de variables




Plusieurs façons de donner une valeur à une variable :
Opérateur d’affectation




n :=

Directive INTO de la requête SELECT


Exemples :





13

dateNaissance:= ’10/10/2004’;
SELECT nome INTO nom
FROM emp
WHERE matr= 509;
Administration des bases de données

SELECT … INTO …





select expr1, expr2,…into var1, var2,…met des valeurs
de la BD dans une ou plusieurs variables expr1, expr2, …
Le select ne doit renvoyer qu’une seule ligne
Avec Oracle il n’est pas possible d’inclure un select sans
«into» dans une procédure : pour ramener plusieurs
lignes  les curseurs.

14

Administration des bases de données

Le type de variables




VARCHAR2


Longueur maximale : 32767 octets



Syntaxe: Nom_variable VARCHAR2(30);


Exemple:



name VARCHAR2(30); name VARCHAR2(30) := ’farid’;

NUMBER(long,dec)


Long : longueur maximale



Dec : longueur de la partie décimale

15



Exemple:



num_tel number(10); farid number(5,2)=142.12;

Administration des bases de données

Le type de variables (2)


DATE



Nom_variable
DATE;
Par défaut DD-MON-YY (18-DEC-02)
Fonction TO_DATE


Exemple :

start_date := to_date(’29-SEP-2003’,’DD-MON-YYYY’);
start_date := to_date(’29-SEP-2003:13:01’,’DD-MONYYYY:HH24:MI’);



BOOLEAN


16

TRUE, FALSE ou NULL

Administration des bases de données

Déclaration %TYPE et %ROWTYPE


On peut déclarer qu’une variable est du même type
qu’une colonne d’une table ou (ou qu’une autre variable) :


Exemple :




nom emp.nome.%TYPE;

Une variable peut contenir toutes les colonnes d’une
ligne d’une table


Exemple :



17

employe emp%ROWTYPE;
déclare que la variable employe contiendra une ligne de la table emp

Administration des bases de données

Exemple d’utilisation
DECLARE
employe emp%ROWTYPE;
nom emp.nome.%TYPE;
BEGIN
SELECT * INTO employe
FROM emp
WHERE matr= 900;
nom := employe.nome;
employe.dept:= 20;

INSERT into emp VALUES employe;
END
18

Administration des bases de données

Commentaires


--Pour une fin de ligne



/* Pour plusieurs
lignes */

19

Administration des bases de données

PL /SQL : les principales
commandes

20

Administration des bases de données

Test conditionnel


IF-THEN




IF l_date > ’11-APR-03’ THEN
l_salaire := l_salaire * 1.15;
END IF;

IF-THEN-ELSE


21

IF l_date > ’11-APR-03’ THEN
l_salaire := l_salaire * 1.15;
ELSE l_salaire := l_salaire * 1.05;
END IF;
Administration des bases de données

Test conditionnel


IF-THEN-ELSIF




IF l_nom = MOHAMED’ THEN
l_salaire := l_salaire * 1.15;
ELSIF l_nom = ‘AHMED’ THEN
l_salaire := l_salaire * 1.05;
END IF;

CASE


22

CASE sélecteur
WHEN expression1 THEN résultat1
WHEN expression2 THEN résultat2
ELSE résultat3
END;
Administration des bases de données

Test conditionnel
Exemple :
val := CASE os
WHEN ‘WINDOWS’ THEN ‘MICROSOFT’
WHEN ‘LEOPARD’ THEN ‘APPLE’
ELSE ‘Unix’
END;

23

Administration des bases de données

Exercices


Soit la table départements avec les champs suivants :






Soit la séquence « dep » suivante :





id_departement : clé primaire, entier non nul
nom : varchar2(40)
id_region : toujours nul

Commence à 1, pas de 1, pas de valeur minimale, valeur maximale
égale à 100

Créez un bloc PL/SQL pour insérer un nouveau département
dans la table départements
Utilisez la séquence dep pour générer un numéro de
département. Créez un paramètre pour le nom du
département.
24

Administration des bases de données

Exercices


Soit la table commande avec les champs suivants :







id_commande : clé primaire
id_employe : clé étrangère
nom : varchar2(40)
total : montant de la commande : entier, trois chiffres après la virgule

Soit la table employés
id_employe : clé_primaire
 commission : entier 3 chiffres
Un employé a plusieurs commandes, une commande a un et un seul
employé






Créez un paramètre qui reçoit un numéro d’employé
Trouvez la somme totale de toutes les commandes traitées
par cet employé
Mettre à jour le pourcentage de commission de cet employé :
25

Administration des bases de données

Exercices






Si la somme < 100,000 passer la commission à 10
Si la somme est comprise entre 100,000 et 1,000,000
inclus passer la commission à 15
Si la somme excède 1,000,000 passer la commission à 20
Si aucune commande n’existe pour cet employé, mettre la
commission à 0
Valider la modification

26

Administration des bases de données

Les boucles


LOOP


instructions exécutables;



END LOOP;



Obligation d’utiliser la commande EXIT



WHILE condition LOOP




instructions exécutables;

END LOOP;

27

Administration des bases de données

Les boucles



FOR variable IN debut..fin



LOOP




instructions;

END LOOP;

28

Administration des bases de données

Affichage



Activer le retour écran




set serveroutput on size 10000

Affichage



29

dbms_output.put_line(chaîne);
Utilise || pour faire une concaténation

Administration des bases de données

Exemple
set serveroutput on --sous SQLPLUS
DECLARE
i number(2);
BEGIN
FOR i IN 1..5 LOOP
dbms_output.put_line(‘Nombre: ’|| i );
END LOOP;
END;

30

Administration des bases de données

Exemple
DECLARE
nb integer;
BEGIN
delete from emp where matr in (600, 610);
nb := sql%rowcount; --curseur sql
dbms_output.put_line('nb = ' || nb);
END;

31

Administration des bases de données

Exemple
DECLARE
compteur number(3);
i number(3);
BEGIN
select count(*) into compteur from clients;
FOR i IN 1..compteur LOOP
dbms_output.put_line('Nombre : ' || i );
END LOOP;
END;
32

Administration des bases de données

Les curseurs

33

Administration des bases de données

Les curseurs





Toutes les requêtes SQL sont associées à un curseur
Ce curseur représente la zone mémoire utilisée pour
parser(analyser) et exécuter la requête
Le curseur peut être implicite (pas déclaré par
l’utilisateur) ou explicite
Les curseurs explicites servent à retourner plusieurs
lignes avec un select

34

Administration des bases de données

Les curseurs






Tous les curseurs ont des attributs que l’utilisateur peut
utiliser
%ROWCOUNT: nombre de lignes traitées par le
curseur
%FOUND: vrai si au moins une ligne a été traitée par la
requête ou le dernier fetch
%NOTFOUND: vrai si aucune ligne n’a été traitée par
la requête ou le dernier fetch
%ISOPEN: vrai si le curseur est ouvert (utile seulement
pour les curseurs explicites)

35

Administration des bases de données

Les curseurs


Les curseurs implicites
 Les curseurs implicites sont tous nommés SQL


Exemple :

DECLARE
nb_lignes integer;
BEGIN
delete from emp where dept= 10;
nb_lignes:= SQL%ROWCOUNT;


36

Administration des bases de données

Les curseurs


Les curseurs explicites



Pour traiter les select qui renvoient plusieurs lignes
Ils doivent être déclarés
On les utilise dans une boucle FOR



Utilisation implicite des instructions OPEN, FETCH et CLOSE




37

Administration des bases de données

Les curseurs


Les curseurs explicites
DECLARE

nom varchar2(30);
CURSOR c_nom_clients IS
SELECT nom,adresse FROM clients;
BEGIN
FOR le_client IN c_nom_clients
LOOP
dbms_output.put_line('Employé: ' ||
UPPER(le_client.nom) ||' Ville : '|| le_client.adresse);
END LOOP;
END;
38

Administration des bases de données

Les curseurs


Curseurs paramétrés



39

Un curseur paramétré peut servir plusieurs fois avec des
valeurs des paramètres différentes
On doit fermer le curseur entre chaque utilisation de
paramètres différents (sauf si on utilise «for »qui ferme
automatiquement le curseur)

Administration des bases de données

Les curseurs


Curseurs paramétrés

DECLARE
CURSOR c(p_dept integer) is
select dept, nome from emp where dept= p_dept;
BEGIN
FOR employe in c(10)LOOP
dbms_output.put_line(employe.nome);
END LOOP;
FOR employe in c(20) LOOP
dbms_output.put_line(employe.nome);
END LOOP;
END;
40

Administration des bases de données

Les exceptions

41

Administration des bases de données

Les exceptions




Une exception est une erreur qui survient durant une
exécution
2 types d’exception :



42

prédéfinie par Oracle
définie par le programmeur

Administration des bases de données

Saisir les exceptions



Une exception ne provoque pas nécessairement l’arrêt du
programme si elle est saisie par un bloc (dans la partie
«EXCEPTION »)

43

Administration des bases de données

Les exceptions prédéfinies


NO_DATA_FOUND




TOO_MANY_ROWS




Erreur numérique

ZERO_DIVIDE




Quand Select into retourne plusieurs lignes

VALUE_ERROR




Quand Select into ne retourne aucune ligne

Division par zéro

OTHERS


44

Toutes erreurs non interceptées

Administration des bases de données

Traitement des exceptions
BEGIN

EXCEPTION
WHEN NO_DATA_FOUND THEN
...
WHEN TOO_MANY_ROWS THEN
...
WHEN OTHERS THEN--optionnel
...
END;
45

Administration des bases de données

Les exceptions Utilisateur




Elles doivent être déclarées avec le type EXCEPTION
On les lève avec l’instruction RAISE

46

Administration des bases de données

Exemple d’exception utilisateur
DECLARE
salaire numeric(8,2);
salaire_trop_bas EXCEPTION;
BEGIN
select sal into salaire from emp where matr= 50;
if salaire < 300 then
RAISE salaire_trop_bas;
end if;
EXCEPTION
WHEN salaire_trop_bas THEN
dbms_output.put_line(‘Salaire trop bas’);
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
47

Administration des bases de données

Procédures et Fonctions

48

Administration des bases de données

Bloc anonyme ou nommé





Un bloc anonyme PL/SQL est un bloc «DECLARE –
BEGIN –END »comme dans les exemples précédents
Dans SQL*PLUS on peut exécuter directement un bloc
PL/SQL anonyme en tapant sa définition
Le plus souvent, on crée plutôt une procédure ou une
fonction nommée pour réutiliser le code

49

Administration des bases de données

Procédures sans paramètres
create or replace procedure list_nom_clients
IS
BEGIN
DECLARE
nom varchar2(30);
CURSOR c_nom_clients IS select nom,adresse from clients;
BEGIN
FOR le_client IN c_nom_clients LOOP
dbms_output.put_line('Employé: '
|| UPPER(le_client.nom)
||' Ville : ‘
|| le_client.adresse);
END LOOP;
END;
END;
50

Administration des bases de données


admbd.pdf - page 1/147
 
admbd.pdf - page 2/147
admbd.pdf - page 3/147
admbd.pdf - page 4/147
admbd.pdf - page 5/147
admbd.pdf - page 6/147
 




Télécharger le fichier (PDF)


admbd.pdf (PDF, 855 Ko)

Télécharger
Formats alternatifs: ZIP



Documents similaires


programmation pl sql bases du langage
pl sql exercices corriges
database 2014 final fr corrige
psppesymfony
tp3 plsql correction
choix libre du mode de cuisson de la terre max 31856 2

Sur le même sujet..