Programao de Banco de Dados Funes 3122021 Jos

Programação de Banco de Dados Funções 3/12/2021 José Antônio da Cunha - CEFET - RN 1

Funções O que é uma função É um tipo de programa, criado no Servidor SQL Server, dentro de um database específico, que tem como principal objetivo retornar um valor ou um conjunto de valores para um usuário ou para outra função, para store procedure ou aplicação. 3/12/2021 José Antônio da Cunha - CEFET - RN 2

Funções Regras para a utilização de User Defined Functions(UDFs) 1. Uma função definida pelo usuário pode receber parâmetros de entrada. 2. Os parâmetros de entrada de uma função não podem ser do datatype timestamp, cursor ou table. 3. Uma função definida pelo usuário não aceita parâmetros de saída (parâmetro output). 4. Uma função retorna um valor escalar ou os dados de uma tabela. 3/12/2021 José Antônio da Cunha - CEFET - RN 3

Funções Regras para a utilização de User Defined Functions(UDFs) 5. Dentro de uma UDF você não pode executar os comandos INSERT, UPDATE e DELETE. 6. Os comandos INSERT, UPDATE e DELETE podem ser utilizados para manipular dados de variáveis de memória do tipo tabela (table). 7. Para que um usuário possa executar uma UDF, ele deve ter permissão de SELECT nela. 3/12/2021 José Antônio da Cunha - CEFET - RN 4

Funções Regras para a utilização de User Defined Functions(UDFs) 8. Para executar uma função, você deve utilizar o comando SELECT e o nome qualificado por duas partes, ou seja, deve utilizar o nome do proprietário da função antes do nome dela para executá-la. 9. As UDFs não podem executar as funções determinísticas internas do SQL Server (Funções Built-in). 3/12/2021 José Antônio da Cunha - CEFET - RN 5

Funções determinísticas Função Significado @@ERROR É uma variável global, também chamada de função, que retorna o erro ocorrido. @@IDENTITY É uma variável global, também chamada de função, que contém o último valor incrementado por uma propriedade identity em uma sessão. @@ROWCOUNT É uma variável global, também chamada de função, que contém o número de linhas de uma tabela afetadas pelo último comando SQL na sessão. @@CONNECTIONS Retorna o número de conexões ou de tentativas de conexões feitas no SQL Server desde a última vez que o SQL Server foi inicializado. @@TRANCOUNT Retorna o número de transações abertas na conexão corrente. 3/12/2021 José Antônio da Cunha - CEFET - RN 6

Funções determinísticas @@TOTAL_READ Retorna o número total de leituras feitas pelo SQL Server desde o último startup. @@TOTAL_WRITE Retorna o número total de gravações feitas pelo SQL Server desde o último startup. APP_NAME() Retorna o nome da aplicação da sessão corrente. CURRENT_TIMESTAMP Retorna a data e a hora corrente do sistema. Equivalente a Getdate(). CURRENT_USER() Retorna o nome do usuário corrente. Equivalente à função User_Name() ou a User. DATENAME() Retorna o nome de uma determinada parte de uma data enviada como parâmetro por essa função. GETANSINULL() Retorna a nulabilidade default para o database na sessão. GETDATE() Retorna a data e a hora do sistema. 3/12/2021 José Antônio da Cunha - CEFET - RN 7

Funções determinísticas @@IDENTITY Retorna o último valor incrementado em um identity em uma sessão. NEWID() Gera um valor único do datatype unique identifier. PERMISSIONS Retorna um valor que indica as permissões do usuário corrente. RAND() Gera um número randômico. SESSION_USER Retorna o nome do usuário corrente. STATS_DATE Retorna a data em que a estatística de um determinado índice foi atualizado pela última vez. SYSTEM_USER Retorna o login do usuário corrente. TEXTPTR Retorna um ponteiro para as colunas text, ntext ou image para ser utilizado nos comandos READTEXT, WRITETEXT e UPDATETEXT. TEXTVALID Verifica a validade de um ponteiro para as colunas text, ntext ou image. USER_NAME Retorna o nome do usuário corrente. 3/12/2021 José Antônio da Cunha - CEFET - RN 8

