partie1 Le langage PL/SQL introduction

Plan

1. Introduction à PL/SQL

2. Les structures de contrôle

3. Interaction avec Oracle et curseurs explicites

4. Gestion des exceptions

5. Les sous programmes

6. Les sous programmes stockés et les packages

7. Les déclencheurs



• Les structures de contrôle
• Interaction avec Oracle et curseurs explicites
• Gestion des exceptions
• Les sous programmes
• Les sous programmes stockés et les
packages
• Les déclencheurs




• PL/SQL?

Introduction

Extension procédurale à SQL
• Avantages de PL/SQL
– Développement de programmes modulaires
• Déclaration des identifiants : Variables, Constantes,..
• Structures de contrôle
• Fonctions intégrées
• Blocs nommés : sous programmes
• Procédures et fonctions stockées
• Blocs imbriqués
• Package
– Déclencheurs de BD
– Portabilité
– Accroissement des performances

• PL/SQL distingue deux types de blocs : anonymes ou nommés
– Bloc anonyme correspond à une séquence d’instructions qui s’exécute
à l’endroit où elle existe
– Bloc nommé est un sous programme : fonction ou procédure
éventuellement paramétrée et pouvant être appelée autant de fois
que nécessaire

• Exemple
DECLARE – ceci est un bloc anonyme
V_Sal NUMBER (9,3); --déclaration
V_Mess VARCHAR2 (50);
BEGIN
SELECT Sal
INTO V_Sal
FROM Chercheur WHERE Chno =1234;
EXCEPTION
WHEN NO_DATA_FOUND THEN
--gestion de l’exception NO_DATA_FOUND
V_Mess := ‘Chercheur non trouvé’;
--suite
END;

• Types PL/SQL
PL/SQL supporte une variété de type de données utiles pour
déclarer des variables et des constantes – compatibles avec les
types Oracle (colonnes de tables)+type BOOLEAN
Type Scalaire
NUMBER[(précision, scale)]
VARCHAR2
CHAR
DATE
BOOLEAN...

• Déclarations de variables
Identificateur Type [(précision, scale)] [NOT NULL] [:=expression];
Où
Identificateur : règles Oracles habituelles
NOT NULL : initialisation obligatoire
Exemples
DECLARE
….
V_sal NUMBER (7,3);
V_message VARCHAR2(80):= ‘Texte initial’;
Married BOOLEAN:=FALSE;
Next_Week DATE :=DATE+7;

• Suivre les conventions suivantes :
Identifiant
Variable
Exception
Curseur
Paramètre
Préfix Exemple
V_ V_sal
E_ E_RupStock
C_ C_EMPDEPT10
P_ P_EMPNO


• L’attribut %Type
Déclaration de variable en se référant au type d’une variable ou d’une colonne
Identificateur nomTable.colonne%Type;
Avantage
Garantit la compatibilité de type surtout si la variable déclaré sera cité dans INTO pour initialisation à partir d’une colonne
Exemple
DECLARE
…
V_deptno1 dept.deptno%TYPE;
V_loc
dept.loc%TYPE;
V_deptno2 deptno%TYPE;
BEGIN
SELECT deptno, loc
INTO v_deptno1, v_loc
FROM …
WHERE …

• Déclaration de constantes
Identificateur CONSTANT Type [(precision,scale)]:=expression;
Exemple
Pie CONSTANT NUMBER(9,5):=3.14159;
Affectations et expressions
Identificateur := expression;
Exemple
V_compteur :=V_compteur+1;
V_message := TO_CHAR(v_compteur);
V_ename :=‘KING’;

Conversion implicite
Outre les conversions explicites par les fonctions prédéfinies (TO_CHAR, TO_NUMEBR, TO_DATE,…), PL/SQL peut effectuer
des conversions implicites c’est-à-dire automatiquement lors des affectations/évaluations d’expressions
1. Affectation d’un nombre dans une chaîne
V_message VARCHAR2 := 1500;
2. Affectation d’un nombre représenté sous forme de chaîne dans une variable numérique une variable numérique :
V_message VARCHAR2 := 15OO;
V_sal NUMBER(9,3):=v_message;
Attention : Ne pas affecter une valeur textuelle dans une variable numérique

• Blocs Imbriqués et Portée d’une variable
<<bloc1>>
DECLARE
Birthdate
BEGIN
……
DECLARE
Birthdate
BEGIN
…
DATE ;
DATE;
END ;
END bloc1;

Instruction Conditionnelle : IF
• Syntaxe :
IF condition1 THEN
séquence;
[ELSIF condition2 THEN
séquence; ]
[ELSIF condition3 THEN
séquence; ]
[ELSE
séquence; ]
END IF;
ELSE et ELSIF : optionnelles

