Cláusula DML RETURNING INTO

La cláusula RETURNING INTO nos permite devolver valores de columna para filas afectadas por declaraciones DML.

  • Preparar
  • INSERT, UPDATE y DELETE Basico
  • DML que afecta a varias filas: retorno a colecciones
  • DML que afecta a varias filas: retorno con agregaciones

Preparar

La siguiente tabla de prueba se utiliza para demostrar la cláusula RETURNING INTO.

DROP TABLE tab1;
DROP SEQUENCE tab1_seq;

CREATE TABLE tab1 (
  id NUMBER(10),
  description VARCHAR2(50),
  CONSTRAINT tab1_pk PRIMARY KEY (id)
);

CREATE SEQUENCE t1_seq;

INSERT INTO t1 VALUES (tab1_seq.nextval, 'ONE');
INSERT INTO t1 VALUES (tab1_seq.nextval, 'TWO');
INSERT INTO t1 VALUES (tab1_seq.nextval, 'THREE');
COMMIT;

INSERT, UPDATE y DELETE Basico

La cláusula RETURNING INTO nos permite devolver valores de columna para filas afectadas por declaraciones DML. Los datos devueltos pueden ser una sola columna, varias columnas o expresiones.
Cuando insertamos datos usando una secuencia para generar nuestro valor de clave principal, podemos devolver el valor de clave principal de la siguiente manera.

SET SERVEROUTPUT ON
DECLARE
  l_id t1.id%TYPE;
BEGIN
  INSERT INTO t1 VALUES (t1_seq.nextval, 'FOUR')
  RETURNING id INTO l_id;
  COMMIT;
  DBMS_OUTPUT.put_line('ID=' || l_id);
END;
/
ID=4

PL/SQL procedure successfully completed.

SQL>

La sintaxis también está disponible para las declaraciones de actualización y eliminación.

SET SERVEROUTPUT ON
DECLARE
  l_id t1.id%TYPE;
BEGIN
  UPDATE t1
  SET    description = description
  WHERE  description = 'FOUR'
  RETURNING id INTO l_id;
  DBMS_OUTPUT.put_line('UPDATE ID=' || l_id);
  DELETE FROM t1
  WHERE  description = 'FOUR'
  RETURNING id INTO l_id;
  DBMS_OUTPUT.put_line('DELETE ID=' || l_id);
  COMMIT;
END;
/
UPDATE ID=4
DELETE ID=4

PL/SQL procedure successfully completed.

SQL>

DML que afecta a varias filas: retorno a colecciones

Cuando DML afecta a varias filas, todavía podemos usar la cláusula RETURNING INTO, pero si queremos que se devuelvan los valores de todas las filas tocadas, debemos devolver los valores a una colección utilizando la cláusula BULK COLLECT.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF t1.id%TYPE;
  l_tab t_tab;
BEGIN
  UPDATE t1
  SET    description = description
  RETURNING id BULK COLLECT INTO l_tab;
  FOR i IN l_tab.first .. l_tab.last LOOP
    DBMS_OUTPUT.put_line('UPDATE ID=' || l_tab(i));
  END LOOP;
  COMMIT;
END;
/
UPDATE ID=1
UPDATE ID=2
UPDATE ID=3

PL/SQL procedure successfully completed.

SQL>

También podemos usar la cláusula RETURNING INTO en combinación con enlaces masivos.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_desc_tab IS TABLE OF t1.description%TYPE;
  TYPE t_tab IS TABLE OF t1%ROWTYPE;
  l_desc_tab t_desc_tab := t_desc_tab('FIVE', 'SIX', 'SEVEN');
  l_tab   t_tab;
BEGIN
  FORALL i IN l_desc_tab.first .. l_desc_tab.last
    INSERT INTO t1 VALUES (t1_seq.nextval, l_desc_tab(i))
    RETURNING id, description BULK COLLECT INTO l_tab;
  FOR i IN l_tab.first .. l_tab.last LOOP
    DBMS_OUTPUT.put_line('INSERT ID=' || l_tab(i).id || 
                         ' DESC=' || l_tab(i).description);
  END LOOP;
  COMMIT;
END;
/
INSERT ID=5 DESC=FIVE
INSERT ID=6 DESC=SIX
INSERT ID=7 DESC=SEVEN

PL/SQL procedure successfully completed.

SQL>

Esta funcionalidad también está disponible en SQL dinámico.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF t1.id%TYPE;
  l_tab t_tab;
BEGIN
  EXECUTE IMMEDIATE 'UPDATE t1
                     SET    description = description
                     RETURNING id INTO :l_tab'
  RETURNING BULK COLLECT INTO l_tab;
  FOR i IN l_tab.first .. l_tab.last LOOP
    DBMS_OUTPUT.put_line('UPDATE ID=' || l_tab(i));
  END LOOP;
  COMMIT;
END;
/
UPDATE ID=1
UPDATE ID=2
UPDATE ID=3

PL/SQL procedure successfully completed.

SQL>

DML que afecta a varias filas: retorno con agregaciones

No estamos obligados a usar colecciones cuando usamos la cláusula RETURNING INTO con DML que afecta a múltiples filas. Si la salida se agrega, se puede colocar en una variable regular.

SET SERVEROUTPUT ON
DECLARE
  l_max_id NUMBER;
BEGIN
  UPDATE t1
  SET    description = description
  RETURNING MAX(id) INTO l_max_id;
  DBMS_OUTPUT.put_line('l_max_id=' || l_max_id);
  COMMIT;
END;
/
l_max_id=3

PL/SQL procedure successfully completed.

SQL>

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *