Cours SQL-PL/SQL : Procédures, Fonctions et Paquetages

Définitions et Avantages des Objets Stockés

  • Objets PL/SQL : Les procédures et fonctions sont des blocs de code nommés et stockés dans la base de données après compilation.
  • Différence fondamentale : Une procédure exécute un traitement spécifique (action), tandis qu'une fonction doit obligatoirement retourner une valeur via la clause RETURNRETURN.
  • Centralisation : Le code est stocké côté serveur, ce qui allège les échanges client-serveur et facilite le partage entre applications.
  • Performance : Le code est pré-compilé, évitant l'analyse syntaxique à chaque exécution.
  • Sécurité : Permet d'autoriser l'exécution d'un traitement sans donner d'accès direct aux tables sous-jacentes.

Gestion des Privilèges

  • Création : GRANT CREATE PROCEDURE TO USERGRANT\,CREATE\,PROCEDURE\,TO\,USER (dans son propre schéma) ou CREATE ANY PROCEDURECREATE\,ANY\,PROCEDURE (n'importe quel schéma).
  • Exécution : GRANT EXECUTE ON objet TO utilisateurGRANT\,EXECUTE\,ON\,objet\,TO\,utilisateur.

Paramètres et Syntaxe

  • Modes de passage :
    • ININ : Passage par valeur, le paramètre est en lecture seule dans le bloc.
    • OUTOUT : Passage par adresse, permet de retourner des valeurs au programme appelant.
    • IN OUTIN\,OUT : Permet de fournir une valeur d'entrée et de récupérer une valeur modifiée.
    • NOCOPYNOCOPY : Option pour IN OUTIN\,OUT qui améliore les performances en évitant la copie locale des grands objets (TABLETABLE, RECORDRECORD).
  • AUTHID :
    • DEFINERDEFINER (Défaut) : Exécution avec les droits du créateur.
    • CURRENT_USERCURRENT\_USER : Exécution avec les droits de l'utilisateur qui appelle l'objet.
  • Compilation : L'ordre CREATE OR REPLACECREATE\,OR\,REPLACE compile et stocke le pseudo-code dans la base.

Utilisation des Fonctions dans le SQL

  • Emplacements autorisés : SELECTSELECT, WHEREWHERE, HAVINGHAVING, ORDER BYORDER\,BY, GROUP BYGROUP\,BY, VALUESVALUES, et UPDATE SETUPDATE\,SET.
  • Restrictions : Les fonctions appelées en SQL ne peuvent pas être intégrées dans une contrainte CHECKCHECK ni manipuler des types spécifiques à PL/SQL. Elles ne doivent pas contenir d'ordres UPDATEUPDATE, INSERTINSERT ou DELETEDELETE si elles sont appelées dans une requête de sélection.

Paquetages (Packages)

  • Structure : Composé de deux parties distinctes :
    1. La Spécification : Déclarations publiques des variables, procédures et fonctions accessibles.
    2. Le Corps (Body) : Implémentation réelle du code et des éléments privés.
  • Modularité : Permet de regrouper logiquement des fonctionnalités liées.
  • Persistance : Les variables déclarées dans un paquetage conservent leurs valeurs pendant toute la durée de la session utilisateur.
  • Surcharge (Overloading) : Possibilité de définir plusieurs sous-programmes avec le même nom au sein d'un même package, à condition que leurs paramètres (nombre ou type) diffèrent.

Maintenance des Objets

  • Recompilation : ALTER PROCEDURE nom COMPILEALTER\,PROCEDURE\,nom\,COMPILE ou ALTER FUNCTION nom COMPILEALTER\,FUNCTION\,nom\,COMPILE.
  • Suppression : DROP PROCEDURE nomDROP\,PROCEDURE\,nom ou DROP FUNCTION nomDROP\,FUNCTION\,nom. Pour les packages : DROP PACKAGE BODY nomDROP\,PACKAGE\,BODY\,nom ou DROP PACKAGE nomDROP\,PACKAGE\,nom.