8 codificación de instrucciones SQL dinámicas

SQL dinámico es una técnica de programación que le permite crear instrucciones SQL dinámicamente en tiempo de ejecución. Puede crear aplicaciones más flexibles y de propósito general utilizando SQL dinámico porque el texto completo de una instrucción SQL puede ser desconocido en la compilación. Por ejemplo, dynamic SQL le permite crear un procedimiento que opera en una tabla cuyo nombre no se conoce hasta el tiempo de ejecución.,

Oracle incluye dos formas de implementar SQL dinámico en una aplicación PL/SQL:

• SQL dinámico nativo, donde se colocan instrucciones SQL dinámicas directamente en bloques PL / SQL.
• llamando a procedimientos en el paquete DBMS_SQL.

Este capítulo cubre los siguientes temas:

• «¿Qué Es SQL Dinámico?»
• «¿Por qué usar SQL dinámico?,»
• «Un escenario SQL dinámico utilizando SQL dinámico nativo»
• «Elegir entre SQL dinámico nativo y el paquete DBMS_SQL»
• «Usar SQL dinámico en lenguajes distintos de PL/SQL»
• «Usar registros PL/SQL en instrucciones SQL INSERT y UPDATE»

puede encontrar detalles sobre el paquete DBMS_SQL Oracle9i suministró la referencia de paquetes y tipos PL/SQL.

¿Qué es Dynamic SQL?

Dynamic SQL le permite escribir programas que hacen referencia a instrucciones SQL cuyo texto completo no se conoce hasta el tiempo de ejecución., Antes de discutir SQL dinámico en detalle, una definición clara de SQL estático puede proporcionar un buen punto de partida para entender SQL dinámico. Las sentencias SQL estáticas no cambian de ejecución A ejecución. El texto completo de las instrucciones SQL estáticas se conoce en Compilación, lo que proporciona las siguientes ventajas:

• compilación exitosa verifica que las instrucciones SQL hacen referencia a objetos de base de datos válidos.
• La compilación exitosa verifica que los privilegios necesarios estén en su lugar para acceder a los objetos de la base de datos.,
• El rendimiento de SQL estático es generalmente mejor que SQL dinámico.

debido a estas ventajas, debe usar SQL dinámico solo si no puede usar SQL estático para lograr sus objetivos, o si usar SQL estático es engorroso en comparación con SQL dinámico. Sin embargo, SQL estático tiene limitaciones que se pueden superar con SQL dinámico. Es posible que no siempre conozca el texto completo de las instrucciones SQL que deben ejecutarse en un procedimiento PL/SQL., Su programa puede aceptar la entrada del usuario que define las instrucciones SQL a ejecutar, o su programa puede necesitar completar algún trabajo de procesamiento para determinar el curso de acción correcto. En tales casos, debe usar SQL dinámico.

por ejemplo, es posible que una aplicación de informes en un entorno de almacén de datos no conozca el nombre exacto de la tabla hasta el tiempo de ejecución., Estas tablas pueden ser nombrados de acuerdo al principio de mes y año, del trimestre, por ejemplo INV_01_1997, INV_04_1997, INV_07_1997, INV_10_1997, INV_01_1998, y así sucesivamente. Puede usar SQL dinámico en su aplicación de informes para especificar el nombre de la tabla en tiempo de ejecución.

también es posible que desee ejecutar una consulta compleja con un orden de clasificación seleccionable por el usuario., En lugar de codificar la consulta dos veces, con diferentes cláusulas ORDER BY, puede construir la consulta dinámicamente para incluir una cláusula ORDER BY especificada.

Los programas SQL dinámicos pueden manejar cambios en las definiciones de datos, sin la necesidad de recompilar. Esto hace que el SQL dinámico sea mucho más flexible que el SQL estático. Dynamic SQL le permite escribir código reutilizable porque el SQL se puede adaptar fácilmente a diferentes entornos..,

Dynamic SQL también le permite ejecutar instrucciones de lenguaje de definición de datos (DDL) y otras instrucciones SQL que no son compatibles con programas SQL puramente estáticos.

¿por qué usar SQL dinámico?

debe usar SQL dinámico en los casos en los que SQL estático no admite la operación que desea realizar, o en los casos en los que no conoce las instrucciones SQL exactas que deben ejecutarse mediante un procedimiento PL/SQL. Estas instrucciones SQL pueden depender de la entrada del usuario, o pueden depender del trabajo de procesamiento realizado por el programa.,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.,

