Anuncios en tutorial de programación PLSQL

miércoles, 7 de octubre de 2015

Paquete DBMS_SQL para utilizar SQL dinámico (Dynamic SQL)

Oracle SQL dinámico o dynamic SQL con el paquete estándar DBMS_SQLEl grupo de paquetes DBMS se trata de un conjunto de funciones y procedimientos que el PL/SQL de Oracle incorpora de forma estándar. Estos paquetes DBMS pueden ser de mucha utilidad cuando estamos programando en PLSQL. Hoy voy a hablar sobre el paquete DBMS_SQL que permite utilizar SQL dinámico en procedimientos almacenados y bloques PL/SQL.

Las sentencias de SQL dinámico tienen la característica de que no forman parte del código fuente PL/SQL, sino que están almacenadas dentro de cadenas de caracteres que, bien forman parte de los parámetros de entrada, o bien son construidas durante la ejecución del programa PLSQL. Esto posibilita la creación de programas de propósito mucho más general. Por ejemplo, se pueden crear procedimientos que operen sobre una tabla cuyo nombre no se conoce hasta el momento de la ejecución, ya que se trata de un parámetro de entrada de dicho procedimiento.

Además, con el paquete DBMS_SQL se pueden ejecutar cualquier tipo de sentencias, tanto DML (Data Manipulation Language - Lenguaje de manipulación de datos), como DDL (Data Definition Language - Lenguaje de definición de datos). Por lo tanto, también permite directamente desde PL/SQL crear, modificar o borrar: tablas, índices, o cualquier objeto DDL (incluso procedimientos).

En el siguiente ejemplo os muestro un procedimiento almacenado en el que, dependiendo de los parámetros de entrada, se actualiza, bien el sueldo (campo salario), bien el departamento (campo dpto), de un determinado empleado.

CREATE OR REPLACE PROCEDURE upd_tabla_empleados
(   p_campo  IN VARCHAR2
, p_emp_id IN VARCHAR2
, p_valor  IN VARCHAR2 ) IS

l_cursor        INTEGER;
-- Sentencia a ejecutar
l_sql           VARCHAR2(500);
-- Número de rows actualizadas
l_updated_rows  INTEGER;
BEGIN

l_sql := 
'UPDATE empleados SET ' || p_campo || ' = ' ||
p_valor || ' WHERE empleado_id = ' || p_emp_id;

l_cursor := dbms_sql.open_cursor;
dbms_sql.parse (l_cursor, l_sql, dbms_sql.native);
l_update_rows := dbms_sql.execute (l_cursor);
dbms_sql.close_cursor (l_cursor);

END;

Por lo tanto si ejecutamos el comando:

SQL> EXEC upd_tabla_empleados ('dpto', '1234', 'Compras');

Estaremos asignando el empleado con ID 1234 al departamento de compras.

Y si ejecutamos:

SQL> EXEC upd_tabla_empleados ('salario', '1122', '3000');

Estaremos asignando al empleado con ID 1122 un salario de 3.000 euros.

7 comentarios:

Jose María dijo...

Hola.

Interesante tu blog.

Respecto a este post... ¿Existe mucha diferencia entre eso y hacer "OPEN CURSOR FOR CADENA" ?

Siendo CURSOR un REF CURSOR.

Un saludo.

Pepelu dijo...

Hola Jose María,

La diferencia está en que con el paquete DBMS_SQL puedes ejecutar cualquier tipo de sentencia SQL (UPDATE, INSERT, SELECT, DELETE, etcétera). La forma que tu comentas es sólo válida para consultas (SELECT) en cursores.

InitialD dijo...

Hola que tal pero si utilizamos EXECUTE IMMEDIATE que diferencia hay con DBMS_SQL ya que los dos soportan sentencias DDL, DML y el REF CURSOR lo podemos utilizar para devolver resultados.

DECLARE
str_sql VARCHAR2(255);
l_cnt VARCHAR2(20);
BEGIN
str_sql := 'SELECT count(*) FROM PAISES';
EXECUTE IMMEDIATE str_sql INTO l_cnt;
dbms_output.put_line(l_cnt);
END;

Pepelu dijo...

Hola InitialD, ciertamente no hay una diferencia notable, yo diría que es cuestión de gustos. Tambien he hablado de lo que tu comentas en este artículo: Ejecución de cursores PLSQL y sentencias DML utilizando SQL dinámico nativo.

Luchex dijo...

Quiero hacer un proceso generico donde le mande por asi decir 2 parametros, el primero una cadena que contenga un bloque anonimo del siguiente tipo

begin
proceso_x(:1,:2,....,:n);
end;

, el segundo una lista de parametros que pueda trabajarla para separarla en tiempo de ejecucion, digamos que se me ocurre algo asi:
parametros de tipo varchar2 =
'param1|param2|....|paramn'

o tal vez asi

'1,param1|2,param2|....|n,paramn'

y que mi procedimiento ejecute el que le mando, lo de separar los parametros en nun arreglo o algo asi no se me hace dificil , pero el tema de asignar los parametros sin sabel la cantidad que puede haber de antemano con un using para el caso de sql dinamico nativo no lo pude resolver asi que me parece que tal vez se pueda con dbms_sql, vi un ejemplo el oracle 11 que mas o menos hace esto pero con DBMS_SQL.TO_REFCURSOR, sin embargo yo estoy con el oracle 9i y no tiene esta posibilidad, crees que me puedas dar una mano con eso?

Pepelu dijo...

Hola Luchex,

Entiendo que lo que quieres hacer es algo así:

DECLARE
plsql_code VARCHAR2(500);
parametro VARCHAR2(500);
par1 VARCHAR2(20);
...
parn VARCHAR2(20);
BEGIN
plsql_code := 'BEGIN proceso_x(:1, ..., :n); END;';

-- Extracción de parámetros desde la variable parametros
par1 := ...
...
parn := ...

EXECUTE IMMEDIATE plsql_code
USING IN OUT par1, ..., parn;
END;
/

Obviamente al procedimiento PL/SQL proceso_x siempre le vas a tener que pasar N parámetros, si inicialmente no sabes el número de parámetros tendrás que diseñar proceso_x para que funcione de una forma u otra dependiendo de cuantos parámetros tienen un valor y cuantos se pasan con un valor NULL.

Davinia Rodríguez Jiménez dijo...

Hola. Quiero desarrollar un procedimiento en el que primero se lean los registros de la tabla EMP, y se inserten esos mismos registros en una tabla con similar estructura creada previamente. La inserción deberá efectuarse de 5 en 5, con un array.

El problema es que no me quedan claros los conceptos del paquete DBMS_SQL. Si pudieran ayudarme o orientarme se los agradecería