SISTEMAS DE INFORMAO Sistemas de Bancos de Dados

SISTEMAS DE INFORMAÇÃO Sistemas de Bancos de Dados 2º Semestre – 2010 Pedro Antonio Galvão Junior E-mail: pedrogalvaojunior@gmail. com Fone: 9531 - 7555 pedro. junior@aes. edu. br 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 - In. Line - Table Value: Create Function F_Produtos (@Descricao Var. Char(100)) Returns Table 1. Funções In. Line não utilizando o As comandos BEGIN/END; Return( 2. Funções In. Line não podem utilizar o comando ORDER BY; Select * from MRP. Produtos Where Descricao = @Descricao) 3. Utilizar funções In. Line como parâmetros para as views; Go 4. • Executando: Select * from F_Produtos ('Luvas') Go Utilizar Funções In. Line em conjunto com view indexadas.

Trabalhando com Functions • Criando Function – Scalar: Create Function F_Somar. Valores (@Numero Int), (@Numero 2 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+@Numero 2 Return (@Total) End Go • Executando: Select dbo. F_Somar. Valores (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_Consultar. Produtos (@Codigo Int) Returns @Tabela 1 Table (codigo int, descricao varchar(10)) As Begin Insert @Tabela 1 Select Codigo, Descricao from Produtos Where Codigo = @Codigo Return End Go • Executando: Select * from F_Consultar. Produtos (1) Go

PRÁTICA - I

Revisão § Functions.