8 codage des instructions SQL dynamiques

le SQL dynamique est une technique de programmation qui vous permet de créer des instructions SQL dynamiquement lors de l’exécution. Vous pouvez créer des applications plus générales et flexibles en utilisant dynamic SQL, car le texte intégral d’une instruction SQL peut être inconnu lors de la compilation. Par exemple, dynamic SQL vous permet de créer une procédure qui fonctionne sur une table dont le nom n’est pas connu avant l’exécution.,

Oracle inclut deux façons d’implémenter le SQL dynamique dans une application PL/SQL:

• SQL dynamique natif, où vous placez des instructions SQL dynamiques directement dans des blocs PL / SQL.
• procédures D’appel dans le packageDBMS_SQL.

Ce chapitre couvre les sujets suivants:

• « QU’est-ce que le SQL dynamique? »
•  » Pourquoi utiliser dynamic SQL?, »
• « Un scénario SQL dynamique utilisant SQL dynamique natif »
• « choisir entre SQL dynamique natif et le paquet DBMS_SQL »
• « utiliser SQL dynamique dans des langages autres que PL/SQL »
• « utiliser des enregistrements PL/SQL dans les instructions SQL INSERT and UPDATE »

Vous pouvez trouver des détails sur le DBMS_SQL Oracle9i a fourni des paquets PL/SQL et des références de types.

QU’est-ce que le SQL dynamique?

dynamic SQL vous permet d’écrire des programmes qui référencent des instructions SQL dont le texte intégral n’est pas connu avant l’exécution., Avant de discuter du SQL dynamique en détail, une définition claire du SQL statique peut fournir un bon point de départ pour comprendre le SQL dynamique. Les instructions SQL statiques ne changent pas d’une exécution à l’autre. Le texte intégral des instructions SQL statiques est connu lors de la compilation, ce qui offre les avantages suivants:

• une compilation réussie vérifie que les instructions SQL référencent des objets de base de données valides.
• une compilation réussie vérifie que les privilèges nécessaires sont en place pour accéder aux objets de la base de données.,
• Les performances du SQL statique sont généralement meilleures que celles du SQL dynamique.

en raison de ces avantages, vous ne devez utiliser dynamic SQL que si vous ne pouvez pas utiliser static SQL pour atteindre vos objectifs, ou si l’utilisation de static SQL est fastidieuse Par rapport à dynamic SQL. Cependant, le SQL statique a des limites qui peuvent être surmontées avec le SQL dynamique. Vous ne connaissez peut-être pas toujours le texte intégral des instructions SQL qui doivent être exécutées dans une procédure PL/SQL., Votre programme peut accepter l’entrée utilisateur qui définit les instructions SQL à exécuter, ou votre programme peut avoir besoin d’effectuer un travail de traitement pour déterminer le bon plan d’action. Dans de tels cas, vous devez utiliser dynamic SQL.

par exemple, une application de reporting dans un environnement d’entrepôt de données peut ne pas connaître le nom exact de la table avant l’exécution., Ces tables peuvent être nommés selon les mois de début et l’année de trimestre, par exemple: INV_01_1997, INV_04_1997, INV_07_1997, INV_10_1997, INV_01_1998, et ainsi de suite. Vous pouvez utiliser dynamic SQL dans votre application de reporting pour spécifier le nom de la table lors de l’exécution.

Vous pouvez également exécuter une requête complexe avec un utilisateur de sélectionner l’ordre de tri., Au lieu de coder la requête deux fois, avec différentes clauses ORDER BY, vous pouvez construire la requête dynamiquement pour inclure une clause ORDER BY spécifiée.

Les programmes SQL dynamiques peuvent gérer les changements dans les définitions de données, sans avoir besoin de recompiler. Cela rend le SQL dynamique beaucoup plus flexible que le SQL statique. Dynamic SQL vous permet d’écrire du code réutilisable car le SQL peut être facilement adapté à différents environnements..,

dynamic SQL vous permet également d’exécuter des instructions DDL (data definition language) et d’autres instructions SQL qui ne sont pas prises en charge dans les programmes SQL purement statiques.

Pourquoi utiliser dynamic SQL?

Vous devez utiliser dynamic SQL dans les cas où static SQL ne prend pas en charge l’opération que vous souhaitez effectuer, ou dans les cas où vous ne connaissez pas les instructions SQL exactes qui doivent être exécutées par une procédure PL/SQL. Ces instructions SQL peuvent dépendre de l’entrée de l’utilisateur, ou elles peuvent dépendre du travail de traitement effectué par le programme.,DL and SCL Statements in PL/SQL

