Autor Arquivo: rodrigosaito

Bando de Dados II – Aula 15A

Simulado para a prova de Banco de dados II, podendo ser baixado no Github, através do seguinte endereço:

https://github.com/rodrigoksaito/anchieta

 

Anúncios

Banco de Dados II – Aula 15

BACKUP

  • Um membro do role de servidor sysadmin pode fazer backup de qualquer banco de dados da instancia e os membros do role de banco de dados db_owner podem fazer backup de seus banco de dados.
  • Voce também pode adicionar um usuário no role de banco de dados fixo db_backupoperator para permitir ao usuário fazer backup de um banco de dados, enquanto impede qualquer outro acesso ao banco de dados.

TIPOS DE BACKUP

  • O SQL Server apresenta quatro diferentes tipos de backup :
    • Completo;
    • Diferencial;
    • Log de Transação;
    • Grupo de arquivos (filegroups)

BACKUP COMPLETO

  • Um backup completo captura todas as páginas que contêm dados dentro de um banco de dados.
  • As páginas que não contém dados não são incluídas no backup.
  • Portanto, um backup nunca é maior (e, a maioria dos casos, é menor) do que o banco de dados para o qual é criado.
  • Um backup completo é a base para a recuperação de um banco de dados e deve existir antes que você possa usar um backup diferencial ou de log de transação;
  • Como é mais comum fazer backup do que restaurar um banco de dados, o mecanismo de backup é otimizado para o processo de backup.

BACKUP

  • Quando um backup é iniciado, o mecanismo de backup captura as páginas dos arquivos de dados o mais rapidamente possível, sem considerar a ordem das páginas, vários threads podem ser usados para gravar páginas em seu dispositivo de backup.
  • Um backup pode ser executado concomitantemente com outras operações do banco de dados.
  • Como pode ser feita alterações no banco de dados enquanto um backup está em execução, o SQL Server precisa acomodá-las ao passo que também garante que os backups sejam consistentes para propósitos de restauração.
  • O fator limitante para a velocidade de um backup é o desempenho do dispositivo onde o backup está sendo gravado.
  • As únicas operações que não são permitidas durante um backup completo são: adicionar ou remover um arquivo do banco de dados e reduzir um banco de dados;
  • Os dois únicos parâmetros exigidos para um backup são o nome do banco de dados e o dispositivo de backup.
  • Quando você especifica um dispositivo de disco para backup, um diretório e um nome de arquivo podem ser especificados;
  • Se não for especificado um diretório, o SQL Server fará o backup no disco e gravará o arquivo no diretório padrão configurado na instancia;
  • Embora a maioria dos backups seja gravada em um único arquivo em disco ou em um único dispositivo em fita, você pode especificar até 64 dispositivos de backup.
  • Quando você especifica mais de um dispositivo de backup, o SQL Server divide o striped backup (backup em faixas) por todos os dispositivos especificados.
  • Quando o SQL Server coloca um striped backup em vários dispositivos, é obrigatório que todos os dispositivos restaurem com sucesso.
  • O SQL Server não fornece failover ou tolerância à falhas dentro do conjunto de faixas. O conjunto de faixas é utilizado estritamente para propósitos de desempenho.

EXEMPLO DE STRIPED BACKUP

--striped backup

use master
go 

--Em Windows
BACKUP DATABASE AdventureWorks2012
  TO DISK = 'C:\temp\AWorks2012_1.bak',
  DISK = 'C:\emp\AWoks2012_2.bak'
GO

--Em Linux
BACKUP DATABASE [AdventureWorks2016CTP3]
  TO DISK = '/var/opt/mssql/AdvWorks2016_1.bak',
  DISK = '/var/opt/mssql/AdvWorks2016_2.bak'
GO

MIRROR TO

  • A cláusula MIRROR TO oferece um recurso interno para criar até quatro cópias de um backup em uma única operação.
  • Quando você inclui a cláusula MIRROR TO, o SQL Server recupera a página uma vez do banco de dados e grava uma cópia da página em cada espelho de backup.
  • Durante a restauração, os espelhos tem um pequeno número de requisitos:
    • Todos os dispositivos de backup devem ser o mesmo tipo de mídia;
    • Cada espelho deve ter o mesmo número de dispositivos de backup;
    • WITH FORMAT deve ser especificado no comando de backup;

DIMINUINDO TEMPOS DE BACKUP

  • A sobrecarga da compactação sempre vale a pena.
  • O tempo economizado por um backup compactado supera em muito a sobrecarga associada à operação de compactação.
  • O SQL Server tem uma opção de configuração chamada padrão de compactação de backup (backup compression default)
  • Você pode configurar de modo a sempre ter backups compactados, independente de especificar a compactação explicitamente, porém está disponível somente no SQL Server Enterprise.
  • Um único dispositivo de backup pode conter vários backups
  • As opções INIT/NOINIT de um comando BACKUP controlam se um arquivo de backup existente é sobrescrito ou recebe anexação.
  • Quando você especifica NOINIT e está fazendo backup em um arquivo já existente, o SQL Server anexa o novo backup no final do arquivo.
  • Se você especifica INIT e o arquivo já existe, o SQL Server sobrescreve o arquivo com o conteúdo do novo backup.
  • Quando CHECKSUM é especificado, o SQL Server confere a soma de verificação da página, se ela existir, antes de gravar a página do backup.
  • Além disso, é calculada uma soma de verificação para o backup inteiro, a qual pode ser usada para determinar se o backup foi corrompido.

BACKUP DE LOG DE TRANSAÇÃO

  • Cada alteração física feita em um banco de dados tem uma entrada inserida no log de transação.
  • Cada linha recebe um número exclusivo internamente, chamado LSN (Log Sequence Number).
  • O LSN é um numero inteiro que começa do 0 quando o banco de dados é criado e é incrementado até o infinito
  • Um LSN nunca é reutilizado para um banco de dados e é sempre incrementado.
  • Basicamente, um LSN fornece um número em sequencia para cada alteração feita em um banco de dados.
  • O conteúdo de um log de transação é dividido em 2 partes básicas – uma ativa e uma inativa.
  • A parte inativa contém todas as alterações efetivadas no banco de dados.
  • A parte ativa contém todas as transações ainda não efetivadas.Quando um backup de log de transação é executado, o SQL Server inicia no menor LSN do log de transação e começa a gravar cada registro de log de transação sucessivo no backup.
  • Assim que o SQL Server atinge o primeiro LSN que ainda não foi efetivado (isto é, a transação aberta mais antiga), o backup de log de transação termina.
  • Então, a parte do log de transação que teve o backup feito é removida, permitindo que o espaço seja reutilizado.
  • Com base no número de sequencia é possível restaurar um backup de log de transação após o outro para recuperar um banco de dados em qualquer ponto no tempo, simplesmente seguindo o encadeamento de transações, conforme identificado pelo LSN.
  • Como os backups de log de transação se destinam a ser restaurados um após o outro, as constraints sobre eles dependem de se ter a sequencia de LSNs inteira intacta.
  • Um backup de log de transação reúne todas as transações efetivadas no log desde o último backup de log de transação.
  • Conteúdo, como um backup de log de transações contém apenas as transações que foram executadas no banco de dados, você precisa de um ponto de partida para o encadeamento de logs de transação.
  • Antes de executar um backup de log de transação, você deve fazer um backup completo.
  • Após o primeiro backup você pode restaurar o banco de dados em qualquer ponto no tempo, desde o encadeamento de logs de transação não seja interrompido.
  • Backups completos adicionais podem ser criados para se ter um ponto de partida mais recente para uma operação de restauração.
  • Independente do número de backups completos criados, desde que você não tenha introduzido uma lacuna no encadeamento de LSNs, pode começar com qualquer backup completo e restaurar qualquer log de transação desse ponto em diante para recuperar um banco de dados.

SINTAXE DO BACKUP DE LOG DE TRANSAÇÃO

Sem título6

BACKUP DIFERENCIAIS

  • Um backup diferencial captura todas as extensões que mudaram desde o último backup completo.
  • O principal objetivo de um backup diferencial é reduzir o número de backups de log de transação que precisam ser restaurados.
  • Um backup diferencial precisa ser aplicado em um backup completo e não pode existir até que um backup completo seja criado.
  • O SQL Server controla cada extensão que foi alterada após um backup completo usando uma página especial no cabeçalho de um banco de dados, chamado DCM (Differential Change Map – mapa de alteração diferencial)
  • Um backup completo zera o conteúdo do DCM.
  • Quando são feitas alterações em extensões dentro do banco de dados, o SQL Server ajusta para 1 o bit correspondente à extensão.
  • Quando um backup diferencial é executado, o SQL Server lê o conteúdo do DCM para localizar todas as extensões que foram alteradas desde o último backup completo.
  • Um backup diferencial não é o mesmo que um backup de log de transação. Um backup diferencial contém todas as páginas alteradas desde o último backup completo.

