(cid:1) PL/SQL (Procedural Language/ SQL), lextension

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

PL/SQL

(cid:1) Portabilit

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

dexploitation qui h berge le serveur Oracle.

(cid:1) En changeant de syst me, les applicatifs nont pas tre modifi s.

http://www.oracle.com

01/05/2015

http://www.oracle.com

BEGIN

instructions ex cutables

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 sils 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 quune variable est du m me type quune

colonne dune table ou dune vue (ou quune 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 dun 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 dun 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 ;

instructions;

END LOOP;

Remarque

Expression peut avoir nimporte quel type simple (ne peut pas par

exemple tre un RECORD)

FOR compteur IN 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 quune seule ligne

(cid:1) Avec Oracle il nest pas possible dinclure un select sans

into dans une proc dure ; pour ramener des lignes

(cid:1) Si le select renvoie :

(cid:1) Plus dune 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 nest

effectu automatiquement la sortie dun 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 lattribut 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

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

(cid:1) pr d finie par Oracle

(cid:1) d finie par le programmeur

(cid:1) Syntaxe

BEGIN

...corps du bloc...

EXCEPTION

when exception1 then

instructions ;

when exception3 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) Sil 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 linstruction 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 TRIGGER nom_trigger

instant liste_evts

ON nom_table

--Corps

instant ::= AFTER | BEFORE

liste_evts ::= evt {OR evt}

evt ::= DELETE | INSERT | UPDATE

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

instructions ex cutables