Oracle - Retour de jeux de résultats (Result Sets) par Procédures Stockées PL/SQL

Logo

Introduction

À la différence des moteurs tels que Sybase et SQL Server, les procédures stockées sous Oracle ne retournent pas par défaut des jeux de résultats. Or les procédures stockées présentent un avantage considérable pour mettre en œuvre des tables temporaires globales (GLOBAL TEMPORARY TABLES) ou d’autres objets et ceci dans le but d’éviter des requêtes SQL dont la syntaxe peut être lourde et difficilement optimisable au niveau des plans d’exécutions.

Cette documentation technique montre comment faire retourner des jeux de résultats par des procédures stockées. Cela peut s’appliquer à des tous les outils (PowerBuilder) ou langages de programmation (PHP, Perl, Pro*C…). Une illustration avec PHP est présentée. Pour des outils tels que PowerBuilder, la mise en œuvre est beaucoup plus simple.

Méthodologie générale

Dans tous les cas de figure, les jeux de résultats sont retournés par une procédure stockée grâce à un curseur, ce dernier curseur étant obligatoirement défini au sein d’un package.

Le curseur peut être défini de deux manières différentes :

  • soit au sein d’un package global destiné à l’implémentation de variables globales accessibles par des procédures stockées créées unitairement (hors package) : PKG_GLOBAL_VARIABLES;
  • soit au sein d’un package dédié (package applicatif) où le curseur sera utilisé par des procédures stockées définies dans ce même package : exemple PKG_RISKUSERS.

Dans cette documentation technique, le curseur retourné par les procédures stockées s’appelle cursor_RISKUSERS.

Package de variables globales :

CREATE OR REPLACE PACKAGE PKG_GLOBAL_VARIABLES AS
    TYPE record_RISKUSERS IS RECORD (
        IDENT RISKUSERS.IDENT%TYPE,
        NAME RISKUSERS.NAME%TYPE
    );

    TYPE cursor_RISKUSERS ISREF CURSOR RETURN record_RISKUSERS;
END PKG_GLOBAL_VARIABLES;

Package applicatif :

CREATE OR REPLACE PACKAGE PKG_RISKUSERS AS
    TYPE record_RISKUSERS IS RECORD (
        IDENT RISKUSERS.IDENT%TYPE,
        NAME RISKUSERS.NAME%TYPE
    );
    
    TYPE cursor_RISKUSERS ISREF CURSOR RETURN record_RISKUSERS;

    PROCEDURE sp_USERS(p_cursor IN OUT cursor_RISKUSERS);

    PROCEDURE sp_USERSBYNAME(a_name IN VARCHAR2,
      p_cursor IN OUT cursor_RISKUSERS);
      
END PKG_RISKUSERS;
    
    CREATE OR REPLACE PACKAGE BODY PKG_RISKUSERS AS
    ...
    ...

Procédures stockées unitaires

Dans cet exemple, un jeu de résultats est retourné par deux procédures stockées unitaires (procédures stockées compilées en dehors de tout package) :

  • sp_getUSERSRISK
  • sp_getUSERSRISKBYNAME

La première procédure stockée retourne tous les utilisateurs (colonnes ident, name) de la table RISKUSERS.

La seconde procédure stockée retourne les utilisateurs (colonnes ident, name) dont le nom commence par une valeur.

Dans le cas de jeux de résultats retournés par des procédures stockées unitaires, le curseur des résultats (cursor_RISKUSERS) doit être défini au sein d’un package de variables globales :

Afin que les procédures stockées sp_getUSERSRISK et sp_getUSERSRISKBYNAME soient en mesure de retourner le jeu de résultats, ces dernières doivent avoir en paramètre IN/OUT une variable du type PKG_GLOBAL_VARIABLES.cursor_RISKUSERS.

CREATE OR REPLACE PROCEDURE <PROCEDURE>
([...,] p_cursor IN OUT PKG_GLOBAL_VARIABLES.cursor_RISKUSERS [,...] )
AS ...

Codes sources des procédures stockées

Code source de la procédure stockée sp_getUSERSRISK

CREATE OR REPLACE PROCEDURE sp_getRISKUSERS
(p_cursor IN OUT PKG_GLOBAL_VARIABLES.cursor_RISKUSERS )
AS BEGIN
    OPEN p_cursor FOR
      SELECT ident, name FROM RISKUSERS ORDER BY name;
END;

Code source de la procédure stockée sp_getUSERSRISKBYNAME


