SQL Server 2008 - Modulo 08 - Curso 6232

-- Aula 01 – Exemplo1

USE AdventureWorks

GO

CREATE FUNCTION Sales.SumSold(@ProductID int) RETURNS int

AS

BEGIN

            DECLARE @ret int

            SELECT @ret = SUM(OrderQty)

            FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID

            IF (@ret IS NULL)

                        SET @ret = 0

            RETURN @ret

END

-- Chamando uma Scalar Function

SELECT ProductID, [Name], Sales.SumSold(ProductID) AS SumSold

FROM Production.Product

ORDER BY 3 DESC

-- Query usada para criar a Scalar Function

SELECT ProductID, SUM(OrderQty) AS SumSold FROM Sales.SalesOrderDetail

GROUP BY ProductID

ORDER BY 2 DESC

--Aula 02 – Exemplo 2

--===================================================================

--Autor                        : Lineu Alves Lima Filho

--Data Criação            : 15/04/2010

--Descrição     : Módulo 08

--===================================================================

--Versão                      : 1.0

--===================================================================

--Versão                                 :

--Data Atualização     :

--===================================================================

USE [master]

GO

IF EXISTS         (SELECT * FROM sys.databases

                                    WHERE [name] = 'DB6232_fn')

DROP DATABASE DB6232_fn

GO

CREATE DATABASE DB6232_fn

GO

USE DB6232_fn

GO

CREATE TABLE vendas

(

            cod_venda                  INT                                          PRIMARY KEY,

            data_venda                SMALLDATETIME       NOT NULL,

            val_venda                   DECIMAL(9,2) NOT NULL       CHECK (val_venda > 0)

)

GO

INSERT INTO vendas VALUES (5,'2008-05-05 15:40:00',30.000),

(75,'2008-05-07 15:40:00',75.000), (189,'2008-05-08 15:40:00',125.000),

(70,'2008-05-12 15:40:00',55.000), (1001,'2008-05-15 15:40:00',23.000),

(19,'2008-05-19 15:40:00',10.000)

SELECT TOP 10 * FROM vendas

--------------------------------------------------------------

--                     Scalar DB6232_fn

--------------------------------------------------------------

-- Parâmetro 1: repete um determinado caractere, exemplo '0'

-- Parâmetro 2: quantas vezes o caractere repetirá, exemplo 10 vezes

SELECT REPLICATE ('0', 10)

SELECT LEN('SQLServer')

-- Criando a função

CREATE FUNCTION fn_LPAD (@caractere CHAR(1), @tamanho TINYINT, @texto VARCHAR(255))

RETURNS VARCHAR(50)  -- Tipo de dado que será retornado

AS

BEGIN  -- Necessário usar BEGIN - END bloco

      DECLARE @resultado VARCHAR(50)

      SET @resultado = REPLICATE(@caractere, @tamanho - LEN(@texto)) + @texto

      RETURN @resultado

END

-- Chamando a função

SELECT            dbo.fn_LPAD(0, 10, cod_venda) AS codigo

                        ,data_venda

                        ,val_venda

FROM vendas

--------------------------------------------------------------

--                     Inline Table-valued DB6232_fn

--------------------------------------------------------------

-- Criando a função

CREATE FUNCTION fn_vendas_codigo (@cod_venda SMALLINT)

RETURNS TABLE

AS

RETURN (SELECT * FROM vendas

                        WHERE cod_venda = @cod_venda)

-- Chamando a função

SELECT * FROM fn_vendas_codigo (5)

--------------------------------------------------------------

--                     Multi-statement Table-valued DB6232_fn

--------------------------------------------------------------

CREATE FUNCTION fn_vendas_report_data (@data SMALLDATETIME)

RETURNS @resultado_tabela TABLE

(cod_venda INT, data_venda SMALLDATETIME, val_venda MONEY)

AS

BEGIN

            IF @data > '2008/05/01'

                        INSERT INTO @resultado_tabela

                        SELECT cod_venda, data_venda, val_venda

                        FROM vendas

                        WHERE data_venda > @data

            RETURN

END

SELECT * FROM vendas

-- Chamando a função

-- 3 registros

SELECT * FROM fn_vendas_report_data ('2008-05-11')

-- 6 registros

SELECT * FROM fn_vendas_report_data ('2008-05-04')

-- 0 registros

SELECT * FROM fn_vendas_report_data ('2008-05-20')

