Forma de composição na programação T-SQL

Esses componentes permitem a manipulação de várias operações que não podem ser concluídas em uma única instrução. Os usuários podem agrupar vários comandos e usar uma das seguintes maneiras:

• Lotes : Uma lista de um ou mais comandos enviados como uma unidade de consulta do aplicativo para o servidor
• Procedimentos armazenados : Um procedimento armazenado é uma coleção de comandos T-SQL pré-compilados e predefinidos no servidor.
• Triggers : um trigger é um tipo de procedimento armazenado que é executado quando o usuário processa um evento como INSERT,UPDATE, DELETE na tabela.
• Scripts : Uma série de instruções T-SQL armazenadas em um arquivo e usadas para entrada via SSMS ou sqlcmd
• Variables : uma variável que pode ser usada pelo usuário como entrada de uma instrução T-SQL
• Controle de fluxo : controle o fluxo dideefu para criar construções condicionais em T-SQL
• Tratamento de erros: mecanismo de tratamento de erros usado para controlar erros e fornecer informações aos usuários quando erros são encontrados.

Lotes T-SQL

O T-SQl é um grupo de uma ou mais instruções enviadas como uma unidade de consulta da aplicação para o servidor. O SQL Server compila o lote em uma única unidade de execução, chamada de plano de execução. No plano de execução, o SQL executará as instruções uma a uma. Cada instrução termina com um “;”. Esta condição é opcional, mas a base para terminar a instrução sem o “;” está obsoleto e pode ser removido na nova versão do SQL Server. Então use dauas ; terminar a declaração é sempre recomendado.

Quando um erro de compilação, como um erro de sintaxe, for encontrado, o lote não será executado.

Um erro em tempo de execução, como uma violação de restrição ou lógica algorítmica, resultará em uma das seguintes situações:

• A maioria dos erros de tempo de execução interromperá o comando atual e as seguintes instruções no lote
• Um erro de tempo de execução especificado como uma restrição será usado somente se a instrução existir e as partes restantes da instrução ainda forem executadas.

As instruções SQL executadas antes da ocorrência do erro de tempo de execução não são afetadas. A única exceção é quando o lote é uma transação e o erro ocorre na transação quando há um rollback.

Por exemplo, suponha que haja 10 instruções no lote e 6 instruções tenham um erro, então as instruções restantes no lote não serão executadas. Se o lote for compilado e o terceiro comando falhar, as duas primeiras instruções serão executadas.

Algumas regras a serem seguidas ao usar lotes:

• CREATE FUNCTION, CREATE DEFAULT, CREATE RULE, CREATE TRIGGER, CREATE PROCEDURE, CREATE VIEW ou CREATE SCHEMA não podem ser concatenados com outra instrução no lote. A instrução CREATE inicia o lote e todas as instruções restantes dentro do lote são consideradas parte da definição da instrução CREATE.
• nenhuma alteração foi feita na tabela e as novas colunas referenciadas de volta ao modo como foram
• Se a primeira instrução do lote for uma instrução de execução, o comando excute não será necessário, ele será necessário apenas se a instrução de execução não existir na primeira instrução do lote.

Exemplo de lote:

USE AdventureWorks2019; GO GREATE VIEW dbo.vProduct AS SELECT ProductNumber, Name FROM Production.Product; GO SELECT * FROM dbo.vProduct; GO

O Exemplo 2 é usado para combinar vários BATCHs em uma transação:

BEGIN TRANSACTION GO USE AdventureWorks2019; GO CREATE TABLE Company( Id_Num int IDENTITY(1,1), Company_Name nvarchar(100)) GO INSERT Company (Company_Names) ('Company 1') INSERT Company (Company_Names) ('Company 2') INSERT Company (Company_Names) ('Company 3') GO SELECT Id_Num, Company_Name FROM dbo.Company ORDER BY Company_Name ASC; GO COMMIT; GO

No código acima, vários lotes são combinados em uma transação. As instruções BEGIN TRANSACTION e COMMIT definem a abertura e o fechamento da transação. CREATE TABLE, BEGIN TRANSACTION, SELECT, COMMIT e USE são lotes de instrução única. As instruções INSERT são todas empacotadas em um lote.

Variável (variável) em T-SQL

No SQL Server, as variáveis podem ser declaradas e usadas como parâmetros ou escrever consultas dinâmicas, como um parâmetro passado do aplicativo. O SQL Server fornece instruções para declarar e atribuir valores às variáveis.

