António Rocha
Nuno Melo e Castro
!
Transact-SQL (T-SQL)
Extensão ao SQL
Permite controlo do fluxo de execução
Permite comandos DDL (contrariamente ao
PLSQL)
T-SQL combina:
poder de manipulação de dados do SQL com
poder de processamento das linguagens
procedimentais
# $%
#$
Variáveis
Tipos de dados escalares
Controlo do fluxo de execução
Funções integradas
Gestão de cursores
Processamento de excepções
Código armazenado na base de dados
"
' %
(
A unidade de programação é semelhante ao já visto
para PL/SQL, podendo ter:
Blocos de código sem nome – scripts. Normalmente
são construídos dinamicamente e executados apenas
uma vez.
Blocos com nome
Procedures e functions - são guardados na BD. Este blocos
normalmente não são alterados depois de construídos e são
executados várias vezes. Os subprogramas são executados
explicitamente via uma chamada a um procedure ou function.
Triggers: São também guardados na BD. Este blocos
normalmente não são alterados depois de construídos e são
executados várias vezes. Os Triggers são executados
implicitamente quando acontecem determinados eventos na BD
(Insert, Update e Delete)
&
' %
(
DECLARE
--Definição de variáveis.
BEGIN
--Acções executáveis
END
Contrariamente ao PL/SQL, não existe propriamente o conceito de
bloco. Um bloco em T/-SQL é um script.
Os elementos BEGIN e END não são obrigatórios e delimitam o
conjunto de acções a efectuar.
Como não existe o conceito de bloco, DECLARE não define uma
secção de declaração de variáveis. É opcional e é utilizada para
definir objectos de T-SQL, tais como as variáveis.
É possível também ter tratamento de excepções, e definir acções a
tomar quando estas ocorrem.
Contrariamente ao PL/SQL não é necessário terminar as instruções
com ponto e vírgula.
)
*
+
Syntax DECLARE
DECLARE { @local_variable [AS] data_type, […n] }
Declare
Declare
@DataNasc
@DataNasc
@idade
@idade
@nome
@nome
@Controlador
@Controlador
@valido
@valido
DATETIME,
DATETIME,
NUMERIC(2),
NUMERIC(2),
VARCHAR(50),
VARCHAR(50),
NUMERIC(2),
NUMERIC(2),
BIT
BIT
*
+
,$
Contrariamente ao PL/SQL, o T-SQL não
permite no momento da definição:
Inicializar
Ter defaults
Constantes
Definir tipos de dados com base no tipo de outras
variáveis
Definir tipos de dados com base na estrutura de
uma tabela
%
-, %
$( . %
$
O T-SQL tem o conceito de variáveis globais:
Não podem ser definidas pelo utilizador, são do
sistema
Distinguem-se das locais por terem no início os
símbolos @@.
Ex. SELECT @@VERSION
Muito semelhante ao já visto para o PL/SQL:
As instruções podem, se necessário, passar de uma linha para a
outra, mas as palavras-chave não podem ser divididas.
As unidades léxicas (identificadores, operadores, etc) podem ser
separadas por um ou mais espaços ou por outros limitadores
que não se confundam com a unidade léxica.
Não se podem usar palavras reservadas como identificadores,
excepto se entre aspas.
Os identificadores têm que começar por uma letra e as variáveis
locais por @.
Os valores literais do tipo caracter ou data têm que ser indicados
entre plicas.
Os comentários podem ser incluídos entre os símbolos /* e */
quando o comentário engloba várias linhas, ou então após -–
quando o comentário é apenas uma linha.
/
0
$.-$%
#$
+
*
/
IS NULL, LIKE, BETWEEN,
IN, =, >, <, <>, !=, ^=, <=, >=
=
+
NOT
AND
OR
Adição
Subtracção
Multiplicação
Divisão
Comparação
Atribuição
Concatenação
Negação lógica
Conjunção
Disjunção
%
1%
$
(
Expressão ou lista
)
Expressão ou lista
;
Fim de instrução
‘
Cadeia de caracteres
[]
Identificador
:
Etiqueta
--
Comentário
/*
Comentário
*/
Comentário
* 1 %
$ .2# $
Contrariamente ao PL/SQL o domínio de Y estende-se desde a sua definição
até ao final.
Não existindo o conceito de bloco, não é possível declarar variáveis com o
mesmo nome dentro do mesmo script (mesmo estando declaradas antes de
BEGIN … END)
$
$
#
3
IF-ELSE
IF condition1
statement1
ELSE
IF condition2
statement2
Para colocar mais do que uma instrução dentro do IF, é ncessário usar
o Begin / End
IF condition1
begin
statement11
statement12
end
ELSE
IF condition2
statement2
"
0
$ 1
$
#
Keyword
4# 5
6
Description
BEGIN...END
Defines a statement block.
BREAK
Exits the innermost WHILE loop.
GOTO label
Continues processing at the statement following the label as
defined by label.
IF...ELSE
Defines conditional, and optionally, alternate execution when a
condition is FALSE.
RETURN
Exits unconditionally.
WHILE
Repeats statements while a specific condition is TRUE.
Other Transact-SQL statements that can be used with control-of-flow language statements are:
/*...*/ (Comment)
EXECUTE
-- (Comment)
PRINT
CASE
RAISERROR
DECLARE local_variable
&
#
DECLARE
@VNome
@VPosto
BEGIN
SELECT
FROM
WHERE
END
%
. %!
,
$ , %
-, %
$
varchar(50),
varchar(50)
@VNome = Nome, @ VPosto = Posto
Empregado
NumEmp = 5
Caso a query retorne mais do que um registo as
variáveis ficam com o valor do último registo
seleccionado (não dá erro).
Este comando é feito no PL/SQL com o INTO.
)
$
Os registos retornados por uma instrução
SQL podem ser manipulados através de um
cursor. Um cursor é semelhante a uma lista
de valores, sendo possível percorrer e
posicionar em qualquer um dos registos.
Os cursores são semelhantes aos já vistos
para o PL/SQL. No entanto o T-SQL tem
muito menos possibilidades de tratamento.
$ $
# $$
%
#
O processo típico da utilização de um cursor é :
1. Declarar variáveis para guardar os valores retornados pelo
cursor
2. Declarar um cursor com o comando Declare e associar-lhe um
Select
3. Usar o comando Open para executar o Select e abrir o cursor
4. Utilizar instruções de posicionamento do cursor - Fetch
5. Fechar o cursor com a instrução Close, para libertar a memória
dos resultados do cursor, e possivelmente utilizar a instrução
Deallocate se não for necessário usar novamente o cursor.
@@Fetch_Status - Permite testar o status do fetch, se diferente
de zero, ocorreu um erro.
$
7$
$%
#% 1
Utilizar instruções de posicionamento do cursor
Fetch First - Avança para o primeiro registo do cursor.
Fetch Next - Avança para o registo seguinte.
Fetch Prior - Avança para o registo anterior.
Fetch Last - Avança para o último registo do cursor.
Fetch Absolute n - Avança para posição n do cursor.
Fetch Relative n - Avança n registos a partir do registo
actual do cursor.
Para utilizar os comandos de fetch (à excepção do Next) é necessário
declarar um cursor do tipo SCROLL.
$
$8 9
Syntax básica:
DECLARE cursor_name CURSOR
[FORWARD_ONLY | SCROLL]
[LOCAL | GLOBAL]
FOR select_statement
[FOR UPDATE [OF column_name [,...n]]]
Para abrir
OPEN nome_cursor
Buscar valores
FETCH NEXT FROM nome_cursor INTO lista_variaveis
Para fechar
CLOSE nome_cursor
Para retirar o Declare
DEALLOCATE nome_cursor
/
$
91
DECLARE Cursor_Cli CURSOR FOR
SELECT ClienteID, Nome
FROM Clientes
Declare @Cli int, @Nome varchar(50)
OPEN Cursor_Cli
FETCH NEXT FROM Cursor_Cli INTO @Cli, @Nome
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @Cli
PRINT @Nome
FETCH NEXT FROM Cursor_Cli INTO @Cli, @Nome
END
CLOSE Cursor_Cli
DEALLOCATE Cursor_Cli
+ %
.
$
# $ $%
1 #% $
Atributos de cursores implícitos: retornam
informação sobre o resultado da última
instrução INSERT, DELETE, UPDATE ou
SELECT executada.
São acedidos através de variáveis globais:
@@ROWCOUNT retorna o número de registos
afectados por uma instrução INSERT, DELETE,
UPDATE ou SELECT
@@ERROR o número do erro da última instrução
executada, ou zero se não existir.
#
Sintaxe básica:
CREATE PROC[EDURE] procedure_name [;number]
[
{@parameter data_type} [VARYING] [= default]
[OUTPUT]
]
[,...n]
AS
sql_statement [...n]
#
91
CREATE PROC Cli @CodCli int=NULL,
@InicioNome Varchar(40)
AS
BEGIN
SELECT * FROM CLIENTES
WHERE ClienteID > @CodCli
AND Nome LIKE (@InicioNome+'%')
END
Para invocar a procedure
EXEC Cli 2, 'A'
OU
EXEC Cli @InicioNome = 'A', @CodCli=2
"
#
91
ALTER PROC Cli @CodCli int, @InicioNome Varchar(40),
@TotalReg int OUTPUT
AS
BEGIN
SELECT @TotalReg = COUNT(*)
FROM CLIENTES
WHERE ClienteID > @CodCli
AND Nome LIKE (@InicioNome+'%')
END
Para invocar a procedure
DECLARE @Tot int
EXEC Cli 1, 'A', @TotalReg = @Tot OUTPUT
PRINT 'Numero de registos = ' + cast(@Tot AS Varchar(10))
&
3 #% $
A utilização de funções é vantajosa em
determinadas circunstâncias :
Podem ser utilizados para encapsular e centralizar a
lógica das regras de negócio.
Podem ser utilizadas numa query, contrariamente as
Stored procedures e podem ser passados parâmetros,
contrariamente às Views.
Nos casos em que o resultado é uma tabela, esta pode
ser usado na cláusula FROM como uma tabela normal.
Nas situações em que o resultado é um único valor, as
funções podem ser usadas na cláusula SELECT como
uma função normal do SQL. Pode ser usada, por
exemplo, para cálculos de valores ou formatação.
)
3 #% $
No T-SQL existem 3 tipos de funções:
1.
Scalar Functions - São funções que retornam unicamente um
valor. Exemplo:
CREATE FUNCTION QtStock (@Qt Smallint)
RETURNS Smallint
AS
BEGIN
IF @QT<0
SET @Qt = 0
RETURN @Qt
END
Para invocar :
SELECT dbo.QtStock(-3)
3 #% $
2.
Inline Table - São funções que têm como
resultado uma tabela de valores que é resultado
de uma única instrução de select. Exemplo:
CREATE FUNCTION AnivMes (@Mes Tinyint)
RETURNS TABLE
AS
RETURN (SELECT ClienteID, Nome
FROM Clientes
WHERE Month(DataNasc) = @Mes)
Para invocar:
SELECT Nome From AnivMes(1)
3 #% $
3.
Multi-statement Table - São funções que retornam tabelas definidas
e trabalhadas pelo utilizador . Exemplo:
CREATE FUNCTION ArtVendasAno (@Ano Int)
RETURNS @VAno TABLE (ArtigoID int,
Descricao Nvarchar(40),
Vendas Int) AS
BEGIN
INSERT @VAno
SELECT ArtigoID, Descricao, (Select SUM(Quant) FROM Facts, linhasFact
FROM Artigos
RETURN
END
Para invocar:
WHERE LinhasFact.ArtigoID = Artigos.ArtigoID
AND LinhasFact.FactID = Facts.FactID
AND @Ano = YEAR(Data))
SELECT * FROM ArtVendasAno(2006) WHERE Vendas > 1