-- Chamando 2 funções, 1 no SELECT e a outra no FROM

SELECT            dbo.fn_LPAD(0, 10, cod_venda) AS [Código]

                        ,data_venda AS [Data da Venda]

                        ,val_venda AS [Valor da Venda (R$)]

FROM fn_vendas_report_data ('2008-05-04')

--------------------------------------------------------------

--                     Obtendo informações sobre DB6232_fn

--------------------------------------------------------------

SELECT [name] AS Nome, [type_desc] AS Tipo, [definition] AS Codigo

FROM sys.sql_modules s

INNER JOIN sys.objects o

ON s.OBJECT_ID = o.OBJECT_ID

WHERE TYPE IN          ('FN', -- Scalar DB6232_fn

                                                'IF', -- Inline Table-valued DB6232_fn

                                                'TF') -- Multi-statement Table-valued DB6232_fn

--Script Função Escalar

--As funções escalares definidas pelo usuário retornam um valor único de

--dados do tipo definido na cláusula RETURNS. Para uma função escalar embutida,

--não há um corpo de função; o valor escalar é o resultado de uma única instrução.

--Para uma função escalar de várias instruções, o corpo da função, definido em

--um bloco BEGIN...END, contém uma série de instruções Transact-SQL,

--que retornam o valor único. O tipo de retorno pode ser qualquer tipo de dados,

--exceto text, ntext, image, cursore timestamp.

--Script Função Escalar 1

--FUNCAO ESCALAR

use master

go

create database ExemplosFuncoes

go

use LPAD

go

drop table lpad_Clientes

create table lpad_Clientes

(

            id int identity(1,1)

            ,timeCoracao varchar(20)

            ,primeiroNome varchar(50)

            ,ultimoNome varchar(50)

            ,cpf varchar(20)

            ,cep varchar(10)

)

select * from lpad_Clientes

insert into lpad_Clientes values

('Corinthians','Edson','Junior','22504777876','12214370'),('Sao Paulo','Samuel','Vitor','44567888986','12214456'),

('Sao Paulo','Ana','Caroline','44787622109','12215678')

select * from lpad_Clientes

--cria a funcao LPAD - Sql Server

-- Criando a função

--LPAD (@String, @Tamanho, @Char), onde:

--@String é a string que deve ser informada

--@Tamanho é o tamanho da string de retorno

--@Char é o caractere que será utilizado para preencher os dados à esquerda.

--Por exemplo:

--SELECT LPAD(‘25’, 4, 0) = 0025

CREATE FUNCTION fn_LPAD (@caractere CHAR(1), @tamanho TINYINT, @texto VARCHAR(255))

RETURNS VARCHAR(50)  -- Tipo de dado que será retornado

AS

BEGIN  -- Necessário usar BEGIN - END bloco

      DECLARE @resultado VARCHAR(50)

      SET @resultado = REPLICATE(@caractere, @tamanho - LEN(@texto)) + @texto

      RETURN @resultado

END

select dbo.fn_LPAD(0,5,id) as ID

from lpad_Clientes

--funcao para concatenar os nomes

create function fn_ConcatenaNomes(@primeiroNome varchar(50),@ultimoNome varchar(50))

returns varchar(100)

as

begin

            declare @resultado varchar(100)

            set @resultado = @primeiroNome + ' ' + @ultimoNome

            return @resultado

end

--usando a funcao fn_ConcatenaNomes

select dbo.fn_ConcatenaNomes(primeiroNome,ultimoNome) as nomeCompleto from lpad_Clientes

--usando as duas funcoes

select dbo.fn_LPAD(0,5,id) as ID,dbo.fn_ConcatenaNomes(primeiroNome,ultimoNome) as nomeCompleto from lpad_Clientes

--funcao para formatar CPF

create function fn_formatarCPF(@cpf char(11))                       

returns char(14)                

as              

begin                        

            declare @retorno varchar(14)                        

            set @retorno = substring(@cpf,1,3) + '.' + substring(@cpf,4,3) + '.' + substring(@cpf,7,3) + '-' + substring(@cpf,10,2) 

    return @retorno               

end

select dbo.fn_formatarCPF(cpf) as CPF_Formatado,cpf as CPF_NaoFormatado

from lpad_Clientes

--formatar cep

create function fn_formatarCEP(@cep varchar(8))

returns varchar(9)

as

