AT-03 SQL HEngholmJr

ADMINISTRAÇÃO DE BANCO DE
DADOS
ARTEFATO 03
AT03 – Diversos II
Page 1 of 25
Documento: Adm BD / SQL
SytCom Training 2014. All rights reserved
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
CRIANDO TRIGGERS .............................................................................................................. 17
COMO FUNCIONAM OS TRIGGERS ...................................................................................... 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
VANTAGENS ................................................................................................................................ 25
Page 2 of 25
Documento: Adm BD / SQL
SytCom Training 2014. All rights reserved
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
Obtendo:
Page 3 of 25
Documento: Adm BD / SQL
SytCom Training 2014. All rights reserved
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 os
fabricantes, 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
Obtendo:
Page 4 of 25
Documento: Adm BD / SQL
SytCom Training 2014. All rights reserved
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
HAVING SUM(Quantidade) > 200
Page 5 of 25
Documento: Adm BD / SQL
SytCom Training 2014. All rights reserved
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
as 200 unidades em estoque. Veja a instrução e o resultado a seguir:
novamente
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
Page 6 of 25
Documento: Adm BD / SQL
SytCom Training 2014. All rights reserved
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.
Page 7 of 25
Documento: Adm BD / SQL
SytCom Training 2014. All rights reserved
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 (=).
Page 8 of 25
Documento: Adm BD / SQL
SytCom Training 2014. All rights reserved
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
nada impede que se utilize em mais de duas
tabela de cliente ao nosso modelo PEDIDO-ITENS por exemplo.
duas tabelas. Mas
tabelas, como a
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
lang=ES-TRAD>ITEN. ITEN_COD ,
Page 9 of 25
Documento: Adm BD / SQL
SytCom Training 2014. All rights reserved
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.
Page 10 of 25
Documento: Adm BD / SQL
SytCom Training 2014. All rights reserved
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');
Page 11 of 25
Documento: Adm BD / SQL
SytCom Training 2014. All rights reserved
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:
Page 12 of 25
Documento: Adm BD / SQL
SytCom Training 2014. All rights reserved
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
Page 13 of 25
Documento: Adm BD / SQL
SytCom Training 2014. All rights reserved
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
Page 14 of 25
Documento: Adm BD / SQL
SytCom Training 2014. All rights reserved
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
exemplo
Associados a uma tabela
Os TRIGGERS são definidos em uma tabela específica, que é denominada tabela de
TRIGGERS;
Page 15 of 25
Documento: Adm BD / SQL
SytCom Training 2014. All rights reserved
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.
Page 16 of 25
Documento: Adm BD / SQL
SytCom Training 2014. All rights reserved
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
As TRIGGERS são criadas utilizando a instrução CREATE TRIGGER
Page 17 of 25
Documento: Adm BD / SQL
SytCom Training 2014. All rights reserved
COMO FUNCIONAM OS TRIGGERS
TRIGGER INSERT
De 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
Mensagem disparada pelo TRIGGER
Page 18 of 25
Documento: Adm BD / SQL
SytCom Training 2014. All rights reserved
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
Page 19 of 25
Documento: Adm BD / SQL
SytCom Training 2014. All rights reserved
•
•
•
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.
Page 20 of 25
Documento: Adm BD / SQL
SytCom Training 2014. All rights reserved
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.
Page 21 of 25
Documento: Adm BD / SQL
SytCom Training 2014. All rights reserved
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
Page 22 of 25
Documento: Adm BD / SQL
SytCom Training 2014. All rights reserved
Exemplo de implementação de transação
Page 23 of 25
Documento: Adm BD / SQL
SytCom Training 2014. All rights reserved
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
Page 24 of 25
Documento: Adm BD / SQL
SytCom Training 2014. All rights reserved
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
Vantagens
Modularidade:
passamos a ter o procedimento divido das outras partes do
software, bastante alterarmos somente às suas operações para que se tenha as
modificações por toda a aplicação;
·
Diminuição de I/O: uma vez que é passado parâmetros para o
servidor, chamando o procedimento armazenado, as operações se desenolam usando
processamento do servidor e no final deste, é retornado ou não os resultados de uma
transação, sendo assim, não há um tráfego imenso e rotineiro de dados pela rede;
·
Rapidez na execução:
·
Segurança de dados:
os stored procedures, após salvos no servidor,
ficam somente aguardando, já em uma posição da memória cache, serem chamados para
executarem uma operação, ou seja, como estão pré-compilados, as ações também já
estão pré-carregadas, dependendo somente dos valores dos parâmetros. Após a primeira
execução, elas se tornam ainda mais rápidas;
podemos também, ocultar a complexidade do
banco de dados para usuários, deixando que sejam acessados somente dados pertinentes
ao tipo de permissão atribuida ao usuário ou mesmo declarando se o Stored Procedure é
proprietário ou público, podendo ser também criptografada com WITH ENCRYPTION
Page 25 of 25
Documento: Adm BD / SQL
SytCom Training 2014. All rights reserved