My Certification Path – Chapter I (Cláusula RETURNING)

Pessoal, estou de volta com mais algumas dicas de estudo para certificação. Hoje irei abordar a 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 em muitas vezes 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 post é mais para despertar a curiosidade e demonstrar partes das possibilidades de utilização da cláusula RETURNING. Espero que tenha ajudado !

Um abraço