diQRbXzB

1
2-- apaga a tabela de usuarios
3if (exists (select * from information_schema.tables where table_schema = 'dbo' and  table_name = 'usuario')) begin
4	drop table usuario;
5end
6
7-- criando a estrutura de usuarios
8create table usuario
9(
10	id int not null identity primary key,
11	nome nvarchar(100),
12	email nvarchar(100)
13);
14
15-- registrando usuários
16insert into usuario values('wellington','well@domino.com');
17insert into usuario values('william',	'will@domino.com');
18insert into usuario values('gustavo',	'gugu@domino.com');
19insert into usuario values('eduardo',	'dudu@domino.com');
20
21-- listando usuários cadastrados
22select * from usuario;
23
24-- adicionando pipes (usando cursores)
25declare cursor_usuario cursor local for select nome, email from usuario for update of email;
26declare @nome nvarchar(100), @email nvarchar(100);
27open cursor_usuario;
28fetch next from cursor_usuario into @nome, @email;
29while @@fetch_status = 0 begin
30	update usuario set email = concat(@email, '|') where current of cursor_usuario;
31	fetch next from cursor_usuario into @nome, @email;
32end
33close cursor_usuario;
34deallocate cursor_usuario;
35
36-- listando usuários com pipe
37select * from usuario;
38
39-- removendo pipes (usando cursores)
40-- isso resolveria => update usuario set nome = replace(nome, '|',''), email = replace(email, '|', '');
41declare cursor_usuario cursor local for select nome, email from usuario for update of nome, email;
42open cursor_usuario;
43fetch next from cursor_usuario into @nome, @email;
44while @@fetch_status = 0 begin
45	update usuario set email = replace(@email, '|', ''), nome = replace(@nome, '|', '') where current of cursor_usuario;
46	fetch next from cursor_usuario into @nome, @email;
47end
48close cursor_usuario;
49deallocate cursor_usuario;
50
51-- listando usuários sem pipe
52select * from usuario;