Webmarketing

200 Ferramentas Top para Marketing Digital

Neste artigo são apresentadas 200 ferramentas recomendadas para Marketing Digital.

Google faz a seleção dos mais populares no Mundial

A Google divulgou a sua seleção dos melhores jogadores do Mundial 2014, até à primeira frase. A escolha dos atletas leva em consideração o ranking dos mais proc...

Marketing Digital quer especialistas

É uma das áreas emergentes e precisa de profissionais especializados. Um estudo do IPAM – The Marketing School, revela que a área do Marketing Digital está em c...

10 dicas para ser mestre de pesquisa no Google

Nem todos os utilizadores sabem como aproveitar o motor de busca da Google em pleno. Há pequenos detalhes que podem tornar a pesquisa mais eficaz e ferramentas ...

Página da NOS no Facebook: falta de ética ou polémica exagerada?

A marca que sucedeu à Zon Optimus viu-se envolvida numa nova vaga de acusações, desta vez por ter transferido de forma automática os Gostos das páginas das anti...
Page:   of 23 

 

PWM is a Hootsuite Partner

Hootsuite Solution Partner

Fluxos básicos – Ciclos
(Parte Prática com TSQL SQL Server)

Eu estava numa empresa, com alguns programadores juniores e descobri que a maioria dos novos programadores, tentam fazer códigos e não tem os ciclos bem definidos, ou seja, vao colocando DO WHILE, LOOP, até que a coisa funcione.

Antigamente, as aulas de Lógica eram mais rigidas e os programadores não podiam compilar uma aplicacao tantas vezes, por isso, existia um momento de se fazer o fluxo do programa e assim por diante.

Mas agora, os programadores já partem para a programação, cometendo diversos erros, que vão gerar problemas de performance e acabam gastando muita energia para pensar sempre no mesmo ponto.

Esse documentos consiste em duas partes:

Fluxos Básicos - Ciclos (Parte teórica)

Fluxos Básicos – Ciclos (Parte prática, com exemplo para T-SQL)

O documento foi feito em inglês por mim e eu decidimos publicar sem tradução.

PS: Atenção, que após os meus exemplos, existem muitos exercícios, aceite o desafio e teste os seus conhecimentos sobre fluxos e T-SQL.

Divirtam-se!

 

Tables for labs and exercises (T-SQL SQL Server)

All labs will work with these tables, please, run the codes below to generate them and open the tables and fill with records.

Main table

Main table will be the base of all labs and exercises.

Name: test_lab_cycle

 Fluxos básicos – Ciclos (Parte Prática com TSQL SQL Server)

 PS: Fill the table, Marital_status can be MA Married, DI Divorced and SI Single

History table

 History table will be an auxiliar table to compare with the Main table and has the same format.

 Name: test_lab_cycle_historic

 Fluxos básicos – Ciclos (Parte Prática com TSQL SQL Server)

 PS: Fill the table, copy some record from test_lab_cycle (not all) and create new records.

 Total table

 TOTAL TABLE will storage the values and total of the process.

 Table name: test_lab_total

 Fluxos básicos – Ciclos (Parte Prática com TSQL SQL Server)

T-SQL Labs and Exercises

This document will not explain the format of T-SQL command, the main mission is to show how use the commands to create stored procedure

Lab 1 - Command While

Code Example – Multiplication Table of 3

 

Code Example:

-- begin -------------------------------------------------

Create procedure test_lab1

as

-- Multiplication Table of 3

declare @count_test int

declare @display_string char(30)

set @count_test = 0

while @count_test < 10

begin

set @count_test = @count_test + 1

set @display_string = ' 3 x ' + convert(char(02),@count_test) + ' = ' + convert(char(02),@count_test* 3)

print @display_string

end

-- end -------------------------------------------------

 

Resulted:

Exec test_lab1

 Fluxos básicos – Ciclos (Parte Prática com TSQL SQL Server)

Code Example – Multiplication Table for any integer

 

Code Example:

 

-- begin -------------------------------------------------

create procedure test_lab2

(@number_value as integer)

as

-- Multiplication Table of n integer

declare @count_test int

declare @display_string char(30)

set @count_test = 0

while @count_test < 10

begin

set @count_test = @count_test + 1

set @display_string = convert(char(05),@number_value) + ' x ' + convert(char(05),@count_test) + ' = ' + convert(char(05),@count_test* @number_value)

print @display_string

end

-- end -------------------------------------------------

 

Resulted:

exec test_lab2 21

 Fluxos básicos – Ciclos (Parte Prática com TSQL SQL Server)

 

Code Example – Multiplication Table for any integer (function)

Create a function with the same funcionaly of the last code.

 Code Example:

 

-- begin -------------------------------------------------

create function dbo.test_lab3

(@number_value int)

RETURNS nvarchar(2000)

