Diário de Informática: SQL Server 2008 - Modulo 07

-- Aula 01 -- Variaveis

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

-- Author: Lineu Alves Lima

-- Create Date: 20/04/2010

-- Description: Stored Procedure

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

-- Versão: 1.0

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

USE master

IF EXISTS (SELECT * FROM sys.databases

WHERE [name] = 'DB6232_SP')

DROP DATABASE DB6232_SP

CREATE DATABASE DB6232_SP

USE DB6232_SP

-- *********************************************************

-- *** Variáveis

-- *********************************************************

--SQL Server 2005

-- Declarando variáveis

DECLARE @Nome VARCHAR(50)

DECLARE @Val SMALLINT

-- Atribuindo valores as variáveis

SET @Nome = 'Maria'

SELECT @Val = 975

-- Vizualizando os valores das variáveis

PRINT @Nome

PRINT @Val

--SQL Server 2008

-- Declarando e atribuindo valores as variáveis

DECLARE @Nome VARCHAR(50) = 'Maria'

DECLARE @Val SMALLINT = 975

-- Vizualizando os valores das variáveis

PRINT @Nome

PRINT @Val

-- *********************************************************

-- *** Dados para teste

-- *********************************************************

CREATE TABLE empregados

(

id INT IDENTITY PRIMARY KEY,

nome VARCHAR(30) NOT NULL,

sexo CHAR(01) NOT NULL,

sal DEC(9,2) NOT NULL,

CONSTRAINT CH_test_sexo CHECK (sexo = 'F' OR sexo = 'M'),

CONSTRAINT CH_test_sal CHECK (sal >= 380)

)

SET NOCOUNT ON

INSERT INTO empregados VALUES

('Maria', 'F', 10000), ('Silvana', 'F', 5000),

('Julia', 'F', 2000), ('Lineu', 'M', 380),

('Mario', 'M', 8000), ('Henrique', 'M', 5000),

('Júlio', 'M', 9000), ('Marco', 'M', 8000)

SELECT * FROM empregados

-- *********************************************************

-- *** BEGIN, END, IF e ELSE

-- *********************************************************

IF (SELECT COUNT(*) FROM empregados) > 10

BEGIN

PRINT 'Esta tabela tem muitas linhas'

PRINT 'É a lista de clientes da empresa.'

END

ELSE

-- BEGIN --> Não é necessário neste caso, mas é uma boa prática usar.

PRINT 'Esta tabela tem poucas linhas'

-- END --> Não é necessário neste caso, mas é uma boa prática usar.

-- *********************************************************

-- *** CASE - WHEN - THEN - ELSE - END

-- *********************************************************

SELECT id

,UPPER(nome)

,CASE sexo

WHEN 'F' THEN 'Feminino'

ELSE

'Masculino'

END

,sal = CASE

WHEN sal = 380 THEN 'Salário Mínimo'

WHEN sal BETWEEN 380 AND 2000 THEN 'Classe C'

WHEN sal BETWEEN 2001 AND 5000 THEN 'Classe B'

ELSE

'Classe A'

END

FROM empregados

--Aula 02 - Store Procedure

-- *********************************************************

-- *** CREATE, ALTER and DROP Stored Procedures

-- *********************************************************

USE AdventureWorks

-- Criando uma Stored Procedure

CREATE PROC Production.LongLeadProducts

-- Ou

-- CREATE PROCEDURE Production.LongLeadProducts

SELECT Name, ProductNumber

FROM Production.Product

WHERE DaysToManufacture >= 1

-- Executando uma Stored Procedure

EXEC Production.LongLeadProducts

-- Alterando uma Stored Procedure

ALTER PROC Production.LongLeadProducts

SELECT Name, ProductNumber, DaysToManufacture

FROM Production.Product

WHERE DaysToManufacture >= 1

ORDER BY DaysToManufacture DESC, Name

-- Executando uma Stored Procedure

EXEC Production.LongLeadProducts

-- Verificando dependência

EXEC sp_depends 'Production.LongLeadProducts'

-- Deletando uma Stored Procedure

DROP PROC Production.LongLeadProducts

-- *********************************************************

-- *** Stored Procedures - Input Parameters

-- *********************************************************

ALTER PROC Production.LongLeadProducts

