Sequence, Package e Trigger Como e Quando Utiliza-los

Tudo tem um começo, um meio e um fim … E hoje chegou ao fim o curso Oracle PL/SQL, fico muito triste com o fim, mas muito feliz por tudo que passamos juntos.

Lembrando que sem ter feito a última aula, você não vai conseguir entender 🙂 … Melhor assisti-la até o final.

Na aula de hoje vou abordar os seguintes assuntos:

1º Corrigir os exercícios da última aula que foram enviados;
2º Sequencies, o que são, como e porque utiliza-las;
3º O que é uma Package;
4º Porque utilizar uma Package;
5º Como utilizar uma Package;
6º O que é uma Trigger;
7º Quanto utilizar uma Trigger;
8º Como utilizar uma Trigger.

Agora um pouco dos códigos que foram passados na última aula:

Código para Criação de uma Sequence:

[well]

CREATE SEQUENCE SQ_VENDA
INCREMENT BY 1
START WITH 1
MAXVALUE 9999999999;

[/well]

Agora para criar uma Package Spec:

[well]

CREATE OR REPLACE PACKAGE PKG_CONTROLA_CAIXA
PROCEDURE SP_CONTROLA_CAIXA(P_ID_PRODUTO IN TB_PRODUTO.ID_PRODUTO%TYPE,
P_ERRO OUT VARCHAR2);
END PACKAGE PKG_CONTROLA_CAIXA;

[/well]

Package Body:

[well]

CREATE OR REPLACE PACKAGE BODY PKG_CONTROLA_CAIXA

FUNCTION FN_RET_VALOR(P_ID_PRODUTO in TB_PRODUTO_ESTOQUE.ID_PRODUTO%type,
P_ERRO OUT varchar2)
return TB_PRODUTO.VL_VENDA_PRODUTO%type
is
--Esse cursor verifico se existe a venda
cursor CUR_VENDA (C_ID_PRODUTO TB_PRODUTO_ESTOQUE.ID_PRODUTO%type) is
select VL_VENDA_PRODUTO
from TB_PRODUTO
where ID_PRODUTO = C_ID_PRODUTO;
--Esse cursor verifico se existe o desconto
cursor CUR_DESCONTO (C_ID_PRODUTO TB_PRODUTO_ESTOQUE.ID_PRODUTO%type) is
select VL_DESCONTO
from TB_DEsCONTO
where ID_PRODUTO = C_ID_PRODUTO;
--Declaração das variaveis
V_VL_DESCONTO TB_DESCONTO.VL_DESCONTO%type;
V_VL_VENDA_PRODUTO TB_PRODUTO.VL_VENDA_PRODUTO%type;

begin
--Abertura do cursor que verifica a venda
open CUR_VENDA(P_ID_PRODUTO);
FETCH CUR_VENDA into V_VL_VENDA_PRODUTO;
close CUR_VENDA;

begin
--Abertura do cursor que verifica o desconto
open CUR_DESCONTO(P_ID_PRODUTO);
FETCH CUR_DESCONTO into V_VL_DESCONTO;
-- Nesse bloco é: se existir registros no desconto, então tenho que aplicar o valor do desconto
if CUR_DESCONTO%FOUND then
V_VL_VENDA_PRODUTO:= V_VL_VENDA_PRODUTO - (V_VL_VENDA_PRODUTO*(V_VL_DESCONTO/100)); -- Aqui é o calculo de %
end if;

EXCEPTION
when OTHERS then
P_ERRO:= 'Não foi possível aplicar desconto no produto'||SQLERRM;
end;
close CUR_DESCONTO;
return V_VL_VENDA_PRODUTO;

end FN_RET_VALOR;

PROCEDURE SP_CONTROLA_CAIXA(
P_ID_PRODUTO IN TB_PRODUTO.ID_PRODUTO%TYPE,
P_ERRO OUT VARCHAR2)
IS
-- Cursor para verificar se existe o produto no estoque
CURSOR CUR_PRODUTO(C_ID_PRODUTO TB_PRODUTO.ID_PRODUTO%TYPE)
IS
SELECT PE.ID_PRODUTO,
P.NM_PRODUTO,
PE.QT_PRODUTO_ESTOQUE
FROM TB_PRODUTO P,
TB_PRODUTO_ESTOQUE PE
WHERE PE.ID_PRODUTO = P.ID_PRODUTO
AND PE.ID_PRODUTO = C_ID_PRODUTO;

