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>
