PROGRAMMATION PL/SQL

OUELBANI FOUED

SGBD

2008-2009

STRUCTURE DUN PROGRAMME PL/SQL

DECLARE Optionnel BEGIN Obligatoire
.

DECLARATION
EXECUTABLE

EXCEPTION Optionnel END

- Obligatoire;

/
2008-2009

OUELBANI FOUED

SGBD

EXEMPLE DUN BLOC PL/SQL

EXEMPLE
DECLARE v_ename varchar(20); BEGIN Select ename into v_ename from emp where empno=7839; END ; / Placer un point virgule (;) la fin de dune instruction SQL ou PL/SQL Utiliser un slash (/) pour excuter un bloc PL/SQL anonyme

OUELBANI FOUED

SGBD

2008-2009

SECTION : DECLARATION
Dbute par le mot DECLARE et permet la dclaration des variables et constantes. EXEMPLE
No_Produit Dsignation PU Number ; VARCHAR2(20) ; NUMBER(6,2) := 100.00;

OUELBANI FOUED

SGBD

2008-2009

SECTION EXECUTABLE : [Link]

Intgre diffrents types dinstructions : Instruction daffectation Instruction de contrle de flux Instruction SQL Instruction curseur

OUELBANI FOUED

SGBD

2008-2009

SECTION : GESTION DES EXCEPTIONS

Traite les erreurs qui se produisent pendant lexcution dun programme PL/SQL Peut tre prdfinie : DUP_VAL_ON_INDEX : insertion dune cl duplique NO_DATA_FOUND : instruction select ne retournant aucune ligne ... Peut tre dfinie : traiter par le programmeur.

OUELBANI FOUED

SGBD

2008-2009

TYPES DE BLOCS PL/SQL

Blocs anonymes
Blocs PL/SQL non nomms, imbriqus dans une application ou crs de faon interactive. Ils sont dclars dans une application lendroit de leur excution. Ils sont disponibles dans tous les environnements PL/SQL.

[DECLARE <Dclarations>] BEGIN <Instructions> [EXCEPTION <Traitements des erreurs>] END ;

OUELBANI FOUED

SGBD

2008-2009

TYPES DE BLOCS PL/SQL

Procdures et fonctions stockes
Blocs PL/SQL nomms et stockes sous forme dobjet de base de donnes. Rsident sur le serveur Oracle. Peuvent utiliser des paramtres dentre et de sortie. Sont invoques dune manire rptitive. FUNCTION nom(paramtres) RETURN type_donne IS <dclaration> BEGIN <instructions> [EXCEPTION] <traitement des exceptions> END

PROCEDURE nom (paramtres) IS <dclaration> BEGIN <instructions> [EXCEPTION] <traitement des exceptions> END ;

OUELBANI FOUED

SGBD

2008-2009

CONSTRUCTION DUN PROGRAMME

Type de construction Bloc Anonyme Description Bloc PL/SQL sans nom dans une application ou excut dune faon intractive Bloc PL/SQL nomm, stock sur le serveur oracle et acceptant des paramtres Bloc PL/SQL nomm, stock dans une application oracle Developer ou une librairie partage acceptant des paramtres Disponibilit Tous les environnements PL/SQL

Procdure ou Fonction stocke Procdure ou Fonction applicative

Serveur Oracle

Produits Oracle Developer (FORMS)

Package

Module PL/SQL nomm regroupant des procdures, des fonctions et des variables

Serveur oracle et les produits Oracle Developer (FORMS)

Trigger bas

Bloc PL/SQL associ une table, qui se dclenche automatiquement sur une instruction LMD
Bloc PL/SQL associ un vnement dans une application avec un dclencheur

Serveur Oracle

Trigger applicatif

Produits Oracle Developer (FORMS)

OUELBANI FOUED

SGBD

2008-2009

GESTION DES VARIABLES

Les variables sont dclares et initialises dans la section

dclaratives
De nouvelles valeurs sont affectes aux variables dans la section excutable Passage des valeurs des programmes par lintermdiaire de paramtres

OUELBANI FOUED

SGBD

2008-2009

10

DECLARATION DES VARIABLES

Suivre des conventions de dnomination Initialiser les variables dfinies avec la contrainte NOT NULL Initialisation laide de loprateur daffectation (:=) ou du mot rserv DEFAULT Dclarer un seul identifiant par ligne Dclarer une constante en utilisant le mot cl CONSTANT

OUELBANI FOUED

SGBD

2008-2009

11

