Objets de la base utilisant PL/SQL

Les déclencheurs de bases de données

Un déclencheur ou trigger est un bloc PL/SQL associé à une table. Ce bloc s’exécutera lorsqu’une instruction du DML (INSERT, UPDATE, DELETE) sera demandée sur la table.

Le bloc PL/SQL qui constitue le trigger peut être exécuté avant ou après la mise à jour et donc avant ou après vérification des contraintes d’intégrité.

Les triggers offrent une solution procédurale pour définir des contraintes complexes ou qui prennent en compte des données issues de plusieurs lignes ou de plusieurs tables. Par exemple, pour garantir le fait qu’un client ne peut pas avoir plus de deux commandes non payées. Toutefois, les triggers ne doivent pas êtres utilisés lorsqu’il est possible de mettre en place une contrainte d’intégrité. En effet, les contraintes d’intégrité étant définies au niveau de la table et faisant partie de la structure de la table, la vérification du respect de ces contraintes est beaucoup plus rapide. De plus, les contraintes d’intégrité garantissent que toutes les lignes contenues dans la tables respectent ces contraintes, tandis que les triggers ne prennent pas en compte les données déjà présentes dans la table lorsqu’ils sont définis.

Le bloc PL/SQL associé au trigger peut être exécuté pour chaque ligne affectée par l’ordre DML (option FOR EACH ROW), ou bien une seule fois pour chaque commande DML exécutée (option par défaut).

Exécution avant ou après vérification des contraintes d’intégrité pour chaque ligne ou chaque ordre.

Dans les triggers BEFORE et FOR EACH ROW, il est possible de modifier les données qui vont être insérées dans la table pour qu’elles respectent les contraintes d’intégrité. Il est également possible de faire des requêtes de type SELECT sur la table sur laquelle porte l’ordre DML uniquement dans le cadre d’un trigger BEFORE INSERT. Toutes ces opérations sont impossibles dans les triggers AFTER car après vérification des contraintes d’intégrité, il n’est pas possible de modifier...

Les triggers sur des événements système ou utilisateur

Il est possible d’utiliser les triggers pour suivre les changements d’état du système, tels que l’arrêt et le démarrage. Ces triggers vont permettre d’améliorer la gestion de la base et de l’application. En effet, certains événements systèmes ou utilisateurs ont un impact direct sur les performances de l’application et/ou la cohérence des données.

Les événements système sont l’arrêt et le démarrage de l’instance Oracle (startup et shutdown) et la gestion des erreurs. Les triggers d’arrêt et de démarrage ont comme portée l’ensemble de l’instance Oracle, tandis que le trigger de gestion des erreurs peut être défini soit au niveau du schéma soit au niveau de la base de données.

Au niveau de l’utilisateur, des triggers peuvent exister pour ses opérations de connexion et de déconnexion (logon et logoff), pour surveiller et contrôler l’exécution des ordres du DDL (CREATE, ALTER et DROP) et du DML (INSERT, UPDATE et DELETE).

Lors de l’écriture de ces triggers, il est possible d’utiliser des attributs pour identifier précisément l’origine de l’événement et adapter les traitements en conséquence.

Les modifications de triggers

Il n’est pas possible de modifier un trigger de base de données. Par contre, il est possible de le supprimer puis de le récréer ou bien de le créer avec l’option OR REPLACE.

La commande ALTER TRIGGER va permettre en réalité de désactiver puis de réactiver les triggers. La désactivation peut être envisagée lors d’un import massif de données ou bien d’une modification de grande envergure.

La désactivation et la réactivation de trigger peuvent être réalisées trigger par trigger ou bien table par table. En effet, la commande ALTER TABLE permet l’activation et la désactivation de tous les triggers posés sur la table.

Syntaxe

ALTER TRIGGER nom_trigger {ENABLE|DISABLE}; 
ALTER TABLE nom_trigger { ENABLE|DISABLE } ALL TRIGGERS;
 

Exemple

Désactivation puis réactivation de triggers :

Pour obtenir des informations sur les triggers il faut interroger le dictionnaire de données. Les trois vues du dictionnaire à utiliser sont : USER_TRIGGERS, ALL_TRIGGERS et DBA_TRIGGERS.

La colonne BASE_OBJECT_TYPE permet de savoir si le trigger est basé sur une table, une vue, un schéma ou la totalité de la base de données.

La colonne TRIGGER_TYPE permet de savoir s’il s’agit d’un trigger BEFORE, AFTER ou INSTEAD...

