SQL Avançado

SQL Avanç
Avançado
Apresentaç
Apresentação
SQL Avanç
Avançado
Leandro Tonietto
[email protected]
www.inf.unisinos.br/~ltonietto
jan-07
SQL Avanç
Avançado
Agenda para 09/12
Introdução Transact-SQL
Elementos de sintaxe
Implementando Views
Conceitos
Criação e modificação
Otimização
SQL Avanç
Avançado
Tipos de linguagem para BD
DDL - Data Definition Language
Definição do BD: criação, alteração e remoção
de campos e tabelas, regras, índices, ...
DML - Data Manipulation Language
Manipulação de dados: consulta, inclusão,
alteração e exclusão
Transaction-SQL
Programação de funções auxiliares
Sobre o instrutor:
Professor no curso de Jogos e Entretenimento
Digital da Unisinos e do Instituto de Informática
da Unisinos
Gerente de Projetos da empresa Qualità
Informática
[email protected]
Expectativa quando ao aproveitamento do
curso
SQL Avanç
Avançado
TransactionTransaction-SQL
O Transact-SQL é uma linguagem de
definição, manipulação e controle de dados.
Padronização SQL-92
ISO e ANSI
Elementos de linguagem de programação
Diretivas, comentários, identificadores, controle
de fluxo, loops, funções, expressões, ...
SQL Avanç
Avançado
Elementos de Linguagem
Diretivas de lotes
Comentários
Identificadores
Tipos de dados
Variáveis
Funções do sistema
Operadores
Expressões
Controle de fluxo
Palavras reservadas
1
SQL Avanç
Avançado
SQL Avanç
Avançado
Diretiva de lotes
Diretiva de lotes
Lote:
GO:
Conjunto de instruções de programação
SQL Server processa um lote de instruções por
vez.
Diretivas de lote:
são instruções para envio dos lotes para
execução do SGBD
Duas instruções: GO e EXEC
SQL Avanç
Avançado
SQL Avanç
Avançado
Diretiva de lotes
Diretiva de lotes
GO:
EXEC:
“Exemplo”:
/* bloco de comandos 1 */
DECLARE @EmpID varchar(11)
GO
/* bloco de comandos 2 */
SET @EmpID = ‘Teste’
Executa comandos do usuário
O escopo da variá
variável
é lote. O lote é
conjunto instruç
instruções
separadas por GO’
GO’s
Serão executados
apenas os lotes
apó
após o comando GO
O que aconteceria se estes blocos de comandos fossem executados?
SQL Avanç
Avançado
Comentá
Comentários
São códigos não executáveis de um lote de
comandos
Geralmente utilizados para:
indicar alguma ponto importante de um código
colocar explicações curtas sobre uma função ou lote
Suprimir execução de linhas de código sem removê-las
Comentário de linha: -Comentário de bloco: /* ... */
Envia lote atual de instruções para execução
O lote atual é composto de todas as instruções
inseridas desde o último comando GO ou desde o início
da sessão ad hoc (ou script, se esse for o primeiro GO).
Os usuários devem seguir as regras referentes a lotes.
Por exemplo, algumas instruções de DDL devem ser
executadas isoladamente de outras instruções
Transact-SQL, separando-as com um comando GO.
Escopo das variáveis é definido dentro de um lote
Comandos TransacSQL
Funções
Lotes
Comandos são passados por parâmetro e o
método retorna o status da execução: sucesso
ou erro.
SQL Avanç
Avançado
Comentá
Comentários
Exemplo 1:
USE northwind
SELECT productname
, (unitsinstock-unitsonorder) -- Calcula inventário
-- , supplierid
-- indica ao interpretador de
FROM products
comando que ignore o estiver à sua
GO
direita, até
até o fim da linha
Suprimindo um linha
2
SQL Avanç
Avançado
Comentá
Comentários
Exemplo 2:
/*
Este código recupera todas as linhas da tabela
products e exibe o preço unitário, o preço unitário
aumentado em 10% e o nome do produto.
*/
USE northwind
SELECT unitprice, (unitprice * 1.1), productname
FROM products
GO
Normalmente os comentá
comentários de blocos são usados para
comentar blocos de linhas de có
código e colocar explicaç
explicações
sobre o que é executado no lote
SQL Avanç
Avançado
Identificadores
Exemplo:
DECLARE @v1 int
SQL Avanç
Avançado
Identificadores
São nomes ou identificação única para variáveis e funções
Os identificadores padrão podem conter de 1 a 128 caracteres,
inclusive letras, símbolos (_, @ ou #) e números.
Não são permitidos espaços.
Regras:
O primeiro caractere deve ser um caractere alfabético, de a-z ou A-Z.
Os identificadores podem incluir letras, números ou os símbolos @, $, #
ou _ .
Os nomes de identificadores que começam com um símbolo têm
utilizações especiais:
Um identificador que inicie com o símbolo @ indica um parâmetro ou variável
local.
Um identificador que inicie com um sinal de tralha (#) indica um procedimento
ou uma tabela temporária.
Um identificador que inicie com um sinal de tralha duplo (##) indica um objeto
temporário global.
Os nomes de objetos temporários não devem ter mais de 116 caracteres,
incluindo os sinais # ou ##, porque o SQL Server atribui um sufixo
numérico interno aos objetos temporários.
SQL Avanç
Avançado
Identificadores delimitados
Os identificadores delimitados podem ser
usados nas seguintes situações:
Quando os nomes contiverem espaços
incorporados
Quando forem utilizadas palavras reservadas
como nomes de objetos ou partes de nomes de
objetos
Os identificadores delimitados devem ser
colocados entre parênteses ou aspas
duplas.
SQL Avanç
Avançado
Convenç
Convenção de Nomes de Identificadores
Mantém um padrão de escrita
Facilita manutenção de código
Diretrizes:
Quando possível, empregue nomes significativos.
Use convenções de nomeação simples e fáceis.
Use um identificador que diferencie tipos de objeto,
principalmente para views e procedimentos
armazenados.
Mantenha a exclusividade dos nomes de objetos e
usuários. Por exemplo, evite criar uma tabela sales
(vendas) e um cargo sales dentro do mesmo banco de
dados.
SQL Avanç
Avançado
Tipos de dados
Os tipos de dados são atributos que
especificam o tipo de informação que pode
ser armazenado em uma coluna, parâmetro
ou variável
Delimita o domínio dos valores possíveis
para uma variável, parâmetro ou coluna
3
SQL Avanç
Avançado
Tipos de dados
Numéricos:
Para representação de números
Inteiros: int, tinyint, smallint e bigint
Decimais: numeric, decimal, money e
smallmoney
Ponto flutuante: float e real
SQL Avanç
Avançado
Tipos de dados
Characters
Este tipo de dados é usado para representar
seqüências ou dados de caractere e inclui tipos de
dados de seqüência de tamanho fixo, como char e
nchar, e de tamanho variável, como varchar e
nvarchar.
Binários
Este tipo de dados é muito semelhante aos tipos de
dados de caractere em termos de armazenamento e
estrutura, porém o conteúdo dos dados é tratado como
uma série de valores de bytes. Os tipos de dados
binários incluem binary e varbinary. O tipo de
dados bit indica um valor de bit único igual a zero ou
um.
SQL Avanç
Avançado
Tipos de dados
Image e Text
Estes tipos de dados são estruturas objeto binário extenso (BLOB)
que representam tipos de dados de tamanho fixo e variável para o
armazenamento de dados binários e de caractere Unicode e nãoUnicode grandes, como image, text e ntext.
Tables
Este tipo de dados pode ser usado apenas para definir variáveis
locais de tipo de tabela ou o valor de retorno de uma função
definida pelo usuário.
Cursors
Este tipo de dados é usado para programação em procedimentos
armazenados e com interfaces de cliente de baixo nível. Ele nunca
é usado como parte de uma instrução de DDL.
SQL Avanç
Avançado
Tipos de dados
Numéricos:
Para representação de números
Inteiros: int, tinyint, smallint e bigint
Decimais: numeric, decimal, money e smallmoney
Ponto flutuante: float e real
Datas:
Representa datas ou períodos de tempo. Os dois tipos
de dados de data são datetime, cuja precisão é de
3,33 milissegundos, e smalldatetime, cuja
precisão é de intervalos de 1 minuto.
SQL Avanç
Avançado
Tipos de dados
Unique Identifiers
Este tipo especial de dados é um
uniqueidentifier que representa um
identificador global exclusivo, o qual é um valor
hexadecimal de 16 bytes que deve ser sempre
exclusivo.
SQL Variantes
Este tipo de dados pode representar valores de
vários tipos de dados para os quais há suporte
no SQL Server, com exceção de text, ntext,
image, timestamp e rowversion.
SQL Avanç
Avançado
Tipos de dados
Definidos pelo usuário
Este tipo de dados é criado pelo administrador
do banco de dados e se baseia nos tipos de
dados do sistema.
Use os tipos de dados definidos pelo usuário
quando várias tabelas devem armazenar o
mesmo tipo de dados em uma coluna, e você
deve garantir que as colunas tenham
exatamente o mesmo tipo de dados, tamanho e
nulidade.
4
SQL Avanç
Avançado
Variá
Variáveis
Variável é um recurso de linguagem de
programação para definição de identificadores
que representam e armazenam um determinado
valor na memória.
Vários locais: DECLARE
Atribuição de valor é feita através do comando
SET ou da execução de uma expressão como um
SELECT
Escopo é do lote. Isto significa que?
Nome da variável deve ser precedido por @
SQL Avanç
Avançado
Variá
Variáveis
Exemplo:
USE northwind
DECLARE @EmpID varchar(11)
,@vlName char(20)
SET @vlname = 'Dodsworth'
SELECT @EmpID = employeeid
FROM employees
WHERE LastName = @vlname
SELECT @EmpID AS EmployeeID
GO
SQL Avanç
Avançado
Funç
Funções do sistema
Tipos de funções:
Retornam um valor individual que opera a partir de nenhum ou de diversos valores
escalares individuais.
As funções escalares podem ser agrupadas nas seguintes categorias:
Configuração - Retorna informações sobre a configuração atual.
Cursor - Retorna informações sobre cursores.
Data e hora - Efetua uma operação sobre um valor inserido de data e hora e retorna um
valor de seqüência, numérico ou de data e hora.
Matemática - Efetua um cálculo com base em valores inseridos como parâmetros para a
função e retorna um valor numérico.
Metadados - Retorna informações sobre o banco de dados e os objetos do banco de
dados.
Segurança - Retorna informações sobre usuários e cargos. Seqüência de caracteres
Efetua uma operação sobre um valor inserido de seqüência (char ou varchar) e retorna
um valor de seqüência de caracteres ou numérico.
Sistema - Efetua operações e retorna informações sobre valores, objetos e configurações
no SQL Server.
Estatística do sistema Retorna informações estatísticas sobre o sistema.
Texto e imagem Efetua uma operação sobre um valor inserido de texto ou imagem ou
sobre uma coluna, e retorna informações sobre o valor.
SQL Avanç
Avançado
Variá
Variáveis
Sintaxe de uso geral:
DECLARE {@<nome> <tipo_de_dados>} [,...n]
SET @nome_da_variável_local = expressão
Exemplos:
DECLARE @nome varchar(70)
DECLARE @soma int
SET @soma 0
SQL Avanç
Avançado
Funç
Funções do sistema
O Transac-SQL permite a execução de funções
pré-definidas do sistema, como:
Sum, avg, sqrt, length, ...
Consultar guia de referência para identificar todas as
funções
Tipos de funções:
Agem sobre um conjunto de dados e retornam alguma
compilação dos mesmos
USE northwind
SELECT AVG(unitprice) AS AvgPrice
FROM products
GO
AVG calcula
média
SQL Avanç
Avançado
Funç
Funções do sistema
Exemplo:
USE northwind
SELECT DB_NAME() AS 'database'
GO
5
SQL Avanç
Avançado
SQL Avanç
Avançado
Funç
Funções do sistema
Tipos de funções:
Podem ser usadas como referências de tabela
em uma instrução Transact-SQL
Exemplo:
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name,
id FROM owner.titles')
GO
Está
Está funç
função funcionará
funcionará sempre?
Funç
Funções do sistema
Função de conversão de data:
SELECT 'ANSI:', CONVERT (varchar(30),
GETDATE(), 102) AS Style
UNION
SELECT 'Japanese:', CONVERT(varchar(30),
GETDATE(), 111)
UNION
SELECT 'European:', CONVERT(varchar(30),
GETDATE(), 113)
Repare que a instruç
instrução SELECT
GO
retorna dados que concatenados
UNION agrupa conjunto de dados
SQL Avanç
Avançado
Funç
Funções do sistema
Resultado:
Style
ANSI: 1998.11.20
Japanese: 11/20/98
European: 20 Nov 1998 16:44:12:857
SQL Avanç
Avançado
Funç
Funções do sistema
Exemplo do uso da função DATEFORMAT:
SET DATEFORMAT dmy
GO
DECLARE @vdate datetime
SET @vdate = '11/29/98'
SELECT @vdate
GO
DATEFORMAT define o formato de
data padrão atual
Repare que a data é impressa (dmy
(dmy))
de forma diferente do padrão ANSI
SQL Avanç
Avançado
Funç
Funções do sistema
Exemplo de função que interage com dados
do SGDB:
USE library
SELECT user_name(), app_name()
GO
SQL Avanç
Avançado
Funç
Funções do sistema
Exemplo de função que obtém dados sobre
o BD:
USE library
SELECT
COLUMNPROPERTY(OBJECT_ID('member'),
'firstname', 'AllowsNull')
GO
6
SQL Avanç
Avançado
Operadores
Operadores são símbolos que efetuam cálculos
matemáticos, concatenações de seqüências de
caracteres e comparações entre colunas,
constantes e variáveis.
É possível combinar e utilizar os operadores em
condições de pesquisa.
A ordem em que os operadores serão
processados baseia-se em uma precedência
predefinida.
Quatro tipos de operadores: aritméticos, de
comparação, de concatenação de seqüências de
caracteres e lógicos
SQL Avanç
Avançado
Operadores
Precedência:
Agrupamento primário: ( )
Aritmético Multiplicativo: *, / e %
Aritmético Aditivo: – e +
Concatenação de seqüências de caracteres: +
Lógico NOT: NOT
Lógico AND: AND
Lógico OR: OR
SQL Avanç
Avançado
Expressões
Exemplo:
USE northwind
SELECT OrderID, ProductID
,(UnitPrice * Quantity) as ExtendedAmount
FROM [Order Details]
WHERE (UnitPrice * Quantity) > 10000
GO
SQL Avanç
Avançado
Operadores
Aritméticos:
Aditivos: + e –
Multiplicativos: *, / e %
Comparação:
Retornam verdadeiro ou falso
=, <, >, <=, >= e <>
Concatenação:
Somente com dados String: +
Lógicos
Utilizados para conectar o resultado de mais de uma expressão
OR, AND e NOT
Usados na cláusula WHERE
SQL Avanç
Avançado
Expressões
Combinação de campos e variáveis com
operadores e funções para retornar um
resultado
Tipo de retorno depende dos elementos da
expressão
SQL Avanç
Avançado
Elementos de Controle de Fluxo
elementos de linguagem que controlam o
fluxo da lógica em uma instrução
a função CASE que permite usar uma lógica
condicional em um único registro de cada
vez, em uma instrução SELECT ou
UPDATE
7
SQL Avanç
Avançado
Elementos de Controle de Fluxo
Exemplo – nível de instrução:
USE northwind
IF EXISTS (SELECT * FROM orders WHERE customerid =
'frank')
PRINT '*** Cliente não pode ser excluído ***'
ELSE
BEGIN
DELETE customers WHERE customerid = 'frank'
PRINT '*** Cliente foi excluído ***'
END
GO
SQL Avanç
Avançado
Views
Definição:
São tabelas que representam uma visão de um
conjunto de dados de uma ou mais tabelas.
Não armazenam nenhum dado, apenas
fornecem a visualização para estes de forma
diferente.
VIEW == SELECT
SQL Avanç
Avançado
Views
Vantagens:
Visualização focalizada
Tirar a complexidade da visualização dos
dados, como por exemplo, uniões de tabelas
restrições sobre os conjuntos
Organização de dados para exportação
Permissão de acesso com restrições
Desempenho, pois o resultado fica
“armezanado”
SQL Avanç
Avançado
Elementos de Controle de Fluxo
Exemplo – nível de registro:
DECLARE @n tinyint
SET @n = 5
IF (@n BETWEEN 4 and 6)
BEGIN
WHILE (@n > 0)
BEGIN
SELECT @n AS 'Número',
CASE
WHEN (@n % 2) = 1
THEN 'ÍMPAR'
ELSE 'PAR'
END AS 'Tipo‘
SET @n = @n - 1
END
END
ELSE
PRINT 'SEM ANÁLISE'
GO
SQL Avanç
Avançado
Views
Exemplos:
Um subconjunto de registros ou colunas de uma tabela
base: subconjunto das pessoas de uma tabela de
pessoas com idade > 30 anos.
Uma união, intersecção ou associação de duas ou mais
tabelas base: Pessoas com idade > 30 e que são do
sexo masculino
Um resumo estatístico de uma tabela base: tabela que
mostra os pedidos com uma coluna totalizando o valor
dos seus itens.
Um subconjunto de outra view ou alguma combinação
de views e tabelas base.
SQL Avanç
Avançado
Views
Sintaxe:
CREATE VIEW propr.nome_da_view [(coluna [,n ])]
[WITH {ENCRYPTION | SCHEMABINDING | VIEW_METADATA}
[,n ]]
AS
instrução_select
[WITH CHECK OPTION]
8
SQL Avanç
Avançado
Views
View no Transact:
USE Northwind
GO
CREATE VIEW dbo.EmployeeView AS
SELECT LastName, Firstname
FROM Employees
SQL Avanç
Avançado
Views
Padronização:
Segue a mesma verificação dos identificadores
Nomes devem ser únicos
É fortemente recomendado fazer distinção
entre padrão de nomes de tabelas e de views:
Tabela: Pessoas
View: View_Pessoas_Masculino
GO
SELECT * from EmployeeView
GO
SQL Avanç
Avançado
Views
Só podem criar views usuários com
permissão de administradores do BD
Definir usuário dba com proprietário de todas
as tabelas
Qualquer usuário com permissões mínimas
no sistema, por padrão, consegue visualizar
os dados de uma view.
SQL Avanç
Avançado
Views
Exemplo 2:
USE Northwind
GO
CREATE VIEW dbo.OrderSubtotalsView (OrderID,
Subtotal)
AS
SELECT OD.OrderID,
SUM(CONVERT
(money,(OD.UnitPrice*Quantity*(1Discount)/100))*100)
FROM [Order Details] OD
GROUP BY OD.OrderID
GO
SQL Avanç
Avançado
Views
Restrições:
A instrução CREATE VIEW não pode incluir as
cláusulas COMPUTE ou COMPUTE BY.
A instrução CREATE VIEW não pode incluir a palavrachave INTO.
A instrução CREATE VIEW só poderá incluir a cláusula
ORDER BY se a palavra-chave TOP for usada.
As views não podem fazer referência a tabelas
temporárias.
As views não podem fazer referência a mais de 1.024
colunas.
A instrução CREATE VIEW não pode ser combinada
com outras instruções Transact-SQL em um único lote.
SQL Avanç
Avançado
Views
Views para fornecer um modo conveniente de examinar informações
de duas ou mais tabelas associadas em um local central:
Neste exemplo, ShipStatusView associa as tabelas Customers
(Clientes) e Orders (Pedidos).
USE Northwind
GO
CREATE VIEW dbo.ShipStatusView
AS
SELECT OrderID, ShippedDate, ContactName
FROM Customers c INNER JOIN Orders o
ON c.CustomerID = O.CustomerID
WHERE RequiredDate < ShippedDate
SELECT * FROM ShipStatusView
OrderID ShippedDate ContactName
GO
SELECT * FROM OrderSubtotalsView
GO
9
SQL Avanç
Avançado
Views
SQL Avanç
Avançado
Views
Encriptação:
Usar a opção WITH ENCRYPTION na criação
da view
Criptografa as entradas da tabela
syscomments, critografando a definição da view
Para descriptografar somente removendo e
criando novamente a view.
Pode ser interessante guardar uma cópia do script
de criação da view em algum lugar (arquivo) seguro
SQL Avanç
Avançado
Views
Exercício:
Crie a tabela Pessoas no transact-SQL, com os
campos nome, idade e sexo
Insira cinco registros nesta tabela; sendo 3
homens e duas mulheres. Três destas pessoas
tem idade maior que 30.
Crie uma view para representar as mulheres
com idade maior que trinta.
Altere os dados e apenas faça mais um
comando de seleção simples na view.
SQL Avanç
Avançado
Views
Atenção:
Se você definir uma view com uma instrução
SELECT * e, depois, alterar a estrutura das
tabelas subjacentes adicionando colunas, as
novas colunas não aparecerão na view.
Quando todas as colunas são selecionadas em
uma instrução CREATE VIEW, a lista de
colunas é interpretada apenas quando você
cria a view pela primeira vez. Para ver as novas
colunas na view, você deve alterá-la.
Exercício:
Criar a view: página ?? Sem a opção de
encriptação
Ver os dados da tabela syscomments
Apagar a view e criar de novo com a opção de
encriptação
Ver novamente os dados da tabela
syscomments
SQL Avanç
Avançado
Views
Alterando de definição da view
Exemplo:
USE Northwind
GO
ALTER VIEW dbo.EmployeeView
AS
SELECT LastName, FirstName, Extension
FROM Employees
SQL Avanç
Avançado
Views
Apagando uma view
DROP VIEW <nome da view>
Apague a view definida no exercício anterior
10
SQL Avanç
Avançado
Views
SQL Avanç
Avançado
Views
Atualização dos dados através das views é
possível considerando as seguintes
restrições:
Que seja proveniente de uma única tabela e
que contenha todos os seu campos não nulos
Não possua funções de agregação
Poderão ocasionar erros se afetarem colunas
às quais a view não faz referência.
SQL Avanç
Avançado
Views
Otimização de desempenho
Armazenamento de dados de consultas
completas
Joins com views é mais rápido do que com várias
tabelas
Evitar aninhamento de views.
Podem esconder problemas com resultados
Exemplo das páginas 49-50
SQL Avanç
Avançado
Procedimentos Armazenados
Práticas recomendadas:
Nomes consistentes e com um padrão que os distinguem das
tabelas; facilitando a identificação das views
Dbo como proprietário: facilita utilização, já que, por ser o usuário
default, não é necessário colocar o nome do usuário proprietário da
view no momento de usá-la.
Verifique dependências dos objetos antes de excluí-los
sp_denpends
Se for necessário esconder a definição de uma view, não apague
esta definição da tabela syscomments; use a opção WITH
ENCRYPTION nos comandos CREATE VIEW ou ALTER VIEW.
Cuidado views aninhadas, tenha certeza de sua real necessidade;
elas podem esconder problemas com resultados.
SQL Avanç
Avançado
Procedimentos Armazenados
Tipos
Armazenados no BD do sistema.
Fornecem informações das tabelas do sistema
Por padrão, precedidos por “sp_”
São funções do sistema
Usados por qualquer BD
Procedimentos de armazenamento local
Stored procedures
Coleção nomeada de instruções TransactSQL
que é armazenada no servidor.
Suporte a recursos de programação
São funções ou procedimentos, logo
encapsulam execução repetida de código
(mesmo código executado em lugares diversos)
SQL Avanç
Avançado
Procedimentos Armazenados
Características
Instruções para executar operações sobre o BD
Permite passagem de parâmetros
Retorno de status da execução
Parâmetros de saída
Uso restrito
Procedimentos temporários locais (#) ou globais (##)
Locais ao usuário ou globais para todas as sessões
Procedimentos externos (construídos como DLL).
Armazenados fora do BD
Prefixo “xp_”
11
SQL Avanç
Avançado
Procedimentos Armazenados
Processamento
Criação
Primeira consulta, coloca o plano de consulta
respectivo no cache.
Cache alocado conforme necessidade
Cache de procedimentos
SQL Avanç
Avançado
Procedimentos Armazenados
Comando de criação: CREATE PROCEDURE
Cria o procedimento e armazena na BD
A criação pode referenciar tabelas, view, variáveis, ...
O que é criado dentro do procedimento não tem vida
externa ao mesmo
Primeiro testar o código e depois realizar o
armazenamento do mesmo
Uma instrução CREATE PROCEDURE não pode
combinada com outras instruções SQL em um único
lote (assim como a VIEW).
Deve ter permissões para CREATE PROCEDURE.
Tamanho máximo de um procedimento é de 128Mb.
SQL Avanç
Avançado
Procedimentos Armazenados
Exemplo 1:
USE Northwind
GO
CREATE PROC dbo.OverdueOrders
AS
SELECT * FROM dbo.Orders WHERE
RequiredDate < GETDATE() AND
ShippedDate IS NULL
GO
SQL Avanç
Avançado
Procedimentos Armazenados
Processo de criação:
Codificação
Verificação sintática.
Em caso erro não será criado nada.
Armazenamento da tabela de sistema
sysobjects e texto na syscomments
Armazenamento é feito dentro da BD atual
SQL Avanç
Avançado
Procedimentos Armazenados
Sintaxe CREATE PROCEDURE
CREATE PROC nome [;número]
[{@parâmetro tipo_dado}
[VARYING][=padrao][OUTPUT]][,...n]
[{RECOMPILE|ENCRYPTION|”ambos”}]
[FOR REPLICATION]
AS
Instrução_sql[...n]
SQL Avanç
Avançado
Procedimentos Armazenados
Diretrizes para criação:
Recomendo que o usuário dbo possua todos os objetos
do BD
Sempre especifique o dbo como proprietário dos
procedimentos, tabelas, views e etc. que você criar
Considere as permissões necessárias em todos os
objetos em que a procedure precisa referenciar.
O ideal é se logar no gerenciador do banco como
administrador, na hora de criar ou alterar objetos do sistema.
Idealmente, uma procedure realiza uma ação
Evitar uso de objetos temporários, por questões de
desempenho.
Prefira sp_executesql do que EXECUTE
12
SQL Avanç
Avançado
Procedimentos Armazenados
Executando um procedimento:
EXEC {[@status retorno=]
{nome_procedimento[;número]|@var_no
me_procedimento}
}
[[@parâmetro={valor|@variável[OUTPUT]
|[DEFAULT]][,...n]
[WITH RECOMPILE]
SQL Avanç
Avançado
Procedimentos Armazenados
Alteração
Altera código de uma procedure existente
Sintaxe é parecida com a CREATE
Ver página 70.
ALTER PROC vai alterar o conteúdo da PROC
dentro da tabela syscomments
Exemplo da página 71
SQL Avanç
Avançado
Procedimentos Armazenados
Usando parâmetros de entrada
Permitem passagem de valores externos para o
procedimento. Generaliza o procedimento.
Comando: CREATE PROC @param tipo
Considere:
Melhor verificar o que está se recebendo, por
exemplo, evitar processar um procedimento com
parâmetro nulo.
Com valor padrão, execução possibilita que não se
passe nenhum valor para um determinado parâmetro
Exemplo da página 76.
SQL Avanç
Avançado
Procedimentos Armazenados
Executando um procedimento:
EXEC OverdueOrders
Possível combinar EXEC dentro de um
INSERT
Inserir dados com o retorno da execução de
uma procedure
Exemplo da página 69.
SQL Avanç
Avançado
Procedimentos Armazenados
Remoção de procedures
Interessante executar comando sp_depends
antes de excluir um registro, para verificar as
dependências.
Sintaxe:
DROP PROCEDURE {nome_proc}
SQL Avanç
Avançado
Procedimentos Armazenados
Considerações sobre o exemplo:
Ordem da declaração difere da ordem de
passagem dos parâmetros
Parâmetros @Region e @Fax não são
especificados, porquê?
No segundo exemplo, página 77, os parâmetros
de passagem não indicam qual parâmetro eles
estão se referenciando, porque eles são
passados na ordem de declaração.
13
SQL Avanç
Avançado
Procedimentos Armazenados
Parâmetros de retorno
Servem para retornar dados processados na
procedure para quem a chamou.
Sintaxe:
CREATE PROC xyz @x1 int OUTPUT
EXEC xyz @idade OUTPUT
Exemplo da página 78.
SQL Avanç
Avançado
Procedimentos Armazenados
Tratamento de erros:
Tratamento de mensagens para informar
sucesso ou falha de um procedimento.
Mensagens tanto para programador, quando
para usuário.
Retorno satisfatório / esclarecedor para quem
invocou a função, de tal forma que este possa
tratar um erro.
Boa prática: verificar situações de erro (RN)
antes de executar uma rotina.
SQL Avanç
Avançado
Procedimentos Armazenados
Tratamento de erros:
USE Northwind
GO
CREATE PROCEDURE dbo.GetOrders
@CustomerID nchar (10)
AS
SELECT OrderID, CustomerID, EmployeeID
FROM [Order Qry]
WHERE CustomerID = @CustomerID
RETURN (@@ROWCOUNT)
GO
SQL Avanç
Avançado
Procedimentos Armazenados
Exercícios:
crie um procedimento que retorne o maior entre
dois números
outro para o menor
outro para retornar a média.
Teste os procedimentos criados...
Calcular fatorial de um número.
SQL Avanç
Avançado
Procedimentos Armazenados
Tratamento de erros:
Instrução RETURN:
Retorna para método anterior
Retorna um valor ou não
Também usada para retornar o status de execução:
normalmente 0 para êxito e valores negativos para
erros.
Valores
Valores
de -1 a -14 estão em uso no SQL Server
de -15 a -99 estão reservados
SQL Avanç
Avançado
Procedimentos Armazenados
Função sp_addmessage
Insere uma mensagem na tabela de
mensagens do BD (sysmessages)
Possibilitando assim, que o usuário que
executou a procedure, identifique o código do
erro que retornou de uma falha de execução da
procedure
14
SQL Avanç
Avançado
Procedimentos Armazenados
Função @@error
Retorna o número do erro que ocorreu na
última execução
Caso não tenha ocorrido erro, retorna zero
Faça o exemplo das página 85-87.
Crie uma mensagem personalizada para cada
caso de erro, adicione na tabela de mensagens
do sistema e mostre o erro gerado, se for o
caso, no lote que executou a procedure.
SQL Avanç
Avançado
Procedimentos Armazenados
Exercício
Fazer pesquisa fonética em uma tabela do BD.
Algoritmo Soundex como procedure.
Este algoritimo foi criado pelo governo Americano para recuperar nomes
para a administração da Seguridade Social nos arquivos Nacionais. O
algoritimo é simples e esta explicado a seguir.
Uma chave de busca é foneticamente igual à algum dado da tabela, se o
código SOUNDEX dela for igual ao código SOUNDEX de um registro do dado
da tabela.
O código soundex é obtido conforme as seguintes regras:
Se o código for maior que quatro caracteres os demais não serão
considerados. Se for menor serão acrescidos Zeros.
Você preserva o primeiro caractere da String e atribui valores conforme a
tabela abaixo até 3 caracteres.
As vogais A,E,I,O,U os caracteres Y,W e H e os demais caracteres não são
considerados.
Os caracteres numéricos são considerados apenas para a primeira casa , os
demais não considerados
Havendo caracteres repetidos somente o primeiro será considerado
SQL Avanç
Avançado
Procedimentos Armazenados
SQL Avanç
Avançado
Procedimentos Armazenados
Função RAISERROR
Gera um erro.
Utilizada quando da verificação da
possibilidade de um erro, lógico, de negócio ou
de sistema.
Pode ser utilizado para evitar o erro do SGBD e
tratar o erro personalizado
Exemplo das páginas 89-90
SQL Avanç
Avançado
Procedimentos Armazenados
Exercício
Código do caractere
Caractere
0
Para palavras curtas
1
B, P, F, V
2
C,S,G,J.K,Q,X,Z
3
D, T
4
L
5
M, N
6
R
Soundex da palavra BROWN
B=B
R=6
O=W=0
N=5
B650
SQL Avanç
Avançado
Funç
Funções do usuá
usuário
Recurso que permite a customização de
funções para o usuário.
Restrições
Devem retornar algum valor (escalar ou tabela)
Aceitam nenhum ou vários parâmetros de
entrada
Não aceitam parâmetros de saída
15
SQL Avanç
Avançado
Funç
Funções do usuá
usuário
Tipos:
Escalares
Funções simples, que processam alguma tarefa e
retornam um escalar.
Com valor de tabela e instruções
Semelhantes a procedimentos
Permitem o uso numa instrução FROM e SELECT
Com valor de tabela in-line
Assemelha-se ao uso de views, porém com suporte
à parâmetros
SQL Avanç
Avançado
Funç
Funções do usuá
usuário
Uso da função em SELECT:
SELECT LastName, City,
dbo.fn_NewRegion(Region) AS Region,
Country
FROM dbo.Employees
SQL Avanç
Avançado
Funç
Funções do usuá
usuário
Criação (CREATE FUNCTION):
Veja sintaxe na página 94 do manual.
GO
CREATE FUNCTION fn_NewRegion (
(@param1 NVARCHAR(30))
RETURNS NVARCHAR(30)
BEGIN
IF @param1 IS NULL
SET @param1 = ‘Não Aplicável’
RETURN @param1
END
SQL Avanç
Avançado
Funç
Funções do usuá
usuário
Alteração:
A alteração da função segue a mesma sintaxe
de estrutura da criação, com exceção da
palavra-chave do comando:
ALTER FUNCTION dbo.fn_NewRegion ...
Exclusão de funções:
Semelhante aos demais objetos:
DROP FUNCTION dbo.fn_NewRegion
SQL Avanç
Avançado
Funç
Funções do usuá
usuário
Fazer exemplos das páginas:
98 funções escalares
99—100 com valor de tabela
101—102 tabela in-line.
Discussão sobre os exemplos:
Quais são as características de cada tipo?
Em qual situação devemos usar qual tipo de função?
SQL Avanç
Avançado
Funç
Funções do usuá
usuário
Boas práticas:
Funções complexas em dados pequenos
Cuidado com o encapsulamento
As funções escondem a complexidade de uma
consulta simples
Prefira funções à procedimentos
Funções in-line são parecidas com view, mas
com uma vantagem: permitem parâmetros
16
SQL Avanç
Avançado
Funç
Funções do usuá
usuário
Exercício:
Converter o procedimento SoundexFunc para uma
função
Criar tabela de Pessoa com os atributos nome e
sobrenome
Escrever testes usando a SoundexFunc para validar o
uso da função
Pergunta:
A comparação usando SoundexFunc é mais lenta que a
comparação direta de nomes, porque precisa converter o nome
para um código antes de pesquisar; mata o índice!! Então qual
seria a solução para utilizar o recurso do soundex sem perda
de índices?
SQL Avanç
Avançado
Disparadores (TRIGGERS)
Uso:
Mais usado para manter integridade
Automatização de operações complexas em
cascata
Customizar verificações e mensagens
Permite trabalhar com estado atual e anterior
de um dado
SQL Avanç
Avançado
Disparadores (TRIGGERS)
Uso:
Customizar verificações e mensagens
Verifica alguma condição especial do sistema e
lança um erro de lógica de negócios apropriado.
Desnormalização
Junção de várias tabelas para consolidação de
dados
Modelo continua íntegro, mas a visualização pode
ser feita conjunta
Permite trabalhar com estado atual e anterior
de um dado
SQL Avanç
Avançado
Disparadores (TRIGGERS)
São um tipo especial de procedimento que
executados sempre que ocorre algum evento.
Por exemplo, pode-se criar uma trigger (ou
disparador) para que, toda vez que seja inserido
um registro em uma determinada tabela, seja
criado algum outro registro de relacionamento.
A execução ocorre automaticamente, quando
ocorre o evento ao qual a trigger está associada.
Inclusive não invocados manualmente
SQL Avanç
Avançado
Disparadores (TRIGGERS)
Uso:
Operações em cascata
Alteração e exclusão em cascata
Automatização de operações complexas de
integridade
Por exemplo, verificar o estado de um atributo
comparando com outras tabelas; caso ele esteja
dentro de uma faixa de valores realiza alguma
operação sobre os dados, caso contrário realiza
outra. Digamos que pedido não pode pedir mais do
que X unidades de um produto que está num limite
mínimo em estoque.
SQL Avanç
Avançado
Disparadores (TRIGGERS)
Considerações:
Podem conter ROLLBACK
Entretanto, se é necessário sair do script da trigger quando é
feito um ROLLBACK, é melhor usar comando RETURN para
sair da execução
Em geral, os disparadores são reativos:
Dado um evento um disparador é executado
Restrições e INSTEAD OF são executados antes do evento
Disparadores podem afetar várias linhas de dados
@@ROWCOUNT
17
SQL Avanç
Avançado
Disparadores (TRIGGERS)
Criação (CREATE TRIGGER)
CREATE TRIGGER nome ON tabela
{FOR | AFTER | INSTEAD OF | }
{INSERT | UPDATE | DELETE}
AS
IF UPDATE(coluna)
...
Instruções
SQL Avanç
Avançado
Disparadores (TRIGGERS)
Alterando uma trigger:
Usar o comando ALTER TRIGGER, cuja sintaxe
é igual a do comando CREATE TRIGGER
Descartando ou removendo uma trigger:
DROP TRIGGER
Desativando / Ativando triggers
ALTER TABLE tabela {ENABLE |
DISABLE} TRIGGER {ALL | nome da
trigger [,...]}
SQL Avanç
Avançado
Disparadores (TRIGGERS)
Funcionamento INSTEAD OF
Pode ser associado a tabelas e views
Não pode ser associado a views com WITH
CHECK OPTION
Executa operações antes da instrução de
atualização
Exemplo das páginas 123—124
SQL Avanç
Avançado
Disparadores (TRIGGERS)
Criação (CREATE TRIGGER):
FOR e AFTER são executados após as ações
INSERT, UPDATE ou DELETE
INSTEAD OF, pelo contrário, cancela a
operação e desvia para execução do código do
disparador
Conferir em casa as instruções que não
podem estar presentes nas triggers (página
114)
Fazer exemplo das páginas 115—116.
SQL Avanç
Avançado
Disparadores (TRIGGERS)
Funcionamento de trigger
Acesso aos dados temporários e log para tomada de
decisões
Insert: tabela inserted
Delete: tabela deleted
Dados removidos das tabelas do BD
Update: registra em ambas as tabelas temporárias:
deleted (os dados anteriores) e inserted (dados novos)
IF UPDATE(coluna) determina se um registro foi alterado ou
não e executar instruções conforme o caso
Exemplo da página 122.
SQL Avanç
Avançado
Disparadores (TRIGGERS)
Disparadores aninhados:
Quando um disparador é executado, o
resultado da sua execução inicia um novo
disparador, que pode iniciar outro e ...
Limite de 32 disparadores aninhados
sp_configure habilita / desabilita
aninhamento de triggers
sp_configure (‘nested triggers’, 0)
Aninhamento cíclio não é permitido / executado
Atenção para aninhamento com dados
complexos
18
SQL Avanç
Avançado
Disparadores (TRIGGERS)
Ativando recursividade
Recursividade é capacidade de um método em
chamar ele mesmo para executar novamente,
sob novas condições, até um ponto de parada.
Ativação:
ALTER DATABASE ClassNorthwind SET
RECURSIVE_TRIGGERS ON
Recursividade pode ser direta ou indireta
(cíclica)
SQL Avanç
Avançado
Disparadores (TRIGGERS)
Exercícios:
Páginas 131—132
Criar uma tabela pessoa com os campos: código, nome e
sobrenome.
Criar uma trigger que, quando um registro é inserido na tabela
pessoa, ela, automaticamente, insere um registro equivalente na
tabela PessoaSoundex com os dados de pessoa, passando pela
função soundexTest. A tabela PessoaSoundex contém os
seguintes campos: codigo, nomex, sobrenomex.
Faça um procedimento que receba como parâmetro o nome de
uma pessoa, e faça a pesquisa pelo nome soundex de uma
pessoa, fazendo uso das tabelas acima. Lógica:
Converter o parâmetro para um código soundex
Procurar na tabela PessoaSoundex os registro com códigos
equivalentes, mas retornar os dados equivalentes na tabela Pessoa
SQL Avanç
Avançado
SQL Avanç
Avançado
Índices
Índices
Uso
Melhorar o desempenho de consultas,
agilizando (indexando) o acesso a dados
Criação de índices
CREATE INDEX
Remoção de índices
DROP INDEX
Índices automáticos:
PRIMARY KEY e UNIQUE
Armazenados na tabela sysindexes
Um índice funciona atribuindo uma chave de
busca a um dado
O situação é: apenas um valor de chave para um dado
(1 : 1)
Pode acontecer de uma chave retornar mais de um
dado.
Quando um índice de agrupamento é criado,
todos os índices sem agrupamento são recriados
Sintaxe e exemplo na página 135
SQL Avanç
Avançado
SQL Avanç
Avançado
Índices
Índices
Índices agrupamento:
CLUSTERED
Exemplo da página 139, mostra índices
duplicados
Sem agrupamento:
NONCLUSTERED
Índices compostos:
União mais de um campo
Por exemplo, não permitir duplicidade de registros com
valores de outras tabelas associadas
Digamos uma tabela de relacionamento entre Pessoa e
uma tabela de endereço. É possível colocar uma
restrição com índice para que uma pessoa não dois
vínculos para um mesmo endereço.
Exemplo da página 141.
Exercício: criar a situação exposta acima (pessoa x
endereço)
19