REGLES DE DENOMINATION
Deux variables peuvent porter le mme nom si elles sont dans des blocs distincts Les noms des variables doivent tre diffrents des noms des colonnes et des tables utiliss dans le bloc. Lidentifiant ne doit pas dpasser 30 caractres. Le premier caractre doit tre une lettre, les autres peuvent tre des lettres, des nombres ou des caractres spciaux.

OUELBANI FOUED

SGBD

2008-2009

12

VARIABLE PL/SQL
INTEGER -- max 38chiffres NUMBER -- max 125 chiffres BINARY_INTEGER : -2147483647 - +2147483647 NATURAL :0 - 2147483647 POSITIVE : 1 - 2147483647 CHAR;-- Max 32767 caractres VARCHAR2; -- Max 32767 caractres LONG; -- Max 2147483647 caractres DATE; -- 4712 avant AVJC 9999 APJC BOOLEAN :TRUE,FALSE,NULL %TYPE : type de variable quivalent au type de colonne dune table ou dune autre variable %ROWTYPE : type de variable quivalent une ligne dune table

OUELBANI FOUED

SGBD

2008-2009

13

DECLARATION DES VARIABLES

EXEMPLES
Declare v_remise CONSTANT real := 0.10; v_hiredate date; g_deptno number(2) NOT NULL := 10; v_integer integer; -- max 38chiffres v_number1 number; -- max 125 chiffres v_number2 number(38,3); v_bool boolean; -- valeurs possibles TRUE, FALSE, NULL et NOT NULL v_char char(5);-- Max 32767 caractres v_varchar2 varchar2(20); -- Max 32767 caractres v_long LONG; -- Max 2147483647 caractres v_date date; -- Les dates peuvent aller de -4712 avant AVJC 9999 APJC

OUELBANI FOUED

SGBD

2008-2009

14

DECLARATION DES VARIABLES

EXEMPLES
Declare v_ename [Link]%type; v_emp emp%rowtype; v_n1 number(5,3); V_n2 v_n1%type;

OUELBANI FOUED

SGBD

2008-2009

15

INITIALISATION DES VARIABLES

Oprateur daffectation (:=) Mot cl DEFAULT Contrainte NOT NULL

EXEMPLES
v_integer integer := 12345; v_bool Boolean :=TRUE; v_char varchar(30) NOT NULL := 'SGBD'; v_date date DEFAULT '01-Janv.-2009';

OUELBANI FOUED

SGBD

2008-2009

16

AFFECTATION DES VALEURS AUX VARIABLES

Oprateur daffectation (:=) Mot cl DEFAULT Contrainte NOT NULL Identifiant := expr;

EXEMPLES
BEGIN v_integer := 12345; v_number1 := 1234567.453; v_number2 := 1.9999E+125; v_bool :=TRUE; v_char := 'SGBD'; v_long := 'Cours PL/SQL'; v_date := '21-fvr.-2008'; END;
OUELBANI FOUED SGBD 2008-2009

17

PORTEE DES VARIABLES

EXEMPLE
DECLARE v1 integer := 100; v2 varchar(5) :='A'; BEGIN DECLARE v1 integer; v3 varchar2(10):=v2||'1'; v4 varchar2(10):='B'; BEGIN dbms_output.put_line('v1(1) =' || v1 || chr(10) || 'v2(1) =' || v2 || chr(10) || 'v3(1) =' || v3); END; dbms_output.put_line('v1(2) =' || v1 || chr(10) || 'v2(2) =' || v2); dbms_output.put_line('v3(2) =' || v3); END; /

OUELBANI FOUED

SGBD

2008-2009

18

STRUCTURES DE CONTRLE : IF THEN ELSE

IF condition THEN instructions; END IF; IF condition THEN instructions1;

ELSE instructions2; END IF;

IF condition1 THEN instructions1; ELSIF condition2; THEN instructions2;

ELSIF ... ELSE instructionsN; END IF;

OUELBANI FOUED

SGBD

2008-2009

19

STRUCTURES DE CONTRLE : IF THEN ELSE

EXEMPLES
DECLARE v1 integer := 1100; v2 integer := 200; BEGIN IF v1 < v2 THEN dbms_output.put_line('v1 < v2'); ELSE dbms_output.put_line('v2 <= v1'); END IF; END; /

OUELBANI FOUED

SGBD

2008-2009

20

STRUCTURES DE CONTRLE : IF THEN ELSIF

EXEMPLES
DECLARE v1 number:= 685; v2 number := 125; V3 number :=870; BEGIN IF v1 < v2 THEN IF v2<v3 then dbms_output.put_line('v1 < v2 < v3'); ELSIF v3 < v1 then dbms_output.put_line('v3 < v1 < v2'); ELSE dbms_output.put_line('v1 <=v3 < v2'); END IF; ELSIF v1 < v3 then dbms_output.put_line('v2 < v1 < v3'); ELSIF v3<v2 then dbms_output.put_line('v3 < v2 <= v1'); ELSE dbms_output.put_line('v2 <= v3 <= v1'); END IF; END; /

OUELBANI FOUED

SGBD

2008-2009

21

STRUCTURES DE CONTRLE : IF THEN ELSE

EXEMPLE DECLARE v1 integer := 1100; v2 integer := 200; BEGIN IF v1 < v2 THEN dbms_output.put_line('v1 < v2'); ELSE dbms_output.put_line('v2 <= v2'); END IF; END; /

OUELBANI FOUED

SGBD

2008-2009

22

STRUCTURES DE CONTRLE : CASE

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

OUELBANI FOUED

SGBD

2008-2009

23

STRUCTURES DE CONTRLE : CASE

EXEMPLES
DECLARE v1 integer := 4; BEGIN CASE v1 WHEN 1 THEN dbms_output.put_line('A'); WHEN 2 THEN dbms_output.put_line('B'); WHEN 3 THEN dbms_output.put_line('C'); ELSE dbms_output.put_line('X'); END CASE; END; /

OUELBANI FOUED

SGBD

2008-2009

24

STRUCTURES DE CONTRLE : CASE

EXEMPLES
DECLARE v1 integer := 18; BEGIN CASE WHEN v1<5 THEN dbms_output.put_line('A'); WHEN v1<10 THEN dbms_output.put_line('B'); WHEN v1<15 THEN dbms_output.put_line('C'); ELSE dbms_output.put_line('X'); END CASE; END; /

OUELBANI FOUED

SGBD

2008-2009

25

STRUCTURES ITERATIVES : WHILE LOOP END LOOP

WHILE condition LOOP instructions; END LOOP;

EXEMPLES

DECLARE v1 integer :=1; BEGIN WHILE v1 <10 LOOP dbms_output.put_line(v1); v1 := v1+1; END LOOP; END; /
OUELBANI FOUED SGBD 2008-2009

26

STRUCTURES ITERATIVES : LOOP WHEN END LOOP

LOOP instructions; EXIT [WHEN condition]; instructions; END LOOP;

EXEMPLES
DECLARE v1 integer :=1; BEGIN LOOP dbms_output.put_line(v1); EXIT WHEN v1 =10; v1 := v1+1; END LOOP; END;/
OUELBANI FOUED SGBD 2008-2009

27

STRUCTURES ITERATIVES : FOR LOOP END LOOP

FOR compteur IN [REVERSE] inf..sup LOOP instructions; END LOOP;

EXEMPLES
DECLARE v1 integer :=1; BEGIN FOR v1 IN 1..10 LOOP dbms_output.put_line(v1); END LOOP; END; /

OUELBANI FOUED

SGBD

2008-2009

28

INSTRUCTION SELECT DANS PL/SQL

Utilisation de la clause INTO pour identifier les variables PL/SQL qui doivent recevoir des valeurs des colonnes des tables dune base de donnes. DECLARE v_col1 v_col2 v_coln BEGIN SELECT {*|col1,col2, .coln} INTO v_col1, v_col2, v_coln FROM table1, table2 , WHERE condition

OUELBANI FOUED

SGBD

2008-2009

29

INSTRUCTION SELECT DANS PL/SQL

EXEMPLES DECLARE v_emp emp%ROWTYPE; BEGIN dbms_output.enable; Select * into v_emp From emp where empno = '7782'; dbms_output.put_line('Nom employ :' || v_emp.ename || chr(10)|| 'Fonction : ' || v_emp.job || chr(10)|| 'Departement : '|| v_emp.deptno || chr(10)|| 'Date recrutement : '|| to_char(v_emp.hiredate, 'dd/mm/yyyy') || chr(10) || 'Salaire : ' || v_emp.sal ); END; /
OUELBANI FOUED SGBD 2008-2009

30

INSTRUCTION SELECT DANS PL/SQL

EXEMPLES Declare salaire_moy [Link]%type; Begin dbms_output.enable; Select avg(sal) into salaire_moy From emp Where deptno=10; dbms_output.put_line('Le salaire moyen des employs du dpartement 10 est : ' || to_char(salaire_moy ,'99999.99')); End ; /

OUELBANI FOUED

SGBD

2008-2009

31

DECLARATION DES PROCEDURES

PROCEDURE nom_procedure [arg1,..,argn] IS [dclaration des variables locales] BEGIN Secion excutable [section exception] END [nom_procedure]; DROP PROCEDURE nom_procedure;
ARGUMENTS DE PROCEDURE
IN : sous-programme ne retourne aucune valeur au programme appelant OUT : le sous-programme retourne une valeur au sous-programme appelant. Il nest pas possible de se rfrer largument mais de lui affecter une valeur seulement IN OUT : sous-programme retourne une valeur au sous programme appelant. dans sous-programme il est possible de se rfrer largument.

OUELBANI FOUED

SGBD

2008-2009

32

DECLARATION DES PROCEDURES

EXEMPLES
DECLARE a number ; b number ; c number;

Procedure Proc1(a IN number,b OUT number, c IN OUT number) IS Begin b:=a; c:=a*10; End;
BEGIN dbms_output.enable; a:= 3.14159 ; dbms_output.put_line('valeurs avant'); dbms_output.put_line('a :' || a || chr(10) ||'b :' || b || chr(10) || c :' || c); Proc1(a, b, c); dbms_output.put_line('valeurs aprs ' ); dbms_output.put_line('a : ' || a || chr(10)||'b :' || b || chr(10) || 'c :' || c); END ; /

OUELBANI FOUED

SGBD

2008-2009

33

DECLARATION DES FONCTIONS

FUNCTION nom_fonction [arg1,arg2,,argn)] RETURN type_donnes_fonction IS [dclaration des variables locales] BEGIN Section_executable [section exception] END [nom_fonction] ; DROP FUNCTION nom_fonction;

OUELBANI FOUED

SGBD

2008-2009

34

DECLARATION DES FONCTIONS

EXEMPLES
Declare v_nbemp number; Function nbemp_dep(v_deptno in number) Return number IS v_nb number ; Begin select count(empno) into v_nb from emp where deptno=v_deptno; return v_nb; end; begin v_nbemp := nbemp_dep(30); dbms_output.put_line(v_nbemp); END; /

OUELBANI FOUED

SGBD

2008-2009

35

DECLARATION DUN CURSEUR

DECALRE CURSOR nom_curseur IS instruction select; EXEMPLES

DECLARE CURSOR emp_curseur IS SELECT empno, ename FROM emp; CURSOR dept_curseur IS SELECT * FROM dept ORDER BY deptno;

OUELBANI FOUED

SGBD

2008-2009

36

OUVERTURE DUN CURSEUR

OPEN nom_curseur; Ouvre le curseur pour excuter la requte et identifier lensemble des lignes. Si la requte ne retourne pas de ligne, aucune exception nest signale. Utilisez les attributs du curseur pour tester le rsultat aprs laffectation; EXEMPLE
DECLARE CURSOR emp_curseur IS SELECT empno, ename FROM emp; BEGIN OPEN emp_curseur; END; /

OUELBANI FOUED

SGBD

2008-2009

37

RAMENER LES DONNEES A PARTIR DUN CURSEUR

FETCH nom_curseur INTO [variable1, variable2, | record_name]; Chargez les valeurs de la ligne courante dans des variables de sortie Prvoir le mme nombre de variables Testez si le curseur contient des lignes

OUELBANI FOUED

SGBD

2008-2009

38

RAMENER LES DONNEES A PARTIR DUN CURSEUR

EXEMPLE
DECLARE v_empno [Link]%type; v_ename [Link]%type; CURSOR emp_curseur IS SELECT empno, ename FROM emp; BEGIN IF NOT emp_curseur%ISOPEN THEN OPEN emp_curseur; END IF; FETCH emp_curseur INTO v_empno, v_ename; WHILE emp_curseur%FOUND LOOP FETCH emp_curseur INTO v_empno, v_ename; END LOOP; CLOSE emp_curseur; END; /

OUELBANI FOUED

SGBD

2008-2009

39

FERMER UN CURSEUR
CLOSE nom_curseur; Chargez les valeurs de la ligne courante dans des variables de sortie Prvoir le mme nombre de variables Testez si le curseur contient des lignes EXEMPLE
DECLARE CURSOR emp_curseur IS SELECT empno, ename FROM emp; BEGIN OPEN emp_curseur; . CLOSE emp_curseur; END; /

OUELBANI FOUED

SGBD

2008-2009

40

ATTRIBUTS DUN CURSEUR

Attribut Type Description

%ISOPEN

Boolean

Evalu TRUE si le curseur est ouvert

%NOTFOUND

Boolean

Evalu TRUE si le dernier fetch na pas retourn de ligne