In PL/SQL, you can only execute the following types of statements using dynamic SQL, rather than static SQL:

• Data definition language (DDL) statements, such as CREATE, DROP, GRANT, and REVOKE
• Session control language (SCL) statements, such as ALTER SESSION and SET ROLE
  
  See Also:

  Oracle9i SQL Reference for information about DDL and SCL statements.,

de plus, vous ne pouvez utiliser que la clause TABLE dans l’instruction SELECT via SQL dynamique. Par exemple, le bloc PL/SQL suivant contient une instruction SELECT qui utilise la clause TABLE et SQL dynamique natif:

exécution de requêtes dynamiques

Vous pouvez utiliser dynamic SQL pour créer des applications qui exécutent des requêtes dynamiques, dont le texte intégral n’est pas connu, De nombreux types d’applications ont besoin d’utiliser des requêtes dynamiques, y compris:

• Applications qui permettent aux utilisateurs d’entrer ou de choisir des critères de recherche ou de tri lors de l’exécution
• Applications qui permettent aux utilisateurs d’entrer ou de choisir des conseils d’optimiseur au moment de l’exécution
• Applications qui interrogent une base de données où les définitions de données des tables sont en constante évolution
• Applications qui interrogent une base de données où de nouvelles tables sont souvent créées

pour des exemples, voir « interroger à L’aide de SQL dynamique: exemple », et voir la requête exemples dans « un scénario SQL dynamique utilisant le SQL dynamique natif ».,

référencement D’objets de base de données qui n’existent pas lors de la Compilation

de nombreux types d’applications doivent interagir avec des données générées périodiquement. Par exemple, vous pouvez connaître les définitions des tables au moment de la compilation, mais pas les noms des tables.

dynamic SQL peut résoudre ce problème, car il vous permet d’attendre l’exécution pour spécifier les noms de table. Par exemple, dans l’exemple d’Application data warehouse discuté dans  » QU’est-ce que dynamic SQL? », de nouvelles tables sont générées chaque trimestre, et ces tables ont toujours la même définition., Vous pouvez laisser un utilisateur spécifier le nom de la table lors de l’exécution avec une requête SQL dynamique similaire à la suivante:

optimisation dynamique de l’exécution

Vous pouvez utiliser dynamic SQL pour créer une instruction SQL d’une manière qui optimise l’exécution en concaténant dynamiquement les indices dans une instruction SQL. Cela vous permet de modifier les conseils en fonction des statistiques de votre base de données actuelle, sans nécessiter de recompilation.,

Par exemple, la procédure suivante utilise une variable nommée a_hint pour permettre aux utilisateurs de passer d’une option d’allusion à la balise SELECT instruction:

Dans cet exemple, l’utilisateur peut transmettre toutes les valeurs suivantes pour le a_hint:

a_hint = '/*+ ALL_ROWS */'a_hint = '/*+ FIRST_ROWS */'a_hint = '/*+ CHOOSE */'ou tout autre valide l’option d’allusion.

l’Exécution Dynamique PL/SQL Blocs

Vous pouvez utiliser la balise EXECUTE IMMEDIATE instruction à exécuter anonyme PL/SQL blocs. Vous pouvez ajouter de la flexibilité en construisant le contenu du bloc lors de l’exécution.

par exemple, supposons que vous souhaitiez écrire une application qui prend un numéro d’événement et l’envoie à un gestionnaire pour l’événement. Le nom du gestionnaire est sous la forme EVENT_HANDLER_event_num, où event_num est le numéro de l’événement., Une approche consiste à implémenter le répartiteur en tant qu’instruction switch, où le code gère chaque événement en effectuant un appel statique à son gestionnaire approprié. Ce code n’est pas très extensible car le code du répartiteur doit être mis à jour chaque fois qu’un gestionnaire pour un nouvel événement est ajouté.,

en utilisant le SQL dynamique natif, vous pouvez écrire un répartiteur d’événements plus petit et plus flexible, similaire à ce qui suit:

effectuer des opérations dynamiques à l’aide de Invoker-Rights

En utilisant la fonctionnalité invoker-rights avec dynamic SQL, vous pouvez créer des applications qui émettent des instructions SQL dynamiques Ces deux fonctionnalités, invoker-rights ET DYNAMIC SQL, vous permettent de créer des sous-composants d’application réutilisables qui peuvent fonctionner et accéder aux données et aux modules de l’invoker.,

un scénario SQL dynamique utilisant le SQL dynamique natif

