Neste artigo iremos explorar outras características do SQL Server 2008 junto com sua ferramenta de gerenciamento de Banco de Dados. Demonstrarei algumas de suas muitas funcionalidades.
O SQL Server se tornou um dos principais Bancos de Dados relacional dos últimos anos, esta versão abordada nestes artigos apesar de Express reúne muitas qualidades e características para desenvolvimento de sistemas de pequeno e médio porte para muitas empresas. A minha intenção, em artigos futuros, será de integrar o SQL Server com o uso do .NET. Este fato se torna muito favorável pelo fato de sua fácil integração com a IDE do Microsoft Visual Studio.
Sinônimos – Synonyms
Os denominados Sinônimos têm como objetivo proporcionar a possibilidade de criar apelidos para um ou mais objetos que façam parte deste sinônimo. A utilização do mesmo facilita o desenvolvimento de scripts quando existe a necessidade de utilizar objetos em locais distintos armazenados no Banco de Dados.
No SQL Server 2008 podemos criar Sinônimos facilmente, para isto clique com o Botão direito e escolha a opção “Novo Sinônimo”, Ver Imagem 01.
Figura 01: Propriedades do Sinônimo.
Podemos realizar esta tarefa por linha de comando, Veja abaixo a sintaxe:
CREATE SYNONYM [dbo].[MeuPedido] FOR [dbo].[TB_PED]
É importante destacar os Objetos do Banco de Dados que podemos utilizar como Sinônimos.
Procedimento armazenado de assembly | Função com valor de tabela de assembly |
Função escalar de assembly | Funções de agregação de assembly |
Procedimento de filtro de replicação | Procedimento armazenado estendido |
Função SQL escalar | Função SQL com valor de tabela |
Função SQL com valor de tabela embutida | Procedimento armazenado SQL |
Exibição | Tabela (definida pelo usuário) |
Podemos excluir um Sinônimo com o comando DROP, observe abaixo:
DROP SYNONYM DBO.MEUPEDIDO
Ou clicando no mesmo e escolhendo a opção “Excluir”. Notem que o SSMSE nos fornece muitas funcionalidades para se trabalhar com os objetos do SQL Server, ou seja, podemos fazer quase tudo por interface gráfica.
Procedimentos Armazenados – Stored Procedures
Para quem não sabe, Stored Procedures é um conjunto de instruções Transact-SQL que são executadas dentro do Banco de Dados. Ele pode ser chamado tanto pelo SGBD (Sistema Gerenciador de banco de Dados) quanto por um sistema que faz interface com o mesmo. Imaginemos que estamos criando uma aplicação em .NET e temos que criar rotinas para tal tarefa, num futuro próximo migramos nossa aplicação para outra linguagem, uma das vantagens de se trabalhar com Stored Procedures é que todo comando está armazenado dentro do SGBD, ou seja, teremos o trabalho apenas de invocar este Stored Procedure na aplicação.
Nota Importante: Ao executar o Stored Procedure pela primeira vez ele é compilado e a cada execução são colocados em CACHE, caso seja executado novamente é utilizado o resultado do CACHE diminuindo o esforço do SQL Server e conseqüentemente aumentando a velocidade de acesso.
Importante ressaltar que existem Stored Procedures do Sistema e definidas por usuários. Na Imagem 02 estão as de Sistema, mas especificamente em nosso artigo iremos abordar as de usuário.
Figura 02: SP de Sistema.
Bem, vamos ao que interessa, abrindo o SSMSE clique no item Programação e logo em seguida em Procedimentos Armazenados. Aparecerá toda a sintaxe para se criar um SP, veja a seguir para melhores detalhes:
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
AS
BEGIN
SET NOCOUNT ON;
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO
Em CREATE PROCEDURE definimos o nome do Stored Procedure, em seguida os parâmetros de Saída e os comandos necessários. No nosso caso criaremos uma SP para retornar os registros de acordo com o que será passado por parâmetro, Veja a seguir:
CREATE PROCEDURE SelecionaPedido
@NUM_PED int
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM TB_ITE WHERE CODIGO = @NUM_PED
END
GO
Clique em executar e verifique que foi criado um Objeto no Banco de Dados chamado dbo.SelecionaPedido. Agora basta executá-lo com o seguinte comando:
EXECUTE [DB_THECLUB].[dbo].[SelecionaPedido] 1
Neste caso foi retornado todos os itens do Pedido cujo código seria o 1. Veja Imagem 03.
Figura 03: Resultado Stored Procedure.
Podemos também alterar um SP já criado, para isto clique com o botão direito e escolha “Modificar”, ou podemos utilizar o seguinte código:
ALTER PROCEDURE [dbo].[SelecionaPedido]
@NUM_PED int
AS
BEGIN
SET NOCOUNT ON;
SELECT COD_PED, DAT_PED FROM TB_PED WHERE COD_PED = @NUM_PED
END
Já o comando DROP exclui nosso Stored Procedure criado.
DROP PROCEDURE SELECIONAPEDIDO
Não podemos esquecer que podemos executar estes comandos utilizando a interface gráfica, Ver Imagem 04.
Figura 04: Propriedades do Objeto.
Funções – Functions
É importante ressaltar que no caso das Funções elas sempre retornam valores ou tabelas. Podemos construir nossas próprias Funções, semelhantes a DATE(), LTRIM(), RTRIM() entre outras.
No SQL Server 2008 temos 4 tipos de Funções, as denominadas Funções de Valor de Tabela, Funções de Valor Escalar, Funções de Agregação e Funções do Sistema. No nosso artigo iremos estudar um pouco das duas primeiras.
Funções de valor de tabela: Este tipo de função retorna especificamente tipo de dados de tabela. Para isto clique com o botão direito em “Funções com Valor de Tabela” e escolha “Nova Função com Valor de Tabela Embutida”. Veja o Script criado abaixo:
CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName>
(
<@param1, sysname, @p1> <Data_Type_For_Param1, , int>,
<@param2, sysname, @p2> <Data_Type_For_Param2, , char>
)
RETURNS TABLE
AS
RETURN
(
SELECT 0
)
Para entendermos melhor criaremos uma Função para retornar os Itens de um Pedido de Venda, para isto verifiquem o código abaixo:
CREATE FUNCTION RETORNA_ITENS
(
@NUM_PED int
)
RETURNS TABLE
AS
RETURN
(
SELECT * FROM TB_ITE WHERE COD_PED = @NUM_PED
)
Para executá-la:
SELECT * FROM RETORNA_ITENS(1)
Figura 05: Resultado Função.
Funções com valor escalar: Estes tipos de funções retornam um valor único de dados do tipo definido na cláusula RETURNS. É importante saber que o tipo de retorno destas funções pode retornar qualquer tipo de dados, exceto text, ntext, image, cursor e timestamp.
Para isto clique com o botão direito em “Funções com Valor Escalar” e escolha “Nova Função com Valor Escalar”. Veja o Script criado abaixo:
CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName>
(
<@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS <Function_Data_Type, ,int>
AS
BEGIN
DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>
SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>
RETURN <@ResultVar, sysname, @Result>
END
Iremos elaborar uma função simples que retira os espaços em branco do lado esquerdo e do lado direito de uma string. Usaremos duas funções internas no SQL Server, uma denominada LTRIM (retira o espaço em branco do lado esquerdo do texto) e outra RTRIM (retira os espaços em branco do lado direito do texto), Abaixo o script para isto:
CREATE FUNCTION RETIRA_ESPACO(@STR VARCHAR(100))
RETURNS VARCHAR(100)
BEGIN
RETURN
(
LTRIM(RTRIM(@STR))
)
END
Para executá-la:
SELECT DBO.RETIRA_ESPACO(NOM_CLI) AS CLIENTE FROM CAD_CLI
Figura 06: Resultado Função.
Clicando com o botão direito na função desejada, podemos realizar vários comandos como exclusão, Modificação. Veja abaixo o código referente à Alteração e a Exclusão respectivamente.
Para Alterar:
ALTER FUNCTION [dbo].[RETORNA_ITENS]
(
@NUM_PED int
)
RETURNS TABLE
AS
RETURN
(
SELECT * FROM TB_ITE WHERE COD_PED = @NUM_PED
)
E para excluir utilize o código abaixo:
DROP FUNCTION [dbo].[RETORNA_ITENS]
Gatilhos de Banco de Dados - Triggers
Resumindo, a Trigger (ou gatilho) é um objeto que é automaticamente executado assim que efetuado um INSERT, DELETE ou UPDATE na tabela. Podemos criá-las de duas maneiras, BEFORE (antes) e AFTER (após). Os gatilhos BEFORE disparam antes das modificações da instrução serem aplicadas, e antes de qualquer restrição ser aplicada. Já os AFTER disparam após todas as restrições terem sido satisfeitas, e após todas as alterações terem sido aplicadas à tabela de destino.
Algumas Limitações de sua utilização
- Não é possível criar um gatilho para uma View (Visão);
- Não podemos utilizar os seguintes comandos SQL:
ALTER DATABASE, ALTER TRIGGER, ALTER PROCEDURE, ALTER TABLE, CREATE DATABASE, CREATE PROCEDURE CREATE TABLE, DROP DATABASE, DROP TABLE, DROP PROCEDURE, DROP TRIGGER, entre outros.
Para criar um Gatilho no SQL Server 2008, clique na tabela desejada e escolha a Opção “Gatilho” e em seguida “Novo Gatilho”, Ver Imagem 07.
Figura 07: Criar um Gatilho.
Abaixo o código referente:
CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name>
ON <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name>
AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>
AS
BEGIN
SET NOCOUNT ON;
END
No nosso caso iremos criar um gatilho que ao dar o comando INSERT na tabela de Pedidos de Venda atualizará um campo ULT_PED da tabela de Clientes, veja a seguir o código para isto.
CREATE TRIGGER UPDATE_CLIENTE
ON TB_PED
AFTER INSERT
AS
BEGIN
DECLARE @COD_PED INT
DECLARE @COD_CLI INT
SELECT @COD_PED = COD_PED, @COD_CLI = COD_CLI FROM Inserted
UPDATE CAD_CLI SET ULT_PED = @COD_PED WHERE COD_CLI = @COD_CLI
END
Neste caso definimos duas variáveis com o comando DECLARE, COD_PED E COD_CLI em seguida recuperamos os valores inseridos utilizando o comando INSERTED para podermos posteriormente realizar o UPDATE na tabela de clientes.
Para Modificar o Gatilho podemos fazer da seguinte forma, clicando com o Botão direito sobre o mesmo escolhendo a opção “Modificar”, veja o código:
ALTER TRIGGER [dbo].[UPDATE_CLIENTE]
ON [dbo].[TB_PED]
BEFORE INSERT
AS
BEGIN
DECLARE @COD_PED INT
DECLARE @COD_CLI INT
SELECT @COD_PED = COD_PED, @COD_CLI = COD_CLI FROM Inserted
UPDATE CAD_CLI SET ULT_PED = @COD_PED WHERE COD_CLI = @COD_CLI
END
Seguindo a mesma lógica dos objetos criados anteriormente, para excluir este gatilho utilize o comando DROP.
DROP TRIGGER UPDATE_CLIENTE
Conclusão
Vimos neste artigo muitas funcionalidades do SQL Server 2008. Com estas dicas os senhores poderão realizar muitas tarefas e conseqüentemente um aumento de produtividade . Vimos também que o SQL Server Management Studio Express nos auxilia na utilização destes recursos junto com o Banco de Dados.
Fico aqui, um forte abraço e até o mês que vem!
Nenhum comentário:
Postar um comentário