Professeur :

M QBADOU
Professeur [Link]

ORACLE PL/SQL

Sommaire
1. Introduction PL/SQL

Les avantages de PL/SQL

Architecture de PL/SQL
Structures de Bloc PL/SQL

7. Exceptions

8. Transactions
9. Dclencheurs (triggers)

2. Variables
3. structures de contrle
4. Curseurs

Principes dutilisation des triggers

Exemples
Cration et gestion des triggers
Conditions de dclenchement

Curseurs implicites/Explicites
Curseurs paramtrs

5. Sous-programmes

Procdures et Fonctions

Paquetages (packages)

Professeur [Link]

ORACLE PL/SQL

Objectifs
Le cours a pour objectifs :
Identifier les diffrentes parties dun bloc PL/SQL
Spcifier et dclarer des variables PL/SQL, utiliser les types PL/SQL, et
en particulier les types implicites (%type, %rowtype)
Ecrire des programmes stocks en PL/SQL : procdures, fonctions,
paquetages
Comprendre et Manipuler des curseurs (Cursor)

Intgrer des requtes select dans un programme PL/SQL

Intgrer une requte delete, update, insert en PL/SQL
Programmer en utilisant les exceptions (dclenchement, traitement,
dclaration dexceptions).
Excuter un bloc PL/SQL (Avec ou sans accs la base)
Professeur [Link]

ORACLE PL/SQL

Introduction PL/SQL
PL/SQL cest quoi
PL/SQL : Procedural Language / Structured Query Language
Langage procdural qui reprsente une extension SQL

Bien intgrer au serveur Oracle, il permet de manipuler toutes les

donnes Oracle :
relationnelles,
Relationnelles-Objets,
Java

Permet dexprimer facilement des rgles de gestion complexes sous

forme de : Procdures, Fonctions, Triggers stocks

Professeur [Link]

ORACLE PL/SQL

Introduction PL/SQL
Avantages de PL/SQL
Intgration parfaite de l SQL ce qui permet PL/SQL de raliser
des traitements complexes sur les donnes de la base
Modulaire et Support bien la POO puissance de la POO
Offre une bonne performance : transmission dun bloc de code au
moteur de base de donnes
rduit le trafic entre les applications et le serveur
diminue les changes rseau

augmente les performances globales des applications

Portabilit : Toutes bases oracle comporte un moteur PL/SQL

Intgration Java : possibilit dutiliser Java (wrappers Java)
Robuste : traitement des erreurs (gestion des exceptions)
Professeur [Link]

ORACLE PL/SQL

Introduction PL/SQL
Le PL/SQL peut tre utilis sous 3 formes :
Un bloc de code, excut comme une commande SQL, via un
interprteur standard (SQL+ ou iSQL*Plus, SQL Developer).

Un fichier de commande PL/SQL.

Un programme stock dans la base(procdure, fonction, package
ou trigger).

Un programmes non stock dans la base mais incorpor dans une

application (Oracle FORMS DEVELOPER et Oracle REPORT
DEVELOPER), dit programme applicatif

Traitement dun bloc PL/SQL :

Un bloc PL/SQL est intgralement envoy au moteur PL/SQL, qui
traite chaque instruction PL/SQL et sous- traite les instructions
purement SQL avec le moteur SQL rduction du trafic rseau
Professeur [Link]

ORACLE PL/SQL

Introduction PL/SQL
Architecture de PL/SQL
Moteur PL/SQL

Bloc
PL/SQL

Procdural
SQL

Instructions
Procdurales

Data

Bloc
PL/SQL

Instructions SQL

Moteur SQL
Serveur Oracle
Professeur [Link]

ORACLE PL/SQL

Introduction PL/SQL
Type de bloc PL/SQL
Anonyme
Bloc sans nom

Procdure & Fonction

Blocs nomms et paramtrs, puis invoqus
Les Triggers sont invoqus implicitement

PLSP
Equivalent en PL des ASP ou JSP. Ddi aux applications pour
Internet ou la tlphonie mobile.
Professeur [Link]

ORACLE PL/SQL

Introduction PL/SQL
Types de bloc PL/SQL
Les types de Blocs sont:

[ DECLARE ]
BEGIN
--Instructions;

PROCEDURE nom
IS
BEGIN
--Instructions;

[ EXCEPTION ]

[ EXCEPTION ]

END;

END;

Professeur [Link]

Fonction

Procdure

Anonyme

ORACLE PL/SQL

FUNCTION nom
RETURN datatype
IS
BEGIN
--Instructions;
RETURN val;
[ EXCEPTION ]

END;

Introduction PL/SQL
Structure dun bloc PL/SQL
PL/SQL est un langage structur en blocs, constitus densemble
dinstructions.

Les instructions regroupes dans un BLOC, ne gnrent quun seul

accs la base de donnes.
Chaque Bloc PL/SQL peut tre constitu de :

Une section
Dclarative:
facultative, de
dclaration et
initialisation de type,
variables et constantes.

Professeur [Link]

Une section
excutable:
obligatoire,
contenant les
instructions
dexcutions.

ORACLE PL/SQL

Une section
dexception:
facultative, pour la
gestion derreurs.

10