as

 

begin

-- Multiplication Table of n integer

declare @count_test int

declare @resulted nvarchar(2000)

declare @display_string char(30)

set @resulted = ''

set @count_test = 0

while @count_test < 10

begin

set @count_test = @count_test + 1

set @display_string = convert(char(05),@number_value) + ' x ' + convert(char(05),@count_test) + ' = ' + convert(char(05),@count_test* @number_value)

set @resulted = @resulted + @display_string

end

 

return @resulted

end

-- end -------------------------------------------------

Resulted:

select dbo.test_lab3(24)

 Fluxos básicos – Ciclos (Parte Prática com TSQL SQL Server)

 

Exercise – Separating Words

Create a store procedure with a cycle (while), where the program receive a word (with 1 until 50 characters) and rewrite it with hyphen between all letters.

Example: exec test_exerc01 ‘MARCIO SALMEIRAO’

Resulted: M-A-R-C-I-O- -S-A-L-M-E-I-R-A-O

PS: It is necessary to know the function LEN() and SUBSTRING()

 

Exercise – Separating Words (Function)

The same of below, but create as function.

 

Exercise – Using the function

Create a simple select command with 2 columns from table test_lab_cycle

 

Columna 1: First_Name

Columna 2: First_name separated by hyphen.

 

Exercise – Using the function with Where

Create a simple select command with 2 columns from table test_lab_cycle

 

Columna 1: First_Name

Columna 2: First_name separated by hyphen

Where: The third letter of First Name like is “a”

 

Exercise – Using the function with Where (2)

Create a simple select command with 2 columns from table test_lab_cycle

 

Columna 1: First_Name

Columna 2: First_name separated by hyphen.

Where: The third letter of First_name separated by hyphen like is “a”

 

Lab 2 – Process records (1 table)

 

Store procedure to read all records from test_lab_cycle and show “Id”, “First Name”, “Last Name” and if marital_status = ‘MAR’ display ‘Happy’ else display ‘Unhappy”

 

Remember, we have 3 models:

  • Store Procedure with direct command

  • Stored Procedure with cursor

  • Stored Procedure without cursor

 

Code Example – Read a table and show records (with direct command)

 

Code Example:

-- begin -------------------------------------------------

create procedure as test_lab2a

as

select

individual_id,

first_name,

last_name,

message = case when marital_status_code = 'MA'then 'Happy' else 'Unhappy'

end

from test_lab_cycle

order by individual_id

-- end -------------------------------------------------

 

Or more complex:

-- begin -------------------------------------------------

create procedure test_lab2aa

as

 

-- Temporary table

create table #temp

(

t_individual_id char(10),

t_first_name char(25),

t_last_name char(30),

t_message char(20))

--

insert into #temp

select

individual_id,

first_name,

last_name,

case when marital_status_code = 'MA'then 'Happy' else 'Unhappy' end

from test_lab_cycle

order by individual_id

 

-- Show the resulted

select * from #temp

-- Drop Table

drop table #temp

 

-- end -------------------------------------------------

 

 

Code Example – Read a table and show records (with cursor)

Code Example:

 

-- begin -------------------------------------------------

create procedure test_lab2b

as

 

-- Local Variables

declare @w_individual_id as char(10)

declare @w_first_name as char(25)

declare @w_last_name as char(30)

declare @w_marital_status_code as char(5)

 

-- Temporary table to store all records processed

create table #temp

(

t_individual_id char(10),

t_first_name char(25),

t_last_name char(30),

t_message char(20))

--

 

-- cursor

declare test_cursor CURSOR

for

select individual_id,

first_name,

last_name,

marital_status_code

from test_lab_cycle

order by individual_id

 

open test_cursor

 

-- First Read

fetch next from test_cursor

into

@w_individual_id,

@w_first_name,

@w_last_name,

@w_marital_status_code

while @@FETCH_STATUS = 0

begin

if @w_marital_status_code = 'MA'

begin

insert into #temp

select

@w_individual_id,

@w_first_name,

@w_last_name,

'Happy'

end

else

begin

insert into #temp

select

@w_individual_id,

@w_first_name,

@w_last_name,

'Unhappy'

end

 

-- Next Read

fetch next from test_cursor

into

@w_individual_id,

@w_first_name,

@w_last_name,

@w_marital_status_code

end

 

-- Close Cursos

close test_cursor

deallocate test_cursor

 

-- Show the resulted

select * from #temp

-- Drop Table

drop table #temp

-- End -------------------------------------------------

 

Code Example – Read a table and show records (without cursor)

 

Code Example:

 

-- begin -------------------------------------------------

 

create procedure test_lab2c

as

 

-- Local Variables

declare @w_individual_id as char(10)

declare @w_first_name as char(25)

declare @w_last_name as char(30)

declare @w_marital_status_code as char(5)

 

