Objetivos:• Sentencias DDL y PL/SQL.
--SQL Dinámico Nativo.
----Sentencia EXECUTE IMMEDIATE.
----Sentencias OPEN-FOR, FETCH y CLOSE
--Package DBMS_SQL.
• SQL Dinámico Nativo vs Package DBMS_SQL.
NOTA: Usamos como ejemplo la Base de Datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.
______________________________________________________________________________________
SQL Dinámico.
SQL Dinámico es una metodología de programación que genera y ejecuta sentencias SQL en tiempo de ejecución. Es útil cuando se escriben programas flexibles y de propósito general como sistemas que usan Consultas Ad-Hoc (Ad-Hoc Queries), cuando se escriben programas que deben ejecutar instrucciones DDL o cuando en tiempo de compilación no se sabe el texto completo de una instrucción SQL o el número o tipos de datos de sus variables de entrada/salida.Formas de escribir SQL dinámico PL/SQL:
• SQL dinámico nativo, una característica de lenguaje PL/SQL (es decir, nativa) para crear y ejecutar instrucciones SQL dinámicas.
• Package DBMS_SQL, una API para crear, ejecutar y describir sentencias SQL dinámicas.
Un código SQL dinámico nativo resulta más fácil de leer y escribir que su equivalente utilizando el paquete DBMS_SQL, de igual forma el primero se ejecuta notablemente más rápido (especialmente cuando puede ser optimizado por el compilador). Sin embargo, para escribir código SQL dinámico nativo, debe saber en tiempo de compilación el número y los tipos de datos de las variables de entrada y salida de la instrucción SQL dinámica. Si en tiempo de compilación no conoce esta información, debe utilizar el paquete DBMS_SQL.
En caso de necesitar ambos (Paquete DBMS_SQL y SQL dinámico nativo), puede cambiar entre ellos utilizando la función DBMS_SQL.TO_REFCURSOR y la función DBMS_SQL.TO_CURSOR_NUMBER.
Necesidad de SQL Dinámico.
En PL/SQL, se necesita SQL dinámico para ejecutar lo siguiente:
• SQL cuyo texto es desconocido en tiempo de compilación.
Ejemplo: Una instrucción SELECT que incluye un identificador desconocido en tiempo de compilación (como un nombre de tabla) o una cláusula WHERE en la que el número de subcláusulas es desconocido en tiempo de compilación.
• Cualquier construcción SQL que no sea compatible como SQL Estático.
Si no necesita SQL Dinámico, utilice SQL Estático, el cual tiene las siguientes ventajas:
• Una compilación exitosa comprueba que las sentencias SQL estáticas hagan referencia a objetos de base de datos válidos y que el usuario tenga los privilegios necesarios para acceder a esos objetos.
• La compilación exitosa crea dependencias de objetos de esquema.
Uso de SQL Dinámico Nativo.
SQL dinámico nativo procesa la mayoría de las sentencias SQL dinámicas por medio de la sentencia EXECUTE IMMEDIATE.
Si la sentencia SQL dinámica es una instrucción SELECT que devuelve varias filas, SQL dinámico nativo le ofrece las siguientes opciones:
• Utilice la sentencias EXECUTE IMMEDIATE con la cláusula BULK COLLECT INTO.
• Utilice las sentencias OPEN-FOR, FETCH y CLOSE.
Puede utilizar variables Bind como parámetros dinámicos en las sentencias EXECUTE IMMEDIATE y OPEN. SQL Dinámico Nativo incluye las siguientes capacidades:
• Define la sentencia SQL dinámica.
• PL/SQL soporta instancias Bind de cualquier tipo de datos SQL.
• Maneja variables Bind con modo: IN, IN OUT y OUT que están enlazadas por posición, no por nombre.
Nota: Los Atributos de Cursor SQL funcionan de la misma manera con SQL dinámico nativo como con SQL Estático.
______________________________________________________________________________________
Sentencias DDL y PL/SQL.
PL/SQL (Procedural Language/Structured Query Language) es un Lenguaje Procedimental o Lenguaje de Procedimiento combinado con SQL que tiene como fin fundamental facilitar el manejo de los datos en una Base de Datos Oracle. PL/SQL garantiza la correcta y completa consistencia de la información, todo esto, exponiendo la base de datos sólo a través de una interfaz que oculta los detalles de la implementación.
Por la anterior introducción podemos deducir que una de las razones por la cual no es posible introducir sentencias DDL en un bloque de PL/SQL es porque dicho lenguaje está orientado al manejo de los datos. Es bueno recargar que las sentencias DDL cambian la definición del esquema, o sea, crean, modifican o destruyen objetos de base de datos (Tablas, Columnas, Indices y demás) y una operación como tal, por lo general es realizada una sola vez.
Sin embargo, a veces surgen casos en los cuales se requiere realizar operaciones inusuales tales como la necesidad de ejecutar una operación DDL en un bloque de PL/SQL. Para estas excepciones nos valemos del SQL Dinámico (Dynamic SQL) presentado a continuación.
Por la anterior introducción podemos deducir que una de las razones por la cual no es posible introducir sentencias DDL en un bloque de PL/SQL es porque dicho lenguaje está orientado al manejo de los datos. Es bueno recargar que las sentencias DDL cambian la definición del esquema, o sea, crean, modifican o destruyen objetos de base de datos (Tablas, Columnas, Indices y demás) y una operación como tal, por lo general es realizada una sola vez.
Sin embargo, a veces surgen casos en los cuales se requiere realizar operaciones inusuales tales como la necesidad de ejecutar una operación DDL en un bloque de PL/SQL. Para estas excepciones nos valemos del SQL Dinámico (Dynamic SQL) presentado a continuación.
______________________________________________________________________________________
Sentencia EXECUTE IMMEDIATE.
La instrucción EXECUTE IMMEDIATE ejecuta una sentencia SQL dinámica o un bloque PL/SQL anónimo. Puede usarla para emitir sentencias SQL que no se pueden incluir directamente en PL/SQL o para generar sentencias en las que no se conocen los nombres de tabla, cláusulas WHERE, etc.Sintaxis:
execute_immediate_statement ::=
EXECUTE_IMMEDIATE dynamic_string
[ INTO { define_variable [, define_variable ...] | record_name } ]
[ USING [ IN | OUT | IN OUT ] bind_argument
[, [ IN | OUT | IN OUT ] bind_argument] ... ]
[ {RETURNING | RETURN } INTO bind_argument [, bind_argument]... ];
Donde:
• bind_argument: Una expresión cuyo valor se pasa a la instrucción SQL dinámica o una variable que almacena un valor devuelto por la instrucción SQL dinámica.
• define_variable: Una variable que almacena el valor de una columna seleccionada.
• dynamic_string: La cadena literal, variable o expresión que representa una sola sentencia SQL o un bloque PL/SQL. Debe ser de tipo CHAR o VARCHAR2, no NCHAR o NVARCHAR2.
• INTO: Utilizada sólo para consultas de una sola fila, esta cláusula especifica las variables o registros en los que se recuperan los valores de las columnas. Para cada valor recuperado por la consulta, debe haber una variable o campo correspondiente compatible con el tipo en la cláusula INTO.
• record_name: Record %ROWTYPE (o definido por el usuario) que almacena una fila seleccionada.
• RETURNING INTO: Utilizada sólo para sentencias DML que tienen una cláusula RETURNING (sin una cláusula BULK COLLECT), esta cláusula especifica las variables Bind en las que se devuelven los valores de columna. Para cada valor devuelto por la sentencia DML, debe haber una variable correspondiente compatible con el tipo en la cláusula RETURNING INTO.
• USING: Especifica una lista de argumentos bind de entrada y/o salida. El modo de parámetro predeterminado es IN.
Nota: Puede utilizar literales numéricos, de caracteres y cadena como argumentos Bind, pero no puede utilizar literales booleanos (TRUE, FALSE y NULL).
Ejemplos:
CREATE PROCEDURE proc_create_table(
p_table_name VARCHAR2,
p_col_specs VARCHAR2
) IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE '||p_table_name||
'('||p_col_specs||')';
END;
/
/*En el pasado ejemplo creamos el procedimiento: proc_create_table el cual recibe dos parámetros, uno equivalente al nombre de una tabla y el segundo a las especificaciones de dicha tabla.*/
---
BEGIN
proc_create_table('EMPLOYEE_NAMES',
'id NUMBER(4) PRIMARY KEY, name VARCHAR2(40)'
);
END;
/
/*En el bloque anterior vemos como podemos implementar el procedimiento: proc_create_table. El ejemplo crea la tabla: EMPLOYEE_NAMES con dos columnas: id y name*/
---
CREATE PROCEDURE proc_add_col(
p_table_name VARCHAR2,
p_col_spec VARCHAR2
) IS
v_statement VARCHAR2(100) := 'ALTER TABLE '||p_table_name||
' ADD '||p_col_spec;
BEGIN
EXECUTE IMMEDIATE v_statement;
END;
/
/*Ahora creamos el procedimiento: proc_add_col que tiene como función agregar una columna o constraint a una tabla previamente creada.*/
---
BEGIN
proc_add_col('employee_names', 'salary number(8,2)');
END;
/
/*El pasado ejemplo muestra el uso de proc_add_col. Notar como luego de ejecutar el bloque es agregada la columna salary a tabla employee_names.*/
---
DECLARE
v_table_name VARCHAR2(11) := 'my_table';
BEGIN
BEGIN
EXECUTE IMMEDIATE
'CREATE TABLE '||v_table_name||'
(
code NUMBER,
name VARCHAR2(15)
)';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
FOR i IN 1..3 LOOP
EXECUTE IMMEDIATE
'INSERT INTO '||v_table_name||' (code, name)
VALUES (:text_string1, :text_string2)'
USING i, 'nombre'||i;
END LOOP;
END;
/
/
/*En el ejemplo se crea un tabla con un nombre X y posteriormente se insertan 3 registros en dicha tabla. Notar el uso de la cláusula USING combinada con las variables Bind: :text_string1 y :text_string2.*/
---
CREATE FUNCTION proc_del_rows(
p_table_name VARCHAR2
) RETURN NUMBER IS
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM '||p_table_name;
RETURN SQL%ROWCOUNT;
END;
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE(proc_del_rows('my_table')|| ' rows deleted.');
END;
/
/*Ahora usamos la función proc_del_rows para eliminar todos los registros de la tabla: my_table (creada en uno de los ejemplos anteriores), a continuación vemos el OUTPUT.*/
---OUTPUT:
p_table_name VARCHAR2
) RETURN NUMBER IS
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM '||p_table_name;
RETURN SQL%ROWCOUNT;
END;
/
/*En este ejemplo creamos la función: proc_del_rows que no solo elimina los registros de la tabla que recibe como parámetro, sino que también retorna el numero de registros eliminados.*/---SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE(proc_del_rows('my_table')|| ' rows deleted.');
END;
/
/*Ahora usamos la función proc_del_rows para eliminar todos los registros de la tabla: my_table (creada en uno de los ejemplos anteriores), a continuación vemos el OUTPUT.*/
---OUTPUT:
SET SERVEROUTPUT ON
DECLARE
TYPE typ_details IS RECORD
(
code NUMBER,
name VARCHAR2(50),
dept_job VARCHAR2(50)
);
TYPE typ_tab IS
TABLE OF typ_details
INDEX BY BINARY_INTEGER;
v_tab typ_tab;
PROCEDURE proc_bring__details
(
p_col_value VARCHAR2,
p_list_emps OUT typ_tab
) IS
BEGIN
EXECUTE IMMEDIATE
'
SELECT
e.employee_id,
e.first_name||'' ''||e.last_name AS name,
(
SELECT d.department_name
FROM hr.departments d
WHERE d.department_id = e.department_id
)
FROM hr.employees e
WHERE e.job_id = :v_filter
UNION
SELECT
e.employee_id,
e.first_name||'' ''||e.last_name AS name,
(
SELECT j.job_title
FROM hr.jobs j
WHERE j.job_id = e.job_id
)
FROM hr.employees e
WHERE TO_CHAR(e.department_id) = TO_CHAR(:v_filter)
'
BULK COLLECT INTO p_list_emps USING p_col_value, p_col_value;
END proc_bring__details;
BEGIN
proc_bring__details(30, v_tab);
FOR i IN NVL(v_tab.FIRST, 1)..NVL(v_tab.LAST,0) LOOP
DBMS_OUTPUT.PUT_LINE
(
v_tab(i).code||', '||RPAD(v_tab(i).name, 10, ' ')||', '||v_tab(i).dept_job
);
END LOOP;
END;
/
/*En este ejemplo se hizo lo siguiente: Un bloque anónimo que contiene un tipo Record de 3 atributos, una tipo tabla que hace referencia al record anterior y un procedimiento anidado (proc_bring_details) con dos parámetros, un parámetro de entrada tipo carácter y otro de salida tipo tabla (previamente creada). El procedimiento anidado ejecuta una consulta dinámica que filtra por el código de departamento o por el tipo de empleo sin necesidad de que el usuario indique que tipo de filtro desea aplicar; Una vez realizada la consulta los resultados son asignados al parámetro tipo tabla mediante la cláusula BULK COLLECT. La siguiente imagen muestra los resultados dependiendo del valor recibido.*/
---OUTPUT:
---OUTPUT:
______________________________________________________________________________________
Sentencias OPEN-FOR, FETCH y CLOSE.
La sentencia OPEN-FOR ejecuta la instrucción SELECT asociada a una variable tipo cursor. OPEN-FOR asigna recursos de base de datos para procesar la sentencia, identifica el conjunto de resultados (las filas que cumplen las condiciones) y coloca la variable tipo cursor antes de la primera fila en el conjunto de resultados.Con la cláusula USING (opcional), OPEN-FOR procesa una sentencia SELECT dinámica que devuelve varias filas: asocia una variable tipo cursor con el SELECT, ejecuta la sentencia, identifica el conjunto de resultados, posiciona el cursor antes de la primera fila del resultado y pone en cero el recuento de filas procesadas (%ROWCOUNT).
Directrices:
• La cláusula USING no puede contener el NULL literal. Para evitar esta restricción, utilice una variable no inicializada donde desea utilizar NULL.
• Utilice la instrucción FETCH para recuperar las filas de conjunto de resultados de una en una, varias a la vez o todas a la vez.
• Utilice la instrucción CLOSE para cerrar la variable tipo cursor.
Sintaxis:
open_for_statement ::=
OPEN { cursor_variable_name | :host_cursor_variable_name }
FOR select_statement [ using_clause ] ;
using_clause ::=
USING [ IN | OUT | IN OUT ] bind_argument
[ [,] [ [ IN | OUT | IN OUT ] bind_argument ]...
Ejemplo:
CREATE PROCEDURE list_employees(deptid NUMBER) IS
TYPE emp_refcsr IS REF CURSOR;
emp_cv emp_refcsr;
emprec employees%ROWTYPE;
v_statement VARCHAR2(200) := 'SELECT * FROM employees';
BEGIN
IF deptid IS NULL THEN
OPEN emp_cv FOR v_statement;
ELSE
v_statement := v_statement || ' WHERE department_id = :id';
OPEN emp_cv FOR v_statement USING deptid;
END IF;
LOOP
FETCH emp_cv INTO emprec;
EXIT WHEN emp_cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emprec.department_id||
' ' ||emprec.last_name);
END LOOP;
CLOSE emp_cv;
END;
/*En el pasado ejemplo se creo el procedimiento list_employees que muestra en pantalla la lista de empleados de algún departamento especificado, en caso de pasar un valor nulo se mostrarían todos los registros de la tabla employees. Notar el uso de los REF CURSOR.*/
---OUTPUT:
---OUTPUT:
______________________________________________________________________________________
Paquete DBMS_SQL.
Utilizando DBMS_SQL, puede escribir procedimientos almacenados y bloques PL/SQL anónimos que usan SQL Dinámico, como ejecutar sentencias DDL en PL/SQL, por ejemplo, ejecutar una sentencia DROP TABLE. Las operaciones proporcionadas por este paquete se realizan bajo el usuario actual, no bajo el propietario del paquete (SYS). El paquete DBMS_SQL proporciona los siguientes subprogramas para ejecutar SQL dinámico:• OPEN_CURSOR para abrir un nuevo cursor y devolver un número de ID del mismo.
• PARSE para analizar la instrucción SQL, es decir, comprueba la sintaxis de la sentencia y la asocia con el cursor abierto. Las sentencias DDL se ejecutan inmediatamente cuando se analizan.
• BIND_VARIABLE para enlazar un valor dado a una variable Bind identificada por su nombre en la sentencia analizada. No es necesario si la sentencia no tiene variables Bind.
• EXECUTE para ejecutar la instrucción SQL y devolver el número de filas procesadas.
• FETCH_ROWS para recuperar la siguiente fila de una consulta (se usa en un bucle para varias filas).
• CLOSE_CURSOR para cerrar el cursor especificado.
Nota: El uso del paquete DBMS_SQL para ejecutar instrucciones DDL puede resultar en un interbloqueo. Por ejemplo, al utilizar dicho paquete para eliminar un procedimiento que todavía está utilizando.
Para procesar dinámicamente una sentencia DML, realice los pasos siguientes:
1. Utilice OPEN_CURSOR para establecer un área en la memoria para procesar una instrucción SQL.
2. Utilice PARSE para establecer la validez de la instrucción SQL.
3. Utilice la función EXECUTE para ejecutar la instrucción SQL. Esta función devuelve el número de filas procesadas.
4. Utilice CLOSE_CURSOR para cerrar el cursor.
Los pasos para ejecutar una instrucción DDL son similares; Pero el paso 3 es opcional porque una instrucción DDL se ejecuta inmediatamente cuando el PARSE se realiza con éxito, es decir, la sintaxis de la sentencia y la semántica son correctas. Si utiliza la función EXECUTE con una instrucción DDL , no haría nada y devolvería un valor de 0 para el número de filas procesadas, ya que las instrucciones DDL no procesan filas.
Ejemplo:
CREATE OR REPLACE FUNCTION delete_all_rows
(
table_name VARCHAR2
) RETURN NUMBER IS
csr_id INTEGER;
rows_del NUMBER;
BEGIN
csr_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(csr_id,'DELETE FROM '||table_name, DBMS_SQL.NATIVE);
rows_del := DBMS_SQL.EXECUTE (csr_id);
DBMS_SQL.CLOSE_CURSOR(csr_id);
RETURN rows_del;
END;
/
/*En el anterior ejemplo notamos el uso de paquete DBMS_SQL. Notar que es requerido usar sus métodos para así realizar una operación X. En los dos siguientes métodos mostraos la implementación de la función antes creada.*/
---CREATE TABLE EMP_COPY AS
SELECT * FROM employees;
BEGIN
DBMS_OUTPUT.PUT_LINE('Rows Deleted: ' ||delete_all_rows('EMP_COPY'));
END;
/
---OUTPUT:
table_name VARCHAR2,
id VARCHAR2,
name VARCHAR2,
region NUMBER
) IS
csr_id INTEGER;
stmt VARCHAR2(200);
rows_added NUMBER;
BEGIN
stmt := 'INSERT INTO '||table_name||
' VALUES (:cid, :cname, :rid)';
csr_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(csr_id, stmt, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(csr_id, ':cid', id);
DBMS_SQL.BIND_VARIABLE(csr_id, ':cname', name);
DBMS_SQL.BIND_VARIABLE(csr_id, ':rid', region);
rows_added := DBMS_SQL.EXECUTE(csr_id);
DBMS_SQL.CLOSE_CURSOR(csr_id);
DBMS_OUTPUT.PUT_LINE(rows_added||' row added');
END;
/
/*En este ejemplo notamos otro uso del paquete DBMS_SQL. Como vemos es requerido un paso adicional para poder asociar una variable Bind a la sentencia dinámica.*/
---OUTPUT:
1. Ejecute DBMS_SQL.DEFINE_COLUMN para cada columna seleccionada.
2. Ejecute DBMS_SQL.BIND_VARIABLE para cada variable de enlace en la consulta.
3. Para cada fila, haga lo siguiente:
• Ejecute DBMS_SQL.FETCH_ROWS para recuperar una fila y devolver el número de filas obtenidas. Detenga el procesamiento adicional cuando se devuelve un valor cero.
• Ejecute DBMS_SQL.COLUMN_VALUE para recuperar cada valor de columna seleccionado en la variable PL/SQL para su procesamiento.
Nota: Aunque el proceso de codificación con DBMS_SQL no es complejo, resulta mas lento y mas propenso a errores en comparación con el uso del enfoque SQL Dinámico Nativo.
______________________________________________________________________________________
Comparación: SQL Dinámico Nativo vs Paquete DBMS_SQL.
El SQL Dinámico Nativo proporciona las siguientes ventajas sobre el paquete DBMS_SQL.• Facilidad de uso: Debido a que el SQL Dinámico Nativo está integrado con SQL, puede utilizarlo de la misma forma en que utiliza SQL Estático dentro del código PL/SQL. El código suele ser más compacto y legible en comparación con el código escrito con el paquete DBMS_SQL.
• Mejora de rendimiento: SQL Dinámico Nativo se desempeña significativamente mejor que DBMS_SQL, en la mayoría de las circunstancias, debido al soporte nativo proporcionado por el intérprete PL/SQL. El enfoque DBMS_SQL utiliza una API de procedimiento y sufre de muchas llamadas a procedimientos y copia de datos de sobrecargas.
• Soporte para tipos definidos por el usuario: SQL Dinámico Nativo soporta todos los tipos soportados por SQL Estático en PL/SQL. Por lo tanto, SQL Dinámico Nativo proporciona soporte para tipos definidos por el usuario, como objetos, colecciones y REFs. El paquete DBMS_SQL no admite tipos definidos por el usuario. Sin embargo, tiene soporte limitado para matrices.
• Soporte para la obtención de registros: con SQL Dinámico Nativo, las filas resultantes de una consulta se pueden obtener directamente en registros PL/SQL. El paquete DBMS_SQL no admite la obtención de estructuras tipo registros.
______________________________________________________________________________________
Fuentes: Oracle Database 10g: Develop PL/SQL Program Units
https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm#LNPLS01108
https://docs.oracle.com/cd/B12037_01/appdev.101/b10807/13_elems017.htm
https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm#LNPLS01108
https://docs.oracle.com/cd/B12037_01/appdev.101/b10807/13_elems017.htm