Les procédures stockées

Une procédure stockée est un bloc de code PL/SQL nommé stocké dans la base de données et qui peut être exécuté à partir des applications ou d’autres procédures stockées. Dans un bloc PL/SQL, il suffit de référencer la procédure par son nom pour l’exécuter. À partir de l’outil SQL*Plus, on peut utiliser l’instruction EXECUTE.

Syntaxe

CREATE [OR REPLACE] PROCEDURE nom procédure 
[(paramètre {IN/OUT/IN OUT} type, ...)] 
{IS/AS} bloc PL/SQL ;
 

OR REPLACE

paramètre

IN

OUT

type

Exemple

Procédure de suppression d’un article :

SQL> create or replace procedure supp_art (numero in char) is 
  2    begin 
  3    delete from ligcdes where refart=numero; 
  4    delete from articles where refart=numero; 
  5    end; 
  6  / 
 
Procédure créée. 
 
SQL>
 

Utilisation par SQL*Plus...

Les fonctions stockées

Comme les procédures, une fonction est un ensemble de code PL/SQL, mais la fonction renvoie une valeur. Ces fonctions stockées seront utilisées comme les fonctions Oracle.

Syntaxe

CREATE [OR REPLACE] FUNCTION nom de fonction 
[(paramètre [IN] type, ...)] 
RETURN type {IS/AS} Bloc PL/SQL ;
 

OR REPLACE

paramètre

type

RETURN type

Exemple

Fonction factorielle :

CREATE FUNCTION factorielle (n IN NUMBER) 
     RETURN NUMBER 
     IS BEGIN 
           if n = 0 then 
                return (1) ; 
           else 
                return ((n * factorielle (n-1))) ;  
           end if ; 
         END ;
 

Utilisation par SQL*Plus :

SQL> Select factorielle (5) from DUAL ; 
FACTORIELLE (5) 
--------------- 
    120 
SQL>
 

Depuis la version 11, il est possible d’indiquer à Oracle de conserver en mémoire le résultat de l’appel d’une fonction. Lorsque cette fonctionnalité est activée pour une fonction, à chaque fois que celle-ci est appelée avec des valeurs différentes des paramètres, Oracle stocke en cache la valeur des paramètres et le résultat de la fonction. Lorsque cette fonction est de nouveau appelée ultérieurement avec les mêmes valeurs...

Les packages

Un package est un objet du schéma qui regroupe logiquement des éléments PL/SQL liés, tels que les types de données, les fonctions, les procédures et les curseurs.

Les packages se divisent en deux parties : un en-tête ou spécification et un corps (body). La partie spécification permet de décrire le contenu du package, de connaître le nom et les paramètres d’appel des fonctions et des procédures. Mais le code n’est pas présent dans la spécification. On trouve le code dans la partie body du package. Cette séparation des spécifications et du code permet de déployer un package sans que l’utilisateur puisse visualiser le code et permet de plus de faire évoluer simplement le code pour répondre à de nouvelles règles.

Les packages offrent de nombreux avantages :

Modularité

Simplification du développement

Informations cachées

Ajout de fonctionnalités

Amélioration des performances

Les transactions autonomes

Une transaction est un ensemble d’ordres SQL qui constitue une unité logique de traitement. Soit la totalité des instructions de l’unité est exécutée avec succès soit aucune instruction n’est exécutée. Dans certaines applications, une transaction doit s’exécuter à l’intérieur d’une autre transaction.

Une transaction autonome est une transaction indépendante qui est lancée depuis une autre transaction : la transaction principale. Durant l’exécution de la transaction autonome, la transaction principale est suspendue.

Les transactions autonomes sont totalement indépendantes, et cette indépendance permet de construire des applications plus modulaires. Bien sûr, les transactions autonomes présentent les mêmes caractéristiques que les transactions régulières.

Pour définir une transaction autonome, il faut utiliser la directive de compilation (pragma) AUTONOMOUS_TRANSACTION. Cette directive doit apparaître dans la section de déclaration des variables, des blocs PL/SQL anonymes, des fonctions, des procédures et des triggers. En règle générale, les directives de compilation sont placées en début de la section de déclaration des variables, ce qui facilite la relecture du programme.

SQL dynamique

Le SQL dynamique est une technique qui permet de construire des ordres SQL de façon dynamique à l’exécution de code PL/SQL. Le SQL dynamique permet de créer des applications plus souples car les noms des objets utilisés par un bloc PL/SQL peuvent être inconnus au moment de la compilation. Par exemple, une procédure peut travailler avec une table dont le nom est inconnu avant l’exécution de la procédure.

