Muito boa tarde!!! Olá galera….
Após mais um sábado de aulas na Fatec São Roque, chegou a hora de se dedicar a comunidade de tecnologia, mais especificamente aos amantes de bancos de dados.
Como promessa é dívida e deve ser cumprida “ou melhor” paga, estou pagando a minha feita a exatos três meses no final do último post da sessão Short Scripts, hoje publicando mais um conjunto de scripts adicionados a minha biblioteca particular de códigos e exemplos nos últimos meses.
O post de hoje
Como de costume selecionei os principais scripts armazenados recentemente na minha biblioteca de códigos, que apresentam os seguintes assuntos:
- Cálculo de Datas;
- Comando Select;
- Computed Column;
- CTE Recursiva;
- Formatação de Plano de Contas;
- Função Format();
- Função Parse();
- Operador Cross Apply;
- Operador Outer Apply;
- Scalar User Defined Function;
- Sequência Fibonacci; e
- Sequência numérica de CEPs.
Chegou a hora, mãos nos teclados, a seguir apresento os códigos e exemplos selecionados para o Short Script – Setembro 2017. Vale ressaltar que todos os scripts publicados nesta sessão foram devidamente testados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, vale sim todo cuidado possível para evitar maiores problemas.
Fique a vontade para compartilhar, comentar e melhorar cada um destes códigos.
Short Scripts
— Short Script 1 – Calculando o Ano e Mês posterior através do ano e mês —
Declare @AnoMes int = 201712
Declare @AnoMesPosteriorDate date = DATEADD(MONTH, @AnoMes % 100, DATEADD(YEAR, @AnoMes / 100 – 1900, 0))
Declare @AnoMesPosterior int = YEAR(@AnoMesPosteriorDate) * 100 + Month(@AnoMesPosteriorDate)
Select @AnoMes, @AnoMesPosterior
Go
Go
— Short Script 2 – Comando Select especificando o nome da coluna e o valor através do sinal de igual —
Select ‘1/0’ = ‘Hello’, ‘2…’ = ‘teste’
Go
Go
— Short Script 3 – Formatando a apresentação de um Plano de Contas —
DECLARE @tableDados TABLE ( Codigo VARCHAR(100) )
INSERT INTO @tableDados
SELECT ‘1.0.0.0.0.00.00’
UNION ALL
SELECT ‘1.1.0.0.0.00.00’
UNION ALL
SELECT ‘1.1.1.1.2.00.00’
UNION ALL
SELECT ‘2.3.7.1.1.01.00’
UNION ALL
SELECT ‘2.3.2.0.5.00.00’
SELECT ‘1.0.0.0.0.00.00’
UNION ALL
SELECT ‘1.1.0.0.0.00.00’
UNION ALL
SELECT ‘1.1.1.1.2.00.00’
UNION ALL
SELECT ‘2.3.7.1.1.01.00’
UNION ALL
SELECT ‘2.3.2.0.5.00.00’
SELECT Codigo FROM @tableDados
SELECT TD.Codigo, LEFT(TD.CODIGO, LEN(TD.CODIGO) – PATINDEX(‘%[1-9]%’, REVERSE(TD.CODIGO)) + 1)
FROM @tableDados AS TD
Go
FROM @tableDados AS TD
Go
— Short Script 4 – Formatando a apresentação de dados através das funções Parse() e Format() —
Declare @Valor Varchar(10)
Set @Valor=’1,540.20′
Set @Valor=’1,540.20′
Select @Valor As Antes, PARSE(@Valor As money Using ‘en-US’) As Depois
Select FORMAT(Cast(@Valor As Money),’C’,’pt-BR’)
Go
Go
— Short Script 5 – Analisando as diferentes de comportamente entre os operadores Cross Apply e Outer Apply —
Declare @Tabela1 Table
(Codigo Int,
Valor Int)
(Codigo Int,
Valor Int)
Declare @Tabela2 Table
(Codigo Int,
Valor Int)
(Codigo Int,
Valor Int)
Insert Into @Tabela1 Values (1,1),(2,2),(Null, Null)
Insert Into @Tabela2 Values (1,1),(2,2),(3,3),(4,4),(5,5), (Null, Null)
Insert Into @Tabela2 Values (1,1),(2,2),(3,3),(4,4),(5,5), (Null, Null)
— Utilizando operador Outer Apply —
Select T.Codigo,
T.Valor
From @Tabela1 T Outer Apply (Select Codigo From @Tabela2
Where Codigo = T.Codigo) As T2
Select T.Codigo,
T.Valor
From @Tabela1 T Outer Apply (Select Codigo From @Tabela2
Where Codigo = T.Codigo) As T2
— Utilizando operador Cross Apply —
Select T.Codigo,
T.Valor
From @Tabela1 T Cross Apply (Select Codigo From @Tabela2
Where Codigo = T.Codigo) As T2
Select T.Codigo,
T.Valor
From @Tabela1 T Cross Apply (Select Codigo From @Tabela2
Where Codigo = T.Codigo) As T2
— Short Script 6 – Criando a sequência Fibonacci de valores —
Create Table Sequence
(Code Int Primary Key Identity(1,1),
Number BigInt Not Null)
Go
(Code Int Primary Key Identity(1,1),
Number BigInt Not Null)
Go
Declare @Counter Int = 1, @String Varchar(Max)
While @Counter <=50
Begin
Begin
Insert Into Sequence (Number)
Select IsNull(Sum(Number),1) from Sequence
Where Code < @Counter -1
Select IsNull(Sum(Number),1) from Sequence
Where Code < @Counter -1
Set @String = (Select Concat(@String,’,’,Number) from Sequence Where Code = @Counter)
Set @Counter +=1
End
Update Sequence
Set Number = 0
Where Code = 1
Set Number = 0
Where Code = 1
Select Number ‘Number List’ From Sequence
Select ‘0’+@String As ‘Sequence Finobacci’
Go
Go
— Short Script 7 – Criando uma Computed Column para uso de uma Scalar User Defined Function —
— Criando a Function F_CalcularDiferencaAnos —
Create Function F_CalcularDiferencaAnos (@DataNascimento Date)
Returns Int
As
Begin
Return (Select DATEDIFF(Year, @DataNascimento, GetDate()))
End
Go
Create Function F_CalcularDiferencaAnos (@DataNascimento Date)
Returns Int
As
Begin
Return (Select DATEDIFF(Year, @DataNascimento, GetDate()))
End
Go
— Criando a Tabela1 para Teste —
Create Table Tabela1
(Codigo Int,
DataNascimento Date,
DiferencaComputada As (dbo.F_CalcularDiferencaAnos(DataNascimento))) — Criando uma coluna computada com a function —
Go
Create Table Tabela1
(Codigo Int,
DataNascimento Date,
DiferencaComputada As (dbo.F_CalcularDiferencaAnos(DataNascimento))) — Criando uma coluna computada com a function —
Go
— Inserindo os dados —
Insert Into Tabela1 (Codigo, DataNascimento)
Values (1,’1980-04-28′), (2,’1981-01-28′)
Go
Insert Into Tabela1 (Codigo, DataNascimento)
Values (1,’1980-04-28′), (2,’1981-01-28′)
Go
— Validando o resultado —
Select * from Tabela1
Go
Select * from Tabela1
Go
— Short Script 8 – Criando uma sequência de valores de CEPs através de uma CTE Recursiva —
Declare @Tabela table
(Codigo int, Cidade varchar(40), Inicio char(9), Fim char(9));
(Codigo int, Cidade varchar(40), Inicio char(9), Fim char(9));
insert into @Tabela values (1, ‘Belo Horizonte’, ‘30000-000’, ‘35000-000’)
;With CTE_Rec as
(
Select Cidade,
Cast(left(Fim, 5) + right(Fim, 3) as int) as Fim,
Cast(left(Inicio, 5) + right(Inicio, 3) as int) as Cep
From @Tabela
(
Select Cidade,
Cast(left(Fim, 5) + right(Fim, 3) as int) as Fim,
Cast(left(Inicio, 5) + right(Inicio, 3) as int) as Cep
From @Tabela
Union all
Select Cidade, Fim, Cep + 1
from CTE_rec
where Cep < Fim
)
from CTE_rec
where Cep < Fim
)
Select Cidade, cast(Cep / 1000 as char(5)) + ‘-‘ + right(’00’ + cast(Cep % 1000 as varchar), 3) as Cep,
(Cep / 1000) as Div,
(Cep % 1000) as Div2
from CTE_Rec
OPTION (MAXRECURSION 0)
Go
(Cep / 1000) as Div,
(Cep % 1000) as Div2
from CTE_Rec
OPTION (MAXRECURSION 0)
Go
Ufa, chegamos ao final! Mais uma relação de short scripts acaba de ser compartilhada, mesmo sendo denominados short entre aspas “pequenos”, posso garantir que todos estes exemplos são de grande importância, apresentam um valor e conhecimento do mais alto nível.
Chegamos ao final de mais um Short Scripts, espero que este material possa lhe ajudar, ilustrando o uso de alguns recursos e funcionalidades do Microsoft SQL Server.
Acredito que você tenha observado que estes códigos são conhecidos em meu blog, todos estão relacionados aos posts dedicados ao Microsoft SQL Server publicados no decorrer dos últimos anos.
Boa parte deste material é fruto de um trabalho dedicado exclusivamente a colaboração com a comunidade, visando sempre encontrar algo que possa ser a solução de um determinado problema, bem como, a demonstração de como se pode fazer uso de um determinado recurso.
Links
Caso você queira acessar os últimos posts desta sessão, não perca tempo acesse os links listados abaixo:
- https://pedrogalvaojunior.wordpress.com/2017/06/08/short-scripts-junho-2017/
- https://pedrogalvaojunior.wordpress.com/2017/03/31/short-scripts-marco-2017/
- https://pedrogalvaojunior.wordpress.com/2016/12/15/short-scripts-dezembro-2016/
- https://pedrogalvaojunior.wordpress.com/2016/10/31/short-scripts-outubro-2016/
- https://pedrogalvaojunior.wordpress.com/2016/06/20/short-scripts-junho-2016/
Nenhum comentário:
Postar um comentário