8 SQL dinámico

SQL dinámico es una técnica de programación que le permite construir sentencias SQL de forma dinámica en tiempo de ejecución. Puede crear aplicaciones flexibles de uso más general utilizando SQL dinámico porque el texto completo de una declaración SQL puede ser desconocido en la compilación. Por ejemplo, SQL dinámico le permite crear un procedimiento que opera en una tabla cuyo nombre no se conoce hasta el tiempo de ejecución.

En versiones anteriores de Oracle, la única forma de implementar SQL dinámico en una aplicación PL / SQL era utilizando el paquete DBMS_SQL. Oracle8i presenta SQL dinámico nativo, una alternativa al paquete DBMS_SQL. Usando SQL dinámico nativo, puede colocar sentencias SQL dinámicas directamente en bloques PL / SQL.

Este capítulo cubre los siguientes temas:

• ¿Qué es SQL dinámico?
• Cuándo utilizar SQL dinámico
• Un escenario de SQL dinámico utilizando SQL dinámico nativo
• SQL dinámico nativo frente al paquete DBMS_SQL
• Aplicación Lenguajes de desarrollo distintos a PL / SQL

¿Qué es SQL dinámico?

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

• Una compilación exitosa verifica que las declaraciones SQL hacen referencia a objetos válidos de la base de datos.
• Una 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 el de 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 complicado 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 sentencias SQL que deben ejecutarse en un procedimiento PL / SQL. Su programa puede aceptar la entrada del usuario que define las declaraciones 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 utilizar SQL dinámico.

Por ejemplo, considere una aplicación de informes que realiza consultas estándar en tablas en un entorno de almacenamiento de datos donde se desconoce el nombre exacto de la tabla hasta el tiempo de ejecución. Para acomodar la gran cantidad de datos en el almacén de datos de manera eficiente, crea una nueva tabla cada trimestre para almacenar la información de la factura del trimestre. Todas estas tablas tienen exactamente la misma definición y se nombran según el mes de inicio y el año del trimestre, por ejemplo, INV_01_1997, INV_04_1997, INV_07_1997, INV_10_1997, INV_01_1998, etc. En tal caso, puede utilizar SQL en su aplicación de informes para especificar el nombre de la tabla en tiempo de ejecución.

Con SQL estático, toda la información de definición de datos, como las definiciones de tabla, a la que hacen referencia las declaraciones SQL en su programa debe conocerse en la compilación. Si cambia la definición de datos, debe cambiar y volver a compilar el programa. Los programas de SQL dinámico pueden manejar cambios en la información de definición de datos, porque las sentencias SQL pueden cambiar «sobre la marcha» en tiempo de ejecución. Por tanto, el SQL dinámico es mucho más flexible que el SQL estático. SQL dinámico le permite escribir código de aplicación que es reutilizable porque el código define un proceso que es independiente de las sentencias SQL específicas utilizadas.

Además, SQL dinámico le permite ejecutar sentencias SQL que no son compatibles con estática. Programas SQL, como declaraciones de lenguaje de definición de datos (DDL). El soporte para estas declaraciones le permite lograr más con sus programas PL / SQL.

Cuándo usar SQL dinámico

Debe usar SQL dinámico en los casos en que el SQL estático no admita el 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. Las siguientes secciones describen situaciones típicas en las que debe usar SQL dinámico y problemas típicos que se pueden resolver mediante SQL dinámico.

Para ejecutar declaraciones DML dinámicas

Puede usar SQL dinámico para ejecutar Sentencias DML en las que no se conoce la sentencia SQL exacta hasta el tiempo de ejecución.Para obtener ejemplos, consulte los ejemplos de DML en los «Ejemplos de código de paquete DBMS_SQL y código SQL dinámico nativo» y «Ejemplo de operación DML».

Para ejecutar sentencias no admitidas por SQL estático en PL / SQL

En PL / SQL, no puede ejecutar los siguientes tipos de declaraciones usando SQL estático:

• Declaraciones del lenguaje de definición de datos (DDL), como CREATE, DROP, GRANT y REVOKE
• Declaraciones de lenguaje de control de sesión (SCL), como ALTER SESSION y SET ROLE
  Consulte también:

  Referencia de SQL de Oracle8i para obtener información sobre las sentencias DDL y SCL.

Utilice SQL dinámico si necesita ejecutar cualquiera de estos tipos de declaraciones dentro de un PL / Bloque SQL.

Además, SQL estático en PL / SQL no permite el uso de la cláusula TABLE en la SELECT declaraciones. No existe tal limitación en SQL dinámico. Por ejemplo, el siguiente bloque PL / SQL contiene una instrucción SELECT que usa la cláusula TABLE y SQL dinámico nativo:

Para ejecutar consultas dinámicas

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