OPÇÃO COPY ONLY

  • Uma das opções que podem ser especificadas para qualquer tipo de backup é a COPY_ONLY.
  • Cada backup executado em um banco de dados tem um efeito no ponto de partida de uma recuperação e em quais backups podem ser usados.
  • Os backups diferenciais contém todas as extensões que mudaram desde o ultimo backup completo; portanto, todo backup completo executado altera o ponto de partida em que um backup diferencial é baseado.
  • Quando um backup de log de transação é executado, as transações que tiveram o backup feito são removidas do log de transação.
  • Ocasionalmente você precisa gerar um backup para criar um banco de dados para um ambiente de desenvolvimento ou teste.
  • Voce quer ter o conjunto de dados mais recente, mas não quer afetar o conjunto de backup do ambiente de produção.
  • A opção COPY_ONLY permite gerar um backup que pode ser usado para criar o ambiente de desenvolvimento ou teste, mas não afeta o estado do banco de dados nem o conjunto de backups que está em produção.
  • Um backup completo com a opção COPY_ONLY não reinicia a página do mapa de alterações de diferencial e, portanto, não tem nenhum impacto nos backups diferenciais.
  • Um backup de log de transação com a opção COPY_ONLY não remove transações de log de transação.

BACKUP DE GRUPOS DE ARQUIVOS (FILE GROUPS)

  • Embora os backups completos capturem todas as páginas usadas no banco de dados inteiro, o backup completo de um banco de dados grande pode consumir uma quantidade significativa de espaço e tempo.
  • Se você precisa reduzir o tempo de backup, pode contar com o recurso de backups de grupo de arquivos, que permite ter como alvo parte de um banco de dados.
  • Backups de grupo de arquivos podem ser usados em conjunto com os backups diferenciais e de log de transação para recuperar uma parte do banco de dados no caso de uma falha.
  • Além disso, desde que você não precise restaurar o grupo de arquivos principal e esteja usando o SQL Server Enterprise, o banco de dados poderá permanecer online e acessível para os aplicativos durante a operação de restauração.
  • Somente a parte do banco de dados que está sendo restaurada fica offiline.

BACKUPS PARCIAIS

  • Os grupos de arquivo podem ser marcados como somente leitura.
  • Um grupo de arquivos somente leitura não pode ter alterações feitas nos objetos nele armazenados.
  • Como os objetivo dos backups é capturar as alterações para que você possa reconstruir um banco de dados no estado mais atual durante uma operação de recuperação, o backup de grupos de arquivo que não podem mudar consome espaço de forma desnecessária dentro do backup.
  • Para reduzir o tamanho de um backup para apenas os grupos de arquivo que podem mudar, você pode fazer um backup parcial. Os backups parciais são feitos especificando se a opção READ_WRITE_FILEGROUPS

Sem título7

  • Quando um backup parcial é executado, o SQL Server faz o backup do grupo de arquivos principal, de todos os grupos de arquivo de leitura/gravação e de todos os grupos de arquivos somente leitura especificados explicitamente.

CORRUPÇÃO DE PÁGINA

  • A partir do SQL Server 2005, houve a introdução da capacidade de colocar páginas corrompidas em quarentena, enquanto permitia ao banco de dados permanecer online.
  • Executando-se o comando abaixo, o SQL Server detecta páginas corrompidas e as coloca em quarentena.

Sem título8

  • Geralmente a corrupção de páginas são causadas por componentes de hardware que falham, especialmente controladoras e unidades de disco.
  • Antes de uma falha, as controladoras ou unidades de disco podem corromper páginas de dados realizando gravações incompletas;
  • Quando  o SQL Server grava uma página no disco, é calculada uma soma de verificação para a página.
  • Quando você ativa a verificação de página, sempre que uma página é lida do disco o SQL Server calcula uma nova soma de verificação e a compara com a soma de verificação armazenada na página.
  • Se as somas de verificação não corresponderem, o SQL Server retornará um erro e registrará a página em uma tabela no banco de dados msdb;
  • Embora páginas corrompidas possam ser colocadas em quarentena, o SQL Server tem um mecanismo de proteção em vigor para proteger seu banco de dados de uma corrupção generalizada.
  • Há um limite de 1.000 páginas corrompidas no total em um banco de dados.
  • Quando você atinge o limite de páginas corrompidas, o SQL Server tira o banco de dados do ar e coloca em um estado suspeito para protege-lo de danos maiores;

VALIDANDO BACKUP

  • Como os backups são sua apólice de seguro para um banco de dados, você precisa garantir que os backups criados sejam válidos e utilizáveis.
  • Para validar um backup, execute o seguinte comando:

Sem título9

  • Quando um backup é validado, o SQL Server realiza as seguintes verificações:
    • Calcula uma soma de verificação para o backup e compara com a soma de verificação armazenada no arquivo de backup;
    • Verifica se o cabeçalho do backup está gravado corretamente e é válido;
    • Percorre o encadeamento de páginas para certificar-se de que todas as páginas estejam no banco de dados e passam ser localizadas;

EXEMPLOS (MIRROR E COMPACTAÇÃO)

--backup com mirror e compactado

USE master
GO

--Em Windows
BACKUP DATABASE AdventureWorks2012
  TO DISK = 'c:\temp\AWorks_B1.bak'
  MIRROR TO DISK = 'c:\temp\AWorks_B2.bak'
  WITH COMPRESSION, INIT, FORMAT, CHECKSUM, STOP_ON_ERROR


--Em Linux
BACKUP DATABASE [AdventureWorks2016CTP3]
  TO DISK = '/var/opt/mssql/AdvWorks2016_compress.bak'
  MIRROR TO DISK = '/var/opt/mssql/AdvWorks2016_compress2.bak'
  WITH COMPRESSION, INIT, FORMAT, CHECKSUM, STOP_ON_ERROR

EXEMPLOS (BACKUP DE LOG DE TRANSAÇÃO)

--backups de transacao

USE AdventureWorks2012
GO

INSERT INTO HumanResources.Department (Name, GroupName)
VALUES ('TESTE1','Research and Development')
GO

BACKUP LOG AdventureWorks2012
TO DISK = 'c:\temp\AdventureWorks2012_1.trn'
WITH COMPRESSION, INIT, CHECKSUM, STOP_ON_ERROR
GO

--alteracao e backup de log novamente
INSERT INTO HumanResources.Department (Name, GroupName)
VALUES ('TESTE2','Research and Development')
GO

BACKUP LOG AdventureWorks2012
TO DISK = 'c:\temp\AdventureWorks2012_2.trn'
WITH COMPRESSION, INIT, CHECKSUM, STOP_ON_ERROR
GO

EXEMPLOS (BACKUP DIFERENCIAL)

--backup diferencial

USE AdventureWorks2012
GO

INSERT INTO HumanResources.Department (Name, GroupName)
VALUES ('TESTE3','Research and Development')
GO

BACKUP DATABASE AdventureWorks2012
TO DISK = 'c:\temp\AdventureWorks2012_1.dif'
MIRROR TO DISK = 'c:\temp\AdventureWorks2012_2.dif'
WITH DIFFERENTIAL, COMPRESSION, INIT, FORMAT, CHECKSUM, STOP_ON_ERROR
GO

EXEMPLOS (UTILIZANDO DISPOSITIVOS)

–criando um dispositivo de backup. mesmo que o caminho nao exista, ele vai
–criar,pois esta referenciando um dispositivo e nao um arquivo fisico

--teste de caminho inválido

exec sp_addumpdevice 'disk','meudispositivo','c:\a\backup.bak'
go


--caminho válido
exec sp_addumpdevice 'disk','meudispositivo','c:\temp\backup.bak'
go

-- excluindo um dispositivo
exec sp_dropdevice 'meudispositivo'
go

--até agora, foi apenas criado o dispositivo, mas nao o backup em si
backup database AdventureWorks2012 to meudispositivo
go

--para restaurar o banco de dados através do dispositivo
restore database AdventureWorks2012 from meudispositivo
go

--imaginando que seu banco corrompeu e voce nao consegue dar
-- drop no banco de dados
restore database AdventureWorks from meudispositivo with replace

--verificar sempre o modo de compatibilidade de restaurancao do bd
--FULL
--Simple - nao deixa fazer restore de um log
--Bulk log - somente guarda as transacoes do tipo bulk
-- o restore precisa ser compatível com o backup

--criando 2 dispositivos, um para backups full e outro para backup de log
exec sp_addumpdevice 'disk', 'meufull', 'c:\temp\MeuFull.bak'
go

exec sp_addumpdevice 'disk', 'meulog', 'c:\temp\MeuLog.bak'
go

--backup com with init, para nao deixar 2 arquivos no mesmo dispositivo
backup database AdventureWorks2012 to meufull with init

--backup do log
backup log AdventureWorks2012 to meulog
go

 
--simulando mais movimentacao no bd
use AdventureWorks
go