Funções Tipos de User Defined Functions (UDF) Você pode criar três tipos de funções: • Funções escalares; • Funções in-line: contêm apenas um comando SELECT para retornar os dados de uma tabela; • Funções que contêm vários comandos e retornam dados de uma tabela. 3/12/2021 José Antônio da Cunha - CEFET - RN 9

Funções Escalares Uma função escalar é semelhante a uma função interna do SQL Server (Built_in Function). Elas podem ou não receber parâmetros de entrada e retornar apenas um único valor. 3/12/2021 José Antônio da Cunha - CEFET - RN 10

Funções Escalares - Sintaxe Create FUNCTION [Proprietário_da_função]. Nome_da_Função ([{@parametro [AS] datatype_escalar [=default ] } [, . . . n]]) RETURNS [WITH <opção> [ [, ]. . . n] ] [AS] BEGIN Corpo da Função RETURN Valor_escalar END < opção > : : = { ENCRYPTION | SCHEMABINDING } 3/12/2021 José Antônio da Cunha - CEFET - RN 11

Funções Exemplo de Função Escalar A função seguinte tem como objetivo calcular a área de um triângulo mediante os valores recebidos pelos parâmetros de entrada @base e @altura. Create FUNCTION F_Area. Triangulo (@base int, @altura int) RETURNS float AS BEGIN RETURN ( (@base * @altura) / 2 ) END ** Para executar a função ** SELECT dbo. F_Area. Triangulo(5, 30) 3/12/2021 José Antônio da Cunha - CEFET - RN 12

Funções Comandos para criar e popular a tabela Triangulos CREATE TABLE [dbo]. [Triangulos]( [id] [varchar](50) NOT NULL, [base] [int] NULL, [altura] [nchar](10) NULL ) insert into Triangulos values ('Triangulo Retangulo 3, 4', 3, 4) insert into Triangulos values ('Triangulo Retangulo 5, 8', 5, 8) ** Para executar a função ** select base, altura, from Triangulos dbo. f_areatriangulo(base, altura) as area José Antônio da Cunha - CEFET 3/12/2021 - RN 13

Funções Exemplo de Função Escalar – outro exemplo A função seguinte tem como objetivo calcular a área de uma circunferência mediante valores recebido pelo parâmetros de entrada @raio. Create FUNCTION F_Area. Circulo (@raio int) RETURNS Bigint AS BEGIN DECLARE @area Bigint SET @area = PI() * POWER(@raio, 2) RETURN @area END 3/12/2021 José Antônio da Cunha - CEFET - RN 14

