Oracle Tutoriel PL/SQL Dynamic SQL : Exécution immédiate et DBMS

⚡ Résumé intelligent

SQL dynamique dans Oracle PL/SQL construit et exécute des instructions au moment de l'exécution, adaptant les requêtes aux exigences changeantes grâce à deux approches : le SQL dynamique natif avec EXECUTE IMMEDIATE et OPEN-FOR, et le package flexible DBMS_SQL pour les cas complexes.

⚙️ SQL d'exécution : Le SQL dynamique génère et exécute des instructions lorsque les noms de tables ou de colonnes sont inconnus à l'avance.
| SQL dynamique natif : EXECUTE IMMEDIATE crée et exécute rapidement des requêtes SQL avec un minimum de code.
(I.e. OUVERT POUR : Gère les requêtes dynamiques multi-lignes que la commande EXECUTE IMMEDIATE ne peut pas exécuter seule.
🧩 SGBD_SQL : Instructions adaptées dont le nombre de colonnes ou les types sont inconnus jusqu'à l'exécution.
(I.e. Lier les variables : La clause USING transmet les valeurs de manière positionnelle et bloque les injections SQL.
🤖 Assistance IA : Les outils d'IA génèrent du SQL dynamique et signalent les risques d'injection lors de l'analyse.

Qu’est-ce que le SQL dynamique ?

Dynamique SQL SQL est une méthodologie de programmation permettant de générer et d'exécuter des instructions SQL à l'exécution. Elle est principalement utilisée pour écrire des programmes génériques et flexibles où les instructions SQL sont créées et exécutées à l'exécution en fonction des besoins, par exemple lorsque les noms de tables, les listes de colonnes ou les conditions WHERE ne sont connus qu'au moment de l'exécution du programme.

Méthodes d'écriture de SQL dynamique

PL/SQL offre deux façons d'écrire du SQL dynamique :

NDS – SQL dynamique natif (les instructions EXECUTE IMMEDIATE et OPEN-FOR)
DBMS_SQL (un colis fourni)

La règle générale est simple : si le nombre et les types de données des variables d’entrée et de sortie sont connus à la compilation, utilisez le SQL dynamique natif, car il est plus rapide et nécessite moins de code. Si ces informations ne sont connues qu’à l’exécution, utilisez le package DBMS_SQL.

NDS (Native Dynamic SQL) – Exécution immédiate

Le SQL dynamique natif est la méthode la plus simple pour écrire du SQL dynamique. Il utilise la commande EXECUTE IMMEDIATE pour créer et exécuter le code SQL à l'exécution. Pour utiliser cette approche, le type de données et le nombre de variables utilisées à l'exécution doivent être connus à l'avance. Il offre également de meilleures performances et une complexité moindre comparé à DBMS_SQL.

Syntaxe

EXECUTE IMMEDIATE dynamic_sql_string
[INTO {variable[, variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument[, ...]]
[RETURNING INTO bind_argument[, ...]];

chaîne_sql_dynamique : Une expression de chaîne (VARCHAR2 ou CHAR, et non NVARCHAR2/NCHAR) contenant une seule instruction SQL ou un seul bloc PL/SQL.
clause INTO : Facultatif. Utilisé uniquement lorsque la requête SQL dynamique est une requête SELECT sur une seule ligne ; elle stocke les valeurs renvoyées dans des variables ou un enregistrement. Chaque colonne sélectionnée doit avoir une variable de type compatible.
clause USING : Optionnel. Permet de lier des variables. Le mode par défaut est IN ; les modes OUT et IN OUT servent à recevoir des valeurs en retour.
RETOUR À LA clause : Utilisé avec les instructions DML comportant une clause RETURNING, pour capturer les valeurs des lignes affectées dans les arguments de liaison.

Exemple 1: Dans cet exemple, nous récupérons les données de la table emp pour emp_no '1001' à l'aide d'une instruction NDS avec une variable de liaison.

DECLARE
   lv_sql       VARCHAR2(500);
   lv_emp_name  VARCHAR2(50);
   ln_emp_no    NUMBER;
   ln_salary    NUMBER;
   ln_manager   NUMBER;
BEGIN
   lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno';
   EXECUTE IMMEDIATE lv_sql
      INTO lv_emp_name, ln_emp_no, ln_salary, ln_manager
      USING 1001;
   DBMS_OUTPUT.PUT_LINE('Employee Name: '   || lv_emp_name);
   DBMS_OUTPUT.PUT_LINE('Employee Number: ' || ln_emp_no);
   DBMS_OUTPUT.PUT_LINE('Salary: '          || ln_salary);
   DBMS_OUTPUT.PUT_LINE('Manager ID: '      || ln_manager);
END;
/

Sortie

Employee Name  : XXX
Employee Number: 1001
Salary         : 15000
Manager ID     : 1000

Code Explication:

Lignes 2-6 : Déclaration des variables.
Ligne 8: Encadrement de la requête SQL à l'exécution. La requête SQL contient la variable de liaison ':empno' dans la clause WHERE.
Lignes 9-11 : Exécution de la requête SQL encadrée avec EXECUTE IMMEDIATE. Les variables de la clause INTO contiennent les valeurs extraites, et la clause USING fournit la valeur de la variable liée :empno.
Lignes 12-15 : Affichage des valeurs récupérées.

Utilisation du SQL dynamique pour le DDL

Le PL/SQL statique ne peut pas exécuter directement des instructions DDL telles que CREATE, ALTER ou DROP. L'instruction EXECUTE IMMEDIATE résout ce problème en construisant l'instruction sous forme de chaîne de caractères, ce qui est également pratique lorsqu'un nom d'objet est fourni lors de l'exécution.

DECLARE
   l_table_name VARCHAR2(30) := 'my_table';
   l_sql_stmt   VARCHAR2(200);
BEGIN
   l_sql_stmt := 'CREATE TABLE ' || l_table_name ||
                 ' (id NUMBER, name VARCHAR2(30))';
   EXECUTE IMMEDIATE l_sql_stmt;
END;
/

Les noms d'objets (table, colonne, schéma) ne peuvent pas être transmis comme variables liées ; ils doivent donc être concaténés dans la chaîne. Il est impératif de toujours valider ces données, par exemple avec DBMS_ASSERT.SIMPLE_SQL_NAME, afin d'éviter les injections SQL.

DBMS_SQL pour SQL dynamique

PL/SQL fournit le package DBMS_SQL pour la manipulation de requêtes SQL dynamiques lorsque la structure de l'instruction n'est connue qu'à l'exécution. Le processus de création et d'exécution de ces requêtes SQL dynamiques comprend les étapes suivantes :

OUVRIR LE CURSEUR : Le SQL dynamique s'exécute comme un curseurPour exécuter l'instruction SQL, nous devons d'abord ouvrir le curseur.
ANALYSER SQL : Analyser le SQL dynamique. Cela vérifie la syntaxe et prépare la requête pour l'exécution.
Valeurs de la variable BIND : Attribuez les valeurs aux variables liées, le cas échéant.
DÉFINIR LA COLONNE : Définissez chaque colonne en utilisant sa position relative dans l'instruction SELECT.
EXÉCUTER: Exécutez la requête analysée.
VALEURS DE RÉCUPÉRATION : Récupérez les valeurs exécutées.
FERMER LE CURSEUR : Une fois les résultats récupérés, fermez le curseur.

Exemple 1: Dans cet exemple, nous récupérons les données de la table « emp » pour l'employé n° 1001 à l'aide d'une instruction DBMS_SQL. Le bloc EXCEPTION ferme le curseur même en cas d'erreur.

DECLARE
   lv_sql            VARCHAR2(500);
   lv_emp_name       VARCHAR2(50);
   ln_emp_no         NUMBER;
   ln_salary         NUMBER;
   ln_manager        NUMBER;
   ln_cursor_id      NUMBER;
   ln_rows_processed NUMBER;
BEGIN
   lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno';
   ln_cursor_id := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(ln_cursor_id, lv_sql, DBMS_SQL.NATIVE);
   DBMS_SQL.BIND_VARIABLE(ln_cursor_id, ':empno', 1001);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 1, lv_emp_name, 50);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 2, ln_emp_no);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 3, ln_salary);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 4, ln_manager);
   ln_rows_processed := DBMS_SQL.EXECUTE(ln_cursor_id);
   LOOP
      IF DBMS_SQL.FETCH_ROWS(ln_cursor_id) = 0 THEN
         EXIT;
      ELSE
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 1, lv_emp_name);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 2, ln_emp_no);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 3, ln_salary);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 4, ln_manager);
         DBMS_OUTPUT.PUT_LINE('Employee Name: '   || lv_emp_name);
         DBMS_OUTPUT.PUT_LINE('Employee Number: ' || ln_emp_no);
         DBMS_OUTPUT.PUT_LINE('Salary: '          || ln_salary);
         DBMS_OUTPUT.PUT_LINE('Manager ID: '      || ln_manager);
      END IF;
   END LOOP;
   DBMS_SQL.CLOSE_CURSOR(ln_cursor_id);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_SQL.CLOSE_CURSOR(ln_cursor_id);