-- Temporary table to store all records processed

create table #temp

(

t_individual_id char(10),

t_first_name char(25),

t_last_name char(30),

t_message char(20))

--

select @w_individual_id = ''

 

-- First Read

select top 1

@w_individual_id = individual_id,

@w_first_name = first_name,

@w_last_name = last_name,

@w_marital_status_code = marital_status_code

from test_lab_cycle

where individual_id > @w_individual_id

order by individual_id

 

 

while @@rowcount > 0

begin

if @w_marital_status_code = 'MA'

begin

insert into #temp

select

@w_individual_id,

@w_first_name,

@w_last_name,

'Happy'

end

else

begin

insert into #temp

select

@w_individual_id,

@w_first_name,

@w_last_name,

'Unhappy'

end

 

-- Next Read

select top 1

@w_individual_id = individual_id,

@w_first_name = first_name,

@w_last_name = last_name,

@w_marital_status_code = marital_status_code

from test_lab_cycle

where individual_id > @w_individual_id

order by individual_id

end

 

 

-- Show the resulted

select * from #temp

-- Drop Table

drop table #temp

 

-- end -------------------------------------------------

 

 

Lab 3 – Process records (2 tables and Transaction command)

 

Store procedure to process all records from test_lab_cycle, updating all salary with this rule:

 

Rule Name

Condition

Bonus

RULE A

Salary < 2000

Salary + 20%

RULE B

Salary >= 2000 and < 5000

Salary + 15%

RULE C

Salary >= 5000

Nothing

 

And stored in test_lab_total the total of records processed by ‘Rule Name’.

Example

 Fluxos básicos – Ciclos (Parte Prática com TSQL SQL Server)

 

 PS: Before to run, it is necessary to recreate all tables, to have the same records and can have the same resulted.

  

Code Example (with direct command)

Code Example

 

-- begin -------------------------------------------------

create procedure test_lab3a

as

 

SET NOCOUNT ON

-- delete total

delete from test_lab_total

 

-- STEP A, TOTAL

insert into test_lab_total

select

code = case when salary < 2000 then 'RULE A' else case when salary >= 2000 and salary <5000 then 'RULE B' else 'RULE C' end end,

value_tot = count(1)

from test_lab_cycle

group by case when salary < 2000 then 'RULE A' else case when salary >= 2000 and salary <5000 then 'RULE B' else 'RULE C' end end

 

-- STEP B, INSERT

update test_lab_cycle

set salary = case when salary < 2000 then salary * 1.2 else case when salary >= 2000 and salary <5000 then salary * 1.15 else salary end end

-- end -------------------------------------------------

 

Code Example (with transaction)

If something happened with STEP B and it didn’t finish, the process will have a problem with the total table, because it was updated and the total were wrong. In this case, it is necessary work with the transaction command.

 

CREATE procedure test_lab3aa

as

 

BEGIN TRAN

 

SET NOCOUNT ON

-- delete total

delete from test_lab_total

 

-- PROCESS A, TOTAL

insert into test_lab_total

select

code = case when salary < 2000 then 'RULE A' else case when salary >= 2000 and salary <5000 then 'RULE B' else 'RULE C' end end,

value_tot = count(1)

from test_lab_cycle

group by case when salary < 2000 then 'RULE A' else case when salary >= 2000 and salary <5000 then 'RULE B' else 'RULE C' end end

 

if @@error = 0

begin

-- PROCESS B, INSERT

update test_lab_cycle

set salary = case when salary < 2000 then salary * 1.2 else case when salary >= 2000 and salary <5000 then salary * 1.15 else salary end end

print 'Transaction OK'

COMMIT TRAN

end

else

begin

print 'Transaction ERROR'

ROLLBACK

End

 

 

Code Example (with cursor)

 

-- begin -------------------------------------------------

create procedure test_lab3b

as

 

SET NOCOUNT ON

 

-- delete total

delete from test_lab_total

 

-- Local Variables

declare @w_individual_id as char(10)

declare @w_salary as money

 

-- cursor

declare test_cursor CURSOR

for

select individual_id,

salary

from test_lab_cycle

order by individual_id

 

open test_cursor

 

-- First Read

fetch next from test_cursor

into

@w_individual_id,

@w_salary

while @@FETCH_STATUS = 0

begin

if @w_salary < 2000

begin

update test_lab_cycle

set salary = salary * 1.20

where individual_id = @w_individual_id

 

update test_lab_total

set value_tot = value_tot + 1

where code = 'RULE A'

if @@rowcount = 0

begin

insert into test_lab_total

select 'RULE A', 1

end

end

else

begin

if @w_salary > 2000 and @w_salary <= 5000

begin

update test_lab_cycle

set salary = salary * 1.15

where individual_id = @w_individual_id

 

update test_lab_total

