Support d’introduction au langage PL SQL

Participez au vote ☆☆☆☆☆★★★★★

Fondements 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 ?

PL/SQL = PROCEDURAL LANGUAGE/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

Principales caractéristiques

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

Utilisation de PL/SQL

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.

Utilisation de PL/SQL (suite)

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

Blocs

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.

Structure d’un bloc anonyme

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

EXCEPTION

?? code de gestion des

e r r e u r s

END;

Seuls BEGIN et END

sont obligatoires

Les blocs se terminent par un ;

Déclaration, initialisation des variables

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.

Exemples

I age integer;

I nom varchar(30);

I dateNaissance date;

I ok boolean := true;

Plusieurs façons d’affecter une valeur à une variable

I Opérateur d’affectation n:=.

I Directive INTO de la requête SELECT.

Exemples

I dateNaissance := to_date(’10/10/2004’,’DD/MM/YYYY’);

I SELECT nom INTO v_nom FROM emp

WHERE matr = 509;

Attention

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.

Types de variables

VARCHAR2

I Longueur maximale : 32767 octets; I Exemples : name VARCHAR2(30); name VARCHAR2(30) := ’toto’;

NUMBER(long,dec)

I Long : longueur maximale; I Dec : longueur de la partie décimale; I Exemples : num_telnumber(10); toto number(5,2)=142.12;

DATE

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’);

BOOLEAN

I TRUE

I FALSE

I NULL

Déclaration %TYPE et %ROWTYPE

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

Exemple

DECLARE

?? Declaration v_employe emp%ROWTYPE;

v_nom emp .nom.%TYPE;

BEGIN SELECT ? INTO v_employe

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 ;

END;

Vérifier à bien retourner un seul tuple avec la requête SELECT INTO

Langage PL/SQL

Commandes

Curseurs

Test conditionnel

IF-THEN

IF v_date > ’01?JAN?08 ’ THEN v_salaire := v_salaire ? 1.15; END IF ;

IF-THEN-ELSE

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

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;

Exemple

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;

Les boucles

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 ;

END LOOP;

WHILE condition LOOP i n s t r u c t i o n s ; END LOOP;

Exemple

LOOP monthly_value := daily_value ? 31; EXIT WHEN monthly_value > 4000;

END LOOP;

Obligation d’utiliser la commande EXIT pour éviter une boucle infinie.

FOR

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é.

Exemple

FOR Lcntr IN REVERSE 1..15 LOOP

LCalc := Lcntr ? 31;

END LOOP;

Affichage

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 ||

Exemple

DECLARE i number ( 2 ) ; BEGIN FOR i IN 1 . . 5 LOOP

dbms_output . put_line ( ’Nombre : ? ’ | | i ) ; END LOOP;

END;

Le caractère / seul sur une ligne déclenche l’évaluation.

Affichage

Exemple bis

DECLARE compteur number ( 3 ) ;

i number ( 3 ) ;

BEGIN SELECT COUNT(?) INTO compteur

FROM EtudiantLIF10 ;

FOR i IN 1 . . compteur LOOP dbms_output . put_line ( ’Nombre? : ?L3IF? ’ | | i ) ; END LOOP;

END;

Langage PL/SQL

Commandes

Curseurs

Les 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.

Les curseurs implicites sont tous nommés SQL

DECLARE nb_lignes integer ; BEGIN

DELETE FROM emp WHERE dept = 10; nb_lignes := SQL%ROWCOUNT;

. . .

END;

Attributs des 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)

Pour traiter les SELECT qui renvoient plusieurs lignes

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

Utilisation

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.

Exemple de boucle LOOP pour les curseurs

DECLARE

CURSOR c _ s a l a i r e s IS

SELECT s a l

FROM emp ; v_salaire number ( 4 ) ; v_total number ( 6 ) ;

BEGIN v_total := 0;

OPEN c _ s a l a i r e s ;

LOOP

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;

Déclaration d’un type associé à un curseur

DECLARE CURSOR c IS

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;

Boucle FOR pour un curseur

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.

Exemple

DECLARE

CURSOR c_nom_clients IS SELECT nom, adresse

FROM c l i e n t s ;

BEGIN

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

Curseurs paramétrés

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.

Exemple

DECLARE CURSOR c ( p_dept integer ) IS

SELECT dept , nom

FROM emp

WHERE dept = p_dept ;

BEGIN

FOR employe in c (10) LOOP

dbms_output . put_line ( employe .nom ) ;

END LOOP;

FOR employe in c (20) LOOP

dbms_output . put_line ( employe .nom ) ;

END LOOP; END;

Fin du cinquième cours.