Cláusula RETURNING: Aprenda como recuperar resultados de operações DML sem executar mais instruções SQL

Cláusula RETURNING

Olá pessoal ! Hoje irei abordar o uso da cláusula RETURNING.

Em todo o meu tempo como desenvolvedor, vi pouquíssimas implementações dessas cláusula em operações DML, o que resulta muitas vezes em mais e mais SQLs feitos para exibir resultados dessas operações.

Se você não conhece essa cláusula, deve estar se perguntando: Afinal, o que é a cláusula RETURNING ?

Cláusula RETURNING

Ela retorna o resultado com os dados afetados por operações DML (se não sabe o que é DML, clique aqui). Resumindo, se você faz um UPDATE que afeta 3 linhas de sua tabela, você conseguirá recuperar o resultado dessa operação sem executar nenhuma instrução SQL a mais.

Para demonstrar a utilização do RETURNING, execute as instruções abaixo:

CREATE TABLE pessoa 
( 
 codigo NUMBER,
 nome   VARCHAR2(200),
 status CHAR(1)
)
/

INSERT INTO PESSOA VALUES (1,'SERGIO'  ,'A')
/
INSERT INTO PESSOA VALUES (2,'WILLIANS','A')
/
INSERT INTO PESSOA VALUES (3,'RICARDO' ,'A')
/
INSERT INTO PESSOA VALUES (4,'SANTOS'  ,'A')
/

COMMIT
/

Agora vamos demonstrar a sua utilização com um pequeno UPDATE.

DECLARE
   v_nome pessoa.nome%TYPE;  

BEGIN
   UPDATE pessoa a
   SET    a.status = 'I'
   WHERE  a.codigo = 1
   RETURNING a.nome INTO v_nome;  

   DBMS_OUTPUT.PUT_LINE('Nome: ' || v_nome);

   ROLLBACK;

EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Erro na atualização do STATUS - ' || TO_CHAR(SQLCODE) || SQLERRM);   

END;

RESULTADO:

Nome: SERGIO

Veja que o registro com o nome SERGIO, que foi o único afetado pelo UPDATE,  foi exibido.Perceba também que se mais de um registro fosse afetado, o seguinte exception ocorreria:

ORA-01422: exact fetch returns more than requested number of rows

Mas aí ficam as perguntas: Se eu precisar recuperar mais campos, preciso criar uma variável para cada campo ? E se mais de um registro for alterado, como faço para recuperar todos ?

Para isso precisaremos fazer uma implementação um pouco mais sofisticada usado TYPE e BULK COLLECTION.

DECLARE
   TYPE r_pessoa IS RECORD (codigo pessoa.codigo%TYPE, 
                            nome   pessoa.nome%TYPE,
                            status pessoa.status%TYPE);                            

   TYPE t_pessoa IS TABLE OF r_pessoa;                                                         

   d_pessoa t_pessoa;          

BEGIN
   UPDATE pessoa a
   SET    a.status = 'I'
   WHERE  a.codigo IN(1,2)
   RETURNING a.codigo,a.nome,a.status BULK COLLECT INTO d_pessoa;

   FOR i IN d_pessoa.FIRST..d_pessoa.LAST LOOP
      DBMS_OUTPUT.PUT_LINE('UPDATE: Código: ' || d_pessoa(i).codigo  || ' - Nome: ' || d_pessoa(i).nome || ' - Status Atual: ' || d_pessoa(i).status);

   END LOOP;

   ROLLBACK;

EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Erro na atualização do STATUS - ' || TO_CHAR(SQLCODE) || SQLERRM);   

END;

Ao executar teremos o resultado:

UPDATE: Código: 1 - Nome: SERGIO - Status Atual: I
UPDATE: Código: 2 - Nome: WILLIANS - Status Atual: I

A claúsula RETURNING não se limita apenas a  UPDATE, mas também pode ser utilizada com DELETE e INSERT. Como na rotina abaixo:

DECLARE
   TYPE r_pessoa IS RECORD (codigo pessoa.codigo%TYPE, 
                            nome   pessoa.nome%TYPE,
                            status pessoa.status%TYPE);                          

   TYPE t_pessoa IS TABLE OF r_pessoa;                    

   d_pessoa t_pessoa;          

BEGIN
   UPDATE pessoa a
   SET    a.status = 'I'
   WHERE  a.codigo IN(1,2)
   RETURNING a.codigo,a.nome,a.status BULK COLLECT INTO d_pessoa;

   FOR i IN d_pessoa.FIRST..d_pessoa.LAST LOOP
      DBMS_OUTPUT.PUT_LINE('UPDATE: Código: ' || d_pessoa(i).codigo  || ' - Nome: ' || d_pessoa(i).nome || ' - Status Atual: ' || d_pessoa(i).status);

   END LOOP;

   d_pessoa := NULL;

   DELETE FROM pessoa a
   WHERE  a.codigo IN(3,4)
   RETURNING a.codigo,a.nome,a.status BULK COLLECT INTO d_pessoa;

   FOR i IN d_pessoa.FIRST..d_pessoa.LAST LOOP
      DBMS_OUTPUT.PUT_LINE('DELETE: Código: ' || d_pessoa(i).codigo  || ' - Nome: ' || d_pessoa(i).nome || ' - Status Atual: ' || d_pessoa(i).status);

   END LOOP;

   d_pessoa := NULL;

   INSERT INTO pessoa a VALUES(5,'GPO','A')
   RETURNING a.codigo,a.nome,a.status BULK COLLECT INTO d_pessoa;

   FOR i IN d_pessoa.FIRST..d_pessoa.LAST LOOP
      DBMS_OUTPUT.PUT_LINE('INSERT: Código: ' || d_pessoa(i).codigo  || ' - Nome: ' || d_pessoa(i).nome || ' - Status Atual: ' || d_pessoa(i).status);

   END LOOP;

   ROLLBACK;

EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Erro na atualização do STATUS - ' || TO_CHAR(SQLCODE) || SQLERRM);   

END;

O resultado será:

UPDATE: Código: 1 - Nome: SERGIO - Status Atual: I
UPDATE: Código: 2 - Nome: WILLIANS - Status Atual: I
DELETE: Código: 3 - Nome: RICARDO - Status Atual: A
DELETE: Código: 4 - Nome: SANTOS - Status Atual: A
INSERT: Código: 5 - Nome: GPO - Status Atual: A

Esse artigo foi feito para despertar a curiosidade e demonstrar partes das possibilidades de utilização da cláusula RETURNING. Espero que tenha ajudado !

Um abraço