Les crashs dev d'un passionné

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