Cursores, Procedures e Functions aprenda a realmente usá-los

Chegamos à penúltima aula do Curso Oracle PL/SQL. E ela é para mim considerada a aula mais importante deste curso, então façam-na com bastante atenção. No final dela eu falo um pouco sobre os planos já para o começo de 2014 e lanço um desafio para quem está #embuscadabolsa, a disputa está acirrada então acesse e assista a aula até o final.

Os assuntos abordados nesta aula são:

O que é um Cursor;
Como utilizar um Cursor;
Function ou Procedure … Qual você deve usar?
Como criar uma Procedure;
Como criar uma Function;
Exercício, que vai valer alguns PONTOS EXTRAS para quem terminar primeiro!

Seguem os scripts feitos durante a aula e depois dos scripts você vai ver as especificações para o exercício.

[well]

create table tb_alunos (ID_ALUNO NUMBER(10),
nm_aluno varchar2(100),
DT_NASCIMENTO DATE);

INSERT INTO TB_ALUNOS (ID_ALUNO, NM_ALUNO, DT_NASCIMENTO)
VALUES (1, ‘WILLIAM’, TO_DATE(’20/01/1989′,’DD/MM/RRRR’));

INSERT INTO TB_ALUNOS (ID_ALUNO, NM_ALUNO, DT_NASCIMENTO)
VALUES (2, ‘WILLIAM2′, TO_DATE(’20/02/1989′,’DD/MM/RRRR’));

INSERT INTO TB_ALUNOS (ID_ALUNO, NM_ALUNO, DT_NASCIMENTO)
VALUES (3, ‘WILLIAM3′, TO_DATE(’20/03/1989′,’DD/MM/RRRR’));

INSERT INTO TB_ALUNOS (ID_ALUNO, NM_ALUNO, DT_NASCIMENTO)
VALUES (4, ‘WILLIAM4′, TO_DATE(’20/04/1989′,’DD/MM/RRRR’));
DECLARE
CURSOR cur_alunos IS
SELECT ID_ALUNO, NM_ALUNO, DT_NASCIMENTO
FROM TB_ALUNOS;

BEGIN

FOR C_ALUNOS IN cur_alunos LOOP
DBMS_OUTPUT.PUT_LINE (‘O CÓDIGO DO ALUNO É: ‘ || C_ALUNOS.ID_ALUNO);
DBMS_OUTPUT.PUT_LINE (‘O NOME DO ALUNO É: ‘ || C_ALUNOS.NM_ALUNO);
DBMS_OUTPUT.PUT_LINE (‘A DATA DE NASCIMENTO DO ALUNO É: ‘ || C_ALUNOS.DT_NASCIMENTO);
DBMS_OUTPUT.PUT_LINE (‘—————————————————————————-‘);
DBMS_OUTPUT.PUT_LINE (”);
END LOOP;
END;
DECLARE
CURSOR cur_alunos(C_ID_ALUNO TB_ALUNOS.ID_ALUNO%TYPE) IS
SELECT ID_ALUNO, NM_ALUNO, DT_NASCIMENTO
FROM TB_ALUNOS
WHERE ID_ALUNO = C_ID_ALUNO;

V_NM_ALUNO TB_ALUNOS.NM_ALUNO%TYPE;
V_ID_ALUNO TB_ALUNOS.ID_ALUNO%TYPE;
V_DT_NASCIMENTO TB_ALUNOS.DT_NASCIMENTO%TYPE;
BEGIN

OPEN cur_alunos(1);
FETCH cur_alunos INTO V_ID_ALUNO,
V_NM_ALUNO,
V_DT_NASCIMENTO;
CLOSE cur_alunos;

END;
CREATE OR REPLACE PROCEDURE SP_REL_ALUNOS (P_ID_ALUNO IN TB_ALUNOS.ID_ALUNO%TYPE,
P_NM_ALUNO OUT TB_ALUNOS.NM_ALUNO%TYPE,
P_DT_NASCIMENTO OUT TB_ALUNOS.DT_NASCIMENTO%TYPE)
IS
CURSOR cur_alunos(C_ID_ALUNO TB_ALUNOS.ID_ALUNO%TYPE) IS
SELECT ID_ALUNO, NM_ALUNO, DT_NASCIMENTO
FROM TB_ALUNOS
WHERE ID_ALUNO = C_ID_ALUNO;

V_NM_ALUNO TB_ALUNOS.NM_ALUNO%TYPE;
V_ID_ALUNO TB_ALUNOS.ID_ALUNO%TYPE;
V_DT_NASCIMENTO TB_ALUNOS.DT_NASCIMENTO%TYPE;
BEGIN

OPEN cur_alunos(P_ID_ALUNO);
FETCH cur_alunos INTO V_ID_ALUNO,
V_NM_ALUNO,
V_DT_NASCIMENTO;
CLOSE cur_alunos;

P_NM_ALUNO := V_NM_ALUNO;
P_DT_NASCIMENTO := V_DT_NASCIMENTO;
END SP_REL_ALUNOS;

DECLARE
V_NM_ALUNO TB_ALUNOS.NM_ALUNO%TYPE;
V_ID_ALUNO TB_ALUNOS.ID_ALUNO%TYPE;
V_DT_NASCIMENTO TB_ALUNOS.DT_NASCIMENTO%TYPE;
BEGIN
V_ID_ALUNO := 3;

SP_REL_ALUNOS(P_ID_ALUNO => V_ID_ALUNO,
P_NM_ALUNO => V_NM_ALUNO,
P_DT_NASCIMENTO => V_DT_NASCIMENTO);

DBMS_OUTPUT.PUT_LINE (‘O CÓDIGO DO ALUNO É: ‘ || V_ID_ALUNO);
DBMS_OUTPUT.PUT_LINE (‘O NOME DO ALUNO É: ‘ || V_NM_ALUNO);
DBMS_OUTPUT.PUT_LINE (‘A DATA DE NASCIMENTO DO ALUNO É: ‘ || V_DT_NASCIMENTO);
DBMS_OUTPUT.PUT_LINE (‘—————————————————————————-‘);
DBMS_OUTPUT.PUT_LINE (”);
END;
CREATE OR REPLACE FUNCTION FN_REL_ALUNO (P_ID_ALUNO IN TB_ALUNOS.ID_ALUNO%TYPE)
RETURN TB_ALUNOS.NM_ALUNO%TYPE
IS
CURSOR cur_alunos(C_ID_ALUNO TB_ALUNOS.ID_ALUNO%TYPE) IS
SELECT ID_ALUNO, NM_ALUNO, DT_NASCIMENTO
FROM TB_ALUNOS
WHERE ID_ALUNO = C_ID_ALUNO;

V_NM_ALUNO TB_ALUNOS.NM_ALUNO%TYPE;
V_ID_ALUNO TB_ALUNOS.ID_ALUNO%TYPE;
V_DT_NASCIMENTO TB_ALUNOS.DT_NASCIMENTO%TYPE;
BEGIN

OPEN cur_alunos(P_ID_ALUNO);
FETCH cur_alunos INTO V_ID_ALUNO,
V_NM_ALUNO,
V_DT_NASCIMENTO;
CLOSE cur_alunos;

return V_NM_ALUNO;

END FN_REL_ALUNO;

SELECT FN_REL_ALUNO(4) FROM DUAL;

[/well]

O Exercício deve ser feito da seguinte forma:

1º Você deve criar uma Procedure Chamada SP_CONTROLA_CAIXA, Esta procedure vai receber o P_ID_PRODUTO, da tabela TB_PRODUTO campo ID_PRODUTO como parâmetro de entrada e terá o parâmetro P_ERRO como parâmetro de saída.
2º Dentro dessa procedure você vai criar um cursor que vai verificar se existe o produto no estoque. Se não existir ele deve retornar uma exception, com o seguinte descrtivo "Este produto não está disponível no estoque!"
3º Se existir o produto no estoque o ID_PRODUTO deve ser o parâmetro de uma function que vai retornar o valor do produto.
4º Dentro dessa function deve haver uma verificação na tabela TB_DESCONTO, que deve ser criar com os campos ID_PRODUTO e VL_DESCONTO (Valor em porcentagem, então deve ser do tipo number (5,2)),
5º Se existir desconto para este produto, lembrando que a verificação deve ser feita por um cursor, deve-se aplicar o desconto retonardo no valor de venda do produto, que deve ser retornado em outro cursor.
6º Este valor deve ser retornado para a procedure e caso não tenha desconto deve-se retornar o valor real do produto.
7º Após o desconto aplicado você vai gravar este valor, o ID_PRODUTO e a DATA_ATUAL dentro da tabela TB_VENDAS.
8º Não se esquecer dos gerenciamentos de erros, vou cadastrar produtos com o valor 0 e tentar gravar um desconto de valor 0, por exemplo então você deve gerencia-los muito bem para eu considerar o exercício como correto.

Boa sorte e para o exercício ser considerado ele deve ser postado na comunidade do facebook https://www.facebook.com/groups/168505356686660/

p.s. Qualquer dúvida sobre o desenvolvimento é só perguntar que eu vou ajudar, mas não vou fazer o exercício 🙂