Votre navigateur n'est pas à jour !

Merci de mettre à jour votre navigateur pour utiliser mon blog. Mettre à jour mon navigateur maintenant

×

Base de données - Créer un package PL/SQL

Date de publication 11 oct. 2016
Oracle utilise l’implémentation SQL nommé Procédural Language/SQL tandis que pour SQL Server c’est Transact SQL.
Le PL/SQL permet d'associer des requêtes SQL et des instructions procédurales (boucles, conditions...) dans des procédures stockées ou des déclencheurs.

Une procédure permet de faire du traitement mais ne retourne pas de données (contrairement aux fonctions). Ces procédures peuvent être regroupées sous format de package afin de les placer dans un namespace commun.
L’intérêt d'une procédure stockée est qu'elle est pré compilée donc plus rapide à exécuter, mais aussi plus sécurisé car l'utilisateur n'accède pas au table directement.

Les paramètres d'une procédure sont
  • IN : paramètre d'entrée qui est considéré comme une constante dans la procédure. C'est ce type qui est défini par défaut
  • OUT : paramètre de sortie qui est initialisé à NULL. Il se comporte comme une variable dans la procédure
  • IN OUT : paramètre d'entrée qui sera mis à jour dans la procédure pour retourner sa nouvelle valeur
Un truc pratique et qui est utilisé en python, c'est l'alias des paramètres afin de ne pas mettre 4 fois NULL si on souhaite saisir que le 5ème paramètre.
pck.proc(param5 => 'gordon'); 
Il est possible de combiner écriture traditionnelle et via les alias :
pck.proc(param1, param2, param5 => 'gordon');

Dans la signature on retrouve ce genre de déclaration
FUNCTION proc(param1 VARCHAR2, param2 VARCHAR2, param3 VARCHAR2, param4 VARCHAR2, param5 VARCHAR2 := 'gordon') RETURN VARCHAR2;
Et dans le body du package
FUNCTION proc(param1 VARCHAR2, param2 VARCHAR2, param3 VARCHAR2, param4 VARCHAR2, param5 DEFAULT 'gordon')

Nous allons voir comment faire le modèle du package.
Le BEGIN END permet de construire un block dans PL/SQL.
Le DECLARE initialise les variables utilisées dans le bloc.
Le / sur une nouvelle ligne à la fin du bloc sert à exécuter le contenu du buffer.
DECLARE
...
BEGIN
...
END;
/

Le ; dit à SQLPlus d'exécuter la requête et de la stocker dans le buffer. Il ne sert pas pour un bloc isolé.
J'ai pour habitude de travailler avec des CURSOR. C'est un pointeur d'une zone mémoire qui stocke les informations de la requête.
CURSOR MyCursor IS (
...
)
On peut ensuite travailler sur le curseur, avec par exemple des informations sur l'existant de donnée : %NOTFOUND

A l'intérieur de ce curseur j'utilise des clauses WITH
CURSOR MyCursor IS
  WITH ONE AS (
   SELECT ...
  ), TWO AS (
   SELECT ..
  )

  SELECT * FROM ONE INNER JOIN TWO ...

Si vous êtes amené à traiter beaucoup de données il est intéressant d'utiliser des BULK COLLECT.
BULK COLLECT permet de récupérer plusieurs enregistrements à partir d'une seule requête dans le but d'améliorer les performances sur une volumétrie importante.
Pour cela nous allons commencer par
  • Créer un Type_Cursor qui est de TYPE correspond à un enregistrement de MyCursor
    TYPE Type_Cursor IS TABLE OF MyCursor%ROWTYPE INDEX BY BINARY_INTEGER;
  • On déclare ensuite un Bulk_Cursor de TYPE Type_Cursor
    Bulk_Cursor Type_Cursor;
  • On ouvre notre curseur
    OPEN MyCursor
  • On récupère les données par groupe défini après LIMIT
    FETCH MyCursor BULK COLLECT INTO Bulk_Cursor LIMIT 10000;
Avec ce concept et associé au FORALL on peut itérer sur un groupe d'enregistrement.
FORALL IDX IN INDICES OF Bulk_Cursor
    INSERT INTO MyTable
    (
        ID,
        ...
    )
    VALUES
    (
        Bulk_Cursor(IDX).ID,
        ...
    );
    COMMIT;    

    Bulk_Cursor.DELETE;

Et voilà un petit modèle prêt à l'emploi
blog comments powered by Disqus