Además, solo puede usar la cláusula TABLE en la instrucción SELECT a través de SQL dinámico. Por ejemplo, el siguiente bloque PL/SQL contiene una instrucción SELECT que utiliza la cláusula TABLE y SQL dinámico nativo:

ejecutar consultas dinámicas

puede usar SQL dinámico para crear aplicaciones que ejecuten consultas dinámicas, cuyo texto completo no se conoce hasta el tiempo de ejecución., Muchos tipos de aplicaciones necesitan usar consultas dinámicas, incluyendo:

• aplicaciones que permiten a los usuarios ingresar o elegir criterios de búsqueda o clasificación de consultas en tiempo de ejecución
• aplicaciones que permiten a los usuarios ingresar o elegir sugerencias de optimizador en tiempo de ejecución
• aplicaciones que consultan una base de datos donde las definiciones de datos de las tablas cambian constantemente
• aplicaciones que consultan una base de datos donde se crean consulta ejemplos en «un escenario SQL dinámico usando SQL dinámico nativo».,
  

  hacer referencia a objetos de base de datos que no existen en la compilación

  muchos tipos de aplicaciones deben interactuar con los datos que se generan periódicamente. Por ejemplo, es posible que conozca las definiciones de tablas en tiempo de compilación, pero no los nombres de las tablas.

  Dynamic SQL puede resolver este problema, ya que le permite esperar hasta el tiempo de ejecución para especificar los nombres de las tablas. Por ejemplo,en la aplicación de ejemplo data warehouse discutida en » ¿qué es SQL dinámico?», se generan nuevas tablas cada trimestre, y estas tablas siempre tienen la misma definición., Puede permitir que un usuario especifique el nombre de la tabla en tiempo de ejecución con una consulta SQL dinámica similar a la siguiente:

  optimizando la ejecución dinámicamente

  puede usar SQL dinámico para crear una instrucción SQL de manera que optimice la ejecución concatenando las sugerencias en una instrucción SQL dinámicamente. Esto le permite cambiar las sugerencias en función de las estadísticas de su base de datos actual, sin necesidad de recompilación.,

  por ejemplo, el siguiente procedimiento utiliza una variable llamada a_hint para permitir a los usuarios pasar una opción de sugerencia a la instrucción SELECT:

  en este ejemplo, el usuario puede pasar cualquiera de los siguientes valores para a_hint:

  a_hint = '/*+ ALL_ROWS */' a_hint = '/*+ FIRST_ROWS */' a_hint = '/*+ CHOOSE */' o cualquier otra opción de sugerencia válida.

  vea también:

  Oracle9i Database Performance Guide and Reference para obtener más información sobre el uso de sugerencias.,

  Ejecutar bloques dinámicos PL / SQL

  puede utilizar la instrucciónEXECUTEIMMEDIATE para ejecutar bloques anónimos PL/SQL. Puede agregar flexibilidad construyendo el contenido del bloque en tiempo de ejecución.

  por ejemplo, supongamos que ythroughthroughou desea escribir una aplicación que toma un número de Evento y lo envía a un controlador para el evento. El nombre del controlador está en la forma EVENT_HANDLER_event_num, donde event_num es el número del evento., Un enfoque es implementar el dispatcher como una instrucción switch, donde el código maneja cada evento haciendo una llamada estática a su controlador apropiado. Este código no es muy extensible porque el código del despachador debe actualizarse cada vez que se agrega un controlador para un nuevo evento.,

  con SQL dinámico nativo, puede escribir un despachador de eventos más pequeño y flexible similar al siguiente:

  realizar operaciones dinámicas con derechos de invocador

  mediante la función derechos de invocador con SQL dinámico, puede crear aplicaciones que emitan instrucciones SQL dinámicas bajo los privilegios y el esquema del invocador. Estas dos características, derechos de invocador y SQL dinámico, le permiten crear subcomponentes de aplicaciones reutilizables que pueden operar y acceder a los datos y módulos del invocador.,

  vea también:

  Guía de usuario y referencia de PL/SQL para obtener información sobre el uso de invokers-rights y SQL dinámico nativo.,

  un escenario SQL dinámico usando SQL dinámico nativo

  este escenario le muestra cómo realizar las siguientes operaciones usando SQL dinámico nativo:

  • ejecutar operaciones DDL y DML
  • ejecutar consultas de fila única y fila múltiple

  La base de datos en este escenario es la base de datos de Recursos Humanos de una empresa (llamada hr) con el siguiente modelo de datos:

  una tabla maestra llamada offices contiene la lista de todas las ubicaciones de la empresa., La tabla offices tiene la siguiente definición:

  Column Name Null? Type LOCATION NOT_NULL VARCHAR2(200)

  Multiple emp_ las tablas de ubicación contienen la información del empleado, donde la ubicación es el nombre de la ciudad donde se encuentra la oficina. Por ejemplo, una tabla llamada emp_houston contiene información de empleados para la oficina de Houston de la compañía, mientras que una tabla llamada emp_boston contiene información de empleados para la oficina de Boston de la compañía.,

  Cada tabla de ubicación emp_tiene la siguiente definición:

  las siguientes secciones describen varias operaciones SQL dinámicas nativas que se pueden realizar en los datos de la base de datos hr.

  operación DML de muestra usando SQL dinámico nativo

  el siguiente procedimiento SQL dinámico nativo da un aumento a todos los empleados con un título de trabajo particular:

  operación DDL de muestra usando SQL dinámico nativo

  la instrucciónEXECUTE IMMEDIATE puede realizar operaciones DDL., Por ejemplo, el siguiente procedimiento agrega una ubicación de office:

  el siguiente procedimiento elimina una ubicación de office:

  consulta de una sola fila de muestra mediante SQL dinámico nativo

  la instrucción EXECUTE IMMEDIATE puede realizar consultas dinámicas de una sola fila. Puede especificar variables de enlace en la cláusula USING y obtener la fila resultante en el destino especificado en la cláusula INTO de la instrucción.,

  la siguiente función recupera el número de empleados en una ubicación particular que realizan un trabajo especificado:

  muestra de consulta de varias filas utilizando SQL dinámico nativo

  las instrucciones OPEN-FOR, FETCH, y CLOSE pueden consultas de fila., Por ejemplo, el siguiente procedimiento enumera todos los empleados con un trabajo en particular en una ubicación especificada:

  elegir entre SQL dinámico nativo y el paquete DBMS_SQL

  Oracle proporciona dos métodos para usar SQL dinámico dentro de PL/SQL: SQL dinámico nativo y el paquete DBMS_SQL. SQL dinámico nativo le permite colocar instrucciones SQL dinámicas directamente en el código PL / SQL. Estas instrucciones dinámicas incluyen instrucciones DML (incluidas las consultas), bloques anónimos PL/SQL, instrucciones DDL, instrucciones de control de transacciones y instrucciones de control de sesión.,

  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.

  
  Note:

  To use native dynamic SQL, the COMPATIBLE initialization parameter must be set to 8.1.0 or higher. See Oracle9i Database Migration for more information about the COMPATIBLE parameter.,

  el paquete DBMS_SQL es una biblioteca PL / SQL que ofrece una API para ejecutar sentencias SQL dinámicamente. El paquete DBMS_SQL tiene procedimientos para abrir un cursor, analizar un cursor, proporcionar enlaces, etc. Los programas que usan el paquete DBMS_SQL hacen llamadas a este paquete para realizar operaciones SQL dinámicas.

  las siguientes secciones proporcionan información detallada sobre las ventajas de ambos métodos.,

  vea también:

  la guía y referencia del usuario de PL/SQL para obtener información detallada sobre el uso de SQL dinámico nativo y la referencia de tipos y paquetes de PL/SQL suministrados por Oracle9i para obtener información detallada sobre el uso del paquete DBMS_SQL. En la guía y referencia del usuario de PL/SQL, el SQL dinámico nativo se conoce simplemente como SQL dinámico.,

  ventajas de Native Dynamic SQL

  native dynamic SQL proporciona las siguientes ventajas sobre el paquete DBMS_SQL:

  native Dynamic SQL es fácil de usar

  debido a que native dynamic SQL está integrado con SQL, puede usarlo de la misma manera que usa static SQL dentro del código PL / SQL. El código SQL dinámico nativo suele ser más compacto y legible que el código equivalente que utiliza el paquete DBMS_SQL.,

  con el paquete DBMS_SQL debe llamar a muchos procedimientos y funciones en una secuencia estricta, haciendo que incluso las operaciones simples requieran mucho código. Puede evitar esta complejidad utilizando SQL dinámico nativo en su lugar.

  la tabla 8-1 ilustra la diferencia en la cantidad de código requerido para realizar la misma operación usando el paquete DBMS_SQL y SQL dinámico nativo.,

  tabla 8-1 comparación de Código del paquete DBMS_SQL y SQL dinámico nativo

  el SQL dinámico nativo es más rápido que DBMS_SQL

  el SQL dinámico nativo en PL / SQL tiene un rendimiento comparable al de SQL estático, porque el intérprete PL/SQL tiene soporte incorporado para él. Los programas que usan SQL dinámico nativo son mucho más rápidos que los programas que usan el paquete DBMS_SQL. Normalmente, las sentencias SQL dinámicas nativas realizan de 1,5 a 3 veces mejores que las llamadas equivalentes DBMS_SQL. (Sus ganancias de rendimiento pueden variar dependiendo de su aplicación.,)

  native dynamic SQL agrupa los pasos de preparación, enlace y ejecución de la instrucción en una sola operación, lo que minimiza la sobrecarga de copia de datos y llamada al procedimiento y mejora el rendimiento.

  el paquete DBMS_SQL se basa en una API de procedimiento e incurre en una alta sobrecarga de llamada de procedimiento y copia de datos. Cada vez que enlaza una variable, el paquete DBMS_SQL copia la variable de enlace PL/SQL en su espacio para usarla durante la ejecución., Cada vez que ejecuta una recuperación, los datos se copian en el espacio administrado por el paquete DBMS_SQL y luego los datos obtenidos se copian, una columna a la vez, en las variables PL/SQL apropiadas, lo que resulta en una sobrecarga sustancial.

  sugerencia de rendimiento: uso de variables de enlace

  Cuando se utiliza SQL dinámico nativo o el paquete DBMS_SQL, puede mejorar el rendimiento mediante el uso de variables de enlace, ya que las variables de enlace permiten a Oracle compartir un único cursor para varias sentencias SQL.,

  por ejemplo, el siguiente código SQL dinámico nativo no utiliza variables de enlace:

  para cada variable my_deptno, se crea un nuevo cursor, lo que causa contención de recursos y un rendimiento deficiente. En su lugar, bind my_deptno como una variable bind:

  Aquí, el mismo cursor se reutiliza para diferentes valores del bind my_deptno, mejorando el rendimiento y la escalabilidad.,

  SQL dinámico nativo admite tipos definidos por el Usuario

  SQL dinámico nativo admite todos los tipos admitidos por SQL estático en PL/SQL, incluidos los tipos definidos por el usuario, como objetos definidos por el usuario, colecciones y REFs. El paquete DBMS_SQL no admite estos tipos definidos por el usuario.

  
  Nota:

  el paquete DBMS_SQL proporciona soporte limitado para matrices. Consulte la referencia de tipos y paquetes PL/SQL suministrados por Oracle9i para obtener más información.,

  el SQL dinámico nativo admite la búsqueda en Registros

  tanto el SQL dinámico nativo como el SQL estático admiten la búsqueda en registros, pero el paquete DBMS_SQL no lo hace. Con SQL dinámico nativo, las filas resultantes de una consulta se pueden obtener directamente en registros PL/SQL.,

  en el siguiente ejemplo, las filas de una consulta se obtienen en el registro emp_rec:

  ventajas del paquete DBMS_SQL

  el paquete DBMS_SQL proporciona las siguientes ventajas sobre SQL dinámico nativo:

  DBMS_SQL es compatible programas

  El paquete DBMS_SQL es compatible con programas del lado del cliente, pero no con SQL dinámico nativo., Cada llamada al paquete DBMS_SQL del programa del lado del cliente se traduce en una llamada a procedimiento remoto PL/SQL (RPC); estas llamadas ocurren cuando necesita enlazar una variable, definir una variable o ejecutar una instrucción.

  DBMS_SQL soporta describir

  el procedimientoDESCRIBE_COLUMNS en el paquete DBMS_SQL se puede usar para describir las columnas de un cursor abierto y analizado a través de DBMS_SQL. Esta característica es similar al comando DESCRIBE en SQL*Plus., SQL dinámico nativo no tiene una instalación DESCRIBE.

  DBMS_SQL admite actualizaciones de varias filas y elimina con una cláusula de retorno

  el paqueteDBMS_SQL admite sentencias con una cláusulaRETURNING que actualiza o elimina varias filas. SQL dinámico nativo solo admite una cláusula RETURNING si se devuelve una sola fila.,

  
  See Also:

  «Performing DML with RETURNING Clause Using Dynamic SQL: Example» for examples of DBMS_SQL package code and native dynamic SQL code that uses a RETURNING clause.

  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., Después del análisis inicial, puede usar la instrucción varias veces con diferentes conjuntos de argumentos de enlace.

  native dynamic SQL prepara una instrucción SQL cada vez que se usa la instrucción, lo que normalmente implica análisis, optimización y generación de planes. Aunque las operaciones de preparación adicionales incurren en una pequeña penalización de rendimiento, la desaceleración suele ser compensada por los beneficios de rendimiento de SQL dinámico nativo.,

  ejemplos de código de Paquete DBMS_SQL y código SQL dinámico nativo

  los siguientes ejemplos ilustran las diferencias en el código necesario para completar operaciones con el paquete DBMS_SQL y SQL dinámico nativo. Específicamente, se presentan los siguientes tipos de ejemplos:

  • Una consulta
  • Una operación DML
  • Una operación de retorno DML

  en general, el código SQL dinámico nativo es más legible y compacto, lo que puede mejorar la productividad del desarrollador.,

  consulta mediante SQL dinámico: ejemplo

  el siguiente ejemplo incluye una instrucción de consulta dinámica con una variable bind (:jobname) y dos columnas select (ename y sal):

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

  este ejemplo consulta a los empleados con la descripción del trabajo SALESMAN en la columna job de la tabla emp. La tabla 8-2 muestra el código de ejemplo que realiza esta consulta utilizando el paquete DBMS_SQL y SQL dinámico nativo.,

  Table 8-2 Querying Using the DBMS_SQL Package and Native Dynamic SQL

  Performing DML Using Dynamic SQL: Example

  el siguiente ejemplo incluye una instrucción dinámica INSERT para una tabla con tres columnas:

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

  Este ejemplo inserta una nueva fila para la que la columna los valores están en las variables PL/SQL deptnumber, deptname, y location. La tabla 8-3 muestra el código de ejemplo que realiza esta operación DML usando el paquete DBMS_SQL y SQL dinámico nativo.,

  tabla 8-3 operación DML Usando el paquete DBMS_SQL y SQL dinámico nativo

  realizar DML con cláusula RETURNING usando SQL dinámico: ejemplo

  el siguiente ejemplo usa una instrucción dinámica UPDATE para actualizar la ubicación de un departamento, luego devuelve el nombre del departamento:

  stmt_str := 'UPDATE dept_new SET loc = :newloc WHERE deptno = :deptno RETURNING dname INTO :dname';

  la tabla 8-4 muestra el código de ejemplo que realiza esta operación utilizando el paquete DBMS_SQL y SQL dinámico nativo.,

  Table 8-4 DML Returning Operation Using the DBMS_SQL Package and Native Dynamic SQL

  Using Dynamic SQL in Languages Other Than PL/SQL

  aunque este capítulo trata sobre el soporte de PL/SQL para dynamic SQL, puede llamar a dynamic SQL desde otros lenguajes:

  • Si utiliza C/C++, puede llamar a dynamic SQL con Oracle Call Interface (OCI), o puede extensiones SQL dinámicas a su código C.
  • Si usa COBOL, puede usar el precompilador Cobol Pro * para agregar extensiones SQL dinámicas a su código COBOL.,
  • Si utiliza Java, puede desarrollar aplicaciones que utilicen SQL dinámico con JDBC.

  Si tiene una aplicación que utiliza OCI, Pro * C / C++ O PRO * COBOL para ejecutar SQL dinámico, debe considerar cambiar a SQL dinámico nativo dentro de procedimientos y funciones almacenados PL/SQL. Los viajes de ida y vuelta de red necesarios para realizar operaciones SQL dinámicas desde aplicaciones del lado del cliente pueden perjudicar el rendimiento. Los procedimientos almacenados pueden residir en el servidor, eliminando la sobrecarga de red., Puede llamar a los procedimientos almacenados PL / SQL y a las funciones almacenadas desde la aplicación OCI, Pro*C/C++ O PRO*COBOL.,br>Ver también:

  para obtener información sobre cómo llamar a procedimientos almacenados de Oracle y funciones almacenadas desde varios idiomas, consulte:

  • 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

  usando registros PL/SQL En instrucciones SQL INSERT y UPDATE

  aunque puede enumerar cada campo de un registro PL/SQL al insertar o actualizar filas en una tabla, el código resultante no es especialmente legible ni se puede mantener., En su lugar, puede usar registros PL/SQL directamente en estas instrucciones. La técnica más conveniente es declarar el registro usando un atributo % ROWTYPE, para que tenga exactamente los mismos campos que la tabla SQL.

  aunque esta técnica ayuda a integrar variables y tipos PL/SQL más estrechamente con instrucciones DML SQL, no puede usar registros PL / SQL como variables de enlace en instrucciones SQL dinámicas.