Le Langage PL/SQL

1
Le Langage PL/SQL

• IUPm3- Université de Nantes

2
Plan du Cours

• Introduction au langage PL/SQL
• Les variables
• Traitements Conditionnels
• Traitements répétitifs
• Les curseurs
• Gestion dexceptions

3
Comparaison avec SQL

• SQL
• Langage assertionnel et non procédural
• PL/SQL
• Langage procédural, qui intègre des ordres SQL
• SELECT, INSERT, UPDATE, DELETE
• INSERT, UPDATE, DELETE
• Gestion de transactions COMMIT, ROLLBACK,
  SAVEPOINT
• Langage à part entière comprenant
• Définition de variables, constantes, expressions,
  affectations
• Traitements conditionnels, répétitifs
• Traitement de Curseurs
• Traitement des erreurs et dexceptions
• Etc

4
Exemple
DECARE qty_on_hand NUMBER(5) BEGIN SELECT
quantity INTO qty_on_hand FROM inventory WHERE
product TENNIS RACKET FOR UPDATE of
quantity IF qty_on_hand gt 0 THEN UPDATE
inventory SET quantity quantity 1 WHERE
product TENNIS RACKET INSERT INTO
purchase_record VALUES(Tennis racket
puchased,SYSDATE) ENF IF
COMMIT END
5
Structure
DECLARE --declarations de variables contantes
-- exceptions et
curseurs BEGIN nom-bloc --instructions SQL
et extentions EXCEPTIONS -- Traitement des
exceptions END ou END nombloc
6
Architecture
PL/SQL Engine
Procedural Statement Executor
PL/SQL Block
PL/SQL Block
Procedural
SQL
SQL Statement Executor
7
Types de Variables

• Variables locales
• De type simple type de base ou booléen
• Faisant référence à la métabase
• De type composé Tableau, Record
• Variables Extérieures
• Variables dun langage hote (ex C) (préfixés par
  )
• Paramètres (ex SQL interactif par )
• Champs décrans (Forms)

8
Variables de type Simple
Declare nom char(15) salaire number(7,2) em
bauche DATE réponse boolean
9
Variables sur la métabase

• Reprend
• Soit le même type quune colonne dans une table
• Soit la même structure quune ligne dans une
  table
• Soit le même type quune variable précédemment
  définie
• Syntaxe
• nom_var1 table.colonneTYPE
• nom_var2 tableROWTYPE
• nom_vars3 nom_var1TYPE
• Exemples
• nom emp.enameTYPE
• enreg empROWTYPE
• commi number(7,2)
• Salaire commiTYPE

10
Initialisation et visibilité

• Dans la déclaration
• Nom char(10) Miller
• Reponse boolean TRUE
• Constantes
• Pi CONSTANT number (7,2) 3.14
• Interdire les valeurs non renseignées NOT NULL
• Debut number NOT NULL 10
• Lordre SELECT
• Select col1, col2
• Into var1, var2
• From table
• Where condition
• Règle
• La clause INTO est obligatoire
• Le select ne doit ramener quune ligne
• Visibilité bloc de déclaration blocs imbriqués

11
Exemple

• Declare
• nom_emp char(15)
• salaire emp.salTYPE
• commission emp.commTYPE
• nom_départ char(15)
• Begin
• Select ename, sal, comm, dname
• Into nom_emp, salaire, commission, nom_départ
• From emp, dept
• Where ename MILLER and emp.deptno
  dept.deptno
• End

12
Traitements Conditionnels
IF condition THEN instruction
instruction ELSEIF condition THEN instruction
instruction ELSEIF condition
THEN instruction instruction ELSE instruc
tion instruction END-IF Une instruction IF
peut contenir plusieurs clauses ELSEIF, mais une
seule clause ELSE.
13
IF THEN ELSE

• IF THEN
• IF condition Then traitement ENDIF
• Exemple
• IF sales gt quota THEN
• compute_bonnus(emp_id)
• UPDATE payroll SET pay pay bonus where
  empno emp_id
• END IF

14

• IF THEN ELSE
• IF condition THEN
• traitement1
• ELSE
• traitement2
• END IF
• Exemple
• IF trans_type CR THEN
• UPDATE accounts SET balance balance credit
  WHERE
• ELSE
• UPDATE accounts SET balance balance debit
  WHERE
• END IF

15
IF THEN ELSEIF

