Anuncios en tutorial de programación PLSQL

miércoles, 19 de abril de 2023

Ejecución de cursores PLSQL y sentencias DML utilizando SQL dinámico nativo (Native Dynamic SQL)

Una de las preguntas más frecuentes que me suele hacer la gente es acerca de la posibilidad de definir un procedimiento PL/SQL en el que se declaren múltiples cursores en base al valor de los parámetros de entrada de dicho procedimiento. Las preguntas suelen incluir condiciones muy variadas, pero lo normal es que los implicados sólo necesiten hacer variable la cláusula WHERE y que el resto del cuerpo del cursor PLSQL se mantenga fijo. Cuando esto ocurre yo siempre contesto remitiendo a los que preguntan al artículo que escribí sobre el paquete estándar PL/SQL DBMS_SQL, un paquete que permite crear sentencias SQL dinámicas.

SQL dinámico en Oracle

Si he decidido escribir un artículo nuevo al respecto es por el hecho de que resulta mucho más legible un código que utilice directamente SQL dinámico nativo, es decir, un código en el que no se utiliza el paquete estándar DBMS_SQL. A continuación os dejo un sencillo ejemplo de cómo se podría construir, utilizando SQL dinámico nativo, un procedimiento PLSQL en el que se define un cursor cuya cláusula WHERE varíe en función de un parámetro.

CREATE OR REPLACE PROCEDURE varcursor
(par1 IN NUMBER DEFAULT NULL)
AS
l_query VARCHAR2(500);
TYPE tcursor IS REF CURSOR;
l_cursor tcursor;
l_cursor_rec dba_users%ROWTYPE;
BEGIN
l_query := 'SELECT * FROM dba_users WHERE 1=1';
IF par1 IS NOT NULL THEN
l_query := l_query ||
' AND user_id = ' || par1;
END IF;
OPEN l_cursor FOR l_query;
LOOP
FETCH l_cursor INTO l_cursor_rec;
EXIT WHEN l_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE
(l_cursor_rec.username);
END LOOP;
CLOSE l_cursor;
END;

En nuestro ejemplo, el procedimiento varcursor sacará por pantalla el username con el user_id que se le pase como parámetro, y si el parámetro se pasa con valor NULL, entonces sacara por pantalla todos los username de la tabla dba_users.

Por otro lado, muchas otras preguntas hacen referencia a la utilización de SQL dinámico nativo con sentencias SQL DML. De todas las sentencias DML la que se lleva la palma es las sentencia UPDATE y, en este caso concreto, las preguntas hacen referencia tanto a la posibilidad de utilizar una cláusula WHERE variable, como a la posibilidad de modificar o no una determinada columna en base a si se pasa o no un determinado parámetro.

Antes de poneros un ejemplo concreto, os diré que la implementación de una cláusula WHERE variable se realiza de forma similar a la utilizada en el procedimiento varcursor, sin embargo, para el caso de que una columna se actualice según se pase o no un determinado parámetro, es mejor no utilizar SQL dinámico. Veamos ahora un ejemplo concreto.

CREATE OR REPLACE PROCEDURE varupdate
(vsal IN NUMBER DEFAULT NULL,
veid IN NUMBER DEFAULT NULL)
AS
l_query VARCHAR2(500);
BEGIN
l_query := 'UPDATE empleados
SET fecha_actualizacion = SYSDATE,
salario = NVL(:vsal, salario)
WHERE 1=1';
IF par2 IS NOT NULL THEN
l_query := l_query ||
' AND empleado_id = :veid';
END IF;
EXECUTE IMMEDIATE l_query USING vsal, veid;
END;

En el ejemplo podéis ver que el primer parámetro es el que determina si la columna salario se actualiza o no, sin embargo, dicha columna la hemos incluido en el bloque estático de la sentencia SQL UPDATE utilizando la función NVL(), de manera que si el parámetro vsal toma el valor NULL, entonces actualizaremos la columna salario con el mismo valor que tenía anteriormente. Esto es mucho más sencillo que utilizar un IF y cambiar dinámicamente la sentencia UPDATE en base a si el parámetro vsal toma el valor NULL o no. No obstante, alguien podría pensar que utilizar el IF puede ahorrar tiempo de procesamiento ya que se evitaría el tener que actualizar una columna innecesariamente, sin embargo, puesto que los índices no tienen que reconstruirse cuando el valor de una columna no cambia realmente, el aumento en tiempo de procesamiento es mínimo y lo más conveniente es utilizar SQL estático y la función NVL().

En cambio, para el parámetro que se utiliza en la cláusula WHERE de nuestro UPDATE, hemos utilizado la misma técnica que en el procedimiento varcursor, es decir, hemos empleado un IF para así utilizar o no la condición asociada con dicho parámetro.

