Júlio Battisti Livros e Cursosa sua sala de aula na internet

Criar Views com Janela de Execução de Comandos T-SQL é simplesmente uma questão de utilizar o comando CREATE VIEW. Vamos estudar este comando neste item.

A sintaxe para o comando CRETE VIEW é a seguinte:

CREATE VIEW [ < database_name > . ] [ < owner > . ] view_name [ ( column [ ,...n ] ) ]

[ WITH < view_attribute > [ ,...n ] ]

AS

select_statement

[ WITH CHECK OPTION ]

Simplificando a sintaxe apresentada no Books OnLine, teríamos o seguinte:

CREATE VIEW Nome_da_View

AS

Comando SQL

Por exemplo, para criar uma View chamada Pedidos_Teste, que retorna os campos OrderID, OrderDate, ShipCountry e ShipCity da tabela Orders do Banco de Dados Northwind, utilizamos o seguinte comando:

USE Northwind

GO

CREATE VIEW Pedidos_Teste

AS

SELECT OrderID, OrderDate, ShipCountry

FROM Orders

É importante que você coloque o comando GO, antes do comando CREATE VIEW, caso o comando CREATE VIEW não seja o primeiro comando. Se não utilizarmos o comando GO, será emitida a seguinte mensagem de erro:

Server: Msg 111, Level 15, State 1, Line 2
‘CREATE VIEW’ must be the first statement in a query batch.

O mais importante para a criação de Views, utilizando a janela de execução de comandos T-SQL, é conhecer a linguagem T-SQL. Pois uma View nada mais é do que um comando T-SQL gravado em um Banco de Dados. Isso só reforça o que eu insisti, exaustivamente, nos exemplos anteriores, ou seja, a importância de conhecer e dominar os comandos T-SQL.

Quando criamos uma View, o que é gravado no Banco de Dados é apenas o comando T-SQL, além de informações sobre permissões de acesso. Os dados, que a View retorna, são obtidos a partir das tabelas do Banco de Dados, toda vez que a View for executada.

A permissão para utilizar o comando CREATE VIEW é atribuída para as roles de Banco de Dados db_owner e db_ddladmin, além da role de servidor sysadmin.

Podemos fazer com que o texto da View, que é armazenado na tabela syscomments, seja criptografado. Isto impede que o código seja acessado simplesmente através de um SELECT na tabela syscomments. Ou melhor, o código até que pode ser acessado, só que o texto estará criptografado, impedindo que seja lido, a menos que a criptografia seja quebrada. A seguir, um exemplo de criação de uma View com a utilização da opção WITH ENCRYPTION:

CREATE VIEW Pedidos_Criptografada

WITH ENCRYPTION

AS

SELECT OrderID, OrderDate, ShipCountry, ShipCity

FROM Orders

WHERE ShipCountry = ‘Alemanha’

Vamos a um exemplo: criar uma view chamada Média_Vendas_por_Ano. Esta View será criada no banco de dados NwindAccess.  Esta view deve exibir um registro para cada ano em que existem pedidos e o valor médio de vendas para cada ano.

O detalhe nesta View é que temos que utilizar dados da tabela Pedidos e da tabela Detalhes do Pedido. Pois os cálculos do total por pedido, os quais vão possibilitar o cálculo da média anual, são realizados a partir dos campos da tabela Detalhes do pedido. Além disso, teremos que utilizar a função Year, para extrair apenas o ano do campo DataDopedido. Depois, agrupamos o campo ano e utilizamos a função AVG na coluna que calcula o valor das vendas.

Para criar esta View, siga os passos indicados a seguir:

1. Abra a janela de execução de comandos T-SQL.

2. Digite e exe­cute o seguinte comando:

USE NwindAccess

GO

CREATE VIEW Média_Vendas_por_Ano

AS

SELECT    YEAR(DataDoPedido) AS [Ano do Pedido],

          AVG(([Quantidade] * [PreçoUnitário])* (1 -[Desconto])) AS [Média Anual]

FROM      [Pedidos]

   INNER JOIN

   [Detalhes do Pedido] ON [Pedidos].[NúmeroDoPedido] =

   [Detalhes do Pedido].[NúmeroDoPedido]

GROUP BY YEAR(DataDoPedido)

Agora vamos testar se a View Média_Vendas_por_Ano está retornando os resultados desejados.

3. Execute o seguinte comando:

SELECT * FROM Média_Vendas_por_Ano

Você deve obter os resultados indicados na Figura 9.31.

Figura 9.31 Média anual de vendas.

Já que estamos com janela de execução de comandos T-SQL aberta, vamos aprender a atribuir permissões para Views e depois testar estas permissões.

Para uma discussão aprofundada sobre o modelo de segurança e atribuição de permissões, consulte o Capítulo 6 deste livro.

No Capítulo 6, havíamos dado permissão para os usuários SERVIDOR\user1 e SERVIDOR\user2 acessar a instância SERVIDOR\SQL2005. Agora, vamos adicioná-los como usuários com permissão de acesso ao Banco de Dados NwindAccess, da instância SERVIDOR\SQL2005.

1. Você ainda deve estar com a janela de execução de comandos T-SQL aberta e logado na instância SERVIDOR\SQL2005. Execute o seguinte comando:

USE NwindAccess
GO
exec sp_grantdbaccess ‘SERVIDOR\user1’
exec sp_grantdbaccess ‘SERVIDOR\user2’

Agora vamos dar permissão de acesso para o usuário SERVIDOR\user1 para a View Média_Vendas_por_Ano, e vamos negar permissão de acesso a esta View, para o usuário SERVIDOR\user2.

2. Para dar permissão de acesso ao usuário SERVIDOR\user1, execute o seguinte comando:

GRANT SELECT ON Média_Vendas_por_Ano
TO [SERVIDOR\user1]

3. Para negar permissão de acesso ao usuário SERVIDOR\user2, execute o seguinte comando:

DENY SELECT ON Média_Vendas_por_Ano
TO [SERVIDOR\user2]

Se o usuário SERVIDOR\user2 tentar executar o seguinte comando:

SELECT * FROM Média_Vendas_por_Ano

o usuário obterá a seguinte mensagem de erro:

Server: Msg 229, Level 14, State 5, Line 1

SELECT permission denied on object ‘Média_Vendas_por_Ano’, database ‘NwindAccess,

A mensagem nos informa que o usuário não tem permissão de acesso. Isto confere com as configurações de permissões que fizemos.

4. Feche a janela de execução de comandos T-SQL e feche o SQL Server Management Studio.

No próximo capítulo e no Apêndice, falarei ainda mais sobre a linguagem T-SQL e também sobre funções disponíveis. No exemplo anterior, utilizamos a função Year, para extrair apenas o ano do campo OrderDate. No próximo capítulo, veremos uma série de funções que podem ser utilizadas em comandos T-SQL.