Introduction PL/SQL
Structure dun bloc PL/SQL
Bloc PL/SQL
[ DECLARE
--Dclaration et initialisation des variables, curseurs,
exceptions, fonctions

BEGIN
--Instructions Excutables (sql ou pl/sql)

[ EXCEPTION
--Interception des erreurs (actions raliser
lorsquune erreur se produit

]
END;

Professeur [Link]

ORACLE PL/SQL

11

Introduction PL/SQL
Structure dun bloc PL/SQL minimum
Bloc PL/SQL
--Dbut de la section des instructions excutables

BEGIN
NULL; --Aucune Action
--La fin de la section des instructions excutables

END;
Professeur [Link]

ORACLE PL/SQL

12

Introduction PL/SQL
Exemple de bloc PL/SQL
Set ServerOutput on
DECLARE
v_variable1
NUMBER;
v_variable2
NUMBER;
v_variable3
NUMBER;
excep1
EXCEPTION;
BEGIN
IF (v_variable2 <> 0) THEN
v_variable3 := v_variable1/v_variable2;
ELSE
RAISE excep1;
END IF;
EXCEPTION
WHEN excep1 THEN
DBMS_OUTPUT.PUT_LINE('Erreur');
END;
Professeur [Link]

ORACLE PL/SQL

13

Package DBMS_OUTPUT
Procdures du Package DBMS_OUTPUT
Doit tre autorise sous SQL*PLUS laide de:
SET SERVEROUTPUT ON
Procdure

Action

GET_LINE

GET_LINE(ligne OUT, statut OUT) extrait une ligne du

tampon de sortie

GET_LINES

GET_LINES(lignes OUT, n OUT) extrait n lignes

NEW_LINE

Affiche la ligne gnre par PUT et place un marqueur de

ligne

PUT

PUT(variable | constante) place la valeur dans le tampon

PUT_LINE

Place la valeur dans le tampon et place un marqueur de

ligne

ENABLE

ENABLE(taille du tampon)

DISABLE

Dsactive le mode trace

Professeur [Link]

ORACLE PL/SQL

14

La section Dclarative Variable et Constante

Rle de la section dclarative
Les variables

Classification

Utilisation

Variables PL/SQL

Typage dynamique

Les variables Non PL/SQL

Exemples
Professeur [Link]

ORACLE PL/SQL

15

La section Dclarative Variable et Constante

Rle de la section Dclarative
Elle est facultative, dbute par le mot cl: DECLARE.

Elle contient toutes les dclarations des variables et leur ventuelle

initialisation. Ces variables sont utilises localement dans la section
excutable,
Cette section ne peut pas contenir dinstructions excutables
Cette section peut contenir des procdures ou des fonctions
intgres.

Professeur [Link]

ORACLE PL/SQL

16

La section Dclarative Variable et Constante

Variables Classification de variables
Variables PL/SQL:
Scalaires (colonnes dune table)

Composes (RECORD, TABLE)

Rfrences (Pointeurs ou REF)

LOB: contiennent des valeurs appeles LOCATORS qui spcifient

lemplacement de Large Objects (Images).
COLLECTION

Des variables non-PL/SQL

BIND variables ( variables htes , variables lies)

Professeur [Link]

ORACLE PL/SQL

17

La section Dclarative Variable et Constante

Utilisation des variables

On utilise des variables pour :

Le stockage temporaire de donnes

La manipulation de valeur stockes

La possibilit de les rutiliser

Simplifier la maintenance du code

Variable type dynamiquement au moyen

dattributs spciaux %ROWTYPE ou %TYPE

Professeur [Link]

ORACLE PL/SQL

18

La section Dclarative Variable et Constante

Variables PL/SQL
Dclaration de Variables et Constantes
nom_var [CONSTANT] Type[NOT NULL][:=|DEFAULT expr];

Adopter des conventions pour nommer des objets.

Initialiser les constantes et les variables dclares NOT NULL.
Initialiser les identifiants en utilisant l'oprateur d'affectation ( := )
ou le mot rserv DEFAULT.

Dclarer au plus un identifiant par ligne.

Le type peut tre primitif ou objet.
Toute variable doit tre dclare avant dtre utilise.

Professeur [Link]

ORACLE PL/SQL

19

La section Dclarative Variable et Constante

Quelques conventions en PL/SQL
Deux variables peuvent partager le mme nom si elles sont dans
des portes distinctes

Les noms des variables doivent tre diffrents des noms des
colonnes des tables utilises dans un bloc
v_empno (variable)

g_deptno (globale)
c_emp (CURSOR)

Lidentifiant est limit 30 caractres,

Le premier caractre doit tre une lettre.

Professeur [Link]

ORACLE PL/SQL

20

La section Dclarative Variable et Constante

Type de donnes scalaires de base
VARCHAR2(TailleMax) : Taille variable jusqu 32767 octets

NUMBER[(precision,echelle)] : Numrique virgule flottante

DATE : -4712 avant J.C. +9999 aprs J.C.
CHAR[(TailleMax)]
LONG : 2 147 483 647 octets
LONG RAW : Binaire jusqu 32 760 octets

BOOLEAN : True, False ou NULL

BINARY_INTEGER : Entier signs compris entre -/+2 147 483 647
PLS_INTEGER : Entier signs compris entre -/+2 147 483 647

Professeur [Link]

ORACLE PL/SQL

21

Section Dclarative Variable et Constante

Types composs
contient des composants internes.
Exemple : Type RECORD, TABLE, NESTED TABLE, VARRAY.

Type LOB:
Il s'agit d'un localisateur spcifiant la localisation dobjets de grande
taille (des images, des vidos, )
BFILE: stocke la rfrence dun fichier du systme dexploitation.
BLOB: permet de stocker un objet binaire jusqu 4 GO.
CLOB: Pour stocker un ensemble de caractres, jusqu 4 GO.
NCLOB: Pour stocker un ensemble de caractres, cods sur un ou
plusieurs octets, jusqu 4 GO.

Professeur [Link]

ORACLE PL/SQL

22

Section Dclarative Variable et Constante

Types dynamique %Type :
Une variable peut tre dclare de mme type quune variable dj
dclare ou dune colonne de la table sur laquelle on travaille en
utilisant lattribut : %Type :
nomVar
[Link]%Type;
nomVar
nomVariableConnue%Type;
Avantage :
Garantit la compatibilit de type, et facilite la maintenance.
Exemples:

V_nombre1
V_nombre2
V_sal
Professeur [Link]

NUMBER(7,2);
V_nombre1%type := 10;

[Link]%type;
ORACLE PL/SQL

23

Section Dclarative Variable et Constante

Les attributs %TYPE et %ROWTYPE - Exemple
DECLARE
ename
job
balance
mini_balance
rec

[Link]%TYPE;
[Link]%TYPE;
NUMBER( 7, 2 );
balance%TYPE := 10;
emp%ROWTYPE

Le type de donnes de la colonne peut tre inconnu.

Le type de donnes de la colonne peut changer en excution.

Facilite la maintenance.

Professeur [Link]

ORACLE PL/SQL

24

Section Dclarative Variable et Constante

Attribut %TYPE & %ROWTYPE - Exemple
DECLARE
rec
emp%ROWTYPE;
address VARCHAR2(64);
income [Link]%TYPE; -- [Link]%TYPE;
BEGIN
SELECT * INTO rec FROM emp
WHERE ROWNUM = 1;
income := [Link]*12;
address := [Link] || CHR(10) ||
income || CHR(10) ||
TO_CHAR([Link],'DD/MM/YYYY');
DBMS_OUTPUT.PUT_LINE(address);
END;
/
SMITH
9600
17/12/1980
Professeur [Link]

ORACLE PL/SQL

25

Section Dclarative Variable et Constante

Dclarations - Exemple1
c
name
cpt
total
order
Ship
pi
done
ID
PRODUIT
V_date
V1
V2
V3
Ok
Professeur [Link]

CHAR( 1 );
VARCHAR2(10) := 'Scott';
BINARY_INTEGER := 0;
NUMBER( 9, 2 ) := 0;
DATE := SYSDATE + 7;
DATE;
CONSTANT NUMBER ( 3, 2 ) := 3.14;
BOOLEAN NOT NULL := TRUE;
NUMBER(3) NOT NULL := 201;
NUMBER(4) := 2*100;
DATE :=TO_DATE('17-OCT.-01','DD-MON-YY');
NUMBER := 10;
NUMBER := 20;
BOOLEAN := (v1>v2);
BOOLEAN := (z IS NOT NULL);
ORACLE PL/SQL

26

La section Dclarative Variable et Constante

Dclarations - Exemple2
SET SERVEROUTPUT ON
DECLARE
pi CONSTANT NUMBER := 4*ATAN(1);
BEGIN
DBMS_OUTPUT.PUT_LINE(pi);
END;
/
3.14159265358979323846264338327950288422

Professeur [Link]

ORACLE PL/SQL

27

La section Dclarative Variable et Constante

Les Variables non-PL/SQL: Utilisation
Les variables de substitution et les variables htes (bind variable)
sont des variables non PL/SQL. Elles sont dclares dans
lenvironnement hte pour passer des valeurs en entre ou en
sortie.
Pour faire rfrence des variables htes ou des variables de
substitution :
:host_variable := expression
&substitution_variable := expression
Pour les chanes de caractres , on doit utiliser les cotes :
'&substitution_variable' := expression

Professeur [Link]

ORACLE PL/SQL

28

La section Dclarative Variable et Constante

Les Variables non-PL/SQL: Dclaration
Pour dclarer une variable hte:
VARIABLE g_nomVariable typeVariable
Exemple :
VARIABLE g_chaine VARCHAR2(30))

DECLARE

Pour dclarer une variable de substitution:

ACCEPT g_nomVariable PROMPT 'Message'
Exemple :
ACCEPT g_salaire PROMPT 'Entrez le salaire : ')

