Cours PL/SQLFondements des bases de données
Programmation en PL/SQL Oracle (1/2)
Équipe pédagogique BD
Version du 12 février 2014
Langage PL/SQL
Commandes
Curseurs
Pourquoi PL/SQL ?
I SQL est un langage non procédural
I 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 I 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
I Extension de SQL : des requêtes SQL cohabitent avec les structures de contrôle habituelles de la programmation structurée (blocs, alternatives, boucles).
I La syntaxe ressemble au langage Ada ou Pascal.
I Un programme est constitué de procédures et de fonctions. I Des variables permettent l’échange d’information entre les requêtes SQL et le reste du programme
I PL/SQL peut être utilisé pour l’écriture des procédures stockées et des triggers.
I (Oracle accepte aussi le langage Java)
I 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).
I Il est aussi utilisé dans des outils Oracle I Ex : Forms et Report.
Le PL/SQL peut être utilisé sous 3 formes :
1. un bloc de code, exécuté comme une unique commande SQL, via un interpréteur standard (SQLplus ou iSQL*PLus)
2. un fichier de commande PL/SQL
3. un programme stocké (procédure, fonction, trigger)
Langage PL/SQL
Commandes
Curseurs
I Un programme est structuré en blocs d’instructions de 3 types :
I procédures ou bloc anonymes, I procédures nommées, I fonctions nommées.
I Un bloc peut contenir d’autres blocs.
I Considérons d’abord les blocs anonymes.
DECLARE
?? d e f i n i t i o n des v a r i a b l e s
BEGIN
?? code du programme
?? code de gestion des
e r r e u r s
Seuls BEGIN et END
sont obligatoires
Les blocs se terminent par un ;
I Identificateurs Oracle :
I 30 caractères au plus,
I commence par une lettre,
I peut contenir lettres, chiffres, _, $ et # I pas sensible à la casse.
I Portée habituelle des langages à blocs
I Doivent être déclarées avant d’être utilisées
I Déclaration et initialisation
I Nom_variable type_variable := valeur;
I Initialisation
I Nom_variable := valeur;
I Déclarations multiples interdites.
I age integer;
I nom varchar(30);
I dateNaissance date;
I ok boolean := true;
I Opérateur d’affectation n:=.
I Directive INTO de la requête SELECT.
I dateNaissance := to_date(’10/10/2004’,’DD/MM/YYYY’);
I SELECT nom INTO v_nom FROM emp
WHERE matr = 509;
Pour éviter les conflits de nommage, préfixer les variables
PL/SQL par v_
SELECT INTO
Instruction SELECT expr1,expr2, INTO var1, var2,
I Met des valeurs de la BD dans une ou plusieurs variables var1, var2,
I Le SELECT ne doit retourner qu’une seule ligne
I Avec Oracle il n’est pas possible d’inclure un SELECT sans INTO dans une procédure.
I Pour retourner plusieurs lignes, voir la suite du cours sur les curseurs.
I Longueur maximale : 32767 octets; I Exemples : name VARCHAR2(30); name VARCHAR2(30) := ’toto’;
I Long : longueur maximale; I Dec : longueur de la partie décimale; I Exemples : num_telnumber(10); toto number(5,2)=142.12;
I Fonction TO_DATE; I Exemples : start_date := to_date(’29-SEP-2003’,’DD-MON-YYYY’); start_date := to_date(’29-SEP-2003:13:01’,’DD-MON-YYYY:HH24:MI’);
I TRUE
I FALSE
I NULL
v_nom .%TYPE;
On peut déclarer qu’une variable est du même type qu’une colonne d’une table ou (ou qu’une autre variable).
v_employe emp%ROWTYPE;
Une variable peut contenir toutes les colonnes d’un tuple d’une table (la variable v_employe contiendra une ligne de la table emp).
Important pour la robustesse du code
?? Declaration v_employe emp%ROWTYPE;
v_nom emp .nom.%TYPE;
FROM emp WHERE matr = 900; v_nom := v_employe .nom; v_employe . dept := 20;
. . .
?? I n s e r t i o n d ’ un tuple dans la baseINSERT into emp VALUES v_employe ;
Vérifier à bien retourner un seul tuple avec la requête SELECT INTO
Langage PL/SQL
Commandes
Curseurs
IF v_date > ’01?JAN?08 ’ THEN v_salaire := v_salaire ? 1.15; END IF ;
IF v_date > ’01?JAN?08 ’ THEN
| v_salaire := v_salaire ? ELSE | 1.15; |
| v_salaire := v_salaire ? END IF ; IF-THEN-ELSIF IF v_nom = ’PARKER ’ THEN | 1.05; |
| v_salaire := v_salaire ? | 1.15; |
ELSIF v_nom = ’SMITH ’ THEN v_salaire := v_salaire ? 1.05;
END IF ;
CASE s e l e c t i o n
WHEN expression1 THEN r e s u l t a t 1
WHEN expression2 THEN r e s u l t a t 2
. . .
ELSE r e s u l t a t END;
val := CASE c i t y
WHEN ’TORONTO’ THEN ’RAPTORS’
CASE renvoie une valeur qui vaut resultat1 ou resultat2 ou ou resultat par défaut.
WHEN ’LOS?ANGELES ’ THEN ’LAKERS ’
WHEN ’SAN?ANTONIO ’ THEN ’SPURS ’
ELSE ’NO?TEAM’
END;
LOOP i n s t r u c t i o n s ;
EXIT[WHEN condition ] ; i n s t r u c t i o n s ;
WHILE condition LOOP i n s t r u c t i o n s ; END LOOP;
LOOP monthly_value := daily_value ? 31; EXIT WHEN monthly_value > 4000;
Obligation d’utiliser la commande EXIT pour éviter une boucle infinie.
FOR v a r i a b l e IN [REVERSE] debut . . f i n
LOOP i n s t r u c t i o n s ; END LOOP;
I La variable de boucle prend successivement les valeurs de debut,debut + 1,debut + 2, , jusqu’à la valeur fin.
I On pourra également utiliser un curseur dans la clause IN (dans quelques slides).
I Le mot clef REVERSE à l’effet escompté.
FOR Lcntr IN REVERSE 1..15 LOOP
LCalc := Lcntr ? 31;
END LOOP;
I Activer le retour écran : set serveroutput on size 10000
I Sortie standard : dbms_output.put_line(chaîne); I Concaténation de chaînes : opérateur ||
dbms_output . put_line ( ’Nombre : ? ’ | | i ) ; END LOOP;
/
Le caractère / seul sur une ligne déclenche l’évaluation.
DECLARE compteur number ( 3 ) ;
i number ( 3 ) ;
FROM EtudiantLIF10 ;
FOR i IN 1 . . compteur LOOP dbms_output . put_line ( ’Nombre? : ?L3IF? ’ | | i ) ; END LOOP;
Langage PL/SQL
Commandes
Curseurs
Toutes les requêtes SQL sont associées à un curseur :
I Ce curseur représente la zone mémoire utilisée pour analyser et exécuter la requête.
I Le curseur peut être implicite (pas déclaré par l’utilisateur) ou explicite.
I Les curseurs explicites permettent de manipuler l’ensemble des résultats d’une requête.
DELETE FROM emp WHERE dept = 10; nb_lignes := SQL%ROWCOUNT;
. . .
END;
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)
I Les curseurs doivent être déclarés explicitement. A la déclaration, on explicite la requête SELECT dont le résultat sera parcouru par le curseur.
I Le code doit les utiliser avec les commandes
I OPEN moncurseur, pour ouvrir le curseur;
I FETCH moncurseur, pour avancer le curseur à la ligne suivante;
I CLOSE moncurseur, pour refermer le curseur
I On utilise souvent les curseurs dans une boucle FOR qui permet une utilisation implicite des instructions OPEN, FETCH et CLOSE.
I Généralement, on sort de la boucle quand l’attribut NOTFOUND est vrai.
DECLARE
CURSOR c _ s a l a i r e s IS
FROM emp ; v_salaire number ( 4 ) ; v_total number ( 6 ) ;
BEGIN v_total := 0;
OPEN c _ s a l a i r e s ;
FETCH c _ s a l a i r e s INTO v_salaire ;
EXIT WHEN c _ s a l a i r e s%NOTFOUND;
IF v_salaire IS NOT NULL THEN v_salaire := v_total + v_salaire ;
dbms_output . put_line ( t o t a l ) ; END IF ;
END LOOP; CLOSE c _ s a l a i r e s ; dbms_output . put_line ( v_total ) ;
END;
SELECT matr , nom, s a l
FROM emp ; employe c%ROWTYPE;
BEGIN OPEN c ; FETCH c INTO employe ; IF employe . s a l IS NOT NULL THEN
. . .
END IF ; END;
I Elle simplifie la programmation car elle évite d’utiliser explicitement les instruction OPEN, FETCH et CLOSE.
I En plus elle déclare implicitement une variable de type ROW associée au curseur.
CURSOR c_nom_clients IS SELECT nom, adresse
FROM c l i e n t s ;
FOR l e _ c l i e n t IN c_nom_clients LOOP dbms_output . put_line (
’ Employe? : ? ’ | | UPPER( l e _ c l i e n t .nom) | |
’? V i l l e ? : ? ’ | | l e _ c l i e n t . adresse ) ; END LOOP;
END;
Préfixer le nom d’un curseur par c_ pour éviter les confusions
I Un curseur paramétré peut servir plusieurs fois avec des valeurs des paramètres différentes.
I On doit fermer le curseur entre chaque utilisation de paramètres différents si on utilise pas la boucle FOR dédiée.
DECLARE CURSOR c ( p_dept integer ) IS
SELECT dept , nom
FROM emp
WHERE dept = p_dept ;
FOR employe in c (10) LOOP
dbms_output . put_line ( employe .nom ) ;
FOR employe in c (20) LOOP
dbms_output . put_line ( employe .nom ) ;
Fin du cinquième cours.