begin

            declare @retorno varchar(9)

            set @retorno = substring(@cep,1,5) + '-' + substring(@cep,6,3)

            return @retorno

end

select dbo.fn_formatarCEP(cep) from lpad_Clientes

--If-else funcao escalar

alter function fn_VerificaTime(@silga varchar(20))

returns varchar(30)

as

begin

            declare @time varchar(50)

            select @time = timeCoracao from lpad_Clientes

            where timeCoracao = @silga

           

            if (@time = 'Corinthians') set @time =  'COR';

            else   set @time =  'SPO';

            return @time

end

select dbo.fn_VerificaTime(timeCoracao) from lpad_Clientes

--Script Função Escalar 2

use master

go

create database funcaoDB

go

use funcaoDB

go

IF OBJECT_ID('SalesHistory')>0  

       DROP TABLE SalesHistory; 

 CREATE TABLE [dbo].[SalesHistory]

 (        

       [SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,        

       [Product] [varchar](10) NULL,              

       [SaleDate] [datetime] NULL,              

       [SalePrice] [money] NULL

 ) 

 DECLARE @i SMALLINT

 SET @i = 1           

 WHILE (@i <=1000)

 BEGIN                          

       INSERT INTO SalesHistory(Product, SaleDate, SalePrice)                    

       VALUES ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))                 

       INSERT INTO SalesHistory(Product, SaleDate, SalePrice)        

       VALUES('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13))                      

       INSERT INTO SalesHistory(Product, SaleDate, SalePrice)            

       VALUES('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29))                             

       SET @i = @i + 1

 END 

 GO

--Script Função Escalar 3

CREATE FUNCTION dbo.udf_GetProductSales

 (

       @Product VARCHAR(10),

       @BeginDate DATETIME,

       @EndDate DATETIME

 )

 RETURNS MONEY

 AS

 BEGIN

       DECLARE @Sales MONEY

     

       SELECT @Sales = SUM(SalePrice)

       FROM SalesHistory

       WHERE

             Product = @Product AND

 SaleDate BETWEEN @BeginDate AND @EndDate 

       RETURN(@Sales)

 END

 SELECT dbo.udf_GetProductSales('PoolTable', '1/1/1990', '1/1/2000')

--Script Função Escalar 4

CREATE FUNCTION dbo.udf_GetProductSalesTable

 (

       @Product VARCHAR(10),

       @SaleID INT

 )

 RETURNS @SalesTable TABLE

 (

       SalesTotal MONEY,

       SalesCount INT

 ) 

 BEGIN

     

       INSERT INTO @SalesTable(SalesTotal, SalesCount)

       SELECT

             SUM(SalePrice), COUNT(SaleID)

       FROM

             SalesHistory

       WHERE

             Product = @Product AND

             SaleID <= @SaleID 

       RETURN

 END

 GO

 SELECT * FROM SalesHistory sh

 CROSS APPLY dbo.udf_GetProductSalesTable(sh.Product, sh.SaleID)

 ORDER BY sh.SaleID ASC

--Script Função Escalar 5

USE AdventureWorks2008;

GO

IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL

    DROP FUNCTION ufnGetInventoryStock;

GO

CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)

RETURNS int

AS

-- Returns the stock level for the product.

BEGIN

    DECLARE @ret int;

    SELECT @ret = SUM(p.Quantity)

    FROM Production.ProductInventory p

    WHERE p.ProductID = @ProductID

        AND p.LocationID = '6';

     IF (@ret IS NULL)

        SET @ret = 0;

    RETURN @ret;

END;

GO

SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply

FROM Production.Product

WHERE ProductModelID BETWEEN 75 and 80;

GO

--Table Value

--As funções com valor de tabela definidas pelo usuário retornam um tipo de

--dados table. Para uma função com valor de tabela embutida, não há um corpo de

--função; a tabela é o conjunto de resultados de uma única instrução SELECT.

--Table Value 1

USE AdventureWorks2008;

GO

IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL

    DROP FUNCTION Sales.ufn_SalesByStore;

GO

CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)

RETURNS TABLE

AS

RETURN

(

    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'

    FROM Production.Product AS P

    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID

    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID

    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID

    WHERE C.StoreID = @storeid

    GROUP BY P.ProductID, P.Name

);

GO

SELECT * FROM Sales.ufn_SalesByStore (602);

--Table Value 2

use master

go

create database funcaoDB

go

use funcaoDB

go

drop table estoqueDB