En conclusión, nuestro procedimiento actualizará la columna salario del empleado con id veid al valor vsal, pero si vsal es NULL, entonces el valor de la columna salario continuará siendo el mismo y sólo se habrá actualizado el valor de la columna fecha_actualizacion. Y si veid toma el valor de NULL, entonces el valor de la columna fecha_actualizacion se actualizará para todos los empleados, ocurriendo lo mismo para la columna salario que tomará el valor vsal, siempre que vsal no sea NULL, en cuyo caso dicha columna no cambiaría.

15 comentarios:

Juan dijo...

Hola,
hace un tiempo que quiero hacerte esta pregunta pero no encuentro como andarte un MSN directamente

La cosa es asi. dentro de un Paquete tengo un Store el que tiene dos paramentros. El de entrada es usado en el Query con la "funcion IN".
Pero no funciona, se te acurre alguna solicion.

Aca abajo dejo un ejemplo (es solo el ejemplo)
PROCEDURE calculo_empleados(p_id_empleados IN VARCHAR2
,p_cantida OUT NUMBER)
AS
BEGIN
--
SELECT COUNT(1)
INTO p_cantida
FROM empleados
WHERE emple_id IN (p_id_empleados);
--
END calculo_empleados;

JLPM dijo...

Hola Juan, yo no veo ningún problema en el procedimiento que has definido. Es totalmente correcto. ¿Por qué dices que no funciona?

José Gabriel González dijo...

Hola Pepelu! Muy buenos tus post. De verdad muchas felicitaciones. Tengo una pregunta. Este codigo tiene un punto de ruptura y no lo veo.

CREATE OR REPLACE FUNCTION GET_EMPLOYEES_BY_SALARY_VAR (SALARY_LESS IN NUMBER) RETURN NUMBER AS

type T_PO_CURSOR is ref cursor;
PO_CURSOR T_PO_CURSOR;
V_SQL_QUERY varchar2(500);

REGISTRO EMPLOYEES%ROWTYPE;
ERROR_MESSAGE VARCHAR(50);
SIN_REGISTROS EXCEPTION;

begin
v_sql_query := 'SELECT * FROM EMPLOYEES A WHERE 1=1';

if (SALARY_LESS is not null) then
v_sql_query := v_sql_query || ' AND SALARY <= ' || SALARY_LESS || ';';
else
v_sql_query := v_sql_query || ' AND SALARY <= 0;';
END IF;

OPEN PO_CURSOR FOR v_sql_query;

FETCH PO_CURSOR INTO REGISTRO;

IF PO_CURSOR%NOTFOUND THEN
RAISE SIN_REGISTROS;
END IF;

WHILE PO_CURSOR%FOUND LOOP
FETCH PO_CURSOR into REGISTRO;
DBMS_OUTPUT.PUT_LINE(REGISTRO.EMPLOYEE_ID || '.- ' || REGISTRO.FIRST_NAME || ' ' || REGISTRO.LAST_NAME);
END LOOP;

CLOSE PO_CURSOR;

RETURN 1;

EXCEPTION

WHEN SIN_REGISTROS THEN
DBMS_OUTPUT.PUT_LINE('No existen empleados que estén cobrando actualmente un sueldo menor a este');
RETURN 0;

WHEN OTHERS THEN
ERROR_MESSAGE := SQLERRM;
RAISE_APPLICATION_ERROR(SQLCODE,TO_CHAR(ERROR_MESSAGE));

END GET_EMPLOYEES_BY_SALARY_VAR;

JLPM dijo...

Hola José Gabriel,

El problema es que no debes poner punto y coma (;) al final de la query SQL, si lo haces vas a recibir el error "ORA-03113 - end-of-file on communication channel". Es decir, no debes añadir al final de la variable v_sql_query un punto y coma.

También puede generar problemas el hecho de que utilices TO_CHAR(ERROR_MESSAGE) ya que ERROR_MESSAGE ya es un VARCHAR.

Espero haber resuelto tus dudas.

José Gabriel González dijo...

Muchas Gracias Amigo! Si esos "punto y coma"(;) eran los que estaban fastidiando. Ok, ya le quite el TO_CHAR al ERROR_MESSAGE y anda bien. Gracias de nuevo. Seguiré por acá preguntandote cosas de Oracle PL/SQL, hasta luego. Full éxito.

Jacqueline Gil Tapia-Ruano dijo...

Hola, soy nueva por aca pero leyendo queria comentarle a Juan que su codigo no funciona porque al parecer esta intentando pasar en la variable p_empleados una cadena con valores separados por comas para que funcione la condicion IN y esto asi no funciona, debe construir la query dinamicamente concatenandole la cadena:
open p_cursor for 'SELECT COUNT(1)FROM empleados
WHERE emple_id IN ('||p_id_empleados||')';
fetch p_cursor in p_cantidad;
close p_cursor;
Espero haber sido de ayuda,

saludos
JG

JLPM dijo...

Una buena observación JG. Ciertamente si Juan estaba intentando hacer lo que mencionas, sun función PL/SQL no funcionaría y una solución es la que tu propones.

