Transact SQL e controle de Fluxo

O Transact SQL é a extensão ao SQL-92, padronizada e publicada pela ANSI. Normalmente quando falamos em “extensão” ao SQL, imaginam-se apenas comandos SQL DML e DDL utilizados em aplicativos em OLTP. Porém o T-SQL vai muito além: é uma poderosa linguagem não apenas para manipular dados, mas capaz de executar rotinas administrativas relacionadas a segurança, integridade e manutenção do SQL Server.

Neste artigo vamos estudar os tipos de dados suportados pelo T-SQL. A seguir vamos conhecer os tipos de variáveis e como podemos declarar e utilizá-las no fluxo de nosso programa. O próximo passo é ver as diversas rotinas de controle de fluxo, indispensáveis para a criação de Stored Procedures e Trigger e em seguida vamos conhecer algumas das principais funções internas do SQL Server e aprender a criar nossas próprias funções (UDFs).

Tipos de Dados

O SQL Server 2000 introduziu novos tipos de dados, como o BigInt, SQL_variant e Table. Na tabela a seguir estão relacionados os principais tipos de dados suportados:

Outros tipos suportados são: Cursor, Variant (normalmente utilizado na Automação OLE), o já mencionado Table, capaz de armazenar um conjunto de resultados, rowversion (timestamp) e GUID (uniqueidentifier). O tipo sysname é utilizado para armazenar nomes de objetos, como tabelas, colunas etc. Para mais detalhes sobre os tipos de dados suportados pelo SQL Server consulte o BOL.

Considerações sobre execução de T-SQL

Em principio qualquer bloco de código que pode ser executado através de uma Ferramenta como o QA, pode ser colocado dentro de um objeto dentro de um aplicativo e executado. Porém temos que tomar cuidado com o escopo do bloco de código. Observe a sentença T-SQL abaixo:

Aparentemente a sintaxe esta correta, porém ao tentarmos executar o código no QA temos um erro!

O motivo: não podemos adicionar uma coluna a uma tabela e em seguida tentar inserir dados, pois na verdade o engine só vai executar o bloco de código após a leitura de todo o lote. Ao executar a instrução Insert a coluna Nome ainda não existe. A solução é separar os dois comandos em escopos diferentes. Isto pode ser feito com o uso do sinal GO. GO diz ao SQL Server “Execute até aqui e depois continue”. O código anterior estará correto com a inserção do sinal entre os comandos:

Uma conseqüência negativa do uso de GO é que, como é iniciado um novo escopo, variáveis locais declaradas anteriormente são retiradas da memória e conseqüentemente perdidas. Go deve ser exclusivo em uma mesma linha. No inicio da seção foi dito que qualquer bloco de código que pode ser executa do através de uma ferramenta como o QA, pode ser colocado dentro de um componente de um aplicativo e executado. Há uma exceção: o bloco deve conter um único lote, em outras palavras, não pode ter o sinal GO. Como então executar o código anterior? Vamos analisar por partes.

O comando

USE NorthWind GO

diz ao SQL Server alterar para o banco de dados NorthWind e depois continuar a execução. Isto é importante, pois vai garantir que todos os comandos posteriores, este caso a criação e alteração da tabela, sejam executados no Banco de Dados desejado. Teríamos o mesmo efeito se cada comando fosse qualificado com a indicação do banco de dados e o proprietário do objeto:

CREATE TABLE NorthWind.dbo.Teste_Escopo (

Codigo int)

ALTER TABLE NorthWind.dbo.Teste_Escopo

ADD Nome varchar(20)

GO

INSERT INTO NorthWind.dbo.Teste_Escopo (Codigo,Nome) VALUES

(10,'José Silva')

No QA podemos alternar facilmente entre diferentes bancos de Dados. Por exemplo, tecle Ctrl+U, P e Enter, quase sem perceber estará conectado ao banco de dados Pubs. Às vezes um DBA com todos os privilégios possíveis no servidor poderá alternar diversas vezes entre banco de dados diferentes e acabar executando um comando onde não deveria, e o pior é que geralmente um banco de dados de testes possui os mesmos objetos do de produção. Por tudo isso utilizar Use ou qualificar o comando é importantíssimo.