create table estoqueDB

(

            ProductID int

            ,ProductName varchar(50)

            ,unitsInStock int

            ,unitPrice decimal(18,2)

            ,Discontinued int

)

--alimentado a tabela

insert into estoqueDB values

(1,'Bolacha',10,0.80,1),(2,'Macarrao',9,0.80,1),(3,'Creme Dental',0,1.88,0)

select * from estoqueDB

CREATE Function ms_RetornaProducts(@Valor decimal(18,2)=NULL,@Descontinuado bit=NULL)

RETURNS @TabelaProduto table(Codigo INT PRIMARY KEY,Nome varchar(50),Estoque varchar(50),obs varchar(50))

AS

BEGIN

     IF @Valor IS NOT NULL

       INSERT INTO @TabelaProduto

           SELECT ProductID,ProductName,unitsInStock,case when unitsInstock<10 then 'Estoque baixo'

           else 'Estoque ok'   end FROM dbo.estoqueDB WHERE unitPrice>@Valor

     ELSE IF @Descontinuado=1

           INSERT INTO @TabelaProduto

           SELECT ProductID,ProductName,'Sem estoque',case when unitsInstock<5 then 'Possui estoque'

           else 'limpar estoque' end FROM dbo.estoqueDB WHERE Discontinued=@Descontinuado

  RETURN

END

select * from estoqueDB

SELECT * FROM dbo.ms_RetornaProducts(0.00,null)

--Table Value3

USE AdventureWorks2008R2;

GO

CREATE VIEW CustomersByRegion

AS

SELECT DISTINCT S.Name AS Store, A.City

FROM Sales.Store AS S

    JOIN Sales.BusinessEntityAddress AS BEA ON BEA.BusinessEntityID = S.BusinessEntityID

    JOIN Person.Address AS A ON A.AddressID = BEA.AddressID

    JOIN Person.StateProvince SP ON

        SP.StateProvinceID = A.StateProvinceID

WHERE SP.Name = N'Washington';

GO

USE AdventureWorks2008R2;

GO

IF OBJECT_ID(N'Sales.ufn_CustomerNamesInRegion', N'IF') IS NOT NULL

    DROP FUNCTION Sales.ufn_CustomerNamesInRegion;

GO

CREATE FUNCTION Sales.ufn_CustomerNamesInRegion

                 ( @Region nvarchar(50) )

RETURNS table

AS

RETURN (

        SELECT DISTINCT s.Name AS Store, a.City

        FROM Sales.Store AS s

        INNER JOIN Person.BusinessEntityAddress AS bea

            ON bea.BusinessEntityID = s.BusinessEntityID

        INNER JOIN Person.Address AS a

            ON a.AddressID = bea.AddressID

        INNER JOIN Person.StateProvince AS sp

            ON sp.StateProvinceID = a.StateProvinceID

        WHERE sp.Name = @Region

       );

GO

-- chamando a funcao

SELECT *

FROM Sales.ufn_CustomerNamesInRegion(N'Washington')

ORDER BY City;

GO

--Script Task 1

-- TASK 1

-- Desenvolver uma Function para somar dois valores

-- TASK 2

-- Desenvolver uma Function para somar o valor total de cada produto em estoque

CREATE TABLE estoque

(

            codigo int                               identity,

            nome   char(30),

            qtda     int,

            valor    dec(9,2)

)

INSERT INTO estoque (nome, qtda, valor) VALUES ('Batata', 10, 1.09)

INSERT INTO estoque (nome, qtda, valor) VALUES ('Cenoura', 10, 1.12)

INSERT INTO estoque (nome, qtda, valor) VALUES ('Sabão em Pó', 5, 5.99)

INSERT INTO estoque (nome, qtda, valor) VALUES ('Amaciante', 8, 8.78)

INSERT INTO estoque (nome, qtda, valor) VALUES ('Sabonete', 3, 1.09)

INSERT INTO estoque (nome, qtda, valor) VALUES ('Shampoo', 9, 6.82)

select * from estoque

--Script Verificando Diferenças

CREATE TABLE dbo.Parent

(ParentId int identity(1,1)

,ParentDate datetime)

CREATE TABLE dbo.Child

(ChildId int identity(1,1)

,ParentId int

,ChildDate datetime)

DECLARE @i int

DECLARE @j int

SET @i = 1

SET @j = 1

WHILE @i < 100

BEGIN

INSERT INTO dbo.Parent