@MinimumLength INT = 1 -- default value

IF (@MinimumLength < 0) -- validate

BEGIN

RAISERROR('Valor Inválido.', 14, 1)

RETURN

END

SELECT Name, ProductNumber, DaysToManufacture

FROM Production.Product

WHERE DaysToManufacture >= @MinimumLength

ORDER BY DaysToManufacture DESC, Name

-- Chamando uma SP com valor DEFAULT

EXEC Production.LongLeadProducts

-- Chamando uma SP passando um valor como parâmetro

-- Passing by parameter name

EXEC Production.LongLeadProducts @MinimumLength = 2

-- Chamando uma SP passando um valor como parâmetro

-- Passing values by position

EXEC Production.LongLeadProducts 3

-- Validando o tratamento de erro da SP

EXEC Production.LongLeadProducts -22

-- Aula 03 Output Parameters and Return Values

-- **************************************************************

-- *** Stored Procedures - Output Parameters and Return Values

-- **************************************************************

USE AdventureWorks

CREATE PROC HumanResources.AddDepartment

@Name nvarchar(50), @GroupName nvarchar(50),

@DeptID smallint OUTPUT

INSERT INTO HumanResources.Department (Name, GroupName)

VALUES (@Name, @GroupName)

SET @DeptID = SCOPE_IDENTITY()

-- Chamando a SP passando parâmetros e retornando valores

DECLARE @dept int

EXEC HumanResources.AddDepartment 'Refunds', '', @dept OUTPUT

SELECT @dept

-- Alterando a SP para melhorar o tratamento de erro

ALTER PROC HumanResources.AddDepartment

@Name nvarchar(50), @GroupName nvarchar(50),

@DeptID smallint OUTPUT

IF ((@Name = '') OR (@GroupName = ''))

RETURN -1

INSERT INTO HumanResources.Department (Name, GroupName)

VALUES (@Name, @GroupName)

SET @DeptID = SCOPE_IDENTITY()

RETURN 0

-- Chamando a SP passando parâmetros e retornando valores

DECLARE @dept int, @result int

EXEC @result = HumanResources.AddDepartment 'Refunds2', 'e', @dept OUTPUT

IF (@result = 0)

SELECT @dept

ELSE

SELECT 'O registro não foi inserido.'

-- Aula 04 - Recompilando uma Store Procedure

-- **************************************************************

-- *** Recompilando Stored Procedures e Limpando o Cache

-- **************************************************************

USE AdventureWorks

EXEC sp_recompile 'Sales.Customer'

-- Removes all elements from the procedure cache.

DBCC FREEPROCCACHE

-- Ou

DBCC FREEPROCCACHE WITH NO_INFOMSGS

-- Aula 05 - Apagando tabela

USE DB6232_SP

-- Apagando tabela

DROP TABLE dbo.TabelaComPK

CREATE TABLE dbo.TabelaSemPK

(

ColA int,

ColB int

)

CREATE TABLE dbo.TabelaComPK

(

ColA int PRIMARY KEY,

ColB int

)

ALTER PROCEDURE dbo.Insere @a int, @b int

SET NOCOUNT ON

BEGIN TRY

INSERT dbo.TabelaSemPK VALUES (@a, @b)

INSERT dbo.TabelaComPK VALUES (@a, @b)

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER() ErrorNumber, ERROR_MESSAGE() [Message]

END CATCH

EXEC dbo.Insere 1, 1

EXEC dbo.Insere 2, 2

EXEC dbo.Insere 1, 3 -- violates the primary key

SELECT * FROM TabelaSemPK

SELECT * FROM TabelaComPK

--TRUNCATE TABLE TabelaSemPK

--TRUNCATE TABLE TabelaComPK

-- Aula 06 - Limpando as tabelas

USE DB6232_SP

-- Limpando as tabelas

TRUNCATE TABLE TabelaSemPK

TRUNCATE TABLE TabelaComPK

ALTER PROCEDURE dbo.Insere @a int, @b int

SET NOCOUNT ON

BEGIN TRY

BEGIN TRAN

INSERT TabelaSemPK VALUES (@a, @b)

INSERT TabelaComPK VALUES (@a, @b)

COMMIT TRAN

END TRY

BEGIN CATCH

