Baixe Interbase Server Manager - Apostilas - Informática Part3 e outras Notas de estudo em PDF para Informática, somente na Docsity!
6) CREATE DESCENDING INDEX IDX_CREDITOCLIENTES ON CLIENTES(CREDITO);
7) CREATE INDEX IDX_NOMECLIENTES ON CLIENTES(NOME);
8) CREATE INDEX IDX_NOME ON TESTE(NOME);
9) ALTER TABLE ITENS ADD CONSTRAINT FK_ID_PEDIDO FOREIGN KEY (ID_PEDIDO)
REFERENCES PEDIDOS(ID_PEDIDO);
10) ALTER TABLE ITENS ADD CONSTRAINT FK_ID_PRODUTO FOREIGN KEY
(ID_PRODUTO) REFERENCES PRODUTOS(ID_PRODUTO);
11) ALTER TABLE CLIENTES ADD
CONSTRAINT CC_STATUS CHECK (STATUS IN (0, 1));
12) ALTER TABLE CLIENTES ADD
CONSTRAINT CC_TIPO CHECK ((Tipo = 'J' ) or (Tipo = 'F'));
13) ALTER TABLE PRODUTOS ADD CONSTRAINT CC_SALDO CHECK (Saldo >= 0);
Generator
Objeto que funciona como um contador, fornecendo um número seqüencial. É amplamente utilizado em triggers, que visam atribuir um valor para campos chave.
14) CREATE GENERATOR GEN_CLIENTES; 15) CREATE GENERATOR GEN_PEDIDOS; 16) CREATE GENERATOR GEN_PRODUTOS;
Exception
A linguagem procedural ISQL, contém um mecanismo próprio para lidar com as ocorrências de erros (Exceptions). Um erro é caracterizado como sendo a ocorrência de uma exception, que dentro do contexto da ISQL pode ser identificado e tratada. Quando ocorre um erro, o InterBase levanta uma exceção, que informa a conexão responsável em três parâmetros:
SQLCODE Mensagem. Descrição < 0 SQLERROR Ocorreu erro, não sendo executado a instrução ISQL. 0 SUCCESS Sucesso na execução dos comandos ISQL. +1 até 99 SQLWARNING System warning or informational message +100 NOT FOUND Registros não encontrados, ou atingido o último registro.
Nota: Consultar referências para ver tabela de erros possíveis.
Baseado nestes parâmetros pode-se agregar a lógica de um bloco ISQL, procedimentos específicos para detectar a ocorrência da exception, bem como, um tratamento específico, inclusive com tomada da decisão. O conceito de exception no InterBase, não significa necessariamente que um erro tenha realmente ocorrido. Uma simples declaração do comando Exception indicando uma exceção previamente criada, levanta uma exceção que caracteriza de forma forçosa uma situação de erro.
Criando Exceptions:
20) CREATE EXCEPTION EXC_REGEXISTENTE "INI Este registro já existe. FIM";
21) CREATE EXCEPTION EXC_USUARIOSEMPERMISS "INI Esta conta de usuário não tem permissão para esta operação. FIM";
22) CREATE EXCEPTION EXC_SALDO "Não há saldo para este produto";
Utilizando Exceptions:
IF (SaldoProduto < New.Quantidade) THEN EXCEPTION EXC_SALDO;
Retorna:
ID_PRODUTO DESCRICAO SALDO PRECO 1 Teclado 1000 12 2 Drive 1.44 1000 25 3 Mouse 1000 5 4 Monitor 1000 280 5 Gabinete 1000 40
5 rows selected.
Onde:
Select - Comando * Indica todas as colunas From - Cláusula que define quais as tabelas envolvidas Clientes Nome da Tabela
Select Clientes.Nome, Clientes.CNPJ From Clientes
Retorna:
NOME CNPJ Lydia Guerço Rodrigues 4235445324534534 Etienne Malman 5353325364569000 Cristine M L Branco G. Rodrigues 3543545544554888 Evelyn Branco Malman 5242455254545480 Raphael Malman Costa 5454544545457877 Laercio Guerço Rodrigues 3543535534535454 Horacio Rodrigues 7557765765776766 Dinorah Lima Branco 6677777777675678 Renata Rozendo Rodrigues 5675675666565656 Pedro Henrique Rozendo Rodrigues 5454673214324233
11 rows selected.
Neste exemplo, de forma explícita, é feito referência às colunas que se deseja como retorno. Este critério resulta numa carga menor no tocante ao processamento e tráfego de dados, devido a diminuição horizontal da estrutura.
Select Clientes.Nome, Clientes.CNPJ From Clientes Where Clientes.Datacad >= ‘ 01/OCT/99 ’
A cláusula Where acrescentada ao procedimento determina uma diminuição no sentido vertical, pela restrição aplicada às linhas (registros) que não atendam ao critério.
Select Clientes.Nome, Clientes.CNPJ From Clientes Where Clientes.Datacad >= ‘ 01/01/1999 ’ Order By Clientes.Nome
Retorna:
NOME DATACAD Cristine M L Branco G. Rodrigues 09-DEC- Dinorah Lima Branco 08-DEC- Pedro Henrique Rozendo Rodrigues 08-DEC- Raphael Malman Costa 08-DEC- Victoria Emanuelle Alves 08-DEC-
Com este último adendo (Order By), vemos o retorno de dados apresentado em uma ordem que não a natural. Vale ressaltar, que não é necessário a existência de índices para que haja sucesso nesta operação. Porém, conforme já abordado no tópico índice, todo o benefício inerente a existência de tais índices, se aplica aqui.
Select com Join Select Clientes.Nome, Pedidos.DataPed, Pedidos.ID_Cliente From Clientes, Pedidos Where Clientes.ID_Cliente = Pedidos.ID_Cliente Order By Pedidos.DataPed
Aqui é apresentado uma instrução que promove um Join (Junção) entre duas tabelas. Na clausula Select pode ser observado a referência a campos de duas tabelas, que obriga referencia-las na clausula From. Na prática, o que temos aqui, é uma representação de uma teoria de conjuntos no tocante a comparação das ocorrências pela igualdade. Isto fica explícito pela seqüência da clausula Where (Clientes =Pedidos.ID_Cliente), onde haverá a seleção de dados baseado nas múltiplas ocorrências em Pedido.ID_Cliente para cada ocorrência Clientes.ID_Cliente. Normalmente estas referências às colunas de múltiplas tabelas (o Intercepta até 255 para comprar um Join) se dão entre as chaves primárias e chaves secundarias, sem que isto seja uma regra.
Usasndo Group By e Having
Select Clientes.Nmoe,Sun(Itens.QuantidadeItens.PrecoVenda).o total From Clientes, Pedidos, Itens Where Clientes.ID_Cliente= Pedidos.ID_Cliente AND Pedidos.ID_Pedido =Iten.ID_Pedido Grop By Clientes.Nome Having Sum(Itens QuantidadeItens.Precovenda) >=1 000, Order By Clientes.Nome DESC
A clausula Gruop By promove a agregação dos elementos baseado na lista de colunas Indicada. Todas as colunas relacionadas na clausula Select devem ser referenciados em Select, as colunas retornadas pelas funções SUM, MIN, MAX e AU5. Na prática, a execução de uma instrução que contenha em Group By necessita de pelo menos uma função na clausula Select. A clausula Having, que é opcional, funciona como restrição para o resultado alcançado pelo agrupamento de Group By.
View
O objeto View é criado com o objetivo de se dimensionar o acesso a uma tabela ou combinação de tabelas, de forma a definir a estrutura no sentido horizontal (pela referência criteriosa na cláusula Select de algumas colunas) e no sentido vertical (pela aplicação de restrições na cláusula Where) Uma View é um objeto composto por uma instrução Select, podendo ser aplicado todo o seu composto (conforme abordado no tópico referente ao comando Select), ou seja Select, From, Where, Group By, Having e Order By. Sua aplicação prática, se estende desde aspectos administrativos, como por exemplo, o fornecer acesso limitado a uma tabela para usuários, como também, elaborar instruções que combinem tabelas (Join), para atender demanda de solicitação de dados numa dimensão não encontrada pelas estruturas lógicas definidas. Como exemplo para esta segunda hipótese, citamos a necessidade de um acesso a dados que tivesse como retorno, uma lista de clientes com compras num certo período e que apresentasse a descrição dos produtos envolvidos. Todas estas possibilidades se aplicam a criação de View que façam referência a outras View combinadas com Tabelas.
17) CREATE VIEW VIEW_CLIENTES (ID_CLIENTE, NOME, CREDITO, STATUS) AS
SELECT CLIENTES.ID_CLIENTE, CLIENTES.NOME,
CLIENTES.CREDITO, CLIENTES.STATUS
FROM CLIENTES;
18) CREATE VIEW VIEW_FULLSELECT (DESCRICAO, TOTAL) AS
SELECT PRODUTOS.DESCRICAO,
SUM(ITENS.QUANTIDADE * ITENS.PRECO) AS TOTAL
FROM PRODUTOS, ITENS, PEDIDOS
WHERE ITENS.ID_PRODUTO = PRODUTOS.ID_PRODUTO AND
ITENS.ID_PEDIDO = PEDIDOS.ID_PEDIDO
GROUP BY PRODUTOS.DESCRICAO
HAVING SUM(ITENS.QUANTIDADE * ITENS.PRECO) > 1000;
19) CREATE VIEW VIEW_PRODITENSPEDCLI
(DESCRICAO, QUANTIDADE, PRECO, DATA, NOME) AS
SELECT PRODUTOS.DESCRICAO, ITENS.QUANTIDADE,
ITENS.PRECO, PEDIDOS.DATA, CLIENTES.NOME
FROM PRODUTOS, ITENS, PEDIDOS, CLIENTES
WHERE PRODUTOS.ID_PRODUTO = ITENS.ID_PRODUTO AND
PEDIDOS.ID_PEDIDO = ITENS.ID_PEDIDO AND
CLIENTES.ID_CLIENTE = PEDIDOS.ID_CLIENTE;
Procedures
Stored Procedures é um objeto de banco de dados que contém comandos que se resumem em linhas de comando que processam tarefas, ou fazem chamadas a outras procedures. Uma Stored Procedure (procedimento armazenado), além de executar instruções, possibilita a interação e aplicação que a invoca e o seu corpo de código por intermédio dos parâmetros definidos no seu cabeçalho. A execução de uma Stored Procedure sempre ocorre de forma explícita, ou de forma interativa, como por exemplo pela utilização do SQL* Plus ou sendo referenciada no corpo do código de uma aplicação Cliente. Neste último exemplo um procedimento armazenado, se revela um excelente núcleo para manter código que processa tarefa envolvendo a estrutura de dados. Manter o código de processamento armazenado no banco de dados, traz de imediato a facilidade na manutenção em um único local, refletindo na aplicação que faz referência a esta procedure. Se elevarmos a análise para um ambiente de desenvolvimento corporativo onde se caracteriza a heterogeneidade pela adoção de linguagens de programação múltiplas, o uso de Stored Procedure alcança importância sem igual, já que seu uso conforme abordado no parágrafo anterior, irá atingir uma maior magnitude. Diferente de uma Trigger, que tem o seu processamento disparado pela ocorrência de comandos para a tabela associada, uma Stored Procedure ocorre quando explicitamente solicitada. Na sequência, teremos alguns exemplos práticos do cotidiano, que visam explorar a plenitude do uso de procedures no contexto do desenvolvimento de sistemas comerciais.
23) CREATE PROCEDURE PROC_GENCLIENTES RETURNS (NUMERO INTEGER) AS
begin NUMERO = GEN_ID(Gen_clientes, 1); Suspend; end
24) CREATE PROCEDURE PROC_GENPRODUTOS RETURNS (NUMERO INTEGER) AS begin NUMERO = GEN_ID(Gen_Produtos, 1); Suspend; end
25) CREATE PROCEDURE PROC_GENPEDIDOS RETURNS (NUMERO INTEGER) AS begin NUMERO = GEN_ID(Gen_pedidos, 1); Suspend; end
29) CREATE PROCEDURE PROC_SELECT RETURNS (VARNOME CHAR(60)) AS BEGIN FOR SELECT NOME FROM CLIENTES INTO :VARNOME DO SUSPEND; END
30) CREATE PROCEDURE PROC_ESTATVENDA (PARID_CLIENTE INTEGER,
PARDATAINI DATE,
PARDATAFIM DATE)
RETURNS ( TOT_VENDA NUMERIC(15, 2),
AVG_VENDA NUMERIC(15, 2),
MIN_VENDA NUMERIC(15, 2),
MAX_VENDA NUMERIC(15, 2)) AS
BEGIN
SELECT SUM(ITENS.QUANTIDADE * ITENS.PRECO),
AVG(ITENS.QUANTIDADE * ITENS.PRECO),
MIN(ITENS.QUANTIDADE * ITENS.PRECO),
MAX(ITENS.QUANTIDADE * ITENS.PRECO)
FROM CLIENTES, PEDIDOS, ITENS
WHERE CLIENTES.ID_CLIENTE = PEDIDOS.ID_CLIENTE AND
PEDIDOS.ID_PEDIDO = ITENS.ID_PEDIDO AND
CLIENTES.ID_CLIENTE = :PARID_CLIENTE AND
PEDIDOS.DATA BETWEEN
:PARDATAINI AND :PARDATAFIM
INTO :TOT_VENDA,
:AVG_VENDA,
:MIN_VENDA,
:MAX_VENDA;
SUSPEND;
END
Triggers
Triggers são blocos ISQL que contém códigos que implementam instruções que podem afetar dados contidos em tabelas, validos dados baseadas em certas condições, acionar procedimentos armazenados entre outros objetos. Uma Trigger, que significa gatilho, é definida (criada) e associada a um evento (ocorrência) conseqüente aos comandos Insert, Delete ou Update. Ao contrário de uma Store Procedure (Procedimento Armazenad), uma Trigger não é invocada nem referenciada em código Cliente nem no servidor InterBase. Conforme sugere seu nome, uma Trigger é disparada ( executada )pela manipulação da tabela na qual foi criada. Conforme conceitualmente já abordado no tópico integridade, uma Trigger é um excelente mecanismo para se controlar as restrições, tendo em vista a possibilidade de interferir nos valores que estão sendo manipulados, pode-se ainda estabelecer uma comunicação clara com a aplicação Front-End. Como exemplos de situações em que a adoção de Triggers se aplica, relacionamos o seguinte:
- Fazer manutenção (atualização) em outras tabelas quando ocorrerem operações de inclusão, alteração ou exclusão na tabela corrente.
- Estabelecer validação para colunas.
- Alimentar tabelas de controle de Log.
- Sinalizar aplicação Front-End de alguma ocorrência específica.
30) CREATE TRIGGER TRIG_TESTACREDITO_INC FOR CLIENTES
ACTIVE BEFORE INSERT POSITION 0 AS
BEGIN
IF (NEW.CREDITO > 20000) THEN
BEGIN
NEW.CREDITO = 20000;
END
END
31) CREATE TRIGGER TRIG_TESTACREDITO_ALT FOR CLIENTES
ACTIVE BEFORE UPDATE POSITION 0 AS
BEGIN
IF (NEW.CREDITO > 20000) THEN
BEGIN
NEW.CREDITO = 20000;
END
END
32) CREATE TRIGGER TRIG_ID_CLIENTE FOR CLIENTES
ACTIVE BEFORE INSERT POSITION 0 AS
BEGIN
NEW.ID_Cliente = GEN_ID(Gen_clientes, 1); END
Outros Objetos
Role
Sintáxe:
CREATE ROLE Nome_da_Role;
Exemplo:
Create Role Administrador;
Grant
Sintáxe:
GRANT UPDATE ON Clientes TO Administrador;
GRANT Administrador TO user1, user2, user3;
GRANT SELECT, DELETE ON Clientes TO Administrador WITH GRANT OPTION;
Revoke
Sintáxe:
REVOKE SELECT ON Clientes FROM Administrador;
