Programmation PL/SQL

Introduction

Procedural Language

extensions to SQL Version 8.0 Disponible dans deux environnements:◦Les outils (Forms, Reports,

Graphics)◦Au serveur de BD

PL/SQL ?

Inclut les énoncés SQL.

Langage avec une structure de bloc.

Déclare des variables et des constantes.

Affecte des valeurs aux variables.

Opérateurs logiques.

Fonctions built-in.

Vue d’ensemble

if - then if – then – else if – then - elsif loop - end loop for – end loop while – end loop exit goto null

Vue d’ensembleStructures de contrôle

On peut définir des zones de travail contenant plusieurs enregistrements (les curseurs)

DECLARE CURSOR OPEN FETCH CLOSE

Vue d’ensembleLes curseurs

Une erreur ou un avertissement se nomme une exception

Dans nos programmes nous pouvons mettre une :

Section EXCEPTION

EXCEPTION WHEN … THEN

Vue d’ensembleTraitement d’erreurs

Sous-programme Procédure externe Package Abstraction de données

◦ Collections◦ Structures◦ Types

Déclarations et manipulation d’objets

Vue d’ensemble

Programmation PL/SQL

Notions fondamentales

3 sections◦ Section déclarative (les variables)◦ Section exécutable (le code)◦ Section des exceptions

La section exécutable est obligatoire

Différentes sortes de blocs◦ Anonyme, nommé, procédure ou

fonction, déclencheur

Structures de blocs

DECLARESection déclarative (les variables)

BEGINSection exécutable (le code)

EXCEPTIONSection des exceptions (si une erreur)

END;

Structures de blocs(anonyme)

Structures de blocs exempleDECLARE

V_NoEmp NUMBER(4) := 1010;

V_NomE VARCHAR2(20); --variable

BEGIN

/* Retrouver les infos de l'élève */

SELECT NomE

INTO V_NomE FROM Emp

WHERE NoEmp = V_NoEmp;

EXCEPTION

WHEN NO_DATA_FOUND THEN

INSERT INTO TableErreur (ColonneInfo)

