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últiplescursores 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 elpaquete estándar PL/SQL DBMS_SQL, un paquete que permite crear sentencias SQL dinámicas.

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 eluser_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 procedimientovarcursor, 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 columnasalario 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 salariocontinuará siendo el mismo y sólo se habrá actualizado el valor de la columnafecha_actualizacion. Y si veid toma el valor de NULL, entonces el valor de la columnafecha_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.