Le SQL Dynamique natif

SheikYerbouti

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

- Titre : Le SQL Dynamique natif

- Auteur : SheikYerbouti

- Parution : 5 janvier 2004

- Licence :

Sommaire

- 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és, 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. À l'inverse du SQL dynamique natif.

- Support de la clause RETURNING pour les opérations 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.
À 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 trois 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