select * from Person.AddressType
go
insert into Person.AddressType (name, rowguid) 
values ('teste1',NewID())
go

 
--voltando ao banco master
use master
go

--backup do log, somente do log
backup log AdventureWorks2012 to meulog
go

 
--demonstrar em ambliente gráfico os arquivos dentro de um dispositivo

--restaurando o backup full e seus logs
--fez o restore somente do full, nao utilizando os logs

restore database AdventureWorks2012 from meufull
go

--fazendo backups novamente com init

backup database AdventureWorks2012 to meufull with init
go

backup log AdventureWorks2012 to meulog with init
go

--esperando pelos logs, em modo restoring
restore database AdventureWorks2012 from meufull with norecovery
Go

--restaurando o primeiro arquivo do log
restore log AdventureWorks2012 from meulog with file=1, recovery
go

-- exemplo de backup diferencial
exec sp_addumpdevice 'disk', 'meudiff', 'c:\temp\meudiff.bak'
go

backup database AdventureWorks2012 to meudiff with differential
go

Video 01

Banco de Dados II – Aula 14

CONCORRÊNCIA DE TRANSAÇÕES

  • Transação é a forma por um grupo, lote, ou uma série de alterações em uma fonte de dados seja todas atualizadas com sucesso.
  • Caso uma delas venha a falhar, todos as alterações são desfeitas.
  • Para preserver a consistência dos dados envolvidos no lote de atualizações, o SQL Server mantém bloqueios nas linhas que estão sendo atualizadas.
  • O período de tempo que estes bloqueios são mantidos é conhecido como escopo de transação (transaction scope).
  • Uma forma de garantir a concorrência do banco de dados é garantir que os escopos de transação sejam matidos o menor tempo possível, permitindo o SQL Server liberar os bloqueios que estão sendo segurados e permitir que outros usuários acessem as linhas que foram modificadas.
  • As transações são uma forma de garantir as propriedade de atomicidade, consistência, isolamento e durabilidade (ACID) de um banco de dados. (pág. 468)

GERENCIAMENTO DE LOCK DO SQL SERVER

  • SQL Server utiliza, um mecanismo de bloqueio dinâmico cooperativo para proteger a consistência dos dados na base de dados.
  • Os muitos objetos que o SQL Server pode bloquear estão listadas na Tabela seguinte:

Sem título1

  • SQL Server irá travar o número mínimo de recursos necessários para atingir o seu objetivo;
  • Isto é conhecido como bloqueio de multi-granular.
  • Devido a este esquema, o SQL Server não deve apenas assumir os bloqueios que ele necessita (como um bloqueio em uma linha de uma página), mas também deve anunciar a sua intenção de níveis mais elevados.
  • Um exemplo pode ajudar a tornar isso claro.
  • Duas transações, T1 e T2, estão cada um tentando modificar diferentes linhas.
  • Para modificar uma linha, o SQL Server deve ter bloqueios exclusivos na linha que pretende modificar.
  • Se o SQL Server levou apenas o bloqueio exclusivo para T1 em R1, em seguida, T2 pediu um bloqueio em R2, o SQL Server teria que examinar a estrutura da árvore para determinar se essa linha pode ser bloqueado.
  • Isso seria muito custoso (processamento), por isso, SQL Server anuncia a sua intenção de níveis mais altos da árvore, colocando bloqueios de intenção acima da linha e navegar na árvore até a raiz.
  • Como você pode ver na figura seguinte, ao examinar os Locks, a intenção do SQL Server pode determinar rapidamente que há uma intenção de bloqueio exclusivo sobre a tabela, mas a linha de T1 está em uma parte separada da árvore, de modo que SQL Server podem agora adquirir o Lock que requer T2.

Sem título2

  • O SQL Server tem os seguintes bloqueios:

Sem título3

  • Os bloqueios de intenção pode ser considerado como um modificador para os outros modos de bloqueio.
  • Você colocaria um bloqueio compartilhado (S) em uma linha e ler essa linha, e um bloqueio de intenção compartilhado (IS) seriam colocados ao longo da navegação da raiz da B-Tree.

COMPATIBILIDADES DE LOCK

  • Embora alguns tipos de bloqueio impedem de outras transações adquirirem bloqueios no mesmo recurso, muitos bloqueios são compatíveis uns com os outros.
  • Uma tabela de compatibilidade de bloqueio abreviado é mostrada na Tabela 10-4.
  • Lendo este quadro pode ser confuso, por isso um exemplo vai ajudar.
  • Transação T1 atualmente detém um bloqueio de atualização (U) em uma linha.
  • Transação T2 concede um bloqueio compartilhado nessa linha.
  • Ao examinar a matriz, você pode ver que T2 será concedido o bloqueio compartilhado.
  • Com base na discussão anterior sobre bloqueios exclusivos, você pode perguntar por que um bloqueio IX atualmente concedido seria compatível com o pedido de um outro bloqueio IX.
  • Isso ocorre porque os bloqueios de intenção nunca são realizadas no nível do recurso real (como uma linha); eles são, pelo menos, a um nível acima na árvore. (Pág. 471)

Sem título4

  • SQL Server usa o gerenciamento de bloqueio dinâmico para aumentar a concorrência no banco de dados.
  • Por exemplo, se sua transação modifica uma linha em uma tabela, o SQL Server irá fazer um bloqueio de linha para a duração da modificação.
  • No entanto, em grandes tabelas em que seriam necessários muitos bloqueios de linha, o SQL Server, ao contrário, terá uma página ou a tabela de bloqueio.
  • Esta é dependente de todas as operações que levam bloqueios em uma tabela.
  • Se o otimizador de consulta decide que um grande número de linhas serão afetadas, os bloqueios de nível superior podem ser tomadas no início da transação.
  • No entanto, o SQL Server também pode escalar dinamicamente os tipos de bloqueio durante a execução.
  • Se o número total de bloqueios de linha tomadas por consultas por 100 usuários excede o limite, o SQL Server terá um bloqueio de tabela e, em seguida, irá liberar todos os bloqueios de leitura.
  • Isso simplifica o gerenciamento dos bloqueios, mas resulta em diminuição de simultaneidade. (Pág. 472)

NOTE TUNING TIP

Microsoft best practices specify that all tables should have a clustered index created because queries against heaps generally lead to table locks (because all leaf pages have to be scanned for results that match the query predicate) and reduce concurrency. (Pág 472)

NÍVEIS DE ISOLAMENTO DA TRANSAÇÃO

  • A American Nations Standards Institute (ANSI) especifica quatro níveis de isolamento de transação, cada um dos quais tem características muito específicas. As características dos níveis de isolamento são as seguintes:
  • Dirty read – Uma leitura suja ocorre quando uma transação tem permissão para ler dados de uma linha que foi modificada por outra transação em execução que ainda não foi comitada.
  • Non-repeatable read – A leitura não repetitiva ocorre quando, durante o curso de uma transação, uma linha é recuperada duas vezes e os valores dentro da linha diferem entre as leituras.
  • Phantom read – A leitura fantasma ocorre quando, no curso de uma operação, duas consultas idênticas são executadas, e a coleção de linhas retornadas pela segundo consulta é diferente da retornada pela primeira.

Sem título5

  • Os níveis de isolamento da transação são:

READ UNCOMMITTED – Os dados que não foram efetivados podem ser lidos. Embora um bloqueio exclusive ainda obstrua outro bloqueio exclusívo, as operações de leitura ignoram um bloqueio exclusívo.

READ COMMITTED  (Padrão do SQL Server) – Um bloqueio exclusívo obstrui bloqueios compartilhados, assim como bloqueios exclusívos. Os bloqueios compartilhados serão liberados assim que os dados forem lidos.

REPEATABLE READ – Os bloqueios exclusívos obstruem os bloqueios compartilhados e exclusívos. Os bloqueios compartilhados obstruem os bloqueios exclusívos. Os bloqueios compatilhados são mantidos pela duração da transação.

READ SERIALIZABLE – Igual a todas as constraints do nível de isolamento Repeatable Read. Além disso, voce não pode inserir uma nova linha dentro do intervalo do conjunto de chaves (keyset) correntemente bloqueado pela transação. Bloqueios são mantidos pela duração da transação.

SNAPSHOT – Usa o recurso de controle de versão de linha para impeder que bloqueis compartilhados e exclusívos obstruam uns aos outros, enquanto mantém a consistência dos dados. Uma operação de leitura recupera dados da versão da linha antes de iniciar uma operação de modificação de dados.

EXEMPLOS DOS NÍVEIS DE ISOLAMENTO

–Verifica nivel de isolamento corrente

DBCC USEROPTIONS
GO

--Criando um banco de dados
CREATE DATABASE IL
GO

USE IL
GO

--Criacao da tabela 1
CREATE TABLE tbIsolationLevel
(
Id INT IDENTITY,
Col1 varchar(10),
Col2 varchar(10),
Col3 varchar(10)
)
GO

