Aula Lab Function

Prévia do material em texto

LPBD - Functions 
- Além das funções já disponibilizadas pelo SQL Server, como COUNT, 
AVG, YEAR ou GETDATE, o SGBD também permite que o usuário crie 
suas próprias funções (UDFs – User Defined Functions). 
 
- No SQL Server, as UDFs tipicamente recebem parâmetros de entrada e 
devem obrigatoriamente retornar algum valor, o qual pode ser de 3 tipos: 
- Scalar: Retorna um único valor, como um inteiro, uma string, uma data,.. 
- Inline Table-Valued: Retorna um conjunto de linhas e colunas (Resultset), 
gerado a partir de um único comando SELECT em tabelas ou views do BD 
- Multi-statement Table-valued: Define explicitamente a estrutura da tabela 
que será retornada, com seus nomes de colunas e seus tipos de dados 
SINTAXE: 
 
 
EXEMPLOS 
1 – Função escalar que retorna o nome da UF a partir da SIGLA 
 
 
Chamada da função 
 
Resultados 
 
2 – Função escalar que calcula o maior salário por departamento 
Tabelas: LPBD RH – Funcionários 
create FUNCTION maiorSalario (@cod_depto int) 
RETURNS float 
as 
BEGIN 
 DECLARE @maiorSal float 
 SELECT @maiorsal = max(salario) FROM funcionarios 
 where departamento_id = @cod_depto 
 RETURN @maiorSal 
END 
Chamada da função 
SELECT dbo.maiorSalario (10) 
3 – Função inline table valued que retorna o Resultset (sobrenome do 
funcionario, o salario, o cargo, o nome do departamento e o nome da cidade). O 
parâmetro de entrada deverá ser o nome da cidade. 
create FUNCTION func_cid (@cid varchar(30)) 
RETURNS table 
as 
RETURN 
(select f.sobre_nome, f.salario, f.cargo, 
d.departamento_nome, l.cidade 
from funcionarios f, departamentos d, locais l 
where f.departamento_id = d.departamento_id and 
d.local_id = l.local_id and l.cidade = @cid) 
Chamada da função 
select * from dbo.func_cid('Oxford') 
4 – Função multi-statement table valued que retorna uma Tabela (sobrenome 
do funcionário, o salário, o cargo, a data de admissão e o nome do departamento). O 
parâmetro de entrada deverá ser a data de admissão. 
create FUNCTION func_dt (@dt date) 
RETURNS @tbl_data table 
(sobrenome varchar(10), salario float, cargo 
varchar(10), 
data_adm date, depto varchar(30)) 
as 
begin 
insert into @tbl_data 
select f.sobre_nome, f.salario, f.cargo, f.dt_admiss, 
d.departamento_nome from funcionarios f,departamentos d 
where f.departamento_id = d.departamento_id 
and f.dt_admiss = @dt 
RETURN 
end 
Chamada da função 
select * from dbo.func_dt('1994-06-07') 
 
5 - Função escalar que verifica se um CPF (CHAR(11) sem o traço) e´ valido. A 
saída deve ser uma mensagem (VALIDO ou INVALIDO). 
 
create FUNCTION cpf (@cpf char(11)) 
RETURNS varchar(8) 
as 
BEGIN 
DECLARE @valido varchar(8), @d1 int, @d2 int, @d3 int, 
@d4 int, @d5 int, @d6 int, @d7 int, @d8 int, @d9 int, 
@d10 int, @d11 int, @s1 int, @s2 int, @r1 int, @r2 int 
set @d1 = cast(substring(@cpf,1,1) as int) 
set @d2 = cast(substring(@cpf,2,1) as int) 
set @d3 = cast(substring(@cpf,3,1) as int) 
set @d4 = cast(substring(@cpf,4,1) as int) 
set @d5 = cast(substring(@cpf,5,1) as int) 
set @d6 = cast(substring(@cpf,6,1) as int) 
set @d7 = cast(substring(@cpf,7,1) as int) 
set @d8 = cast(substring(@cpf,8,1) as int) 
set @d9 = cast(substring(@cpf,9,1) as int) 
set @d10 = cast(substring(@cpf,10,1) as int) 
set @d11 = cast(substring(@cpf,11,1) as int) 
set @s1 = @d1 * 10 + @d2 * 9 + @d3 * 8 + @d4 * 7 + @d5 
* 6 + @d6 * 5 + @d7 * 4 + @d8 * 3 + @d9 * 2 
set @r1 = ((@s1 * 10)%11) 
IF @r1 = 10 
 set @r1 = 0 