DECLARAR

A variável é inicializada por meio da instrução DECLARE no corpo do lote. A essas variáveis são atribuídos valores usando instruções SELECT ou SET. As variáveis são inicializadas com o valor NULL se nenhum valor for fornecido pelo usuário no momento da declaração.

Sintaxe:

DECLARE {(@local_variable [AS] data_type) } [= value]}

Por exemplo:

USE AdventureWorks2019; GO DECLARE @find varchar(30) = 'Man%'; SELECT p.LastName, p.FirstName, ph.PhoneNumber FROM Person.Person AS p JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID WHERE LastName LIKE @find;

DEFINIR

A instrução SET atribui um valor a uma variável local inicializada com DECLARE para determinar o valor da variável

Sintaxe:

SET {@local_variable = {expression}} {@local_variable {+= | -= | *= | /= | %= | &= | ^= | |=}}

Explicação do operador:

• += adicionar e atribuir iguais
• -= subtrair e atribuir iguais
• *= multiplicar e atribuir iguais
• /= divide e atribui iguais
• %= divide pelo resto e atribui igual a
• &= bit a bit AND e atribuir depois
• ^= XOR bit a bit e atribuir depois
• |= Bitwise OR e atribuir posteriormente.

Por exemplo:

DECLARE @myvar char(20); SET @myvar = 'Hello world';

SELECIONAR

A instrução SELECT especifica a variável local especificada por meio da instrução DECLARE para recuperar a expressão

Sintaxe:

SELECT {@local_variable {= | += | -= | *= | /= | %= | &= | ^= | != } expresssion}

Por exemplo:

USE AdventureWorks2019 GO DECLARE @var1 nvarchar(30); SELECT @var1 = 'company hello'; SELECT @var1 = Name FROM Sales.Store WHERE BusinessEntityID = 10; SELECT @var1 AS 'Company name';

As instruções SET e SELECT parecem semelhantes, mas têm uma pequena diferença:

• Apenas uma variável pode ser atribuída por vez usando SET, porém, SELECT pode atribuir valores a várias variáveis ao mesmo tempo.
• SET pode atribuir apenas uma atribuição de tipo de dados básico da consulta, ocorrerá um erro quando os dados forem um conjunto de linhas e colunas. No entanto, SELECT pode atribuir vários valores de retorno a uma variável.

Sinônimos

Sinônimos é um objeto de banco de dados que atende aos seguintes propósitos:

• Ele sugere um nome diferente para diferentes objetos de banco de dados, também chamados de objetos base, que podem existir no servidor remoto ou local.
• Ele representa uma camada de abstração que protege o aplicativo cliente de modificações feitas no local e no nome do objeto base.

Por exemplo, considere a tabela Department no AdventureWorks2019 que está localizada em um servidor chamado server1. Para fazer referência a uma tabela do servidor 2 chamada server2, o cliente terá que usar quatro nomes de elementos:
server1.AdventureWorks2019.HumanResources.Department

Se o local da tabela for modificado, por exemplo, para outro servidor, o aplicativo cliente não precisa ser afetado por essa alteração. Para resolver esses dois problemas, os usuários podem criar um sinônimo DeptEmpTable owr Server2 para a tabela Department no Server1
A partir daí, o cliente só precisa usar um chamado DeptEmpTable para se referir à tabela Department .

Da mesma forma, se o local da tabela Department for alterado, o usuário precisará editar o sinônimo , DeptEmpTable para apontar para o novo local da tabela Department .
E observe que, sem a instrução ALTER Synonym , você precisa descartar o sinônimo e reinicializá-lo com o nome antigo, mas apontando para o novo local do Department .

Lista de objetos de banco de dados para os quais o usuário pode criar sinônimos:

• Procedimentos armazenados estendidos
• Função com valor de tabela SQL
• Procedimentos armazenados SQL
• Tabela (definida pelo usuário)
• Procedimento de filtro de replicação
• Funções escalares SQL
• Função SQL com valor tabelado em linha
• Visão

Sinônimos e esquemas

Suponha que um usuário queira criar um sinônimo e tenha um esquema padrão que não seja de sua propriedade. Nesse caso, eles podem identificar o nome do sinônimo com o scema que realmente possuem. Por exemplo, o usuário possui recursos do esquema, mas Material é o esquema padrão, se o usuário deseja criar sinônimo, ele deve prefixar o sinônimo com recursos do esquema .