(ParentDate)

SELECT GETDATE()

WHILE @j < 100

BEGIN

INSERT INTO dbo.Child

(ParentId

,ChildDate)

SELECT @i

,GETDATE()

SET @j = @j + 1

END

SET @i = @i + 1

END

select * from Parent

select * from Child

CREATE VIEW dbo.vJoin

AS

SELECT p.ParentId

,p.ParentDate

,c.ChildId

,C.ChildDate

FROM dbo.Parent p

JOIN dbo.Child c

ON p.ParentId = c.ParentId

select * from dbo.vJoin

CREATE FUNCTION dbo.SingleUDF ()

RETURNS TABLE

AS

RETURN

(

SELECT p.ParentId

,p.ParentDate

,c.ChildId

,C.ChildDate

FROM dbo.Parent p

JOIN dbo.Child c

ON p.ParentId = c.ParentId

)

select * from SingleUDF()

CREATE Function dbo.MultiUDF()

RETURNS @Multi TABLE

(ParentId int

,ParentDate datetime

,ChildId int

,ChildDate datetime)

AS

BEGIN

INSERT INTO @Multi

(ParentId

,ParentDate

,ChildId

,ChildDate)

SELECT p.ParentId

,p.ParentDate

,c.ChildId

,C.ChildDate

FROM dbo.Parent p

JOIN dbo.Child c

ON p.ParentId = c.ParentId

RETURN

END

select * from MultiUDF()

set statistics time on

select * from vJoin

select * from SingleUDF()

select * from MultiUDF()

set statistics time off

--Script Verificando Diferenças 2

USE [master]

GO

IF EXISTS         (SELECT * FROM sys.databases

                                    WHERE [name] = 'DB6232_fn')

DROP DATABASE DB6232_fn

GO

CREATE DATABASE DB6232_fn

GO

USE DB6232_fn

GO

CREATE TABLE vendas

(

            cod_venda                  INT                                          PRIMARY KEY,

            data_venda                SMALLDATETIME       NOT NULL,

            val_venda                   DECIMAL(9,2) NOT NULL       CHECK (val_venda > 0)

)

GO

INSERT INTO vendas VALUES (5,'2008-05-05 15:40:00',30.000),

(75,'2008-05-07 15:40:00',75.000), (189,'2008-05-08 15:40:00',125.000),

(70,'2008-05-12 15:40:00',55.000), (1001,'2008-05-15 15:40:00',23.000),

(19,'2008-05-19 15:40:00',10.000)

SELECT TOP 10 * FROM vendas

--------------------------------------------------------------

--                     Inline Table-valued DB6232_fn

--------------------------------------------------------------

-- Criando a função

CREATE FUNCTION fn_vendas_codigo (@cod_venda SMALLINT)

RETURNS TABLE

AS

RETURN (SELECT * FROM vendas

                        WHERE cod_venda = @cod_venda)

-- Chamando a função

SELECT * FROM fn_vendas_codigo (5)

--------------------------------------------------------------

--                     Multi-statement Table-valued DB6232_fn

--------------------------------------------------------------

CREATE FUNCTION fn_vendas_report_data (@data SMALLDATETIME)

RETURNS @resultado_tabela TABLE

(cod_venda INT, data_venda SMALLDATETIME, val_venda MONEY)

AS

BEGIN

            IF @data > '2008/05/01'

                        INSERT INTO @resultado_tabela

                        SELECT cod_venda, data_venda, val_venda

                        FROM vendas

                        WHERE data_venda > @data

            RETURN

END

SELECT * FROM vendas

-- Chamando a função

-- 3 registros

SELECT * FROM fn_vendas_report_data ('2008-05-11')

-- 6 registros

SELECT * FROM fn_vendas_report_data ('2008-05-04')

-- 0 registros

SELECT * FROM fn_vendas_report_data ('2008-05-20')

--------------------------------------------------------------

--                     Obtendo informações sobre DB6232_fn

--------------------------------------------------------------

SELECT [name] AS Nome, [type_desc] AS Tipo, [definition] AS Codigo

FROM sys.sql_modules s

INNER JOIN sys.objects o

ON s.OBJECT_ID = o.OBJECT_ID

WHERE TYPE IN          ('FN', -- Scalar DB6232_fn

                                                'IF', -- Inline Table-valued DB6232_fn

                                                'TF') -- Multi-statement Table-valued DB6232_fn