Funções In-line que Retornam Dados de uma Tabela Create FUNCTION [Proprietário_da_função]. Nome_da_Função ([{@parametro [AS] datatype_escalar [=default ] } [, . . . n]]) RETURNS TABLE [WITH <opção > [ [, ]. . . n] [AS] RETURN [ ( ] Comando SELECT [ ) ] 3/12/2021 José Antônio da Cunha - CEFET - RN 15

Funções Exemplo De acordo com a tabela Funcionário e seus dados apresentados em seguida, a função F_Data. Cadastro() recebe uma data como parâmetro e retorna, da tabela Funcionário, todos os funcionários que foram cadastrados nessa data. Cod_Func Nome_Func Sexo_Func Sal_Func Data_Func Num_Regiao 1 Manda Chuva M 5000 01/01/98 1 2 Chuchu M 3000 01/01/99 1 3 Bacana M 2000 01/01/00 2 4 Espeto M 2500 01/01/01 2 5 Batatinha F 4000 01/01/02 3 3/12/2021 José Antônio da Cunha - CEFET - RN 16

Funções Comandos para criar as tabelas Funcionario e Regiao CREATE TABLE [Funcionario]( [Cod_Func] [int] NOT NULL, [Nome_Func] [varchar](100) NULL, [Sexo_Func] [char](1) NULL, [Sal_Func] [float] NULL, [Data_Func] [datetime] NULL, [Num_Regiao] [int] NULL ) CREATE TABLE [Regiao]( [Num_Regiao] [int] NOT NULL, [Regiao] [varchar](50) NULL ) 3/12/2021 José Antônio da Cunha - CEFET - RN 17

Funções Comandos para popular as tabelas Funcionario e Regiao insert into '1998 -01 -01 insert into '1999 -01 -01 insert into '2000 -01 -01 insert into '2001 -01 -01 insert into '2002 -01 -01 funcionario values(1, 'Manda Chuva', 'M', 5000, 00: 00. 000', 1) funcionario values(2, 'Chuchu', 'M', 3000, 00: 00. 000', 1) funcionario values(3, 'Bacana', 'M', 2000, 00: 00. 000', 2) funcionario values(4, 'Espeto', 'M', 2500, 00: 00. 000', 2) funcionario values(5, 'Batatinha', 'F', 4000, 00: 00. 000', 3) insert Regiao into values (1, 'Norte') (2, 'Sul') (3, 'Leste') (4, 'Oeste') 3/12/2021 José Antônio da Cunha - CEFET - RN 18

Funções Exemplo – solução Create FUNCTION F_Data. Cadastro (@data small. Datetime) RETURNS TABLE AS RETURN (SELECT * FROM dbo. Funcionario WHERE Data_Func = @data) ** Para executar a função F_Data. Cadastro ** SELECT * FROM F_Data. Cadastro(’ 01/01/98’) ** Fazendo um JOIN com o resultado da função F_Data. Cadastro ** select F. *, Regiao from F_Data. Cadastro('01/01/98') as F, Regiao where F. Num_Regiao = Regiao. Num_Regiao 3/12/2021 José Antônio da Cunha - CEFET - RN 19

