Les procédures et fonctions stockées en PL/SQL
Les procédures et fonctions stockées en PL/SQL
Programmation, Mathématiques · course
Fiche de révisionFiche de cours
Rédigée à partir de ce document, puis vérifiée avant publication.
Ce matériel couvre les notions fondamentales des procédures et fonctions en PL/SQL, en distinguant les sous-programmes stockés (autonomes) et non stockés (non autonomes). Il s’adresse aux étudiants et développeurs souhaitant maîtriser la création, l’utilisation et les avantages des procédures et fonctions dans un environnement Oracle.
Les procédures et fonctions stockées sont des sous-programmes PL/SQL autonomes, compilés et conservés dans la base de données Oracle. Ils sont créés à l’aide des commandes CREATE OR REPLACE PROCEDURE ou CREATE OR REPLACE FUNCTION. Ces objets peuvent être appelés directement par les utilisateurs ou indirectement via d’autres programmes.
CREATE [OR REPLACE] PROCEDURE nom_procédure
[(argument1 [mode_passage1] type1, ..., argumentN [mode_passageN] typeN)]
IS
-- Section déclarative optionnelle (sans mot-clé DECLARE)
[déclaration variables locales]
BEGIN
-- Section exécutable obligatoire
[instructions]
EXCEPTION
[gestion des exceptions]
END [nom_procédure];
Modes de passage des paramètres :
IN : paramètre en entrée (par défaut)OUT : paramètre en sortieIN OUT : paramètre en entrée et sortieCREATE 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épartements est : ' || nbre);
END;
DECLARE
nb NUMBER;
BEGIN
add_dept(300, 'IT', nb);
END;
CREATE [OR REPLACE] FUNCTION nom_fonction
[(argument1 [mode_passage1] type1, ..., argumentN [mode_passageN] typeN)]
RETURN type_retour IS
-- Section déclarative optionnelle (sans mot-clé DECLARE)
[déclaration variables locales]
BEGIN
-- Section exécutable obligatoire
[instructions]
EXCEPTION
[gestion des exceptions]
END [nom_fonction];
Note : Tous les paramètres d’une fonction sont en mode IN par défaut et ce mode peut être omis.
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
RETURN FALSE;
END IF;
END;
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;
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;
SELECT fn_dept_name(100) FROM dual;
user_objects. On peut les consulter avec :SELECT object_name, object_type FROM user_objects;
user_source. Par exemple :SELECT * FROM user_source WHERE name = 'FN_DEPT_NAME';
DESCRIBE permet d’examiner les arguments et le type de retour d’une fonction ou procédure :DESCRIBE fn_check_sal;
Les procédures et fonctions non stockées sont déclarées à l’intérieur d’un bloc PL/SQL, d’un autre sous-programme ou d’un package. Elles ne sont pas enregistrées dans la base de données avec une commande CREATE OR REPLACE.
PROCEDURE nom_procédure
[(argument1 [mode_passage1] type1, ..., argumentN [mode_passageN] typeN)]
IS
-- Section déclarative optionnelle (sans mot-clé DECLARE)
[déclaration variables locales]
BEGIN
-- Section exécutable obligatoire
[instructions]
EXCEPTION
[gestion des exceptions]
END [nom_procédure];
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;
FUNCTION nom_fonction
[(argument1 [mode_passage1] type1, ..., argumentN [mode_passageN] typeN)]
RETURN type_retour IS
-- Section déclarative optionnelle (sans mot-clé DECLARE)
[déclaration variables locales]
BEGIN
-- Section exécutable obligatoire
[instructions]
EXCEPTION
[gestion des exceptions]
END [nom_fonction];
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;