ce scénario vous montre comment effectuer les opérations suivantes en utilisant le SQL dynamique natif:

• exécuter des opérations DDL et DML
• exécuter des requêtes à une ligne et à plusieurs lignes

La base de données dans ce scénario) avec le modèle de données suivant:

une table principale nommée officescontient la liste de tous les emplacements de L’entreprise., La tableoffices a la définition suivante:

Column Name Null? Type LOCATION NOT_NULL VARCHAR2(200)

Plusieursemp_les tables d’emplacement contiennent les informations sur l’employé, où l’emplacement est le nom de la ville où se trouve le bureau. Par exemple, une table nommée emp_houston contient des informations sur les employés du bureau de Houston de l’entreprise, tandis qu’une table nommée emp_boston contient des informations sur les employés du bureau de Boston de L’entreprise.,

chaque table d’emplacementemp_a la définition suivante:

Les sections suivantes décrivent diverses opérations SQL dynamiques natives qui peuvent être effectuées sur les données de la base de donnéeshr.

exemple D’opération DML utilisant SQL dynamique natif

la procédure SQL dynamique native suivante donne une relance à tous les employés avec un titre de poste particulier:

exemple D’opération DDL utilisant SQL dynamique natif

l’instructionEXECUTE IMMEDIATE peut effectuer des opérations DDL., Par exemple, la procédure suivante ajoute un emplacement de bureau:

la procédure suivante supprime un emplacement de bureau:

exemple de requête à une ligne à l’aide de SQL dynamique natif

l’instruction EXECUTE IMMEDIATE peut effectuer des requêtes dynamiques à une ligne. Vous pouvez spécifier des variables de liaison dans la clauseUSING et récupérer la ligne résultante dans la cible spécifiée dans la clauseINTO de l’instruction.,

la fonction suivante récupère le nombre d’employés à un emplacement particulier effectuant une tâche spécifiée:

exemple de requête à plusieurs lignes à L’aide de SQL dynamique natif

les instructions OPEN-FOR, FETCH Et CLOSE-requêtes de ligne., Par exemple, la procédure suivante répertorie tous les employés ayant une tâche particulière à un emplacement spécifié:

choisir entre SQL dynamique natif et le paquet DBMS_SQL

Oracle fournit deux méthodes pour utiliser le SQL dynamique dans PL/SQL: SQL dynamique natif et le paquet DBMS_SQL. SQL dynamique natif vous permet de placer des instructions SQL dynamiques directement dans le code PL/SQL. Ces instructions dynamiques incluent les instructions DML (y compris les requêtes), les blocs anonymes PL/SQL, les instructions DDL, les instructions de contrôle de transaction et les instructions de contrôle de session.,

To process most native dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. To process a multi-row query (SELECT statement), you use OPEN-FOR, FETCH, and CLOSE statements.

LeDBMS_SQL paquet est une bibliothèque PL / SQL qui offre une API pour exécuter des instructions SQL dynamiquement. Le packageDBMS_SQL a des procédures pour ouvrir un curseur, analyser un curseur, fournir des liaisons, etc. Les programmes qui utilisent le package DBMS_SQL effectuent des appels à ce package pour effectuer des opérations SQL dynamiques.

Les sections suivantes fournissent des informations détaillées sur les avantages des deux méthodes.,

avantages du SQL dynamique natif

Le SQL dynamique natif offre les avantages suivants par rapport au packageDBMS_SQL:

le SQL dynamique natif est facile à utiliser

parce que le SQL dynamique natif est intégré à SQL, vous pouvez l’utiliser de la même Le code SQL dynamique natif est généralement plus compact et lisible que le code équivalent qui utilise le package DBMS_SQL.,

avec le packageDBMS_SQL, vous devez appeler de nombreuses procédures et fonctions dans un ordre strict, ce qui fait que même les opérations simples nécessitent beaucoup de code. Vous pouvez éviter cette complexité en utilisant le SQL dynamique natif à la place.

le tableau 8-1 illustre la différence entre la quantité de code requise pour effectuer la même opération en utilisant le packageDBMS_SQL et le SQL dynamique natif.,

tableau 8-1 comparaison de Code du paquet DBMS_SQL et du SQL dynamique natif

le SQL dynamique natif est plus rapide que DBMS_SQL

le SQL dynamique natif dans PL/SQL fonctionne de manière comparable aux performances du SQL statique, car L’interpréteur PL / SQL le prend en charge. Les programmes qui utilisent SQL dynamique natif sont beaucoup plus rapides que les programmes qui utilisent le package DBMS_SQL. En règle générale, les instructions SQL dynamiques natives exécutent 1,5 à 3 fois mieux que les appels équivalents DBMS_SQL. (Vos gains de performance peuvent varier en fonction de votre application.,)