%FOUND

Boolean

Evalu TRUE si le dernier FETCH a retourn une ligne complment de %NOTFOUND

%ROWCOUNT

Number

Contient le nombre total de lignes retournes jusquici

OUELBANI FOUED

SGBD

2008-2009

41

LATTRIBUT %ISOPEN
Utilisez lattribut du curseur : %ISOPEN avant de raliser une affectation, pour tester si le curseur est ouvert. EXEMPLE
DECLARE CURSOR emp_curseur IS SELECT empno, ename FROM emp; BEGIN IF NOT emp_curseur%ISOPEN THEN OPEN emp_curseur; END IF; END; /

OUELBANI FOUED

SGBD

2008-2009

42

LATTRIBUT %NOTFOUND / %FOUND

Utilisez lattribut du curseur : %FOUND pour tester si le dernier FETCH a retourn une ligne. Utilisez lattribut du curseur : %NOTFOUND pour tester si le dernier FETCH na pas retourn une ligne. EXEMPLE
DECLARE v_empno [Link]%type; v_ename [Link]%type; CURSOR emp_curseur IS SELECT empno, ename FROM emp; BEGIN IF NOT emp_curseur%ISOPEN THEN OPEN emp_curseur; END IF FETCH emp_curseur INTO v_empno, v_ename; WHILE emp_curseur%FOUND END; /

OUELBANI FOUED

SGBD

2008-2009

43

LATTRIBUT %ROWCOUNT
Utilisez lattribut du curseur : %FOUND pour tester si le dernier FETCH a retourn une ligne. Utilisez lattribut du curseur : %NOTFOUND pour tester si le dernier FETCH na pas retourn une ligne.

OUELBANI FOUED

SGBD

2008-2009

44

LATTRIBUT %ROWCOUNT
EXEMPLE
DECLARE CURSOR emp_curseur IS SELECT empno, ename FROM emp; v_empno [Link]%type; v_ename [Link]%type; BEGIN IF NOT emp_curseur%ISOPEN THEN OPEN emp_curseur; END IF; FETCH emp_curseur INTO v_empno, v_ename; WHILE emp_curseur%FOUND LOOP dbms_output.put_line(emp_curseur%rowcount); FETCH emp_curseur INTO v_empno, v_ename; END LOOP; CLOSE emp_curseur; END; /

OUELBANI FOUED

SGBD

2008-2009

45

CURSEURS ET RECORDS
Utilisation dun Record pour rcuprer une ligne retourne par FETCH EXEMPLE
DECLARE CURSOR emp_curseur IS SELECT empno, ename FROM emp; emp_record emp_curseur%ROWTYPE; BEGIN OPEN emp_curseur; FETCH emp_curseur INTO emp_record; WHILE emp_curseur%FOUND LOOP FETCH emp_curseur INTO emp_record; END LOOP; CLOSE emp_curseur; END; /

OUELBANI FOUED

SGBD

2008-2009

46

CURSEUR DANS UNE BOUCLE FOR

FOR nom_record IN nom_Curseur LOOP instruction1; instruction1; instruction1; END LOOP; Simplification dcriture pour les curseurs explicites Ouverture, fetch et implicite du curseur Le record est dclar implicitement

OUELBANI FOUED

SGBD

2008-2009

47

CURSEUR DANS UNE BOUCLE FOR

EXEMPLE
DECLARE CURSOR emp_curseur IS SELECT empno, ename FROM emp; emp_record emp_curseur%ROWTYPE; BEGIN FOR emp_record IN emp_curseur LOOP -- Un open et un fetch implicites ont eu lieu dbms_output.put_line(emp_record.ename || || emp_record.ename ); END LOOP; -- Un close implicite a eu lieu END; /
OUELBANI FOUED SGBD 2008-2009

48

CURSEUR DANS UNE BOUCLE FOR EN UTILISANT DES SOUS-REQUETES

FOR nom_record IN (SELECT ename, deptno FROM emp) LOOP instruction1; instruction1; instruction1; END LOOP;

EXEMPLE
BEGIN FOR emp_record IN (SELECT ename, empno FROM emp) LOOP dbms_output.put_line(emp_record.ename || || emp_record.ename ); END LOOP; END; /

OUELBANI FOUED

SGBD

2008-2009

49

CURSEURS PARAMETRES
CURSOR nom_CURSEUR [(param1 type_donne, param1 type_donne, ) ] IS SELECT .; Transmet les paramtres au curseur lors de son ouverture et lexcution de la requte (open cursor) Permet dexcuter plusieurs fois un curseur explicite, avec un paramtrage diffrent chaque fois.

