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>