Les procédures et fonctions stockées (autonomes)
Les procédures et fonctions non stockées (non autonomes)
PL / SQL
Les sous-programmes: procédures et fonctions
Ines BAKLOUTI
Ecole Supérieure Privée d’Ingénierie et de Technologies
Les procédures et fonctions stockées (autonomes)
Les procédures et fonctions non stockées (non autonomes)
Plan
1 Les procédures et fonctions stockées (autonomes)
Les procédures stockées
Les fonctions stockées
2 Les procédures et fonctions non stockées (non autonomes)
Les procédures non stockées
Les fonctions non stockées
2/18
Les procédures et fonctions stockées (autonomes)
Les procédures et fonctions non stockées (non autonomes)
Introduction
Les fonctions et procédures sont des sous-programmes considérées
comme des blocs PL/SQL nommés.
Les fonctions et procédures stockées sont des sous-programmes
PL/SQL autonomes qui sont compilés et stockées dans le
dictionnaire de données à l’aide d’une clause CREATE OR
REPLACE FUNCTION / PROCEDURE.
Une fonction ou procédure non autonome peut être déclarée dans
un bloc PL/SQL, un sous-programme ou un package sans utiliser la
clause CREATE OR REPLACE.
3/18
Les procédures et fonctions stockées (autonomes)
Les procédures et fonctions non stockées (non autonomes)
Les procédures stockées
Les fonctions stockées
Plan
1 Les procédures et fonctions stockées (autonomes)
Les procédures stockées
Les fonctions stockées
2 Les procédures et fonctions non stockées (non autonomes)
Les procédures non stockées
Les fonctions non stockées
4/18
Les procédures et fonctions stockées (autonomes)
Les procédures et fonctions non stockées (non autonomes)
Les procédures stockées
Les fonctions stockées
Les procédures et fonctions stockées (autonomes)
Une procédure (ou fonction) autonome ou stockée est un
sous-programme PL/SQL qui est conservé dans une base de données
ORACLE et appelé par un utilisateur, directement ou indirectement.
AVANTAGES
Efficacité : minimiser l’appel des requêtes SQL côté client en les
remplaçant par des appels de procédures côté serveur intégrant
plusieurs instructions SQL
Réutilisabilité : une procédure stockée peut être utilisée dans diverses
situations (SQL,déclencheur,application)
Portabilité : une procédure stockée est indépendante de la version
système d’exploitation ou des compilateurs
Maintenabilité : en appelant la même procédure à partir de plusieurs
outils (SQL*PLUS, application, autre procédure stockée) on réduit le
coût de maintenance de cette procédure centralisée.
5/18
Les procédures et fonctions stockées (autonomes)
Les procédures et fonctions non stockées (non autonomes)
Les procédures stockées
Les fonctions stockées
Les procédures stockées
Syntaxe
CREATE [OR REPLACE] PROCEDURE nom procédure
[(argument1 [mode passage1] type1. . . ,[argumentN [mode passageN] typeN])]
IS
–Section déclarative optionelle et sans utiliser le mot clé DECLARE
[déclaration variables locales]
BEGIN
Publicité
–Section exécutable obligatoire
[section exception]
END [nom procédure];
Pour une procédure, il y a trois modes de passage de paramètre:
IN : en entrée (par défaut)
OUT : en sortie
IN OUT : en entrée et sortie
6/18
Les procédures et fonctions stockées (autonomes)
Les procédures et fonctions non stockées (non autonomes)
Les procédures stockées
Les fonctions stockées
Les procédures stockées
Exemple
CREATE OR REPLACE PROCEDURE add dept (dept id IN
departments.department id%TYPE, dept name IN
departments.department name%TYPE, nbre OUT number)
IS
BEGIN
insert into departments(department id,department name)
values(dept id,dept name);
commit;
select count(*) into nbre from departments;
dbms output.put line(’Le nombre de département est : ’||nbre);
END;
7/18
Les procédures et fonctions stockées (autonomes)
Les procédures et fonctions non stockées (non autonomes)
Les procédures stockées
Les fonctions stockées
Les procédures stockées
Appel de procédures stockées
Exemple
DECLARE
nb number;
BEGIN
add dept(300,’IT’,nb);
end;
8/18
Les procédures et fonctions stockées (autonomes)
Les procédures et fonctions non stockées (non autonomes)
Les procédures stockées
Les fonctions stockées
Les fonctions stockées
Syntaxe
CREATE [OR REPLACE] FUNCTION nom fonction
[(argument1 [mode passage1] type1. . . ,[argumentN [mode passageN] typeN])]
RETURN type retour IS
–Section déclarative optionelle et sans utiliser le mot clé DECLARE
[déclaration variables locales]
BEGIN
–Section exécutable obligatoire
[section exception]
END [nom fonction];
Tous les paramètres d’une fonction sont en mode IN (dans ce cas,
on n’est pas obligé d’écrire le mode).
9/18
Les procédures et fonctions stockées (autonomes)
Les procédures et fonctions non stockées (non autonomes)
Les procédures stockées
Les fonctions stockées
Les fonctions stockées
Exemple 1
CREATE OR REPLACE FUNCTION fn check sal (empno employees.employee id%TYPE)
RETURN Boolean IS
dept id employees.department id%TYPE;
sal employees.salary%TYPE;
avg sal employees.salary%TYPE;
BEGIN
SELECT salary,department id INTO sal,dept id FROM employees WHERE employee id=empno;
SELECT avg(salary) INTO avg sal FROM employees WHERE department id=dept id;
IF sal > avg sal THEN
RETURN TRUE;
ELSE
Publicité
RETURN FALSE;
END IF;
END;
10/18
Les procédures et fonctions stockées (autonomes)
Les procédures et fonctions non stockées (non autonomes)
Les procédures stockées
Les fonctions stockées
Les fonctions stockées
Exemple 2
CREATE OR REPLACE FUNCTION fn dept name (deptno number)
RETURN varchar IS
dname varchar(30);
BEGIN
select department name into dname from departments where
department id=deptno;
RETURN dname;
END;
11/18
Les procédures et fonctions stockées (autonomes)
Les procédures et fonctions non stockées (non autonomes)
Les procédures stockées
Les fonctions stockées
Les fonctions stockées
Appel de fonctions stockées
Exemple 1
BEGIN
IF (fn check sal(124)) THEN
DBMS OUTPUT.PUT LINE(’Salary > average’);
ELSE
DBMS OUTPUT.PUT LINE(’Salary < average’);
END IF;
END;
Exemple 2
SELECT department id No dept, fn dept name(department id)
Nom dept,last name Nom, first name Prenom FROM employees order by
department id;
12/18
SELECT fn dept name(100) FROM dual;
Les procédures et fonctions stockées (autonomes)
Les procédures et fonctions non stockées (non autonomes)
Les procédures stockées
Les fonctions stockées
Remarques
Lors de la création d’un objet quelconque, tel qu’une table, une
procédure ou une fonction, les entrées correspondantes sont créées
dans la table user objects. vous pouvez examiner le contenu de la
table user objects en exécutant la commande suivante:
SELECT object name,object type FROM user objects;
Le code source d’une procédure ou fonction stockées est enregistré
dans la table user source. Vous pouvez examiner le code source de
la procédure en exécutant la commande suivante:
SELECT * FROM user source WHERE name=’FN DEPT NAME’;
Utilisez la commande DESCRIBE afin d’examiner les arguments et le
type de données renvoyé par une fonction ou procédure.
Exemple : DESCRIBE fn check sal;
13/18
Les procédures et fonctions stockées (autonomes)
Les procédures et fonctions non stockées (non autonomes)
Les procédures non stockées
Les fonctions non stockées
Plan
1 Les procédures et fonctions stockées (autonomes)
Les procédures stockées
Les fonctions stockées
2 Les procédures et fonctions non stockées (non autonomes)
Les procédures non stockées
Les fonctions non stockées
14/18
Les procédures et fonctions stockées (autonomes)
Les procédures et fonctions non stockées (non autonomes)
Les procédures non stockées
Les fonctions non stockées
Les procédures non stockées
Publicité
Syntaxe
PROCEDURE nom procédure
[(argument1 [mode passage1] type1. . . ,[argumentN [mode passageN] typeN])]
IS
–Section déclarative optionelle et sans utiliser le mot clé DECLARE
[déclaration variables locales]
BEGIN
–Section exécutable obligatoire
[section exception]
END [nom procédure];
15/18
Les procédures et fonctions stockées (autonomes)
Les procédures et fonctions non stockées (non autonomes)
Les procédures non stockées
Les fonctions non stockées
Les procédures non stockées
Exemple
DECLARE
a number:=10;
b number:=30;
s number;
p number;
PROCEDURE myproc(a in number, b in number, surf out number, peri out number)
IS
BEGIN
surf:=a*b;
peri:=(a+b)*2;
END myproc;
BEGIN
myproc(a,b,s,p);
dbms output.put line(’La surface: ’||s);
dbms output.put line(’Le périmètre: ’||p);
END;
16/18
Les procédures et fonctions stockées (autonomes)
Les procédures et fonctions non stockées (non autonomes)
Les procédures non stockées
Les fonctions non stockées
Les fonctions non stockées
Syntaxe
FUNCTION nom fonction
[(argument1 [mode passage1] type1. . . ,[argumentN [mode passageN] typeN])]
RETURN type retour IS
–Section déclarative optionelle et sans utiliser le mot clé DECLARE
[déclaration variables locales]
BEGIN
–Section exécutable obligatoire
[section exception]
END [nom fonction];
17/18
Les procédures et fonctions stockées (autonomes)
Les procédures et fonctions non stockées (non autonomes)
Les procédures non stockées
Les fonctions non stockées
Les fonctions non stockées
Exemple
DECLARE
a number:=10;
b number:=30;
c number;
FUNCTION myfunc(a number, b number)
RETURN number IS
surf number;
BEGIN
surf:=a*b;
return surf;
END;
BEGIN
c:=myfunc(a,b);
dbms output.put line(’La surface: ’||c);
END;
18/18