VALUES (‘Employé 1010 n''existe pas');

END;

Commentaires

-- sur une ligne

∕* sur plusieurs lignes physiques *∕

Maximum 30 caractères ◦ tous significatifs

Commence par une lettre Peut contenir

◦ $◦ _◦ #

Pas d ’espace Pas de différence entre maj et

min

Nom d ’objet

Nombre◦ 415, 21.6, 3.05 E19, null, …

chaîne de caractères◦ ‘ chaîne de caractères ’◦ ‘ 31-jan-97 ’◦ null

booléen◦ TRUE◦ FALSE◦ NULL

Littéraux

38 types différents

Numérique◦ NUMBER

Caractère◦ VARCHAR2, CHAR

Booléen◦ BOOLEAN

Date Heure◦ DATE

Type de variables

Nom_Var Type [CONSTANT] [NOT NULL] [:=valeur]

Exemples : V_DateEmbauche DATE; V_AssezDonnees BOOLEAN; V_RevenueTot NUMBER (15,2); V_LongPara VARCHAR2(2000); C_Date CONSTANT DATE := ‘ 15-avr-99 ’;

V_Temp NUMBER NOT NULL := 0;

V_Compte NUMBER := 0; V_Compte NUMBER DEFAULT 0;

C_ValMin CONSTANT NUMBER(5) := 10;

V_NoEmp Emp.NoEmp%TYPE; (Prend le type de la colonne NoEmp de la table Emp et l’applique à la variable V_NoEmp.

Déclaration de variables

Les opérateurs

Opérateur Opération

**, NOT

Puissance,Négation

+,- Signe

*,/ Mul, Div

+, - Add, Sous|| ConcaténationOpérateursRelationnels

Voir acétate suivante

AND Et

OR Ou

Les opérateurs relationnels

Opérateur Opération

= Égal à

<>, !=, ~= Différent

< Plus petit

> Plus grand

<= Plus petit ou égal

>= Plus grand ou égal

IS NULL◦ Compare avec la valeur NULL◦ IF V_Temp IS NULL THEN …

LIKE◦ Compare avec une valeur

contenant des frimes◦ V_Nom LIKE ‘L%SE’

(permet Lise, Lyse ou Louise) BETWEEN IN

Les autres opérateurs

Seulement les énoncés de DML (Data Manipulation Language)

◦SELECT◦INSERT◦UPDATE◦DELETE

Le SQL permis

• INSERT en SQLINSERT INTO TABLE Classes (Dept, Cours, Groupe, Description, Max_Ele, Inscrits, Local)VALUES (‘INF’,101, 2901, ‘Intro prog’,30, 28, ‘C5:29’);

• INSERT en PL/SQL

DECLARE V_Texte Classes.Description%TYPE;BEGIN V_Texte := ‘Intro prog’; INSERT INTO Classes

(Dept, Cours, Groupe, Description,Max_Ele, Inscrits, Local)

VALUES (‘INF’,101, 2901, V_Texte, 30, 28, ‘C5:29’);

END;

INSERT

Le SELECT doit retourner une ligne sinon il y aura erreur (EXCEPTION)

SELECT Nom_Colonne INTO Variable FROM Table WHERE Clause_Where;

Exemple:

DECLAREV_Dept Classes.Dept%TYPE;V_Cours Classes.Cours%TYPE;

BEGINSELECT Dept, Cours

INTO V_Dept, V_Cours FROM Classes

WHERE Local = ‘C5.29’;END;

SELECT

DECLARE V_No Eleve.No%TYPE;BEGIN SELECT Seq_EleveId.NEXTVAL INTO V_No FROM DUAL;

INSERT INTO Eleve (No,Nom,Prenom) VALUES (V_No, ‘Tard’, ‘Guy’);

INSERT INTO Eleve (No,Nom, Prenom) VALUES (Seq_EleveId.NEXTVAL, ‘Polo’, ‘Marco’);

END;

INSERT + SELECT

Package permettant des sorties dans SQL*Plus venant de procédures PL/SQL

Il faut obligatoirement avoir:◦ SET SERVEROUTPUT ON

PROCEDURE Prc_Sal (P_id NUMBER) IS V_TempSal NUMBER; BEGIN SELECT Salaire INTO V_TempSal FROM Employe WHERE id = P_id;

DBMS_OUTPUT.PUT_LINE ('Salaire=‘ || TO_CHAR(V_TempSal)); END;

DBMS_OUTPUT

Les énoncés decontrôle

IF condition THENune séquence d’instructions

END IF;

Exemple:IF V_TotalVente > V_Quota THEN UPDATE Employe SET Salaire = Salaire +

V_Bonus WHERE id = V_Temp;END IF;

IF THEN

IF condition THENune séquence d’instructions

ELSEune séquence d’instructions

END IF;

Exemple:IF V_TypeTrans = ‘CR’ THEN UPDATE Compte SET Balance = Balance +

V_Credit WHERE …;ELSE UPDATE Compte SET Balance = Balance –

V_Credit WHERE …;END IF;

IF THEN ELSE

IF condition THENune séquence d’instructions

ELSIF condition THENune séquence d’instructions

ELSEune séquence d’instructions

END IF;

Exemple:IF V_TotalVentes > 500000 THEN V_Bonus:=15000;ELSIF V_TotalVentes > 50000 THEN V_Bonus:=5000;ELSE V_Bonus:=1000;END IF;

IF THEN ELSIF

Quand une erreur arrive on dit qu’une exception s’est produite

Si nécessaire, on traite les exceptions dans la section EXCEPTION

Il y a des exceptions:◦ Pré-définies◦ Définies par l’usager

Les exceptions

ACCESS_INTO_NULL DUP_VAL_ON_INDEX INVALID_NUMBER LOGIN_DENIED NO_DATA_FOUND NOT_LOGGED_ON TOO_MANY_ROWS VALUE_ERROR ZERO_DIVIDE OTHERS

Exceptions Pré-définies

DECLARE V_TempSal NUMBER;BEGIN

SELECT salaireINTO V_TempSalFROM EmployeWHERE Nom = ‘Tremblay’;…

EXCEPTIONWHEN NO_DATA_FOUND THEN Message(‘Tremblay n’’existe

pas’);WHEN TOO_MANY_ROWS THEN Message(‘Il y a plusieurs

Tremblay’);END;

Exemple

On utilise une exception usager lorsque l’on rencontre un cas particulier (un cas à tous les 2 ans par exemple)

DéclarationsDECLARE Nom_Exeption EXCEPTION;…

DéclenchementBEGINIF Condition THEN RAISE Nom_Exception;…

CodeEXCEPTION

WHEN Nom_Exception THENTraitement de l’exeption;

END;…

Exceptions Usager

Procédures et Fonctions

de base de données

Créer une Procédure

• Utiliser un éditeur pour écrire un énoncé SQL– CREATE PROCEDURE

• Transporter l’énoncé dans SQLPLUS.

• Pour détruire la procédure– DROP PROCEDURE nom;

Syntaxe de l’énoncé SQL

CREATE [OR REPLACE]

PROCEDURE Nom_fct(Paramètres[,…])

IS

[Déclarations]

BEGIN

Énoncés;

[EXCEPTION

Traitement d ’exception;]

END;

Il est possible d’utiliser les paramètres pour retourner de l’information (passage par valeur ou référence).

Nom_Paramètre [ IN | OUT | INOUT ] Type [ := valeur par défaut ]◦ pas de restriction sur le type

Ex. NUMBER(4) - - invalideNUMBER - - valide

IN : valeur transférée vers la procédure mais non retournée.

OUT : valeur retournée uniquement. INOUT : valeur transféré et retournée. Par défaut le préfix IN est appliqué.

Paramètred ’une procédure

ExempleCREATE PROCEDURE

Verif(P_Nom IN VARCHAR2, P_No OUT NUMBER)IS DéclarationBEGIN Énoncées;[EXEPTION

Traitement des exceptions;END;]

Exemple de paramètres

Utiliser l’énoncé SQL◦ CREATE FUNCTION

Créer une fonction

CREATE [OR REPLACE] FUNCTION Nom_fct(Paramètres[,…]) RETURN Type

IS

Déclarations

BEGIN

Enoncés;

RETURN …;

[EXCEPTION

Traitement d ’exception;]

END;

• Pour détruire la procédure– DROP FUNCTION Nom_fct;

D’une autre fonction ou procédure.

D’un outils interactif◦ex: SQL PLUS.

D ’une application◦Form builder◦programme avec pré-compilateur

Dans un énoncé SQL (pour une fonction).

Appel d’uneprocédure ou fonction

EXECUTE Nom_fct(Paramètre[,…¸]);

Exemple:◦ soit la procédure Fct_Augmt_Sal(P_NoEmp, P_Montant)◦ EXECUTE Fct_Augmt_Sal (1043,200);

Appel de SQLPLUS PROCEDURE

Créer une variable session

VARIABLE V_Montant NUMBER

Exécuter la fonction

EXECUTE :V_Montant := Fct_Revenu(1243);

Appel de SQLPLUSFONCTION

Dans SELECT, INSERT, UPDATE, DELETE, comme les fonctions SQL régulières

Appel d’une fonctiond’un énoncé SQL

SELECT Nom, TO_DATE(date_embauche), Revenu(id)

FROM employe

ORDER BY Revenu(id);

Met fin à l’exécution d ’une procédure ou fonction.

Fonction:◦ RETURN (valeur)

Procédure:◦ RETURN

L ’énoncé RETURN

PL/SQL Avancé

• Les variables simples• Les variables de type enregistrement

• Structure itérative (boucles)• Définition et manipulation d'un curseur

Exemple 1:V_NoEmp employe.id%TYPE; (variable ayant le même type que la colonne id de la table employe)

Exemple 2:V_Essai V_NoEmp%TYPE;(variable du même type qu'une autre variable)

Variable simple

LOOPSéquence d ’instructions

END LOOP;on sort de la boucle avec: EXIT WHEN ou EXIT

Exemple1:DECLARE

…BEGIN

LOOPSELECT…EXIT WHEN V_Salaire >

200;END LOOP;

END;

Boucles SANS CONDITION

Exemple2On cherche le nom et le salaire du premieremployé dont le salaire est plus grand que20000 et on les inscrit dans la table Tempo.

DECLAREV_Sal Employe.Salaire%TYPE;V_NomE Employe.Nom%TYPE;

BEGIN LOOP SELECT Nom, Salaire INTO V_Sal, V_NomE

FROM Employe; IF V_Sal > 20000 THEN EXIT;

END IF; END LOOP;

INSERT INTO Tempo VALUES(V_Sal, V_NomE);COMMIT;

END;

Boucle sans condition

WHILE Condition LOOP Séquence d ’instructions

LOOP;

Exemple:On cherche le nom et le salaire du premieremployé dont le salaire est supérieur à20000 et on les inscrit dans la table

tempo.

DECLAREV_Sa; Employe.Salaire%TYPE;V_NomE Employe.Nom%TYPE;

BEGINWHILE V_Sal < 20000 LOOP

SELECT Salaire,Nom INTO V_Sal,V_ NomE

FROM Employe;END LOOP;

INSERT INTO Tempo VALUES(V_Sal, V_NomE);COMMIT;

END;

Boucles avec CONDITION

FOR V_Compteur IN [REVERSE] BorneInf..BorneSup LOOPSéquence d'instructions;

END LOOP;

Bornes: variables ou des constantes numériques Si la variable compteur n'est pas définie à

l'exécution de la boucle, elle le sera implicitement

exemple:V_Limite := 50;FOR V_Compteur IN 1 ..V_Limite LOOP

Séquences d'instructions; END LOOP;

Note : l’incrémentation ou la décrémentation ne

peut être que de 1

Boucles avec CONDITION

L'utilisation d'un curseur nous permet de manipuler ligne par ligne le résultat d'une requête qui retourne plusieurs lignes. Le curseur pointe sur la ligne courante de l'ensemble des lignes.

Mot clef CURSOR dans la section DECLARE d'un bloc d'instruction PL/SQL

CURSOR NomDuCurseur IS Requete

Exemple 1: CURSOR Cur_Vente IS

SELECT Numero,Detail,Quantite

FROM Produit;

Définition et utilisation du curseur

CURSOR Cur_Vente IS SELECT Num, Detail, Qte

FROM JeuWHERE Qte > 30;

Le curseur vente permet d’accéder auxcolonnes: Num, Detail et Qte de chacun desjeux dont la Qte > 30.

Exemple 2:

OPEN Nom_Curseur(initialisation (ouverture) du curseur)

FETCH Nom_Curseur INTO Nom_Var [,…](obtenir des informations de la ligne courrant et les placer dans les variables.)

ex. FETCH Cur_Vente INTO V_Num, V_Detail,V_Qte

CLOSE Nom_Curseur fermeture du curseur

Manipulation du curseur

Nom_Curseur%NOTFOUND: vrai, si la dernière instruction FETCH n’a pas réussie parce qu’il ne reste plus de rangée à traiter

Nom_Curseur%FOUND: vrai si la dernière instruction FETCH a réussi

Nom_Curseur%ROWCOUNT: retourne le nombre de rangées qui ont été traitées par l’instruction FETCH

Nom_Curseur%ISOPEN: donne vrai si le curseur est ouvert

Interrogation du curseur

Trouve le profit des jeux dont la cote est B et les résultats sont conservés dans une table (tempo) pour traitement ultérieur.

DECLAREV_No Jeu.Numero%TYPE;V_PrixNet Jeu.Detail%TYPE;V_PrixGros Jeu.Gros%TYPE;V_ProfitPourcent Tempo.Profit%TYPE;

CURSOR Cur_CurseurB IS SELECT Numero, Detail, Gros

FROM Jeu WHERE Cote = ‘ B ’;

BEGINOPEN Cur_CurseurB; curseur placé avant première

rangéeFETCH Cur_CurseurB

INTO V_No, V_PrixNet, V_PrixGros;--cuseur placé sur la rangée suivante et met les infos dans --les variables

WHILE Cur_CurseurB%FOUND LOOPV_ProfitPourcent := V_PrixNet –

V_PrixGros/V_PrixNet * 100;INSERT INTO Tempo

VALUES (V_No,V_ProfitPourcent);--traitement de la rangée suivanteFETCH Cur_CurseurB

INTO V_No, V_PrixNet, V_PrixGros;END LOOP;CLOSE Cur_CurseurB;COMMIT;END;

While LOOPS

Exemple:DECLAREV_No Jeu.Numero%TYPE;V_PrixNet Jeu.Detail%TYPE;V_PrixGros Jeu.Gros%TYPE;V_ProfitPourcent Tempo.Profit%TYPE;

CURSOR Cur_CurseurB IS SELECT Numero, Detail, Gros

FROM Jeu WHERE Cote = ‘ B ’;

BEGINOPEN Cur_CurseurB; curseur placé avant première

rangéeLOOPFETCH Cur_CurseurB

INTO V_No, V_PrixNet, V_PrixGros;--cuseur placé sur la rangée suivante et met les infos dans --les variables

EXIT WHEN Cur_CurseurB%NOTFOUND; --sort lorsqu ’il n ’y a plus de rangée à fetchéeV_ProfitPourcent := V_PrixNet –

V_PrixGros/V_PrixNet * 100;INSERT INTO Tempo VALUES (V_No,V_ProfitPourcent);

END LOOP;CLOSE Cur_CurseurB;COMMIT;END;

Boucle LOOP…END LOOP

Exemple:DECLAREV_No Jeu.Numero%TYPE;V_PrixNet Jeu.Detail%TYPE;V_PrixGros Jeu.Gros%TYPE;V_ProfitPourcent Tempo.Profit%TYPE;

CURSOR Cur_CurseurB IS SELECT Numero, Detail, Gros

FROM Jeu WHERE Cote = ‘ B ’;--enregistrement pour recevoir les données fetchéesCur_LigneJeu Cur_CurseurB%ROWTYPE;

BEGIN--- OPEN curseur est fait implicitementFOR Cur_LigneJeu IN Cur_CurseurB LOOP---un FETCH implicite est fait

V_ProfitPourcent := Cur_LigneJeu.Detail – Cur_LigneJeu.Gros/

Cur_LigneJeu.Detail * 100;INSERT INTO Tempo

VALUES (Cur_LigneJeu.Numero,V_ProfitPourcent);

END LOOP;--- CLOSE curseur est fait implicitement COMMIT;END;

Curseur FOR LOOPS