END;
/

Sortie

Employee Name  : XXX
Employee Number: 1001
Salary         : 15000
Manager ID     : 1000

Code Explication:

Lignes 1-8 : Déclaration de variable.
Ligne 10: Formulation de la requête SQL.
Ligne 11: Ouverture du curseur à l'aide de DBMS_SQL.OPEN_CURSOR, qui renvoie l'identifiant du curseur ouvert.
Ligne 12: Une fois le curseur ouvert, le code SQL est analysé.
Ligne 13: La valeur de liaison '1001' est attribuée à la place de ':empno'.
Lignes 14-17 : Définition des colonnes par leur position relative : (1) emp_name, (2) emp_no, (3) salary, (4) manager.
Ligne 18: L'exécution de la requête avec DBMS_SQL.EXECUTE renvoie le nombre d'enregistrements traités.
Lignes 19-32 : Récupération des enregistrements en boucle. La fonction FETCH_ROWS renvoie 0 lorsqu'il ne reste plus de lignes, ce qui met fin à la boucle.
bloc EXCEPTION : Garantit la fermeture du curseur afin d'éviter les fuites de mémoire en cas d'erreur.

NDS ou DBMS_SQL : quand utiliser lequel ?

Les deux approches exécutent des requêtes SQL à l'exécution, mais elles conviennent à des situations différentes :

