Banco de Dados - Aula 14 - 01 de Novembro

SISTEMAS DE INFORMAÇÃO
Sistemas de Bancos de Dados
2º Semestre – 2010
Pedro Antonio Galvão Junior
E-mail: [email protected] Fone: 9531 - 7555
[email protected]
Versão 1.10.11 – Nov/2010.
Comandos Transact SQL (T-SQL)
Functions(Funções)
Funções
• User-Defined Functions, são funções definidas pelo próprio usuário, que não fazem parte do
conjunto de funções do SQL Server, essas funções podem retornar valores comuns ou mesmo um
valor no formato de uma tabela, com múltiplas ocorrências, existentes no mesmo banco de dados.
• Uma função pode ser definida com a utilização de múltiplos comandos em Transact-SQL, retornando
valores de acordo com o princípio para o qual foi desenvolvida. Dependendo da maneira como as
instruções são colocadas no corpo de uma função, a função poderá ser classificada como sendo
ESCALAR, INLINE ou MULTI-STATEMENT.
• Se o retorno da função for uma tabela que não corresponde à lista de colunas especificada, a função
é considerada INLINE. Uma função INLINE é uma função que retorna uma tabela, contendo em seu
corpo de definição apenas uma instrução SELECT. As colunas, incluindo os tipos de dados, da tabela a
ser retornada pela função, são derivadas da lista de campos da cláusula SELECT definida no corpo da
função.
• Se existe uma definição sobre a estrutura da tabela, bem como os seus campos com os respectivos
tipos de dados, a função será considerada como MULTI-STATEMENT. Dentro de uma function, não é
possível utilizar uma stored procedure.
Funções
• As instruções a seguir, correspondem as operações que podemos realizar no corpo de definição de
uma função MULTI-STATEMENT, o que não estiver nessa lista, não pode ser utilizado dentro de uma
função definida pelo usuário:
• Operações de atribuição:
- Instruções de controle de fluxo(WHILE,CASE,IF).
- Instruções de DECLARE para variáveis e cursores locais no escopo da função.
- Instruções de SELECT para fazer operações de atribuição em variáveis locais do escopo da função.
- Operações para manipulação de cursores locais na função, como abrir, fechar e etc.
- Apenas instruções FETCH que fazem atribuições em variáveis locais na função serão permitidas
usando a cláusula INTO; FETCH que retorne dados diretamente para um cliente não é possível.
INSERT, UPDATE, e DELETE que modifique uma variável local do tipo table.
- Além disso, não é possível utilizar nondeterministec functions no corpo de uma função.
Nondeterministec Functions são funções que sempre retornam valores diferentes cada vez que você
faz uma chamada, independente dos valores de entrada.
-
Funções
•Veja alguns exêmplos na tabela a seguir:
–@@CONNECTIONS;
–@@TOTAL_ERRORS;
–@@CPU_BUSY;
–@@TOTAL_READ;
–@@IDLE;
–@@TOTAL_WRITE;
–@@IO_BUSY GETDATE;
–@@MAX_CONNECTIONS GETUTCDATE;
–@@PACK_RECEIVED NEWID;
–@@PACK_SENT RAND;
–@@PACKET_ERRORS TEXTPTR;
–@@TIMETICKS.
Trabalhando com Functions
•
Toda função deve ser criada e utilizada com o objetivo de retornar informações para o
SQL Server, aplicativo ou usuário.
•
Existem algumas considerações que devemos tomar para definir, o deverá ser
retornando e de que forma este valor será retornado e apresentado.
•
Onde:
 RETURNS  Esta claúsula indica qual o tipo de dados ou conjunto de dados será retornado.
 RETURN  Finaliza a function e devolve o valor e o controle da transação para a claúsula
RETURNS.
Trabalhando com Functions
• Criando Function - InLine - Table Value:
Create Function F_Produtos (@Descricao VarChar(100))
Returns Table
1. Funções InLine não utilizando o
As
comandos BEGIN/END;
Return(
2. Funções InLine não podem utilizar o
comando ORDER BY;
Select * from MRP.Produtos
Where Descricao = @Descricao)
3. Utilizar
funções
InLine
como
parâmetros para as views;
Go
4.
• Executando:
Select * from F_Produtos ('Luvas')
Go
Utilizar Funções InLine em conjunto
com view indexadas.
Trabalhando com Functions
• Criando Function – Scalar:
Create Function F_SomarValores (@Numero Int), (@Numero2 Int)
Returns Int
1. A função Escalar(Scalar),
As
não retorna valores para
Begin
os tipos de dados: TEXT,
Declare @Total Int
NTEXT,
IMAGE
e
TIMESTAMP;
Set @Total=@Numero+@Numero2
Return (@Total)
End
Go
• Executando:
Select dbo.F_SomarValores (10)
Go
2. Além disso, não trabalha
com cursores;
3. Declarar o nome da
função em duas partes.
Trabalhando com Functions
• Criando Function - Multi - Statament - Table – Value:
Create Function F_ConsultarProdutos (@Codigo Int)
Returns @Tabela1 Table
(codigo int,
descricao varchar(10))
As
Begin
Insert @Tabela1
Select Codigo, Descricao from Produtos
Where Codigo = @Codigo
Return
End
Go
• Executando:
Select * from F_ConsultarProdutos (1)
Go
PRÁTICA - I
Revisão
 Functions.