ROLLBACK TRAN

SELECT ERROR_NUMBER() ErrorNumber, ERROR_MESSAGE() [Message]

END CATCH

EXEC dbo.Insere 1, 1

EXEC dbo.Insere 2, 2

EXEC dbo.Insere 1, 3 -- violates the primary key

SELECT * FROM TabelaSemPK

SELECT * FROM TabelaComPK

-- Aula 09 - Trabalhando com RAISERROR

-- *********************************************************

-- *** Trabalhando com RAISERROR

-- *********************************************************

USE AdventureWorks

DECLARE @DBID INT

SET @DBID = DB_ID()

DECLARE @DBNAME NVARCHAR(128)

SET @DBNAME = DB_NAME()

RAISERROR

(N'The current database ID is: %d, the database name is: %s.',

10, -- Severity.

1, -- State.

@DBID, -- First substitution argument.

@DBNAME); -- Second substitution argument.

-- *********************************************************

-- *** Criando, Utilizando e Apagando mensagens de erro

-- *********************************************************

-- Criando uma mensagem

EXECUTE sp_addmessage 50005, 10, 'Não foi possível atualizar o registro!'

-- Visualisando a mensagem

RAISERROR (50005, 10, 1)

-- Apagando messanges

EXECUTE sp_dropmessage 50005

-- Exemplo

EXECUTE sp_dropmessage 50005

EXECUTE sp_addmessage 50005, -- Message id number.

10, -- Severity.

N'The current database ID is: %d, the database name is: %s.'

DECLARE @DBID INT

SET @DBID = DB_ID()

DECLARE @DBNAME NVARCHAR(128)

SET @DBNAME = DB_NAME()

RAISERROR (50005,

10, -- Severity.

1, -- State.

@DBID, -- First substitution argument.

@DBNAME); -- Second substitution argument.

-- Aula 10 -- Pivot Table

--exemplo pivot table

use master

IF EXISTS(SELECT NAME FROM SYS.DATABASES WHERE NAME = 'DB1')

DROP DATABASE DB1

create database DB1

use DB1

create table venda (ano int, mes int, valor numeric(9,2))

insert venda values (2003, 2, 10)

insert venda values (2003, 2, 1)

insert venda values (2003, 3, 20)

insert venda values (2003, 4, 30)

insert venda values (2004, 1, 40)

insert venda values (2004, 2, 50)

insert venda values (2004, 3, 60)

insert venda values (2004, 4, 70)

insert venda values (2005, 1, 80)

select * from venda order by 1,2,3

--select comun

select ano

, mes_1 = sum(case when mes=1 then valor end)

, mes_2 = sum(case when mes=2 then valor end)

, mes_3 = sum(case when mes=3 then valor end)

, mes_4 = sum(case when mes=4 then valor end)

from venda

group by ano

order by ano;

--select com pivot

--Aula 11 --Procedure com Parametros

use [adventureworks]

-- 1 - procedure simples / sem parametro de entrada

create procedure usp_SalesOrderHeader

select * from Sales.SalesOrderHeader

exec usp_SalesOrderHeader

-- 2 - retorna o total do pedido de cada cliente e quantos pedidos existe para o cliente

create procedure usp_TotalPorCliente

select CustomerID,sum(TotalDue) as Total,count(SalesOrderID) as [Qtde. Total de Pedidos]

from Sales.SalesOrderHeader

group by CustomerID

order by [Qtde. Total de Pedidos] desc,Total

exec usp_TotalPorCliente

-- 3 - procedure com 1 parametro de entrada

-- descricao - retorna todas as vendas feitas pelo cliente

-- parametro - Customer

create procedure usp_VendaPorCliente

@customerID int

select * from Sales.SalesOrderHeader

where CustomerID = @customerID

order by OrderDate

exec usp_VendaPorCliente 11091

-- 4 - procedure com inner join / like / parametro de entrada

-- descricao - busca pelo nome do cliente as vendas

-- parametro - Customer

create procedure usp_VendaPorCliente_Nome

@nomeProduto nvarchar(50)

select p.Name,convert(money,sum(LineTotal)) as Total

from Sales.SalesOrderDetail s

inner join Production.Product p on(p.ProductID=s.ProductID)

where p.Name like '%'+@nomeProduto+'%'

group by p.Name

exec usp_VendaPorCliente_Nome Cl

-- alterando a prpocedure 4

-- ordenando pelos valores de venda

ALTER procedure [dbo].[usp_VendaPorCliente_Nome]

@nomeProduto nvarchar(50)

select p.Name,convert(money,sum(LineTotal)) as Total

from Sales.SalesOrderDetail s

inner join Production.Product p on(p.ProductID=s.ProductID)

where p.Name like '%'+@nomeProduto+'%'

group by p.Name

order by Total desc

-- 5 - procedure com 2 parametros de entrada

create procedure usp_VendasPorTerritorio

@group nvarchar(50),

@countryRegionCode nvarchar(3)

select * from Sales.SalesTerritory

where [Group] = @group and CountryRegionCode = @countryRegionCode

exec usp_VendasPorTerritorio 'Europe','FR'

--procedure com tratamento de erro

use master

create database DB1

use DB1

create table torcedores

(

id int identity(1,1)

,nome varchar(50)

,time_do_coracao varchar(50)

)

--criando procedure

create procedure usp_insClientes

@nome varchar(50),

@time_do_coracao varchar(50),

@id int output

if ((@nome) = '' or (@time_do_coracao = ''))

return -1

insert into torcedores (nome,time_do_coracao) values (@nome,@time_do_coracao)

set @id = scope_identity()

return 0

--executando a procedure

declare @id int, @result int

exec @result = usp_insClientes 'Milena','Corinthians', @id output

if (@result = 0)

select @id

else

select 'Ocorreu um erro ao inserir registro.'

select * from torcedores

-- Aula 12 - Estrutura de Controle

-- estruturas de controle

-- usando if / then / else em procedures

-- 1 -

create procedure usp_VendasPorTerritorio

--criando os parametros

@group1 nvarchar(50),

@group2 nvarchar(50)

--criando as variaveis locais

declare @vendasUltimoAno1 money,@vendasUltimoAno2 money

declare @mensagem1 varchar(100),@mensagem2 varchar(100)

--definindo os valores para as variaveis

SET @vendasUltimoAno1 = (select sum(SalesLastYear) from Sales.SalesTerritory where [Group] = @group1)

SET @vendasUltimoAno2 = (select sum(SalesLastYear) from Sales.SalesTerritory where [Group] = @group2)

--estrutura de vcontrole if/then/else

if (@vendasUltimoAno1) > (@vendasUltimoAno2)

begin

set @mensagem1 = 'O grupo 1 vendeu mais que o grupo 2'

print(@mensagem1)

end

else

begin

set @mensagem2 = 'O grupo 2 vendeu mais que o grupo 1'

print(@mensagem2)

end

exec usp_VendasPorTerritorio 'Europe','Europe'

--alterando a procedure

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

-- estruturas de controle

-- usando if / then / else em procedures

-- 1 -

ALTER procedure [dbo].[usp_VendasPorTerritorio]

--criando os parametros

@group1 nvarchar(50),

@group2 nvarchar(50)

--criando as variaveis locais

declare @vendasUltimoAno1 money,@vendasUltimoAno2 money

declare @mensagem1 varchar(100),@mensagem2 varchar(100),@mensagem3 varchar(100)

--definindo os valores para as variaveis

SET @vendasUltimoAno1 = (select sum(SalesLastYear) from Sales.SalesTerritory where [Group] = @group1)

SET @vendasUltimoAno2 = (select sum(SalesLastYear) from Sales.SalesTerritory where [Group] = @group2)

--estrutura de vcontrole if/then/else

if (@vendasUltimoAno1) > (@vendasUltimoAno2)

begin

set @mensagem1 = 'O grupo 1 vendeu mais que o grupo 2'

print(@mensagem1)

end

else

if (@vendasUltimoAno1) < (@vendasUltimoAno2)

begin

set @mensagem2 = 'O grupo 2 vendeu mais que o grupo 1'

print(@mensagem2)

end

else

begin

set @mensagem3 = 'O valor das vendas são iguais'

print(@mensagem3)

end

exec usp_VendasPorTerritorio 'Europe','Europe'

-- Aula 13 -- Estrutura de Repetição

--estrutura de repeticao while/continue