Funções que contêm vários comandos e Retornam Dados de uma Tabela Create FUNCTION [Proprietário_da_função]. Nome_da_Função ([{@parametro [AS] datatype_escalar [=default ] } [, . . . n]]) RETURNS @Nome_Variável_Retornada TABLE <Definição da Estrutura da Tabela> [WITH <opção > [ [, ]. . . n] [AS] BEGIN Corpo_da_Função RETURN END < opções_da_Função > : : = {ENCRYPTION | SCHEMABINDING } <Definição da Estrutura da Tabela> : : = ( { Definição_da_Coluna |José Constraint } [, . . . n ]) Antônio da Cunha - CEFET 3/12/2021 - RN 20

Funções que contêm vários comandos e Retornam Dados de uma Tabela Alter FUNCTION Trigonometria ( @Angulo. Ini integer, @Angulo. Fim integer ) RETURNS @Func Table ( Angulo int not null, Seno float null, Cosseno float null, Tangente float null ) AS BEGIN declare @alfa integer 3/12/2021 José Antônio da Cunha - CEFET - RN 21

Funções que contêm vários comandos e Retornam Dados de uma Tabela set @alfa = @Angulo. Ini while @alfa <= @Angulo. Fim begin insert into @Func values ( @alfa, sin(@alfa*pi()/180), cos(@alfa*pi()/180), tan(@alfa*pi()/180) ) set @alfa = @alfa + 1 end return END 3/12/2021 José Antônio da Cunha - CEFET - RN 22

Funções Exemplo: a tabela “Usuario” a seguir é utilizada no próximo exemplo. User_Name Num_Regiao Dbo 10 Ana 1 Maria 2 José 3 Rita 4 João 1 3/12/2021 José Antônio da Cunha - CEFET - RN 23

Funções Exemplo: a tabela “Usuario” a seguir é utilizada no próximo exemplo. CREATE TABLE [dbo]. [Usuario]( [User_Name] [varchar](50) [Num_Regiao] [int] NULL ) NULL, insert into usuario values ('george', 1) insert into usuario values ('dbo', 10) insert into usuario values ('ana', 2) 3/12/2021 José Antônio da Cunha - CEFET - RN 24

Funções A função seguinte obtém o código da região do usuário corrente da tabela Usuario apresentada anteriormente e testa. Se o número da região for 10, significa que o usuário é o dbo (database owner) e pode ler todos os dados da tabela Funcionário. Se não, o usuário corrente, que deve existir na tabela Usuario, pode ler dados da tabela funcionario, mas apenas da região da qual ele recebeu permissão por meio do Número da região. Se o usuário corrente não estiver na tabela Usuario, a função retorna apenas as colunas da tabela Funcionario, mas sem valor algum. 3/12/2021 José Antônio da Cunha - CEFET - RN 25

Funções Create FUNCTION F_Func 2 ( ) RETURNS @Func Table ( Nome_Func varchar(100) not null, Sal_Func decimal(10, 2) not null ) AS BEGIN DECLARE @numreg tinyint SELECT @numreg = Num_Regiao FROM Usuario WHERE User_name = User IF @numreg IS NOT NULL AND @numreg <> 10 INSERT @Func SELECT Nome_Func, Sal_Func FROM Funcionario WHERE Num_regiao = @numreg ELSE IF @numreg = 10 INSERT @Func SELECT Nome_Func, Sal_Func FROM Funcionario RETURN END 3/12/2021 José Antônio da Cunha - CEFET - RN 26

Funções ** Código que executa a função *** SELECT * FROM dbo. F_Func 2( ) Para testar a função, crie os usuários seguintes e atribua as permissões: Sp_addlogin 'Ana', 'abc 123_' Sp_addlogin 'George', 'abc 123_' Sp_Grant. DBAccess 'Ana' Sp_Grant. DBAccess 'George' Grant SELECT ON F_Func 2 TO Ana Grant SELECT ON F_Func 2 TO George 3/12/2021 José Antônio da Cunha - CEFET - RN 27

Funções Em seguida, conecte-se como Ana, depois como Maria, José, Rita e João e execute a função F_Func 2() da seguinte maneira: SELECT * FROM dbo. F_Func 2 () É possível fazer JOIN com a tabela de resultado, da mesma forma apresentada nas funções in-line SELECT F. *, Funcionario. Data_Func FROM F_Func 2() as F, Funcionario WHERE F. Nome_Func = Funcionario. Nome_Func 3/12/2021 José Antônio da Cunha - CEFET - RN 28

Funções A opção “With Encryption” Use esta cláusula para criptografar o “código-fonte” das suas funções na tabela syscomments, da mesma forma como acontece com as views. 3/12/2021 José Antônio da Cunha - CEFET - RN 29

Funções A opção “With Schema. Binding” Esta cláusula indica que os objetos referenciados dentro da função não podem ter sua estrutura alterada e não podem ser eliminados. Esta cláusula pode ser colocada em uma função apenas se: • As funções e views referenciadas pela função em questão também tiverem sido criadas com a cláusula WITH SCHEMABINDING; • Os objetos utilizados pela função forem referenciados com o nome qualificado por duas partes; • O usuário que criar a função deve ter permissão de REFERENCES em todos os objetos referenciados pela função. 3/12/2021 José Antônio da Cunha - CEFET - RN 30

Funções Observação: Para executar funções internas do sistema (Built_in) que retornam dados de uma tabela, você deve usar dois pontos antes do nome da função. Veja: SELECT * FROM : : fn_helpcollations() 3/12/2021 José Antônio da Cunha - CEFET - RN 31

Funções Os comandos Create, Alter e Drop Function Como você observou anteriormente, para cria uma função utiliza-se o comando CREATE FUNCTION. Para alterar a estrutura de uma função sem perder as permissões já atribuídas aos usuários, você utiliza o comando ALTER FUNCTION e para eliminar uma função do database, utilize comando DROP FUNCTION. Fim 3/12/2021 José Antônio da Cunha - CEFET - RN 32