Cristobal dijo...

Hola Pepelu, estoy intentando usar sql dinamico para crear un bloque anonimo y ejecutarlo dinámicamente. El problema que tengo es que no se si esto realmente funciona. Uso un bloque anonimo, en lugar de un procedimiento almacenado porque no quiero crear la paqueteria en la BBDD. Además necesito crear un tipo de dato rowtype pasando el nombre de la tabla como parámetro.
Quizás puedas orientarme en como hacerlo ya que soy nuevo en el uso de pl/sql dinámico. Gracias de antemano, explendido blog.

carlino dijo...

Hola José Luis , estoy realizando esta manipulacion de un campo, de una tabla en Oracle 11g:

{code}
SELECT (substr ('PUN_26_AL_REA P',1, INSTR( 'PUN_26_AL_REA P',' ')-1)) AS PRUEBA FROM DUAL;
{code}

Es para igualar el contenido de una columna, con la de otra columna de otra tabla.

Ahora necesito aplicar las mismas funciones, a un rango de campos de una tabla.

Supongo que es algo asi:

{code}
declare cursor c_cur is select pointname from desde_ap; v_cur c_cur%ROWTYPE; --v_convert INTEGER; BEGIN v_sql:= '' OPEN C_CUR; loop FETCH C_CUR INTO V_CUR; EXIT WHEN c_cur%NOTFOUND; select (substr ('|| v_cur ||',1, INSTR( '|| v_cur ||',' ')-1)) FROM dual; end loop; DBMS_OUTPUT.PUT_LINE(v_cur.pointname); close c_cur; end;
{code}

pero no me funciona. Puedo usar sql_dinamico?

Podras ayudarme por favor?

Gracias!

JLPM dijo...

Hola Juan,

Hay varios errores en tu código PLSQL...

No estoy muy seguro de lo que quieres hacer pero tu código corregido quedaría:

DECLARE

CURSOR c_cur IS
SELECT pointname FROM desde_ap;

v_cur c_cur%ROWTYPE;
v_salida desde_ap.pointname%TYPE;

BEGIN

OPEN C_CUR;
LOOP
FETCH C_CUR INTO V_CUR;
EXIT WHEN c_cur%NOTFOUND;

v_salida := SUBSTR(v_cur.pointname,1,INSTR(v_cur.pointname,' ')-1));
DBMS_OUTPUT.PUT_LINE(v_salida);
END LOOP;

CLOSE c_cur;
END;

carlino dijo...

Gracias Juan, lo probé pero no me devuelve valores (al igual que mi código erróneo).
Funciona el substr sobre una variable?

Eso si funciona:
SELECT substr(pointname, 1,15) FROM desde_ap;

Saludos

Unknown dijo...

Buenas tardes, quisiera saber si alguien sabe como se maneja los casos en los que el query se genera dinamicamente y no se sabe si en el select se consultan 2 o 3 o N columnas, para establecer las variables en el fetch into como se soluciona ese problema?.

Gracias.

JLPM dijo...

Hola Alexander,

Creo que este artículo te puede ser de ayuda:

http://www.orafaq.com/forum/t/164791/0/

Usuario dijo...

Hola,
tengo el siguiente codigo y recibo el mismo error :

ORA-01006: bind variable does not exist

v_query := 'SELECT id_contribuent FROM CO_CONTRIBUENTS WHERE ROWNUM <=50';
IF(p_id_contribuent is not null) THEN
v_where := ' AND ID_CONTRIBUENT like '||p_id_contribuent;
END IF;
IF(p_num_document is not null) THEN
v_where := v_where||' AND NUM_DOCUMENT like ' ||p_num_document;
END IF;
IF(p_nom is not null) THEN
v_where := v_where|| ' AND NOM_CONTRIBUENT like ''%'||p_nom||'%''';
END IF;
IF(p_cognom1 is not null) THEN
v_where := v_where|| ' AND COGNOM1 like '||p_cognom1;
END IF;
IF(p_cognom2 is not null) THEN
v_where := v_where|| ' AND COGNOM2 like '||p_cognom2;
END IF;
IF(p_nom_empresa is not null) THEN
v_where := v_where||' AND COGNOM1 like '||p_nom_empresa;
END IF;

v_query := v_query|| v_where;

OPEN c_dades FOR v_query USING p_id_contribuent, p_num_document,p_nom,p_cognom1,p_cognom2,p_nom_empresa;
DBMS_OUTPUT.PUT_LINE('ID_CONTRIBUENT');
LOOP
FETCH c_dades INTO v_id_contribuent;
EXIT WHEN c_dades%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_id_contribuent);
END LOOP;
CLOSE c_dades;

Usuario dijo...

Tengo tambien problemas con el resto de atributos por parametro (he probado p_nom) y me da el mismo error.

¿Alguien podria ayudarme? Gracias