• IF condition1THEN
• Traitement 1
• ELSEIF condition2 THEN
• Traitement2
• ELSE
• traitement3
• END IF
• Exemple
• IF sales gt 50000 THEN
• bonus 1500
• ELSEIF sales gt 35000 THEN
• bonus 500
• ELSE
• bonus 100

16

• IF condition1 THEN
• traitement1
• ELSEIF condition2 THEN
• traitement2
• ELSEIF condition3 THEN
• END IF

• IF condition1 THEN
• traitement1
• ELSE
• IF condition2 THEN
• traitement2
• ELSE
• IF condition3 THEN
• traitement3
• END IF
• END IF
• END IF

Sont équivalents
17
Exemple

• DECLARE
• emploi char (10)
• nom char(15) MILLER
• ctl char(30)
• BEGIN
• Select job INTO emploi FROM emp WHERE ename
  nom
• IF emploi is null THEN ctl nom napas
  demploi 
• ELSEIF emploi SALESMAN
• THEN update emp
• set comm 1000 where ename nom
• ctl nom commission modifiee
• ELSE
• update emp
• set comm 0 where ename nom
• ctl nom pas de commission
• END IF
• insert into resultat values(ctl)
• commit
• END

18
Répétition
Instruction LOOP simple LOOP instruction
instruction END LOOP Instruction While
LOOP WHILE condition LOOP instruction
instruction END LOOP Instruction FOR..
LOOP FOR variable_boucle IN REVERSE
borne_inférieure borne_supérieure
LOOP instruction instruction END LOOP
FOR i in 1.. Max_loop LOOP dbms_output.put_l
ine(i to_char(i)) END LOOP
19
EXIT, GOTO, NULL, Commentaires
Instruction GOTO GOTO nom_étiquette Instructio
n NULL IF (mod(i,10) 0) THEN i i
1 else NULL END IF Commentaires instructio
n -- Bla bla bla instruction Ou avec / Bla
bla bla /
20
EXAMPLE 1
DECLARE max_records CONSTANT int 100 i int
1 BEGIN FOR i in 1.. Max_records LOOP if
(mod(i,10) 0) then INSERT INTO teste_table
(val, current_date) values (i, SYSDATE) else N
ULL END IF END LOOP COMMIT END /
21
Exemple 2
SQLgt set serveroutput on SQL gt SQLgt declare
2 2 Average_Body_Temp Patient.Body_Temp_Deg_
Ftype 3 3 begin 4 4
dbms_output.enable 5 5 select
avg(Body_Temp_Deg_F) into Average_Body_Temp from
Patient 6 6 dbms_output.put_line(Te
mpérature moyenne du corps en degrés F
to_char(Average_Body_Temp,999.99))
7 7 end 8 / Temperature
moyenne du corps en degrés F 99,80
Procedure PL/SQL terminée avec succès.
22
Imbrication de blocs
PL/SQL permet dinclure des sous-blocs dans un
bloc (pratique nest pas recommandé) .
Declare x real Begin declare x
real begin end End
x extérieur
Visibilité
x intérieur
x extérieur
23
Procédures

• PROCEDURE nom_procédure (argument1 ,
  argumentN) IS
• déclarations_de_variables_locales
• BEGIN
• section_exécutable
• section_exception
• END nom_procedure
• Si on fait CREATE PROCEDURE ou FUNCTION, la
  procédure ou fonction qui est crée est
  permanente. Elle peut être appelée par un script
  SQLPlus, un sous-programme PL/SQL etc.
• Les variables déclarées dans une procédure ne
  sont pas accessibles en dehors delle-même.

24
Exemple
Declare New_patient_ID Patient.Patient_ID
type High_Fever constant real
42.0 Procedure Record _Patient_Temp_Deg_C(Patien
t_ID varchar2, Body_Temp_Deg_C real)
is Temp_Deg_F real Begin Temp_Deg_F
(9.0/5.0)Body_Temp_Deg_C 32.0 insert into
Patient (Patient_ID, Body_Temp_Deg_F) values
(Patient_ID, Temp_Deg_F)
Temp_Deg_F) commit end Begin New_Patient_ID
GG9999 Record_Patient_Temp_Deg_C
(Nex_Patient_ID, High_Fever) End /
25
Fonctions
FUNCTION nom_fonction argument1 , argumentN)
RETURN type_données-fonction IS déclaration-var
iabels_locales BEGIN Section_exécutable section_
exeption END nom_fonction
26
Exemple
Declare Course_ID Course.Course_IDtype Function
Max_Additional_Fees (Dept_ID IN
varchar2) return varchar2 is Additional_Fees Co
urse.Additional_Feestype Units Cours.Unitstyp
e Cours_ID Course.Course_IDtype Begin select
Course_ID into Cours-ID from Course where
Departement_ID Dept_ID and additional_Fees
in (select max(Additional_Fees) from
Course where Departement_ID Dept_ID) return
Course_ID End Begin dbms_output.enable Course
_ID Max_Additional_Fees(ECON) dbms_output.p
ut_line(Course_ID Course_ID) End /
27
Recherche de données avec un curseur