Structure Case
WHEN expr1 THEN instructions1;
WHEN expr2 THEN instructions2;
…
WHEN exprN THEN instructionsN;
[ELSE instructionsN+1;]
END CASE [étiquette];
[<<étiquette>>]
CASE variable

Exemples
IF choix = 1 Then
UPDATE
ELSE
UPDATE
END IF ;
EMP SET sal = sal + 100;
EMP SET sal = sal * 1.1;

• La boucle de base : LOOP
[<<étiquette>>]
LOOP
Séquence;
Infinie !!!
END LOOP [<<étiquette>>];
On peut quitter une boucle avec la commande EXIT.
EXIT [étiquette] [ WHEN condition] ;
Exemple :
Compteur := 0;
LOOP
Compteur := Compteur + 1;
Insert into LIGNE values (Compteur) ;
…
EXIT WHEN Compteur =10;
…
END LOOP;
Syntaxe :
WHILE condition
LOOP
Séquence;
END LOOP;

La boucle WHILE
LOOP et END LOOP jouent le rôle de FAIRE et FIN FAIRE dans une boucle
algorithmique TANT QUE.
Exemple :
Compteur := 1;
WHILE compteur <=5
LOOP
...
Insert into DEPT (deptno,Loc)
Values (dept_seq.NEXTVAL,’Sfax’) ;
Compteur := Compteur + 1;
…
END LOOP;






Syntaxe :

La boucle FOR
FOR indice IN [REVERSE] b_inf..b_sup
LOOP
Séquence;
END LOOP;
Où
Indice :
variable entière déclarée implicitement par la boucle
locale
Référencé dans la boucle seulement
l’affecter à une variable si nécessaire
S’incrémente automatiquement de 1 à la fin de chaque itération

• Les commandes SQL utilisées dans un bloc PL/SQL sont
dites des curseurs.
• On distingue deux types de curseurs :
– Les curseurs explicites
– Les curseurs implicites

Les curseurs explicites
– C’est une commande SELECT pouvant ramener plusieurs
lignes et qui est totalement à la charge du développeur.

–Un curseur explicite doit être explicitement :

• Déclaré dans la section DECLARE

• Géré par le développeur dans la section exécutable.
• La gestion d’un curseur consiste à exécuter les opérations : ouverture du curseur, lecture et traitement des lignes, fermeture.

Les curseurs implicites
– Un curseur implicite est toute commande SQL (Select, Insert,
Update, Delete) située dans la partie exécutable d’un bloc :
• NON déclaré explicitement

• Nommé ‘SQL’ implicitement par Oracle : tester l’état du curseur

• Gestion automatique par PL/SQL : ouverture, traitement,fermeture.
– Lorsque le curseur implicite est un ordre SELECT, alors il doit ramener une seule ligne, sinon erreur

Exemple d’un curseur implicite
DECLARE
V_deptno NUMBER(2);
V_Loc
.....
BEGIN
VARCHAR2(15);
SELECT deptno, loc
INTO
FROM v_deptno, v_loc  Obligatoire
dept
WHERE upper(dname) = ‘SALES’;
…
END;

Exemple


DECLARE

Les curseurs implicites
V_Rows_Updated
BEGIN
UPDATE EMP
NUMBER;
SET
WHERE
sal = sal*1.1
deptno = 10;
V_Rows_Updated := SQL%ROWCOUNT;
-- SQL%ISOPEN est FALSE
INSERT INTO History_Tab
Values (‘Dept 10’, V_Rows_Updated, SYSDATE);
END ;

• Motivation
– Besoin de consulter n-uplets issus d’une ou de plusieurs tables de la base de données
– Effectuer des traitements en examinant
individuellement
• Curseur explicite
– Est une commande Select déclaré et nommé chaque ligne
– Généralisation du curseur implicite d’interrogation
– Possédant les quatre attributs suivants : %ISOPEN , %FOUND,
%NOTFOU%NOTFOUND et %ROWCOUNT%ROWCOUN .
• Exigences du curseur explicite
– Déclaration
– Ouverture
– Accès aux lignes du curseur
– Fermeture