--Inserindo dados na tabela 1
INSERT INTO tbIsolationLevel(Col1,Col2,Col3)
VALUES ('AAAAA','BBBBB','CCCCC')
GO 5

--Criacao da tabela 2
CREATE TABLE tbDeadlock ( id INT)
GO

--Inserindo dados de tabela 2
INSERT INTO tbDeadlock(ID) values (1),(2)
GO

READ COMMITED

/*********  RODAR NA CONEXÃO 1 *********/

BEGIN TRAN
UPDATE tbIsolationLevel set Col1 = 'DDDDD' where id = 1
WAITFOR DELAY '00:00:10'
ROLLBACK TRAN



/*********  RODAR NA CONEXÃO 2 *********/
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * FROM tbIsolationLevel

READ UNCOMMITED

/*********  RODAR NA CONEXÃO 1 *********/

BEGIN TRAN
UPDATE tbIsolationLevel set Col1 = 'DDDDD' where id = 1
WAITFOR DELAY '00:00:10'
ROLLBACK TRAN

/*********  RODAR NA CONEXÃO 2 *********/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM tbIsolationLevel

-- Usando o hint NOLOCK
SELECT * FROM tbIsolationLevel with (nolock)

REPETEATABLE READ

/*********  RODAR NA CONEXÃO 1 *********/
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM tbIsolationLevel
WAITFOR DELAY '00:00:10'
SELECT * FROM tbIsolationLevel
ROLLBACK TRAN

/*********  RODAR NA CONEXÃO 2 *********/
UPDATE tbIsolationLevel set Col1 = 'DDDDD' where id = 1
--select * from tbIsolationLevel
INSERT INTO tbIsolationLevel(Col1,Col2,Col3)
VALUES ('DDDDD','EEEEE','FFFFF')

SNAPSHOT ISOLATION LEVEL

  • Em Snapshot Isolation, as versões de linha atualizados para cada transação são mantidos em TempDB. Uma vez que a transação foi iniciada, ignora todas as linhas mais recentes inseridos ou atualizados na tabela.
USE AdventureWorks2012
GO

--Aciona permissão de uso do SnapShot no banco
/
ALTER DATABASE AdventureWorks2012
SET ALLOW_SNAPSHOT_ISOLATION ON
GO

SELECT ModifiedDate
FROM HumanResources.Shift
GO

-- Session 1
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
UPDATE HumanResources.Shift
SET ModifiedDate = GETDATE()
GO

-- Session 2
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
SELECT ModifiedDate
FROM HumanResources.Shift
GO

-- Session 1
COMMIT

-- Session 2
SELECT ModifiedDate
FROM HumanResources.Shift
GO

--AINDA OS DADOS NAO FORAM ALTERADOS
-- Session 2
COMMIT

SELECT ModifiedDate
FROM HumanResources.Shift
GO

SNAPSHOT (INSTANTÂNEOS)

  • O recurso Database SnapShot foi introduzido no SQL Server 2005 para oferecer aos usuários um método para criar cópias somente leitura de dados rapidamente.
  • Esse recurso somente está disponível no SQL Server Enterprise.
  • O DataBase SnapShot não é compatível com FILE STREAM.

RECURSO DE CRIAÇÃO DE SNAPSHOTS

  • A criação de um snapshot de banco de dados é muito parecida com a criação de qualquer banco de dados.
  • Para criar um snapshot de banco de dados, você usa o comando CREATE DATABASE com a cláusula AS SNAPSHOT OF.
  • Como um snapshot é somente um cópia de leitura de dados de um banco de dados em um ponto no tempo, você não especifica um log de transação;

REQUISITOS PARA CRIAÇÃO DE SNAPSHOT

  • Voce deve incluir uma entrada para cada arquivo de dados especificado no banco de dados de origem;
  • O nome lógico de cada arquivo deve corresponder exatamente ao nome no banco de dados de origem;

RESTRIÇÕES PARA UM SNAPSHOT

  • Voce não pode fazer backup, restaurar ou remover um snapshot de banco de dados;
  • O snapshot de banco de dados deve existir na mesma instância do banco de dados de origem;
  • Índices full text não são suportados;
  • FILESTREAM não é suportado e quaisquer dados FILESTREAM serão inacessíveis por meio do snapshot de banco de dados;
  • Voce não pode criar um snapshot de banco de dados em um banco de dados de sistema;
  • Voce não pode eliminar, restaurar ou remover um banco de dados de origem que tenha um snapshot de banco de dados;
  • Você não pode fazer referencia a grupos de arquivos que estejam off-line, extintos ou sem restauração;
  • Para que um aplicativo acessa o snapshot é da mesma forma que acessasse um banco de dados normalmente;
  • No momento da criação do snapshot, o banco de dados não contém dados. Quando uma instrução SELECT é executada, o SQL Server usa o banco de dados de origem para recuperar dados que não mudaram desde que você criou o snapshot;

REVERTENDO DADOS DE UM SNAPSHOT

  • É possível fazer o retorno dos dados até o momento da criação do snapshot.
  • Em casos extremos, você pode usar o snapshot para retornar todo o conteúdo do banco de dados de origem para o estado do snapshot.
  • Por exemplo, se você precisar descartar todas as alterações ocorrida dentro do banco de dados desde que o snapshot de banco de dados foi criado;
  • Uma reversão de banco de dados é uma categoria especial de restauração de dados que pode ser executada quando você tiver criado um snapshot;
  • Você pode reverter apenas uma linha ou parte do banco de dados, podendo usar os comandos INSERT, UPDATE, DELETE ou MERGE, ou também reverter um banco de dados por completo.
  • Quando você usa o snapshot para reverter o banco de dados inteiro, o banco de dados de origem volta exatamente para como estava no momento em que o snapshot foi criado.
  • Todas as transações executadas no banco de dados são perdidas.
  • Quando você reverte um banco de dados de origem existem várias restrições:
    •  Pode existir apenas um snapshot de banco de dados para o banco de dados origem;
    • Catálogos full text no banco de dados de origem devem ser eliminados e então recriados, depois que a reversão terminar;
    • Como o log de transação é reconstruído, o encadeamento de logs de transação é desfeito;
    • O banco de dados de origem e o snapshot de banco de dados ficam offline durante o processo de reversão;
    • O banco de dados de origem não pode ser ativado para FileStream;

EXEMPLO DE CRIAÇÃO DE SNAPSHOT

USE master
GO

CREATE DATABASE AdventureWorks2012Snap on
(NAME=N'AdventureWorks2012_Data',
FILENAME=N'c:\temp\AdventureWorks2012.ds')
AS SNAPSHOT OF AdventureWorks2012
GO

use AdventureWorks2012Snap
select * from person.Address

use master
go

--Restaurando o banco de dados do snapshot
RESTORE DATABASE AdventureWorks2012 FROM
DATABASE_SNAPSHOT = 'AdventureWorks2012Snap'

SELECT * FROM AdventureWorks2012.sys.database_files
SELECT * FROM AdventureWorks2012Snap.sys.database_files
SELECT * FROM master.sys.databases

REFERÊNCIAS

https://msdn.microsoft.com/pt-br/library/ms173763.aspxhttp://www.diegonogare.net/2013/01/transaction-isolation-level-voc-est-usando-certo/

BIBLIOGRAFIA BÁSICA

  • HOTEK, M. MCTS Self-Paced Training Kit (Exam 70-432) Microsoft SQL Server 2008 – Implementation and Maintenance. EUA: Microsoft Press, 2009.
  • STANEK, William R. Microsoft SQL Server 2008 – Guia de Bolso do Administrador. Porto Alegre: Bookman, 2010.
  • HOTEK, M. Microsoft SQL Server 2008 – Passo a Passo. Porto Alegre: Bookman, 2010.

Video 01

Video 02

Banco de Dados II – Aula 13

PERMISSÃO PARA MODO DEBUG

ScreenHunter_114 May. 06 23.47

EXECUÇÃO DA PROCEDURE

ScreenHunter_115 May. 06 23.48

PASSOS PARA FAZER O DEBUG

ScreenHunter_116 May. 07 00.23

EXEMPLO DE CÓDIGO

CREATE OR REPLACE PROCEDURE SP_TESTE_DEBUG
  (p_employee_id_inicial IN EMPLOYEES.EMPLOYEE_ID%TYPE,
   p_employee_id_final   IN EMPLOYEES.EMPLOYEE_ID%TYPE)
IS
  v_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE;
  v_last_name   EMPLOYEES.LAST_NAME%TYPE;
  v_hire_date   EMPLOYEES.HIRE_DATE%TYPE;
  v_contador NUMBER;
  
  CURSOR cur_ret_employees is
    SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID BETWEEN p_employee_id_inicial AND p_employee_id_final;
