Apresentação do PowerPoint

Programação de Banco de Dados
Funções
31/05/2017
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.
31/05/2017
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.
31/05/2017 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.
31/05/2017
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).
31/05/2017
José Antônio da Cunha - CEFET - RN
5
Funções
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.
31/05/2017
José Antônio da Cunha - CEFET - RN
6
Funções
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.
31/05/2017
José Antônio da Cunha - CEFET - RN
7
Funções
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.
31/05/2017
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.
31/05/2017
José Antônio da Cunha - CEFET - RN
9
Funções
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.
31/05/2017
José Antônio da Cunha - CEFET - RN
10
Funções
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 }
31/05/2017
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_AreaTriangulo (@base smallint, @altura smallint)
RETURNS int
AS
BEGIN
RETURN ( (@base * @altura) / 2 )
END
** Para executar a função **
SELECT dbo.F_AreaTriangulo(5,30)
31/05/2017
José Antônio da Cunha - CEFET - RN
12
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_AreaCirculo (@raio int)
RETURNS Bigint
AS
BEGIN
DECLARE @area Bigint
SET @area = PI() * POWER(@raio,2)
RETURN @area
END
31/05/2017
José Antônio da Cunha - CEFET - RN
13
Funções
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 [ ) ]
31/05/2017
José Antônio da Cunha - CEFET - RN
14
Funções
Exemplo
De acordo com a tabela Funcionário e seus dados apresentados em seguida, a função
F_DataCadastro() 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
31/05/2017
José Antônio da Cunha - CEFET - RN
15
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
)
31/05/2017
José Antônio da Cunha - CEFET - RN
16
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:00.000',1)
funcionario values(2,'Chuchu','M',3000,
00:00:00.000',1)
funcionario values(3,'Bacana','M',2000,
00:00:00.000',2)
funcionario values(4,'Espeto','M',2500,
00:00:00.000',2)
funcionario values(5,'Batatinha','F',4000,
00:00:00.000',3)
insert
insert
insert
insert
Regiao
Regiao
Regiao
Regiao
into
into
into
into
values
values
values
values
(1,'Norte')
(2,'Sul')
(3,'Leste')
(4,'Oeste')
31/05/2017
José Antônio da Cunha - CEFET - RN
17
Funções
Exemplo – solução
Create FUNCTION F_DataCadastro (@data smallDatetime)
RETURNS TABLE
AS
RETURN (SELECT * FROM dbo.Funcionario
WHERE Data_Func = @data)
** Para executar a função F_DataCadastro **
SELECT * FROM F_DataCadastro(’01/01/98’)
** Fazendo um JOIN com o resultado da função F_DataCadastro **
select F.*, Regiao.Regiao
from F_DataCadastro('01/01/98') as F, Regiao
where F.Num_Regiao = Regiao.Num_Regiao
31/05/2017
José Antônio da Cunha - CEFET - RN
18
Funções
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 | Constraint } [,...n ])
31/05/2017
José Antônio da Cunha - CEFET - RN
19
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
31/05/2017
José Antônio da Cunha - CEFET - RN
20
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)
31/05/2017
José Antônio da Cunha - CEFET - RN
21
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.
31/05/2017
José Antônio da Cunha - CEFET - RN
22
Funções
Create FUNCTION F_Func2 ( )
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
31/05/2017
José Antônio da Cunha - CEFET - RN
23
Funções
** Código que executa a função ***
SELECT * FROM dbo.F_Func2( )
Para testar a função, crie os usuários seguintes e atribua as permissões:
Sp_addlogin 'Ana','abc123_'
Sp_addlogin 'George','abc123_'
Sp_GrantDBAccess 'Ana'
Sp_GrantDBAccess 'George'
Grant SELECT ON F_Func2 TO Ana
Grant SELECT ON F_Func2 TO George
31/05/2017
José Antônio da Cunha - CEFET - RN
24
Funções
Em seguida, conecte-se como Ana, depois como Maria, José, Rita e João e execute
a função F_Func2() da seguinte maneira:
SELECT * FROM dbo.F_Func2 ()
É 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_Func2() as F, Funcionario
WHERE F.Nome_Func = Funcionario.Nome_Func
31/05/2017
José Antônio da Cunha - CEFET - RN
25
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.
31/05/2017
José Antônio da Cunha - CEFET - RN
26
Funções
A opção “With SchemaBinding”
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.
31/05/2017
José Antônio da Cunha - CEFET - RN
27
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()
31/05/2017
José Antônio da Cunha - CEFET - RN
28
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
31/05/2017
José Antônio da Cunha - CEFET - RN
29