• Definition
• Un curseur est un mécanisme permettant de
  rechercher un nombre arbitraire de lignes avec
  une instruction SELECT.
• Deux types de curseurs
• Le curseurs implicite généré et géré par le
  noyau pour chaque ordre SQL dun bloc
• Le curseur explicite généré para lutilisateur
  pour traiter un ordre SELECT qui ramène plusieurs
  lignes. Utilisation
• Déclaration
• Ouverture du curseur
• Traitement des lignes
• Fermeture du curseur

28
Déclaration dun curseur explicite

• Se fait dans la section Declare
• Syntaxe
• cursor nom_curseur is ordre_select
• Exemple
• Declare
• cursor dept_10 is
• select ename, sal From emp
• where deptno 10 order by sal
• Begin
• End

29
Ouverture

• Louverture déclanche
• Allocation de mémoire pour le lignes du curseur
• Lanalyse syntaxique et sémantique du select
• Le positionnement de verrous éventuels
• Louverture se fait dans la section Begin
• Syntaxe OPEN nom_curseur

Declare cursor dept_10 is select ename, sal
From emp where deptno 10 order by sal
Begin open dept_10 End
30
Traitement de Lignes

• Les lignes ramenées sont traitées une par une, la
  valeur de chaque colonne doit être stockée dans
  une variable réceptrice
• Syntaxe
• Fetch nom_curseur into liste_variables
• Le Fetch ramène une ligne à la fois.

31
Exemple
Declare cursor dept_10 is select ename, sal
From emp where deptno 10 order by sal nom
emp.enameTYPE salaire emp.salTYPE Begin Open
dept_10 Loop Fetch dept_10 into nom,
salaire If salaire gt 2500 then insert into
résultat values (nom,salaire) end if exit
when salaire 5000 end loop End
32
Fermeture

• Syntaxe Close nom_curseur
• Action libère la place de mémoire

Declare cursor dept_10 is select ename, sal
From emp where deptno 10 order by sal nom
emp.enameTYPE salaire emp.salTYPE Begin Open
dept_10 Loop Fetch dept_10 into nom,
salaire If salaire gt 2500 then insert into
résultat values (nom,salaire) end if exit
when salaire 5000 end loop close
dept_10 End
33
Exemple
Prompt Nombre de salaires ? Accept
nombre Declare Cursor c1 is select ename, sal
from emp order bay sal
desc vename emp.enameTYPE vsal emp.salTYPE
Begin open c1 for i in 1..nombre loop fetc
h c1 into vename, vsal insert into résultat
values (vsal, vename) end loop close
c1 End
34
Les attributs dun curseur

• Définition indicateurs sur létat dun curseur.
• FOUND nom_curseurFOUND
• TRUE le dernier FETCH a ramené une ligne
• FALSE plus de ligne
• NOTFOUND nom_curseurNOTFOUND
• TRUE le dénier FETCH na pas ramené de ligne
• ISOPEN nom_curseurISOPEN
• TRUE le curseur est ouvert
• ROWCOUNT nom_curseurrowcount
• Nbre de lignes ramenées par le FetCH