Porém em um aplicativo de produção o banco de dados é definido na conexão e dificilmente alterado, por isso não precisamos ter a preocupação com que o código seja executado no banco de dados correto, podemos dispensar Use ou a qualificação dos comandos. Já para o restante do código, temos que executar a alteração da tabela e a adição do registro em execuções diferentes.

Variáveis locais

O SQL Server suporta dois tipos de variáveis: globais, que veremos na próxima seção e locais.Variáveis locais são válidas dentro do escopo onde foram declaradas. Para a declaração de variáveis locais usamos a palavra chave Declare, seguido pelo nome da variável e o tipo de dado. Uma variável local deve ter como prefixo o caractere @. Observe o exemplo:

DECLARE @Nome nvarchar(30)

Podemos declarar diversas variáveis em um mesmo comando, separando-as com virgula:

DECLARE @Nome nvarchar(30), @idade INT

Para atribuir um valor a uma variável podemos utilizar Set ou Select:

--Exemplo 1

DECLARE @Nome varchar(30),@Idade int

SET @Nome = 'Ana'

SELECT @Idade = 30

SELECT @Idade AS 'SET E SELECT'

Utilizando Select podemos atribuir valores a diversas variáveis em um mesmo comando:

--Exemplo 2

DECLARE @Nome varchar(30),@Idade int SELECT @Nome = 'Ana',@Idade = 30 SELECT @Nome AS 'SELECT'

Variáveis locais não podem receber um valor Default.

Variáveis Globais

Variáveis globais têm com prefixo @@ e não podem ser definidas pelo usuário. Sua função é retornar informação do sistema. Podemos obter o valor de uma variável global usando select:

SELECT @@Error

ou print

PRINT @@Error

Podemos ainda atribuir uma variável global a uma variável local. Isto é importante quando precisamos tratar o valor de uma variável global que pode ser alterado diversas vezes dentro de um mesmo escopo. Ao atribuirmos o valor a uma variável local, não corremos o risco de perder o valor desejado.

DECLARE @erro int DELECT @erro = @@Error PRINT @erro

Uma variável global pode ser utilizada como condição em uma consulta:

--Exemplo 3

USE Northwind

SELECT @@CONNECTIONS AS 'CONEXÕES'

Veja a seguir algumas das variáveis globais do SQL Server:

@@CONNECTIONS: Retorna o número de conexões e tentativas desde que o SQL Server iniciou.

@@ERROR: Retorna o código do ultimo erro ocorrido

@@IDENTITY: Retorna o ultimo valor Identity inserido

@@ROWCOUNT: Retorna o número de linhas do último comando executado.

@@VERSION: Retorna diversas informações a respeito do servidor, como versão, data e CPU

Para uma lista completa de variáveis globais consulte o BOL.

Controle de Fluxo

O verdadeiro poder do T-SQL está no controle de fluxo, sem o qual toda a regra de negocio teria que continuar no cliente. Normalmente um SGDB não tem uma linguagem para controle de fluxo tão poderosa quando um ambiente de desenvolvimento como o Delphi ou o Visual Basic, mas proporcionam os recursos necessários para a implementação das regras de negócio do aplicativo.

IF...ELSE

IF...ELSE nos oferece controle de fluxo condicional, a sintaxe é descrita a seguir:

IF Boolean_expression

{ sql_statement | statement_block }

[ ELSE

{ sql_statement | statement_block } ]

No exemplo abaixo, é atribuída a uma variável o total de vendas de um determinado produto, a seguir um comando IF...Else verifica se a venda superou as 1000 unidades, imprimindo mensagens distintas para cada caso:

--Exemplo 4

USE Northwind

GO

DECLARE @Valor money

SELECT @Valor = (select sum(Quantity) FROM [Order Details]

WHERE ProductID = 31)

IF @Valor > 1000

SELECT 'Produto com boas vendas' AS 'IF'

ELSE

SELECT 'Produto com poucas vendas' AS 'IF'

Caso haja mais de um comando a ser executado em uma determinada condição, podemos usar um bloco Begin...end.

Case