-- Declaração das váriaveis
V_ID_PRODUTO TB_PRODUTO_ESTOQUE.ID_PRODUTO%TYPE;
V_NM_PRODUTO TB_PRODUTO.NM_PRODUTO%TYPE;
V_QT_PRODUTO_ESTOQUE TB_PRODUTO_ESTOQUE.QT_PRODUTO_ESTOQUE%TYPE;
V_VALOR_PRODUTO TB_PRODUTO.VL_VENDA_PRODUTO%TYPE;
V_ID_VENDAS TB_VENDAS.ID_VENDA%TYPE;
e_produto_nao_encontrado exception;

BEGIN
BEGIN
OPEN CUR_PRODUTO (P_ID_PRODUTO);
FETCH CUR_PRODUTO INTO V_ID_PRODUTO, V_NM_PRODUTO, V_QT_PRODUTO_ESTOQUE;
-- Neste bloco verifico se existe registros no cursor, se não existir mostro uma msg ao usuário
IF CUR_PRODUTO%NOTFOUND THEN
P_ERRO := 'PRODUTO INEXISTENTE, VERIFIQUE O CÓDIGO INFORMADO';
raise e_produto_nao_encontrado;
--Aproveitei e já verifico se existe o produto no estoque, caso não exista disparo outra mensagem para o usuario
ELSIF V_QT_PRODUTO_ESTOQUE <= 0 THEN
P_ERRO :='ESTE PRODUTO NÃO ESTÁ DISPONÍVEL NO ESTOQUE!';
raise e_produto_nao_encontrado;
END IF;
CLOSE CUR_PRODUTO;
EXCEPTION
WHEN OTHERS THEN
P_ERRO:= 'ERRO'||SQLERRM;
END;
-- Nesse bloco de if, se o programa obedecer as condições acima, o programa eh finalizado
IF P_ERRO IS NOT NULL THEN
RAISE_APPLICATION_ERROR(-20000, P_ERRO);
END IF;

-- Chamada da function, passando como parametro o id do produto.
BEGIN
V_VALOR_PRODUTO:= FN_RET_VALOR(P_ID_PRODUTO, P_ERRO);
EXCEPTION
WHEN OTHERS THEN
P_ERRO:= 'ERRO'||SQLERRM;
END;

IF P_ERRO IS NOT NULL THEN
RAISE_APPLICATION_ERROR(-20000, P_ERRO);
END IF;

BEGIN

-- Neste trecho conforme especificação do exer. eh feito a inserção dos registros na tabela TB_VENDAS
BEGIN
INSERT
INTO TB_VENDAS
(ID_VENDA,
ID_PRODUTO,
DT_ATUAL,
VALOR_PRODUTO)
VALUES
(SQ_VENDA.NEXTVAL,
P_ID_PRODUTO,
SYSDATE,
V_VALOR_PRODUTO);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
P_ERRO:= 'ERRO'||SQLERRM;
END;
EXCEPTION WHEN e_produto_nao_encontrado THEN
P_ERRO := 'PRODUTO INEXISTENTE, VERIFIQUE O CÓDIGO INFORMADO';
END SP_CONTROLA_CAIXA;
END PKG_CONTROLA_CAIXA;

[/well]

Como chamar uma Package:

[well]

DECLARE
V_ERRO VARCHAR2(1000);
BEGIN
PKG_CONTROLA_CAIXA.SP_CONTROLA_CAIXA(P_ID_PRODUTO => 1000,
P_ERRO => V_ERRO);
END;

[/well]

Criando uma Trigger de Log:

[well]

CREATE OR REPLACE TRIGGER TRG_VENDAS
BEFORE UPDATE OR DELETE ON TB_VENDAS
FOR EACH ROW

BEGIN

IF UPDATING THEN
INSERT INTO TB_VENDAS_HST (ID_VENDA, ID_PRODUTO, DT_ATUAL, VALOR_PRODUTO, DT_ALTERACAO, NM_EVENTO)
VALUES (:OLD.ID_VENDA, :OLD.ID_PRODUTO, :OLD.DT_ATUAL, :OLD.VALOR_PRODUTO, SYSDATE, 'UPDATE');
ELSIF DELETING THEN
INSERT INTO TB_VENDAS_HST (ID_VENDA, ID_PRODUTO, DT_ATUAL, VALOR_PRODUTO, DT_ALTERACAO, NM_EVENTO)
VALUES (:OLD.ID_VENDA, :OLD.ID_PRODUTO, :OLD.DT_ATUAL, :OLD.VALOR_PRODUTO, SYSDATE, 'DELETE');
END IF;

END TRG_VENDAS;

[/well]