set value_tot = value_tot + 1

where code = 'RULE B'

if @@rowcount = 0

begin

insert into test_lab_total

select 'RULE B', 1

end

 

end

else

begin

update test_lab_cycle

set salary = salary

where individual_id = @w_individual_id

 

update test_lab_total

set value_tot = value_tot + 1

where code = 'RULE C'

if @@rowcount = 0

begin

insert into test_lab_total

select 'RULE C', 1

end

end

end

 

--print @w_individual_id 'we can use this command to check the program running

 

-- Next Read

fetch next from test_cursor

into

@w_individual_id,

@w_salary

end

 

-- Close Cursos

close test_cursor

deallocate test_cursor

-- End -------------------------------------------------

 

TIP:

When the SP is running open a new query and run the command

select * from test_lab_total

You can control/check the process running

 

 

Code Example (without cursor)

 

Code Example

 

-- begin -------------------------------------------------

alter procedure test_lab3c

as

 

SET NOCOUNT ON

 

-- delete total

delete from test_lab_total

 

-- Local Variables

declare @w_individual_id as char(10)

declare @w_salary as money

 

select @w_individual_id = ''

 

-- First Read

select top 1

@w_individual_id = individual_id,

@w_salary = salary

from test_lab_cycle

where individual_id > @w_individual_id

order by individual_id

while @@rowcount > 0

begin

if @w_salary < 2000

begin

update test_lab_cycle

set salary = salary * 1.20

where individual_id = @w_individual_id

 

update test_lab_total

set value_tot = value_tot + 1

where code = 'RULE A'

if @@rowcount = 0

begin

insert into test_lab_total

select 'RULE A', 1

end

end

else

begin

if @w_salary > 2000 and @w_salary <= 5000

begin

update test_lab_cycle

set salary = salary * 1.15

where individual_id = @w_individual_id

 

update test_lab_total

set value_tot = value_tot + 1

where code = 'RULE B'

if @@rowcount = 0

begin

insert into test_lab_total

select 'RULE B', 1

end

 

end

else

begin

update test_lab_cycle

set salary = salary

where individual_id = @w_individual_id

 

update test_lab_total

set value_tot = value_tot + 1

where code = 'RULE C'

if @@rowcount = 0

begin

insert into test_lab_total

select 'RULE C', 1

end

end

end

 

--print @w_individual_id 'we can use this command to check the program running

 

-- Next Read

select top 1

@w_individual_id = individual_id,

@w_salary = salary

from test_lab_cycle

where individual_id > @w_individual_id

order by individual_id

end

-- End -------------------------------------------------

 

 

TIP:

You can run the process with BEGIN TRANSACTION

 

Exercises

  

All exercises must done with the 3 models (direct, with cursor and without cursor).

Remember, before each exercise, recreate all tables (cycle, hist and total).

 

Exercise 1 – Check Historic

 Store procedure to process all records from test_lab_cycle and count the number of individual with history:

 

Rule Name

Condition

HISTORIC EXIST

test_lab_cycle.individual_id exists in test_lab_cycle_historic

HISTORIC NOT EXIST

test_lab_cycle.individual_id not exists in test_lab_cycle_historic

 

And storage in test_lab_total the total of records processed by ‘Rule Name’.

Example

Code

Value_tot

EXIST

8383

NOT EXIST

5678

  

Exercise 2 – Check Salary

 

Store procedure to process all records from test_lab_cycle and update the salary in historic information:

 

Rule Name

Condition

HISTORIC EXIST

Update historic.salary = salary

HISTORIC NOT EXIST

Nothing

 

 Exercise 1 – Check Salary and count the number of records update

 

Store procedure to process all records from test_lab_cycle and update the salary in historic information:

 

Rule Name

Action

HISTORIC EXIST

Update historic.salary = salary

HISTORIC NOT EXIST

Nothing

 

And storage in test_lab_total the total of records with salary updated.

Example

Code

Value_tot

SALARY UPDATED

73737

SALARY NOT UPDATED

8888

 

 Exercise 1 – Transfer the records to Cycle

 

Store procedure to process all records from test_lab_cycle and if not exist in historic, copy all information:

 

Rule Name

Action

HISTORIC EXIST

Nothing

HISTORIC NOT EXIST

Copy the record to historic.

 

And storage in test_lab_total the total of records copied.

Example

Code

Value_tot

COPIED

73737

NOT COPIED

8888

 

 

Poderá ainda ter interesse em:

Fluxos básicos – Ciclos (Parte Teórica)

DOTNETNUKE: Portal e Gestor de Conteúdos

Inquérito: O “Paypal” é um padrão?

Marcio Roberto Salmeirão [+]
www.salmeirao.com
View Marcio Salmeirao's profile on LinkedIn

Newsletter


Colunistas


Ferramentas de Webmarketing