Atribuir permissões em Sinônimos

Apenas os membros das funções db_owner ou db_ddladmin ou o proprietário do sinônimo têm permissão para conceder permissões a um sinônimo. Um usuário pode negar, conceder ou revogar todas ou quaisquer permissões em um sinônimo.

Lista de permissões:

• EXCLUIR
• INSERIR
• TOMAR POSSE
• VER DEFINIÇÃO
• AO CONTROLE
• EXECUTAR
• SELECIONAR
• ATUALIZAR

Trabalhando com sinônimos

Os usuários trabalham com sinônimos no SEL SERver 2019 ou T-SQL no SSMS. Para criar um sinônimo no SSMS usando o explorador de objetos, faça o seguinte:

Para criar um sinônimo usando T-SQL, use a sintaxe:

CREATE SYNONYM [schema_name_1.] synonym_name FOR <object> <object> ::= { server_name.[database_name].[schema_name_2].|database_name.[schema_name_2].|schema_name_2.] object_name}

Por exemplo:

USE AdventureWorks2019; GO CREATE SYNONYM MyAddressType FOR AdventureWorks2019.Person.AddressType; GO

Declarações de fluxo do programa

Os diferentes tipos de instruções de fluxo de programa suportados pelo T-SQL são os seguintes:

Linguagem de controle de fluxo T-SQL

O controle de fluxo mostra o fluxo de instruções SQL, blocos de comandos, funções definidas pelo usuário e procedimentos armazenados.

Por padrão, as instruções T-SQL são executadas sequencialmente. O fluxo do programa permite que as instruções sejam executadas em situações específicas e inter-relacionadas e é gerada entre o código usando construções semelhantes às linguagens de programação.

Lista de palavras-chave da linguagem de controle de fluxo :

• RETORNA
• LANÇAR
• TENTE… PEGAR
• ESPERAR POR
• ENQUANTO
• COMEÇO… FIM
• PARAR
• TIẾP TỤC
• Etiqueta GOTO
• SE… OUTRA

COMEÇO… FIM

Sintaxe:

BEGIN { sql_statement | statement_block } END

Por exemplo

USE AdventureWorks2019; GO BEGIN TRANSACTION; GO IF @@TRANCOUNT = 0 BEGIN SELECT FristName, MiddleName FROM Person.Person WHERE LastName = 'Andy'; ROLLBACK TRANSACTION; PRINT N'Rolling back the transaction two times would cause an error.'; END; ROLLBACK TRANSACTION; PRINT N'Rolled back the transcaction'; GO

SE… OUTRA

O TRansact-SQL depende da palavra-chave IF e a condição é executada somente se for satisfeita e retornar true. A palavra-chave ELSE é opcional na instrução T-SQl que é executada quando a condição IF não é satisfeita e retorna FALSE.

Sintaxe:

IF Boolean_expression {sql_statement | statement_block} [ELSE sql_statement | statement_block } ]

Por exemplo:

USE AdventureWorks2019; GO DECLARE @ListPrice money; SET @ListPrice = (SELECT MAX(p.ListPrice) FROM Production.Product AS p JOIN Production.ProductSubcategory AS s ON p.ProductSubcategoryID = s.ProductSubcategoryID WHERE s.[Name] = 'Mountain Bikes'); PRINT @ListPrice IF @ListPrice < 3000 PRINT 'All the products in this category can be purchased for an amount less than 3000' ELSE PRINT 'The prices for some products in this category exceed 3000'

ENQUANTO

A instrução WHILE define a condição para repetir a execução do bloco. A instrução é executada repetidamente até que a condição especificada retorne true. A instrução de execução no loop WHILE pode ser controlada com a palavra-chave BREAK ou CONTINUE .

Sintaxe:

WHILE Boolean_expression {sql_statement | statement_block | BREAK | CONTINUE}

Por exemplo:

DECLARE @flag int SET @flag = 10 WHILE (@flag <= 95) BEGIN IF @flag % 2 = 0 PRINT @flag SET @flag = @flag+ 1 CONTINUE; END; GO

CASO QUANDO

Sintaxe:

 SELECT column_list, CASE column_name WHEN value1 then display1 WHEN value2 then display2 ... ELSE display END [AS alias] FROM relative_table [WHERE condition];

Por exemplo

 SELECT studentid, studentname, GENDER = CASE gender WHEN 1 then 'Male' WHEN 0 then 'Female' ELSE 'Unknow' END FROM Student;