· 6 years ago · Oct 18, 2019, 11:44 PM
1create database banco2Engenharia4sem2019;
2use banco2Engenharia4sem2019;
3
4create table marca (codigo int not null identity(1,1),
5 marca varchar(20), primary key (codigo))
6
7select * from marca
8where marca like '%'
9order by marca
10
11insert marca values ('Gm')
12insert marca values ('Honda')
13insert marca values ('Kia')
14insert marca values ('Fiat')
15insert marca values ('Vw')
16insert marca values ('Ford')
17
18create table veiculo (placa varchar(7) not null, modelo varchar(30),
19 marca int, ano int, cor varchar(20),
20 constraint pk_veiculo primary key (placa),
21 constraint fk_vei_marc
22 foreign key (marca) references marca(codigo))
23
24-- drop table veiculo -- se for necessário apagar a tabela.
25select * from veiculo
26
27insert into veiculo values ('abc1234','Cruze',1,2017,'Prata')
28insert into veiculo values ('dfy9322','Palio',4,2000,'Branco')
29insert into veiculo values ('dda2300','Civic',2,2015,'Branco')
30insert into veiculo values ('iue8899','Fusca',5,1979,'Verde')
31insert into veiculo values ('zno4567','Fusion',6,2018,'Preto')
32insert into veiculo values ('foo4321','CRV',2,2008,'Azul')
33
34select *from marca
35select * from veiculo
36
37
38select * from marca where codigo not in (select marca from veiculo)
39
40insert into marca values ('Ferrari')
41
42select * from marca m where not exists
43 (select * from veiculo v where m.codigo = v.marca )
44
45
46insert into veiculo (placa,modelo,ano,cor) values ('abc1235','Cruze',2017,'Prata')
47
48select marca
49from marca
50where codigo in
51(select marca from veiculo)
52
53select marca
54from marca
55where codigo --exists
56(select placa from veiculo)
57
58select * from marca
59select * from veiculo
60
61select m.marca as 'Nome da Marca', v.modelo, v.cor, v.ano
62from marca m inner join veiculo v on m.codigo = v.marca
63where 1=1 and v.modelo = 'civic'
64order by m.marca
65
66select m.marca, v.modelo, v.cor, v.ano, v.placa
67from marca m left join veiculo v on m.codigo = v.marca
68where 1=1
69order by m.marca
70
71select m.marca, v.modelo, v.cor, v.ano, v.placa
72from marca m right join veiculo v on m.codigo = v.marca
73where 1=1
74order by m.marca
75
76select m.marca, v.modelo, v.cor, v.ano, v.placa
77from marca m full join veiculo v on m.codigo = v.marca
78where 1=1
79order by m.marca
80
81select * from veiculo
82
83insert into veiculo (placa, modelo, ano, cor) values
84 ('abc8770','C300',2016,'Azul')
85
86------
87create view consulta_veiculo as
88select m.marca, v.modelo, v.cor, v.ano, v.placa
89from marca m full join veiculo v on m.codigo = v.marca
90where 1=1
91---order by m.marca
92
93
94select * from consulta_veiculo
95where 1=1 and cor like '%a%'
96order by marca
97
98alter view veiculo2 as
99select placa,modelo, marca, cor
100from veiculo
101
102select * from veiculo2
103insert into veiculo2 values('fff1299','Ferrari',1,'vermelho')
104
105
106----------------------------------------------------------------------
107--transaction
108begin transaction
109insert into marca values ('Bmw')
110insert into marca values ('Jaguar')
111insert into marca values ('Hyundai')
112if @@error ! = 0 rollback else commit
113
114print ('Numero do Erro' + convert (varchar(10),@@error))
115
116select * from marca
117
118select * from veiculo
119
120begin tran
121insert into veiculo values ('XJT9712','Ka',6,2019,'Branco')
122rollback
123
124begin tran
125insert into veiculo values ('XJT9712','Ka',6,2019,'Branco')
126commit
127
128
129create table usuario (codigo int not null identity(1,1), nome varchar(10),
130 funcao varchar(20), salario float, comissao float,
131 primary key (codigo))
132
133insert into usuario values ('João','Analista',1900,0)
134insert into usuario values ('Maria','Presidente',8000,0)
135insert into usuario values ('Marcos','Adm',1200,0)
136insert into usuario values ('Marta','Secretaria',2000,0)
137insert into usuario values ('José','Vendedor',1500,500)
138
139select * from usuario
140
141begin tran
142delete from usuario where salario > 10000
143commit
144
145------------Criar um ponto para restaurar
146
147begin tran
148save tran point1 --- possui todos os registros
149delete from usuario where codigo in(1,3)
150save tran point2--- faltam os registros 1 e 3
151delete from usuario where codigo = 5
152select * from usuario
153
154select * from usuario ---para testar
155rollback tran point1
156
157begin tran
158save tran point3
159delete from usuario
160
161commit
162commit
163commit
164/* Executando p bloco todo, se der rollback no point2 volta os registro
165registro 5 , e rollback no point1 volta todos os registros.
166mesmo apos um rollback, outro pode ser executado.
167mas apos um commit ou apos o uso, perde-se a referencia ao ponteiro.*/
168
169/* Procedures - funções para banco de dados.
170 Oferece a possibilidade de transferir a regra de negocio da aplicação
171 para o BD.
172 Possui caracteristicas de qualquer linguagem de programação.
173 Laço (for), decisão (if), variaveis.
174 minimizar o processamento do lado da aplicação e centralizar
175 a Regra de negócio.
176 Sintaxe:
177 create procedure <nome>
178 <variaveis interna da procedure>
179 Logica da função
180-----
181*/
182
183create procedure exemplo11
184@ano int --- variavel passagem parametro
185as
186 select * from veiculo where ano = @ano
187 --
188
189 exec exemplo11 2017
190 execute exemplo11 2017
191
192create procedure exemplo2
193 as
194 select v.modelo, m.marca, v.cor, v.ano
195 from veiculo v inner join marca m on m.codigo = v.marca
196 where 1=1
197 order by v.modelo
198 -----fim da procedure
199
200 exec exemplo2
201
202create procedure exemplo3
203@marca varchar(10), @ano int
204 as
205 select v.modelo, m.marca, v.cor, v.ano
206 from veiculo v inner join marca m on m.codigo = v.marca
207 where 1=1 and m.marca = @marca and v.ano = @ano
208 order by v.modelo
209
210 ---- fim da procedure
211
212 exec exemplo3 'fiat',2000
213
214 create procedure exemplo3a
215@marca varchar(10), @ano int
216 as
217 select v.modelo, m.marca, v.cor, v.ano
218 from veiculo v inner join marca m on m.codigo = v.marca
219 where 1=1 and m.marca like '%' + @marca + '%' and v.ano = @ano
220 order by v.modelo
221---fim da procedure
222
223exec exemplo3a 'h',2015
224
225create procedure exemplo4
226as
227 declare @valor int
228 set @valor = 2
229 print ('valor da variavel' + convert(varchar(2),@valor))
230 set @valor = @valor * 3
231 print ('valor novo : ' + convert(varchar (2),@valor))
232 --fim procedure
233
234
235 exec exemplo4
236
237
238 alter procedure exemplo4a
239 @valorx int
240as
241 declare @valor int
242 set @valor = @valorx
243 print ('valor da variavel :' + convert(varchar(2),@valor))
244 set @valor = @valor * 3
245 print ('valor novo : ' + convert(varchar(2),@valor))
246 --fim procedure
247
248 exec exemplo4a 23
249
250
251 alter procedure exemplo4b
252as
253 declare @media float
254select @media = avg(ano) from veiculo
255 print ('media ano do veiculo : ' + convert(varchar(4),@media))
256 ------ fim procedure
257
258 exec exemplo4b
259
260 create procedure exemplo4c
261 @informacao varchar(10), @valor1 int
262 as
263 declare @valor2 int
264 set @valor2 = @valor1*2
265 print ('valor do parametro' + @informacao)
266 print ('valor1 ' + convert(varchar(4),@valor1))
267 print ('valor2 ' + convert(varchar(4),@valor2))
268 if @valor2 > 100
269 print('valor superior a 100')
270 else
271 print('valor inferir')
272-------------------------------
273execute exemplo4c 'testando',60
274
275/* Exercicios:
276------------
277Elaborar uma procedure com passagem de parametro de dois valores e 1 operação
278operação de multiplicação, soma ou subtração.
279A procedure devera receber esses parametros e de acordo com a operação,
280calcular o resultado e apresentar.
281*/
282
283alter procedure calculadora
284 @valor1 int, @valor2 int , @op varchar(1)
285 as
286 declare @resultado int
287
288 if @op = '+'
289 begin
290 set @resultado = @valor1 + @valor2
291 end
292
293 if @op = '-'
294
295 begin
296 set @resultado = @valor1 - @valor2
297 end
298
299
300
301 if @op = '*'
302
303 begin
304 set @resultado = @valor1 * @valor2
305 end
306
307 print ('Resultado da' + @op + ' é ' + convert(varchar(4),@resultado))
308
309 execute calculadora 10,5,'*'
310-------------------------------------------------------------------------
311create procedure exercicio1 -- if simples
312@operacao varchar(1), @valor1 float, @valor2 float
313as
314declare @resultado float
315if @operacao = '+' begin select @resultado = @valor2 + @valor1 end
316if @operacao = '-' begin select @resultado = @valor2 - @valor1 end
317if @operacao = '*' begin select @resultado = @valor1 * @valor2 end
318if @operacao = '/' begin select @resultado = @valor2 / @valor1 end
319print ('Resultado da' + @operacao + ' é ' + convert(varchar(4),@resultado))
320---------
321exec exercicio1 '+', 2, 3
322exec exercicio1 '-', 2, 3
323exec exercicio1 '/', 2, 3
324exec exercicio1 '*', 2, 3
325
326-------------------
327/*Elaborar uma procedure com passagem de parametro solicitando uma marca de veiculo.
328Internamente na Procedure apresentar a quantidade de veiculos da marca.
329Se não encontrar nenhum veiculo com a marca especificada apresentar mensagem.
330*/
331
332create procedure veiculo1
333@para_marca varchar(10)
334
335as
336declare @quant int
337
338
339
340
341set @quant = (select count (m.marca) from veiculo v inner join marca m on m.codigo = v.marca
342where 1=1 and m.marca = @para_marca)
343
344
345
346if @quant = 0
347 print('ERRO! Nenhum veiculo da marca --' + @para_marca + '-- encontrado')
348else
349print ('Quantidades de veiculo da marca '+ @para_marca +':'+ convert(varchar(4),@quant))
350
351
352
353
354
355
356
357exec veiculo1 'fiat'
358
359
360------codigo do professor
361
362create procedure exercicio_professor
363@marca varchar (10)
364
365as
366declare @qtd float
367select @qtd = count (*) from veiculo inner join marca on
368 marca.codigo = veiculo.marca where marca.marca = @marca
369
370if @qtd = 0
371print ('nao encontrado nenhum veiculo da marca' +@marca)
372else
373print ( 'Qtd de veiculo ' + @marca + ' é :' + convert (varchar (4),@qtd))
374
375
376--
377exec exercicio_professor 'honda'
378
379
380
381
382
383/*Complementando o exercicio anterior.
384Pesquisar inicialmente se a marca procurada esta cadastrada no banco de dados
385se não existir seu cadastro apresentar mensagem de que a marca não existe
386Caso contrario pesquisar os veiculos Cadastrados esta marca.
387*/
388alter procedure exercicio2
389@marca varchar(10)
390as
391declare @qtd float
392declare @existe int
393select @existe = count (*) from marca where marca = @marca
394if @existe <> 0
395begin
396select @qtd = count(*) from veiculo inner join marca on
397 marca.codigo = veiculo.marca where marca.marca = @marca
398print ('Qtd de veiculo ' + @marca + ' é : ' + convert(varchar(4),@qtd))
399end
400else
401begin
402print ('não existe esta marca cadastrada')
403end
404
405
406exec exercicio2 'fiat'
407
408
409
410---delete cascade
411-------------------------------------------------------------------
412---aula 27.08
413
414create procedure exemplo5
415
416@valor int output
417as
418 set @valor = 10
419------------
420
421
422
423declare @contador int
424exec exemplo5 @valor=@contador output
425print( 'valor de retorno ' + convert (varchar(2),@contador))
426
427----------------------------------------------------------
428
429create procedure exemplo6
430@valor1 int,
431@valor2 int output
432as
433
434 set @valor2 = @valor1
435 set @valor2 = @valor2 *2
436--------------------------
437
438declare @contador int
439exec exemplo6 20 , @valor2= @contador output
440print ('valor de retorno ' + convert (varchar(2),@contador))
441----------teste de valor null
442create procedure exemplo7
443@valor1 int,
444@valor2 int output
445as
446
447 set @valor2 = @valor1
448 set @valor2 = @valor2 *2
449--------------------------
450
451declare @contador int
452exec exemplo6 null , @valor2= @contador output
453print ('valor de retorno ' + convert (varchar(2),@contador))
454----------------------------------------------------------
455
456
457alter procedure exemplo7
458@valor1 int,@valor2 int
459
460as
461
462if @valor1 is not null begin
463 set @valor2 = @valor1
464 set @valor2 = @valor2 * 10
465 print ('valor1: ' + convert(varchar(3),@valor1) + 'v2 : ' + convert(varchar(3),@valor2))
466
467end else
468print (' detectando valor nulo' )
469
470
471exec exemplo7 10,11
472exec exemplo7 null,11
473
474------------------
475
476
477alter procedure exemplocalc
478@operacao int, --> 1+2-3*4/
479@valora int,
480@valorb int,
481@resultado float output
482as
483 if @operacao = 1
484 set @resultado = @valora + @valorb
485 else if @operacao =2
486 set @resultado = @valora - @valorb
487 else if @operacao = 3
488 set @resultado = @valora * @valorb
489 else if @operacao = 4
490 set @resultado = @valora / @valorb
491 else
492 begin
493 print ('Operador nao reconhecido')
494 set @resultado = 0
495 end
496 return @@error
497------------------------------------
498declare @erro int
499declare @retorno int
500
501
502exec @erro = exemplocalc 3,10,5,@retorno output
503select @erro,@retorno
504------------------------------------
505create procedure testedependencia
506@valor1 int,
507@valor2 int
508as
509DECLARE @Retorno int
510exec exemplocalc 1,@valor1,@valor2,@Retorno OUTPUT
511print ('valor de retorno' + convert (varchar(2),@Retorno))
512
513---------------
514
515exec testedependencia 12,12
516
517
518----------------------------
519
520select * from marca
521select * from usuario
522select * from veiculo
523
524create procedure consultadinamica
525@nome as varchar(100),
526@funcao as varchar(100),
527@faixasalarial as float
528
529as
530declare @sql as varchar (3000)
531set @sql = 'select codigo , nome, funcao, salario from usuario where 1=1'
532if (@nome is not null)
533 set @sql = @sql +'and nome =''' + @nome +''''
534if (@funcao is not null)
535 set @sql = @sql + 'and funcao =''' + @funcao+ ''''
536if (@faixasalarial is not null )
537 set @sql = @sql + ' and salario >= ' + convert(varchar(4),@faixasalarial)
538
539
540--print (@sql)
541------------------
542exec (@sql)
543
544
545-------------------
546
547exec consultadinamica 'Pedro','Analista',1000
548exec consultadinamica null,'Analista',1000
549exec consultadinamica null,'Presidente',null
550
551
552-------------------------------------------
553-------------------------------------------
554--Exercicio
555-- Criar uma procedure para receber parametros marca dos veiculos e Cor
556--Se o usuario ao exercutar a procedure passar o parametro marca, o resultado
557--deve ser um print com informacoes de qtd de veiculos da marca
558--media de ano dos veiculos com a cor informada
559--se passar os dois parametros para a procedure, as duas mensagem deve
560--ser apresentada .
561
562
563alter procedure exercicio2_consulta
564@marca varchar(20),
565@cor varchar(20)
566
567as
568
569 declare @qtdveiculos int
570 declare @mediaano float
571
572 if @marca is not null
573 begin
574 set @qtdveiculos = (select count (*) from marca m inner join
575 veiculo v on m.codigo =v.marca where m.marca=@marca)
576
577 print ('Existem ' + convert (varchar (3),@qtdveiculos)+' veiculos com a marca ' +@marca)
578
579
580 end
581 if @cor is not null
582 begin
583 set @mediaano = (select avg(ano) from veiculo where cor = @cor )
584 print('A media dos veiculos da cor ' + @cor + ' é ' + convert (varchar(20),@mediaano))
585
586 end
587
588exec exercicio2_consulta 'Fiat' , 'Branco'
589
590
591select * from marca
592select * from veiculo
593
594-- apaga relacao das tabelas
595alter table veiculo drop constraint fk_vei_marc
596
597
598
599-- adiciona a chave e relaciona as tabelas seta que ele pode ser deletada toda relacao
600alter table veiculo add constraint fk_vei_marc foreign key (marca) references marca(codigo) on delete cascade
601
602delete from marca where codigo = 2 -- deleta a marca do codigo 2
603
604--procedure para deletar em cascada
605alter procedure cleanregistros
606@marca varchar(10)
607
608as
609
610 declare @qtdmarca int, @qtdveiculo int
611 set @qtdmarca =(select count(*) from marca where marca = @marca)
612 set @qtdveiculo =(select count(*) from veiculo v inner join marca m on
613 m.codigo = v.marca where m.marca = @marca)
614 if @qtdmarca > 0
615 begin
616 delete from marca where marca = @marca
617 print ('Deletado ' + convert (varchar(2),@qtdmarca)+' registros tabela marca' + 'e' + convert (varchar(3),@qtdveiculo) + 'veiculo tabela veiculo.')
618 end
619 else begin
620 print ('nao encontrada registros da marca ' + @marca)
621 end
622----------------------------------
623exec cleanregistros 'marca'
624
625
626create table oldcars (seq int not null identity (1,1),placa varchar(7),
627 marca varchar(10),modelo varchar(10), ano int , cor varchar(10))
628
629select * from oldcars
630
631
632
633
634
635
636------------------------------------
637
638-- procedure para inserir dados em tabelas . Obs . tratamento de dados
639alter procedure inserirveiculos
640@placa varchar(7),@modelo varchar(20),@marca varchar(10),@ano int,@cor varchar(10)
641as
642 declare @qualcodigo int
643 declare @exists int
644 set @exists = (select count(*) from marca where marca = @marca)
645 if @exists > 0
646 begin
647
648 set @qualcodigo = (select codigo from marca where marca = @marca)
649
650
651
652 end else begin
653 insert into marca values (@marca)
654 set @qualcodigo = (select codigo from marca where marca = @marca)
655
656
657 end
658 insert into veiculo values (@placa,@modelo,@qualcodigo,@ano,@cor)
659 if @ano < 1980
660 begin
661 insert into oldcars values (@placa,@marca,@modelo,@ano,@cor)
662 end
663
664------------------------------------------------------
665
666select * from marca
667select * from veiculo
668select * from oldcars
669-------------------------------------otimizando o codigo
670
671alter procedure inserirveiotimizado
672@placa varchar(7), @modelo varchar(20),@marca varchar(10),@ano int , @cor varchar (10)
673as
674
675 declare @exist int
676 declare @qualcodigo int
677 if (@ano is not null ) and ( @ano <= 1900)
678 begin
679 set @exist = (select count (*) from marca where marca =@marca)
680 if @exist = 0
681 insert into marca values (@marca)
682 set @qualcodigo = (select codigo from marca where marca = @marca)
683 insert into veiculo values (@placa , @modelo,@qualcodigo,@ano,@cor)
684 if @ano < 1980
685 insert into oldcars values (@placa,@marca,@modelo,@ano,@cor)
686
687 end
688
689
690-----------------------------------------------
691exec inserirveiotimizado 'ZZZ6699', 'Carroça','BTG',1930,'Marrom'
692select * from marca select * from veiculo
693select * from oldcars
694
695
696---------------------------------
697exec inserirveiculos 'foo3124','D20','Gm',2019,'branco'
698exec inserirveiculos 'ppu9899','Civic','honda',2019,'preto'
699exec inserirveiculos 'AAA8172','D20','Gm',1978,'verde'
700exec inserirveiculos 'bbb8765','f1000','ford',1970,'marrom'
701
702
703
704
705
706
707
708
709
710
711
712---------------------------------------------------------------
713
714-------------------------------------
715select modelo,cor
716from consulta1
717where cor like '%a%'
718
719--------------------
720--views - visões
721
722create view consulta_veiculo as
723select m.marca, v.modelo, v.cor, v.ano, v.placa
724from marca m full join veiculo v on m.codigo = v.marca
725where 1=1
726-------------------->fim da view
727
728select * from consulta_veiculo
729
730create view veich as
731select modelo, cor from veiculo
732-------------------> fim da view
733-------------------------------------------------------------
734
735
736create procedure testereturn
737@valor int
738 as
739 if (@valor <=10)
740 begin
741 print ('mensagem menor que 10 . abortar ')
742 return
743 end else begin
744 print ('mensagem maior que 10 continua')
745 end
746 print ('ultima linha')
747
748exec testereturn 20
749exec testereturn 5
750
751-------------------------------------------------------------
752
753select * from veich
754
755update veich set cor = 'Cinza' where modelo= 'Civic'
756
757select * from veiculo
758
759
760
761
762
763alter table veiculo add codigouser int ,
764 constraint pk_func_veic foreign key (codigouser)
765 references usuario (codigo)
766
767select * from veiculo
768select * from usuario
769
770
771
772update veiculo set codigouser = 1 where cor = 'Verde'
773update veiculo set codigouser = 3 where ano = 2019
774update veiculo set codigouser = 5 where ano in (2017,2000,30)
775update veiculo set codigouser = 2 where ano in (2018,1930,2015,1970)
776
777
778
779create table abastecimento (sequencia int not null identity (1,1),primary key (sequencia),
780 placa varchar(7), data date, hodometro int, combustivel varchar(3),
781 valorcombustivel float, qtdlitros float,
782 foreign key (placa) references veiculo (placa))
783
784insert into abastecimento values ('AAA8172', '09/06/2019',80000,'E',2.6,35)
785insert into abastecimento values ('AAA8172', '09/15/2019',80300,'E',2.5,38)
786insert into abastecimento values ('AAA8172', '03/03/2019',80430,'E',2.8,42)
787insert into abastecimento values ('dda2300', '05/08/2019',16100,'G',3.4,52)
788insert into abastecimento values ('iue8899', '08/10/2019',16500,'E',2.8,52)
789
790select * from abastecimento
791
792select v.modelo,m.marca,a.data,a.hodometro,a.combustivel,a.valorcombustivel,a.qtdlitros,(a.valorcombustivel * a.qtdlitros)
793 as 'Custo'
794 from marca m inner join veiculo v on m.codigo = v.marca inner join abastecimento a
795 on a.placa =v.placa
796order by a.data , v.modelo
797
798-------------------------------------------------------------
799
800select * from abastecimentos
801
802-------------------------------------------------------------
803create view abastecimentos
804as
805select v.modelo , m.marca, a.data, a.hodometro, a.combustivel, a.valorcombustivel,
806 a.qtdlitros, (a.valorcombustivel * a.qtdlitros) as 'Custo'
807from marca m inner join veiculo v on m.codigo = v.marca inner join abastecimento on a.placa = v.placa
808
809select * from abastecimentos
810
811--------------------------------------
812
813create view consultatipocombustivel
814as
815select convert (varchar(5) ,m.codigo) + '-' + m.marca as ' Marca',
816v.placa,v.modelo,a.combustivel + ' - ' +
817case when (a.combustivel = 'E') then 'Etanol' else
818case when (a.combustivel = 'G' ) then ' Gasolina' else
819case when (a.combustivel = 'D' ) then 'Diesel' else 'Outros'
820end end end as 'Tipo Combustivel'
821
822from marca m inner join veiculo v on m.codigo = v.marca
823 inner join abastecimento a on v.placa = a.placa
824
825
826--------------------------------------
827-- UTILIZANDO SUB- select
828-- utilizando subselect na clausula select
829
830select * from consultatipocombustivel
831where placa like '%A%'
832
833
834select v.modelo , m.marca, v.cor , (select max (a.hodometro )
835from abastecimento a where v.placa = a.placa)
836from veiculo v inner join marca m on v.marca = m.codigo
837order by v.modelo
838
839
840--utilizando subselect na clausula from
841-- consultando total de litros por veiculo
842select v.placa, v.modelo , v.cor, subquery1.total
843from veiculo v, (select a.placa, sum (a.qtdlitros) as total from abastecimento a
844 group by a.placa) subquery1
845where v.placa = subquery1.placa
846
847
848select v.placa , v.modelo,v.cor, subquery1.total
849from veiculo v, (select a.placa, sum (a.qtdlitros) as total from abastecimento a
850 group by a.placa) subquery1
851 on v.placa = subquery1.placa
852
853
854--subselect na clausura where
855select marca from marca
856--where codigo in (1, 3,4)
857where codigo in (select marca from veiculo where cor like '&P')
858----------------------------------------------------------------
859while-laço
860create procedure laco1
861
862declare @cont int
863set @cont=0
864while (@cont<= @numero)
865begin
866print('valor:'+ convert (varchar,@cont))
867set @cont=@cont+1
868end
869--------------------------
870exec laco1 25
871create table soma(contador int ,soma int)
872select*from soma
873
874alter procedure laco2
875 @num int
876 as
877 declare @cont int,@soma int
878 set @cont=0
879set @soma =0
880while( @cont<= @num ) begin
881set @soma =@soma+@cont
882print (convert (varchar,@soma))
883insert into soma values (@cont,@soma )
884set @cont+=1
885end
886------------
887exec laco2 1000
888select * from soma
889--exercio
890/* exercicio
891criar uma procedure com objetivo de validar informaçoes para cadastro
892de registro para a tabela abastecimento a procedure deve primeiro
893vereficara se os parametros placa hodometro qtd de litros e valor do combustvel
894foi corretamente informado no paarametro se algum desses parametros nao estiver presente
895sao obrigatorios e sair da procedure.
896apos validar os parametrosdeve verificar para a placa informada se existe um registro de hododometro
897superior ao que esta sendo apresentado no parametro, se nao satisfazer esta condicao sai da procedure
898se essas condicoes for satisfatoria, inserir o registro.
899quando validar o hodometro e ocorrer registro inferior ao ultimo registro
900essa informacao deve ser armazenada em uma tabela de logerro( cria essa tabela para receber logs com os campos atual ( automatica)
901hodometro e placa)
902
903
904
905
906*/
907
908create table logerro (seq int not null identity (1,1), datalog date ,
909 placa varchar(7), hodometro int )
910
911select * from logerro
912
913alter procedure ex1_13_09
914@placa varchar (7),
915@data date ,
916@hodometro int ,
917@tpcomb varchar(1) ,
918@valorcomb int,
919@qtd_litros int
920
921as
922
923alter @valida_hodo int, @existe_placa int
924
925
926 if (@placa = null or @data = null or @hodometro = null or @tpcomb = null or @valorcomb = null or @qtd_litros = null)
927 begin
928 print (' Falta parametros, parametros sao obrigatorios ')
929 return
930 end
931 --fazer if da placa se existe a placa
932 set @existe_placa = ( select count (*) from veiculo where placa = @placa)
933 if (@existe_placa = 0 )
934 begin
935 print (' Placa nao existe')
936 return
937 end
938 -----------------------------
939 set @valida_hodo = (select max(hodometro) from abastecimento where placa = @placa)
940 if (@valida_hodo <= @hodometro)begin
941 print ('hodometro informado incorretamente ')
942 insert into logerro values (getdate(),@placa,@hodometro)
943 return
944 end
945 --tudo validado
946 else
947 begin
948 insert into abastecimento values (@placa,@data,@hodometro,@tpcomb,@valorcomb,@qtd_litros)
949 print ('registro inserido com sucesso!!')
950 end
951
952exec ex1_13_09 'FFF1299', '09/06/2019',500,'E',2.8,52
953exec ex1_13_09 'xxxxx', '09/06/2019',500,'E',2.8,52
954exec ex1_13_09 'AAA8172 ' , '10/10/2019', 100, 'E',2.7,45
955
956select * from abastecimento
957/*2 - Criar uma procedure com auxilio de while com objetivo de inserir registros em uma tablea de
958projecao de gastos ( tabela deve ser criada os campos
959A procedure devera receber os parametros do mes e ano, atual e fazer uma projecao de gastos para os veiculos
960para os proximos 12 meses inserindo os registros na tabela criada.
961O calculo da projecao sera a media de litros utilizados e o valor do
962litro de combustivel acrescendo 2% ao mes. No final mostrar o custo total dos 12 meses em mensagem de print
963alem do select na tabela para projecao apresentada.
964*/
965--------------------------
966create table projecaogastos (seq int not null identity (1,1), placa varchar (7), ano int,
967 mes varchar(30), litros int, valor float)
968
969
970select * from projecaogastos
971
972select * from abastecimento
973--------------------------------------------
974--------------------------------------------
975
976
977create procedure exercf2
978@placa varchar(7)
979as
980 declare @ano int, @mes int, @existe_placa int
981 set @existe_placa = ( select count (*) from veiculo where placa = @placa)
982 if (@existe_placa = 0 )
983 begin
984 print (' Placa nao existe')
985 return
986 end
987 set @ano = year(getdate())
988 set @mes = month (getdate())
989 declare @medialitros float , @mediavalor float
990 set @medialitros = ( select avg (qtdlitros) from abastecimento where placa = @placa)
991 set @mediavalor = (select avg (valorcombustivel) from abastecimento where placa = @placa)
992 declare @cont int
993 set @cont = 0
994 while (@cont < 12) begin
995 if @mes = 12
996 begin
997 set @ano += 1
998 set @mes = 1
999 end
1000 else begin
1001 set @mes += 1
1002 end
1003 set @medialitros = @medialitros * 1.02
1004 set @mediavalor = @mediavalor * 1.02
1005 insert into projecaogastos values (@placa,@ano,@mes,@medialitros,convert(decimal(10,2),@mediavalor))
1006 set @cont += 1
1007
1008end
1009select * from projecaogastos where placa = @placa
1010
1011
1012
1013-----------------------------------------------------------
1014
1015
1016-- trigger gatilho
1017
1018--Ação executada automaticamente junto com comandos que manipulam dados
1019-- nas tabelas
1020
1021select * from veiculo
1022select * from marca
1023select * from abastecimento
1024
1025
1026create trigger trexemplo1 on marca for insert, update
1027as
1028
1029 print('trigger acionada')
1030
1031-----------------------------------------------------
1032/* sintaxe
1033create/alter trigger <nome> on <tabela> for insert,update,delete
1034*/
1035
1036
1037insert into marca values('teste')
1038update marca set marca = 'teste2' where marca = 'teste'
1039select * from marca
1040insert into marca values ('Tesla')
1041
1042
1043
1044create table log (numerador int not null identity(1,1),
1045 descricao varchar(180))
1046
1047
1048
1049select * from log
1050------------------------------------------------
1051alter trigger trexemplo1 on marca for insert,update
1052as
1053 print('trigger acionada')
1054 insert into log values('marca modificada '+
1055 convert(varchar,getdate()))
1056--------------------------------------------
1057insert into marca values('Mazda')
1058
1059select * from log
1060
1061select * from marca
1062
1063--drop trigger <nometrigger>
1064
1065
1066create trigger tr_exemplo2 on veiculo for update,insert,delete
1067as
1068 declare @qtd int
1069 select @qtd = count(*) from deleted
1070 if @qtd > 0
1071 begin
1072 print('Registro da tabela alterado ou deletado')
1073 end
1074 select * from deleted
1075
1076----------------------------------
1077select * from veiculo
1078update veiculo set ano = 1970 where placa = 'AAA8172'
1079insert into veiculo values ('BBB2222','Fusca',5,1969,'Azul',1)
1080delete from veiculo where placa ='BBB2222'
1081
1082--Alterar a trigger anterior para permitir acao apenas em caso
1083--de delete. apresentar a quantidade de registros deletados da tabela
1084--e quais placas que foram excluidas.
1085-- se nenhum registro for excluido tambem apresentar a mensagem.
1086
1087
1088alter trigger tr_exemplo2 on veiculo for delete
1089
1090as
1091 declare @qtd int, @resultado varchar(200)
1092 set @resultado = ''
1093 select @qtd = count(*) from deleted -- qtd de registro deletado
1094 print(convert(varchar(3),@qtd) + ' Registros deletados')
1095 select @resultado = @resultado + placa + ';' from deleted
1096 select placa from deleted
1097 print ( 'placas excluídas' + @resultado)
1098
1099------------------------
1100
1101select * from veiculo
1102
1103
1104
1105insert into veiculo values ('QQQ1111','model1',3,1000,'Bordo',5)
1106insert into veiculo values ('RRR2222','model2',3,2000,'Anil',5)
1107
1108delete from veiculo where modelo in ( 'model1','model2')
1109
1110select * from log
1111
1112
1113----------------------------------------------
1114
1115alter trigger tr_exemplo2 on veiculo for delete
1116
1117as
1118 declare @qtd int, @resultado varchar(200)
1119 set @resultado = ''
1120 select @qtd = count(*) from deleted -- qtd de registro deletado
1121 print(convert(varchar(3),@qtd) + ' Registros deletados')
1122 select @resultado = @resultado + placa + ';' from deleted
1123 select placa from deleted
1124 print ( 'placas excluídas' + @resultado)
1125 insert into log values (convert(varchar(2),@qtd)+
1126 ' registro(s) deletados(s) ' + @resultado)
1127
1128-------------------
1129--complementando a sintaxe. para depois ao invés de
1130--create trigger <nome> on <tabela> [for / after/ instead of ] [insert,update,delete]
1131--as
1132-- conteudo da trigger
1133------------------------------------------------
1134-/* for - executado junto com o comando da ação.
1135 after - após o comando da ação.
1136 instead of - executada no lugar do comando da ação.
1137*/
1138
1139
1140select * from usuario
1141create table logusuario (data_ativ datetime,atividade varchar(100))
1142
1143select * from logusuario
1144
1145
1146create trigger exec1 on usuario after insert
1147as
1148 insert into logusuario values (getdate(),'registro inserido')
1149--fim
1150
1151
1152insert into usuario values('Geraldo','Diretor', 1800,1)
1153
1154select * from usuario
1155select * from logusuario
1156
1157
1158alter trigger exec1 on usuario instead of insert
1159as
1160 insert into logusuario values (getdate(),'registro inserido')
1161
1162
1163-- fim
1164
1165
1166alter trigger exec1 on usuario instead of insert
1167as
1168 insert into usuario values ( 'Geraldo','Diretor',1800,0)
1169 insert into logusuario values (getdate(),'registro inserido')
1170
1171--------------------------------------
1172insert into usuario values('Geraldo','Diretor', 1800,1)
1173
1174
1175-------------------------------------------------------------------
1176
1177select * from veiculo
1178
1179--set nocount on -- nao mostra informacoes e alteracao de registro
1180--set no couint off - mostra informacoes de alteracao de registro
1181
1182-- validar cadastro antes de inserir com trigger instead of
1183
1184create trigger cadveiculo on veiculo instead of insert
1185-- ao inves do insert
1186as
1187
1188 if not exists (select v.placa from veiculo as v inner join inserted as i
1189 on v.placa = i.placa )
1190
1191
1192 begin
1193 insert into veiculo (placa,modelo,marca,ano,cor,codigouser)
1194 select placa,modelo,marca,ano,cor,codigouser from inserted
1195 print('cadastro finalizado com sucesso')
1196
1197 end else begin
1198 print('cadastro falhou')
1199
1200 end
1201select * from inserted
1202
1203----------------------------------------------------------------------
1204
1205
1206select * from veiculo
1207
1208
1209insert into veiculo values ('AAA9999','GOL', '1' , 1980,'Vermelho',1)
1210insert into veiculo values ('BBB2222','GOL', '1' , 1992,'Branco',1)
1211----------------------------------------------------------------------
1212
1213create trigger alterveiculo on veiculo for update
1214
1215as
1216
1217 declare @modeloantigo varchar(20), @modelonovo varchar(20)
1218 select @modelonovo = modelo from inserted
1219 select @modeloantigo = modelo from deleted
1220 print ('Modelo ' + @modeloantigo +' foi alterado para ' + @modelonovo)
1221
1222-----------------------------------------------------------------------
1223
1224
1225select * from veiculo
1226
1227update veiculo set modelo = 'D-21' where placa = 'AAA8172'
1228
1229select * from log
1230
1231
1232
1233
1234
1235alter trigger alterveiculo on veiculo for update
1236
1237as
1238
1239 declare @modeloantigo varchar(20), @modelonovo varchar(20)
1240 select @modelonovo = modelo from inserted
1241 select @modeloantigo = modelo from deleted
1242 insert into log values('Modelo ' + @modeloantigo +' foi alterado para ' + @modelonovo)
1243
1244
1245
1246select * from veiculo
1247
1248update veiculo set modelo = 'Karman' where placa = 'AAA9999'
1249
1250
1251select * from log
1252
1253--faltou validar qual campo esta sendo modificado. if
1254
1255
1256--Exercicio para treinar em casa.
1257/*
1258Criar uma tabela caixa com os campos data, codigo da venda e valor float.
1259
1260Criar duas trigger para a tabela de vendas com objetivo de :
1261 trigger 1 - para ocorrencias de insert - preencher automaticamente a tabela
1262 caixa atualizando o saldo para a data do registro de insert de tabela de venda.
1263
1264
1265 trigger 2 - para ocorrencias de delete - preencher automaticamente a tabela
1266 caixa atualizando o saldo para a data do registro de delete que foi dado na tabela de venda
1267
1268
1269
1270*/