CREATE OR REPLACE PROCEDURE sp_getRISKUSERSBYNAME
(a_name IN varchar2,
  p_cursor IN OUT PKG_GLOBAL_VARIABLES.cursor_RISKUSERS
)
AS BEGIN
     OPEN p_cursor FOR
       SELECT ident, name FROM RISKUSERS WHERE name like a_name||'%' ORDER BY name;
END;

Exécution SQL*Plus

L’option autoprint permet de déterminer l’affichage automatique ou non du jeu de résultats de la procédure stockée

  • sans option autoprint
variable c_users refcursor
exec sp_getRISKUSERS(:c_users);
print c_users;
exec sp_getRISKUSERSBYNAME('S',:c_users);
print c_users;
  • avec option autoprint
set autoprint on;
variable c_users refcursor
exec sp_getRISKUSERS(:c_users);
exec sp_getRISKUSERSBYNAME('S',:c_users);

Procédures stockées packagées

Dans cet exemple, un jeu de résultats est retourné par deux procédures stockées packagées (procédures stockées compilées au sein d’un package PKG_RISKUSERS) :

  • sp_USERS
  • sp_USERSBYNAME

La première procédure stockée retourne tous les utilisateurs (colonnes ident, name) de la table RISKUSERS.

La seconde procédure stockée retourne les utilisateurs (colonnes ident, name) dont le nom commence par une valeur.

Dans le cas de jeux de résultats retournés par des procédures stockées unitaires, le curseur des résultats (cursor_RISKUSERS) est défini au sein du package applicatif PKG_RISKUSERS contenant :

CREATE OR REPLACE PACKAGE PKG_RISKUSERS
AS
   TYPE record_RISKUSERS IS RECORD (
       IDENT RISKUSERS.IDENT%TYPE,
       NAME RISKUSERS.NAME%TYPE
   );
   TYPE cursor_RISKUSERS IS REF CURSOR RETURN record_RISKUSERS;
   PROCEDURE sp_USERS(p_cursor IN OUT cursor_RISKUSERS);
   PROCEDURE sp_USERSBYNAME(a_name IN VARCHAR2, p_cursor IN OUT cursor_RISKUSERS);
END PKG_RISKUSERS;

CREATE OR REPLACE PACKAGE BODY PKG_RISKUSERS AS
PROCEDURE sp_USERS(p_cursor IN OUT cursor_RISKUSERS)
IS
BEGIN
      OPEN p_cursor FOR
       SELECT ident, name
       FROM RISKUSERS
       ORDER BY name;
END sp_USERS;

PROCEDURE sp_USERSBYNAME(a_name IN VARCHAR2, p_cursor IN OUT cursor_RISKUSERS)
IS
BEGIN
      OPEN p_cursor FOR
       SELECT ident, name
       FROM RISKUSERS
       WHERE name like a_name||'%'
       ORDER BY name;
END sp_USERSBYNAME;
END PKG_RISKUSERS;

Afin que les procédures stockées sp_USERSRISK et sp_USERSBYNAME soient en mesure de retourner le jeu de résultats, ces dernières doivent avoir en paramètre IN/OUT la variable cursor_RISKUSERS définie au sein du package applicatif.

CREATE OR REPLACE PROCEDURE <PROCEDURE>
([...,] p_cursor IN OUT cursor_RISKUSERS [,...] )
AS ...

Exécution SQL*Plus

L’option autoprint permet de déterminer l’affichage automatique ou non du jeu de résultats de la procédure stockée.

  • sans option autoprint
variable c_users refcursor
exec PKG_RISKUSERS.sp_USERS(:c_users);
print c_users;
exec PKG_RISKUSERS.sp_USERSBYNAME('S',:c_users);
print c_users;
  • avec option autoprint
set autoprint on;
variable c_users refcursor
exec PKG_RISKUSERS.sp_USERS(:c_users);
exec PKG_RISKUSERS.sp_USERSBYNAME('S',:c_users);

Exemple avec PHP 4 / 5

Connexion Oracle

La connexion est établie en langage avec la fonction ociplogon retournant un objet connexion.

ociplogin('USER','PASSWORD','ORACLE_SID')

Si ORACLE_SID n’est pas spécifié, PHP recherche ORACLE_SID dans les variables d’environnement.

<?php
  // Connexion Oracle
  $conn = ociplogon("RISK","RISK","RISKD");

  if (! $conn) {
     "Connexion a Oracle en échec"; die(); }
  else { echo "Connexion OK... "; }
  
?>

Récupération du jeu des procédures stockées unitaires