Il faut rappeler que dans le SQL statique toutes les informations sont connues au moment de la compilation et bien sûr les ordres SQL statiques ne changent pas d’une exécution à une autre. Toutefois cette solution offre des avantages car le succès de la compilation garantit que les ordres SQL font référence à des objets valides de la base de données. La compilation vérifie également que les privilèges nécessaires sont en place pour accéder et travailler avec les objets de la base de données. De plus, la performance du code SQL statique est de meilleure qualité que celle du SQL dynamique.

À cause de tous ces avantages, le SQL dynamique ne doit être utilisé que si le SQL statique ne peut pas répondre à nos besoins, ou bien si la solution en statique est beaucoup plus compliquée qu’en SQL dynamique.

Cependant, le SQL statique possède certaines limites qui sont dépassées par le SQL dynamique. On choisira le SQL dynamique si par exemple on ne connaît pas à l’avance les ordres SQL qui vont devoir être exécutés par le bloc PL/SQL, ou bien si l’utilisateur doit donner des informations qui vont venir construire les ordres SQL à exécuter.

De plus, par l’intermédiaire du SQL dynamique, il devient possible d’exécuter des instructions du DDL (CREATE, ALTER, DROP, GRANT et REVOKE) ainsi que les commandes ALTER SESSION et SET ROLE à l’intérieur du code PL/SQL, ce qui est impossible avec le SQL statique.

Le SQL dynamique sera donc utilisé pour répondre à l’un des points suivants :

Collections et enregistrements

Dans le chapitre précédent, la déclaration et l’initialisation des collections et des enregistrements ont été abordées. Ce type de variable est très pratique et une bonne connaissance de leur utilisation permet souvent de gagner du temps lors de l’écriture de traitements en PL/SQL.

L’utilisation des tableaux en PL/SQL n’est pas forcément évidente au premier abord. En effet, pourquoi s’obliger à utiliser une telle structure alors que pour stocker un ensemble d’informations, il est très facile de créer une table temporaire.

La raison est très simple : en stockant l’ensemble des données sous forme de collection directement dans le bloc PL/SQL, tous les traitements peuvent être effectués par le moteur PL/SQL. En limitant les requêtes SQL, on limite les accès à la base, ce qui permet d’accélérer le temps de traitement du bloc PL/SQL, mais on limite également l’occupation du moteur SQL et les requêtes des autres utilisateurs peuvent donc être traités plus rapidement.

On voit donc qu’il existe des bénéfices à travailler avec les collections même si dans un premier temps le code PL/SQL à mettre en place est un peu plus compliqué. Il est à noter que l’instruction FORALL (vue plus loin dans ce chapitre) permet de faciliter considérablement les étapes de codages nécessaire pour pouvoir travailler avec les collections dans un bloc PL/SQL.

nom_collection (indice)
 

La copie des données par blocs

Complètement intégré au SGBDR Oracle, le moteur procédural PL/SQL traite toutes les commandes procédurales et le moteur SQL s’occupe de traiter toutes les commandes SQL. L’interaction entre les deux moteurs est fréquente car le code PL/SQL travaille avec des données issues de la base de données, et extraites par l’intermédiaire de commandes SQL.

Lors de chaque passage du moteur PL/SQL au moteur SQL, et inversement, une charge de travail supplémentaire est demandée au serveur. Afin d’améliorer les performances, il est important de réduire ce nombre de changements de moteur. Les copies de données par blocs offrent une solution pour réduire le nombre d’interactions entre ces deux moteurs.

Répartition du travail entre les deux moteurs :

Avec les copies par blocs, les ordres SQL vont pouvoir concerner toute la collection et non seulement les éléments les uns après les autres.

L’exemple suivant, qui concerne l’insertion de lignes dans une table, permet de comparer le gain de temps entre le traitement classique des ordres SQL dans les blocs PL/SQL et le traitement par blocs.

Création de la table Composants :

La table créée ci-dessus va être remplie par l’intermédiaire d’un bloc PL/SQL. Pour chacune des deux méthodes d’insertion, le temps d’exécution sera mesuré.

Les avantages du traitement par blocs :

FORALL index IN borne_inférieur..borne_supérieur 
commande_SQL;
 

Fonctions et ensembles de lignes

