SQL dynamique

SQL dynamique - présentation

Le SQL dynamique est un moyen très pratique pour le DBA ou le développeur d'automatiser certaines taches répétitives fastidieuses.
Le principe est d'écrire un petit script qui génère l'ensemble des ordres SQL désirés, en s'appyant sur le dictionaire de données. Il suffira ensuite de lancer ce script et le tour est joué.
Si l'on souhaite par exemple vider toutes les tables d'un utilisateur sans détruire leur contenu, et éviter de faire une liste de ses tables à partir du dictionnaire et ensuite saisir une liste interminable de 'TRUNCATE TABLE ...", la solution c'est le SQL dynamique.
On peut concaténer des constantes chaines de caractères à des variables ramenées par un SELECT en SQL. On utilisera cette possibilité pour faire du SQL dynamique.
Ainsi on peut écrire :

SELECT 'Bonjour mon cher '|| nom_client FROM tab_clients;

le résultat est du style :

Bonjour mon cher MARTIN
Bonjour mon cher DUPONT
Bonjour mon cher DURAND
...

De manière un peu plus sophistiquée je peux créer de la sorte une suite d'ordres SQL de destruction, de mes tables. La liste de mes tables est données par l'ordre SQL suivant (si je suis connecté dans mon compte évidemment...) :

SELECT table_name from USER_TABLES;

le résultat est du genre :

SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
ANNUAIRE
BONUS
DEPT
EMP
4 ligne(s) sélectionnée(s).
SQL>

si je concatène maintenant l'ordre 'DROP TABLE' et le caractère ';' à la fin j'obtiens :

SQL> select 'DROP TABLE '||table_name||';'
from user_tables;
'DROP TABLE'||TABLE_NAME||';'
------------------------------------------
DROP TABLE ANNUAIRE;
DROP TABLE BONUS;
DROP TABLE DEPT;
DROP TABLE EMP;
4 ligne(s) sélectionnée(s).
SQL>

si je supprime maintenant les lignes superflues par les commandes adéquates d'environnement SQL*Plus:
SET PAGESIZE 0
SET ECHO OFF
SET TERMOUT OFF
SET FEEDBACK OFF
SET PAUSE OFF
j'obtiens cette fois :

SQL>select 'DROP TABLE '||table_name||';'
from user_tables;
DROP TABLE ANNUAIRE;
DROP TABLE BONUS;
DROP TABLE DEPT;
DROP TABLE EMP;
SQL>

il ne reste plus qu'a faire un spool de cette sortie dans un fichier drop.sql , et d'executer ensuite le fichier (start ou @drop.sql sous sqlplus). Le script complet SQL dynamique est le suivant :

SET PAGESIZE 0
SET ECHO OFF
SET TERMOUT OFF
SET FEEDBACK OFF
SET PAUSE OFF
SPOOL drop.sql
select 'DROP TABLE '||table_name||';'
from user_tables;
SPOOL OFF
@DROP.SQL

Pour plus de détails voir les exemples ci-après ...

suppression de tous les objets d'un user

REM DROP_OBJET.SQL
REM a lancer sous sqlplus a partir du compte SYSTEM
REM SET PAUSE OFF
SET VERIFY OFF
PROMPT Entrez le nom de l'utilisateur : ACCEPT NOM
PROMPT Entrez le type d'objet (TABLE, VIEW, SYNONYM, ...) :
ACCEPT OBJET
SET PAGES 0
SET ECHO OFF
SET TERMOUT OFF
SET FEEDBACK OFF
REM spool Unix, chemin windows\temp a substituer par exemple
SPOOL /tmp/drop.tmp
SELECT 'DROP '||'&OBJET'||' '||'&NOM'||'.'||OBJECT_NAME||';'
FROM DBA_OBJECTS
WHERE OWNER='&NOM'
/
SPOOL OFF
PROMPT DROP des &OBJET de &NOM en cours...
REM remplacer par le chemin adéquat si Windows
START /tmp/drop.tmp
REM mettre de DEL qui va bien si Windows...
!rm /tmp/drop.tmp

vidage de toutes les tables d'un user

REM TRUNC_TABS_USER.SQL
REM a lancer sous SQLPlus a partir du compte SYSTEM
REM
SET PAUSE OFF
SET VERIFY OFF
PROMPT Entrez le nom de l'utilisateur : ACCEPT NOM
SET PAGES 0
SET ECHO OFF
SET TERMOUT OFF
SET FEEDBACK OFF
REM fichier temporaire Unix, changer le chemin si windows...
SPOOL /tmp/trunc.tmp
SELECT 'TRUNCATE TABLE '||'&NOM'||'.'||TABLE_NAME||';'
FROM DBA_TABLES
WHERE OWNER='&NOM'
/
SPOOL OFF
PROMPT TRUNCATE des tables de &NOM en cours...
START /tmp/trunc.tmp
REM suppression tempon Unix, substituer DEL si Windows...
!rm /tmp/trunc.tmp