SQL y PL/SQL - La nueva sentencia MERGE

La sentencia MERGE, a la que muchos denominan UPSERT debido a su funcionalidad, está disponible desde la aparición de la versión 9i de la base de datos Oracle. Se trata de una de las funcionalidades del kernel de Oracle más utiles a la hora de permitir el uso de la tecnología ETL (Extract, Transform and Load - Extraer, Transformar y Cargar) con las bases de datos Oracle. Este tipo de tecnología puede utilizarse y está especialmente enfocada para ser usada en aplicaciones de data warehousing (almacen de datos). Básicamente, lo que permite la sentencia SQL MERGE es, dependiendo de una condición lógica, actualizar registros (UPDATE) cuando la condición se cumple, o insertar registros (INSERT) cuando dicha condición no se cumple, de ahí surge la denominación de UPSERT.

Anteriormente a la versión 9i de Oracle, la alternativa en SQL era ejecutar dos sentencias DML, un UPDATE y un INSERT, cada una utilizando condiciones lógicas opuestas. En cuanto a las alternativas en PL/SQL eran: bien intentar insertar un registro y si la sentencia INSERT fallaba debido a una excepción PLSQL del tipo DUP_VAL_ON_INDEX, entonces realizar un UPDATE del registro en cuestión; bien intentar actualizar un registro y si la sentencia UPDATE devolvía SQL%NOTFOUND, entonces ejecutar la correspondiente sentencia INSERT.

Las ventajas de la sentencia SQL MERGE son claras:

- Permite con una misma sentencia realizar un UPDATE si el registro existe, o un INSERT si se trata de un nuevo registro.

- La sentencia puede paralelizarse de forma transparente.

- Se evita la necesidad de realizar actualizaciones múltiples.

- Es especialmente útil para realizar operaciones en masa y, como ya he mencionado, en aplicaciones de data warehousing.

- El rendimiento de la base de datos mejora ya que, al necesitarse menos sentencias SQL para realizar las mismas operaciones, también se necesitan menos accesos a las tablas fuente.

Ejemplo de sentencia MERGE

En el presente ejemplo voy a partir del hecho de que tenemos dos tablas en un data warehouse, una de ellas con muchos registros denominada clientes y otra más pequeña denominada datos_cli cuyos registros deben insertarse en la tabla clientes. En pocas palabras, estoy suponiendo que la tabla datos_cli contiene los cambios a realizar sobre la tabla clientes. La sentencia SQL MERGE que debemos utilizar para actualizar la tabla cliente podría ser la siguiente:

MERGE INTO clientes cli USING datos_cli dac
  ON (cli.cliente_id = dac.cliente_id)
  WHEN MATCHED THEN
    UPDATE SET 
      cli.nombre = dac.nombre,
      cli.direccion = dac.direccion
  WHEN NOT MATCHED THEN
    INSERT (cliente_id, nombre, direccion)
    VALUES (dac.cliente_id, dac.nombre, dac.direccion);

Así pues, la sentencia MERGE del ejemplo realizará las siguientes operaciones:

- Si existe un registro en datos_cli con el mismo ID de otro registro en la tabla clientes, entoces actualizará el valor de los campos nombre y dirección de la tabla clientes con los valores correspondientes de la tabla datos_cli.

- Si no existe un registro en clientes con el ID del registro de datos_cli que está siendo procesado, entonces se insertará dicho registro en la tabla clientes.

En resumen, la sintaxis de la sentencia MERGE debe incorporar:

Una cláusula INTO, que especifica la tabla destino donde los registros serán actualizados o insertados.

Una cláusula USING, especificando el origen de los datos que van a ser insertados o que van a servir para actualizar la tabla destino. El origen de los datos puede tratarse de una tabla, una vista, o del resultado de la ejecución de una consulta SELECT.

Una cláusula ON, que especifica la condición bajo la cual se realizará, bien la operación UPDATE (si la condición se cumple), bien la operación INSERT (si la condición no se cumple).

Las cláusulas WHEN MATCHED | NOT MATCHED, que son las que indican a la base de datos Oracle que acción realizar si se cumple o no se cumple la condición del ON. Estas dos cláusulas se pueden poner en cualquier orden.

