	Rechercher: sur developpez.com sur les forums	Forums \| Tutoriels \| F.A.Q's \| Participez \| Hébergement \| Contacts

Emploi

Business Intelligence

Le SQL Dynamique natif

Date de publication : 05/01/2004

Par SheikYerbouti (Page d'accueil)

Le SQL dynamique permet de construire dans un programme une requête SQL avant de l’exécuter.

1. Utilité du SQL dynamique
2. Les avantages du SQL dynamique natif
3. Les avantages du package DBMS_SQL
4. EXECUTE IMMEDIATE
4.1. Dynamic_string
4.2. INTO
4.3. USING
4.4. RETURNING INTO
4.5. Attributs de curseur
5. Exemples de SQL dynamique natif
Remerciements

1. Utilité du SQL dynamique

Le SQL dynamique permet de construire dans un programme une requête SQL avant de l’exécuter. L’utilité principale est de fabriquer un code « générique » et réutilisable. Sans cela, le paramétrage d’une requête se limite aux valeurs de remplacement de la clause where :

CURSOR C_CURSEUR( LN$Id IN EMP.empid%TYPE ) IS
SELECT
	empno
FROM
	EMP
WHERE
	empid = LN$Id
;
FOR C_EMP IN C_CURSEUR( 1024 ) LOOP
…….

Dans cet exemple on voit que seul le numéro de l’employé donne son caractère “paramétrable” à la requête. Ni les colonnes ramenées, ni le nom de la table ne peuvent être paramétrées, pas plus que les termes de la clause where.

CURSOR C_CURSEUR( LN$Id IN EMP.empid%TYPE ) IS
SELECT
	COUNT(*)
FROM
	EMP
WHERE
	empid > LN$Id
;
OPEN  C_CURSEUR( 1024 ) ;
FETCH C_CURSEUR INTO LN$Total ;
CLOSE C_CURSEUR
…….

Dans cet autre exemple qui permet un comptage d’enregistrements, on voit que cette requête ne compte que les enregistrements de la table EMP sur un critère figé (empid > LN$Id).
Dans l’optique d’une procédure qui doit généraliser ce type de comptage sur plusieurs tables, il faut écrire autant de curseurs que de tables visées. Le SQL dynamique permet de s’affranchir de cette limitation en permettant de « construire » l’intégralité des termes d’une requête.

FUNCTION Compte_rec
  (
        PC$Table IN VARCHAR2,
        PC$ClauseWhere IN VARCHAR2
  ) RETURN PLS_INTEGER
IS
  LC$Requete      VARCHAR2(512) ;
  LN$Total        PLS_INTEGER ;
BEGIN
  LC$Requete := ‘Select count(*) From ‘ || PC$Table || ‘ where ‘ || PC$ClauseWhere ;
  EXECUTE IMMEDIATE LC$Requete INTO LN$Total ;
  return LN$Total ;
END ;

Cette fonction utilisant le SQL dynamique est totalement générique puisqu’elle permet d’interroger n’importe quelle table passée en paramètre avec des clauses where totalement différentes d’un appel à l’autre.

LN$Nombre := Compte_rec( ‘EMP’, ‘empid > 1024’ ) ;
LN$Nombre := Compte_rec( ‘EMP’, ‘empname like ‘’MART%’’ ) ;
LN$Nombre := Compte_rec( ‘DEPT’, ‘deptno between 1 and 15’ ) ;
Etc…

Le SQL dynamique permet également d’écrire et de compiler des programmes manipulant des objets qui ne sont pas encore existants au moment de la compilation et d’exécuter des ordres de description de données (CREATE TABLE…).

2. Les avantages du SQL dynamique natif

Codage simplifié par rapport au package DBMS_SQL qui impose de nombreuses fonctions dans un ordre strict.

DBMS_SQL Package

Native Dynamic SQL

CREATE PROCEDURE insert_into_table (
      table_name  VARCHAR2, 
      deptnumber  NUMBER, 
      deptname    VARCHAR2, 
      location    VARCHAR2) IS
   cur_hdl         INTEGER;
   stmt_str        VARCHAR2(200);
   rows_processed  BINARY_INTEGER;
 
BEGIN
   stmt_str := 'INSERT INTO ' || 
      table_name || ' VALUES 
      (:deptno, :dname, :loc)';
 
   -- open cursor
   cur_hdl := dbms_sql.open_cursor;
 
   -- parse cursor
   dbms_sql.parse(cur_hdl, stmt_str,
      dbms_sql.native);
 
   -- supply binds
   dbms_sql.bind_variable
      (cur_hdl, ':deptno', deptnumber);
   dbms_sql.bind_variable
      (cur_hdl, ':dname', deptname);
   dbms_sql.bind_variable
      (cur_hdl, ':loc', location);
 
    -- execute cursor
    rows_processed := 
    dbms_sql.execute(cur_hdl);
 
    -- close cursor
    dbms_sql.close_cursor(cur_hdl);
 
END;

CREATE PROCEDURE insert_into_table (
      table_name  VARCHAR2, 
      deptnumber  NUMBER, 
      deptname    VARCHAR2, 
      location    VARCHAR2) IS
   stmt_str    VARCHAR2(200);
 
BEGIN
   stmt_str := 'INSERT INTO ' || 
      table_name || ' values 
      (:deptno, :dname, :loc)';
 
   EXECUTE IMMEDIATE stmt_str 
      USING 
      deptnumber, deptname, location;
 
END;

Plus grande rapidité d’exécution

En moyenne, le SQL dynamique natif s’exécute de 1.5 à 3 fois plus rapidement.

Support des types définis

Le SQL dynamique natif supporte tous les types définis par l’utilisateur, Objets, collections, etc…

Support des types RECORD pour les ordres Select

Le SQL dynamique natif permet d’utiliser un objet de type RECORD dans une clause INTO

3. Les avantages du package DBMS_SQL

Supporté par le client

Les fonctions du package DBMS_SQL sont supportées par les applications clientes, A l’inverse du SQL dynamique natif.

Support de la clause RETURNING pour les opération UPDATE et DELETE

DBMS_SQL supporte la clause RETURNING pour des opérations UPDATE ou DELETE effectuées sur plusieurs lignes. Le SQL dynamique natif ne supporte une clause RETURNING que sur les opérations ne retournant qu’une seule ligne.

4. EXECUTE IMMEDIATE

Cette fonction est disponible depuis la version Oracle8i

4.1. Dynamic_string

Chaîne de caractères, variable ou expression contenant un ordre SQL ou un block PL/SQL

Dans le cas d’un ordre SQL, le caractère de terminaison( ;) ne doit pas figurer dans la chaîne, alors qu’il est nécessaire pour terminer un block PL/SQL La chaîne contenant l’ordre peut contenir des paramètres de substitution ( :1, :2, etc.). Cela permet la réutilisation du curseur par Oracle

DECLARE
  LC$Requete  VARCHAR2(256) ;
  Emp_id      emp.emp_id%TYPE := 1214 ;
BEGIN
  LC$Requete := ‘CREATE TABLE XX ( ID NUMBER, LIBELLE VARCHAR2(100) )’;
  EXECUTE IMMEDIATE LC$Requete ;
  LC$Requete := ‘BEGIN Mon_package.Ma_procedure( :1 ); END;’;
  EXECUTE IMMEDIATE LC$Requete USING Emp_id;
END ;

4.2. INTO

N’est utilisé que pour les requêtes ne ramenant qu’une ligne.
A chaque colonne ramenée par la requête doit correspondre une variable de type compatible dans la clause INTO.
Une variable de type RECORD peut également être utilisée.

DECLARE
  Emp_id            emp.emp_id%TYPE := 1214 ;
  Emp_name          emp.name_id%TYPE ;
  Emp_rec           emp%ROWTYPE ;
  LC$Requete        VARCHAR2(256) ;
BEGIN
  LC$Requete:= ‘SELECT emp_name from EMP WHERE empno = :1’;
  EXECUTE IMMEDIATE LC$Requete INTO Emp_name USING Emp_id ;

  LC$Requete:= ‘SELECT * from EMP WHERE empno = :1’;
  EXECUTE IMMEDIATE LC$Requete INTO Emp_rec USING Emp_id ;
END ;

Dans la première requête, la valeur de la colonne emp_name de la table EMP est copiée dans la variable locale Emp_name.
Dans la deuxième requête, les valeurs de toutes les colonnes de la table EMP sont copiées dans la variable locale Emp_rec.

4.3. USING

Cette clause définit la liste des arguments de substitution (bind arguments).
Sans spécification de mode de paramètre, le défaut est IN.
Ces arguments ne peuvent pas être de type Boolean (TRUE, FALSE) ni de type NULL.
Pour passer un argument de type NULL, il faut déclarer une variable et lui affecter la valeur NULL.

DECLARE
  Emp_id            emp.emp_id%TYPE := 1214 ;
  Salary            NUMBER(7,2) ;
  LN$Null           NUMBER := NULL ;
  LC$Requete        VARCHAR2(256) ;
BEGIN

  LC$Requete:= ‘SELECT salary from EMP WHERE empno = :1’;
      EXECUTE IMMEDIATE LC$Requete INTO salary USING Emp_id ;
      
  LC$Requete:= ‘UPDATE EMP SET salary = :1 WHERE empno = :2’;
      EXECUTE IMMEDIATE LC$Requete USING NULL, Emp_id ; -- Incorrect
      
  LC$Requete:= ‘UPDATE EMP SET salary = :1 WHERE empno = :2’;
      EXECUTE IMMEDIATE LC$Requete USING LN$Null, Emp_id ; -- Correct
      
END ;

Dans cet exemple, le paramètre de substitution :1 prend la valeur de la variable Emp_id.

4.4. RETURNING INTO

N’est utilisé que pour les ordres de manipulation (DML) dotés d’une clause RETURNING. Cette clause définit la ou les variables dans lesquelles les valeurs de colonnes sont retournées.

DECLARE
  Emp_id            emp.emp_id%TYPE := 1214 ;
  Salary            NUMBER(7,2) ;
  LC$Requete        VARCHAR2(256) ;
BEGIN
  LC$Requete:= ‘UPDATE EMP SET sal = sal * 1.1 WHERE empno = :1 RETURNING sal INTO :2’;
  EXECUTE IMMEDIATE LC$Requete USING Emp_id RETURNING INTO Salary;
END ;

Le SQL dynamique supporte tous les types SQL (Collections, Objets LOB, Instances de types objets, refs) mais aucun type spécifique PL/SQL à l’exception du type RECORD dans une clause INTO.
L’utilisation de paramètres de substitution permet la réutilisation de l’instruction SQL.

LC$Requete:= ‘DELETE FROM EMP WHERE empno = ’ || LN$Id ; -- non réutilisé
LC$Requete:= ‘DELETE FROM EMP WHERE empno = :2’;         -- réutilisé

La chaîne contenant l’ordre peut être entièrement constituée par programmation.

PROCEDURE   Dynamic_Sql
      (
            LC$Colonne  in  VARCHAR2,
            LC$Table    in  VARCHAR2,
            LC$Clause   in  VARCHAR2,
            LN$Pk       in  NUMBER,
            LC$Valeur   out VARCHAR2
      )
IS
  Emp_id            emp.emp_id%TYPE := 1214 ;
  Salary            NUMBER(7,2) ;
  LC$Requete        VARCHAR2(256) ;
BEGIN
LC$Requete:= ‘UPDATE ‘ || LC$Table || ‘ SET ‘ || LC$Colonne || ‘ = ‘ || LC$Valeur || ‘ WHERE empno = :1’;
      EXECUTE IMMEDIATE LC$Requete USING LN$Pk ;
END ;

Seul le nom de la table ne peut pas être passé via un argument de substitution.

DECLARE
  Emp_id            emp.emp_id%TYPE := 1214 ;
  LC$Table          VARCHAR2(30) := ‘EMP’ ;
  LC$Requete        VARCHAR2(256) ;
BEGIN
  LC$Requete:= ‘UPDATE :1 SET salary = 1000 WHERE empno = :2’;
      EXECUTE IMMEDIATE LC$Requete USING LC$Table, Emp_id ; -- Incorrect
      
  LC$Requete:= ‘UPDATE ‘ || LC$Table || ‘ SET salary = 1000 WHERE empno = :2’;
      EXECUTE IMMEDIATE LC$Requete USING Emp_id ; -- Correct
END ;

Pour assurer la compatibilité avec les versions précédentes, les valeurs retournées dans la clause RETURNING INTO peuvent l’être également dans la clause USING.

DECLARE
	LC$Requete VARCHAR2(256);
	my_empno NUMBER(4) := 7902;
	my_ename VARCHAR2(10);
	my_job   VARCHAR2(9);
	my_sal   NUMBER(7,2) := 3250.00;
BEGIN
  LC$Requete := 'UPDATE emp SET sal = :1 WHERE empno = :2 RETURNING ename, job INTO :3, :4';

  /* valeurs retournées dans la clause USING  */
  EXECUTE IMMEDIATE LC$Requete USING my_sal, my_empno, OUT my_ename, OUT my_job;
 
  /* valeurs retournées dans la clause RETURNING INTO */
  EXECUTE IMMEDIATE LC$Requete USING my_sal, my_empno RETURNING INTO my_ename, my_job;
 
END;

Si vous utilisez EXECUTE IMMEDIATE pour exécuter une procédure acceptant un paramètre OUT :

CREATE PROCEDURE create_dept (
   deptno IN OUT NUMBER,
   dname  IN VARCHAR2,
   loc    IN VARCHAR2) AS
BEGIN
   SELECT deptno_seq.NEXTVAL INTO deptno FROM dual;
   INSERT INTO dept VALUES (deptno, dname, loc);
END;

Il faut le spécifier dans la clause USING.

DECLARE
   plsql_block VARCHAR2(500);
   new_deptno NUMBER(2);
   new_dname  VARCHAR2(14) := 'ADVERTISING';
   new_loc    VARCHAR2(13) := 'NEW YORK';
BEGIN
   plsql_block := 'BEGIN create_dept(:a, :b, :c); END;';
   EXECUTE IMMEDIATE plsql_block USING IN OUT new_deptno, new_dname, new_loc;
   IF new_deptno > 90 THEN ...
END;

Requêtes ramenant plusieurs lignes (BULK COLLECT)

Il est possible d’utiliser l’instruction EXECUTE IMMEDIATE pour renseigner une collection (index-by table, nested table, varay).
Les éléments de ces collections doivent être de type SQL (CHAR, DATE,NUMBER,…)

Les 3 instructions supportant le BULK COLLECT dynamique sont :

EXECUTE IMMEDIATE

EXECUTE IMMEDIATE dynamic_string
   [[BULK COLLECT] INTO define_variable[, define_variable ...]]
   [USING bind_argument[, bind_argument ...]]
   [{RETURNING | RETURN} 
   BULK COLLECT INTO bind_argument[, bind_argument ...]];

DECLARE
   TYPE T_EMP_NAME is TABLE of EMP.emp_name%TYPE;
   TYPE T_EMP_SAL  is TABLE of EMP.salary%TYPE;
   T_Names     T_EMP_NAME ;
   T_Sal       T_EMP_SAL ;
   LC$Requete  VARCHAR2(256);
BEGIN
LC$Requete:= ‘SELECT emp_name, salary from EMP’;

    EXECUTE IMMEDIATE LC$Requete BULK COLLECT INTO T_Names, T_sal;

END;

Seules les instructions INSERT, UPDATE ou DELETE peuvent avoir des valeurs retournées dans la clause RETURNING INTO

DECLARE
	TYPE T_EMP_NAME is TABLE of EMP.emp_name%TYPE;
	TYPE T_EMP_SAL  is TABLE of EMP.salary%TYPE;
	T_Names  T_EMP_NAME ;
	T_Sal    T_EMP_SAL ;
	Bonus    NUMBER := 500 ;
	LC$Requete       VARCHAR2(256);
BEGIN
     LC$Requete := 'UPDATE emp SET bonus = :1 RETURNING emp_name INTO :2';
     EXECUTE IMMEDIATE LC$Requete USING bonus RETURNING BULK COLLECT INTO T_Names;
END ;

FETCH

FETCH dynamic_cursor BULK COLLECT INTO define_variable[, define_variable ...];
 

DECLARE
   TYPE T_EMP_ID    is TABLE of EMP.empno%TYPE;
   TYPE T_EMP_NAME  is TABLE of EMP.ename%TYPE;
           TYPE EmpCurTyp   is REF CURSOR;
   Emp_cv      EmpCurTyp ;
   T_Id        T_EMP_ID ;
   T_Nom       T_EMP_NAME ;
   LC$Requete  VARCHAR2(256);
BEGIN
LC$Requete:= ‘SELECT emp_no, ename from EMP’ ;

    OPEN Emp_cv for LC$Requete ;

    FETCH Emp_cv BULK COLLECT INTO T_id, T_Nom ;

    CLOSE Emp_cv ;

END;

FORALL

FORALL index IN lower bound..upper bound
   EXECUTE IMMEDIATE dynamic_string
   USING bind_argument | bind_argument(index)
      [, bind_argument | bind_argument(index)] ...
   [{RETURNING | RETURN} BULK COLLECT 
      INTO bind_argument[, bind_argument ... ]];



DECLARE
   TYPE T_EMP_ID   is TABLE of EMP.empno%TYPE;
   TYPE T_EMP_NAME  is TABLE of EMP.ename%TYPE;
   T_Id        T_EMP_ID ;
   T_Nom       T_EMP_NAME ;
   LC$Requete  VARCHAR2(256);
BEGIN
   LC$Requete:= ‘SELECT emp_no, ename from EMP’;

   EXECUTE IMMEDIATE LC$Requete BULK COLLECT INTO T_Id, T_Nom;

 
    FORALL i IN T_Id.first..T_Id.last
      EXECUTE IMMEDIATE 'UPDATE emp SET sal = sal * 1.1 WHERE empno = :1 RETURNING ename INTO :2'
               USING T_Id(i) RETURNING BULK COLLECT INTO T_Nom;
 
END;

4.5. Attributs de curseur

Les 4 attributs de curseur %FOUND, %ISOPEN, %NOTFOUND, et %ROWCOUNT sont bien évidement valorisés par l’instruction EXECUTE IMMEDIATE.

function    Supprime_lignes ( LN$MaxSal IN NUMBER ) return NUMBER
IS
   LC$Requete        VARCHAR2(256) ;
BEGIN
   LC$Requete:= ‘DELETE FROM EMP WHERE Sal > :1’ ;
   EXECUTE IMMEDIATE LC$Requete USING LN$MaxSal;
   return SQL%ROWCOUNT ;
END ;

5. Exemples de SQL dynamique natif

Changement du paramétrage en cours de session

EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY/MM/DD HH24:MI:SS''' ;

Désactivation d’un trigger basé

EXECUTE IMMEDIATE 'ALTER TRIGGER TRG_BIR_MA_TABLE DISABLE' ;

Modification de structure

EXECUTE IMMEDIATE 'ALTER TABLE MA_TABLE ADD MON_CHAMPS VARCHAR2(100)' ;

Travaux temporaires

DECLARE
   LC$TableTmp VARCHAR2(30) ;
   LN$Col1     NUMBER ;
   LC$Col2     VARCHAR2(100) ;
   LD$Date     DATE ;
BEGIN
   LC$TableTmp := ‘TMP_XX’ ;
 
   LC$Requete := ‘CREATE TABLE ‘ || LC$TableTmp || ‘ ( COL1 NUMBER, COL2 VARCHAR2(100), COL3 DATE )’ ;
   EXECUTE IMMEDIATE LC$Requete ;

   LC$Requete := ‘INSERT INTO ‘ || LC$TableTmp || ‘ ( COL1, COL2, COL3 ) VALUES ( :1, :2, :3 )’ ;
   EXECUTE IMMEDIATE LC$Requete USING LN$Col1, LC$Col2, LD$Date ;

   …
   -- Traitements
   …

   LC$Requete := ‘DROP TABLE ‘ || LC$Table_tmp ;
   EXECUTE IMMEDIATE LC$Requete ;
END ;

Recherche globale d’une chaîne de caractères dans les colonnes d’ une table donnée

CREATE OR REPLACE PACKAGE Pkg_Recherche_globale

 AUTHID CURRENT_USER IS

 /*----------------------------------------*/
 /*               TYPES                    */
 /*----------------------------------------*/
  -- table des ROWID
  TYPE T_ROW IS TABLE OF VARCHAR2(40);

  -- procédure qui renvoie les ROWID des lignes
  PROCEDURE P_Get_Lines( PT_Rows IN OUT T_ROW ,PC$TABLE IN VARCHAR2,PC$WHERE IN VARCHAR2 DEFAULT NULL);
  
END;

/

CREATE OR REPLACE PACKAGE BODY Pkg_Recherche_globale IS


  -- procédure qui renvoie le contenu de la table des ROWID
  PROCEDURE P_Get_Lines( PT_Rows IN OUT T_ROW ,PC$TABLE IN VARCHAR2,PC$WHERE IN VARCHAR2 )
  IS

  LC$Where     VARCHAR2(100) := UPPER(PC$WHERE);

  -- Table des ROWID --
  C_ROW        T_ROW;

 
  TYPE REFCUR1 IS REF CURSOR ;
  cur          REFCUR1;
  LC$SQL VARCHAR2(10000) := 'SELECT ROWID ';
  LC$SQLW      VARCHAR2(10000):= ' ';
  LC$Col VARCHAR2(50);

  -- Colonnes cibles pour Recherche globale --

  CURSOR C_WI ( PC$Tab IN VARCHAR2 ) IS
  SELECT
     COLUMN_NAME,
     DATA_TYPE
  FROM
     USER_TAB_COLUMNS
  WHERE
     TABLE_NAME = PC$Tab
  AND
     DATA_TYPE IN ('CHAR','VARCHAR2','NUMBER','DATE')
  ;

  LN$I   PLS_INTEGER := 1 ;

  BEGIN

   --------------------------------
   -- Construction de la requete --
   --------------------------------

   LN$I := 1 ;

   FOR CWI IN C_WI( PC$Table ) LOOP

     IF LN$I > 1 THEN
        LC$SQLW := LC$SQLW || '||' ;
     END IF ;

     IF CWI.DATA_TYPE = 'NUMBER' THEN
        LC$Col := 'To_char("'|| CWI.COLUMN_NAME || '")' ;
     ELSIF CWI.DATA_TYPE = 'DATE' THEN
        LC$Col := 'To_char("'|| CWI.COLUMN_NAME || '",''DD/MM/YYYY'')' ;
     ELSE
        LC$Col := '"'|| CWI.COLUMN_NAME || '"' ;
     END if ;
     LC$SQLW := LC$SQLW || LC$Col || '||' || '''' || CHR(9) || '''' ;
     LN$I := LN$I + 1 ;
   END LOOP ;

   LC$SQL := LC$SQL || ' FROM ' || PC$TABLE || ' WHERE UPPER(' || LC$SQLW || ') LIKE ''%'||LC$WHERE||'%''';


   -- Execution de la requete --
   OPEN cur FOR LC$sql ;
   FETCH cur BULK COLLECT INTO PT_Rows ;
   CLOSE cur;

  END;
END;

/

-- Procédure de recherche –
DECLARE
  TABLE_ROWS Pkg_Recherche_Globale.T_ROW ;
BEGIN
  Pkg_Recherche_Globale.P_Get_Lines( TABLE_ROWS ,'MA_TABLE', 'MA CHAINE RECHERCHEE');

  FOR I IN TABLE_ROWS.first..TABLE_ROWS.last LOOP
     DBMS_OUTPUT.PUT_LINE( TABLE_ROWS(I) ) ;
  END LOOP ; 

END ;

Remerciements

Chaleureux remerciements à Developpez.com et l'équipe SGBD

Responsable bénévole de la rubrique Oracle : Vincent Rogier - Contacter par EMail :

Vos questions techniques : forum d'entraide Oracle - Publiez vos articles, tutoriels et cours
et rejoignez-nous dans l'équipe de rédaction du club d'entraide des développeurs francophones
Nous contacter - Copyright © 2000-2008 www.developpez.com - Legal informations.