BEGIN
  v_contador := 0;
  
  FOR r_employees IN cur_ret_employees LOOP
    FETCH cur_ret_employees
    INTO v_employee_id, v_last_name, v_hire_date;
    EXIT WHEN cur_ret_employees%NOTFOUND;
    
    v_contador := v_contador +1;
    
    DBMS_OUTPUT.PUT_LINE('Record    : '|| v_contador);
    DBMS_OUTPUT.PUT_LINE('EmployeeId: '||   v_employee_id);
    DBMS_OUTPUT.PUT_LINE('Last Name : ' ||   v_last_name);
    DBMS_OUTPUT.PUT_LINE('Hire Date : ' ||   v_hire_date);
    DBMS_OUTPUT.PUT_LINE('');
  END LOOP;
  EXCEPTION WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error was detected');
END;
/

TESTE DE DEBUG

  • Não crie bloco anônimo para fazer o teste do debug no código;
  • Nos parâmetros, coloque intervalos pequenos somente para ver o resultado;

TRIGGERS (GATILHOS)

  • Trigger é um bloco PL/SQL que é armazenado no banco de dados e executado em resposta de um específico evento;
  • O banco de dados Oracle executa um trigger automaticamente quando é especificada uma ocorrência de uma condição;
  • Uma trigger pode ser definida em uma tabela, view, schema (dono do schema), ou banco de dados (para todos os usuários)Untitled

TIPOS DE EVENTOS DAS TRIGGERS

  • Podemos escrever triggers que disparam em uma das operações que ocorre no banco de dados:
    • DML (insert, update, delete)
    • DDL (create, alter, drop)
    • Operações no banco como SERVERERROR, LOGON, LOGOFF, STARTUP ou SHUTDOWN;

CENÁRIOS DE APLICAÇÕES PARA IMPLEMENTAÇÃO

  • Podemos utilizar triggers em:
    • Segurança
    • Auditoria
    • Integridade de Dados
    • Integridade Referencial
    • Replicação de dados
    • Dados derivados de cálculos
    • Eventos de Logging

DISPONIBILIDADE DOS TIPOS DE TRIGGERS

  • Em DML simples
    • BEFORE
    • AFTER
    • INSTEAD OF

TIPOS DE EVENTOS DA TRIGGER E CORPO

  • Um tipo de evento da trigger determina como as cláusulas da declaração DML irá executar a trigger. As possibilidade são:
    • INSERT
    • UPDATE [OF COLUMN]
    • DELETE
  • O corpo determina a ação é chamada e é um bloco PL/SQL ou a chamada de uma procedure;

CRIANDO TRIGGER DML

ScreenHunter_117 May. 07 01.00

ESPECIFICANDO O TEMPO DE EXECUÇÃO

  • Voce pode especificar o tempo que a trigger irá ser executada em uma das declarações:
    • BEFORE: Executa o corpo da trigger antes do acionamento do evento DML na tabela;
    • AFTER: Executa o corpo da trigger depois do acionamento do evento DML na tabela;
    • INSTEAD OF: Executa o corpo da trigger ao invés do evento disparado (geralmente um comando DML);

SEQUENCIA DE DISPARO DAS TRIGGERS: MANIPULAÇÃO SIMPLES DE LINHA

  • A sequencia de disparo de uma trigger em uma tabela quando um linha é manipulada:

Untitled2

SEQUENCIA DE DISPARO DAS TRIGGERS: MANIPULAÇÃO DE VÁRIAS LINHAS

  • A sequencia de disparo de uma trigger em uma tabela quando são manipuladas várias linhas:

Untitled3

EXEMPLO DA TRIGGER SECURE_EMP

Untitled4

TESTANDO A TRIGGER SECURE_EMP

Untitled5

USANDO PREDICADOS CONDICIONAIS

CREATE OR REPLACE TRIGGER secure_emp BEFORE
  INSERT OR UPDATE OR DELETE ON employees
BEGIN
  IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR
     (TO_CHAR(SYSDATE,'HH24') NOT BETWEEN '08' AND '18') THEN
    IF DELETING THEN RAISE_APPLICATION_ERROR(
       -20502,'Voce pode deletar empregados durante o horario comercial.');
    ELSIF INSERTING THEN RAISE_APPLICATION_ERROR (
       -20500,'Voce pode inserir empregados durante o horario comercial.');
    ELSIF UPDATING ('SALARY') THEN RAISE_APPLICATION_ERROR (
       -20503,'Voce pode atualizar o salary em horario comercial');
    ELSE RAISE_APPLICATION_ERROR(
       -20504,'Voce pode atualizar a tabela empregados durante o horario comercial');
    END IF;
  END IF;
END;

desc employees

insert into employees (EMPLOYEE_ID, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID)
VALUES (207,'TESTE','TESTE',TO_DATE('05/05/2017','MM/DD/YYYY'),'HR_REP')

CRIANDO TRIGGER DE DML DE LINHA

–EXEMPLO 2

CREATE OR REPLACE TRIGGER SALARIO_RESTRITO
  BEFORE INSERT OR UPDATE OF SALARY ON EMPLOYEES
  FOR EACH ROW
BEGIN
  IF NOT (:NEW.job_id IN ('AD_PRES','AD_VP'))
    AND :NEW.salary > 15000 THEN
      RAISE_APPLICATION_ERROR (-20202,
        'Empregado não pode ganhar mais que $15000.');
  END IF;
END;

--teste da chamada
UPDATE employees
SET salary = 15500
WHERE last_name = 'Russell';

USANDO QUALIFICADORES OLD E NEW

  • Quando uma linha é atingida, o motor do run-time PL/SQL cria e popula duas estruturas de dados:
  • OLD: armazena os valores originais do registro processado pela trigger;
  • NEW: contém os novos valores;
  • NEW e OLD tem a mesma estrutura do registro declarado usando %ROWTYPE na tabela a qual a trigger é atachada (criada);

Untitled6

CREATE TABLE AUDIT_EMP (
  USER_NAME        VARCHAR2(30),
  TIME_STAMP       DATE,
  ID               NUMBER(6),
  OLD_LAST_NAME    VARCHAR2(25),
  NEW_LAST_NAME    VARCHAR2(25),
  OLD_TITLE        VARCHAR2(10),
  NEW_TITLE        VARCHAR2(10),
  OLD_SALARY       NUMBER(8,2),
  NEW_SALARY       NUMBER(8,2)
)
/

CREATE OR REPLACE TRIGGER AUDIT_EMP_VALUES
  AFTER DELETE OR INSERT OR UPDATE ON EMPLOYEES
  FOR EACH ROW
BEGIN
  INSERT INTO AUDIT_EMP(USER_NAME, TIME_STAMP, ID,
                        OLD_LAST_NAME, NEW_LAST_NAME,
                        OLD_TITLE,    NEW_TITLE,
                        OLD_SALARY,   NEW_SALARY)
  VALUES (USER, SYSDATE, :OLD.EMPLOYEE_ID,
          :OLD.LAST_NAME, :NEW.LAST_NAME,
          :OLD.JOB_ID, :NEW.JOB_ID,
          :OLD.SALARY, :NEW.SALARY);
END;

--EXMEPLO DE CHAMADA DA FUNÇÃO

INSERT INTO EMPLOYEES (EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY,
EMAIL, HIRE_DATE)
VALUES (999,'TEMP EMP','SA_REP',6000, 'TEMP EMP', TRUNC(SYSDATE));
/

UPDATE EMPLOYEES
SET SALARY = 7000,
LAST_NAME = 'Smith'
WHERE EMPLOYEE_ID = 999;

SELECT *
FROM AUDIT_EMP;

Exercícios

1. Crie uma tabela chamada empregados2 com a mesma estrutura da tabela employees. Faça uma trigger que a medida que a tabela employees for sido preenchida, essa tabela empregados2 deve ter os mesmos dados;

2. Faça uma trigger que verifique se os dados da tabela empregados2 está sendo alterada. Caso o salário esteja sendo alterado, a trigger não deve permitir gravar o registro;

3. Crie uma tabela chamada empregados3. Copie todos os dados que tenham o job_id = ‘IT_PROG’ da tabela employees para essa tabela. Crie uma outra tabela chamada log_empregados3, contendo as estrutura da tabela empregados3, com dados antes e depois de alterações. Faça uma trigger na tabela empregados3 para fazer essa auditoria;

PACKAGES (Pacotes)

  • Package é um objeto de um schema que agrupa logicamente tipos PL/SQL, variáveis e subprogramas;
  • Packages geralmente são divididos em 2 partes:
  • A especificação (spec)
  • O corpo
  • A especificação é uma interface para a Package. Ela declara os tipos, variáveis, constantes, exceções, cursores, e subprogramas que pode ser referenciados do lado de fora da Package;
  • O corpo define as queries de cursores e o código para os subprogramas;
  • Habilita o servidor Oracle a ler múltiplos objetos na memória de uma vez;