DECLARE
.
/
Professeur [Link]

ORACLE PL/SQL

29

La section Dclarative Variable et Constante

Les Variables non-PL/SQL: Exemple

SQL> SELECT * FROM DEPT WHERE DEPTNO = '&1';

Entrez une valeur pour

1: 10

ancien

1: SELECT * FROM DEPT WHERE DEPTNO = '&1'

Nouveau

1: SELECT * FROM DEPT WHERE DEPTNO = '10'

DEPTNO DNAME

LOC

---------- -------------- ------------10 ACCOUNTING

Professeur [Link]

NEW YORK

ORACLE PL/SQL

30

La section Dclarative Variable et Constante

Les Variables non-PL/SQL: Exemple
SQL> VARIABLE x NUMBER

SQL> BEGIN
2

SELECT SUM(sal) INTO :x FROM EMP;

END;

PL/SQL procedure successfully completed.

SQL> PRINT x
X
---------29025
Pour afficher les variables globales :

SQL> VARIABLE
variable
x
datatype
NUMBER
Professeur [Link]

ORACLE PL/SQL

31

La section Dclarative Variable et Constante

Les Variables non-PL/SQL: Dclaration
SQL> VARIABLE x NUMBER
SQL> ACCEPT y PROMPT 'Enter a number : '
Enter a number : 1000
SQL> DECLARE
2
v NUMBER(9,2) := &y;
3 BEGIN
4 :x := v/12;
5* END;
old
2: v NUMBER(9,2) := &y;
new
2: v NUMBER(9,2) := 1000;
PL/SQL procedure successfully completed.
SQL> PRINT x
X
---------83.3333333
Professeur [Link]

ORACLE PL/SQL

32

La section Dclarative Variable et Constante

Passage de Valeurs un Fichier Script

Crer un fichier script incluant un ordre SELECT.

Utiliser la notation &chiffre dans lordre SELECT.

A lexcution du fichier spcifier la valeur du paramtre

sur la ligne de commande derrire le nom du fichier. La

position de chaque paramtre est significative.

SQL> START my_file value1 value2

Professeur [Link]

ORACLE PL/SQL

33

La section Dclarative Variable et Constante

Passage de Valeurs un Fichier Script

Exemple
SQL> SELECT * FROM DEPT WHERE DEPTNO = '&1';
SQL> SAVE ma_requete

SQL> START d:\plsql\\ma_requete.sql

20

old

1: SELECT * FROM DEPT WHERE DEPTNO = '&1'

new

1: SELECT * FROM DEPT WHERE DEPTNO = '20'

DEPTNO DNAME

LOC

---------- -------------- -------------

20 RESEARCH
Professeur [Link]

DALLAS

ORACLE PL/SQL

34

Section Excutable

Dfinition

Fonctions SQL et PL/SQL

Conversion implicite et explicite

Les oprateurs en PL/SQL

Blocs imbriqus et la porte des variables

Les instructions

Exemples

Professeur [Link]

ORACLE PL/SQL

35

Section Excutable
Dlimite par les mots cl BEGIN et END; elle contient :
les instructions d'excution du bloc PL/SQL,
les instructions de contrle et d'itration,
l'appel des procdures et fonctions, l'utilisation des fonctions
natives,
les ordres SQL, etc.
Chaque instruction doit tre suivi du terminateur d'instruction ; .

Syntaxe des blocs PL/SQL et Rgles suivre:

Les instructions peuvent stendre sur plusieurs lignes.
Les valeurs caractres ou dates doivent tre mises entre simples
cotes.
Les identifiants ne peuvent contenir de mots rservs moins
quil soient mis entre guillemets.
Professeur [Link]

ORACLE PL/SQL

36

Section Excutable
Fonctions SQL en PL/SQL:
Fonctions Disponibles:
Mono-ligne Numrique.
Comme
Mono-ligne Caractre.
en SQL
Conversion de type de donnes
Date
Fonctions non Disponibles :
Fonctions de groupe.
Exemples:
V_name := LOWER(v_ename);
V_date := TO_DATE( 01 Janvier 1999 , DD Month YYYY);
v_chaine := concat(Base, Donnes);
Commenter le code:
Commencer les commentaires sur une ligne avec deux tirets (--).
Placer les commentaires s tendant sur plusieurs lignes entre les
symboles /* et */.
Professeur [Link]

ORACLE PL/SQL

37

Section Excutable
La conversion de types de donnes:
est ralise:
Soit de manire implicite, par le systme. Les conversions
fonctionnent selon des rgles prcises.
Soit de manire explicite par lutilisateur. Elle se fait au moyen des
fonctions de conversion.
La conversion implicite:
Pour laffectation:

DE

VERS

VARCHAR2 ou CHAR

NUMBER

VARCHAR2 ou CHAR

DATE

NUMBER

VARCHAR2

DATE

VARCHAR2

Pour lvaluation dexpressions: DE

Professeur [Link]

VERS

VARCHAR2 ou CHAR

NUMBER

VARCHAR2 ou CHAR

DATE

ORACLE PL/SQL

38

Section Excutable
Oprateurs en PL/SQL:

Logiques
Arithmtiques
Concatnation
Parenthses pour contrler lordre des
oprations

Identiques
ceux de
SQL

Exemples:
Incrmenter lindex pour une boucle:
v_count := v_count+1;
Dfinir la valeur dun indicateur boolen:
v_equal := (v_n1 = v_n2);
Valider le numro dun employ sil contient une valeur:
v_valid := (v_empno IS NOT NULL);
Professeur [Link]

ORACLE PL/SQL

39

Section Excutable
Blocs imbriqus et porte des variables:

Des instructions peuvent tre imbriques partout ou une

instruction excutable est permise.

Un bloc imbriqu devient une instruction.

Une section dexceptions peut contenir des blocs imbriqus.

La porte dun objet est la zone du programme qui peut sy rfrer.

Un identifiant est visible dans les zones dans lesquelles vous

pouvez y faire rfrence:
Un bloc peut aller chercher dans le bloc qui le contient (PARENT).
Un bloc ne peut pas aller chercher dans les blocs quil contient
(ENFANT).

Professeur [Link]

ORACLE PL/SQL

40

Section Excutable
DECLARE
v_nombre NUMBER := 600;
v_message VARCHAR2(100):=Message Principal;
v_test VARCHAR2(30) := True;
BEGIN

Sous-Bloc

Bloc Principal

DECLARE
v_nombre NUMBER :=1;
v_message VARCHAR2(100):=Message second;
v_local VARCHAR2(100):=Message Interne;
BEGIN
v_nombre:=v_nombre+1;
v_local:=MSG1: ||v_local;
END;

v_nombre:=v_nombre+1;
v_message:=v_message||MSG2;
v_local:=MSG3||v_local;
END;
Professeur [Link]

ORACLE PL/SQL

41

Section Excutable
Les instructions :
Linstruction: Assignation (affectation)
Pour affecter une valeur une variable, on utilise :
Loprateur daffectation PL/SQL :=
Linstruction SELECTINTO ;
Linstruction FETCH (cette instruction sera vue avec plus de
dtails dans la partie ddie aux curseurs)
Exemple:
DECLARE
v_salaire NUMBER(4) ;
v_empno [Link]%TYPE ;
BEGIN
v_salaire := 1100;
SELECT empno INTO v_empno
FROM emp
WHERE sal := v_salaire;
END;
Professeur [Link]

ORACLE PL/SQL

42

