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: SERGIOVeja 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 rowsMas 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: IA 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: AEsse 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
Bem explicado ! Parabéns !
Obrigado , prometo caprichar ainda mais nos próximos !
Tenho a OCA 10g. Você saberia as provas que preciso fazer para OCP 11g?
Abraço.
Flávio Jacinto