SQL dynamique natif regroupe les étapes de préparation, de liaison et d’exécution d’instructions en une seule opération, ce qui minimise la surcharge de copie de données et d’appel de procédure et améliore les performances.

le package DBMS_SQL est basé sur une API procédurale et entraîne une surcharge élevée d’appel de procédure et de copie de données. Chaque fois que vous liez une variable, le packageDBMS_SQL copie la variable de liaison PL/SQL dans son espace pour l’utiliser pendant l’exécution., Chaque fois que vous exécutez une récupération, les données sont copiées dans l’espace géré par le package DBMS_SQL, puis les données récupérées sont copiées, une colonne à la fois, dans les variables PL/SQL appropriées, ce qui entraîne une surcharge importante.

Conseil sur les performances: utilisation des Variables de liaison

lorsque vous utilisez du SQL dynamique natif ou le packageDBMS_SQL, vous pouvez améliorer les performances en utilisant des variables de liaison, car les variables de liaison permettent à Oracle de partager un seul curseur pour plusieurs instructions SQL.,

Par exemple, le code SQL dynamique natif suivant n’utilise pas de variables de liaison:

pour chaque variable my_deptno distincte, un nouveau curseur est créé, ce qui entraîne une contention des ressources et de mauvaises performances. Au lieu de cela, bind my_deptno en tant que variable de liaison:

ici, le même curseur est réutilisé pour différentes valeurs de la liaison my_deptno, améliorant ainsi les performances et la scalabilité.,

le SQL dynamique natif prend en charge les Types définis par L’utilisateur

le SQL dynamique natif prend en charge tous les types pris en charge par le SQL statique dans PL/SQL, y compris les types définis par l’utilisateur tels que les objets définis par l’utilisateur, les collections etREFs. Le packageDBMS_SQL ne prend pas en charge ces types définis par l’utilisateur.

le SQL dynamique natif prend en charge la récupération dans les enregistrements

Le SQL dynamique natif et le SQL statique prennent en charge la récupération dans les enregistrements, mais le packageDBMS_SQL ne le fait pas. Avec SQL dynamique natif, les lignes résultant d’une requête peuvent être directement récupérées dans des enregistrements PL/SQL.,

dans l’exemple suivant, les lignes d’une requête sont récupérées dans l’enregistrement emp_rec:

avantages du paquet DBMS_SQL

Le Paquet DBMS_SQL offre les avantages suivants par rapport au SQL dynamique natif:

DBMS_SQL est pris en charge programs

le package DBMS_SQL est pris en charge dans les programmes côté client, mais pas dans le SQL dynamique natif., Chaque appel au package DBMS_SQL du programme côté client se traduit par un appel de procédure à distance PL/SQL (RPC); ces appels se produisent lorsque vous devez lier une variable, définir une variable ou exécuter une instruction.

DBMS_SQL supporte DESCRIBE

la procédureDESCRIBE_COLUMNS dans le packageDBMS_SQL peut être utilisée pour décrire les colonnes d’un curseur ouvert et analysé viaDBMS_SQL. Cette fonctionnalité est similaire à la commande DESCRIBE dans SQL*plus., Le SQL dynamique natif n’a pas de fonction DESCRIBE.

DBMS_SQL prend en charge plusieurs mises à jour et suppressions de lignes avec une Clause de retour

le packageDBMS_SQL prend en charge les instructions avec une clauseRETURNING qui mettent à jour ou suppriment plusieurs lignes. Le SQL dynamique natif ne prend en charge une clause RETURNING que si une seule ligne est renvoyée.,

DBMS_SQL Supports SQL Statements Larger than 32KB

The DBMS_SQL package supports SQL statements larger than 32KB; native dynamic SQL does not.

DBMS_SQL Lets You Reuse SQL Statements

The PARSE procedure in the DBMS_SQL package parses a SQL statement once., Après l’analyse initiale, vous pouvez utiliser l’instruction plusieurs fois avec différents ensembles d’arguments de liaison.

SQL dynamique natif prépare une instruction SQL chaque fois que l’instruction est utilisée, ce qui implique généralement l’analyse, l’optimisation et la génération de plans. Bien que les opérations de préparation supplémentaires entraînent une légère pénalité en termes de performances, le ralentissement est généralement compensé par les avantages en termes de performances du SQL dynamique natif.,

exemples de code de paquet DBMS_SQL et de code SQL dynamique natif