Artículos relacionados:
La sentencia INSERT multitabla de la versión 9i de Oracle.
La cláusula WITH de la versión 9i de la base de datos Oracle.

SQL y PL/SQL - La sentencia INSERT multitabla de Oracle 9i

La versión 9i de las bases de datos Oracle ha introducido la posibilidad de utilizar sentencias INSERT multitabla. Así pues, la sentencia SQL o PLSQL INSERT... SELECT ha cambiado ligeramente su sintaxis, de manera que ahora permite la inserción de datos en más de una tabla de la base de datos de forma paralela. Existen dos formas de utilizar el comando INSERT multitabla: no condicional y condicional. En la forma no condicional, una cláusula compuesta INTO se ejecuta cada vez que la consulta SELECT devuelve un registro. En la forma condicional, las cláusulas compuestas INTO figuran dentro de cláusulas WHEN a partir de las que se determina si la correspondiente cláusula compuesta INTO se ejecuta o no.

Una claúsula compuesta INTO consiste de una o más cláusulas INTO. Una cláusula INTO debe especificar la tabla de la base de datos sobre la que se van a insertar los datos. Esta cláusula no admite alias. La cláusula INTO tambien proporciona el valor del los campos a ser insertados mediante la cláusula VALUES. La expresiones usadas en la cláusula VALUE pueden tratarse de cualquier expresión permitida, pero siempre debe hacer referencia a columnas devueltas por la consulta SELECT incluida en el INSERT.

Veamos un ejemplo de la forma no condicional:

INSERT ALL
 INTO productos
 VALUES (producto_id, producto, cantidad)
 INTO ventas
 VALUES (cliente_id, producto_id, cantidad, total)
SELECT cliente_id,
      producto_id,
      producto,
      SUM(cantidad) cantidad,
      SUM(precio) total
FROM   pedidos, lineas_pedidos
WHERE  pedidos.pedido_id = lineas_pedidos.pedido_id
GROUP BY cliente_id, producto_id, producto;

En el ejemplo vemos como la sentencia INSERT se utiliza para insertar los valores producto_id, producto y cantidad en la tabla productos y los valores cliente_id, producto_id, cantidad y total en la tabla ventas, todo ello utilizando una sola sentencia SQL o PL/SQL. Resulta obvio comentar que la sentencia que he utilizado como ejemplo, podría escribirse de forma alternativa mediante el empleo de dos sentencias INSERT.

La forma condicional de las sentencias SQL o PLSQL INSERT multitabla dispone, a su vez, de dos opciones:

1) Forma condicional FIRST, sólo se insertan los valores especificados en la primera condicion verdadera.

2) Forma condicional ALL, se insertan los valores especificados en todas las condiciones verdaderas.

La sintaxis sería:

INSERT [ALL/FIRST]
WHEN  THEN
 INTO  (col, col, ...) VALUES (val, val, ...)
 .....
 INTO  (col, col, ...) VALUES (val, val, ...)
WHEN  THEN
 INTO  (col, col, ...) VALUES (val, val, ...)
 .....
 INTO  (col, col, ...) VALUES (val, val, ...)
ELSE
 INTO  (col, col, ...) VALUES (val, val, ...)
 .....
 INTO  (col, col, ...) VALUES (val, val, ...)
SELECT ...;

Ventajas de las sentencias INSERT multitabla en SQL y PL/SQL

1) Eliminan la necesidad de utilizar múltiples sentencias INSERT ... SELECT para añadir registros en varias tablas de la base de datos.

2) Elimina la necesidad de utilizar un procedimiento PL/SQL para realizar diferentes inserciones de registros dependiendo de diferentes condiciones lógicas. Esto ya se puede realizar con una única sentencia SQL del tipo INSERT ... SELECT, mediante el uso de la cláusula WHEN.

3) Mejora el rendimiento significativamente ya que la consulta SELECT correspondiente, al existir un sólo comando INSERT, sólo tienen que ejecutarse una vez, en lugar de tener que repetirse su ejecución en cada INSERT.

Artículos relacionados: La nueva cláusula WITH de la versión 9i de la base de datos Oracle.