OUELBANI FOUED

SGBD

2008-2009

50

CURSEURS PARAMETRES

EXEMPLE
DECLARE CURSOR emp_curseur (v_deptno number, v_job varchar2) IS SELECT empno, ename FROM emp WHERE deptno=v_deptno and job=v_job; BEGIN OPEN emp_curseur(10,CLERK); .. END; /

OUELBANI FOUED

SGBD

2008-2009

51

CURSEURS FOR UPDATE

EXEMPLE
Declare Cursor c1 is select last_name, salary from employees for update of salary ; Begin For rec in c1 Loop If [Link] > 1500 then update employees set salary = salary * 1.2 where current of c1 ; end if; End loop; end;

OUELBANI FOUED

SGBD

2008-2009

52

OUVERTURE DUN CURSEUR SQL incluant l'ordre select

declare type ref_cur is ref cursor ; c_emp ref_cur; begin open c_emp for requte select ; end; EXEMPLE declare type rc is ref cursor ; c_emp rc; begin open c_emp for select * from employees;
End; /

OUELBANI FOUED

SGBD

2008-2009

53

OUVERTURE DE PLUSIEURS REQUETES EN UTILISANT LA MEME VARIABLE CURSEUR

DECLARE TYPE rc is REF CURSOR; vrc rc; vemp employees%ROWTYPE; vdep departments%rowtype; BEGIN OPEN vrc FOR SELECT * from employees; LOOP FETCH vrc INTO vemp; EXIT WHEN vrc%NOTFOUND; dbms_output.put_line(to_char(vemp.employee_id)||' '|| vemp.first_name); END LOOP; dbms_output.put_line('============================================'); OPEN vrc FOR SELECT * fROM departments; LOOP FETCH vrc INTO vdep; EXIT WHEN vrc%NOTFOUND; dbms_output.put_line(vdep.department_name); END LOOP; CLOSE vrc; END;

OUELBANI FOUED

SGBD

2008-2009

54

PROCEDURES ET FONCTIONS STOCKEES

Une procdure ou une fonction stocke est un sous-programme PL/SQL qui est conserv dans une base de donnes ORACLE et appel par un utilisateur, directement ou indirectement. AVANTAGES Efficacit : minimiser lappel des requtes SQL ct client en les remplaant par des appels de procdures ct serveur intgrant plusieurs instructions SQL. Rutilisabilit : une procdure ou fonction stocke peut tre utilise dans diverses situations (SQL,dclencheur,application) Portabilit : une procdure stocke est indpendante de la version systme dexploitation ou des compilateurs. Maintenabilit : en appelant la mme procdure partir de plusieurs outils (SQL*PLUS, application, autre procdure stocke) on rduit le cot de maintenance de cette procdure centralise.

OUELBANI FOUED

SGBD

2008-2009

55

PROCEDURES ET FONCTIONS STOCKEES

REMARQUE Il faut faire la diffrence entre la procdure dclare (cas prsent) qui est une procdure temporaire lie lexcution du bloc anonyme et la procdure stocke cre avec lordre CREATE PROCEDURE et qui est permanente et accessible par nimporte quel script SQL ou PLSQL ou un dclencheur.

OUELBANI FOUED

SGBD

2008-2009

56