Case nos permite substituir determinados valores conforme uma lista de condições.Permite ainda especificar um valor a ser retornado se nenhuma condição for verdadeira. É bastante útil para tornar a saída mais legível para o usuário final. Por exemplo, se um campo EstadoCivil em uma tabela de seu sistema armazena valores char como ‘C’ ou ‘D’, você pode facilmente substituí-los por Casado ou Divorciado em uma consulta ou relatório. O exemplo abaixo substituiu títulos de tratamento como Mr. E Ms. Por Senhor e Senhora:

Observe a saída.

WHILE

O While é um laço de repetição com a seguinte sintaxe:

WHILE Boolean_expression

{ sql_statement | statement_block }

[ BREAK ]

{ sql_statement | statement_block }

[ CONTINUE ]

Break e continue, causam a parada e o reinicio do laço, respectivamente. O laço while normalmente é utilizado dentro de cursores.

Print

Retorna uma mensagem definida pelo usuário.

PRINT ‘Este é um exemplo do uso de print’

Podemos imprimir ainda uma variável local ou global:

--Exemplo 6

PRINT @@Version

SELECT @@Version AS 'PRINT NÃO É MOSTRADO EM UM RESULTSET'

Geração de erros

Raiserror permite gerar um erro definido pelo usuário. O procedimento recebe os seguintes argumentos:

•    msg_id | msg_str: Permite que seja passado um ID do erro previamente gravado na tabela de sistema sysmessages (veremos como adicionar um erro personalizado logo a seguir) ou ainda uma String informativa sobre o erro. Caso se utilizenao uma string, o id do erro será 50000.

•    Severity: Informa a gravidade do erro, um valor de 0 a 18 pode ser utilizado por qualquer usuário, de 19 a 25 apenas por membros do papel Sysadmin. Utilizando um gravidade acima de 20, a conexão é encerrada.

•    State:Utilizado para indicar a origem do erro, pode assumir um valor entre 1 e 127.

•    WITH: Permite definir algumas opções: Log: grava o erro no log do sistema. Esta opção é obrigatória com uma gravidade acima de 20. NOWAIT: Retorna a mensagem imediatamente para o usuário e SETERROR, que define o valor da variável global @@error como o ID do erro ou com 50000, independente da gravidade do erro.

Neste exemplo, é gerado um erro simples de baixa gravidade:

Um erro com gravidade 25 e gravação em log:

 

Criando erros personalizados

Através da procedure de sistema sp_addmessage podemos definir nossos próprios erros. Esta procedure recebe os seguintes argumentos:

•    msg_id: Um valor inteiro maior que 50000 e menor que 2147483647. Deve ser único, caso já exista uma mensagem com o id especificado o erro não será criado;

•    Severity: Um valor entre 1 e 25 conforme descrito na seção anterior;

•    Msg: É a mensagem de erro, pode ter até 255 caracteres;

•    Language; Idioma utilizado, quando omitido utiliza o idioma da seção;

•    with_log: determina se o erro vai ser gravado no log do sistema operacional;

•    Replace: Determina se o erro deve substituir um erro já existente.

Exemplo de criação de um erro:

EXEC sp_addmessage 50001,16,'Esta é uma nova mensagem de erro',null,False,replace

Comentários

O T-SQL suporta dois tipos de comentários: O comentário até o final da linha, especificado pelos caracteres -- e os comentários de bloco, iniciados por /* e terminados com */, por exemplo:

-- Este é um comentário de uma única linha /* Este é um comentário que pode ter diversas linhas */

Funções

O SQL Server 2000 possui dezenas de funções internas, divididas em mais de 10 categorias. Uma função pode ter o mais variado fim: Tratar uma string, retornar algum valor de configuração do SGDB, manipular datas etc. Vamos ver algumas das principais funções do SQL Server nas principais categorias, acompanhados de exemplos e de suas respectivas saídas.

Funções de Tratamento de String

ASCII: Retorna o código ASCII do caractere mais à esquerda. Observe o exemplo:

· CHAR: Retorna o caractere equivalente ao código ASCII passado como parâmetro:

· CHARINDEX: Retorna a posição Inicial de uma String em outra. Recebe três parâmetros: os caracteres que são procurados, a string onde a busca será realizada, e a posição inicial da busca, observe o exemplo:

· LEFT e RIGHT: Retornam a parte esquerda e direita, respectivamente, de uma string. O segundo argumento é o número de caracteres que devem ser retornados:

· LEN: Retorna o número de caracteres de uma string:

· LOWER e UPPER: Converte uma string para letras minúsculas e maiúsculas respectivamente:

· LTRIM E RTRIM; Removem espaços em branco a esquerda e a direita de uma string, respectivamente.

· REPLACE: Substitui todas as ocorrências de uma String em outra. Recebe três parâmetros: A string onde a busca deverá ser realizada; a string que se quer encontrar e a string que deve ser utilizada na substituição:

· REPLICATE: Repete uma string um certo número de vezes, determinado pelo segundo parâmetro. No exemplo abaixo REPLICATE é utilizado em conjunto com REPLACE :

· SUBSTRING: Retorna parte de uma string. Recebe três parâmetros: A string da qual se quer retornar parte; a posição inicial e o número de caracteres.

Funções de Data e Hora

Um tipo DateTime pode conter informações de ano, mês, dia, hora, minutos etc. A primeira função que devemos estudar é DatePart, que retorna parte de uma data passada como parâmetro. O parâmetro que indica a parte da data desejada é importante pois é utilizada em outras funções de data e hora do SQL Server. Observe a tabela a seguir:

Parte díi Data/Hora	Representação	Abreviação
Ano	Year	yy, yyyy
Mês	Month	m
Díei no Ano	dayofyear	dy. y
Dia no Mês	Day	dd,d
Semana no Ano	Week	wk. ww
Hora	Hour	Hh
Vliruilo	minute	mi, n
Segundo	second	ss, s
Milesegundo	millisecond	Ms

· DATEPART: Conforme especificado no parágrafo anterior, retorna da data/hora indicada no primeiro argumento:

A função Getdate() utilizada no exemplo anterior retorna a data e hora atuais no servidor. O SQL Server possui ainda as funções MONTH, YEAR, DAY, que retornam, respectivamente o mês, ano e dia de uma Data. Poderíamos ter um resultado equivalente a instrução anterior utilizando YEAR:

· DATEADD:Adiciona um intervalo a data passada como parâmetro. Recebe três argumentos, A parte da data a ser incrementada (DatePart), o incremento e a data alvo. No exemplo abaixo são adicionados dois anos a data atual:

· DATEDIFF: Retorna a diferença entre duas datas. Recebe três argumentos, a parte da data (DatePart), a data inicial e a data final:

· DATENAME: Retorna uma descrição textual da data passada como parâmetro. No exemplo abaixo geramos uma saída traduzida ao usuário do dia da semana utilizando Case:

Funções Matemáticas

Dentre as diversas funções matemáticas existentes no SQL Server 2000, vamos obviamente ver apenas as mais importantes:

· POWER: Retorna um número (primeiro parâmetro) elevado a potencia do valor passado como segundo parâmetro:

ABS: Retorn a o valor absoluto de uma expressão:

· SQRT: Retorna a raiz quadrada de uma expressão:

FLOOR: Retorna o menor valor inteiro da expressão:

Funções de sistema

Utilizadas para retornar informações diversas sobre o Servidor. · CURRENT_USER: Retorna o usuário corrente da seção.

HOST_NAME ( ) : Retorna o nome da estação :

· ISNULL: Utilizado para substituir valores nulos pelo valor especificado no segundo parâmetro:

· ISDATE: Retorna 1 caso uma expressão passada como parâmetro seja uma data válida, caso contrário retorna zero:

· CAST: Faz uma conversão entre tipos de dados, a sintaxe de uso é: CAST ( expression AS data_type )

Por exemplo, para converter uma String em um inteiro:

UDFs (Funções Definidas pelo Usuário)

Até a versão 7, o SQL Server dispunha apenas de funções internas do Servidor. As UDFs, ou funções definidas pelo usuário, foram uma das grandes novidades da versão 2000. Podemos criar basicamente dois tipos de UDFs: As que retornam um único valor (funções escalares) e as que retornam uma tabela. Funções que retornam uma tabela podem ser uma poderosa alternativa para Views. Imagine por exemplo que você quer criar diversas views de uma mesma tabela, cada uma com uma condição diferente. Alternativamente podemos criar uma única UDF e especificar a condição através de um ou mais parâmetros. Um parâmetro de uma UDF pode ter um valor Default, assim como numa Stored Procedure, a diferença é que temos que declarar explicitamente, através do uso da palavra chave DEFAULT, que desejamos utilizar o valor padrão. Algumas outras observações a respeito de funções:

·  Uma UDF não pode fazer qualquer alteração em um objeto do servidor, como uma tabela;

· Uma UDF não pode chamar uma função não deterministica em seu escopo. Uma função não deterministica e qualquer função que retorne um valor diferente a cada chamada, por exemplo: GETDATE() ;

· A chamada de uma função escalar deve ser qualificada com pelo menos o nome do proprietário (owner).

Vamos ver primeiramente um exemplo de UDF escalar, e logo a seguir funções que retornam uma tabela. A função MaiorVenda verifica qual a maior quantidade que um produto vendeu em um mesmo pedido.

USE Northwind

CREATE FUNCTION MaiorVenda (@ProductID int)

RETURNS int

AS

BEGIN

RETURN(Select max(Quantity) from [order details] where

ProductID = @ProductID) END

Podemos usar a função em uma consulta de produtos, onde verificamos os dez produtos com maior saída em uma única venda. Tudo a ser feito é uma consulta normal ao nome do produto, já o segundo argumento é uma chamada à função maiorvenda (observe que a chamada esta qualificada) passando o ID do produto como parâmetro.

SET ROWCOUNT 10 determina que a saída seja de apenas 10 resultados, como a ordenação é feita pelo segundo argumento da lista de seleção, teremos os 10 resultados com vendas mais alta.

UDFs que retornam uma tabela podem ser de dois tipos: Inline e Multi-Statment. A UDF Inline é mais simples, não há uma definição da tabela de retorno, ao invés disto esta é definida através de uma consulta, tudo que uma função Inline tem é uma instrução Select. Neste exemplo a função executa uma consulta de fornecedores usando como condição o nome da cidade, passada como parâmetro:

USE Northwind

GO

CREATE FUNCTION Fornecedores_Cidade (@Cidade varchar(15))

RETURNS TABLE

AS

RETURN (SELECT SupplierID AS 'ID',CompanyName AS 'Nome'

,Phone AS 'Telefone' FROM

SupplierS WHERE City = @Cidade )

A chamada é feita como se a função fosse uma tabela, obviamente passando possíveis parâmetros:

Já na UDF Multi-Statment, um tipo Table deve ser declarado e definido, que posteriormente será utilizado como retorno. A UDF Multi-Statment permite qualquer tipo de controle de fluxo em seu corpo, não se restringindo a uma consulta. O exemplo Fornecedores_Cidade2 produz exatamente o mesmo resultado da função anterior, porém possui as caracteristicas UDF Multi-Statment: note a declaração de um tipo Table (@Tabela) e a existência de um bloco begin end (Inexistente na UDF Inline). A tabela criada é preenchida através de um comando Insert aninhado com Select:

USE Northwind

GO

CREATE FUNCTION Fornecedores_Cidade2 (@Cidade varchar(15))

RETURNS @Tabela TABLE

(

[id] int,

Nome varchar(40),

Telefone varchar(24)

BEGIN

INSERT INTO @Tabela

select SupplierID,CompanyName,Phone from suppliers where City

= @cidade

RETURN

END

É utilizada da mesma forma que uma UDF Inline:

Efeito de geração de erros no SQL Server ao aplicativo

A geração de um erro no SQL Server pode ter três efeitos diferentes no aplicativo: O erro se propaga ao aplicativo, a transação é cancelada e a conexão encerrada; o erro se propagada até o aplicativo e a transação é cancelada, mas a conexão não é encerrada; ou o erro não se propaga até o aplicativo. O que vai definir o comportamento do erro é a gravidade (severity) do mesmo, conforme abaixo:

· Até 10 o erro não é passado ao aplicativo;

· Acima de 10 o erro é passado ao aplicativo, a transação é encerrada;

· Igual ou superior a 20 o erro é passado ao aplicativo, a transação é abortada e a conexão é encerrada;