use master

--criando banco de dados

create database Soma

--criando a tabela

create table somaNatural

(

numero int,

somaAcunulada int

)

use Soma

create procedure usp_CalculaSoma

@numero int

--variavel para controlar o laco

declare @contador int

set @contador = 1

--variavel para a soma/inicia com valor zero

declare @soma int

set @soma = 0

while (@contador < @numero)

begin

set @soma = @soma + @contador

insert into somaNatural values (@contador,@soma)

set @contador = @contador + 1

end

exec usp_CalculaSoma 10

select * from somaNatural

--alterando a procedure

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTER procedure [dbo].[usp_CalculaSoma]

@numero int

--apagando os dados da tabela

delete from somaNatural

--variavel para controlar o laco

declare @contador int

set @contador = 1

--variavel para a soma/inicia com valor zero

declare @soma int

set @soma = 0

while (@contador < @numero)

begin

set @soma = @soma + @contador

insert into somaNatural values (@contador,@soma)

set @contador = @contador + 1

end

exec usp_CalculaSoma 4

select * from somaNatural

-- Aula 14 - Procedure com parametros de saida

-- procedures com parametros de saida/entrada

-- procedure para retornar a media de vendas por grupo de regiao

use AdventureWorks

create procedure usp_MediaPorGrupo

@group nvarchar(50),

@media money output

set @media = (select avg(SalesLastYear) from Sales.SalesTerritory where [Group] = @group)

--executando a procedure

declare @resultado money

exec usp_MediaPorGrupo 'Europe', @resultado output

print(@resultado)

-- Aula 15 - Procedure Criptografada

--alguns comandos

use AdventureWorks

sp_help usp_VendasPorTerritorio

--usp_VendasPorTerritorio dbo stored procedure 2010-05-25 10:08:47.883

--@group1 nvarchar 100 50 NULL 1 Latin1_General_CI_AS

--@group2 nvarchar 100 50 NULL 2 Latin1_General_CI_AS

sp_helptext usp_VendasPorTerritorio

--criando um procedure criptografada

create procedure usp_returnSales

WITH ENCRYPTION

select * from Sales.SalesPerson

exec usp_returnSales

sp_helptext usp_returnSales

--The text for object 'usp_returnSales' is encrypted.

-- Aula 16 - Tratamento de erro Try Catch

--tratamento de erro try/cath

use master

IF EXISTS(SELECT NAME FROM SYS.DATABASES WHERE NAME = 'DB1')

DROP DATABASE DB1

create database DB1

use DB1

create table cidades_com_nomes_estranhos

(

id int primary key,

nome varchar(50)

)

--criando procedure com try/catch

create procedure usp_InsCidades

@id int,

@nome varchar(50)

begin try

insert into cidades_com_nomes_estranhos (id,nome) values(@id,@nome)

end try

begin catch

select ERROR_NUMBER() ErrorNumber, ERROR_MESSAGE() [Message]

end catch

exec usp_InsCidades 1,'Curralinho'

exec usp_InsCidades 2,'Jijoca de Jericoara'

exec usp_InsCidades 1,'Nhecolândia '

select * from cidades_com_nomes_estranhos

--***************************************************************************************

--procedure com try/catch - commit/rollback

use master

IF EXISTS(SELECT NAME FROM SYS.DATABASES WHERE NAME = 'DB1')

DROP DATABASE DB1

create database DB1

use DB1

create table cidades_com_nomes_estranhos

(

id int primary key,

nome varchar(50)

)

create procedure usp_InsCidades

@id int,@id1 int,

@nome varchar(50),@nome1 varchar(50)

begin try

begin tran

insert into cidades_com_nomes_estranhos (id,nome) values(@id,@nome)

insert into cidades_com_nomes_estranhos (id,nome) values(@id1,@nome1)

commit tran

end try

begin catch

rollback tran

select ERROR_NUMBER() ErrorNumber, ERROR_MESSAGE() [Message]

end catch

exec usp_InsCidades 1,1,'Recursolândia','Chupinguaia'

select * from cidades_com_nomes_estranhos

quarta-feira, 5 de outubro de 2011

SQL Server 2008 - Modulo 07 - Curso 6232

Nenhum comentário:

Postar um comentário