• Déclaration d’un curseur : Syntaxe
CURSOR nom_curseur [(paramètre1, paramètre2,…)] IS Commande_SELECT
[FOR UPDATE [OF colonne1, colonne2,…] [NOWAIT]];
Où
nom_curseur : est le nom du curseur
Commande_SELECT : est une requête pouvant utiliser les clauses habituelles
(group by, having, order by
Paramètre : est un paramètre formel décrit comme suit :
Nom_paramètre [IN] Type [{:=|DEFAULT} valeur]
FOR UPDATE [OF colonne1, colonne2,…] : place un verrou exclusif portant sur
des n-uplets de la table du SELECT
[OF colonne1, colonne2,…] : les colonnes à mettre à jour par le curseur sont
verrouillées
NOWAIT : pas d’attente pour accéder aux n-uplets

• Déclaration d’un curseur : Exemples
DECLARE
……
CURSOR C1_Cher IS – curseur non paramétré
SELECT Cnom, Labno
FROM chercheur
WHERE Sal> 1200;
**************************************************************
CURSOR C2_Cher (P_Labno IN NUMBER) IS
-- IN est optionnel
SELECT Grade, Cnom
FROM chercheur
WHERE Labno = P_Labno;
**************************************************************
CURSOR C3_CHER (P_Min NUMBER DEFAULT 0, P_Max NUMBER DEFAULT 99) IS
SELECT ….
FROM ….
WHERE …BETWEEN P_Min AND P_Max;

• Ouverture d’un curseur : Syntaxe
OPEN nom_curseur [(paramètre_effectif,…)];
• Exemples
OPEN C1_Cher;
OPEN C2_Cher (10);
V_Labno :=10;
OPEN C2_Cher (V_Labno);

• Accès aux lignes d’un curseur : Syntaxe
FETCH nom_curseur INTO variable1, variable2,…;
• Fonctionnalités
– Ramène le contenu de la ligne courante
– Assigne les données dans les variables de INTO
– Déplace le pointeur vers la ligne suivante

• Accès aux lignes d’un curseur : Exemple
DECLARE
CURSOR C1_Cher IS
SELECT Cnom, Labno
FROM Chercheur
WHERE Sal >1400;
V_Cnom Chercheur.Cnom%TYPE;
V_Labno Chercheur.Labno%TYPE;
BEGIN
OPEN C1_Cher;--Exécution du SELECT
LOOP
FETCH C1_Cher INTO V_Cnom, V_Labno;
EXIT WHEN C1_Cher%NOTFOUND;--Test de sortie

--
--
…

ici %FOUND est TRUE
traitement de la ligne ramenée par FETCH

END LOOP;
…
END; --fin du bloc

• Fermeture d’un curseur
– Libération de l’espace mémoire alloué
– Réouverture si nécessaire
• Syntaxe
CLOSE nom_curseur;
• Remarque
toute opération sur un curseur fermé (Fetch, %attribut) engendra l’exception
prédéfinie INVALID_CURSOR
• Attributs d’un curseur explicite
– %FOUND
Cet attribut prend la valeur TRUE lorsque une ligne est ramenée, sinon il prend la
valeur FALSE
– %NOTFOUND
Cet attribut prend la valeur FALSE lorsque une ligne est ramenée, sinon il prend la
valeur TRUE
– %ISOPEN
Cet attribut prend la valeur TRUE lorsque le curseur indiqué est ouvert, sinon il
prend la valeur FALSE
– %ROWCOUNT
Cet attribut retourne le nombre de lignes impactées par la dernière instruction

• Curseur et enregistrement : Syntaxe
Nom_enregistrement nom_curseur%ROWTYPE;
• Exemple
DECLARE
CURSOR C1 IS
SELECT Cnom, Sal+NVL(prime,0) salaire
FROM chercheur
WHERE Labno=10;
Rec1 C1%ROWTYPE;-- Rec1 de même schéma que C1
V_Cnom Chercheur.Cnom%TYPE;
…
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO Rec1;
EXIT WHEN C1%NOTFOUND OR C1%ROWCOUNT >5;
V_Cnom:=Rec1.Cnom;
V_Salaire := Rec1.salaire;
END LOOP;
Close C1;
END;__ fin bloc

• Boucle dédiée curseur
PL/SQL offre une boucle FOR spéciale pour les curseurs explicites. Elle prend
en charge toutes les opérations du curseur (OPEN, FETCH, EXIT et
CLOSE)
• Syntaxe 1 : cas d’un curseur nommé
FOR nom_record IN nom_curseur [(paramètre,…)]
LOOP
…
--traitement de la ligne courante
END LOOP;
• Syntaxe 2 : cas d’un curseur anonyme
FOR nom_record IN (Commande_SELECT)
LOOP
…
--traitement de la ligne courante
END LOOP;

• Boucle dédiée curseur : Exemple 1
DECLARE
CURSOR C1 (P_DATEREC DATE) IS
SELECT Cnom, Grade FROM Chercheur
WHERE DATEREC < P_DATEREC;
BEGIN
FOR REC IN C1 (’01-JAN-99’) -- curseur ouvert
LOOP -- ici une ligne est disponible
-- traitement des lignes, une par itération
END LOOP;
/* Ici le curseur est automatiquement fermé
les attributs ne sont pas utilisables
Toute référence à REC est invalide*/
-- suite traitement
END;

• Boucle dédiée curseur : Exemple 2
DECLARE
……
BEGIN
…
V_TOT_SAL NUMBER :=0;
FOR REC IN (SELECT Cnom, Sal
FROM Chercheur
WHERE Labno=10)
LOOP
-- traitement de la ligne courante
IF Rec.Sal > 1000 THEN …
END LOOP;
….
END;