Prof. Eduardo Bento da Rocha / BAN-II

CCT – Joinville
Prof. Eduardo Bento da Rocha / BAN-II
Fone: 47-9964-3507
www.youngarts.com.br/ban2
[email protected]
EXCEPTIONS
CREATE EXCEPTION
Cria uma mensagem de erro, armazenada no servidor, e só pode ser usado em
Stored Procedure e ou Trigger:
Sintaxe :
CREATE EXCEPTION “NOME_DA_EXCEPTION” ‘MENSAGEM’
Ex: CREATE EXCEPTION “NOME_INVALIDO” ‘O Valor informado para o campo
é inválido’
Utilização: este trecho de código, está contigo dentro de uma Trigger:
IF (NEW.NOME = ‘’) THEN
EXCEPTION NOME_INVALIDO;
STORED PROCEDURES
Uma stored procedure é um programa escrito numa linguagem própria para
procedures e triggers do Firebird que é armazenado como parte do banco de
dados. Stored procedures podem ser chamadas por aplicações cliente ou por
outras stored procedures ou triggers.
Triggers são quase a mesma coisa que stored procedures exceto pelo modo
como são chamadas. Triggers são chamadas automaticamente quando uma
alteração em uma linha da tabela ocorre.
Aplicativo
Cliente
Stored Procedures
Triggers
Aplicativo
Cliente
Aplicativo
Cliente
Aplicativo
Cliente
REDE
Vantagens:
-
redução de tráfico na rede;
Aumento performance;
Não tem que ser analisada e optimizada cada vez que é chamada;
Executam operações muito mais complexas que uma simples query.
Manutenção é mais fácil, pois é centralizado;
Stored procedures são criadas através do comando CREATE PROCEDURE que
tem a seguinte sintaxe:
CREATE PROCEDURE NomedaProcedure
(<parâmetros de entrada>)
RETURNS
(<parâmetros de saída>)
AS
<declaração de variáveis locais>
BEGIN
<comandos da procedures>
END
Exemplo 01:
SET TERM ^;
CREATE PROCEDURE SUB_TOT_BUDGET(
HEAD_DEPT VARCHAR(3)
)
RETURNS (
TOT_BUDGET NUMERIC (15, 2),
AVG_BUDGET NUMERIC (15, 2),
MIN_BUDGET NUMERIC (15, 2),
MAX_BUDGET NUMERIC (15, 2)
)
AS
BEGIN
SELECT SUM(BUDGET),
AVG(budget), MIN(budget), MAX(budget)
FROM department
WHERE head_dept = :head_dept
INTO :tot_budget, :avg_budget, :min_budget, :max_budget;
SUSPEND;
END ^
SET TERM ;^
Variáveis locais :
CREATE PROCEDURE SHIP_ORDER(
PO_NUM CHAR(8)
)
AS
DECLARE VARIABLE ord_stat CHAR(7);
DECLARE VARIABLE hold_stat CHAR(1);
DECLARE VARIABLE cust_no INTEGER;
DECLARE VARIABLE any_po CHAR(8);
BEGIN
SELECT s.order_status, c.on_hold, c.cust_no
FROM sales s, customer c
WHERE po_number = :po_num
AND s.cust_no = c.cust_no
INTO :ord_stat, :hold_stat, :cust_no;
/* Este pedido já foi enviado */
IF (ord_stat = 'shipped') THEN
BEGIN
EXCEPTION order_already_shipped;
SUSPEND;
END
.
.
.
.
.
Escrevendo o corpo da procedure:
SET TERM ^ ;
CREATE PROCEDURE "SP_MOV_CAIXA_GERAL"
(
/* Variáveis de entrada */
"DATA_INICIAL" DATE,
"DATA_FINAL" DATE
)
RETURNS
(
/* Variáveis de saída */
"DATA_PGTO" DATE,
"VENCIMENTO" DATE,
"OPERACAO" VARCHAR(10),
"NOME" VARCHAR(100),
"VALOR_PAGO" DOUBLE PRECISION,
"SALDO" DOUBLE PRECISION
)
AS
BEGIN
SALDO=0;
FOR SELECT REC_DATA_PGTO ,
REC_VENCIMENTO ,
CAST('Entrada' AS VARCHAR(10)) ,
CAST(CLI_NOME AS VARCHAR(100)) ,
REC_VALOR_PAGO
FROM CONTAS_A_RECEBER CR JOIN CLIENTES C ON
(CR.CLI_COD=C.CLI_COD)
WHERE (CR.REC_DATA_PGTO BETWEEN '01-01-2003' AND '01-01-2004')
AND (CR.REC_PAGA='True')
ORDER BY 1
INTO :DATA_PGTO,
:VENCIMENTO,
:OPERACAO,
:NOME,
:VALOR_PAGO
DO
BEGIN
SALDO = SALDO + :VALOR_PAGO;
SUSPEND;
END
END
^
SET TERM ; ^
/
Outros elementos da linguagem :
-
Comentários:
/* Variáveis de saída */
-
BEGIN-END – Sem ponto-vírgula no final:
BEGIN
END
-
Comandos de atribuição:
Var1 = (Var2 * Var3) / Var4;
-
IF-THEN-ELSE:
IF <expressão condicional> THEN
<comando>
ELSE
<comando>
Exemplos:
IF (any_sales > 0) THEN
BEGIN
EXCEPTION reassign_sales;
SUSPEND;
END
IF (first IS NOT NULL) THEN
line2 = first || ' ' || last;
ELSE
line2=last;
IF (:mngr_no IS NULL) THEN
BEGIN
mngr_name='--TBH--';
title='';
END
ELSE
SELECT full_name, job_code
FROM employee
WHERE emp_no=:mngr_no
INTO :mngr_name, :title;
-
WHILE-DO:
WHILE (i <=5) DO
BEGIN
SELECT language_req FROM job
WHERE ((job_code=:code) AND (job_grade=:grade)
(job_country=:cty) AND (language_req IS NOT NULL))
INTO :languages;
IF (languages=' ') THEN
languages='NULL';
i=i +1;
SUSPEND;
END
AND
EXERCÍCIO 01:
Criar uma procedure: Matricular (CODIGO INTEGER, SIGLA CHAR(7)) que
matricula o aluno indicado em CODIGO na disciplina SIGLA.
EXERCÍCIO 02:
Altere a SP anterior, bloqueando matrícula para alunos com idade menor que 17
anos. Utilize para isso as exceptions.
EXERCÍCIO03:
Altere a SP anterior, de modo que não permita alunos matriculados em mais de
8 disciplinas.
EXERCÍCIO 03:
Faça com que a SP anterior determine impossibilite a matrícula de alunos que
não fizeram a disciplina pré-requisito. Considere a disciplina feita quando a
matrícula do aluno tiver nota.
EXERCÍCIO 04:
Altere a procedure anterior para que ela escolha automaticamente a turma
dessa disciplina que tenha menos alunos matriculados:
EXERCÍCIO 05:
Alterar a SP MATRICULAR de modo que a matricula só poderá ser efetuada na
disciplina requisitada se o aluno já tiver cursado a disciplina pré-requisito da
mesma e tiver obtido nota > 5. Caso contrário, deve ser retornado um erro
“ViolaçãoDePréRequisito”:
1) SELECT P/ ESCOLHA DA TURMA COM MENOS ALUNOS:
SELECT COUNT(A.CODIGOALUNO), B.CODIGO
FROM MATRICULA A RIGHT JOIN
TURMA B ON (A.CODIGOTURMA = B.CODIGO)
WHERE B.SIGLA = 'SCE-200'
GROUP BY B.CODIGO
2) CRIAÇÃO DA STORED PROCEDURE:
SET TERM ^ ;
CREATE PROCEDURE MATRICULAR
(
CODIGO INTEGER,
SIGLA CHAR(7)
)
AS
DECLARE VARIABLE TURMA INTEGER;
DECLARE VARIABLE TURMAATUAL INTEGER;
DECLARE VARIABLE QUANTIDADE INTEGER;
DECLARE VARIABLE CONTAGEM INTEGER;
BEGIN
QUANTIDADE = 9999999;
FOR SELECT COUNT(A.CODIGOALUNO), B.CODIGO
FROM MATRICULA A RIGHT JOIN
TURMA B ON (A.CODIGOTURMA = B.CODIGO)
WHERE B.SIGLA = :SIGLA
GROUP BY B.CODIGO
INTO :CONTAGEM, :TURMAATUAL
DO
BEGIN
IF (CONTAGEM < QUANTIDADE) THEN
BEGIN
QUANTIDADE = :CONTAGEM;
TURMA = :TURMAATUAL;
END
END
INSERT INTO MATRICULA (CODIGOTURMA, CODIGOALUNO, NOTA)
VALUES (:TURMA, :CODIGO, NULL);
END^
SET TERM ; ^
COMMIT WORK;
EXECUTE PROCEDURE MATRICULAR (123, 'SCE-179');
COMMIT;
EXERCÍCIO 02:
Alterar a SP MATRICULAR de modo que a matricula só poderá ser efetuada na
disciplina requisitada se o aluno já tiver cursado a disciplina pré-requisito da
mesma e tiver obtido nota > 5. Caso contrário, deve ser retornado um erro
“ViolaçãoDePréRequisito”: