Archive

Articles taggués ‘Oracle’

Utilisation avancée des procédures stockées en PL SQL

Utilisation avancée des procédures stockées en PL SQL

Passage de paramètres et récupération de tableaux.

Introduction

L’utilisation des procédures stockées est considérée par la plupart des développeurs comme une bonne pratique; celles-ci présentent effectivement plusieurs intérêts :

  • Elles sont bien séparées du code serveur et peuvent être optimisées en parallèle par des spécialistes en SQL
  • Elles offrent un contexte d’exécution plus sécurisé
  • Il est possible de transmettre des informations plus complexe qu’en passant par une requête en dur dans le code
  • On sait exactement ce que l’on doit donner en entrée et ce que l’on aura en retour
  • Côté serveur, le code les appelant n’est pas nécessairement plus concis mais est plus lisible

Bien sûr, il est possible de rencontrer des projets où elles ne sont pas utilisées. Bien souvent, il s’agit de projets où il y a une maitrise limitée de l’interface entre celles-ci et le langage serveur (le C# en l’occurrence). C’est d’ailleurs à juste titre ; l’approche n’est pas nécessairement des plus intuitives et, aussi bien au niveau de leur écriture que de leur appel, il y a certaines subtilités qui sont à connaitre.

Cet article est une première approche assez détaillée des principaux écueils que l’on peut rencontrer. Il se focalise sur l’écriture et l’utilisation des procédures stockées dans un contexte purement PL/SQL mais en vue de les utiliser depuis un serveurs développé en C#. Les explications concernant ces appels feront l’objet d’un second article (à paraitre bientôt).

Concepts généraux

Déclarations des types hors package :

Il y a deux façons différentes de déclarer de nouveau type dans une base Oracle :

  • Soit hors d’un package, dans la liste des types directement accessibles à l’ensemble de la base.
  • Soit à l’intérieur d’un package, auquel cas, il faudra désigner le nom du package dans lequel il est déclaré pour pouvoir l’utiliser en dehors.

Attention : Les types objets composés doivent être déclarés hors du package, sinon le code C# ne sait pas y accéder. (Comme nous le verrons, le mapping est géré différemment dans ce cas.)

Pour les mêmes raisons, les listes d’objets composés doivent absolument être déclarées hors du package.

Remarque : Déclarer des tables d’objets personnalisés peut être utile pour requêter plus facilement sur celles-ci. Il est inutile de déclarer des objets n’ayant qu’une propriété pour de les intégrer directement dans une requête car il est possible d’accéder à l’unique colonne avec column_value, comme nous le verrons. Sauf  si l’on pense que l’on ne sera amené à ajouter de nouvelles propriétés par la suite.

Code PL/SQL


-- Objet composé
create or replace TYPE MON_OBJET AS OBJECT
(
  PROP_NUM NUMBER,
  PROP_STR VARCHAR2(10)
);

-- Liste d’objets composés
create or replace TYPE MON_OBJET_TABLE AS TABLE OF MON_OBJET;


-- Liste d’objets système
TYPE GLOBAL_NUMBER_TABLE AS TABLE OF NUMBER;

Le package peut déclarer localement certaines informations comme certains types, les déclarations de procédures stockées ou autres fonctions.

Cependant, les listes/tables ne sont pas systématiquement à déclarer ici si on veut que la procédure stockée puisse être appelée par le code C# :

  • Dans le cas des listes d’objets système, on peut déclarer le type table localement dans le package, mais cela implique une manipulation assez fastidieuse dans la procédure (Et attention à l’appel depuis PL SQL : cf. : Tests d’une procédure stockée)
  • Dans le cas de listes d’objets composés personnalisés, on doit déclarer le type hors du package.

Les listes déclarées dans le package doivent être indexées.

Code PL/SQL :


create or replace PACKAGE PKG_STORPROC IS

  -- Les curseurs doivent être déclarés localement
  TYPE refCursor IS REF CURSOR;

  -- Liste d’objets système
  -- Attention : le  INDEX BY BINARY_INTEGER est très important!
  TYPE NUMBER_TABLE AS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

  -- Déclaration d’une procédure avec paramètres
  PROCEDURE SP_TEST(
    ParamStr IN VARCHAR2,
    ParamNum IN NUMBER,
    ListeNum IN NUMBER_TABLE,-- Local au package
    ListeNumGlobale IN GLOBAL_NUMBER_TABLE,-- Externe au package
    ParamCustom IN MON_OBJET,-- Externe au package
    ListeCustom IN MON_OBJET_TABLE, -- Externe au package
    Result OUT refCursor
  );

END PKG_STORPROC;

Définition du package :

Pour utiliser des types personnalisés au sein du corps d’un package, il suffit de les manipuler comme les autres.

Code PL/SQL


create or replace PACKAGE BODY PKG_STORPROC IS

  PROCEDURE SP_TEST(
    ParamStr IN VARCHAR2,
    ParamNum IN NUMBER,
    ListeNum IN NUMBER_TABLE,
    ListeNumGlobale IN GLOBAL_NUMBER_TABLE,
    ParamCustom IN MON_OBJET,
    ListeCustom IN MON_OBJET_TABLE,
    DetailResult OUT refCursor
  )
  IS

  BEGIN

    sys.dbms_output.put_line(‘ParamStr: ' || ParamStr || ' - ParamCustom. PROP_NUM: ' || ParamCustom.PROP_NUM);

    IF TripIds.First IS NOT NULL THEN

      sys.dbms_output.put_line(‘ListeCustom. First. PROP_NUM: ' || ListeCustom.First.PROP_NUM);

    END IF;

  END;

END PKG_STORPROC;

Retourner des résultats

En utilisant un Ref Cursor

Pour récupérer un tableau de résultats lors d’un appel à procédure stockée, il est possible d’utiliser un REF CURSOR. Cela a l’avantage de pouvoir s’adapter à différents contextes d’exécution.

Celui-ci doit être absolument déclaré dans l’entête du package.

Le retour est indiqué par le mot clé OUT dans les paramètres.

Code PL SQL


--------------------------------------------------------------------
-- Dans l’entete du package

create or replace PACKAGE PKG_STORPROC IS

  TYPE refCursor IS REF CURSOR;

  PROCEDURE SP_TEST_SIMPLE (ParamNum IN NUMBER, resultat OUT refCursor);

END PKG_STORPROC;


--------------------------------------------------------------------
-- Dans le corps du package

create or replace PACKAGE BODYPKG_STORPROC IS

  PROCEDURE SP_TEST_SIMPLE (
    ParamNum IN NUMBER,
    Resultat OUT refCursor
  )
  IS

  BEGIN
  
  -- On ouvre le curseur pour insérer les résultats dedans
  OPEN Resultat FOR
    SELECT
      t.StrVal as Name,
      t.NumVal as Age,
      t.LongStrVal as Adresse
    FROM FOURRE_TOUT t
    WHERE t.Cat = ParamNum;

  END;

END PKG_STORPROC;

En utilisant un tableau

Pour récupérer un tableau de résultats lors d’un appel à procédure stockée, il est également possible d’utiliser un tableau d’objets personnalisés. Mais cela a l’inconvénient d’être assez spécifique à un cas d’utilisation et le fait que ce type soit déclaré hors du package pollue un peu l’environnement.

Le retour est indiqué par le mot clé OUT dans les paramètres.

Code PL SQL

--------------------------------------------------------------------
-- Dans l’entete du package

create or replace PACKAGE PKG_STORPROC IS

  PROCEDURE SP_TEST_SIMPLE_TABL (ParamNum IN NUMBER, resultat OUT MON_OBJET_TABLE);

END PKG_STORPROC;


--------------------------------------------------------------------
-- Dans le corps du package

create or replace PACKAGE BODYPKG_STORPROC IS

  PROCEDURE SP_TEST_SIMPLE_TABL (
    ParamNum IN NUMBER,
    Resultat OUT MON_OBJET_TABLE
  )
  IS

  BEGIN

    -- On ouvre le curseur pour insérer les résultats dedans
    SELECT
      t.NumVal,
      t.StrVa
    BULK COLLECT INTO Resultat
    FROM FOURRE_TOUT t
    WHERE t.Cat = ParamNum;

  END;

END PKG_STORPROC;

Passage de paramètres

Passage d’une liste de types simples en paramètre

L’intérêt de passer une liste à une procédure stockée serait de pouvoir faire une requête directement sur celle-ci, en l’utilisant comme une table.

Déclaration du type à l’intérieur du package

Cela est possible de manière indirecte en transformant la liste passée en paramètre en une table locale. Pour cela, il faut déclarer un autre type table contenant des objets pour lesquels on pourra accéder aux propriétés.

Cette façon de procéder est plus fastidieuse côté PLSQL mais demandera moins de développement côté C#.

Notes :

  • Attention à l’appel depuis PL SQL : cf. : Tests d’une procédure stockée
  • les performances semblent légèrement moins bonnes avec cette façon de procéder.

Code PLSQL


-- Hors package, pour que les types génériques puissent être utilisés dans d’autres packages :

-- Objet simple permettant de l’inclure dans une requête
create or replace TYPE MON_OBJET_NOMBRE AS OBJECT
(
  PROP_NUM NUMBER
);


-- Liste d’objets
create or replace TYPE TABLE_DE_NOMBRE AS TABLE OF MON_OBJET_NOMBRE;


--------------------------------------------------------------------
-- Dans l’entête du package
create or replace PACKAGE PKG_STORPROC IS

  TYPE refCursor IS REF CURSOR;

  -- Liste d’objets systèmes
  -- Attention : le  INDEX BY BINARY_INTEGER est très important!
  TYPE NUMBER_TABLE AS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

  PROCEDURE SP_TEST_LISTE_SIMPLE ( ListeNum IN NUMBER_TABLE, resultat OUT refCursor );

END PKG_STORPROC;


--------------------------------------------------------------------
-- Dans le corps du package
create or replace PACKAGE BODYPKG_STORPROC IS

  PROCEDURE SP_TEST_LISTE_SIMPLE(
  ListeNum IN NUMBER_TABLE,-- Local au package, pour l’appel extérieur
  Resultat OUT refCursor
  )
  IS

    numTable TABLE_DE_NOMBRE:= TABLE_DE_NOMBRE ();-- Externe au package pour les manipulation internes

  BEGIN

    -- On commence par recréer une table dans un environnement qui permet de l’utiliser et avec une structure plus facile à manipuler.
    numTable.Extend(ListeNum.count);

    FOR i IN ListeNum.First .. ListeNum.Last
    LOOP
      numTable(i) := MON_OBJET_NOMBRE(ListeNum(i));
    END LOOP;

    -- On peut ensuite l’utiliser comme une table dès lors qu’on l’a castée.
    SELECT * FROM table(numTable) nt where nt.PROP_NUM = 1 ;

  END;

END PKG_STORPROC;

Déclaration en dehors du package

En déclarant le type table en dehors du package, cela permet de le convertir simplement en une table dans le code PLSQL. Cette table ne doit pas être indexée, contrairement à la précédente.

Cette façon de procéder est plus simple côté PLSQL mais demandera plus de développement côté C#.

Notes :

  • Attention à l’appel depuis PL SQL : cf. : Tests d’une procédure stockée
  • les performances semblent légèrement moins bonnes avec cette façon de procéder.

Code PLSQL


-- Hors package, pour que les types génériques puissent être utilisés dans d’autres packages :

-- Liste de nombre
-- On passe cette fois directement par une table de Number pour montrer comment accéder à la colonne sans nom.
create or replace TYPE GLOBAL_NUMBER_TABLE AS TABLE OF NUMBER;


--------------------------------------------------------------------
-- Dans l’entête du package
create or replace PACKAGE PKG_STORPROC IS

  TYPE refCursor IS REF CURSOR;

  PROCEDURE SP_TEST_LISTE_SIMPLE_HP ( numberTable IN GLOBAL_NUMBER_TABLE, resultat OUT refCursor );

END PKG_STORPROC;


--------------------------------------------------------------------
-- Dans le corps du package
create or replace PACKAGE BODYPKG_STORPROC IS

  PROCEDURE SP_TEST_LISTE_SIMPLE(
    numberTable IN GLOBAL_NUMBER_TABLE,-- Externe au package, manipulable directement
    Resultat OUT refCursor
  )
  IS

  BEGIN

    -- On peut directement l’utiliser comme une table dès lors qu’on l’a castée.
    SELECT * FROM table(numberTable) nt where nt.column_value = 1 ;

  END;

END PKG_STORPROC;

Paramètres d’objets de types utilisateur

Les paramètres de type Object se manipulent assez simplement, comme les paramètres système.

Ils peuvent être déclarés dans ou hors du package sans que cela ait une incidence négative sur leur accessibilité côté C#.

Code PLSQL


-- Hors package, pour que les types génériques puissent être utilisés dans d’autres packages :

-- Objet composé
create or replace TYPE MON_OBJET AS OBJECT
(
  PROP_NUM NUMBER,
  PROP_STR VARCHAR2(10)
);

-- Dans l’entete du package
create or replace PACKAGE PKG_STORPROC IS

  TYPE refCursor IS REF CURSOR;

  PROCEDURE SP_TEST_OBJET (paramObjet IN MON_OBJET, resultat OUT refCursor);

END PKG_STORPROC;


-- Dans le corps du package
create or replace PACKAGE BODYPKG_STORPROC IS

  PROCEDURE SP_TEST_OBJET (
    paramObjet IN MON_OBJET,
    resultat OUT refCursor
  )
  IS

  BEGIN

    -- On peut ensuite l’utiliser comme un paramètre classique...
    sys.dbms_output.put_line('ParamCustom.PROP_NUM: ' || ParamCustom.PROP_NUM);

  END;

END PKG_STORPROC;

Liste d’objets de types utilisateur

Tout comme pour les listes d’objets système, le but est d’utiliser cette entrée comme une table.

Là encore, il faut transformer la liste passée en paramètre en une table locale. Mais il est inutile, cette fois, de recourir à une nouvelle structure de table.

Elles peuvent être déclarées dans ou hors du package sans que cela ait une incidence négative sur leur accessibilité.

Code PLSQL


-- Hors package, pour que les types génériques puissent être utilisés dans d’autres packages :
-- Objet composé
CREATE OR REPLACE TYPE MON_OBJET AS OBJECT
(
  PROP_NUM NUMBER,
  PROP_STR VARCHAR2(10)
);

-- Liste d’objets composés
CREATE OR REPLACE TYPE MON_OBJET_TABLE AS TABLE OF MON_OBJET;

-- Dans l’entete du package
CREATE OR REPLACE PACKAGE PKG_STORPROC
IS

  TYPE refCursor IS REF CURSOR;

  PROCEDURE SP_TEST_LISTE_OBJET(
        paramListeObjets IN MON_OBJET_TABLE,
        resultat OUT refCursor
  );

END PKG_STORPROC;


-- Dans le corps du package
CREATE OR REPLACE PACKAGE BODY PKG_STORPROC
IS

  PROCEDURE SP_TEST_LISTE_ OBJET(
    paramListeObjets IN MON_OBJET_TABLE,
    resultat OUT refCursor )
  IS
    tableObjetsLocale MON_OBJET_TABLE:= MON_OBJET_TABLE ();
  BEGIN
    -- On commence par recréer la table dans un environnement qui permet de l’utiliser.
    tableObjetsLocale.Extend(paramListeObjets.count);

    FOR i IN paramListeObjets.First .. paramListeObjets.Last
    LOOP
      tableObjetsLocale (i) := MON_OBJET(paramListeObjets(i).PROP_NUM, paramListeObjets(i).PROP_STR);
    END LOOP;

    -- On peut ensuite l’utiliser comme une table dès lors qu’on l’a castée.
    SELECT nt.PROP_STR
      FROM TABLE(tableObjetsLocale) nt
      WHERE nt.PROP_NUM = 1 ;

  END;

END PKG_STORPROC;

Tests d’une procédure stockée côté PL SQL

La seule subtilité à indiquer réside dans la création de paramètres de type liste d’objets système quand ils sont déclarés dans le package. En effet, dans ce cas uniquement, il ne faut pas « créer » le tableau après l’avoir déclaré.

Le résultat peut être affiché, en utilisant le debugger

Code PL SQL


DECLARE
  ParamStr VARCHAR2;
  ParamNum NUMBER;
  ListeNum NUMBER_TABLE;
  ParamCustom MON_OBJET;
  ListeCustom MON_OBJET_TABLE;

  Result refCursor;

BEGIN

  ParamStr := 'valeur';
  ParamNum := 1;

  -- Rien à faire pour initialiser ListeNum
  ListeNum(1) := 1;
  ListeNum(2) := 2;

  ParamCustom := MON_OBJET(0, 'valeur objet');
  ListeCustom := MON_OBJET_TABLE();
  ListeCustom(1) := MON_OBJET(10, 'val obj');
  ListeCustom(2) := MON_OBJET(20, 'val obj2');

  PKG_STORPROC.SP_TEST(
    ParamStr => ParamStr,
    ParamNum => ParamNum,
    ListeNum => ListeNum,
    ParamCustom => ParamCustom,
    ListeCustom => ListeCustom,
    Result => Result
  );

END;