sp_getRISKUSERS

<?php
  
    ...
    $curs= OCINewCursor($conn);
    $stmt = OCIParse($conn,"begin sp_getRISKUSERS(:p_usersRISK); end;");
    OCIBindByName($stmt,":p_usersRISK",$curs,-1,OCI_B_CURSOR);

    OCIExecute($stmt);
    OCIExecute($curs);

    echo "Recuperation SQL d'une procédure stockée simple sans paramètres<BR>";
    echo "<TABLE class=\"lstcontent\">";
    echo "<TR class=\"lstheader\"><TD>Ident.</TD><TD>Name</TD></TR>";

    while(OCIFetchInto($curs,$usersRISK,OCI_ASSOC)) {
      echo "<TR><TD>";
      echo $usersRISK['IDENT'];
      echo "</TD><TD>";
      echo $usersRISK['NAME'];
      echo< "</TD></TR>";
    }
    
    echo "</TABLE><BR><BR>";
    
    ocifreestatement($stmt);
    ocifreestatement($curs);
    
?>

sp_getRISKUSERSBYNAME

<?php
  
    ...
  
    $curs= OCINewCursor($conn);
    $stmt= OCIParse($conn,"begin sp_getRISKUSERSBYNAME(:a_name,:p_usersRISK); end;");

    $aname='S';

    OCIBindByName($stmt,":a_name",$aname,32);
    OCIBindByName($stmt,":p_usersRISK",$curs,-1,OCI_B_CURSOR);
    OCIExecute($stmt);
    OCIExecute($curs);

    echo "Recuperation SQL d'une procédure stockée simple avec 1 paramètre<BR>";
    echo "<TABLE class=\"lstcontent\">";
    echo "<TR class=\"lstheader\"><TD>Ident.</TD><TD>Name</TD></TR>";
    
    while(OCIFetchInto($curs,$usersRISK,OCI_ASSOC)){
      echo "<TR><TD>";
      echo $usersRISK['IDENT'];
      echo "</TD><TD>";
      echo $usersRISK['NAME'];
      echo "</TD></TR>";
    }
    echo "</TABLE><BR><BR>";

    ocifreestatement($stmt);
    ocifreestatement($curs);
    
?>

Récupération du jeu des procédures stockées packagées

PKG_RISKUSERS.sp_USERS

<?php
  
    ...
  
    $curs = OCINewCursor($conn);
    $stmt = OCIParse($conn,"begin PKG_RISKUSERS.sp_USERS(:p_usersRISK); end;");

    OCIBindByName($stmt,":p_usersRISK",$curs,-1,OCI_B_CURSOR);
    OCIExecute($stmt);
    OCIExecute($curs);

    echo "Recuperation SQL d'une procédure stockée packagée sans paramètres<BR>";
    echo "<TABLE class=\"lstcontent\">";
    echo "<TR class=\"lstheader\"><TD>Ident.</TD><TD>Name</TD></TR>";

    while(OCIFetchInto($curs,$usersRISK,OCI_ASSOC)) {
      echo "<TR><TD>";
      echo $usersRISK['IDENT'];
      echo "</TD><TD>";
      echo $usersRISK['NAME'];
      echo "</TD></TR>";
    }
    echo "</TABLE><BR><BR>";

    ocifreestatement($stmt);
    ocifreestatement($curs);

?>

PKG_RISKUSERS.sp_USERSBYNAME

<?php
  
    ...
  
    $curs=OCINewCursor($conn);
    $stmt=OCIParse($conn,"begin PKG_RISKUSERS.sp_USERSBYNAME(:a_name,:p_usersRISK); end;");

    $aname='S';

    OCIBindByName($stmt,":a_name",$aname,32);
    OCIBindByName($stmt,":p_usersRISK",$curs,-1,OCI_B_CURSOR);

    OCIExecute($stmt);
    OCIExecute($curs);

    echo "Recuperation SQL d'une procédure stockée packagée avec 1 paramètre<BR>";
    echo "<TABLE class=\"lstcontent\">";
    echo "<TR class=\"lstheader\"><TD>Ident.</TD><TD>Name</TD></TR>";
    
    while(OCIFetchInto($curs,$usersRISK,OCI_ASSOC)){
        echo "<TR><TD>";
        echo $usersRISK['IDENT'];
        echo "</TD><TD>";
        echo $usersRISK['NAME'];
        echo "</TD></TR>";
    }
    echo "</TABLE><BR><BR>";

    ocifreestatement($stmt);
    ocifreestatement($curs);
    
?>