VANTAGENS DO USO DAS PACKAGES

  • Modularidade: Encapsulando construtores relacionados;
  • Fácil manutenção: mantendo logicamente funcionalidades relacionadas juntas;
  • Fácil projeto de aplicação: Codificando e compilando a especificação e o corpo separadamente;
  • Escondendo informação:
    • Somente as declarações na Especificação da Package são visíveis e acessíveis para aplicações;
    • Construtores privados no corpo da Package são escondidos e inacessíveis;
    • Toda codificação é escondida no corpo da Package;
  • Adição de funcionalidades: Persistência de variáveis públicas e cursores;
  • Melhor desempenho:
    • Toda a Package é carregada para a memória quando uma Package é referenciada na primeira vez;
    • Há uma única cópia na memória para todos os usuários;
    • A hierárquia de dependência é simplificada;
    • Overloading: Múltiplos subprogramas com o mesmo nome;

COMPONENTES DE UMA PACKAGE PL/SQL

Untitled

VISIBILIDADE INTERNA E EXTERNA DOS COMPONENTES DA PACKAGE

Untitled2

CRIANDO A ESPECIFICAÇÃO DA PACKAGE

Untitled3

  • Variáveis declaradas na especificação da Package são inicializados NULL por padrão;
  • Todos os construtores declarados na especificação da Package são visíveis a usuários que tem privilégios na Package;

CRIANDO A ESPECIFICAÇÃO DA PACKAGE NO SQL DEVELOPER

Untitled4

CRIANDO O CORPO DA PACKAGE NO SQL DEVELOPER

Untitled5

EXEMPLO DE ESPECIFICAÇÃO DA PACKAGE

CREATE OR REPLACE PACKAGE COMM_PKG IS
V_STD_COMM NUMBER := 0.10; --INICIALIZADO COM 0.10
PROCEDURE RESET_COMM(P_NEW_COMM NUMBER);
END COMM_PKG;
/
  • V_STD_COMM é variável pública global inicializada com 0.10;
  • RESET_COMM é procedure publica usada para resetar a comissão padrão baseada em algumas regras de negócio; Será implementadado no corpo da Package;

CRIANDO O CORPO DA PACKAGE

Untitled6

  • Identificadores definidos como privado o corpo da Package e não visível do lado de fora do corpo da package;
  • Todos construtores privados precisam ser declarados antes de eles serem referenciados;
  • Construtores públicos são visíveis para todo o corpo da Package;

EXEMPLO DE CORPO DA PACKAGE

CREATE OR REPLACE PACKAGE BODY COMM_PKG IS
  FUNCTION VALIDATE(P_COMM NUMBER) RETURN BOOLEAN IS
    V_MAX_COMM  EMPLOYEES.COMMISSION_PCT%TYPE;
  BEGIN
    SELECT  MAX(COMMISSION_PCT)
    INTO    V_MAX_COMM
    FROM    EMPLOYEES;
    RETURN (P_COMM BETWEEN 0.0 AND V_MAX_COMM);
  END VALIDATE;

  PROCEDURE RESET_COMM(P_NEW_COMM NUMBER) IS
  BEGIN
    IF VALIDATE(P_NEW_COMM) THEN
      V_STD_COMM := P_NEW_COMM; -- RESETA A VARIAVEL PUBLICA
    ELSE
      RAISE_APPLICATION_ERROR (-20210,'COMISSAO RUIM');
    END IF;
  END RESET_COMM;
END COMM_PKG;
/

CHAMANDO SUBPROGRAMAS DA PACKAGE

EXECUTE COMM_PKG.RESET_COMM(0.15);

Exercícios

  1. Faça uma Package que faça a Insert de funcionários (employees) e Select por ID. Deve conter também uma função que retorne o nome completo do funcionário, passando por parâmetro o ID. Faça os devidos testes de chamadas;
  1. Crie uma Package que faça o Insert, Update, Delete, e Select por ID do Departamento e outra Select pelo Nome do Departamento. Para fazer Inserção e atualização, é necessário verificar se o nome do departamento não exista na tabela. Em caso de um departamento já existente, exiba uma mensagem para ser exibido pelo ambiente chamador. Faça os devidos testes de chamadas;

 

Video 01

Banco de Dados II – Aula 12

MANIPULANDO EXCEÇÕES

  • Quando desenvolvemos procedures que são chamadas de outras procedures, devemos ter ciência dos processamentos e exceções de não processamento nas transações e chamadas de procedures;
  • Quando uma exceção é levantada na chamada de uma procedure, o controle vai imediatamente para a seção de exceção daquele bloco.
  • Uma exceção é considera processada se a exceção fornece um manipulador para a exceção levantada;
  • Quando uma exceção ocorre e é manipulada, o seguinte fluxo de código ocorre:
    • A exceção é erguida/levantada;
    • O controle é transferido para o manipulador de exceção;
    • O bloco é terminado;
    • O bloco/programa chamador continua a executar como se nada tivesse acontecido;

Sem título

EXEMPLO

CREATE OR REPLACE PROCEDURE add_department (p_name   VARCHAR2,
                                            p_mgr    NUMBER,
                                            p_loc    NUMBER) IS
BEGIN
  INSERT INTO departments(department_id,
                          department_name,
                          manager_id,
                          location_id)
  VALUES (departments_seq.NEXTVAL,
          p_name,
          p_mgr,
          p_loc);

  DBMS_OUTPUT.PUT_LINE('Departamento adicionado: ' || p_name);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Erro: adicionando departamento:' || p_name);
END add_department;
/

–CRIANDO UMA PROCEDURE PARA CHAMADA

CREATE OR REPLACE PROCEDURE CREATE_DEPARTMENTS IS
BEGIN
  add_department('Midia',100,1800);
  add_department('Edicao',99,1800);
  add_department('Propaganda',101,1800);
END;
/

–VERIFICANDO DADOS

SELECT * FROM DEPARTMENTS;

SET SERVEROUTPUT ON;

EXECUTE CREATE_DEPARTMENTS;

SELECT EMPLOYEE_ID
FROM   EMPLOYEES
WHERE  EMPLOYEE_ID IN (100,99,101);

ERRO GERADO DA CHAMADA DA PROCEDURE

Sem título2

MANIPULANDO EXCEÇÕES

  • Se uma exceção não é manipulada, então todos as declarações DML na chamada da procedure são feitas ROLLBACK, não efetivando nenhuma alteração em dados;
  • Declarações DML que não são afetadas são declarações que foram executadas antes da chamada do código PL/SQL a qual exceções não são manipuladas;

EXEMPLO

–EXEMPLO DE EXCEÇÕES NAO MANIPULADAS

SET SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE add_department_noex (p_name VARCHAR2,
                                                 p_mgr  NUMBER,
                                                 p_loc  NUMBER ) IS
BEGIN
  INSERT INTO DEPARTMENTS (department_id,      
                           department_name,
                           manager_id,
                           location_id)
  VALUES(DEPARTMENTS_SEQ.NEXTVAL, p_name,
         p_mgr, p_loc);
  DBMS_OUTPUT.PUT_LINE('Departamento adicionado: ' || p_name);
END add_department_noex;
/

–CRIACAO DA PROCEDURE CHAMADORA

CREATE OR REPLACE PROCEDURE create_departments_noex IS
BEGIN
  add_department_noex('Midia 2',100,1800);
  add_department_noex('Edicao 2',99,1800);
  add_department_noex('Propaganda 2',101,1800);
END;

–CHAMADA DA PROCEDURE NOEX

EXECUTE create_departments_noex;

SELECT * FROM DEPARTMENTS;
  • Perceba que neste último exemplo, sem o tratamento de exceção, uma única linha não consistente não deixou que as demais linhas fossem incluídas na tabela;

Sem título3

EXCLUSÃO DE PROCEDURES

  • Para se fazer a exclusão de uma procedure, utilizamos o comando:
    • DROP PROCEDURE
  • Verifique antes na documentação se essa procedure não está sendo utilizada por nenhuma aplicação;
  • Caso você apague uma procedure que é chamada por outros objetos, o ORACLE não irá alertá-lo sobre isso;
  • As procedures ou objetos que são dependentes da procedure apagada ficam em estado “pendentes”;

Sem título4

VISUALIZANDO INFORMAÇÕES DE PROCEDURES UTILIZANDO DICIONARIO DE DADOS DAS VIEWS

  • O código fonte de subprogramas PL/SQL é armazenado no dicionário de dados;
  • Para ver os códigos armazenados, execute select nas declarações seguidas das tabelas:
    • Tabela USER_SOURCE mostra o código PL/SQL do seu dono;
    • Tabela ALL_SOURCE mostra o código a qual você tem direito concedido de EXECUTE pelo dono do subprograma;
DESCRIBE USER_SOURCE;

select * from USER_SOURCE;

DESCRIBE ALL_SOURCE;

select * from ALL_SOURCE;