• Aplicaciones que permiten a los usuarios ingresar o elegir criterios de clasificación o búsqueda de consultas en tiempo de ejecución
• Aplicaciones que permiten a los usuarios ingresar o elija 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 nuevas tablas a menudo

Para ver ejemplos, consulte «Ejemplo de consulta» y los ejemplos de consulta en «Un escenario de SQL dinámico con SQL dinámico nativo».

Para 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, puede ser posible determinar la definición de las tablas de la base de datos en la compilación, pero no los nombres de las tablas, porque periódicamente se generan nuevas tablas. Su aplicación necesita acceder a los datos, pero no hay forma de saber los nombres exactos de las tablas hasta el tiempo de ejecución.

El SQL dinámico puede resolver este problema, porque el SQL dinámico le permite esperar hasta el tiempo de ejecución para especificar el nombres de tabla a los que necesita acceder. Por ejemplo, en la aplicación de almacenamiento de datos de muestra analizada en «¿Qué es SQL dinámico?», Se generan nuevas tablas cada trimestre, y estas tablas siempre tienen la misma definición. En este caso, 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:

Para optimizar la ejecución dinámicamente

Si usa estática SQL, debe decidir en la compilación cómo desea construir sus declaraciones SQL, si debe tener sugerencias en sus declaraciones y, si incluye sugerencias, exactamente qué sugerencias tener. Sin embargo, puede usar SQL dinámico para construir una declaración SQL de una manera que optimice la ejecución y / o concatene las sugerencias en una declaración SQL de forma dinámica. Esto le permite cambiar las sugerencias en función de las estadísticas de su base de datos actual, sin necesidad de volver a compilar.

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

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

Para invocar bloques dinámicos PL / SQL

Puede utilizar EXECUTE IMMEDIATE para invocar bloques PL / SQL anónimos. La capacidad de invocar bloques PL / SQL dinámicos puede ser útil para la extensión y personalización de la aplicación donde el módulo que se ejecutará se determina dinámicamente en tiempo de ejecución.

Por ejemplo, suponga que desea escribir una aplicación que toma un evento número y se envía a un controlador para el evento. El nombre del controlador tiene el formato EVENT_HANDLER_ event_num, donde event_num es el número del evento. Un enfoque sería implementar el despachador como una declaración de cambio, como se muestra a continuación, donde el código maneja cada evento haciendo una llamada estática a su manejador 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. Sin embargo, usando SQL dinámico nativo, puede escribir un despachador de eventos extensible similar al siguiente:

Para realizar operaciones dinámicas usando derechos de invocador

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

Un escenario de SQL dinámico utilizando SQL dinámico nativo

El escenario descrito en esta sección ilustra el poder y la flexibilidad de SQL dinámico nativo. Este escenario incluye ejemplos que le muestran cómo realizar las siguientes operaciones usando SQL dinámico nativo:

• Ejecutar operaciones DDL y DML
• Ejecutar consultas de una sola fila y de múltiples filas

Modelo de datos

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)

Varias emp_ tablas de ubicación contienen la información del empleado, donde ubicación es el nombre de la ciudad donde se encuentra la oficina se encuentra. Por ejemplo, una tabla llamada emp_houston contiene información de empleados para la oficina de la empresa en Houston, mientras que una tabla llamada emp_boston contiene información para la oficina de Boston de la empresa.

Cada emp_ La tabla de ubicación tiene la siguiente definición:

Las siguientes secciones describen varias operaciones de SQL dinámico nativo que se pueden realizar en los datos en el hr base de datos.

Ejemplo de operación de DML

El siguiente procedimiento de SQL dinámico nativo da un aumento a todos los empleados con un puesto de trabajo en particular:

Ejemplo de operación de DDL

La instrucción EXECUTE IMMEDIATE puede realizar operaciones DDL. Por ejemplo, el siguiente procedimiento agrega una ubicación de oficina:

El siguiente procedimiento elimina una ubicación de oficina:

Consulta dinámica de muestra de una sola fila

El 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 declaración.

La siguiente función recupera la cantidad de empleados en una ubicación particular que realizan un trabajo específico:

Consulta dinámica de muestra de varias filas

La OPEN-FOR, FETCH y CLOSE pueden realizar consultas dinámicas de varias filas. Por ejemplo, el siguiente procedimiento enumera a todos los empleados con un trabajo en particular en una ubicación específica:

SQL dinámico nativo frente al 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. El SQL dinámico nativo le permite colocar sentencias SQL dinámicas directamente en el código PL / SQL. Estas declaraciones dinámicas incluyen declaraciones DML (incluidas consultas), bloques anónimos PL / SQL, declaraciones DDL, declaraciones de control de transacciones y declaraciones de control de sesión.