PROCEDURES STOCKEES
CREATE [OR REPLACE] PROCEDURE nom_procedure [(argument1,[argumentN)] IS [dclaration_variables_locales] BEGIN Section excutable [section exception] END [nom_procdure]

OUELBANI FOUED

SGBD

2008-2009

57

FONCTIONS STOCKEES
CREATE [OR REPLACE] FUNCTION nom_fonction [(argument1,[argumentN)] RETURN type_donnes_fonction IS [dclaration des variables locales] BEGIN Section_exccutable [section_exception] RETURN valeur_fonction END [nom_fonction] ;

OUELBANI FOUED

SGBD

2008-2009

58

FONCTIONS STOCKEES
EXEMPLE

Cration dune fonction qui permet de retourner le revenu annuel dun employ selon la formule suivante : Si la commission > 0 : (salaire + commission) * 12 Sinon : salaire * 13
Create or replace function fn_revenu_annuel (v_empno in number) Return number is v_sal_annuel number; begin select ((sal * 12) + decode(nvl(comm,0),0,sal,comm*12)) into v_sal_annuel from emp where (empno = arg_empno); return v_sal_annuel; end;

APPEL DE FONCTION
OUELBANI FOUED

select empno, ename, sal, comm, fn_revenu_annuel(empno) from emp; SGBD 2008-2009

59

CREATION DE TRIGGERS
CREATE [OR REPLACE] TRIGGER nom_trigger {BEFORE|AFTER|INSTEAD OF} {INSERT |UPDATE [ OF nom_colonne [, nom_colonneN ] |DELETE} OR {INSERT |UPDATE [ OF nom_colonne [, nom_colonneN ] |DELETE} REFERENCING {[OLD [AS] ancien] | [NEW [AS] nouveau]}] ON nom_table for each row When (condition) BEGIN

END; /

OUELBANI FOUED

SGBD

2008-2009

60

DESCRIPTION DUN TRIGGER

Evnement : INSERT DELETE UPDATE UPDATE [ OF nom_colonne, ] Type : Ligne FOR EACH ROW Global (appliqu 1seule fois au niveau table) Squencement : BEFORE AFTER INSTEAD OF (Vue seulement) ON : Table Vue WHEN (Condition(s)) : Validation des contraintes

OUELBANI FOUED

SGBD

2008-2009

61

EXEMPLES DE TRIGGERS
-------------Cration des tables T11 et T12

create table T11(a int , b varchar(10)) create table T12(a int , b varchar(10))
-------------Cration de triggers globaux ie s'appliquant une seule fois au niveau table

create or replace trigger trig_before_insert_global before insert on T11 begin dbms_output.put_line('Dbut d''insertion'); end; create or replace trigger trig_after_insert_global after insert on T11 begin dbms_output.put_line('Fin d''insertion'); end;
----------------Cration de triggers lignes

create or replace trigger trig_before_insert before insert on T11 For each row begin :New.a:=:New.a +100; end;

OUELBANI FOUED

SGBD

2008-2009

62

EXEMPLES DE TRIGGERS
-----------------Alimentation de la table T12

begin for i in 1.. 10 loop insert into T12 values (i, 'a'||i); end loop; End;
-----------------Alimentation de la table T11 partir de la table T12

begin insert into T11 select * from T12; end;

-----------------Alimentation de la table T11

begin for i in 1.. 10 loop insert into T11 values (i*10, 'b'||10*i); end loop; End;
OUELBANI FOUED SGBD 2008-2009

63

PREDICATS
PREDICATS : INSERTING DELETING UPDATING
EXEMPLE CREATE OR REPLACE TRIGGER TRIG_dep BEFORE INSERT OR UPDATE OR DELETE ON DEPARTMENTS BEGIN
IF INSERTING THEN dbms_output.put_line(Insertion); END IF; IF UPDATING THEN dbms_output.put_line(Modification) ; END IF; IF DELETING THEN dbms_output.put_line(Suppression); END IF;

END ;

OUELBANI FOUED

SGBD

2008-2009

64

PREDICATS
EXEMPLE
CREATE OR REPLACE TRIGGER trig_ins_maj_supp_dep BEFORE INSERT OR UPDATE OR DELETE ON DEPARTMENTS for each row BEGIN IF INSERTING THEN dbms_output.put_line('Insertion'); END IF; IF UPDATING THEN dbms_output.put_line('Modification'); END IF; IF DELETING THEN dbms_output.put_line('Suppression'); END IF; END; UPDATE departments set department_name = lower(substr(department_name,1,1)) || substr(department_name,2,length(department_name)-1)

OUELBANI FOUED

SGBD

2008-2009

65

EXEMPLES DE TRIGGERS
create or replace trigger trig_before_ligne before insert on T11 for each row when (new.a > 10) begin RAISE_APPLICATION_ERROR (-20001, 'Numro > 10'); end;
insert into T11 values (11,'b11') Au moment de lexcution

ORA-20001: Numro > 10 ORA-06512: "HR.TRIG_BEFORE_LIGNE", ligne 2 ORA-04088: erreur lors d'excution du dclencheur 'HR.TRIG_BEFORE_LIGNE'

00

OUELBANI FOUED

SGBD

2008-2009

66

:OLD - :NEW
Dans un trigger ligne, on doit pouvoir accder aux anciennes et nouvelles valeurs de colonnes de la ligne. Les noms permettent de dsigner ces deux valeurs sont : :New contient les nouvelles valeurs. :OLD contient les anciennes valeurs
:NEW :OLD

INSERT
UPDATE DELETE

Nouvelle valeur
Nouvelle valeur Null

Null
Ancienne valeur Ancienne valeur

OUELBANI FOUED

SGBD

2008-2009

67

:OLD - :NEW
EXEMPLE
create or replace trigger trig1_ins before insert on trig1 for each row begin :New.C1 := :New.C1+100; end; /

OUELBANI FOUED

SGBD

2008-2009

68

GESTION DES TRIGGERS

Cration, excution CREATE TRIGGER ALTER TRIGGER DROP TRIGGER Information sur les triggers dans les tables USER_TRIGGERS ALL_TRIGGERS DBA_TRIGGERS

OUELBANI FOUED

SGBD

2008-2009

69

GESTION DES EXCEPTIONS

Dans PL/SQL les exceptions sont traites dans la section EXCEPTION Exceptions Prdfinies : ex NO_DATA_FOUND ,TOO_MANY_ROWs Exceptions dfinies par lutilisateur Dclaration dune exception Declare

exception1 EXCEPTION
Leve dune exception RAISE exception1 Traitement de lexception dans la section Exception EXCEPTION WHEN <exception1> [OR <exception2> OR ...] THEN <instructions> WHEN <exception3> [OR <exception2> OR ...] THEN <instructions> WHEN OTHERS THEN <instructions> END; OUELBANI FOUED SGBD 2008-2009

70

GESTION EXCEPTIONS UTILISATEUR

DECLARE nom_erreur EXCEPTION ; BEGIN IF (anomalie) THEN RAISE nom_erreur ; EXCEPTION WHEN nom_erreur THEN (traitement) ;

OUELBANI FOUED

SGBD

2008-2009

71

GESTION EXCEPTIONS ORACLE

DECLARE nom_erreur EXCEPTION ; PRAGMA EXCEPTION_INIT(nom_erreur, code_erreur); BEGIN Ds que l'erreur Oracle est rencontre, passage automatique la section EXCEPTION pour raliser le traitement appropri EXCEPTION WHEN nom_erreur THEN (traitement) ; [WHEN OTHERS THEN (traitement) ;] ;

OUELBANI FOUED

SGBD

2008-2009

72

CREATION DES PACKAGES

Package = ensemble nomm d'objets associs TYPES VARIABLES FONCTIONS Stockes PROCEDURES stockes Package comporte deux parties Une partie SPECIFICATION visible depuis les applications externes Une partie CORPS invisible depuis les applications externes

OUELBANI FOUED

SGBD

2008-2009

73

CREATION DES PACKAGES

CREATE OR REPLACE package Nom_Package is function Nom_Fonction1 RETURN Type_de_retour; function Nom_Fonction2(parametre IN typeParametre) RETURN Type_de_retour; procedure Nom_Procedure1; procedure Nom_Procedure2 (parametre IN typeParametre); end Nom_Package;

OUELBANI FOUED

SGBD

2008-2009

74

CREATION DES PACKAGES

CREATE OR REPLACE package body Nom_Package as function Nom_Fonction1 RETURN Type_de_retour DECLARE BEGIN . END Nom_Fonction ; END Nom_Package; / L'excution d'une procdure d'un package s'effectue par la commande EXECUTE nom_package.nom_procdure(liste_paramtres effectifs) DROP PACKAGE package_name; DROP PACKAGE BODY package_name;

OUELBANI FOUED

SGBD

2008-2009

75

CREATION DES PACKAGES

EXEMPLES CREATE OR REPLACE PACKAGE over_pack IS PROCEDURE add_dept (p_deptno IN departments.department_id%TYPE,

p_name IN departments.department_name%TYPE DEFAULT 'unknown', p_loc IN departments.location_id%TYPE DEFAULT 0);

PROCEDURE add_dept (p_name IN departments.department_name%TYPE DEFAULT 'unknown', p_loc IN departments.location_id%TYPE DEFAULT 0); END over_pack;

OUELBANI FOUED

SGBD

2008-2009

76

CREATION DES PACKAGES EXEMPLE

CREATE OR REPLACE PACKAGE BODY over_pack IS PROCEDURE add_dept (p_deptno IN departments.department_id%TYPE, p_name IN departments.department_name%TYPE DEFAULT 'unknown', p_loc IN departments.location_id%TYPE DEFAULT 0) IS BEGIN INSERT INTO departments (department_id, VALUES (p_deptno, p_name, p_loc); END add_dept; PROCEDURE add_dept (p_name IN departments.department_name%TYPE DEFAULT 'unknown', p_loc IN departments.location_id%TYPE DEFAULT 0) IS BEGIN INSERT INTO departments (department_id,department_name, location_id) VALUES (departments_seq.NEXTVAL, p_name, p_loc); END add_dept; END over_pack;

OUELBANI FOUED

SGBD

2008-2009

77