LOOPS (LAÇOS)

  • Loops são repetições de um determinado trecho de código, ou seja, as instruções são executadas por várias vezes;
  • Em PL/SQL existem três tipos de loops;
    • LOOP BÁSICO (LAÇO);
    • LOOP FOR (PARA);
    • LOOP WHILE (ENQUANTO)

EXEMPLO DE LOOP BÁSICO

–LOOPS BASICO

DESCRIBE DEPARTMENTS;

DECLARE
  v_max_department_id  number;
  v_novo_departamento  departments.department_name%TYPE;
  v_contador           number := 1;
BEGIN
  SELECT MAX(DEPARTMENT_ID)
  INTO       v_max_department_id
  FROM      DEPARTMENTS;
  
  v_novo_departamento := '&NovoDepto';

  LOOP
    v_novo_departamento := v_novo_departamento || v_contador;
    
    INSERT INTO DEPARTMENTS(DEPARTMENT_ID, DEPARTMENT_NAME)
    VALUES (v_max_department_id + v_contador, v_novo_departamento);
    
    v_contador := v_contador + 1;
    
    EXIT WHEN v_contador > 4;
  END LOOP;
END;
/

–VERIFIQUE OS RESULTADOS

select * from departments;

EXEMPLO DE LOOP WHILE

DECLARE
  v_max_department_id  number;
  v_novo_departamento  departments.department_name%TYPE;
  v_contador           number := 1;
BEGIN
  SELECT MAX(DEPARTMENT_ID)
  INTO       v_max_department_id
  FROM      DEPARTMENTS;

  v_novo_departamento := '&NovoDepto';

  WHILE v_contador < 4 LOOP
    v_novo_departamento := v_novo_departamento || v_contador;
    INSERT INTO DEPARTMENTS(DEPARTMENT_ID, DEPARTMENT_NAME)
    VALUES (v_max_department_id + v_contador, v_novo_departamento);
    v_contador := v_contador + 1;
END LOOP;
END;
/

–VERIFIQUE OS RESULTADOS

select * from departments;

EXEMPLO DE LOOP FOR

DECLARE
  v_max_department_id   number;
  v_novo_departamento  departments.department_name%TYPE;
  v_contador            number := 1;
BEGIN
  SELECT MAX(DEPARTMENT_ID)
  INTO       v_max_department_id
  FROM      DEPARTMENTS;

  v_novo_departamento := '&NovoDepto';

  FOR I IN 1..3 LOOP
    v_novo_departamento := v_novo_departamento || v_contador;
    
    INSERT INTO DEPARTMENTS(DEPARTMENT_ID, DEPARTMENT_NAME)
    VALUES (v_max_department_id + v_contador, v_novo_departamento);

    v_contador := v_contador + 1;
  END LOOP;
END;
/

–VERIFIQUE OS RESULTADOS

select * from departments;

INSTRUÇÃO CONTINUE

  • É utilizado dentro de um dos tipos de loops;
  • Faz com que a condição do laço seja avaliada antes de terminar o bloco do loop;
  • Oferece aos programadores a habilidade de transferir o controle para a próxima interação;

EXEMPLO DE CONTINUE

SET SERVEROUTPUT ON;
DECLARE
  v_soma SIMPLE_INTEGER :=0;
BEGIN
  FOR i IN 1..10 LOOP
    v_soma := v_soma + 1;
    DBMS_OUTPUT.PUT_LINE('A soma é: ' || v_soma);
    CONTINUE WHEN i > 6;
    v_soma := v_soma +1;
    DBMS_OUTPUT.PUT_LINE('Sem entrar continue, soma é: ' || v_soma);
  END LOOP;
END;
/

DATATYPE

  • Datatypes são tipo de dados que possuem múltiplos valores, ou seja, pode-se armazenar vários dados em uma única variável, que ao contrário do tipos char, number, date, podem armazenar somente 1 por vez;
  • Podem ser do tipos RECORD e COLLECTION;

RECORDS e COLLECTIONS

  • RECORD é quando queremos armazenar um valor com diferentes tipos de dados em uma única ocorrência naquele momento;
  • COLLECTION é quando queremos armazenar alguns valores do mesmo tipo de dado (record);

Sem título5

  • RECORD contém um ou mais componentes, também conhecidos como campos de qualquer tipo escalar (number, char, date, etc);
  • São similares a estrutura da linguagem C;
  • Trata uma coleção de campos como uma unidade lógica;

ESTRUTURA DE UM RECORD

Sem título6

MANUPILAÇÃO DOS RECORDS

  • Notação de um campo individual:
    • CLI_RECORD.NOME;
  • Atribuindo um valor ao record:
    • CLI_RECORD.NOME := ‘JOAO DA SILVA’;

ATRIBUTO %ROWTYPE

  • Declara uma variável de acordo com uma coleção de colunas de uma tabela uma view;
  • Campos de um RECORD tem que ter mesmos nomes e tipos de colunas de uma tabela ou view;

EXEMPLO DE  %ROWTYPE

SET SERVER_OUTPUT ON;
DECLARE
  TYPE EMP_REC IS RECORD (
  v_sal       number(8),
  v_minsal    number(8) default 1000,
  v_hire_date employees.hire_date%TYPE,
  v_emp1      employees%ROWTYPE);
  v_meurec EMP_REC;
BEGIN
  v_meurec.v_sal := v_meurec.v_minsal + 500;
  v_meurec.v_hire_date := sysdate;

  SELECT  *
  INTO    v_meurec.v_emp1
  FROM    employees
  WHERE   EMPLOYEE_ID = 100;

  DBMS_OUTPUT.PUT_LINE(v_meurec.v_emp1.last_name || ' ' ||
  to_char(v_meurec.v_hire_date) || ' ' ||
  to_char(v_meurec.v_sal));
END;
/

CURSORES

  • Toda instrução SQL que é executada no Servidor Oracle é associado um cursor individual;
  • Cursor implícito é declarado e gerenciado pelo PL/SQL;
  • Cursor explícito é declarado e gerenciado pelo programador;

CURSORES EXPLICITOS

Sem título7

CONTROLANDO CURSORES EXPLÍCITOS

Sem título8

DECLARANDO CURSORES

  • Não incluir INTO na declaração do cursor, pois o carregamento dos valores será feita após o FETCH;
  • Caso queira que seja processado em uma determinada sequencia, utilize ORDER BY;
  • Um cursor pode suportar SELECTs com JOINS;
SET SERVEROUTPUT ON;
DECLARE
  CURSOR c_emp_cursor is
    SELECT  employee_id, last_name
    FROM    employees
    WHERE   department_id = 30;
  v_empno employees.employee_id%TYPE;
  v_lname employees.last_name%TYPE;
BEGIN
  OPEN c_emp_cursor;
  FETCH c_emp_cursor INTO v_empno, v_lname;
  DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_lname);
END;
/

CURSORES COM LOOP

SET SERVEROUTPUT ON;
DECLARE
  CURSOR c_emp_cursor is
    SELECT  employee_id, last_name    
    FROM    employees WHERE   department_id = 30;
    v_empno employees.employee_id%TYPE;
    v_lname employees.last_name%TYPE;
BEGIN
  OPEN c_emp_cursor;
  
  LOOP
    FETCH c_emp_cursor INTO v_empno, v_lname;

    EXIT WHEN c_emp_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_lname);
  END LOOP;

  CLOSE c_emp_cursor ;
END;
/

LOOP FOR COM CURSORES

SET SERVEROUTPUT ON;
DECLARE
  CURSOR c_emp_cursor IS
    SELECT employee_id, last_name FROM employees
    WHERE department_id = 30;
BEGIN
  FOR emp_record IN c_emp_cursor
  LOOP
    DBMS_OUTPUT.PUT_LINE(emp_record.employee_id ||' '||   emp_record.last_name);
  END LOOP;
END;
/

STATUS DE CURSORES

Sem título9

UTILIZAÇÃO DO %ISOPEN

SET SERVEROUTPUT ON;
DECLARE
  CURSOR c_emp_cursor is
    SELECT  employee_id, last_name
    FROM    employees
    WHERE   department_id = 30;
  v_empno employees.employee_id%TYPE;
  v_lname employees.last_name%TYPE;
BEGIN
  IF NOT c_emp_cursor%ISOPEN THEN
    OPEN c_emp_cursor;
  END IF;
  
  LOOP
    FETCH c_emp_cursor INTO v_empno, v_lname;
    EXIT WHEN c_emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_lname);
  END LOOP;
END;
/

UTILIZAÇÃO DO %ROWCOUNT e %NOTFOUND

SET SERVEROUTPUT ON;
DECLARE
  CURSOR c_emp_cursor is
    SELECT  employee_id, last_name FROM    employees
    WHERE   department_id = &cod_departamento;
  v_emp_record c_emp_cursor%ROWTYPE;