Utiliser SQL dynamique natif (EXECUTE IMMEDIATE / OPEN-FOR) Lorsque le nombre et les types de données des entrées et des sorties sont connus à la compilation, le code est plus rapide, plus lisible et nécessite moins de code.
Utilisez DBMS_SQL lorsque la structure est inconnue jusqu'à l'exécution, par exemple une requête dont le nombre de colonnes sélectionnées ou de variables liées varie, connue sous le nom de SQL dynamique de méthode 4, ou une instruction trop grande pour tenir dans une seule variable VARCHAR2 de 32 Ko.

FAQ

Les variables liées transmettent les entrées utilisateur sous forme de données, jamais sous forme de code exécutable. La clause USING fournit les valeurs de manière positionnelle, empêchant ainsi tout texte malveillant de modifier la structure de l'instruction. Il est impératif de toujours lier les entrées non fiables plutôt que de les concaténer.

Non. Oracle Cette fonction ne lie que les valeurs des données, et non les noms des objets. Pour vous prémunir contre les injections SQL, concaténez les identifiants dans la chaîne et validez-les avec DBMS_ASSERT.SIMPLE_SQL_NAME.

La commande EXECUTE IMMEDIATE ne récupère qu'une seule ligne. Pour plusieurs lignes, ouvrez un curseur REF CURSOR avec l'instruction OPEN-FOR, puis parcourez la boucle FETCH jusqu'à %NOTFOUND et fermez le curseur.

Ajoutez une clause RETURNING à l'instruction INSERT, UPDATE ou DELETE, puis utilisez la clause RETURNING INTO de l'instruction EXECUTE IMMEDIATE pour capturer les valeurs de la ligne affectée dans les arguments de liaison.

Le SQL dynamique ajoute une surcharge d'analyse car les instructions sont compilées à l'exécution. La réutilisation des variables liées permet Oracle partagez les curseurs et réduisez les analyses syntaxiques complexes, gardezping performances proches de celles du SQL statique.

La chaîne doit être de type VARCHAR2 ou CHAR. Les types de caractères nationaux tels que NVARCHAR2 et NCHAR ne sont pas autorisés. Pour les textes de plus de 32 Ko, DBMS_SQL accepte une collection de fragments VARCHAR2.

Oui. Les assistants IA tels que GitHub Copilot génèrent des blocs EXECUTE IMMEDIATE et DBMS_SQL à partir d'invites simples, suggèrent des espaces réservés pour les variables de liaison et expliquent chaque clause, même si un développeur doit toujours examiner le résultat.

Les analyseurs de code basés sur l'IA signalent les entrées utilisateur concaténées et recommandent l'utilisation de variables liées ou des vérifications DBMS_ASSERT. Ils mettent en évidence les schémas à risque lors de l'analyse et aidentping Les équipes repèrent les défauts d'injection avant le déploiement.

Oracle Tutoriel PL/SQL Dynamic SQL : Exécution immédiate et DBMS_SQL