Il est maintenant possible de définir en PL/SQL des fonctions qui acceptent en paramètres d’entrée et/ou qui retournent non pas une valeur simple mais un ensemble de lignes. L’avantage de ces fonctions est qu’il n’est alors plus nécessaire de stocker les données dans une table temporaire avant d’appeler l’exécution de la fonction. Il est possible d’utiliser de telles fonctions à tous les endroits où il est possible de faire référence à un nom de table et notamment avec la clause FROM d’une requête SELECT.

Afin d’améliorer les temps de réponses de ces fonctions qui retournent un ensemble de données, l’instruction pipelined précise que les données vont être retournées au fur et à mesure de l’exécution de la fonction. C’est d’ailleurs avec ce type de fonction que la gestion des ensembles de valeurs renvoyées est la plus simple.

Lors de la déclaration de la fonction, le mot clé PIPELINED est ajouté dans l’en-tête et les informations sont retournées à l’aide de la commande PIPE ROW.

Ces fonctions peuvent accepter en paramètre d’entrée un ensemble de lignes sous la forme d’une collection (comme un tableau de type VARRAY) ou bien sous la forme...

DBMS_OUTPUT

Le package DBMS_OUTPUT permet d’envoyer des messages depuis une procédure, une fonction, un package ou un déclencheur (trigger) de base de données. Les procédures PUT et PUT_LINE de ce package permettent de placer des informations dans un tampon qui pourra être lu par un autre bloc PL/SQL qui utilisera la procédure GET_LINE pour récupérer l’information.

DBMS_OUTPUT.ENABLE (taille_tampon IN INTEGER...

Le package UTL_FILE

Le package PL/SQL UTL_FILE permet aux programmes PL/SQL de travailler en lecture et écriture avec des fichiers texte du système de fichiers. Le flux d’entrée/sortie ouvert vers le système d’exploitation est limité au seul travail avec ces fichiers.

Côté serveur, l’exécution du programme PL/SQL qui travaille avec le package UTL_FILE, doit être réalisée dans un mode de sécurité privilégié, et d’autres options de sécurité limitent la possibilité des actions menées au travers de UTL_FILE.

Historiquement, côté serveur, les répertoires accessibles par UTL_FILE devaient être précisés dans le fichier des paramètres (INIT.ORA) par l’intermédiaire du paramètre UTL_FILE_DIR.

Syntaxe

UTL_FILE_DIR=c:\temp.
 

À partir la version 9i il est préférable d’utiliser la commande CREATE DIRECTORY pour gérer les répertoires accessibles depuis le package UTL_FILE à la place du paramètre d’initialisation UTL_FILE_DIR.

Il est possible de connaître la liste des répertoires définis sur le serveur à l’aide de la vue ALL_DIRECTORIES.

Syntaxe

CREATE OR REPLACE DIRECTORY nom_repertoire AS 'chemin';
 

Le package DBMS_LOB

Les initiales LOB permettent d’identifier les objets de grande dimension (Large OBject).

Le travail avec les éléments de grande dimension (BLOB : Binary LOB, CLOB : Character LOB, NCLOB : uNicode CLOB et BFILE : fichier binaire) n’est pas aussi facile qu’avec les données de type plus classique (caractère, nombre, date). Le langage PL/SQL permet de travailler avec ces données à partir du moment où elles sont présentes dans la base mais les opérations de chargement depuis un fichier du système d’exploitation de comparaison ou de modification ne peuvent être réalisées qu’à l’aide du package DBMS_LOB.

file_readonly  CONSTANT BINARY_INTEGER     :=0; 
lob_readonly   CONSTANT BINARY_INTEGER     :=0; 
lob_readwrite  CONSTANT BINARY_INTEGER     :=1; 
lobmaxsize     CONSTANT INTEGER            :=18446744073709551615; 
call           CONSTANT PLS_INTEGER        :=12; 
session        CONSTANT PLS_INTEGER        :=10;
 

DBMS_LOB.APPEND( 
destination  IN OUT NOCOPY BLOB, 
source    IN BLOB); 
DBMS_LOB.APPEND( 
destination  IN OUT NOCOPY   CLOB CHARACTER SET ANY_CS, 
source    IN      CLOB  CHARACTER SET destination%CHARSET);
 

DBMS_LOB.CLOSE( 
{lob_origine    IN OUT NOCOPY BLOB 
| lob_origine    IN OUT NOCOPY CLOB CHARACTER SET ANY CS 
| fichier_origine   IN OUT NOCOPY BFILE);