BEGIN
  OPEN c_emp_cursor;
  LOOP
    FETCH c_emp_cursor INTO v_emp_record;
    EXIT WHEN c_emp_cursor%ROWCOUNT > 10 OR
    
    c_emp_cursor%NOTFOUND;
    
    DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id
    || ' ' || v_emp_record.last_name);
  END LOOP;
  CLOSE c_emp_cursor;
END;
/

FUNÇÕES

  • É um bloco PL/SQL nomeado que retorna um valor;
  • Pode ser armazenado no banco de dados como um objeto de schema para execuções repetidas;
  • É chamada como parte de uma expressão ou é usada para fornecer um valor de parâmetro para outro subprograma;
  • Pode ser agrupadas em pacotes do PL/SQL;
  • O bloco PL/SQL precisa ter ao menos uma declaração de retorno (RETURN);
  • É criado com o comando CREATE FUNCTION;
  • Apesar de parâmetros OUT e IN OUT serem modos que podem utilizar as funções, não é uma boa prática utilizá-las em funções;
  • No entanto, se você precisa retornar mais de um valor de uma função, considere retornar valores compostos de estrutura de dados como um registro ou tabela PL/SQL;

DIFERENÇAS ENTRE PROCEDURES E FUNÇÕES

PROCEDURES FUNCTIONS
Executa como declaração PL/SQL Chamado com uma parte de uma expressão
Não contém cláusula RETURN no cabeçalho Precisa conter um RETURN no cabeçalho
Pode passar valores (se precisar) usando parâmetros output Precisa retornar um valor simples
Pode conter a declaração RETURN sem um valor Precisa conter ao menos uma declaração RETURN

VISÃO GERAL DA CRIAÇÃO E EXECUÇÃO DE FUNÇÕES

Sem título10

CRIANDO E CHAMANDO UMA FUNÇÃO

–CRIANDO A FUNÇÃO

CREATE OR REPLACE FUNCTION get_sal
  (p_id IN employees.employee_id%TYPE)
RETURN NUMBER IS
  v_sal employees.salary%TYPE := 0;
BEGIN
  SELECT salary
  INTO   v_sal
  FROM   employees
  WHERE  employee_id = p_id;
  
  RETURN v_sal;
END get_sal;
/

–CHAMANDO A FUNÇÃO EM UMA EXPRESSAO
–COM UM VALOR DE PARAMETRO

SET SERVEROUTPUT ON;

EXECUTE dbms_output.put_line(get_sal(100));

DIFERENTES MÉTODOS PARA SE EXECUTAR UMA FUNÇÃO

–EXEMPLO 1

VARIABLE b_salary NUMBER

EXECUTE :b_salary := get_sal(100);

PRINT b_salary;

–EXEMPLO 2

SET SERVEROUTPUT ON

DECLARE
  sal employees.salary%TYPE;
BEGIN
  sal := get_sal(100);
  DBMS_OUTPUT.PUT_LINE('o SALÁRIO É: ' || sal);
END;
/

–EXEMPLO 3

EXECUTE dbms_output.put_line   (get_sal(100));

–EXEMPLO 4

SELECT  JOB_ID, GET_SAL(EMPLOYEE_ID)
FROM    EMPLOYEES;

TABELA DO DICIONÁRIO DE DADOS

  • Podemos utilizar a tabela USER_OBJECTS para identificar os tipos de objetos que o usuário corrente tem acesso;
SELECT   object_name,
         object_type
FROM     user_objects;

EXEMPLO DE FUNÇÃO COM COMPARAÇÕES

CREATE OR REPLACE FUNCTION check_sal
  RETURN Boolean IS
  v_dept_id   employees.department_id%TYPE;
  v_empno     employees.employee_id%TYPE;
  v_sal       employees.salary%TYPE;
  v_avg_sal   employees.salary%TYPE;
BEGIN
  v_empno := 205;
  SELECT  salary, department_id
  INTO    v_sal, v_dept_id,
  FROM    employees
  WHERE   employee_id = v_empno;

  SELECT  avg(salary)
  INTO    v_avg_sal
  FROM    employees
  WHERE   department_id = v_dept_id;

  IF v_sal > v_avg_sal THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;

MOSTRANDO POSSÍVEIS ERROS

–mostrando onde estão os possíveis erros

SHOW ERRORS FUNCTION check_sal

--SERÁ MOSTRADO POSSIVELMENTE

LINE/COL ERROR
-------- ----------------------------------
9/3      PL/SQL: SQL Statement ignored
11/3     PL/SQL: ORA-00936: missing expression

AJUSTE DA FUNÇÃO

–ajuste a seguinte parte da função

SELECT  salary,
        department_id
INTO    v_sal,
        v_dept_id

CHAMADA DA FUNÇÃO

–chamada da função

SET SERVEROUTPUT ON;
BEGIN
  IF (check_sal IS NULL) THEN
  DBMS_OUTPUT.PUT_LINE('A função retornou NULL devido a exceção');
  ELSIF (check_sal) THEN
    DBMS_OUTPUT.PUT_LINE('Salario > média');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Salario < média');
  END IF;
END;
/

PASSANDO PARAMETROS PARA UMA FUNÇÃO

CREATE OR REPLACE FUNCTION check_sal
  (p_empno employees.employee_id% TYPE)
RETURN Boolean IS
  v_dept_id   employees.department_id%TYPE;
  v_sal       employees.salary%TYPE;
  v_avg_sal   employees.salary%TYPE;
BEGIN
  SELECT  salary, department_id
  INTO    v_sal, v_dept_id
  FROM    employees
  WHERE   employee_id = p_empno;
  
  SELECT  avg(salary)
  INTO    v_avg_sal
  FROM    employees
  WHERE   department_id = v_dept_id;

  IF v_sal > v_avg_sal THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;
/

CHAMADA DA FUNÇÃO COM PARAMETROS

SET SERVEROUTPUT ON;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Checando o empregado 205');
  
  IF (check_sal(205) IS NULL) THEN
    DBMS_OUTPUT.PUT_LINE('A função retornou NULL devido   a exceção');
  ELSIF (check_sal(205)) THEN
    DBMS_OUTPUT.PUT_LINE('Salario > média');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Salario < média');   END IF;   DBMS_OUTPUT.PUT_LINE('Checando o empregado 70');      IF (check_sal(70) IS NULL) THEN     DBMS_OUTPUT.PUT_LINE('A função retornou NULL devido     a exceção');   ELSIF (check_sal(70)) THEN     DBMS_OUTPUT.PUT_LINE('Salario > média');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Salario < média');
  END IF;
END;
/

Exercícios

  1. Crie uma função, que passado 2 parâmetros do tipo number, (o valor do primeiro parâmetro deve ser menor que o segundo) retorne o valor do maior salário encontrado. Os parâmetros passados são correspondentes aos códigos dos empregados. Essa função deve tratar erro, caso o primeiro parâmetro seja maior que o segundo parâmetro. No ambiente chamador, mostre o valor encontrado;
  1. Crie uma função, que passado um parâmetro do tipo number e outro do tipo data, que retorne a quantidade de tempo que o empregado está na empresa em anos que o valor deve ser arredondado. Os parâmetros passados são correspondentes ao códigos do empregados e a data de admissão. No ambiente chamador, deve-se chamar a função através de um comando select;
  1. Crie uma função, que passado um parâmetro do tipo number, retorne se o funcionário tem o job_id igual a “IT_PROG” ou “PU_CLERCK”. O parâmetro passado é correspondente ao código do funcionário. No ambiente chamador, deve ser impresso se o funcionário tem ou não tem o job_id igual a “IT_PROG” ou “PU_CLERCK”;
  1. Crie uma função, que passado um parâmetro do tipo number, retorne a quantidade de funcionário que existem em um determinado departamento. O parâmetro passado é correspondente ao departamento. No ambiente chamador, é necessário estar impresso o código do departamento e a quantidade de funcionários;

Vídeo – Parte 1

Vídeo – Parte 2

Vídeo – Parte 3

 

 

 

 

learningdatabase.com.br

Tecnologias em Banco de Dados Relacionais, Modelagem de dados dimencionais, tecnologias SQL Servere e Oracle

Aprendendo Programação

Algorítmos, C, C++,Pascal, Python, R

WikiDBA

by Virendra Yaduvanshi - Microsoft SQL Server Database Architect | Consultant | Blogger | Specialist | DBA | Speaker

Blog - Fabiano Neves Amorim

SELECT * FROM [Coisas Da Minha Cabeça] WHERE dbo.fn_TempoParaPost() < dbo.fn_TempoLivre()

Tércio Costa, Oracle Developer, OCE SQL, ACE Associate

Guia de estudos para certificação ORACLE SQL(1Z0-047, 1Z0-051, 1Z0-061 e 1Z0-071) e PL/SQL(1Z0-144, 1Z0-146 e 1Z0-148)

Strate SQL

Data Adventures with an Architect