set @s2 = @d1 * 11 + @d2 * 10 + @d3 * 9 + @d4 * 8 + @d5 
* 7 + @d6 * 6 + @d7 * 5 + @d8 * 4 + @d9 * 3 + @d10 * 2 
set @r2 = ((@s2 * 10)%11) 
IF @r2 = 10 
 set @r2 = 0 
 
IF ((@r1 = @d10) AND (@r2 = @d11)) 
 set @valido = 'Valido' 
ELSE 
 set @valido = 'Invalido' 
RETURN @valido 
END 
Chamada da função 
SELECT dbo.cpf('28089312802') 
 
SELECT dbo.cpf('12345678910') 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
LPBD - AULA 10 – FUNCTIONS 
Instruções: 
1. Entrar no SGBD (SQL SERVER). 
2. Colocar em uso no MySQL o Banco de Dados LPBDRH 
3. Crie as functions abaixo em SQL: 
a. Função inline table valued que retorna o Resultset (descrição 
do cargo, número de funcionários). O parâmetro de entrada deverá ser 
o código do cargo. 
 
b. Função multi-statement table valued que retorna a Tabela 
FUNC (sobrenome do funcionário, o cargo, cidade e o nome do pais). O 
parâmetro de entrada deverá ser o nome do pais. 
 
c. Função para calcular o valor de uma prestação (PMT), de 
acordo com a fórmula abaixo (juros compostos). Os parâmetros de 
entrada deverão ser: o valor financiado (PV), a taxa de juros aplicada (i) 
e o número de prestações mensais (n). 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
LPBD - AULA 10 – FUNCTIONS 
Instruções: 
1. Entrar no SGBD (SQL SERVER). 
2. Colocar em uso no MySQL o Banco de Dados LPBDRH 
3. Crie as functions abaixo em SQL: 
a. Função inline table valued que retorna o Resultset (descrição 
do cargo, número de funcionários). O parâmetro de entrada deverá ser 
o código do cargo. 
 
b. Função multi-statement table valued que retorna a Tabela 
FUNC (sobrenome do funcionário, o cargo, cidade e o nome do pais). O 
parâmetro de entrada deverá ser o nome do pais. 
 
c. Função para calcular o valor de uma prestação (PMT), de 
acordo com a fórmula abaixo (juros compostos). Os parâmetros de 
entrada deverão ser: o valor financiado (PV), a taxa de juros aplicada (i) 
e o número de prestações mensais (n). 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
RESPOSTAS 
 
--a 
create FUNCTION cargo_func (@cid varchar(10)) 
RETURNS table 
as 
RETURN 
(select c.cargo_desc, count(*) as n_func 
from cargos c, funcionarios f 
where c.cargo_id = f.cargo 
and f.cargo = @cid 
group by c.cargo_desc) 
 
select * from dbo.cargo_func('TI_PROG') 
 
--b 
create FUNCTION func_pais (@pn varchar(40)) 
RETURNS @FUNC table 
(sobrenome varchar(10), cargo varchar(10), 
cidade varchar(30), pais varchar(40)) 
as 
begin 
insert into @FUNC 
select f.sobre_nome, f.cargo, l.cidade, p.pais_nome 
from funcionarios f, departamentos d, locais l, paises 
p 
where 
f.departamento_id = d.departamento_id and 
d.local_id = l.local_id and 
l.pais_id = p.pais_id 
and p.pais_nome = @pn 
RETURN 
end 
 
select * from dbo.func_pais('Canada') 
 
 
--c 
create FUNCTION pmt (@pv float, @i float, @n int) 
RETURNS float 
as 
BEGIN 
DECLARE @pmt float, @num float, @den float 
set @num = @i*POWER((1+@i),@n) 
set @den = POWER((1+@i),@n)-1 
set @pmt = @pv*(@num/@den) 
RETURN @pmt 
END 
 
SELECT dbo.pmt(40000,0.015,24)