Les procédures et fonctions stockées en PL/SQL

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 (autonomes)

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.

Avantages des procédures stockées

• Efficacité : elles minimisent les appels SQL côté client en regroupant plusieurs instructions SQL côté serveur.
• Réutilisabilité : une procédure stockée peut être utilisée dans différents contextes (SQL, déclencheurs, applications).
• Portabilité : elles sont indépendantes du système d’exploitation ou du compilateur.
• Maintenabilité : en centralisant le code, elles réduisent les coûts de maintenance.

Syntaxe d’une procédure stockée

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 sortie
• IN OUT : paramètre en entrée et sortie

Exemple de procédure stockée

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épartements est : ' || nbre);
END;

Appel d’une procédure stockée

DECLARE
  nb NUMBER;
BEGIN
  add_dept(300, 'IT', nb);
END;

Syntaxe d’une fonction stockée

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.

Exemple 1 de fonction stockée

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;

Exemple 2 de fonction stockée

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;

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;

SELECT fn_dept_name(100) FROM dual;

Remarques sur les objets stockés

• Les objets créés (tables, procédures, fonctions) sont enregistrés dans la table user_objects. On peut les consulter avec :

SELECT object_name, object_type FROM user_objects;

• Le code source des procédures et fonctions stockées est conservé dans la table user_source. Par exemple :

SELECT * FROM user_source WHERE name = 'FN_DEPT_NAME';

• La commande 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 (non autonomes)

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.

Syntaxe d’une procédure non stockée

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];

Exemple de procédure non stockée

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;

Syntaxe d’une fonction non stockée

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];

Exemple de fonction non stockée

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;

Glossaire des termes clés

• Procédure stockée : sous-programme PL/SQL autonome enregistré dans la base de données et pouvant être appelé par plusieurs outils.
• Fonction stockée : sous-programme PL/SQL autonome qui retourne une valeur et est stocké dans la base de données.
• Procédure non stockée : procédure déclarée dans un bloc PL/SQL sans être enregistrée dans la base de données.
• Fonction non stockée : fonction déclarée dans un bloc PL/SQL sans être enregistrée dans la base de données.
• Mode IN : paramètre passé en entrée uniquement.
• Mode OUT : paramètre passé en sortie uniquement.
• Mode IN OUT : paramètre passé en entrée et modifiable en sortie.
• DBMS_OUTPUT.PUT_LINE : procédure Oracle utilisée pour afficher du texte dans la console SQL.
• Clause CREATE OR REPLACE : commande SQL permettant de créer ou de remplacer un objet dans la base de données.
• Table USER_OBJECTS : table système contenant les objets créés par l’utilisateur.
• Table USER_SOURCE : table système contenant le code source des objets PL/SQL.

Points clés à retenir

• Les procédures et fonctions stockées sont autonomes, compilées et stockées dans la base Oracle.
• Les procédures et fonctions non stockées sont déclarées dans des blocs PL/SQL et ne sont pas enregistrées dans la base.
• Les procédures peuvent avoir des paramètres en mode IN, OUT ou IN OUT, tandis que les fonctions ont uniquement des paramètres en mode IN.
• Les procédures ne retournent pas de valeur, contrairement aux fonctions qui retournent une valeur via la clause RETURN.
• Les procédures et fonctions stockées améliorent l’efficacité, la réutilisabilité, la portabilité et la maintenabilité du code.
• Le code source et les métadonnées des objets stockés sont accessibles via les tables système USER_SOURCE et USER_OBJECTS.
• La commande DESCRIBE permet d’examiner la signature des procédures et fonctions stockées.