Les exemples suivants illustrent les différences dans le code nécessaire pour effectuer des opérations avec leDBMS_SQL paquet et SQL dynamique natif. Plus précisément, les types d’exemples suivants sont présentés:

• a query
• A DML operation
• A DML returning operation

en général, le code SQL dynamique natif est plus lisible et compact, ce qui peut améliorer la productivité des développeurs.,

l’Interrogation à l’Aide de Dynamic SQL: Exemple

L’exemple suivant inclut une instruction de requête dynamique avec un lier des variables (:jobname) et deux colonnes (ename et sal):

stmt_str := 'SELECT ename, sal FROM emp WHERE job = :jobname';

Cet exemple, les requêtes pour les employés avec la description du poste SALESMAN dans le job colonne de la balise emp tableau. Le tableau 8-2 montre un exemple de code qui accomplit cette requête à l’aide du package DBMS_SQL Et du SQL dynamique natif.,

table 8-2 interrogation à L’aide du paquet DBMS_SQL et SQL dynamique natif

exécution DML à L’aide de SQL dynamique: exemple

l’exemple suivant inclut une instruction dynamic INSERT pour une table à trois colonnes:

stmt_str := 'INSERT INTO dept_new VALUES (:deptno, :dname, :loc)';

es valeurs des colonnes sont dans les variables PL/SQLdeptnumber,deptnameEtlocation. Le tableau 8-3 montre un exemple de code qui accomplit cette opération DML à l’aide du packageDBMS_SQLEt du SQL dynamique natif.,

table 8-3 opération DML utilisant le paquet DBMS_SQL et SQL dynamique natif

exécution DML avec clause RETURNING utilisant SQL dynamique: exemple

l’exemple suivant utilise une instruction dynamic UPDATE pour mettre à jour l’emplacement d’un département, puis renvoie le nom div>

le tableau 8-4 montre un exemple de code qui accomplit cette opération en utilisant à la fois le package DBMS_SQLet le SQL dynamique natif.,

tableau 8-4 opération de retour DML en utilisant le paquet DBMS_SQL et SQL dynamique natif

en utilisant SQL dynamique dans des langages autres que PL/SQL

bien que ce chapitre traite de la prise en charge PL/SQL pour SQL dynamique, vous pouvez appeler SQL dynamique à partir d’autres langages:

• Si vous utilisez C/C++, vous pouvez appeler SQL dynamique avec L’Oracle Call Interface (OCI), ou vous pouvez utiliser le précompilateur Pro*C/C++ pour ajouter extensions SQL dynamiques à votre code C.
• Si vous utilisez COBOL, vous pouvez utiliser le précompilateur Pro*COBOL pour ajouter des extensions SQL dynamiques à votre code COBOL.,
• Si vous utilisez Java, vous pouvez développer des applications qui utilisent SQL dynamique avec JDBC.

Si vous avez une application qui utilise OCI, Pro*C/C++ ou Pro*COBOL pour exécuter du SQL dynamique, vous devriez envisager de passer au SQL dynamique natif dans les procédures et fonctions stockées PL / SQL. Les allers-retours réseau requis pour effectuer des opérations SQL dynamiques à partir d’applications côté client peuvent nuire aux performances. Les procédures stockées peuvent résider sur le serveur, éliminant ainsi la surcharge du réseau., Vous pouvez appeler les procédures stockées PL/SQL et les fonctions stockées à partir de L’application OCI, Pro*C/C++ ou Pro*COBOL.,

• Oracle Call Interface Programmer’s Guide
• Pro*C/C++ Precompiler Programmer’s Guide
• Pro*COBOL Precompiler Programmer’s Guide
• Oracle9i Java Stored Procedures Developer’s Guide

utilisation des enregistrements PL/SQL dans les instructions SQL INSERT et UPDATE

bien que vous puissiez énumérer chaque champ d’un enregistrement PL/SQL lors de l’insertion ou de la mise à jour de lignes dans une table, le code résultant n’est pas particulièrement lisible ni maintenable., Au lieu de cela, vous pouvez utiliser des enregistrements PL/SQL directement dans ces instructions. La technique la plus pratique consiste à déclarer l’enregistrement à l’aide d’un attribut %ROWTYPE, de sorte qu’il ait exactement les mêmes champs que la table SQL.

bien que cette technique aide à intégrer plus étroitement les variables et les types PL/SQL avec les instructions SQL DML, vous ne pouvez pas utiliser les enregistrements PL / SQL comme variables de liaison dans les instructions SQL dynamiques.