Baixe o app para aproveitar ainda mais
Prévia do material em texto
BD2 Profa. Rossana Junqueira 1 Disciplina: Banco de Dados 2 Profa. Rossana de Paula Junqueira Almeida Ciência da Computação BD2 Profa. Rossana Junqueira 2 Capítulo 1: O Problema BD2 Profa. Rossana Junqueira 3 O problema • Problema a ser utilizado: Fabrica(Id_Fabrica, Nome, Endereco, Cidade, UF, Telefone, Email) Produto(Id_Produto, Referencia, Descricao, Unidade, Id_Fabrica, Id_ProdutoC) Id_Fabrica referencia Fabrica Id_ProdutoC referencia Produto CondPagto(Id_Condicao, Descricao) Cliente(Id_Cliente, Nome, Endereco, Cidade, UF, Telefone, Contato) Pedido(Id_Pedido, Data, Valor, Id_Cliente, Id_Vendedor, Id_Transportadora) Id_Cliente referencia Cliente Id_Vendedor referencia Vendedor Id_Transportadora refencia Transportadora BD2 Profa. Rossana Junqueira 4 O problema • Problema a ser utilizado: Vendedor(Id_Vendedor, Nome) Transportadora(Id_Transportadora, Nome, Endereco, Cidade, UF) ProdutoPedido(Id_Pedido, Id_Produto, Quantidade) Id_Pedido referencia Pedido Id_Produto referencia Produto ProdutoCond(Id_Produto, Id_Condicao, Preco) Id_Produto referencia Produto Id_Condicao referencia CondPagto BD2 Profa. Rossana Junqueira 5 Capítulo 2: Criação de Banco de Dados BD2 Profa. Rossana Junqueira 6 Criação de Banco de Dados • Conjunto de objetos que armazenam e manipulam dados. • Como nomear arquivos e objetos: – Regras para nomeação dos objetos: • Caracteres A...Z, a...z, 0...9, $ e _ • Nomes devem começar com A...Z ou a...z. • Nomes limitados a 31 caracteres. • Nomes dos objetos devem ser únicos. • CREATE DATABASE „C:\Banco de Dados\empresa.fdb‟; • Dialetos: – 1 Compatibilidade com bases de dados criadas em versões anteriores. – 2 Modo de diagnóstico (não deve ser usado) – 3 Acesso total às novas implementações introduzidas a partir da versão 6 do Interbase e que foram incorporadas no Firebird. BD2 Profa. Rossana Junqueira 7 Criação de Banco de Dados • Os objetos de uma Banco de Dados: – Domínios – Tabelas – Índices – Restrições Foreing Key – Visões – Restrições CHECK – Exceções – Stored Procedures – Geradores – Triggers – Papéis – Atribuições de privilégios BD2 Profa. Rossana Junqueira 8 Capítulo 3: Criação de Domínos BD2 Profa. Rossana Junqueira 9 Criação de Domínios • São tipos de dados definidos pelo usuário a partir de um tipo de dado primitivo. • Tipos de dados: – BLOB – Char(n) – Date – Decimal – Integer – Numeric – Smallint – Time – Varchar etc 10 Criação de Domínios • Para criar um domínio, usa-se a instrução: CREATE DOMAIN [nome do domínio] as <tipo de dado> • Domínios simples: – Consistem apenas em definir um apelido para um tipo de dado. – Exemplo: CREATE DOMAIN Tipo_Nome as VARCHAR(40); • Domínios complexos: – Além de definirem um apelido para o tipo de dado, estabelecem regras e restrições para o novo tipo de dado. – Exemplo: CREATE DOMAIN Tipo_UF as CHAR(2) CHECK (VALUE in („AM‟, „AC‟, „RR‟, RO‟, „AP‟, „PA‟, „MA‟, „PI‟, „CE‟, „RN‟, „PE‟, „PB‟, „AL‟, „SE‟, „MT‟, „MS‟, „GO‟, „TO‟, „BA‟, „MG‟, „ES‟, „RJ‟, „SP‟, „PR‟, „SC‟, „RS‟, „DF‟)) NOT NULL; BD2 Profa. Rossana Junqueira BD2 Profa. Rossana Junqueira 11 Criação de Domínios • Domínios complexos: – Exemplo: CREATE DOMAIN Tipo_Monetario as NUMERIC(10,2) CHECK (VALUE > 0) NOT NULL; • Remover domínio existente: DROP DOMAIN [nome do domínio]; • Alterar domínio: ALTER DOMAIN [nome do domínio] SET DEFAULT <novo valor>; – Exemplo: ALTER DOMAIN Tipo_UF SET DEFAULT „PA‟; BD2 Profa. Rossana Junqueira 12 Capítulo 4: Criação de Tabelas BD2 13 Criação de Tabelas • Criar: CREATE TABLE • Alterações: ALTER TABLE • Eliminar: DROP TABLE • Restrições a serem especificadas para uma coluna de tabela: – Regras que governam os relacionamentos e validam entradas de dados. – Atuam em todas as transações que acessam o banco de dados e são automaticamente mantidas pelo sistema. – Restrição UNIQUE e PRIMARY KEY: • Asseguram que os valores inseridos em uma ou mais colunas são únicos para cada linha da tabela. • Uma ou mais colunas definidas com essas restrições devem também ser definidas com o atributo NOT NULL. Profa. Rossana Junqueira Criação de Tabelas – Restrição FOREIGN KEY: • Chave estrangeira é uma ou mais colunas em uma tabela que corresponde exatamente a uma ou mais colunas definida(s) como chave primária em outra tabela. • O Firebird força a integridade referencial, como também é chamada a restrição de chave estrangeira, das seguintes maneiras: – As colunas com restrição de chave primária ou única devem ser definidas antes de criar a chave estrangeira que as referencia. – Verificações de integridade referencial estão disponíveis com as opções ON UPDATE e ON DELETE da cláusula REFERENCES. Com essas opções pode-se especificar o que acontecerá à chave estrangeira quando houver uma alteração na chave primária. As ações podem ser: Ação Efeito na chave estrangeira NO ACTION A chave estrangeira não se altera e ocorre um erro. CASCADE As chaves estrangeiras são atualizadas ou deletadas. SET DEFAULT Os valores de chave estrangeira são alterados para o valor padrão. Pode ocasionar erro se o valor padrão não tiver chave primária correspondente. SET NULL Os valores das chaves estrangeiras mudam para NULL. BD2 Profa. Rossana Junqueira 15 Criação de Tabelas – Se as opções ON UPDATE ou ON DELETE não forem usadas, deve-se assegurar que, se a informação mudar em um lugar, ela deve ser modificada em toda coluna que a ela se refere. – Se uma linha da tabela referenciada for deletada, todas as linhas que se referem a ela devem ser eliminadas antes. Se for utilizada a opção ON DELETE CASCADE, o Firebird faz isso automaticamente. – Só é possível adicionar um valor a uma coluna definida como chave estrangeira se existir a chave primária referenciada. – Restrição CHECK: • Estabelece uma condição que deve ser verdadeira durante uma entrada ou atualização de dados na tabela. – Além dessas restrições, uma coluna pode ser definida com o atributo NOT NULL. Esse atributo não permite valores nulos na coluna onde é definida e é obrigatório em colunas com restrições PRIMARY KEY ou UNIQUE. BD2 Profa. Rossana Junqueira 16 Criação de Tabelas • CREATE TABLE cria uma nova tabela, suas colunas e restrições de integridade em um banco de dados. – Tabela Fabrica CREATE TABLE FABRICA ( ID_FABRICA INTEGER NOT NULL, NOME VARCHAR(40) NOT NULL, ENDERECO VARCHAR(40), CIDADE VARCHAR(30), UF TIPO_UF, TELEFONE VARCHAR(20), CONSTRAINT PK_FABRICA PRIMARY KEY (ID_FABRICA)); BD2 Profa. Rossana Junqueira 17 Criação de Tabelas – Tabela Cliente CREATE TABLE CLIENTE ( ID_CLIENTE INTEGER NOT NULL, NOME VARCHAR(40), ENDERECO VARCHAR(40), CIDADE VARCHAR(30), UF TIPO_UF, TELEFONE VARCHAR(20), CONTATO VARCHAR(20), CONSTRAINT PK_CLIENTE PRIMARY KEY (ID_CLIENTE)); 18 Criação de Tabelas – Tabela Vendedor CREATE TABLE Vendedor (Id_Vendedor INTEGER NOT NULL, Nome VARCHAR(40) NOT NULL, CONSTRAINT PK_Vendedor PRIMARY KEY (ID_Vendedor)); – Tabela Transportadora CREATE TABLE Transportadora (Id_Transportadora INTEGER NOT NULL, Nome VARCHAR(40) NOT NULL, Endereco VARCHAR(40) NOT NULL, Cidade VARCHAR(30) NOT NULL, UF Tipo_UF, CONSTRAINT PK_TRANSPORTADORA PRIMARY KEY (ID_TRANSPORTADORA)); BD2 BD2 Profa. Rossana Junqueira 19 Criação de Tabelas – Tabela CondPagto CREATE TABLE CondPagto (Id_Condicao INTEGER NOT NULL, Descricao VARCHAR(30) NOT NULL, CONSTRAINT PK_CONDPAGTO PRIMARY KEY (ID_CONDICAO)); BD2 Profa. Rossana Junqueira 20 Criação de Tabelas – Tabela Pedido CREATE TABLE Pedido (Id_Pedido INTEGER NOT NULL, Data DATE DEFAULT CURRENT_DATENOT NULL, Valor Tipo_Monetario, Id_Cliente INTEGER NOT NULL, Id_Transportadora INTEGER NOT NULL, Id_Vendedor INTEGER NOT NULL, CONSTRAINT PK_PEDIDO PRIMARY KEY (ID_PEDIDO), CONSTRAINT FK_PEDIDO_CLIENTE FOREIGN KEY (ID_CLIENTE) REFERENCES Cliente (Id_Cliente), CONSTRAINT FK_PEDIDO_TRANSPORTADORA FOREIGN KEY (ID_TRANSPORTADORA) REFERENCES Transportadora (Id_Transportadora), CONSTRAINT FK_PEDIDO_VENDEDOR FOREIGN KEY (ID_VENDEDOR) REFERENCES Vendedor (Id_Vendedor)); BD2 Profa. Rossana Junqueira 21 Criação de Tabelas – Tabela Produto CREATE TABLE Produto (Id_Produto INTEGER NOT NULL, Referencia VARCHAR(15), Descricao VARCHAR(40) NOT NULL, Unidade CHAR(2) DEFAULT 'UN' NOT NULL, Id_Fabrica INTEGER NOT NULL, Id_ProdutoC INTEGER, CONSTRAINT PK_PRODUTO PRIMARY KEY (ID_PRODUTO), CONSTRAINT FK_PRODUTO_FABRICA FOREIGN KEY (ID_FABRICA) REFERENCES Fabrica (Id_Fabrica), CONSTRAINT FK_PRODUTO_PRODUTO FOREIGN KEY (ID_PRODUTOC) REFERENCES Produto (Id_Produto)); OBS: A cláusula DEFAULT estabelece que se nada for explicitamente inserido nesta coluna, o valor padrão é assumido. BD2 Profa. Rossana Junqueira 22 Criação de Tabelas – Tabela ProdutoPedido Create TABLE ProdutoPedido (Id_Pedido INTEGER NOT NULL, Id_Produto INTEGER NOT NULL, Quantidade INTEGER NOT NULL, CONSTRAINT PK_PRDODUTOPEDIDO PRIMARY KEY (Id_Pedido, Id_Produto), CONSTRAINT CH_QUANTIDADE CHECK (Quantidade > 0), CONSTRAINT FK_PRODUTOPEDIDO_PEDIDO FOREIGN KEY (ID_PEDIDO) REFERENCES Pedido (Id_Pedido), CONSTRAINT FK_PRODUTOPEDIDO_PRODUTO FOREIGN KEY (ID_PRODUTO) REFERENCES Produto (Id_Produto)); BD2 Profa. Rossana Junqueira 23 Criação de Tabelas – Tabela ProdutoCond CREATE TABLE ProdutoCond (Id_Produto INTEGER NOT NULL, Id_Condicao INTEGER NOT NULL, Preco TIPO_MONETARIO, CONSTRAINT PK_PRODUTOCOND PRIMARY KEY (Id_Produto, Id_Condicao), CONSTRAINT FK_PRODUTOCOND_PRODUTO FOREIGN KEY (ID_PRODUTO) REFERENCES Produto (Id_Produto), CONSTRAINT FK_PRODUTOCOND_CONDICAO FOREIGN KEY (ID_CONDICAO) REFERENCES CondPagto (Id_Condicao)); BD2 Profa. Rossana Junqueira 24 Alteração de Tabelas • ALTER TABLE modifica uma tabela adicionando, modificando ou eliminando colunas ou restrições. • Para alterar uma tabela, deve-se observar o seguinte: – Uma tabela pode ser alterada por seu criador ou qualquer usuário com direitos de superusuário do sistema operacional. – ALTER TABLE provoca erro se os novos dados em uma tabela violam as definições de restrição PRIMARY KEY ou UNIQUE. – Eliminar uma coluna provoca falha se: • A coluna é parte de uma restrição UNIQUE, PRIMARY KEY ou FOREING KEY. • A coluna é usada em uma restrição CHECK. • A coluna é utilizada em uma expressão value de uma coluna calculada. • A coluna é referenciada por outros objetos, tais como uma visão. BD2 Profa. Rossana Junqueira 25 Alteração de Tabelas – Antes de alterar ou eliminar colunas em uma tabela, é necessário: • Verificar se há privilégios apropriados. • Salvar os dados da coluna a ser modificada/eliminada. • Eliminar qualquer restrição existente na coluna. • Salvar os dados de uma coluna consiste de um processo de cinco passos: – Adicionar uma coluna temporária à tabela com as mesmas definições da coluna a ser alterada. – Copiar os dados da coluna a ser alterada para a coluna temporária. – Modificar a coluna. – Copiar os dados da coluna temporária para a coluna modificada. – Eliminar a coluna temporária. BD2 Profa. Rossana Junqueira 26 Alteração de Tabelas • Exemplos de utilização de ALTER TABLE: – Incluir nova coluna • ALTER TABLE Fabrica ADD Email VARCHAR(30); • ALTER TABLE Fabrica ADD Contato VARCHAR(30) , ADD Fax VARCHAR(18); – Eliminar uma coluna existente • ALTER TABLE Fabrica DROP Fax; – Alterar a posição de uma coluna • ALTER TABLE Produto ALTER Id_ProdutoC POSITION 5; BD2 Profa. Rossana Junqueira 27 Alteração de Tabelas • Exemplos de utilização de ALTER TABLE: – Alterar o tipo de dado de uma coluna • ALTER TABLE Fabrica ALTER Email TYPE VARCHAR(40); • Obs: Tamanhos de tipos não podem ser reduzidos e o novo tipo de dado deve ser capaz de manter os dados originais. – Alterar o nome da coluna • ALTER TABLE Fabrica ALTER Nome TO RazaoSocial; – Adicionar uma nova restrição • ALTER TABLE Fabrica ADD CONSTRAINT Un_Mail UNIQUE (Email); – Eliminar uma restrição existente • ALTER TABLE Fabrica DROP CONSTRAINT Un_Mail; BD2 Profa. Rossana Junqueira 28 Exclusão de Tabelas • A instrução DROP TABLE remove dados de tabela. • Não se pode eliminar uma tabela que é referenciada em uma coluna calculada, uma visão, restrição de chave estrangeira ou procedimento armazenado. • DROP TABLE [nome da tabela] BD2 Profa. Rossana Junqueira 29 Capítulo 5: Povoamento de Tabelas BD2 Profa. Rossana Junqueira 30 Povoamento de Tabelas • INSERT armazena uma ou mais linhas de dados em uma tabela. • Os valores inseridos devem estar na mesma ordem das colunas da tabela. • Se o número de colunas informadas no comando for menor que o número de colunas da tabela, valores padrões ou NULL são armazenados nas colunas não informadas. • Para inserir uma única linda de dados na tabela, deve-se especificar uma lista de valores na cláusula VALUES. • Inserções de dados nas tabelas não podem violar as restrições. • É importante inserir dados primeiramente nas tabelas referenciadas por chaves estrangeiras. BD2 Profa. Rossana Junqueira 31 Povoamento de Tabelas • Exemplos: – INSERT INTO Vendedor VALUES (1, „João Dias‟); – A instrução seguinte insere uma linha na mesma tabela, mas informa explicitamente as colunas para as quais serão informados: • INSERT INTO Vendedor (Id_Vendedor, Nome) VALUES (2, „Fábio Almeida‟); • INSERT INTO Cliente VALUES (1, „Francisco de Assis‟, „Av. Principal, 500‟, „Santarém‟, „PA‟, „3522-0101‟, „Francisco‟); 32 Povoamento de Tabelas – INSERT INTO Transportadora VALUES (1, „Transpedroso‟, „Av. Getúlio Vargas, 500‟, „São Paulo‟, „SP‟); – INSERT INTO Pedido VALUES (1, ‟06/15/2005‟, 500, 1, 1, 1); • O campo data é inserido no formato mm/dd/aaaa – INSERT INTO Fabrica (Id_Fabrica, RazaoSocial, UF) VALUES (1, ‟CAP Computadores Ltda‟, „SP‟); – INSERT INTO Produto VALUES (1, null, 'Microcomputador', 'UN', null, 1); • As colunas Referencia e Id_ProdutoC da tabela Produto permitem valores nulos e esta é uma maneira de inserir nulos. • A outra é a maneira como foi inserida a linha na tabela Fabrica. Omitiram-se as colunas que receberão valores nulos. BD2 BD2 Profa. Rossana Junqueira 33 Povoamento de Tabelas – INSERT INTO Produto VALUES (2, Null, „Monitor de Vídeo‟, „UN‟, 1, 1); – INSERT INTO Produto VALUES (3, Null, „Placa Mãe‟, „UN‟, 1, 1); • Inserem produtos que compõem o Microcomputador. A chave estrangeira Id_ProdutoC é gravada com o valor 1, que é chave primária de Microcomputador. – Os dados inseridos nas tabelas até agora não foram gravados definitivamente nelas. – Após os inserts, deve ser emitido o comando: COMMIT; E os dados serão gravados em definitivo no banco de dados. – Se for necessário cancelar essas inserções antes de serem gravadas, deve ser emitido o comando ROLLBACK; BD2 Profa. Rossana Junqueira 34 Alteração de dados • A instrução UPDATE modifica os dados em toda ou parte de uma linha de uma tabela. • Em caso de atualizações seletivas, a cláusula opcional WHERE pode ser usada para restringir as atualizações a um subconjunto de linhas na tabela. • Se a cláusula WHERE não for utilizada, todas as linhas da tabela serão atualizadas. • O seguinte comando modifica as linhas da tabela. Esse comando calcula um novo preço para todos os produtos, reajustando-os em 5%: – UPDATE ProdutoCond SET Preco = Preco * 1.05; 35 Alteração de dados • O comando a seguir modifica apenas uma linha databela. Modifica a descrição do produto que tem chave primária igual a 2: – UPDATE Produto SET Descricao = „Monitor de vídeo 15”‟ WHERE Id_Produto = 2; – Quando for necessário alterar os dados de uma única linha é aconselhável selecioná-la pela chave primária. Este é a garantia de que a atualização ocorrerá em uma única linha, no máximo. • UPDATE Pedido SET Id_Transportadora = 1, Id_Vendedor = 2 WHERE Data = ‟06/15/2005‟; – O comando acima modifica dados de duas colunas dos pedidos efetuados em 15/06/2005. • Deve ser utilizado o comando COMMIT para confirmar as atualizações ou ROLLBACK para desfazê-las. BD2 Profa. Rossana Junqueira BD2 Profa. Rossana Junqueira 36 Exclusão de linhas • O comando DELETE elimina linhas de uma tabela. • Especifica uma ou mais linhas a serem eliminadas de uma tabela. • No caso de deleções seletivas, a cláusula WHERE deve ser utilizada para restringir as exclusões a um subconjunto de linhas de uma tabela. • Se a cláusula WHERE não for especificada, todas as linhas da tabela são eliminadas. • O comando a seguir deleta todas as linhas da tabela: – DELETE FROM ProdutoPedido; Anny Caroliny Nota Diferença entre DROP e DELETE.nullnullDROP - apaga a tabela e os dados existente dela no banco no geral.nullnullDELETE - apaga apenas os dados existentes na tabela, a tabela permanece no banco. BD2 Profa. Rossana Junqueira 37 Exclusão de linhas • A exclusão de linhas de uma tabela não pode violar as restrições de chave estrangeira. • As linhas de tabelas referenciadas de chaves estrangeiras só podem ser excluídas se foi utilizada a cláusula ON DELETE CASCADE. • Se não, devem ser excluídas inicialmente as linhas da tabela filha (a tabela que tem a chave estrangeira). • O comando seguinte elimina linhas específicas de uma tabela: – DELETE FROM ProdutoPedido WHERE Id_Pedido = 1; • Deve ser utilizado o comando COMMIT para confirmar as atualizações ou ROLLBACK para desfazê-las. BD2 Profa. Rossana Junqueira 38 Capítulo 6: Consultas a Dados de uma Tabela BD2 Profa. Rossana Junqueira 39 Consultas a dados de uma tabela • Uma das funções mais importantes de um SGBD é permitir que os dados armazenados em um banco de dados sejam recuperados das formas mais diversas que se possa imaginar. • O comando SELECT é o comando SQL utilizado para recuperar informações de bancos de dados. • Operadores: – A cláusula WHERE utiliza expressões que limitam as linhas da tabela a serem recuperadas. – Dessas expressões fazem parte os operadores que podem ser classificados em quatro categorias: • Aritméticos • Lógicos • De comparação • De concatenação BD2 Profa. Rossana Junqueira 40 Consultas a dados de uma tabela – Aritméticos: + Adição - Subtração * Multiplicação / Divisão – Lógicos: AND E OR Ou NOT Não – De concatenação: || Concatenação de strings – De comparação: < Menor que > Maior que <= Menor ou igual a >= Maior ou igual a = Igual <> Diferente BETWEEN Entre IN Em IS É LIKE Igual a BD2 Profa. Rossana Junqueira 41 Consultas a dados de uma tabela • Consultas simples: – SELECT * FROM CLIENTE; • Esse comando recupera todas as linhas e colunas da tabela CLIENTE. • O asterisco (*) indica que todas as colunas são mostradas. – Se for desejado recuperar colunas específicas da tabela, um exemplo seria: • SELECT NOME, CONTATO, TELEFONE FROM CLIENTE; – É possível também, nas consultas, que os nomes originais das colunas sejam modificados. • SELECT ID_FABRICA AS ID, RAZAOSOCIAL AS "RAZÃO SOCIAL“ FROM FABRICA; • A cláusula AS permite que as colunas recebam um apelido. BD2 Profa. Rossana Junqueira 42 Consultas a dados de uma tabela • A cláusula AS permite que as colunas recebam um apelido. • Se o apelido possuir espaços deve-se defini-lo entre aspas. • A cláusula AS é opcional. • O comando no formato a seguir produz o mesmo resultado: – SELECT ID_FABRICA ID, RAZAOSOCIAL "RAZÃO SOCIAL“ FROM FABRICA; – As colunas de consultas podem incluir expressões aritméticas. • SELECT ID_PRODUTO, ID_CONDICAO, PRECO * 1.05 FROM PRODUTOCOND; • Esta consulta mostra os preços dos produtos com seus valores reajustados em 5%. • No resultado da consulta, essa coluna não estará identificada. Assim, é fortemente aconselhável utilizar sempre os apelidos. – SELECT ID_PRODUTO, ID_CONDICAO, PRECO * 1.05 “Novo Preço” FROM PRODUTOCOND; BD2 Profa. Rossana Junqueira 43 Consultas a dados de uma tabela – A consulta seguinte utiliza o operador de concatenação: • SELECT NOME, ENDERECO, CIDADE || '-' || UF "CIDADE-ESTADO" FROM CLIENTE; • A terceira coluna da consulta é resultante da concatenação de três strings: as colunas da tabela, CIDADE e UF, e o literal hífen (-). • Foi atribuído um apelido à coluna resultante do cálculo. – A cláusula DISTINCT é utilizada para suprimir linhas duplicadas do resultado de uma coluna. • SELECT CIDADE FROM CLIENTE; • O comando acima poderia retornar linhas duplicadas. • Para eliminar as duplicações utiliza-se DISTINCT: – SELECT DISTINCT CIDADE FROM CLIENTE; BD2 Profa. Rossana Junqueira 44 Consultas a dados de uma tabela – A cláusula FIRST n retorna as n primeiras linhas do resultado da consulta. • SELECT FIRST 3 * FROM CLIENTE; – A cláusula SKIP n faz com que as n primeiras linhas resultantes sejam ignoradas e as linhas a partir da n+1 são mostradas. • SELECT SKIP 3 * FROM CLIENTE; BD2 Profa. Rossana Junqueira 45 Consultas a dados de uma tabela • Uso da cláusula WHERE: – O uso de WHERE juntamente com as expressões envolvendo os operadores irá permitir que seja obtido o resultado de uma consulta seletiva, onde apenas as linhas que atendem às restrições estabelecidas são mostradas. – Com operadores de Comparação: • SELECT ID_CLIENTE, NOME FROM CLIENTE WHERE UF = 'PA'; – Retornam da tabela CLIENTE as linhas que atendem a condição de que a coluna UF seja igual a „PA‟. • SELECT ID_PEDIDO, VALOR, ID_CLIENTE FROM PEDIDO WHERE DATA > '06/15/2005'; – Essa consulta mostra os pedidos cuja DATA seja maior que 15/06/2005. BD2 Profa. Rossana Junqueira 46 Consultas a dados de uma tabela • SELECT ID_CLIENTE, NOME FROM CLIENTE WHERE NOME LIKE 'A%'; – O operador LIKE recupera da tabela os clientes que têm o nome iniciando com a letra A. • SELECT * FROM PEDIDO WHERE VALOR BETWEEN 200 AND 700; – Essa consulta retorna todos os pedidos, tais que a coluna VALOR esteja no intervalo entre 200 e 700 reais. – Se houver linhas cujos valores sejam iguais a 200 ou 700, elas serão retornadas. • SELECT ID_FABRICA, RAZAOSOCIAL FROM FABRICA WHERE UF IN ('PA', 'AM', 'AC', 'AP'); – Essa consulta retorna as linhas onde a coluna UF seja igual a qualquer dos elementos relacionados entre parênteses. BD2 Profa. Rossana Junqueira 47 Consultas a dados de uma tabela – Com operadores Lógicos: • SELECT * FROM CLIENTE WHERE NOT UF = 'PA'; – Mostra os clientes que não são do estado do Pará. • SELECT * FROM PEDIDO WHERE VALOR NOT BETWEEN 200 AND 700; – Retorna os pedidos cujos valores não estão entre 200 e 700. • SELECT * FROM CLIENTE WHERE NOME NOT LIKE 'D%'; – Recupera os clientes cujos nomes não iniciam com a letra D. • SELECT * FROM PRODUTO WHERE ID_PRODUTOC IS NOT NULL; – Mostra os produtos que fazem parte da composição de outro produto, ou seja, aqueles em que a coluna ID_COMPOSTOC não é nula. BD2 Profa. Rossana Junqueira 48 Consultas a dados de uma tabela • SELECT * FROM PEDIDO WHERE VALOR > 200 AND VALOR < 700; – AND e OR são utilizados para criar condições complexas resultantes de duas ou mais condições que usam operadores de comparação. • Uso da cláusula ORDER BY: – Como padrão, uma consulta recupera linhas na mesma ordem em que ela as encontra na tabela. – Para especificar uma ordem naqual as linhas devem ser retornadas por uma consulta, usa-se a cláusula ORDER BY no fim do comando SELECT. • SELECT ID_FABRICA, RAZAOSOCIAL, CIDADE FROM FABRICA ORDER BY CIDADE; • SELECT ID_FABRICA, RAZAOSOCIAL, CIDADE FROM FABRICA ORDER BY CIDADE DESC, RAZAOSOCIAL; BD2 Profa. Rossana Junqueira 49 Consultas a dados de uma tabela • Uso de funções: – AVG • SELECT AVG(VALOR) FROM PEDIDO; – Calcula a média dos valores numéricos em uma coluna. – COUNT • SELECT COUNT(*) FROM CLIENTE; – Calcula o número de linhas que satisfazem uma condição de seleção de uma consulta. – MAX • SELECT MAX(VALOR) FROM PEDIDO; – Recupera o valor máximo de uma coluna. – MIN • SELECT MIN(VALOR) FROM PEDIDO; – Recupera o valor mínimo de uma coluna. BD2 Profa. Rossana Junqueira 50 Consultas a dados de uma tabela • Agrupamento de linhas com GROUP BY: – Habilita uma consulta retornar um resumo sobre grupos de linhas que compartilham valores de coluna. – SELECT ID_CLIENTE, AVG(VALOR) FROM PEDIDO GROUP BY ID_CLIENTE; • O exemplo retorna o valor médio dos pedidos de cada cliente. • A cláusula GROUP BY garante que o valor médio dos pedidos seja calculado e recuperado com base no identificados de cada cliente. BD2 Profa. Rossana Junqueira 51 Consultas a dados de uma tabela • Restrição de linhas agrupadas com HAVING: – Assim como a cláusula WHERE restringe o número de linhas retornadas pela cláusula SELECT, a cláusula HAVING pode reduzir o número de linhas retornadas por uma cláusula GROUP BY. – SELECT ID_CLIENTE, AVG(VALOR) FROM PEDIDO GROUP BY ID_CLIENTE HAVING AVG(VALOR) > 500; • O comando retorna o valor médio dos pedidos de cada cliente. • A cláusula GROUP BY assegura que os valores médios dos pedidos sejam calculados e recuperados baseados nos identificadores dos clientes. • A cláusula HAVING restringe o resultado àqueles grupos onde a média dos valores dos pedidos é maior que 500. BD2 Profa. Rossana Junqueira 52 Capítulo 7: Relacionamentos entre Tabelas BD2 Profa. Rossana Junqueira 53 Relacionamento entre tabelas • Devido ao Firebird se tratar de um banco de dados relacional, às vezes pode ser necessário construir consultas a dados que estão em tabelas diferentes. • A recuperação de dados de duas ou mais tabelas usando um único comando SELECT é denominada junção (join). • select PEDIDO.ID_PEDIDO, PEDIDO.DATA, PEDIDO.VALOR, CLIENTE.NOME from PEDIDO, CLIENTE where PEDIDO.ID_CLIENTE = CLIENTE.ID_CLIENTE and PEDIDO.DATA >= '06/25/2006'; BD2 Profa. Rossana Junqueira 54 Capítulo 8: Subconsultas BD2 Profa. Rossana Junqueira 55 Subconsultas • É um comando SELECT entre parênteses dentro da cláusula WHERE de outro comando SQL mais externo, o que funciona como uma condição de seleção para restringir as linhas afetadas pelo comando SQL externo. • Faz parte de uma condição de seleção, ela geralmente é avaliada antes do comando SQL mais externo, que usa o resultado para determinar se as linhas atendem ou não as condições definidas na cláusula WHERE. • Determina a condição de seleção da cláusula WHERE do comando mais externo de três formas diferentes: – Produz uma lista de valores para avaliação por um operador IN na cláusula WHERE do comando mais externo, ou onde o operador de comparação é modificado pelos operadores ALL, ANY ou SOME. BD2 Profa. Rossana Junqueira 56 Subconsultas – Retorna um único valor a ser usado com um operador de comparação. – Verifica se os dados atendem ou não as condições especificadas por um operador EXISTS na cláusula WHERE do comando mais externo. • Subconsultas simples: select ID_PEDIDO, DATA, VALOR from PEDIDO where VALOR > (select AVG(VALOR) from PEDIDO); – Recupera a lista de pedidos que tenha o valor maior que a média. BD2 Profa. Rossana Junqueira 57 Subconsultas select ID_CLIENTE, NOME from CLIENTE where CIDADE = (select CIDADE from CLIENTE where NOME = 'Francisco de Assis') and NOME <> 'Francisco de Assis'; – Consulta que recupera os clientes localizados na mesma cidade que o cliente de nome „Francisco de Assis‟. – select nome, endereco from TRANSPORTADORA where CIDADE = (select CIDADE from FABRICA where id_fabrica = 1); – Subconsultas e os comandos mais externos podem se referir a tabelas diferentes. BD2 Profa. Rossana Junqueira 58 Subconsultas – Se a subconsulta puder retornar mais de uma linha o operador a ser utulizado deve ser IN: – select nome, endereco from TRANSPORTADORA where cidade in (select distinct cidade from FABRICA where UF = 'SP'); – Deve-se observar que, ao se utilizar o operador de igualdade, o comando externo espera receber um único valor da subconsulta, Se isso não ocorrer, será retornada uma mensagem de erro. BD2 Profa. Rossana Junqueira 59 Subconsultas – Apesar de os exemplos apresentados anteriormente utilizarem sempre o comando SELECT como comando externo, pode-se aplicar o conceito de subconsultas em outros comandos SQL, tais como o UPDATE: • update empregado set salario = salario * 1.10 where salario < (select avg(salario) from empregado); BD2 Profa. Rossana Junqueira 60 Capítulo 9: Uso de Visões BD2 Profa. Rossana Junqueira 61 Uso de Visões • Uma visão é uma tabela virtual baseada em um subconjunto de linhas de uma ou mais tabelas em um banco de dados. • São usadas para: – Restringir o acesso aos dados apresentando apenas um subconjunto dos dados disponíveis. – Rearranjar e apresentar os dados de uma ou mais tabelas de uma maneira especialmente útil para o programa. – Substituir consultas longas e complexas por algo mais fácil de ser entendido e manipulado. • Diferente de uma tabela, uma visão não armazena dados em um banco de dados. • Quando a visão é criada, sua definição é armazenada no banco de dados. • Quando um programa usa uma visão, o Firebird lê a visão e rapidamente gera uma saída como se fosse uma tabela. BD2 Profa. Rossana Junqueira 62 Uso de Visões • Criação de visões para SELECT: – create view Lista_Pedidos as select PEDIDO.ID_PEDIDO, PEDIDO.DATA, CLIENTE.NOME from PEDIDO, CLIENTE where PEDIDO.ID_CLIENTE = CLIENTE.ID_CLIENTE; – O nome da visão deve ser único dentro do banco de dados. – Podemos definir ou não os nomes das colunas mostradas na visão. – create view Lista_Pedidos2 (Id, Data, Nome) as select PEDIDO.ID_PEDIDO, PEDIDO.DATA, CLIENTE.NOME from PEDIDO, CLIENTE where PEDIDO.ID_CLIENTE = CLIENTE.ID_CLIENTE; BD2 Profa. Rossana Junqueira 63 Uso de Visões – Após a criação da visão é possível definir uma consulta exatamente como se definem consultas em tabelas, por exemplo: • select * from LISTA_PEDIDOS; • Exclusão de visões: – drop view LISTA_PEDIDOS; BD2 Profa. Rossana Junqueira 64 Capítulo 10: Procedimentos Armazenados BD2 Profa. Rossana Junqueira 65 Procedimentos Armazenados • São programas escritos em PSQL. • Esses procedimentos são chamados por aplicações clientes ou, ainda, por outros procedimentos armazenados e gatilhos. • Exemplo: create procedure REAJUSTA_PRECO( pPERCENTUAL numeric(5, 2)) AS begin update PRODUTOCOND set PRECO = PRECO * (1 + :pPERCENTUAL / 100); end BD2 Profa. Rossana Junqueira 66 Procedimentos Armazenados • O cabeçalho do procedimento: – A parte do procedimento ente o CREATE OR ALTER PROCEDURE e o AS é denominada cabeçalho do procedimento. – O cabeçalho contém: • O nome do procedimento armazenado que deve ser único. • Lista opcional de parâmetros de entrada com seus tipos de dados. • O corpo do procedimento: – Parte do procedimento após o AS. – É composto de comandos da linguagem PSQL delimitados por BEGIN e END. BD2 Profa. Rossana Junqueira 67 Procedimentos Armazenados • Exemplos deprocedimentos: – create or alter procedure INCLUI_PROD_CONDICAO (pID_PRODUTO integer, pID_CONDICAO integer, pPRECO numeric(7,2)) as begin insert into PRODUTOCOND values (:pID_PRODUTO, :pID_CONDICAO, :pPRECO); end – Para executar esse procedimento: • execute procedure INCLUI_PROD_CONDICAO (2, 1, 500); Cria um procedimento executável que insere uma linha na tabela PRODUTOCOND BD2 Profa. Rossana Junqueira 68 Procedimentos Armazenados • Exemplos de procedimentos: create procedure REAJUSTA_PRECO( pPERCENTUAL numeric(5, 2)) AS begin update PRODUTOCOND set PRECO = PRECO * (1 + :pPERCENTUAL / 100); end – Para executar esse procedimento: • execute procedure REAJUSTA_PRECO (5); Cria um procedimento executável que atualiza a coluna Preco da tabela PRODUTOCOND no percentual inserido. 69 Procedimentos Armazenados create or alter procedure ATUALIZA_VENDEDOR (pDATA_INICIAL date, pDATA_FINAL date) as declare variable vTOTAL_VENDA numeric(7,2); declare variable vID_VENDEDOR integer; begin update VENDEDOR set TOTAL_VENDA = 0; for select ID_VENDEDOR, VALOR from PEDIDO where DATA between :pDATA_INICIAL and :pDATA_FINAL into :vID_VENDEDOR, :vTOTAL_VENDA do begin update VENDEDOR set TOTAL_VENDA = TOTAL_VENDA + :vTOTAL_VENDA where ID_VENDEDOR = :vID_VENDEDOR; end end Atualiza o campo TOTAL_VENDA da tabela VENDEDOR de um determinado mês definido pelos parâmetros de entrada pDATA_INICIAL e pDATA_FINAL. Para testar: execute procedure ATUALIZA_VENDEDOR ('06/01/2005', '08/01/2005'); 70 Procedimentos Armazenados create or alter procedure ATUALIZA_PRECO (pID_FABRICA integer, pPERCENTUAL numeric(5,2)) as declare variable vID_PRODUTO integer; declare variable vID_FABRICA integer; begin for select ID_PRODUTO, ID_FABRICA from PRODUTO into :vID_PRODUTO, vID_FABRICA do begin if (vID_FABRICA = pID_FABRICA) then update PRODUTOCOND set PRECO = PRECO * (1 + :pPERCENTUAL / 100) where ID_PRODUTO = :vID_PRODUTO; end end Atualiza preços de produtos fornecidos de uma fábrica. Para testar: execute procedure ATUALIZA_PRECO (1,10); Profa. Rossana Junqueira 71 Procedimentos Armazenados create or alter procedure VENDA_CLIENTE (pID_CLIENTE integer, pDATA_INICIAL date, pDATA_FINAL date) returns (pTOTAL_VENDA numeric(7,2)) as declare variable vVALOR_PEDIDO numeric(7,2); begin pTOTAL_VENDA = 0; for select VALOR from PEDIDO where ID_CLIENTE = :pID_CLIENTE and DATA between :pDATA_INICIAL and :pDATA_FINAL into :vVALOR_PEDIDO do begin pTOTAL_VENDA = pTOTAL_VENDA + vVALOR_PEDIDO; end suspend; end Retorna o total de vendas efetuadas a um cliente em um intervalo de datas. O comando SUSPEND é obrigatório em procedimentos de seleção. Para testar: select * from VENDA_CLIENTE (1, '06/01/2005', '10/31/2005'); Profa. Rossana Junqueira 72 Procedimentos Armazenados create or alter procedure ANALISE_CLIENTE (pID_CLIENTE integer) returns (pTOTAL_VENDA numeric(7,2), pMIN_VENDA numeric(7,2), pMAX_VENDA numeric(7,2), pAVG_VENDA numeric(7,2)) as begin select sum(VALOR), min(VALOR), max(VALOR), avg(VALOR) from PEDIDO where ID_CLIENTE = :pID_CLIENTE into :pTOTAL_VENDA, :pMIN_VENDA, :pMAX_VENDA, :pAVG_VENDA; suspend; end Mostra a utilização das funções agregadas. Para testar: select * from ANALISE_CLIENTE(1); Profa. Rossana Junqueira BD2 Profa. Rossana Junqueira 73 Procedimentos Armazenados • Vantagens do uso de procedimentos: – Desenho modular: aplicações que acessam o mesmo banco de dados podem compartilhar procedimentos armazenados, eliminando a duplicação de código e reduzindo o tamanho das aplicações. – Manutenção rápida e eficiente: quando um procedimento é atualizado, as alterações são refletidas automaticamente em todas as aplicações que o utilizam sem necessidade de recompilação. – Aumento de performance: os procedimentos são executados pelo servidor e não pelo cliente, o que reduz o tráfego na rede. – Maior segurança: os procedimentos podem acessar dados de tabelas às quais os usuários não têm direito de acesso. BD2 Profa. Rossana Junqueira 74 Procedimentos Armazenados • Alteração e Exclusão de procedimentos: – drop procedure NAME; – Com a implementação de CREATE OR ALTER PROCEDURE, o comando ALTER PROCEDURE perdeu o sentido. BD2 Profa. Rossana Junqueira 75 Capítulo 11: Gatilhos BD2 Profa. Rossana Junqueira 76 Gatilhos • É uma rotinha em PSQL associada a uma tabela ou visão que automaticamente executa uma ação quando uma linha é inserida, alterada ou excluída. • Um gatilho nunca é chamado diretamente. Quando um usuário ou aplicação tenta um INSERT, UPDATE ou DELETE em uma linha de uma tabela, quaisquer gatilhos associados com a tabela e a operação em questão são disparados automaticamente. • É composto de um cabeçalho e um corpo. BD2 Profa. Rossana Junqueira 77 Gatilhos • O cabeçalho contém: – O nome do gatilho. – Um nome de tabela com a qual o gatilho está associado. – Comandos que determinam quando o gatilho dispara. – O estado do gatilho, ACTIVE ou INACTIVE. – O indicador de tempo, BEFORE ou AFTER. • BEFORE especifica que as ações do gatilho são executadas antes da operação. • AFTER determina que as ações são executadas depois da operação. – O indicador de comando especifica a operação SQL que causa o disparo do gatilho: INSERT, UPDATE ou DELETE. Mais de uma operação pode ser especificada no mesmo gatilho. – O indicador de seqüência, POSITION number, especifica a ordem que o gatilho dispara em relação a outros gatilhos na mesma tabela e evento. BD2 Profa. Rossana Junqueira 78 Gatilhos • O corpo contém: – Consiste de uma lista opcional de declarações de variáveis, seus tipos de dados e, opcionalmente, os valores de inicialização, seguida de um bloco de comandos. – Um bloco é composto de comandos em PSQL, delimitados por BEGIN e END. – Adicionalmente, gatilhos incluem: • Variáveis do contexto NEW e OLD, que mantém os valores antigos e novos de uma coluna da tabela que causa o disparo do gatilho. • Variáveis de contexto INSERTING, UPDATING e DELETING, que determinam que operação está ocorrendo. • Geradores, para gerar valores numéricos únicos utilizados principalmente em chaves primárias. BD2 Profa. Rossana Junqueira 79 Gatilhos • Variáveis de contexto NEW e OLD: – A variável de contexto OLD refere-se a valores anteriores ou atuais em uma linha que está sendo atualizada ou excluída na tabela que provoca o disparo do gatilho. – OLD não é usada para inserts. – NEW refere-se a um conjunto novo de valores de uma linha que está sendo inserida ou atualizada. – NEW não é usada para DELETE. – São freqüentemente usadas para comparar os valores de uma coluna antes e depois de ser modificada. 80 Gatilhos create or alter trigger INS_HISTORICO_PRECO for PRODUTOCOND after update as begin if (new.PRECO <> old.PRECO) then insert into HISTORICOPRECO (ID_PRODUTO, ID_CONDICAO, DATA_ALTERACAO, PRECO_ANTERIOR, NOVO_PRECO) values (new.ID_PRODUTO, new.ID_CONDICAO, 'now', old.PRECO, new.PRECO); end O gatilho demonstra a utilização das variáveis de contexto NEW e OLD. Sempre que houver uma alteração do preço na tabela PRODUTOCOND ocorre uma inserção na tabela HISTORICOPRECO. Para testar: update PRODUTOCOND set preco = preco * 1.10; 81 Gatilhos • Variáveis de contexto INSERTING, UPDATINGe DELETING: – Retornam um valor booleano de acordo com a operação que causou o disparo do gatilho. create or alter trigger UDP_VENDA for PEDIDO after insert or delete as begin if (inserting) then update VENDEDOR set TOTAL_VENDA = TOTAL_VENDA + new.VALOR where ID_VENDEDOR = new.ID_VENDEDOR; if (deleting) then update VENDEDOR set TOTAL_VENDA = TOTAL_VENDA - old.VALOR where ID_VENDEDOR = old.ID_VENDEDOR; end A coluna TOTAL_VENDA da tabela VENDEDOR deve ser atualizada sempre que ocorrer uma atualização em VALOR na tabela PEDIDO. Para testar: insert into PEDIDO values (3, 'now', 300.00, 1, 1, 2) BD2 Profa. Rossana Junqueira 82 Gatilhos • Uso de geradores: – É um objeto do banco de dados que é incrementado sempre que a função GEN_ID() é chamada. – A função GEN_ID() pode ser usada em qualquer declaração onde uma variável pode ser usada. – São usados principalmente para assegurar que o valor inserido em uma coluna seja único e seqüencial. – Para criar um gerador: • create generator GEN_ID_VENDEDOR – Ao ser criado é inicializado com zero. – Para inicializá-lo com um valor diferente deve ser usado o comando: • set generator GEN_ID_VENDEDOR to 3 BD2 Profa. Rossana Junqueira 83 Gatilhos • Uso de geradores: create or alter trigger SET_ID_VENDEDOR for VENDEDOR before insert as begin new.ID_VENDEDOR = GEN_ID(GEN_ID_VENDEDOR, 1); end Demonstra o uso de um gerador para inserir uma chave primária na tabela VENDEDOR. Para testar: insert into VENDEDOR (NOME, TOTAL_VENDA) values ('Sebastião', 0) BD2 Profa. Rossana Junqueira 84 Gatilhos • Finalidades dos gatilhos: – Realizar atualizações correlacionadas. – Forçar restrições de dados. – Converter automaticamente dados. – Notificar alterações no banco de dados às aplicações clientes através dos alertadores de eventos. – Executar atualizações de integridade referencial em cascata. BD2 Profa. Rossana Junqueira 85 Gatilhos • Exemplos de gatilhos: create or alter trigger UPD_CLIENTE for CLIENTE before insert as begin new.NOME = UPPER(new.NOME); end Converte os dados de uma coluna para caracteres caixa alta utilizando a função UPPER(). BD2 Profa. Rossana Junqueira 86 Gatilhos • Exclusão de gatilhos: – DROP TRIGGER name; – Se o que se deseja fazer é suspender temporariamente o gatilho, utiliza- se o comando ALTER TRIGGER com a especificação INACTIVE. BD2 Profa. Rossana Junqueira 87 Capítulo 12: Exceções BD2 Profa. Rossana Junqueira 88 Exceções • É uma mensagem de erro que pode ser levantada por um procedimento ou gatilho. • Criadas por: CREATE EXCEPTION • Alteradas com: ALTER EXCEPTION • Excluídas com: DROP EXCEPTION • Quando uma exceção é levantada, há o retorno de uma mensagem de erro ao programa cliente e a execução do procedimento ou gatilho é encerrado. • Exemplo: create exception VALOR_DE_PEDIDO_INVALIDO 'O valor do pedido é menor que zero' BD2 Profa. Rossana Junqueira 89 Exceções • Depois de criada, uma exceção pode ser utilizada em um gatilho ou procedimento armazenado. • Exemplo: create or alter trigger CHK_VALOR_PEDIDO for PEDIDO before insert as begin if (new.VALOR <= 0) then exception VALOR_DE_PEDIDO_INVALIDO; end
Compartilhar