Para procesar la mayoría de las declaraciones SQL dinámicas nativas, utilice el EXECUTE IMMEDIATE declaración. Sin embargo, para procesar una consulta de varias filas (SELECT instrucción), utilice OPEN-FOR, FETCH y CLOSE declaraciones.

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

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

Ventajas del SQL dinámico nativo

El SQL dinámico nativo ofrece las siguientes ventajas sobre DBMS_SQL paquete:

Facilidad de uso

El SQL dinámico nativo es mucho más sencillo de usar que el paquete DBMS_SQL.Debido a que el SQL dinámico nativo está integrado con SQL, puede usarlo de la misma manera que actualmente usa SQL estático dentro del código PL / SQL. Además, el código SQL dinámico nativo suele ser más compacto y legible que el código equivalente que usa el paquete DBMS_SQL.

El DBMS_SQL no es tan fácil de usar como el SQL dinámico nativo. Hay muchos procedimientos y funciones que deben usarse en una secuencia estricta. Normalmente, realizar operaciones sencillas requiere una gran cantidad de código cuando se utiliza el paquete DBMS_SQL. 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 necesaria para realizar la misma operación utilizando DBMS_SQL paquete y SQL dinámico nativo.

Tabla 8-1 Comparación de código del paquete DBMS_SQL y SQL dinámico nativo

Mejoras de rendimiento

El rendimiento del SQL dinámico nativo en PL / SQL es comparable al rendimiento del SQL estático porque el intérprete PL / SQL tiene soporte incorporado para SQL dinámico nativo. Por lo tanto, el rendimiento de los programas que usan SQL dinámico nativo es mucho mejor que el de los programas que usan el paquete DBMS_SQL. Normalmente, las sentencias SQL dinámicas nativas funcionan entre 1,5 y 3 veces mejor que las sentencias equivalentes que utilizan el paquete DBMS_SQL. Por supuesto, sus ganancias de rendimiento pueden variar según su aplicación.

El paquete DBMS_SQL se basa en una API de procedimiento y, como resultado, incurre en una gran sobrecarga de llamadas a procedimientos y copia de datos. Por ejemplo, cada vez que vincula una variable, el paquete DBMS_SQL copia la variable de vinculación PL / SQL en su espacio para su uso posterior durante la ejecución. De manera similar, cada vez que ejecuta una búsqueda, primero 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 el Variables PL / SQL, lo que resulta en una sobrecarga sustancial resultante de la copia de datos. Por el contrario, el SQL dinámico nativo agrupa los pasos de preparación, enlace y ejecución de declaraciones en una sola operación, lo que minimiza la copia de datos y la sobrecarga de llamadas a procedimientos y mejora el rendimiento.

Consejo de rendimiento

Al utilizar SQL dinámico nativo o el paquete DBMS_SQL, puede mejorar el rendimiento mediante el uso de variables de vinculación, ya que el uso de variables de vinculación permite que Oracle comparta un solo cursor para varias declaraciones de SQL.

Por ejemplo, el siguiente código SQL dinámico nativo no usa variables de vinculación:

Para cada variable my_deptno distinta, se crea un nuevo cursor, que puede provocar contención de recursos y un rendimiento deficiente. En su lugar, vincule my_deptno como una variable de vinculación, como en el siguiente ejemplo:

Aquí, el mismo cursor se reutiliza para diferentes valores de la vinculación my_deptno, mejorando así el rendimiento y la escalabilidad.

Compatibilidad con tipos definidos por el usuario

El SQL dinámico nativo admite todos los tipos admitidos por SQL estático en PL / SQL. Por lo tanto, SQL dinámico nativo brinda soporte para 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.

Soporte para recuperar en registros

Tanto el SQL dinámico nativo como el SQL estático admiten la recuperación en registros, pero el DBMS_SQL paquete no. Con SQL dinámico nativo, las filas resultantes de una consulta se pueden recuperar directamente en registros PL / SQL.

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

Ventajas del paquete DBMS_SQL

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

Soporte para programas del lado del cliente

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

Soporte para DESCRIBE

El procedimiento DESCRIBE_COLUMNS en el paquete DBMS_SQL se puede utilizar para describe las columnas de un cursor abierto y analizado a través de DBMS_SQL. La funcionalidad es similar al comando DESCRIBE en SQL * Plus. El SQL dinámico nativo no tiene una función DESCRIBE.

Soporte para SQL dinámico masivo

SQL masivo es la capacidad de procesar múltiples filas de datos en una sola declaración DML. Bulk SQL mejora el rendimiento al reducir la cantidad de cambio de contexto entre SQL y el lenguaje host. Actualmente, el paquete DBMS_SQL admite SQL dinámico masivo.

Aunque no hay soporte directo para operaciones masivas en SQL dinámico nativo, puede simular un SQL masivo dinámico nativo. declaración colocando la declaración SQL masiva en un bloque «BEGIN … END» y ejecutando el bloque dinámicamente. Esta solución le permite obtener los beneficios de SQL masivo dentro de un programa de SQL dinámico nativo. Por ejemplo, el siguiente código SQL dinámico nativo copia la ename columna de una tabla a otra:

Actualizaciones y eliminaciones de varias filas con una cláusula RETURNING

El paquete DBMS_SQL admite declaraciones con una RETURNING cláusula que actualiza o elimina varias filas. SQL dinámico nativo solo admite una cláusula RETURNING si se devuelve una sola fila.

Compatibilidad con sentencias SQL de más de 32 KB

El DBMS_SQL el paquete admite sentencias SQL de más de 32 KB; SQL dinámico nativo no lo hace.

Reutilización de sentencias SQL

El procedimiento PARSE en el DBMS_SQL paquete analiza una declaración SQL una vez. Después del análisis inicial, la declaración se puede usar varias veces con diferentes conjuntos de argumentos de vinculación.

Por el contrario, el SQL dinámico nativo prepara una declaración SQL para su ejecución cada vez que se utiliza la declaración. La preparación de declaraciones generalmente implica análisis, optimización y generación de planes. La preparación de una declaración cada vez que se utiliza conlleva una pequeña penalización de rendimiento. Sin embargo, el mecanismo de cursor compartido de Oracle minimiza el costo, y la penalización del rendimiento suele ser trivial en comparación con los beneficios de rendimiento del 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.

Ejemplo de consulta

El siguiente ejemplo incluye una declaración de consulta dinámica con una variable de vinculación (:jobname) y dos columnas seleccionadas (ename y sal):

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

Este ejemplo consulta a empleados con j ob descripción SALESMAN en la job columna de la emp tabla. La Tabla 8-2 muestra un código de muestra que realiza esta consulta mediante el paquete DBMS_SQL y SQL dinámico nativo.

Tabla 8-2 Consulta mediante el paquete DBMS_SQL y SQL dinámico nativo

Ejemplo de DML

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

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

Este ejemplo inserta una nueva fila cuyos valores de columna están en las variables PL / SQL deptnumber, deptname y location. La Tabla 8-3 muestra un código de muestra que logra 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

Ejemplo de devolución de DML

El siguiente ejemplo incluye un UPDATE declaración que actualiza la ubicación de un departamento cuando se le da el número de departamento (deptnumber) y una nueva ubicación (location), y luego devuelve el nombre del departamento:

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

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

Tabla 8-4 Operación de devolución de DML usando DBMS_SQL Paquete y SQL dinámico nativo

Lenguajes de desarrollo de aplicaciones distintos de PL / SQL

Hasta ahora, la discusión en este capítulo ha sido sobre el soporte PL / SQL para SQL dinámico.Sin embargo, puede utilizar otros lenguajes de desarrollo de aplicaciones para implementar programas que utilizan SQL dinámico. Estos lenguajes de desarrollo de aplicaciones incluyen C / C ++, COBOL y Java.

Si usa C / C ++, puede desarrollar aplicaciones que usen SQL dinámico con Oracle Call Interface (OCI), o puede usar el precompilador Pro * C / C ++ para agregar extensiones de SQL dinámico a su C código. De manera similar, si usa COBOL, puede usar el precompilador Pro * COBOL para agregar extensiones SQL dinámicas a su código COBOL. Si usa Java, puede desarrollar aplicaciones que usen SQL dinámico con JDBC.

En el pasado, la única forma de usar SQL dinámico en aplicaciones PL / SQL era usando el DBMS_SQL paquete. Existen varias limitaciones para el uso de este paquete, incluidas las cuestiones de rendimiento. En consecuencia, los desarrolladores de aplicaciones pueden haber utilizado una de las alternativas a PL / SQL discutidas anteriormente para implementar SQL dinámico. Sin embargo, con la introducción de SQL dinámico nativo en PL / SQL, ahora se eliminan muchos de los inconvenientes de usar PL / SQL para SQL dinámico.

Si tiene una aplicación que usa OCI, Pro * C / C ++ o Pro * COBOL para la ejecución dinámica de SQL, los viajes de ida y vuelta de la red necesarios para realizar operaciones de SQL dinámico pueden afectar el rendimiento. Debido a que estas aplicaciones normalmente residen en clientes, se requieren más llamadas de red para completar las operaciones de SQL dinámico. Si tiene este tipo de aplicación, considere mover la funcionalidad de SQL dinámico a procedimientos almacenados y funciones almacenadas en PL / SQL que usan SQL dinámico nativo. Si lo hace, podría mejorar el rendimiento de su aplicación porque los procedimientos almacenados pueden residir en el servidor, eliminando así la sobrecarga de la red. A continuación, puede llamar a los procedimientos almacenados PL / SQL y las funciones almacenadas desde la aplicación.