•• •• •• •• •• •• •• ••

(cid:1) PL/SQL (Procedural Language/ SQL), l’extension

procédurale proposée par Oracle pour SQL (L4G),

(cid:1) Il permet de combiner des requêtes SQL (SELECT,

INSERT, UPDATE et DELETE) et des instructions

procédurales (boucles, conditions...),

(cid:1) Créer des traitements complexes destinés à être stockés sur le

serveur de base de données,

(cid:1) Les structures de contrôle habituelles d’un langage (IF,

WHILE…) ne font pas partie intégrante de la norme SQL.

Oracle les prend en compte dans PL/SQL.

(cid:1) Intégration complète du SQL,

(cid:1) Parfaite Intégration avec Oracle et Java,

(cid:1) On peut lancer des sous-programme PL/SQL à partir de Java et de

même, on peut appeler des procédures Java à partir d’un block

PL/SQL

(cid:1) Portabilité

(cid:1) Les programmes PL/SQL sont indépendants du système

d’exploitation qui héberge le serveur Oracle.

(cid:1) En changeant de système, les applicatifs n’ont pas à être modifiés.

http://www.oracle.com

01/05/2015

http://www.oracle.com

[DECLARE # déclarations et initialisation]

BEGIN # instructions exécutables

[EXCEPTION # interception des erreurs]

END;

(cid:1) Syntaxe

(cid:1) Exemples

(cid:1) num NUMBER(4) ;

(cid:1) num2 NUMBER NOT NULL := 3.5 ;

(cid:1) en_stock BOOLEAN := false ;

(cid:1) limite CONSTANT REAL := 5000.00 ;

(cid:1) Remarques

http://www.oracle.com

(cid:1) La contrainte NOT NULL doit être suivie d'une clause d'initialisation

(cid:1) Les déclarations multiples ne sont pas permises. Donc, on ne peut

pas écrire :

v1 , v2 NUMBER ;

01/05/2015

(cid:1) PL/SQL supporte deux types de commentaires :

1. Mono-lignes:

commençant au symbole -- et finissant à la fin de la ligne,

1. Multi-lignes:

commençant par /*et finissant par */

(cid:1) Les instructions peuvent être écrites sur plusieurs lignes

(cid:1) Les identifiants peuvent contenir jusqu'à 30 caractères et

doivent :

(cid:1) Être encadrés de guillemets s’ils contiennent un mot réservé

(cid:1) Commencer par une lettre

(cid:1) Avoir un nom distinct de celui d'une table ou d'une colonne de

la base

(cid:1) Déclarations multiples interdites :

(cid:1) i, j integer;

http://www.oracle.com

SET SERVEROUTPUT ON

DECLARE

v_date DATE :='01-10-2010';

BEGIN

DBMS_OUTPUT.PUT_LINE(ADD_MONTHS(v_date,3));

--01/01/11

DBMS_OUTPUT.PUT_LINE(LAST_DAY(v_date));

--31/10/10

DBMS_OUTPUT.PUT_LINE(MONTHS_BETWEEN(v_date, ADD_MONTHS(v_date,-

2)));

--2

DBMS_OUTPUT.PUT_LINE(NEXT_DAY(v_date, 'Lundi')); --04/10/10

End;

http://www.oracle.com

(cid:1) Les types habituels correspondants aux types SQL ou

Oracle : integer, varchar,…

(cid:1) Types composites adaptés à la récupérationdes colonnes

et lignes des tables SQL : %TYPE, %ROWTYPE

(cid:1) Types composés: type RECORD

(cid:1) Utilité

(cid:1) On peut déclarer qu’une variable est du même type qu’une

colonne d’une table ou d’une vue (ou qu’une autre variable) :

(cid:1) Exemple

http://www.oracle.com

(cid:1) Déclarer une variable à partir d'un ensemble de colonnes

d'une table ou d'une vue

(cid:1) Préfixer %ROWTYPE avec le nom de la table de la base

de données

(cid:1) Les champs dans le RECORD ont les mêmes noms et les

mêmes types de données que les colonnes de la table ou

de la vue associées

employe emp%ROWTYPE; nom emp.nome.%TYPE;

select * INTO employe from emp where matr = 900;

nom := employe.nome; employe.dept := 20; … insert into emp values employe; …

http://www.oracle.com

01/05/2015

(cid:1) Une variable de type record peut représenter une ligne

d'une table relationnelle

(cid:1) Peut contenir un ou plusieurs champs de type scalaire,

RECORD ou TABLE

(cid:1) Similaire à la structure d'enregistrement utilisée dans les

L3G

(cid:1) Traite un ensemble de champs comme une unité logique

(cid:1) Pratique pour récupérer et traiter les données d'une table

DECLARE

Déclaration d’un RECORD contenant 3 champs, dont un est non nul avec initialisation du champ qtyInStock

TYPE R_product IS RECORD (

id NUMBER NOT NULL=1,

identifiant CHAR(15),

qtyInStock NUMBER := 50);

r_produit R_product;

BEGIN

--Affectation d’un record

r_produit.id:=5;

r_produit.identifiant:=’Lait’;

r_produit.qtyInStock:=150;

http://www.oracle.com

END;

http://www.oracle.com

IF condition THEN Instructions; END IF;

IF condition THEN Instructions 1; ELSE Instructions 2; END IF;

IF condition THEN Instructions 1; ELSIF condition2 THEN Instructions 2; ELSIF … … ELSE Instructions N; END IF;

http://www.oracle.com

CASE expression WHEN expr1 THEN instructions1; WHEN expr2 THEN instructions2; … ELSE instructionsN; END CASE;

WHILE condition LOOP

instructions;

END LOOP;

LOOP

instructions; EXIT [WHEN condition]; instructions;

END LOOP;

Remarque

Expression peut avoir n’importe quel type simple (ne peut pas par exemple être un RECORD)

FOR compteur IN [REVERSE] inf..sup LOOP

instructions;

END LOOP;

http://www.oracle.com

(cid:1) Définir une fonction factorielle :

http://www.oracle.com

01/05/2015

(cid:1) select expr1, expr2,… into var1, var2,…

met des valeurs de la BD dans une ou plusieurs variables

(cid:1) Le select ne doit renvoyer qu’une seule ligne (cid:1) Avec Oracle il n’est pas possible d’inclure un select sans «into» dans une procédure ; pour ramener des lignes

(cid:1) Si le select renvoie :

(cid:1) Plus d’une ligne, une exception «TOO_MANY_ROWS» (ORA-

01422) est levée

(cid:1) Aucune ligne, une exception «NO_DATA_FOUND» (ORA-

01403) est levée

DECLARE

v_nom emp.nome%TYPE; v_emp emp%ROWTYPE;

BEGIN

select nome into v_nom from emp where matr = 500;

select * into v_emp from emp where matr = 500;

http://www.oracle.com

(cid:1) Les requêtes SQL (insert, update, delete,…) peuvent

utiliser les variables PL/SQL

(cid:1) Les commit et rollback doivent être explicites ; aucun n’est

effectué automatiquement à la sortie d’un bloc

DECLARE

v_emp emp%ROWTYPE; v_nom emp.nome%TYPE;

BEGIN

v_nom := 'Dupond'; insert into emp (matr, nome) values(600, v_nom);

v_emp.matr := 610; v_emp.nome := 'Durand'; insert into emp (matr, nome) values(v_emp.matr,v_emp.nome); commit;

END;

http://www.oracle.com

01/05/2015

(cid:1) Les instructions INSERT, DELETE, UPDATE s'écrivent

telles quelles dans un programme

(cid:1) Elles peuvent utiliser les variables du programme

(cid:1) Il faut donner des noms différents aux variables du programme

et aux colonnes des tables manipulées par le programme

(cid:1) Pour une requête dont le résultat est constitué d'une

unique ligne, on peut utiliser la syntaxe

SELECT ... INTO....

(cid:1) Pour une requête qui ramène un nombre quelconque de

lignes, il faut utiliser un curseur

http://www.oracle.com

Ou bien

NB : emp_rec de type Employee%rowtype

(cid:1) Un curseur est une structure de données séquentielle

avec une position courante

(cid:1) On utilise un curseur pour parcourir le résultat d'une

requête SQL dans un programme PL/SQL.

(cid:1) Deux types de curseur :

(cid:1) Curseurs implicites : déclarés pour toutes les instructions LMD

et les SELECT en PL/SQL

(cid:1) Curseurs Explicites : déclarés et nommés au sein du code

source (par le développeur)

http://www.oracle.com

01/05/2015

(cid:1) PL/SQL déclare implicitement un curseur :

(cid:1) pour les instructions du DML qui modifient la base (INSERT,

DELETE, UPDATE)

(cid:1) pour les requêtes de la forme SELECT INTO.

(cid:1) Avec un curseur implicite, on peut obtenir des

informations sur la requête réalisée, grâce aux attributs.

(cid:1) En effet SQL%attribut applique l'attribut sur la dernière requête

SQL exécutée

(cid:1) Les curseurs implicites sont tous nommés SQL

http://www.oracle.com

(cid:1) Pour traiter les select qui renvoient plusieurs lignes

(cid:1) Ils doivent être déclarés

(cid:1) Le code doit les utiliser explicitement avec les ordres

OPEN, FETCH et CLOSE

(cid:1) Le plus souvent on les utilise dans une boucle dont on

sort quand l’attribut NOTFOUND du curseur est vrai

http://www.oracle.com

01/05/2015

(cid:1) Instructions :

(cid:1) OPEN : initialise le curseur (cid:1) FETCH : extrait la ligne courante et passe à la suivante (pas

d'exception si plus de ligne) (cid:1) CLOSE : invalide le curseur (cid:1) Si on veut parcourir toutes les lignes : boucle FOR

(cid:1) Attributs du curseur :

(cid:1) %found vrai si le dernier fetch a ramené une ligne (cid:1) %notfound vrai si le dernier fetch n'a pas ramené de ligne (cid:1) %isopen vrai ssi le curseur est ouvert (cid:1) %rowcount le nombre de lignes déjà ramenées

http://www.oracle.com

(cid:1) On peut définir des paramètres en entrée utilisés dans la

requête

(cid:1) Exemple

CURSOR emp_cursor(dnum NUMBER) IS

SELECT salary, comm FROM Employee WHERE deptno = dnum ;

http://www.oracle.com

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;

http://www.oracle.com

(cid:1) Un module stocké est un programme rangé dans la base

CREATE OR REPLACE PROCEDURE

p_name [ les_parametres ] IS

de données

(cid:1) On peut ainsi définir en PL/SQL :

(cid:1) des procédures

(cid:1) des fonctions

(cid:1) des paquetages

(cid:1) Ces programmes peuvent être appelés par les

programmes clients, et sont exécutés par le serveur

declarations

BEGIN

code PL/SQL

END ;

CREATE OR REPLACE FUNCTION f_name [ les_parametres ] RETURN datatype IS

Declarations

BEGIN

code PL/SQL

END ;

http://www.oracle.com

01/05/2015

(cid:1) Pour déclarer un paramètre, la syntaxe est :

nom_param [mode ] datatype [ := valeur_defaut ]

CREATE OR REPLACE FUNCTION nom_dept(numero dept.dept_no%type) return VARCHAR2 IS

nom dept.dept_name%type ;

(cid:1) Il y a trois modes de passage de paramètre :

BEGIN

(cid:1) mode IN : paramètre en entrée (par défaut)

(cid:1) mode OUT : paramètre en sortie

(cid:1) mode IN OUT : paramètre en entrée et sortie

select dept_name into nom from dept where dept_no = numero ; return nom ;

END;

http://www.oracle.com

CREATE OR REPLACE PROCEDURE nom_dept2 (

numero IN dept.dept_no%type, nom OUT dept.dept_name%type) IS

BEGIN

select dept_name into nom from dept where dept_no = numero ;

END ;

http://www.oracle.com

01/05/2015

(cid:1) En PL/SQL, la gestion des erreurs se fait grâce aux exceptions (cid:1) Il existe 2 types d’exception :

(cid:1) prédéfinie par Oracle (cid:1) définie par le programmeur

(cid:1) Syntaxe BEGIN

...corps du bloc...

EXCEPTION

when exception1 [or exception2 ...] then

instructions ;

when exception3 [or exception4 ...] then

instructions ; ...

[when others then instructions ;]

END;

(cid:1) Si l'exception existe dans une clause When, alors les

instructions de cette clause sont exécutées et le

programme est terminé

(cid:1) Sinon

(cid:1) S’il existe une clause When Others alors les instructions de

cette clause sont exécutées et le programme est terminé

(cid:1) Sinon l'exception est propagée au bloc englobant ou au

programme appelant

http://www.oracle.com

DECLARE

pe_ratio NUMBER(3,1);

BEGIN

SELECT prix / gains INTO pe_ratio FROM stocks WHERE NumProd = 110;

-- pourrait provoquer une erreur de division par zéro

INSERT INTO stats (NumProd, ratio) VALUES (110,

pe_ratio);

EXCEPTION WHEN ZERO_DIVIDE THEN INSERT INTO stats (NumProd, ratio) VALUES (110,

NULL);

END;

(cid:1) TOO_MANY_ROWS : instruction select ... into qui ramène plus

d'une ligne.

(cid:1) NO_DATA_FOUND : instruction select ... into qui ne ramène

aucune ligne.

(cid:1) INVALID_CURSOR : ouverture de curseur non valide.

(cid:1) CURSOR_ALREADY_OPEN : ouverture d'un curseur déjà

ouvert.

(cid:1) VALUE_ERROR : erreur arithmétique (conversion, taille, ...)

pour un NUMBER.

(cid:1) ZERO_DIVIDE : division par 0 ;

(cid:1) STORAGE_ERROR : dépassement de capacité mémoire.

(cid:1) LOGIN_DENIED : connexion refusée

http://www.oracle.com

01/05/2015

(cid:1) Elles doivent être déclarées avec le type EXCEPTION

(cid:1) On les lève avec l’instruction RAISE

(cid:1) On déclare l'exception nomException grâce à l'instruction

nomException EXCEPTION ;

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; -- suite du bloc

EXCEPTION

WHEN salaire_trop_bas THEN . . .; WHEN OTHERS THEN

dbms_output.put_line(SQLERRM);

END;

http://www.oracle.com

(cid:1) Un trigger (déclencheur) est un programme qui se

déclenche automatiquement suite à un événement

(cid:1) Il fait partie du schéma (comme les modules stockés)

mais que l'on n'appelle pas explicitement, à la différence

d'une procédure stockée

http://www.oracle.com

CREATE [OR REPLACE] TRIGGER nom_trigger instant liste_evts ON nom_table [FOR EACH ROW] [WHEN ( condition ) ]

--Corps

instant ::= AFTER | BEFORE liste_evts ::= evt {OR evt} evt ::= DELETE | INSERT | UPDATE [OF { liste_cols }] liste_col ::= nom_col { , nom_col }

--corps de pgme PL/SQL

(cid:1) On définit :

(cid:1) la table à laquelle le trigger est lié,

(cid:1) les instructions du DML qui déclenchent le trigger

(cid:1) le moment où le trigger va se déclencher par rapport à l'instruction

DML (avant ou après)

(cid:1) si le trigger se déclenche

(cid:2) une seule fois pour toute l'instruction (i.e. trigger instruction),

(cid:2) une fois pour chaque ligne modifiée/insérée/supprimée. (i.e. trigger ligne,

avec l'option FOR EACH ROW)

(cid:1) et éventuellement une condition supplémentaire de déclenchement

(clause WHEN)

http://www.oracle.com

(cid:1) Utiliser BEFORE

(cid:1) Si le trigger doit déterminer si l'instruction DML est autorisée

(cid:1) Si le trigger doit "fabriquer" la valeur d'une colonne pour pouvoir

ensuite la mettre dans la table

(cid:1) Utiliser AFTER

(cid:1) Si on a besoin que l'instruction DML soit terminée pour exécuter

le corps du trigger

(cid:1) Un trigger instruction se déclenche une fois, suite à une

instruction DML

(cid:1) Un trigger ligne (FOR EACH ROW) se déclenche pour

chaque ligne modifiée par l'instruction DML

http://www.oracle.com

01/05/2015

(cid:1) Dans un trigger ligne, on peut faire référence à la ligne

courante, celle pour laquelle le trigger s'exécute

(cid:1) Pour cette ligne, on a accès à la valeur avant l'instruction

DML (nommée :old) et à la valeur après l'instruction

(nommée :new)

:old

null

Insert

:new

Ligne insérée

Delete

Ligne supprimée

null

Update

Ligne avant modification

Ligne après modification

On peut définir une condition pour un trigger ligne

le trigger se déclenchera pour chaque ligne vérifiant la condition

http://www.oracle.com

Create or replace trigger journal_emp

after update of salary on EMPLOYEE for each row when (new.salary < old.salary) -- attention, ici on utilise new et pas :new

begin

insert into EMP_LOG(emp_id, date_evt, msg) values (:new.empno, sysdate, 'salaire

diminué');

end ;

(cid:1) Les triggers se déclenchent dans l'ordre suivant :

1. Triggers instruction BEFORE

2. Triggers ligne BEFORE (déclenchés n fois)

3. Triggers ligne AFTER (déclenchés n fois)

4. Triggers instruction AFTER

http://www.oracle.com

01/05/2015

(cid:1) Un paquetage permet de regrouper un ensemble des

(cid:1) La spécification contient :

procédures, exceptions, constantes...

(cid:1) Un paquetage est composé de :

(cid:1) Une spécification : contient des éléments que l'on rend

accessibles à tous les utilisateurs du paquetage

(cid:1) Un corps : contient l'implémentation et ce que l'on veut cacher

(cid:1) des signatures de procédures et fonctions

(cid:1) des constantes et des variables

(cid:1) des définitions d'exceptions

(cid:1) des définitions de curseurs

http://www.oracle.com

(cid:1) Le corps contient :

(cid:1) Les corps des procédures et fonctions de la spécification

(obligatoire)

(cid:1) D'autres procédures et fonctions (cachées)

(cid:1) Des déclarations que l'on veut rendre privées

(cid:1) Un bloc d'initialisation du paquetage si nécessaire

-- spécification

CREATE OR REPLACE PACKAGE mon_paq AS

procedure p ;

procedure p(i NUMBER) ;

function p(i NUMBER) return NUMBER ;

cpt NUMBER ;

function get_cpt return NUMBER ;

mon_exception EXCEPTION ;

PRAGMA EXCEPTION_INIT(mon_exception, -20101);

END ;

Package créé.

http://www.oracle.com

01/05/2015

-- corps create or replace package body mon_paq as

procedure p is begin

dbms_output.put_line('toto');

end ; procedure p(i NUMBER) is begin

dbms_output.put_line(i);

end ; function p(i NUMBER) return NUMBER is begin

if (i > 10) then raise mon_exception ; end if ; return i ;

end ; function get_cpt return NUMBER is begin return cpt ; end ;

end ; /

http://www.oracle.com

01/05/2015

PL/SQL Overview

PL/SQL Overview