APS material segundo semestre

Prévia do material em texto

Exercícios: Sistemas de Banco de Dados II
Stored Procedure/Gerenciamento de Transações
1. (1,0 ponto) Faça uma Store Procedure para calcular o IMC ( Índice de Massa Corporal), conforme tabela abaixo. O cálculo do IMC é feito dividindo o peso (em quilogramas) pela altura (em metros) ao quadrado.
	Resultado
	Situação
	Abaixo de 17
	Muito abaixo do peso
	Entre 17 e 18,49
	Abaixo do peso
	Entre 18,5 e 24,99
	Peso normal
	Entre 25 e 29,99
	Acima do peso
	Entre 30 e 34,99
	Obesidade I
	Entre 35 e 39,99
	Obesidade II (severa)
	Acima de 40
	Obesidade III (mórbida)
EXEC stpIMC(150,1.87);
Resultado esperado: 
'Obesidade III (mórbida)', '42.895135691612566'
create proc [dbo].[stpIMC]
	@PESO float,
	@ALTURA float
as
begin
	declare @IMC float
	set @IMC = @PESO/@ALTURA
	if @IMC = 17
	begin 
		print 'Considerado muito abaixo do peso!'
	end
	if @IMC > 17 and @IMC < 18.49
	begin 
		print 'Considerado abaixo do peso!'
	end
	if @IMC > 18.5 and @IMC < 29.99
	begin 
		print 'Considerado acima do peso!'
	end
	if @IMC > 30 and @IMC < 34.99
	begin 
		print 'Está em obesidade I!'
	end
	if @IMC > 35 and @IMC < 39.99
	begin 
		print 'Está em obesidade II(Severa)!'
	end
	if @IMC > 40
	begin 
		print 'Está em Obesidade III(Mórbida)!'
	end
	
end
2. (1,0 ponto) Faça uma Store Procedure que some todos os números pares entre dois números. 
EXEC sptNumerosPares(0,100,@x out); 
select @x; 
Resultado esperado: 2550
create proc [dbo].[sptNumerosPares]
	@INICIO int,
	@FIM int
as
begin
	declare @SOMA int = 0
	
	while @INICIO <= @FIM
	begin
		if @INICIO%2 = 0
		begin
			set @SOMA = @SOMA + @INICIO
		end
		set @INICIO = @INICIO + 1
	end
	print @SOMA
end
3. (3,0 pontos) O DER abaixo foi criado para armazenar os dados de um sistema financeiro de um banco. Crie o banco de dados e faça as inserções, conforme orientações abaixo: 
A tabela TipoMovimento armazena os possíveis tipos de transações bancárias, a saber:
IdTipoMovimento	Descricao
1	Saque
2	Depósito
3	Transferência de
4	Transferência para
A tabela TipoConta armazena os tipos de conta:
IdTipoConta	Descricao
1	CC
2	Poupança
A tabela Conta armazena as contas dos clientes. Um Cliente pode ter várias contas.
A tabela Movimento armazena as transações ocorridas.
Ao fazer a transferência de uma conta para outra, devem ser inserido na tabela Movimento dois registros de movimentação da conta as duas operações, que devem ser consideradas, portanto, como uma única transação. Para utilizar esse recurso, devemos usar o recurso de transação explícita, utilizando os comandos BEGIN TRAN, COMMIT, ROLLBACK.
Faça uma Stored Procedure fazer efetuar uma transferência bancária, utilizando gerenciamento de transação e controle de erros BEGIN TRY...END TRY/BEGIN CATCH...END CATCH. A SP deve receber três parâmetros: IdConta de Origem e IdConta de Destino. Se alguma dessas contas não for encontrada, deve ser retornada a mensagem de erro (conta de origem/destino inexistente). Qualquer outra mensagem de erro também devem ser informada.
create proc [dbo].[createTransition]
	@VALOR float,
	@IDCONTA int,
	@TIPOTF char(100)
as
begin
	begin try
		begin transaction
			if(NOT EXISTS (select *from dbo.Conta where IdConta = @IDCONTA))
			begin
				;throw 51110 ,'A conta de destino ou origem não existe!!,1
			end
			if @TIPOTF != 'Saque' and @TIPOTF != 'Depósito' and @TIPOTF != 'Uma transferência de: ' and @TIPOTF != 'Uma transferência para: '
			begin
				;throw 51001,'Tipo de transacao disponivel, Saque, Depósito, Transferência de e Transferência para',1
			end
			if @TIPOTF = 'Saque'
			begin
				insert into 
					dbo.Movimento (IdTipoMovimento,DataHora,Valor,IdConta)
				values
					(1,SYSDATETIME(),@VALOR,@IDCONTA)
			end
			if @TIPOTF = 'Depósito'
			begin
				insert into 
					dbo.Movimento (IdTipoMovimento,DataHora,Valor,IdConta)
				values
					(2,SYSDATETIME(),@VALOR,@IDCONTA)
			end
			if @TIPOTF = 'Uma transferência de:'
			begin
				insert into 
					dbo.Movimento (IdTipoMovimento,DataHora,Valor,IdConta)
				values
					(3,SYSDATETIME(),@VALOR,@IDCONTA)
			end
			if @TIPOTF = 'Uma transferência para:'
			begin
				insert into 
					dbo.Movimento (IdTipoMovimento,DataHora,Valor,IdConta)
				values
					(4,SYSDATETIME(),@VALOR,@IDCONTA)
			end
		commit
	end try
	begin catch
		declare @MESSAGE varchar(max) = concat('Error:',error_message(),' ',error_number())
		print @MESSAGE
		if @@TRANCOUNT > 0
		begin
			rollback
		end
		;throw
	end catch
end