Transcript Le Langage PL/SQL

Le Langage PL/SQL
IUPm3- Université de Nantes
Plan du Cours
•
•
•
•
•
•
Introduction au langage PL/SQL
Les variables
Traitements Conditionnels
Traitements répétitifs
Les curseurs
Gestion d’exceptions
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 d’exceptions
Etc…
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 > 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;
Structure
[DECLARE
--declarations de variables contantes
-- exceptions et curseurs]
BEGIN [nom-bloc]
--instructions SQL et extentions
[EXCEPTIONS
-- Traitement des exceptions]
END; ou END nombloc
Architecture
PL/SQL Engine
PL/SQL
Block
PL/SQL
Block
Procedural
SQL
Procedural
Statement
Executor
SQL Statement Executor
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 d’un langage hote (ex: C) (préfixés par :)
– Paramètres (ex: SQL interactif par &)
– Champs d’écrans (Forms)
Variables de type Simple
Declare
nom
salaire
embauche
réponse
char(15);
number(7,2);
DATE ;
boolean;
Variables sur la métabase
• Reprend
– Soit le même type qu’une colonne dans une table
– Soit la même structure qu’une ligne dans une table
– Soit le même type qu’une variable précédemment définie
• Syntaxe
nom_var1
table.colonne%TYPE
nom_var2
table%ROWTYPE
nom_vars3
nom_var1%TYPE
• Exemples:
nom
emp.ename%TYPE;
enreg
emp%ROWTYPE;
commi
number(7,2);
Salaire
commi%TYPE;
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;
•
L’ordre SELECT
Select col1, col2
Into var1, var2
From table
[Where condition ];
•
Règle
–
–
•
La clause INTO est obligatoire
Le select ne doit ramener qu’une ligne
Visibilité: bloc de déclaration + blocs imbriqués
Exemple
Declare
nom_emp
char(15);
salaire
emp.sal%TYPE
commission
emp.comm%TYPE;
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;
Traitements Conditionnels
IF condition THEN
instruction; … instruction;
[ELSEIF condition THEN
instruction; … instruction; ]
…
[ELSEIF condition THEN
instruction; … instruction;]
[ELSE
instruction; … instruction;]
END-IF;
Une instruction IF peut contenir plusieurs clauses ELSEIF, mais
une seule clause ELSE.
IF THEN ELSE
• IF THEN
IF condition Then traitement ENDIF;
• Exemple:
IF sales > quota THEN
compute_bonnus(emp_id);
UPDATE payroll SET pay = pay + bonus where empno = emp_id;
END IF;
• 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;
IF THEN ELSEIF
• IF condition1THEN
Traitement 1;
ELSEIF condition2 THEN
Traitement2;
ELSE
traitement3;
END IF;
• Exemple:
IF sales > 50000 THEN
bonus := 1500;
ELSEIF sales > 35000 THEN
bonus := 500;
ELSE
bonus := 100;
IF condition1 THEN
traitement1;
ELSE
IF condition2 THEN
traitement2;
ELSE
IF condition3 THEN
traitement3;
END IF;
END IF;
END IF;
IF condition1 THEN
traitement1;
ELSEIF condition2 THEN
traitement2;
ELSEIF condition3 THEN
END IF;
Sont équivalents
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 || ‘n’apas d’emploi ’;
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;
/
OBS: || fait la concaténation et / provoque la soumission du bloc au moteur PL/SQL
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_line(‘i: ‘|| to_char(i));
END LOOP;
EXIT, GOTO, NULL, Commentaires
Instruction GOTO
GOTO nom_étiquette;
Instruction NULL
IF (mod(i,10) = 0) THEN
i := i + 1;
else
NULL;
END IF;
Commentaires
instruction;
-- Bla bla bla
instruction
Ou avec /* Bla bla bla */
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
NULL;
END IF;
END LOOP;
COMMIT;
END;
/
Exemple 2
SQL> set serveroutput on
SQL >
SQL> declare
2
2 Average_Body_Temp
Patient.Body_Temp_Deg_F%type;
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(‘Tempé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.
Imbrication de blocs
PL/SQL permet d’inclure des sous-blocs dans un bloc (pratique n’est
pas recommandé)
.Declare
x real;
Begin
x extérieur
…
declare
x real;
begin
…
end;
…
End;
x intérieur
x extérieur
Visibilité
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 SQL*Plus, un sous-programme PL/SQL etc.
• Les variables déclarées dans une procédure ne sont pas accessibles en
dehors d’elle-même.
Exemple
Declare
New_patient_ID
High_Fever
Patient.Patient_ID %type;
constant real := 42.0;
Procedure Record _Patient_Temp_Deg_C(Patient_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);
commit;
end;
Begin
New_Patient_ID := ‘GG9999’;
Record_Patient_Temp_Deg_C (Nex_Patient_ID, High_Fever);
End;
/
Fonctions
FUNCTION nom_fonction [argument1… [, argumentN) ]
RETURN type_données-fonction IS
[déclaration-variabels_locales]
BEGIN
Section_exécutable
[section_exeption]
END [nom_fonction];
Declare
Course_ID
Exemple
Course.Course_ID%type;
Function Max_Additional_Fees (Dept_ID IN varchar2)
return varchar2 is
Additional_Fees
Course.Additional_Fees%type;
Units
Cours.Units%type;
Cours_ID
Course.Course_ID%type;
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.put_line(‘Course_ID: ‘|| Course_ID);
End;
/
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 d’un bloc
– Le curseur explicite: généré para l’utilisateur pour traiter un ordre
SELECT qui ramène plusieurs lignes. Utilisation:
–
–
–
–
Déclaration
Ouverture du curseur
Traitement des lignes
Fermeture du curseur
Déclaration d’un 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;
Ouverture
•
L’ouverture déclanche:
–
–
–
•
•
Allocation de mémoire pour le lignes du curseur
L’analyse syntaxique et sémantique du select
Le positionnement de verrous éventuels
L’ouverture 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;
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.
Exemple
Declare
cursor dept_10 is
select ename, sal From emp
where deptno = 10 order by sal;
nom emp.ename%TYPE;
salaire emp.sal%TYPE;
Begin
Open dept_10;
Loop
Fetch dept_10 into nom, salaire;
If salaire > 2500
then insert into résultat values (nom,salaire);
end if;
exit when salaire = 5000;
end loop;
…
End;
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.ename%TYPE;
salaire emp.sal%TYPE;
Begin
Open dept_10;
Loop
Fetch dept_10 into nom, salaire;
If salaire > 2500
then insert into résultat values (nom,salaire);
end if;
exit when salaire = 5000;
end loop;
close dept_10;
End;
Exemple
Prompt Nombre de salaires ?
Accept nombre;
Declare
Cursor c1 is select ename, sal from emp
order bay sal desc;
vename
emp.ename%TYPE;
vsal
emp.sal%TYPE;
Begin
open c1;
for i in 1..&nombre
loop
fetch c1 into vename, vsal;
insert into résultat values (vsal, vename);
end loop;
close c1
End;
Les attributs d’un curseur
• Définition indicateurs sur l’état d’un curseur.
– %FOUND : nom_curseur%FOUND
• TRUE: le dernier FETCH a ramené une ligne
• FALSE: plus de ligne
– %NOTFOUND: nom_curseur%NOTFOUND
• TRUE: le dénier FETCH n’a pas ramené de ligne
– %ISOPEN: nom_curseur%ISOPEN
• TRUE: le curseur est ouvert
– %ROWCOUNT: nom_curseur%rowcount
• Nbre de lignes ramenées par le FetCH
Exemple - %FOUND
Declare
cursor dept_10 is
select ename, sal From emp
where deptno = 10 order by sal;
nom emp.ename%TYPE;
salaire emp.sal%TYPE;
Begin
Open dept_10;
Fetch dept_10 into nom, salaire;
While dept_10%FOUND
Loop
If salaire > 2500
then insert into résultat values (nom,salaire);
end if;
Fetch dept_10 into nom, salaire;
end loop;
close dept_10;
End;
Exemple - %NOTFOUND
Declare
cursor dept_10 is
select ename, sal From emp
where deptno = 10 order by sal;
nom emp.ename%TYPE;
salaire emp.sal%TYPE;
Begin
Open dept_10;
Loop
Fetch dept_10 into nom, salaire;
Exit when dept_10%NOTFOUND;
If salaire > 2500
then insert into résultat values (nom,salaire);
end if;
end loop;
close dept_10;
End;
Exemple - %ISOPEN
Declare
cursor dept_10 is
select ename, sal From emp
where deptno = 10 order by sal;
nom emp.ename%TYPE;
salaire emp.sal%TYPE;
Begin
If not(dept_10%ISOPEN) the Open dept_10; end if;
Loop
Fetch dept_10 into nom, salaire;
Exit when dept_10%NOTFOUND;
If salaire > 2500
then insert into résultat values (nom,salaire);
end if;
end loop;
close dept_10;
End;
Exemple - %ROWCOUNT
Declare
cursor dept_10 is
select ename, sal From emp
where deptno = 10 order by sal;
nom emp.ename%TYPE;
salaire emp.sal%TYPE;
Begin
Open dept_10;
Loop
Fetch dept_10 into nom, salaire;
Exit when dept_10%NOTFOUND or dept_10%ROWCOUNT > 15;
If salaire > 2500
then insert into résultat values (nom,salaire);
end if;
end loop;
close dept_10;
End;
Gestion des Erreurs
• Section Exception
• Anomalie programmeur
• Erreur Oracle
Section Exception
• Notion d’exception: traitements d’erreurs
• Types d’erreurs:
– Erreurs internes Oracle (Sqlcode <> 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
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
Exceptions Prédéfinies
• DUP_VAL_ON_INDEX
– Lorsqu’une instruction SQL tente de créer une valeur dupliquée dans une
colonne sur laquelle un index unique a été défini
• INVALID_NUMBER
– Lorsqu’une instruction SQL spécifie un nombre invalide
• NO_DATA_FOUND
– Lorsqu’une instruction Select ne retourne aucune ligne
• TOO_MANY_ROWS
– Une instruction Select ne peut pas renvoyer plus d’une ligne sans
provoquer l’exception TOO_MANY_ROWS
• VALUE_ERROR
– Provoquée dans des situations d’erreur résultant de valeurs tronquées ou
converties
Exemple
Declare Course_Rec
Cours%ROWTYPE;
Begin
dbms_output.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.
Déclaration d’une Exception
Declare
pas_comm
salaire
commi
numero
EXCEPTION;
emp.sal%TYPE;
emp.comm%TYPE;
emp.empno%TYPE;
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)
Test d’exécution avec SQLCODE et
SQLERRM
• SQLCODE
– Fonction prédéfinie qui renvoie le statut d’erreur système de l’instruction
qui vient d’être exécutée (si sans erreur, SQLCODE = 0).
• SQLERRM
– Fonction prédéfinie qui renvoie le message d’erreur associé à la valeur
retournée par SQLCODE (si sans erreur, SQLERRM = ‘ORA-0000’).
Declare
Begin
dbms_output.enable;
dbms_output.put_line(‘SQLCODE: ‘ || to_char(SQLCODE));
dbms_output.put_line(‘SQLERRM: ‘ || SQLERRM);
End;
/
SQLCODE: 0
SQLERRM: ORA-0000: exécution normale, terminé avec succès
Exemple
Declare
Class_Rec
Class%ROWTYPE;
Begin
dbms_output.enable;
select * into Class_Rec
from class
Exception
when OTHERS then
dbms_output.put_line(‘SQLCODE: ‘ || to_char(SQLCODE));
dbms_output.put_line(SQLERRM);
End;
/
SQLCODE: -1422
ORA-01422: l’extraction exacte ramène plus que le nombre de lignes demandé
Procédure PL/SQL terminée avec succès.