Oracle 23c - Mejoras a cláusula RETURNING

En esta versión de Oracle Database se introduce una pequeña pero interesante mejora a la clausula RETURNING que si bien es válida para cualquier sentencia DML (INSERT, UPDATE o DELETE) es realmente útil para UPDATE.

Como funcionaba RETURNING hasta Oracle 23c

La clausula RETURNING permitía obtener los valores involucrados en una sentencia DML, devolviendo el valor insertado en el caso de INSERT (ya que no hay un valor anterior), el valor existente o "anterior" en el caso de DELETE (ya que una vez borrado el registro no hay mas valores disponibles) y en el caso de UPDATE el nuevo valor asignado a la columna.

Ejemplo

Para ver el comportamiento, vamos a usar el código siguiente para crear una tabla de pruebas, con un registro en la misma:

CREATE TABLE PruebaReturning (
  ID NUMBER CONSTRAINT PK_PruebaReturning PRIMARY KEY,
  Valor VARCHAR2(50)
);
--
INSERT INTO PruebaReturning VALUES (1, 'Valor Inicial');
--
COMMIT;
--

Luego de crear y popular la tabla, ejecutaremos el siguiente código que usa RETURNING en un UPDATE para obtener el valor actualizado. Noten que el comportamiento por defecto de la clausula solo requiere identificar la columna para la cual se desea obtener el valor, y donde lo vamos a "almacenar":

DECLARE 
    vValorFinal   VARCHAR2(50) := '-';
BEGIN
    --
    UPDATE PruebaReturning
    SET Valor = REPLACE(Valor,'Inicial','Actualizado')
    WHERE ID = 1
    RETURNING Valor INTO vValorFinal;
    --
    dbms_output.put_line('vValorFinal = ' || vValorFinal);
END;
/
ROLLBACK;

Al ejecutar este código obtenemos el siguiente resultado:

El problema es que no podemos obtener el valor anterior que poseía la columna, lo cual puede ser algo interesante de tener en el caso de UPDATE.

Como funciona RETURNING desde Oracle 23c

Desde Oracle 23c la clausula RETURNING permite especificar la opción OLD o  NEW antes del nombre de la columna, lo cual permite "elegir" uno de ellos, o proporcionar ambos de ser requerido.

Sintaxis

La nueva sintaxis de la cláusula es la siguiente:

RETURNING [OLD | NEW] <Expresion> INTO <Variable>

Nota: Si bien es posible usar esta nueva opción en sentencias INSERT o DELETE, en el caso de estar insertando un nuevo registro el valor anterior (OLD) sera siempre NULL,  y en el caso de estar borrando el valor nuevo (NEW) será también siempre NULL, por lo que la misma no es útil aunque es permitida.

Ejemplo

A continuación, vamos a probar con un bloque PL/SQL similar al anterior, pero esta vez usaremos OLD y NEW obteniendo tanto el valor anterior como el nuevo:

DECLARE 
    vValorInicial VARCHAR2(50) := '-';
    vValorFinal   VARCHAR2(50) := '-';
BEGIN
    --
    UPDATE PruebaReturning
    SET Valor = REPLACE(Valor,'Inicial','Actualizado')
    WHERE ID = 1
    RETURNING OLD Valor, NEW Valor INTO vValorInicial, vValorFinal;
    --
    dbms_output.put_line('vValorInicial = ' || vValorInicial);
    dbms_output.put_line('vValorFinal = ' || vValorFinal);
    --
END;
/

Al consultar la salida de DBMS_OUTPUT podemos ver que pudimos capturar tanto el valor anterior como el nuevo valor al hacer la actualización de la fila:

 

Conclusión

Este cambio en Oracle Database 23c permite una mayor flexibilidad al momento de capturar las versiones anteriores o actuales de los datos que son alcanzados por una sentencia UPDATE.

Si desean conocer más sobre Oracle 23c, es recomendable que vean estos artículos en este blog como punto de partida:

• Oracle Database 23c Free - Developer Edition.
• Instalando Oracle Database 23c (Free Developer Edition) en Oracle Cloud.

Adicionalmente, pueden consultar todos los artículos relacionados a Oracle Database 23c agrupados en en el tag Database 23c.