Section Excutable
L instruction: Commit
Permet d'enregistrer dans la base toutes les modifications effectues
au cours de la transaction.
Un commentaire d'un maximum de 50 caractres peut apparatre
entre apostrophes derrire le mot cl COMMENT.
Syntaxe:
COMMIT [COMMENT 'Message'];

Professeur [Link]

ORACLE PL/SQL

43

Section Excutable
Linstruction: Exit:
Syntaxe:
Exit [label] WHEN expression_booleene;
Permet de quitter une structure itrative.
label facultatif permet de nommer prcisment la
structure de laquelle on veut sortir.
expression boolenne permet de spcifier une condition
de sortie.
Exit saute l'instruction suivant le mot cl END LOOP;
Dans le cas de boucles imbriques, l'indication d'un label permet
de quitter tout ou partie des boucles imbriques
Professeur [Link]

ORACLE PL/SQL

44

Section Excutable
Linstruction: IF ELSE END IF
Syntaxe:
IF exp_booleene
instructions ;
ELSIF exp_booleene
instructions ;
ELSE
instructions ;
END IF ;

THEN
THEN

Cette instruction permet de faire des tests conditionnels.

expression boolenne reprsente un test gnrant un
boolen TRUE ou FALSE

Seuls les mots cl IF et END IF; sont obligatoires. Les clauses

ELSIF et ELSE sont facultatives
Professeur [Link]

ORACLE PL/SQL

45

Section Excutable
Linstruction: CASE
Cette instruction, utilisable aussi dans les requtes SQL, permet de
mettre en place une structure slective :
Syntaxe:
CASE selecteur
WHEN val1 THEN
instruction;
WHEN val2 THEN
instruction;
...
ELSE
instruction;
END CASE;

oprateur peut tre de n'importe quel type PL/SQL l'exception des

objets suivants : BLOB, BFILE, Type objet, Enregistrement, Collection
(NESTED TABLE, INDEX-BY TABLE, VARRAY)
Professeur [Link]

ORACLE PL/SQL

46

Section Excutable
Linstruction: LOOP END LOOP;
Cette instructions met en place une boucle o aucune condition de sortie
n'est indique. linstruction EXIT permet de sortir de la boucle.
Syntaxe:
LOOP
instructions;

EXIT When condition;

END LOOP;
Exemple:
Declare
cpt integer := 0 ;
Begin
Loop

cpt := cpt + 1 ;
dbms_output.put_line( to_char( cpt) ) ;
exit when cpt > 2 ;
End loop ;
End ;
Professeur [Link]

ORACLE PL/SQL

47

Section Excutable
Linstruction: WHILE condition LOOP

END LOOP;

Cette syntaxe permet de mettre en place une boucle dont la condition de

test est value au dbut.
Syntaxe:
WHILE condition
LOOP
compteur := compteur+1;
instructions;
END LOOP;
Exemple: Declare
cpt integer := 0 ;
Begin
WHILE cpt < 3
Loop
cpt := cpt + 1 ;
dbms_output.put_line( to_char( cpt) ) ;
End loop ;

End ;
Professeur [Link]

ORACLE PL/SQL

48

Section Excutable
Linstruction: For IN LOOP END LOOP
Cette instruction permet de mettre en place une boucle dont le nombre
d'itrations est fix ds l'entre.
Syntaxe:
FOR variable_index IN [REVERSE] borne_dbut .. borne_fin
LOOP
instructions;
END LOOP;
Variable_index reprsente le nom de la variable qui servira d'indice.
Cette variable ne ncessite pas de dfinition pralable dans la section
dclarative
Reverse permet de faire varier l'indice dans le sens contraire
(dcrmentation)

Borne_dbut reprsente l'indice de dpart

Borne_fin reprsente l'indice de fin
Professeur [Link]

ORACLE PL/SQL

49

Section Excutable
Boucles Imbriques et Labels
Boucles imbriques des niveaux multiples
Utiliser des labels pour distinguer les blocs et les boucles

Quitter la boucle avec l'instruction EXIT rfrenant le label.

Professeur [Link]

ORACLE PL/SQL

50

Section Excutable
Boucles imbriques
BEGIN
FOR v_outerloopcounter IN 1..2 LOOP
FOR v_innerloopcounter IN 1..4 LOOP
DBMS_OUTPUT.PUT_LINE(
'Outer Loop counter is ' || v_outerloopcounter ||
' Inner Loop counter is ' || v_innerloopcounter);
END LOOP;
END LOOP;
END;
/
Outer
Outer
Outer
Outer
Outer
Outer
Outer
Outer

Loop
Loop
Loop
Loop
Loop
Loop
Loop
Loop

Professeur [Link]

counter
counter
counter
counter
counter
counter
counter
counter

is
is
is
is
is
is
is
is

1
1
1
1
2
2
2
2

Inner
Inner
Inner
Inner
Inner
Inner
Inner
Inner

Loop
Loop
Loop
Loop
Loop
Loop
Loop
Loop

ORACLE PL/SQL

counter
counter
counter
counter
counter
counter
counter
counter

is
is
is
is
is
is
is
is

1
2
3
4
1
2
3
4
51

Section Excutable
Exemple : Boucles Imbriques et Labels
Quitter la boucle extrieure daprs les valeurs d'un bloc interne :
BEGIN
<<outerloop>>
FOR v_outerloopcounter IN 1..2 LOOP
<<innerloop>>
FOR v_innerloopcounter IN 1..4 LOOP
DBMS_OUTPUT.PUT_LINE(
'Outer Loop counter is ' || v_outerloopcounter ||
' Inner Loop counter is ' || v_innerloopcounter);
EXIT outerloop WHEN v_innerloopcounter = 3;
END LOOP innerloop;
END LOOP outerloop;
END;
/
Outer Loop counter is 1 Inner Loop counter is 1
Outer Loop counter is 1 Inner Loop counter is 2
Outer Loop counter is 1 Inner Loop counter is 3
Professeur [Link]

ORACLE PL/SQL

52

Section Excutable
Linstruction: NULL
Cette instruction n'excute rien et n'a aucun effet.

Linstruction: RAISE
Cette instruction permet de gnrer une exception
RAISE nom_exception;
nom exception reprsente soit le nom d'une exception prdfinie, soit
une exception utilisateur dfinie dans la section dclarative

LInstruction: RETURN
Cette instruction permet de sortir d'une procdure ou d'une fonction
RETURN expression;

expression reprsente la valeur de retour d'une fonction. Cette valeur

doit tre compatible avec le type dfini dans la clause RETURN de la
dclaration de fonction
Professeur [Link]

ORACLE PL/SQL

53

Section Excutable
Linstruction: SAVEPOINT
Cette instruction permet de placer une tiquette savepoint dans le
corps du code.
Elle permet au traitement d'annuler, avec l'instruction ROLLBACK, les
modifications effectues partir de cette tiquette

Linstruction: ROLLBACK TO SAVEPOINT

Cette instruction permet d'annuler en base toutes les modifications
effectues au cours de la transaction
ROLLBACK TO SAVEPOINT nom_savepoint;
Nom_savepoint reprsente le nom d'une tiquette savepoint
pralablement dfinie dans le corps du code avec l'instruction :
SAVEPOINT nom_savepoint ;
Avec TO SAVEPOINT nom_savepoint, l'annulation porte sur toutes
les modifications effectues partir de l'tiquette nom_savepoint.
Professeur [Link]

ORACLE PL/SQL

54

Section Excutable
Instruction SQL
Reprsente toute instruction SQL valide.
INSERT
UPDATE
DELETE

Exemple:
Declare
v_message VARCHAR2(60):='Mise jour effectue';
Begin
update emp
set sal=5000
where empno=7200;
dbms_output.put_line( v_message ) ;
End ;
Professeur [Link]

ORACLE PL/SQL

55

Section Excutable
Linstruction: SELECT INTO From
Slection d'une ou de plusieurs lignes.
SELECT salaire INTO v_sal
FROM employee;
Cet ordre ne doit ramener qu'une ligne sous peine de gnrer l'exception
NO_DATA_FOUND si aucune ligne n'est ramene ou TOO_MANY_ROWS si
plus d'une ligne sont ramenes .
Utilise avec la clause BULK COLLECT, elle permet de charger une collection
avec les lignes ramenes.

Exemple:
Declare
type
type_tab_emp
IS TABLE OF emp%rowtype
INDEX BY pls_integer;
r_emp type_tab_emp;
BEGIN
select * bulk collect into r_emp from emp;
FOR i IN 1..r_emp.count LOOP
dbms_output.put_line( To_char( r_emp(i).empno ) || ' - ' || r_emp(i).ename ) ;
END LOOP ;
END;
Professeur [Link]

ORACLE PL/SQL

56

Section Excutable

Exemple 1 Select into

SET SERVEROUTPUT ON
DECLARE

v_name VARCHAR2(10);

Affectation

BEGIN
SELECT ename INTO v_name

FROM emp WHERE empno=7839;

DBMS_OUTPUT.PUT_LINE(v_name);
EXCEPTION

WHEN OTHERS
THEN NULL;
END;

/
Professeur [Link]

ORACLE PL/SQL

57

Section Excutable
Exemple2 : Cas plusieurs variables :
DECLARE
v_ename

VARCHAR2(12);

v_sal NUMBER(7,2);
BEGIN
SELECT

ename,sal INTO v_ename,v_sal

FROM emp WHERE ROWNUM = 1;

DBMS_OUTPUT.PUT_LINE(v_ename);
DBMS_OUTPUT.PUT_LINE(v_sal);

EXCEPTION
WHEN OTHERS

THEN NULL;

END;

/
Professeur [Link]

ORACLE PL/SQL

58

Section Excutable
Exemple3 :Exception TOO_MANY_ROWS
SET SERVEROUTPUT ON
DECLARE
v_nom VARCHAR2(10);

v_sal VARCHAR2(10);
BEGIN
SELECT ename,sal INTO v_nom,v_sal

FROM emp WHERE ROWNUM < 5;

DBMS_OUTPUT.PUT_LINE(v_nom||v_sal);
EXCEPTION

WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE('Erreur');
END;

/
Professeur [Link]

ORACLE PL/SQL

59

Les curseurs PL/SQL

Les curseurs SQL:
Un curseur est une zone mmoire de taille fixe, utilise par le moteur SQL
pour analyser et interprter un ordre SQL.
Un curseur explicite, contrairement au curseur implicite (par exemple
SELECT INTO) est gr par l'utilisateur
pour traiter un ordre Select qui ramne plusieurs lignes.
Tout curseur explicite gr dans la section excution doit avoir t dclar
dans la section dclarative
Oui

DECLARE

OPEN

FETCH

Existence?

CLOSE
Non

Crer une
zone SQL
nomme

Professeur [Link]

Identifier
lensemble actif
de lignes

Charger la ligne
courante dans
des variables

ORACLE PL/SQL

Librer
lensemble
actif
60

Les curseurs PL/SQL

Les curseurs SQL:
Dclaration:
CURSOR nom_curseur [(,,)] IS
instruction_select;
nom curseur reprsente le nom du curseur que l'on dclare
dclaration des paramtres (facultatif) reprsente la liste des paramtres
transmis au curseur
instruction select reprsente l'ordre SQL Select d'alimentation du curseur.
Ouverture dun curseur:
OPEN nom_curseur;
Ouvrir le curseur pour excuter la requte et identifier lensemble de
lignes Actif.
Utiliser les attributs du curseur pour tester le rsultat aprs affectation.
Professeur [Link]

ORACLE PL/SQL

61

Les curseurs PL/SQL

Les curseurs SQL:
Ramener les donnes partir du curseur:
FETCH nom_curseur INTO [ (variable1,
variable2, ) | record_name];

Charger les valeurs de la ligne courante dans des variables de sortie.

Prvoir le mme nombre de variables.
Ajuster la position des variables par rapport aux colonnes.
Tester si le curseur contient des lignes.

Fermeture dun curseur:

CLOSE nom_curseur;

Professeur [Link]

ORACLE PL/SQL

62

Les curseurs PL/SQL

Les curseurs SQL:
Les attributs dun curseur:
Chaque curseur dispose de 4 attributs:
%FOUND
Cet attribut prend la valeur TRUE lorsque une ligne est
ramene, sinon il prend la valeur FALSE
%NOTFOUND
Cet attribut prend la valeur FALSE lorsque une ligne est
ramene, 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 impactes par la
dernire instruction SQL

Professeur [Link]

ORACLE PL/SQL

63

Les curseurs PL/SQL

Les curseurs SQL:
Curseur dans une boucle FOR:
Syntaxe:

FOR record_name IN cursor_name

LOOP
instruction1;
instruction2;

END LOOP;
Simplification dcriture pour les curseurs explicites.
Ouverture, fetch et fermeture implicites du curseur.
Le record est dclar implicitement.

Professeur [Link]

ORACLE PL/SQL

64

Les curseurs PL/SQL

Les curseurs SQL:
Les Records:
Sont composs dun ou plusieurs champs de types: scalaire, records ou
PL/SQL table.
Sont diffrents des lignes dune table de la base de donnes.
Traitent un ensemble de champs comme une unit logique.
Sont pratiques pour extraire et manipuler une ligne dune table de la base de
donnes.
Lattribut %ROWTYPE:
Dfinir une variable partir dun ensemble de colonnes dune table de la
base de donnes ou dun curseur.
Prfixer %ROWTYPE par le nom de la table ou du curseur.
Les champs du record prennent les noms et les types des colonnes de la
table ou du curseur.
Professeur [Link]

ORACLE PL/SQL

65

Les curseurs PL/SQL

Les curseurs SQL:
Les Records: Utilisation
DECLARE

CURSOR c_emp IS
select empno from emp;
r_emp c_emp%ROWTYPE;
v_sal
[Link]%TYPE;
v_ename [Link]%TYPE;
BEGIN
open c_emp;
loop
fetch c_emp into r_emp;
select sal, ename into v_sal, v_ename from emp
where empno=r_emp.empno
;
dbms_output.put_line( Fin);
EXIT When c_emp%NOTFOUND;
end loop;
END.

Professeur [Link]

ORACLE PL/SQL

66

Les curseurs PL/SQL

Les curseurs SQL: Exemple 1:
DECLARE
--declaration du curseur
CURSOR c_examen IS
--linstruction select
select num_exam, date, sale from examen;
--variables dacceuil
v_num_exam
examen.num_exam%TYPE;
v_date
[Link]%TYPE;
v_sale
[Link]%TYPE;

BEGIN
open c_examen; --ouverture du curseur
loop --boucle sur les lignes
--lecture dune ligne
fetch c_examen into v_num_exam, v_date, v_sale;
--sortir lorsque le curseur ne ramne pas de ligne
EXIT When c_emp%NOTFOUND;
end loop;
close c_examen; --fermeture du curseur

END;
Professeur [Link]