35
Exemple - FOUND
Declare cursor dept_10 is select ename, sal
From emp where deptno 10 order by sal nom
emp.enameTYPE salaire emp.salTYPE Begin Open
dept_10 Fetch dept_10 into nom,
salaire While dept_10FOUND Loop If salaire
gt 2500 then insert into résultat values
(nom,salaire) end if Fetch dept_10 into
nom, salaire end loop close dept_10 End
36
Exemple - NOTFOUND
Declare cursor dept_10 is select ename, sal
From emp where deptno 10 order by sal nom
emp.enameTYPE salaire emp.salTYPE Begin Open
dept_10 Loop Fetch dept_10 into nom,
salaire Exit when dept_10NOTFOUND If
salaire gt 2500 then insert into résultat values
(nom,salaire) end if end loop close
dept_10 End
37
Exemple - ISOPEN
Declare cursor dept_10 is select ename, sal
From emp where deptno 10 order by sal nom
emp.enameTYPE salaire emp.salTYPE Begin If
not(dept_10ISOPEN) the Open dept_10 end
if Loop Fetch dept_10 into nom,
salaire Exit when dept_10NOTFOUND If
salaire gt 2500 then insert into résultat values
(nom,salaire) end if end loop close
dept_10 End
38
Exemple - ROWCOUNT
Declare cursor dept_10 is select ename, sal
From emp where deptno 10 order by sal nom
emp.enameTYPE salaire emp.salTYPE Begin Open
dept_10 Loop Fetch dept_10 into nom,
salaire Exit when dept_10NOTFOUND or
dept_10ROWCOUNT gt 15 If salaire gt 2500 then
insert into résultat values (nom,salaire) end
if end loop close dept_10 End
39
Gestion des Erreurs

• Section Exception
• Anomalie programmeur
• Erreur Oracle

40
Section Exception

• Notion dexception traitements derreurs
• Types derreurs
• Erreurs internes Oracle (Sqlcode ltgt 0)
• Erreurs programme utilisateur
• Règles à respecter
• Définir et donner un nom à chaque erreur
• Associer ce nom à la section Exception (partie
  declare)
• Définir la traitement dans la partie Exception

41
Gestion des Exceptions

• Syntaxe
• EXCEPTION
• WHEN nom_exception1 THEN
• instructions_PL_SQL
• WHEN nom_exceptionN Then
• instructions PL/SQL
• WHEN OTHERS THEN
• instrctions_PL/SQL
• END
• Sortie du bloc après exécution du traitement

42
Exceptions Prédéfinies

• DUP_VAL_ON_INDEX
• Lorsquune instruction SQL tente de créer une
  valeur dupliquée dans une colonne sur laquelle un
  index unique a été défini
• INVALID_NUMBER
• Lorsquune instruction SQL spécifie un nombre
  invalide
• NO_DATA_FOUND
• Lorsquune instruction Select ne retourne aucune
  ligne
• TOO_MANY_ROWS
• Une instruction Select ne peut pas renvoyer plus
  dune ligne sans provoquer lexception
  TOO_MANY_ROWS
• VALUE_ERROR
• Provoquée dans des situations derreur résultant
  de valeurs tronquées ou converties

43
Exemple
Declare Course_Rec CoursROWTYPE Begin dbms_ou
tput.enable select into Course_Rec from
Course where Course_ID 777 Exception when
No_Data_Found then dbms_output.put_line(Aucune
donnée retournée) when other then
null End / Aucune donnée retournée Procedure
PL/SQL terminé avec succès.
44
Déclaration dune Exception
Declare pas_comm EXCEPTION salaire emp.salTYP
E commi emp.commTYPE numero emp.empnoTYPE
Begin Select sal, comm, empno into salaire,
commi, numero from emp where empno
num_emp If commi 0 or commi is null then
raise pas_comm else traitement end
if Exception When pas_comm then insert into
resultat values (numéro, salaire, pas de
comm) End Obs num_emp fait référence à une
variable extérieure au bloc PL/SQL)
45
Test dexécution avec SQLCODE et SQLERRM

• SQLCODE
• Fonction prédéfinie qui renvoie le statut
  derreur système de linstruction qui vient
  dêtre exécutée (si sans erreur, SQLCODE 0).
• SQLERRM
• Fonction prédéfinie qui renvoie le message
  derreur associé à la valeur retournée par
  SQLCODE (si sans erreur, SQLERRM ORA-0000).

Declare Begin dbms_output.enable dbms_output.pu
t_line(SQLCODE to_char(SQLCODE)) dbms_ou
tput.put_line(SQLERRM SQLERRM) End / SQL
CODE 0 SQLERRM ORA-0000 exécution normale,
terminé avec succès
46
Exemple
Declare Class_Rec ClassROWTYPE Begin dbms_outp
ut.enable select into Class_Rec from
class Exception when OTHERS then dbms_output.pu
t_line(SQLCODE to_char(SQLCODE)) dbms_ou
tput.put_line(SQLERRM) End / SQLCODE
-1422 ORA-01422 lextraction exacte ramène plus
que le nombre de lignes demandé Procédure PL/SQL
terminée avec succès.