ADMINISTRAÇÃO DE BANCO DE
DADOS
ARTEFATO 03
Indice
EXEMPLOS COM GROUP BY E COM A CLÁUSULA HAVING - TOTALIZANDO DADOS ... 3
GROUP BY ... 3
Cláusula HAVING com GROUP BY ... 5
ENTENDENDO JOINS ... 7
INNER JOIN ... 8
OUTER JOIN ... 9
CROSS JOIN ... 10
EXEMPLO COMPLETO INNER, LEFT, RIGHT E FULL OUTER JOIN ... 11
CROSS JOIN ... 12
INNER JOIN ... 12
LEFT JOIN ... 13
RIGHT JOIN ... 14
FULL OUTER JOIN ... 14
TRIGGERS ... 15
CONCEITOS ... 15
ORIENTAÇÕES BÁSICAS QUANDO ESTIVER USANDO TRIGGER. ... 16
Usos e aplicabilidade dos TRIGGERS ... 17
CRIANDOTRIGGERS ... 17
COMOFUNCIONAMOSTRIGGERS ... 18
TRIGGER INSERT ... 18
TRIGGER DELETE ... 19
DICAS PARA TRABALHARMOS COM TRIGGERS ... 19
TRANSAÇÕES SQL SERVER ... 21
INTRODUÇÃO ... 21
VERIFICANDO ERROS DENTRO DE UMA TRANSAÇÃO ... 22
EXEMPLO DE IMPLEMENTAÇÃO DE TRANSAÇÃO ... 23
DICAS NA UTILIZAÇÃO DE TRANSAÇÕES ... 24
STORED PROCEDURE COM SQL... 25
Exemplos com Group By e com a
cláusula Having - Totalizando Dados
Group by
Para compreendermos melhor o uso do GROUP BY, considere a tabela Produtos
Objetivo I:
obter o número de produtos em estoque, agrupados pelo tipo, para que depois seja feita a soma da quantidade existente em cada um dos grupos. Para isso usamos a função SUM() em conjunto com o GROUP BY, como a instrução a seguir nos mostra:SELECT Tipo, SUM(Quantidade) AS 'Quantidade em
Estoque'
FROM Produtos
GROUP BY Tipo
Objetivo II:
contar o número de produtos em estoque de acordo com os fabricantes disponíveis. Assim como no exemplo anterior, mais agora levando em conta osfabricantes, os produtos devem ser agrupados por eles, para que depois sejam
contabilizados os produtos em relação a essa divisão. Para isso, devemos usar a seguinte instrução:
SELECT Fabricante, SUM(Quantidade) AS 'Quantidade em Estoque'
FROM Produtos
GROUP BY Fabricante
Obtendo
Objetivo III:
somar a quantidade de produtos em estoque de acordo com os tipos e fabricantes disponíveis. Primeiro, será agrupados os produtos de acordo com os tipos e fabricantes, para que depois seja feita a soma de cada um desses grupos (essa é a ordem que o SQL Server faz logicamente, mais nossa instrução segue a ordem inversa). Utilize:SELECT Tipo, Fabricante, SUM(Quantidade) AS 'Quantidade em
Estoque'
FROM Produtos
GROUP BY Tipo, Fabricante
Objetivo IV
: obter o valor total dos produtos em estoque, agrupados por tipo utilizando:SELECT Tipo, SUM(Quantidade * VlUnitario) AS 'Valor do Estoque'
FROM Produtos
GROUP BY Tipo
Obtendo
:
Cláusula HAVING com GROUP BY
A cláusula HAVING determina uma condição de busca para um grupo ou um conjunto de registros, definindo critérios para limitar os resultados obtidos a partir do agrupamento de registros. É importante lembrar que essa cláusula só pode ser usada em parceria
com GROUP BY.
A cláusula GROUP BY pode ser empregada, entre outras finalidades, para agrupar os produtos de acordo com cada tipo existente. Dentro de cada um dos grupos, a
cláusula HAVING pode ser usada para restringir apenas os registros que possuem uma quantidade superior a 200 unidades no estoque, por exemplo.
Obs: O HAVING é diferente do WHERE. O WHERE restringe os resultados
obtidos sempre após o uso da cláusula FROM, ao passo que a cláusula HAVING filtra o retorno do agrupamento.
Para fazermos o filtro conforme especificado acima, devemos usar a seguinte instrução:
SELECT Tipo, SUM(Quantidade) AS 'Quantidade em Estoque' FROM Produtos
GROUP BY Tipo
Obtendo:
Perceba que este tipo de filtragem nos retornou apenas 2 registros, que são os tipos que, somados, ultrapassam o valor de 200 unidades em estoque.
Objetivo V:
agrupar os produtos com base nos tipos e fabricantes disponíveis. Logo após, retornaremos apenas os registros cuja quantidade supera novamente as 200 unidades em estoque. Veja a instrução e o resultado a seguir:SELECT Tipo, Fabricante, SUM(Quantidade) AS 'Quantidade em
Estoque'
FROM Produtos
GROUP BY Tipo, Fabricante
HAVING SUM(Quantidade) > 200
Retornando:
Objetivo VI:
exemplo mais complexo: vamos supor que o agrupamento deverá ser feito pelo Nome. Dentro deste agrupamento, desejamos obter apenas aqueles cuja quantidade novamente supera as 200 unidades em estoque e cujo valor estocado seja igual ou superior a 100 mil. Veja como fazer a seguir, e seu resultado:SELECT Nome, SUM(Quantidade) AS 'Quantidade em
Estoque', SUM(Quantidade * VlUnitario) AS 'Valor em Estoque'
FROM Produtos
GROUP BY Nome
HAVING SUM(Quantidade) > 200 AND SUM (Quantidade * VlUnitario)
>= 10000.00
Retornando:
Entendendo Joins
Joins são a maneira de se ligar as tabelas em uma instrução SQL. Nos exemplos, vou me referir a utilização de joins em instruções SELECT, mas a partir do SQL Server 2000 podemos utilizar os joins tanto nas instruções SELECT como UPDATE e DELETE.
Exercício: Crie as tabelas PEDIDO,
CLIENTE e ITENSPEDIDO, que reproduzem o clássico relacionamento pai-filho:
CREATE TABLE PEDIDO (
PED_COD INT NOT NULL,
PED_DATA SMALLDATETIME NOT NULL , PED_CLI INT NOT NULL
)
CREATE TABLE CLIENTE (
CLI_COD INT NOT NULL,
CLI_NOME VARCHAR(50) NOT NULL )
CREATE TABLE ITENSPEDIDO (
PED_COD INT NOT NULL, ITEN_COD INT NOT NULL,
ITEN_QTD NUMERIC(5,2) NOT NULL )
A tabela PEDIDO possui uma relação de um-para-muitos com a tabela ITENSPEDIDO, através do campo PED_COD. A tabela PEDIDO possui uma relação de um-para-um com a tabela CLIENTE, através do campo CLI_COD.
Inner Join
O Inner Join, que geralmente é a maneira mais utilizada de se retornar dados espalhados entre tabelas, funciona seguindo o princípio de que para os registros retornados de uma tabela, deve haver algum tipo de relação com os registros da outra tabela. No caso da igualdade, somente os registros das duas tabelas, que possuírem o mesmo valor para um determinado campo, são retornados. Exemplo:
SELECT
PED.PED_COD , PED. PED_DATA ,
ITEN.lang=ES-TRAD>ITEN_COD , ITEN. ITEN_QTD
FROM PEDIDO PED , ITENSPEDIDO ITEN WHERE
PED.PED_COD = ITEN.PED_COD
Verifique no exemplo acima que somente os registros que contiverem o mesmo valor para o campo PED_COD nas duas tabelas são retornados. Apesar de existir outra maneira de se fazer o INNER JOIN, eu recomendo a maneira com o WHERE, pois torna o código da instrução mais legível. Em termos de performance, as duas instruções se equivalem. Outra maneira, que retorna os mesmos resultados: SELECT PED.PED_COD , PED. PED_DATA , lang=ES-TRAD>ITEN. ITEN_COD , ITEN. ITEN_QTD
FROM PEDIDO PED INNER JOIN ITENSPEDIDO ITEN ON PED.PED_COD = ITEN.PED_COD </span
Importante: o Inner join permite o uso de outros operadores que não sejam somente o igual (=).
Outer join
O tipo de join chamado Outer join, possui o funcionamento um pouco diferente. Utilizando o Outer join, além de podermos retornar os registros das duas tabelas seguindo alguma relação, ainda podemos retornar registros que não entram nesta relação. Geralmente, este tipo de join pode ser utilizado em duas tabelas. Mas nada impede que se utilize em mais de duas tabelas, como a tabela de cliente ao nosso modelo PEDIDO-ITENS por exemplo.
Pensando na ligação entre duas tabela, foram criados duas opções para se utilizar no Outer Join:
1. Opção LEFT OUTER JOIN, visando aplicar o conceito de Outer Join na tabela que se encontrar à esquerda da relação.
Apesar de não ser recomendado, pode ser substituída pelo operador *= 2. Opção RIGHT OUTER JOIN, visando aplicar o conceito de Outer
Join na tabela que se encontrar à direita da relação.
Apesar de não ser recomendado, pode ser substituída pelo operador =*
Como podemos alterar a ordem na qual colocamos a tabela na instrução, há um equivalência em termos de funcionalidade para estas
opções de Outer Join.
Importante: esta ordem é em relação às tabelas, e não à comparação feita após a palavra chave ON.
Por exemplo, queremos todos os pedidos que tenham relação com a tabela de itens e também os pedidos que não tenham relação com nenhum item:
SELECT PED.PED_COD , PED. PED_DATA , <span lang=ES-TRAD>ITEN. ITEN_COD , ITEN. ITEN_QTD
FROM PEDIDO PED LEFT OUTER JOIN ITENSPEDIDO ITEN ON PED.PED_COD = ITEN.PED_COD </span
Pode ser escrita como:
SELECT
PED.PED_COD , PED. PED_DATA , <span
ITEN. ITEN_QTD
FROM ITENSPEDIDO ITEN RIGHT OUTER JOIN PEDIDO PED ON PED.PED_COD = ITEN.PED_COD </span
Ou ainda: SELECT PED.PED_COD , PED. PED_DATA , <span lang=ES-TRAD>ITEN. ITEN_COD , ITEN. ITEN_QTD
FROM ITENSPEDIDO ITEN , PEDIDO PED
WHERE PED.PED_COD *= ITEN.PED_COD </span
Veja que para os registros em que a relação de igualdade foi encontrada, os campos de ambas as tabelas são retornados corretamente.
Nos registros em que nenhum item de pedido foi encontrado,
é colocado o valor NULL para todos os campos da tabela ITENSPEDIDO.
Cross Join
O Cross Join possui uma funcionalidade completamente diferente dos outros tipos de Join. Ele simplesmente obtém todos os registros de todas as tabelas e faz o produto cartesiano (ou seja, cada registro de uma tabela é relacionado com cada registro da outra tabela), obtendo assim, o número total de registros através da multiplicação do total de registros das tabelas envolvidas no Cross Join.
Exemplo: SELECT PED.PED_COD , PED. PED_DATA , CLI.CLI_COD, CLI.CLI_NOME
FROM PEDIDO PED , CLIENTE CLI
Perceba que no exemplo acima, todos os pedidos se relacionarão com todos os clientes, independendo do valor dos campos CLI_COD e PED_CLI.
Este tipo de Join poderia ser escrito e fazer dois Outer Join, que neste caso seria chamado de Full Outer Join. A partir da versão 2000 do SQL Server, não mais podemos utilizar o operador *=* para fazer Cross Join, pois este operador foi extinto.
Exemplo completo
Inner, left, right e full
outer join
Crie as tabelas
create table carros( marca varchar(100), modelo varchar(100), ano int,
cor varchar(100) )
create table marcas( marca varchar(50), nome varchar(50) )
Insira os dados abaixo
insert into marcas values('VW','Volkswagem') insert into marcas values('Ford','Ford')
insert into marcas values('GM','General Motors') insert into marcas values('Fiat','Fiat')
insert into marcas values('Renault','Renault') insert into marcas values('MB','Mercedes Bens') insert into carros values('VW','Fox',2005,'preto'); insert into carros values('VW','Fox',2008,'preto');
insert into carros values('Ford','Ecosport',2009,'verde'); insert into carros values('Ford','KA',2008,'prata');
insert into carros values('Fiat','Punto',2008,'branco'); insert into carros values('Fiat','Uno',2007,'preto'); insert into carros values('Fiat','Stilo',200,'4prata'); insert into carros values('Fiat','Uno',2005,'prata'); insert into carros values('Fiat','Stilo',2008,'verde'); insert into carros values('Fiat','Uno',2009,'branco'); insert into carros values('Peugeot','207',2010,'prata'); insert into carros values('Peugeot','207',2010,'prata'); insert into carros values('Peugeot','207',2007,'azul'); insert into carros values('Chrysler','300 C',2008,'verde');
Verifique como ficaram as duas tabelas:
select * from marcas select * from carros
Cross Join
A junção cross join irá juntar todos os registros da tabela marcas com todos os registros da tabela carros, formando um produto cartesiano. Veja o exemplo abaixo:
select m.nome, c.modelo
from marcas as m cross join carros as c
Inner Join
A junção inner join irá juntar os registros da tabela marca que tiver um correspondente na tabela carros. Essa correspondência é feita pelos campos marca que está presente nas duas tabelas. Embora não esteja explícito, mas o campo marca seria a chave primária (na tabela marcas) e chave estrangeira (na tabela carros). Veja o exemplo:
select m.nome, c.modelo
from marcas as m inner join carros as c on c.marca = m.marca
Apenas 10 registros satisfazem o inner join. Podemos dizer que 10 carros estão associados a alguma marca, enquanto que os demais não.
Left Join
O left join irá fazer a junção das duas tabelas “dando preferência” aos registros da tabela marcas. Assim, todos os registros da tabela marcas serão mostrados, independente de haver correspondência na tabela carros. Quando não houver correspondência na tabela carros, será mostrado o valor NULL ou nulo. Exemplo:
select m.nome, c.modelo
from marcas as m left join carros as c on c.marca = m.marca
Right Join
A junção right join funciona de forma inversa ao left join. Aplica-se o mesmo conceito, porém, de forma invertida. Com o right join será mostrado todos os carros, mesmo aqueles que não estejam associados a nenhum registro da tabela marcas. Exemplo:
select m.nome, c.modelo
from marcas as m right join carros as c on c.marca = m.marca
207 e 300 C são modelos que estão cadastrados em carros, mas não estão associados a nenhuma marca.
Full Outer Join
A junção full outer join seria o mesmo que left join e right join juntas, ou seja, ela irá mostrar todas as marcas e todos os carros, independente de existir valores
correspondente na tabela oposta. Veja um exemplo:
select m.nome, c.modelo
from marcas as m full outer join carros as c
on c.marca = m.marca
Triggers
CONCEITOS
• Tipo especial de procedimento armazenado, que é executado sempre que há uma tentativa de modificar os dados de uma tabela que é protegida por ele.
• São invocados automaticamente pelos servidores de banco de dados.
• Os procedimentos armazenados são invocados explicitamente enquanto os gatilhos são associados a tabelas particulares e são executados quando existe uma tentativa de modificação dos dados da tabela.
• Gatilhos e regras são associados a tabelas específicas mas enquanto as regras só podem realizar testes simples sobre os dados, os gatilhos podem utilizar todo o poder da SQL, podendo ser utilizados para garantir a integridade referencial. • Podem ser usados para disparar uma seqüência de modificações em diversas
tabelas relacionadas de um Banco de Dados. Dependendo do SGBD relacional, um gatilho pode ter acesso a outras bases de dados pela rede através da chamada de procedimentos remotos.
•
Pode-se também utilizar gatilhos para garantir regras de negócios. Um pedido de cliente pode ser recusado se a sua conta corrente apresentar débito pendente, por exemploAssociados a uma tabela
Os TRIGGERS são definidos em uma tabela específica, que é denominada tabela de TRIGGERS;
Chamados Automaticamente
Quando há uma tentativa de inserir, atualizar ou excluir os dados em uma tabela, e um TRIGGER tiver sido definido na tabela para essa ação específica, ele será executado automaticamente, não podendo nunca ser ignorado.
Não podem ser chamados diretamente
Ao contrário dos procedimentos armazenados do sistema, os disparadores não podem ser chamados diretamente e não passam nem aceitam parâmetros.
É parte de uma transação
O TRIGGER e a instrução que o aciona são tratados como uma única transação, que poderá ser revertida em qualquer ponto do procedimento, caso você queria usar “ROLLBACK”, conceitos que veremos mais a frente.
Orientações básicas quando estiver usando TRIGGER.
- As definições de TRIGGERS podem conter uma instrução “ROLLBACK TRANSACTION”, mesmo que não exista uma instrução explícita de “BEGIN TRANSACTION”;
- Não é uma boa prática utilizar “ROLLBACK TRANSACTION” dentro de seus TRIGGERS, pois isso gerará um retrabalho, afetando muito no desempenho de seu banco de dados, pois toda a consistência deverá ser feita quando uma transação falhar, lembrando que tanto a instrução quanto o TRIGGER formam uma única transação. O mais indicado é validar as informações fora das transações com TRIGGER para então efetuar, evitando que a transação seja desfeita.
Usos e aplicabilidade dos TRIGGERS
• Impor uma integridade de dados mais complexa do que uma restrição CHECK;
• Definir mensagens de erro personalizadas;
• Manter dados desnormalizados;
• Comparar a consistência dos dados – posterior e anterior – de uma instrução
UPDATE;
Os TRIGGERS são usados com enorme eficiência para impor e manter integridade referencial de baixo nível, e não para retornar resultados de consultas. A principal vantagem é que eles podem conter uma lógica de processamento complexa.
Você pode usar TRIGGERS para atualizações e exclusões em cascata através de tabelas relacionadas em um banco de dados, impor integridades mais complexas do que uma restrição CHECK, definir mensagens de erro personalizadas, manter dados desnormalizados e fazer comparações dos momentos anteriores e posteriores a uma transação.
Quando queremos efetuar transações em cascata, por exemplo, um TRIGGER de exclusão na tabela PRODUTO do banco de dados Northwind pode excluir os registros correspondentes em outras tabelas que possuem registros com os mesmos valores de PRODUCTID excluídos para que não haja quebra na integridade, como a dito popular “pai pode não ter filhos, mas filhos sem um pai é raro”.
Você pode utilizar os TRIGGERS para impor integridade referencial: Executando uma ação ou atualizações e exclusões em cascata.
CRIANDO TRIGGERS
COMO FUNCIONAM OS TRIGGERS
TRIGGER INSERTDe acordo com a sua vontade, um TRIGGER por lhe enviar mensagens de erro ou sucesso, de acordo com as transações.
Criamos a tabela
Criamos o TRIGGER INSERT
create trigger tgr_onInsertUsuario on usuarios for insert
as
if (select count(*) from usuarios, inserted where login = inserted.login and senha = inserted.senha = 1 )
RAISERROR(‘PAR LOGIN/SENHA EXISTENTES’, 16, 1) ROLLBACK TRANSACTION
end
TRIGGER DELETE
Quando um registro é acrescentado a tabela temporária DELETED, ele deixa de existir na tabela do banco de dados. Portanto, a tabela DELETED, não apresentará registros em comum com as tabelas do banco de dados;
É alocado espaço na memória para criar a tabela DELETED, que está sempre em cache;
Dicas para trabalharmos com Triggers
• Pode-se ter mais de um Trigger do mesmo tipo para uma mesma tabela. A ordem de execução vai do que foi criado primeira até o último que foi criado.
• O encadeamente de Trigger (um Trigger chamado outro, ou a si mesmo) pode ocorrer, desde que seja habilitada uma opção do servidor. Por padrão esta opção é habilitada.
• Utilize Triggers para colocar sua regra de negócio, mas sem abusar. Para verificações mais simples, procure utilizar outros objetos do Banco de dados, como constraints
• Triggers podem ser utilizados para replicação (cópia dos dados). Por exemplo: a cada vez que o usuário inserir um dados, você insere em outra tabela.
• Triggers não podem ser chamados manualmente como Stored Procedures. Eles são chamados somente pela instrução determinada pelo
tipo do Trigger.
• Existem os novos Trigger INSTEAD OF que funcionam um pouco diferente dos Triggers padrão. Eles substituem a instrução que os disparou.
Transações SQL SERVER
Introdução
• Sequência de operações num sistema de gerência de banco de dados que são tratadas como um bloco único e indivisível (atômico) durante uma recuperação de falhas
• Provê isolamento entre acessos concorrentes na mesma massa de dados.
• Transação é uma unidade lógica de processamento que tem por objetivo preservar a integridade e a consistência dos dados.
• O processamento da transação pode ser executado totalmente ou não. Sintaxe básica
Begin Transaction --Corpo de comando Commit ou Rollback
Onde:
• Begin Transaction: Tag inicial para o inicio de uma transação.
--Corpo de comando: Conjunto de comando a serem executados dentro de uma transação.
• Commit: Comando que confirma o conjunto de comandos
• Rollback: Comando que desfaz todo o processo executado pelo corpo de comandos caso tenha ocorrido algum evento contrario ao desejado. • transaction_name: Nome atribuído à operação.
• @tran_name_variable: É o nome de uma variável definida pelo usuário que contém um nome de transação válido. A variável deve ser declarada com um char,
varchar, nchar, ou o tipo de dados nvarchar. Se mais de 32 caracteres são passados para a variável, apenas os primeiros 32 caracteres serão utilizados; os caracteres restantes serão truncados.
• WITH MARK [ 'description' ]: Especifica que a transação está marcado no registro. A inscrição é uma string que descreve a marca. Uma descrição mais de 128 caracteres são truncados para 128 caracteres antes de serem armazenados na tabela msdb.dbo.logmarkhistory.
Verificando erros dentro de uma transação
No SQL SERVE temos uma função de sistema que faz a identificação de um erro dentro de uma transação chamada de ‘@@ERROR’ função essa que por padrão recebe o valor 0 (zero) caso não ocorra nem um erro , no caso de algum erro ela assume o valor 1 (um). Exemplo
BEGIN TRANSACTION
UPDATE FROM TbContas SET NuSaldo= 10.000 WHERE NuSaldo IF @@ERROR = 0 COMMIT ELSE ROLLBACK END Nomeando transações
Dicas na utilização de transações
1. Mantenha transações curtas, ou seja, não coloque muitas instruções SQL
entre o BEGIN TRANSACTION e o COMMIT. Apesar de outros usuários enxergarem os dados de uma transação que ainda não fez o COMMIT, os dados ficam gravados no SQL dentro do arquivo de Transaction Log (geralmente com a
extensão .ldf)
2. Vamos supor que alguém resolva utilizar a mesma tomada do computador servidor em que o SQL Server está rodando para ligar uma cafeiteira , quando uma transação foi iniciada mais ainda não fez o COMMIT. Neste
caso , quando o serviço do SQL Server foi reiniciado , todas as transações que ainda não executaram o COMMIT voltarão ao seu estado inicial antes do BEGIN
TRANSACTION.
3. Procure sempre nomear as transações.Isso obriga o utilização do TRANSACTION no COMMIT e no ROLLBACK. Exemplo: BEGIN TRANSACTION TRAN_01 DELETE FROM TABELA1 IF @@ERROR <> 0
ROLLBACK TRANSACTION TRAN_01 ELSE
COMMIT TRANSACTION TRAN_01
4) Encadeamento de transações são permitidos. Para ver em que nível de transação você está , utilize a variável @@TRANCOUNT
Stored Procedure com SQL
Coleção de instruções implementadas com linguagem T-SQL (Transact-Sql, no SQL Server 2000/2005), que, uma vez armazenadas ou salvas, ficam dentro do servidor de forma pré-compilada, aguardando que um usuário do banco de dados faça sua execução. oferecem suporte a variáveis declaradas pelo próprio usuário, uso de expressões
condicionais, de laço e muitos outros recursos