ORACLE PL/SQL

67

PL/SQL - Partie II

Procdure, fonction et package

Paramtres

Exemples

Java et PL/SQL

Intgration du code Java dans PL/SQL

Enveloppes PL/SQL

Classe Java stocke la base Oracle

Professeur [Link]

Curseurs en PL/SQL

ORACLE PL/SQL

68

Procdure, fonction et package

Fonction/Procdure - syntaxe
Procdure :
Pour crer ou remplacer
CREATE OR REPLACE PROCEDURE nom_proc
[(parameter[, parameter, ...])]
AS|IS
[declarations locales]
BEGIN
instructions executables
[EXCEPTION
exception handlers]
END [nom_proc];
Pour dtruire une procdure
Drop PROCEDURE nom_procedure ;
Professeur [Link]

ORACLE PL/SQL

69

Procdure, fonction et package

Exemple2 : Bloc interne

Procdure interne

DECLARE
x VARCHAR2(5);y VARCHAR2(5);
PROCEDURE EcrireTexte (str IN VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(str);
END EcrireTexte;
BEGIN
x := 'maman'; y := 'maman';
IF x = y THEN
EcrireTexte ('Egaux');
ELSE
EcrireTexte ('Pas gaux');
END IF;
EcrireTexte (LENGTH(x));EcrireTexte (LENGTH(y));
END;
/
Professeur [Link]

ORACLE PL/SQL

70

Procdure, fonction et package

Fonction/Procdure - syntaxe
Fonction :
Pour crer ou remplacer
CREATE OR REPLACE FUNCTION Nom_Fonc
[(parameter[, parameter, ...])]
RETURN type_retour
AS|IS
[declarations locales]
BEGIN
instructions executables
RETURN expression ;
[EXCEPTION
exception handlers]
END [nom_proc];
Pour dtruire une procdure
Drop FUNCTION nom_fonc ;
Professeur [Link]

ORACLE PL/SQL

71

Procdure, fonction et package

Fonction/Procdure - modes de passage des paramtres
Les paramtres formels possdent 3 modes :
IN : se comporte comme une constante PL/SQL. Elle est considre
comme en lecture seule, et ne peut donc tre modifie.
OUT: dans ce mode le paramtre se comporte comme une variable
PL non initialise, et possde donc une valeur NULL. Elle peut tre lue
et peut tre modifie.
IN OUT : la valeur effective du paramtre est passe la procdure
qui linvoque. Au sein de cette procdure, le paramtre agt comme
une variable initialise qui peut tre modifie.
OUT NOCOPY : le compilateur PL/SQL passera le paramtre par
rfrence plutt que par valeur. Meilleurs performances, surtout en
prsence de grosses tables
Professeur [Link]

ORACLE PL/SQL

72

Procdure, fonction et package

Manipuler une variable passe IN
VARIABLE x NUMBER
CREATE OR REPLACE FUNCTION CallFunc(p1 IN NUMBER)

RETURN NUMBER AS
BEGIN
DBMS_OUTPUT.PUT_LINE('CallFunc called with ' || p1);

RETURN p1;
END CallFunc;
/
CALL CallFunc(1) INTO :x;
PRINT :x

1
Professeur [Link]

ORACLE PL/SQL

73

Procdure, fonction et package

Manipuler une variable passe IN OUT
CREATE OR REPLACE PROCEDURE CallProc(p1 IN OUT NUMBER) AS
BEGIN
DBMS_OUTPUT.PUT_LINE('CallProc called with ' || p1);
p1 := p1 + 1;
END CallProc;
/
CALL CallProc(:x);
PRINT :x

1
CALL CallProc(:x);
PRINT :x

2
Professeur [Link]

ORACLE PL/SQL

74

Procdure, fonction et package

Variables IN OUT dun paquetage

Professeur [Link]

ORACLE PL/SQL

75

Procdure, fonction et package

Package Exemple :
CREATE OR REPLACE PACKAGE math AS|IS
PROCEDURE add ( num IN NUMBER, s IN OUT NOCOPY NUMBER) ;

END math;
/
CREATE OR REPLACE PACKAGE BODY math AS|IS

PROCEDURE add(num IN NUMBER, s IN OUT NOCOPY NUMBER) IS

BEGIN
s := s + num;
END add;
END math ;
/
Professeur [Link]

ORACLE PL/SQL

76

Procdure, fonction et package

Paquetage math lors de lexcution
SET SERVEROUTPUT ON
DECLARE

s NUMBER DEFAULT 0;
BEGIN
[Link] ( 1,s);

[Link] ( 5,s);
[Link] (20,s);
DBMS_OUTPUT.PUT_LINE (s);
END;
/

26
Professeur [Link]

ORACLE PL/SQL

77

Procdure, fonction et package

GRANT EXECUTE

GRANTOR

SQL> CONNECT scott/scott2013

GRANTEE

SQL> GRANT EXECUTE ON math TO tp21_1;

SQL> CONNECT / AS SYSDBA
SQL> SELECT grantee, privilege, table_name

FROM DBA_TAB_PRIVS
WHERE grantee='tp12_1';

GRANTEE

PRIVILEGE TABLE_NAME

--------------- ----------- ----------Tp12_1

Professeur [Link]

EXECUTE

MATH
ORACLE PL/SQL

78

Java dans PL/SQL

Objectif :
Construire une classe Java
Accder ses fonctionnalits dans PL/SQL

Outils :
Oracle dispose d'un nouveau produit appel JServer, qui se
compose des lments suivants:
Oracle Java Virtual Machine (JVM), appele Aurora, l'environnement
d'excution et de bibliothques de classes Java
Object Request Broker (l'Aurora / ORB) et Enterprise JavaBeans (EJB)
L'acclrateur JServer (compilateur natif)

Professeur [Link]

ORACLE PL/SQL

79

Java dans PL/SQL

Dmarche suivre :
Pour accder des mthodes de classe Java partir d'Oracle, on
doit :
Crer les lments de code Java en utilisant un IDE(Eclipse, NetBeans,
) ou un simple editeur (NotePad, NotePad++)
Accorder des privilges au besoin sur les programmes wrapper PL /
SQL et la classe Java rfrence.
Chargez la classe Java (s) dans Oracle en utilisant lutilitaire ligne de
commande loadjava ou en utilisant l'instruction CREATE JAVA.

Publier les mthodes de la classe Java l'intrieur de PL / SQL en

crivant des programmes enveloppes (wrapper) en PL / SQL autour
du code Java.
Appelez les programmes warpper PL / SQL
Professeur [Link]

ORACLE PL/SQL

80

Java dans PL/SQL

Cration de la Classe Exemple Manipulation de texte
Cration du code java
public class MajMinTexte {
public static String toMaj(String str) {
return [Link]();}
public static String toMin(String str) {
return [Link]();}
public static void main(String[] args){
String s="Ceci est un texte";
[Link](toMin(s)+"\n"+ toMax(s));
}
Compilation et test :
> javac [Link]
> java MajMinTexte
Professeur [Link]

ORACLE PL/SQL

81

Java dans PL/SQL

Dfinition des privilges :
Pour bnficier de certaines oprations java depuis Oracle, il est
ncessaire de disposer de lun des rles suivants :
JAVASYSPRIV
JAVAUSERPRIV

Depuis un compte SYSDBA, Linstruction Grant permet daccorder

ces rles un utilisateur :

Exemples :
...>sqlplus sys/sysadmin2013 as sysdba
sql>grant JAVAUSERPRIV to tp12_2;

Professeur [Link]

ORACLE PL/SQL

82

Java dans PL/SQL

Chargement de la classe dans la base Oracle:
>loadjava -user tp12_1/tp12_1 -oci8 -resolve
[Link]

Professeur [Link]

ORACLE PL/SQL

83

Java dans PL/SQL

Vrification du chargement de la classe:
Pour vrifier que la classe est charge, il suffit de consulter le contenu
de la vue USER_OBJECTS :

SQL> Select Object_name from User_Objects where

Object_type='JAVA CLASS';

Dchargement de la classe de la base Oracle:

>Dropjava -user tp12_1/tp12_1 -oci8 -resolve
[Link]

Professeur [Link]

ORACLE PL/SQL

84

Java dans PL/SQL

Cration des enveloppes PL/SQL
Une enveloppe PL/SQL est une association entre une mthode
dune classe java charge et une fonction/procdure PL/SQL.
Cela se fait par :
CREATE OR REPLACE FUNCTION plToMaj (txt IN
VARCHAR2)
RETURN VARCHAR2 AS
LANGUAGE JAVA
NAME '[Link]
([Link]) return [Link] ';
CREATE OR REPLACE FUNCTION plToMin (txt IN
VARCHAR2) RETURN VARCHAR2 AS
LANGUAGE JAVA
NAME '[Link]
([Link]) return [Link] ';
Professeur [Link]

ORACLE PL/SQL

85

Java dans PL/SQL

Classe java stocke directement dans oracle
Il est possible de crer une classe java et de la stocke dans la
base oracle grace linstruction PL/SQL CREATE OR REPLACE
AND COMPILE JAVA SOURCE :
CREATE OR REPLACE AND COMPILE JAVA SOURCE
NAMED "MajMinTexte" AS

public class MajMinTexte {

public static String toMaj(String str){
return [Link]();}
public static String toMin(String str){
return [Link]();}
}
/
Professeur [Link]

ORACLE PL/SQL

86

Java dans PL/SQL

Appel des enveloppes PL/SQL
SQL> SELECT pltoMin(ename),pltoMax(job)
FROM emp

WHERE empno = 7839

Rsultat :
PLTOMin(ENAME)

PLTOMAJ(JOB)

------------------------- -----------king

Professeur [Link]

PRESIDENT

ORACLE PL/SQL

87

Types de Donnes Composs

Objectifs :

Crer des RECORDS PL/SQL

Crer des RECORDS avec l'attribut %ROWTYPE

Crer des table PL/SQL

Crer des tables de RECORDS PL/SQL

Professeur [Link]

ORACLE PL/SQL

88

Types de donnes composs

Les types composs sont :

RECORDS

TABLES

NESTED TABLE

VARRAY

ASSOCIATIVE ARRAY

Professeur [Link]

ORACLE PL/SQL

89

Types de donnes composs

Les types composs sont :

RECORDS

Collections

TABLES

NESTED TABLE

VARRAY

ASSOCIATIVE ARRAY

Professeur [Link]

ORACLE PL/SQL

90

Types de Donnes Composites

Record PL/SQL
Peuvent contenir un ou plusieurs champs de type scalaire, RECORD
ou TABLE.

Sont similaires la structure d'enregistrements utilise dans les

L3G.
Traitent un ensemble de champs comme une unit logique.

Pratiques pour rcuprer et traiter les donnes d'une table.

Professeur [Link]

ORACLE PL/SQL

91

Types de Donnes Composites

Dclarer un RECORD PL/SQL
Syntaxe
DECLARE
TYPE type_name IS RECORD
( champ1 field_type [NOT NULL] {:=|DEFAULT expr}
[,champ2 field_type [NOT NULL] {:=|DEFAULT expr }] );
identifier
Exemple :
DECLARE
TYPE

type_name;

emp_record_type IS RECORD
( ename
VARCHAR2( 25 ),
job
VARCHAR2( 25 ),
sal
NUMBER( 7,2 ) );

employee_record
.../...
Professeur [Link]

emp_record_type;

ORACLE PL/SQL

92

Types de Donnes Composites

L'Attribut %ROWTYPE
Dclarer une variable partir d'un ensemble de colonnes d'une
table ou d'une vue.

Prfixer %ROWTYPE avec le nom de la table de la base de donnes.

Les champs dans le RECORD ont les mmes noms et les mmes
types de donnes que les colonnes de la table ou de la vue
associes.

Professeur [Link]

ORACLE PL/SQL

93

Types de Donnes Composites

Avantages
Le nombre de colonnes & le type respectif peuvent tre inconnus.
Le nombre de colonnes & leur type respectif peuvent changer.
Peut tre associ un curseur.
Utile lorsqu'on recherche Une ligne avec l'ordre SELECT.

Plusieurs lignes avec un curseur explicite.

DECLARE
dept_rec
empl_rec

Professeur [Link]

dept%ROWTYPE;
empl%ROWTYPE;

ORACLE PL/SQL

94

Types de Donnes Composites

Exemple lmentaire
SQL>
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
KING

DECLARE
TYPE emp_record_type IS RECORD
( ename
VARCHAR2( 25 ),
job
VARCHAR2( 25 ),
sal
NUMBER( 7,2 )
);
employee_record emp_record_type;
BEGIN
SELECT ename,job,sal -- une seule ligne!
INTO employee_record
FROM emp
WHERE empno = 7839;
DBMS_OUTPUT.PUT_LINE(employee_record.ename);
DBMS_OUTPUT.PUT_LINE(employee_record.job);
DBMS_OUTPUT.PUT_LINE(employee_record.sal);
END;
/
PRESIDENT 5000

Professeur [Link]

ORACLE PL/SQL

95

Types de Donnes Composites

Exemple
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2
rec emp%ROWTYPE;
3 BEGIN
4
SELECT *
5
INTO rec FROM emp
6
WHERE ROWNUM = 1;
7
DBMS_OUTPUT.PUT_LINE([Link]);
8
DBMS_OUTPUT.PUT_LINE([Link]);
8 END;
/
SMITH
800

Professeur [Link]

ORACLE PL/SQL

96

Types de Donnes Composites

Collections :
Dfinition
Sont composes de deux lments :
Une cl primaire de type BINARY_INTEGER

Clef Primaire

Colonne
Une colonne
... de type scalaire ou record ...
1

SCOTT

SMITH

KING

...

...

BINARY_INTEGER
Professeur [Link]

ORACLE PL/SQL

Scalaire
97

Types de Donnes Composites

TABLES PL/SQL :
Sont composes de deux lments :
Une cl primaire de type BINARY_INTEGER
Une colonne de type scalaire ou record
Clef Primaire
...

Colonne
...

SCOTT

SMITH

KING

...

...

BINARY_INTEGER
Professeur [Link]

ORACLE PL/SQL

Scalaire
98

Types de Donnes Composites

Caractristiques d'une table PL/SQL
Taille non limite
Peut s'accrotre dynamiquement

Peut avoir une colonne (DATE, NUMBER, VARCAHAR2 ou

ventuellement dfinie l'aide de %TYPE) et une clef primaire sans
nom.
La colonne peut tre de type scalaire ou RECORD mais la clef doit
possder le type BINARY_INTEGER
Ne peut tre initialise au moment de la dclaration

Professeur [Link]

ORACLE PL/SQL

99

Types de Donnes Composites

dclaration de TABLE PL/SQL
DECLARE
TYPE type_name IS TABLE OF scalar_datatype
[ NOT NULL ] INDEX BY BINARY_INTEGER ;
identifier type_name ;
Exemple
DECLARE
TYPEtxt_table_type IS TABLE OF VARCHAR2(25)
INDEX BY BINARY_INTEGER ;
first_name_table
last_name_table

Professeur [Link]

text_table_type;
text_table_type;

ORACLE PL/SQL

100

Types de Donnes Composites

Exemple : loi de Laplace-Gauss (1/2)
CREATE OR REPLACE FUNCTION
gauss (x IN NUMBER,m IN NUMBER, s IN NUMBER)
RETURN NUMBER IS
TYPE ConstantesTabTyp IS TABLE OF NUMBER(10,9)
INDEX BY BINARY_INTEGER;
arrayOfCst ConstantesTabTyp;
pi
CONSTANT NUMBER(12,11) := 3.14159265359;
xx
NUMBER;
y
NUMBER;
q
NUMBER;
poly NUMBER := 0;

BEGIN
arrayOfCst(1)
arrayOfCst(2)
arrayOfCst(3)
arrayOfCst(4)
arrayOfCst(5)
arrayOfCst(6)
Professeur [Link]

:= 1.330274429;
:= -1.821255978;
:= 1.781477937;
:= -0.356563782;
:= 0.319381530;
:= 0.0;
ORACLE PL/SQL

101

Types de Donnes Composites

Exemple (2/2)
IF s = 0 THEN
RETURN -1;
END IF;
xx := (x-m)/s;
y := EXP(-xx*xx/2.0)/SQRT(2.0*pi);
q := 1/(1+0.2316419*ABS(xx));
FOR i IN 1..6 LOOP
poly := poly * q + arrayOfCst(i);
END LOOP;
IF x > m THEN
RETURN (1-poly*y);
ELSE
RETURN (poly*y);
END IF;
END;
/
Professeur [Link]

ORACLE PL/SQL

102

Types de Donnes Composites

Fonctions utilisables avec des tables PL
EXISTS(n) renvoie TRUE si le nime lment existe
COUNT renvoie le nombre d'lment contenus
FIRST renvoie le premier rang de la table sinon NULL
LAST renvoie le dernier rang de la table sinon NULL
PRIOR(n) renvoie le nombre qui prcde n
NEXT(n) renvoie le nombre qui succde n
EXTEND, EXTEND(n) rajoute 1 ou n lment NULL tandis que EXTEND (n,i)
rajoute n copies de l'lment de rang i la table
TRIM ou TRIM(n) supprime le dernier ou les n derniers lments de la
table
DELETE, DELETE(n) ou DELETE(n,m) pour supprimer les lments de la
plage n-m
Professeur [Link]

ORACLE PL/SQL

103

Types de Donnes Composites

Exemple : table PL (1/2)
SET SERVEROUTPUT ON
DECLARE
TYPE ConstantesTabTyp IS TABLE OF NUMBER(3)
NOT NULL INDEX BY
BINARY_INTEGER;
arrayOfCst ConstantesTabTyp;
BEGIN
arrayOfCst(1) := 45;
arrayOfCst(2) := 12;
arrayOfCst(3) := 15;
arrayOfCst(4) := 20;
arrayOfCst(5) := 50;
arrayOfCst(6) := 100;
DBMS_OUTPUT.PUT_LINE([Link]);
Professeur [Link]

ORACLE PL/SQL

104

Types de Donnes Composites

Exemple : table PL (2/2)
IF [Link](3) THEN
DBMS_OUTPUT.PUT_LINE(arrayOfCst(3));
END IF;
DBMS_OUTPUT.PUT_LINE([Link]);
DBMS_OUTPUT.PUT_LINE(arrayOfCst([Link]));
DBMS_OUTPUT.PUT_LINE([Link]);
DBMS_OUTPUT.PUT_LINE(arrayOfCst([Link]));
DBMS_OUTPUT.PUT_LINE([Link](4));
DBMS_OUTPUT.PUT_LINE(arrayOfCst([Link](4)));
DBMS_OUTPUT.PUT_LINE([Link](4));
DBMS_OUTPUT.PUT_LINE(arrayOfCst([Link](4)));
END;
/
Professeur [Link]

ORACLE PL/SQL

105

Types de Donnes Composites

Table de RECORDS PL
Dfinir une variable TABLE avec l'attribut %ROWTYPE
SET SERVEROUTPUT ON
DECLARE

TYPE deptTableType IS TABLE OF [Link]%ROWTYPE

NOT NULL INDEX BY BINARY_INTEGER;
deptTable deptTableType;
BEGIN

DBMS_OUTPUT.PUT_LINE([Link]);
SELECT * INTO deptTable(1) FROM dept WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE([Link]);
DBMS_OUTPUT.PUT_LINE(deptTable(1).deptno);
DBMS_OUTPUT.PUT_LINE(deptTable(1).loc);
DBMS_OUTPUT.PUT_LINE(deptTable(1).dname);
END;
/
Professeur [Link]

ORACLE PL/SQL

106

Types de Donnes Composites

DECLARE
CURSOR c1 IS SELECT * FROM emp WHERE SAL > 10000 ORDER BY sal
DESC;
TYPE empArrayType IS TABLE OF c1%ROWTYPE
NOT NULL INDEX BY BINARY_INTEGER;
empArray empArrayType;
space CHAR(1) := ' ';
PROCEDURE ln (str IN VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(str);
END ln;
BEGIN
FOR v1 IN c1 LOOP
EXIT WHEN c1%ROWCOUNT > 5 OR c1%NOTFOUND;
empArray([Link]+1) := v1;
END LOOP;
IF [Link]>0 THEN
FOR k IN [Link]..[Link] LOOP
ln ( empArray(k).ename || space ||
empArray(k).job
|| space ||
empArray(k).sal);
Tables PL
END LOOP;
denregistrements
END IF;
END;
/
Professeur [Link]

ORACLE PL/SQL

107

Types de Donnes Composites

Variance: calculs mathmatiques
...
FOR j IN [Link]..[Link] LOOP

z := z + empArray(j).sal;
END LOOP;

ln ('Moyenne des 5:' || z/[Link]);

...
Sachant que la variance sobtient en divisant la
somme des carrs des carts la moyenne par le
carr de la moyenne..

Professeur [Link]

ORACLE PL/SQL

108

Types Composites Exemple de Type Objet

Dclaration
--type TAdresse
CREATE OR REPLACE TYPE tadresse AS OBJECT
(
numero
varchar2 10 ),
rue
varchar2( 30 ),
ville
varchar2( 20 ),
region
varchar2( 10 ),
codePostal
varchar2( 10 )
);
-- Type TClient
CREATE OR REPLACE TYPE tclient AS OBJECT
(
code
number(5),
nom
varchar2(30),
numContact
varchar2(12),
addr
taddresse,
member procedure afficher,
member procedure setAdress(add tadresse),
map member function comparer return number
);
/
Professeur [Link]

ORACLE PL/SQL

109

Types Composites Exemple de Type Objet

Implmentation des mthodes
--type tclient
CREATE OR REPLACE TYPE BODY tclient AS
MEMBER PROCEDURE afficher IS
BEGIN
dbms_output.put_line('Code: '|| code);
dbms_output.put_line('nom : '|| nom);
[Link];

END afficher;
MEMBER PROCEDURE setAdresse(add tadresse) IS
BEGIN
addr:=add;
END afficher;
MAP MEMBER FUNCTION comparer return number IS
BEGIN
return code;
END comparer;
END;
/
Professeur [Link]

ORACLE PL/SQL

110