AULA 10 CREATE PROCEDURE SP CALCULA IR

Prévia do material em texto

--ROTEIRO DE NOSSA AULA SOBRE CURSOR/VERTOR/ARRAY 
--MODELO FÍSICO A SER UTILIZADO 
 
 
 
 
--INIALMENTE CRIAMOS A TABELA QUE SERÁ UTILIZADA PARA A BUSCA DA ALÍQUOTA DE IR 
--CORRESPONDENTE AO SALÁRIO DO FUNCIONÁRIO 
--PARA CRIÁ-LA TEMOS: 
CREATE TABLE ALIQUOTA_IR 
(ALIQUOTA_ID INT IDENTITY PRIMARY KEY NOT NULL, 
ALIQUOTA INT NULL, 
VLR_INICIO NUMERIC(18,2) NULL, 
VLR_FINAL NUMERIC(18,2) NULL) 
 
--APÓS A CRIAÇÃO DA MESMA PODEMOS ENTÃO INCLUIR OS DADOS ABAIXO: 
INSERT INTO ALIQUOTA_IR VALUES (0,1,3000) 
INSERT INTO ALIQUOTA_IR VALUES (10,3001,6000) 
INSERT INTO ALIQUOTA_IR VALUES (15,6001,10000) 
INSERT INTO ALIQUOTA_IR VALUES (20,10001,100000) 
 
--PARA CRIAÇÃO DA PROCEDURE USAMOS CREATE E DEPOIS ALTER PARA ALTERÁ-LA 
CREATE PROCEDURE SP_CALCULA_IR 
 
--PARA CADA MÊS QUE FORMOS FAZER O CÁLCULO DO IR TEMOS QUE INFORMAR O MÊS E ANO, --PARA ISSO TEMOS QUE CRIAR OS PARÂMETROS @MES E @ANO PARA RECEBER OS DADOS 
--NECESSÁRIOS PARA O PROCESSAMENTO DA PROCEDURE 
@MES INT, @ANO INT 
 
AS 
--PARA QUE NÃO SEJA APRESENTADO LINHAS DE INFORMAÇÕES DURANTE A EXECUÇAO DA 
--PROCEDURE LIGAMOS NÃO CONTAGEM 
SET NOCOUNT ON 
 
--PRECISAMOS VERIFICAR SE A TABELA ESTÁ CRIADA NO BANCO DE DADOS 
--ASSIM ACESSMOS A TABELA DO SISTEMA --SYSOBJECTS-- E NELA É GRAVADA 
--TUDO O CRIAMOS NO BANCO DE DADOS, SEJA TABELAS, PROCEDURES E ETC. 
--EXISTE O OBJETO COM NOME IR? 
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'IR') 
 
--SE EXISTIR ENTRA AQUI 
--TODO BLOCO DE IF E WHILE USAMOS O INÍCIO E FIM (BEGIN/END) 
 BEGIN 
 
--COMO A TABELA JÁ EXISTE VAMOS ENTÃO ELIMINAR OS DADOS SE HOUVEREM PARA NÃO 
--TERMOS DUPLICIDADE DE DADOS COM OS DADOS DOS PARÂMETROS INFORMADOS POR 
--EXEMPLO: EXECUTE SP_CALCULA_IR 10,2015 
 DELETE IR WHERE MES = @MES AND ANO = @ANO 
 END 
 
ELSE 
 
--SE NÃO EXISTIR A TABELA, ENTÃO A CRIAMOS. 
 BEGIN 
 CREATE TABLE IR 
 (IR_ID INT IDENTITY PRIMARY KEY NOT NULL, 
 NM_FUNCIONARIO VARCHAR(80) NULL, 
 SALARIO NUMERIC (18,2) NULL, 
 ALIQUOTA INT NULL, 
 DESCONTO_IR NUMERIC (18,2) NULL, 
 SALARIO_LIQUIDO NUMERIC (18,2) NULL, 
 MES INT NULL, 
 ANO INT NULL) 
 END 
 
--PARA NOSSO PROCESSAMENTO IDENTIFICAMOS QUE PRECISAREMOS DE VARIÁVEIS DE 
--MEMÓRIA PARA ARMAZENAR O IR_ID (PK) DA TABELA IR, O VALOR DO SALÁRIO E A 
--ALÍQUOTA QUE USAREMOS PARA CALCULAR O DESCONTO DE IR OS TIPOS DAS VARIÁVEIS 
--DEPENDEM DAS TABELAS ENVOLVIDAS ASSIM É NECESSÁRIO VERIFICAR NO MODELO FÍSICO 
DECLARE @IR_ID INT,@SALARIO NUMERIC (18,2), @ALIQUOTA INT 
 
--COMO É UMA TABELA FATO,IR, (DATAMART) NÃO PERTENCE AO BANCO COORPORATIVO VAMOS 
--A INCLUIR OS DADOS QUE PRECISAMOS DA TABELA FUNCIONÁRIO, E OS DADOS QUE SERÁO 
--CALCULADOS DEPOIS CADASTRAMOS COM ZEROS, QUE É O CASO DA ALÍQUOTA, DO 
--DESCONTO_IR, E DO SALARIO_LIQUIDO COMO ABAIXO 0,0,0 
INSERT INTO IR SELECT NM_FUNCIONARIO, SALARIO,0,0,0,@MES, @ANO FROM FUNCIONARIO 
 
--VAMOS CRIAR UM CURSOR (ARRAY/VETOR) PARA RECEBER OS DADOS QUE PRECISAMOS PARA 
--REALIZAR OS CÁLCULOS ASSIM APÓS TERMOS CADASTRADOS OS DADOS, TEMOS QUE BUSCAR 
--O VALOR DA ALÍQUOTA PARA CALCULAR O DESCONTO_IR E POR FIM O SALARIO_LIQUIDO, 
--MAS SOMENTE NO MÊS E ANO INFORMADO, POR ISSO TEMOS QUE USAR A CLÁUSULA 
--WHERE ANO = @ANO AND MES = @MES 
DECLARE PESQUISA_IR CURSOR 
FOR SELECT IR_ID FROM IR WHERE ANO = @ANO AND MES = @MES 
 
--APÓS A DECLARAÇÃO DO CURSOR VAMOS ABRI-LO 
OPEN PESQUISA_IR 
 
--TEMOS QUE COLOCAR O PONTEIRO DO CURSOR/VETOR NA PRIMEIRA LINHA ASSIM TEMOS O 
--COMANDO ABAIXO PERCEBA QUE PARA FAZER ISSO PRECISAMOS DE UMA VARIÁVEL DE 
--MEMÓRIA, POR ISSO A CRIAMOS ANTERIORMENTE SE HOUVER MAIS DE UMA COLUNA NO 
--CURSOR TEMOS CRIAR TANTAS COLUNAS QUANTAS NECESSITAMOS PARA NOSSO 
--PROCESSAMENTO, NESTE CASO SÓ PRECISAMOS DO IR_ID 
FETCH PESQUISA_IR INTO @IR_ID 
 
--COMEÇAMOS O LAÇO DE REPETIÇÃO COM A CONDIÇÃO FAÇA ENQUANTO O STATUS DE FIM DAS 
--LINHAS ARMEZANADAS NO CURSOR FOR FALSO, OU SEJA =0, QUANDO FOR 1 CHEGOU ATÉ A 
--ÚLTIMA LINHA 
WHILE @@FETCH_STATUS = 0 
 BEGIN 
 
--ENTENDA QUE TEMOS QUE TER NA MEMÓRIA O VALOR DO SALÁRIO PARA BUSCARMOS A 
--ALÍQUOTA NA OUTRA TABELA (ALIQUOTA_IR) ASSIM DA PRIMEIRA LINHA DO CURSOR 
--SELECIONAMOS O SALÁRIO COM BASE NA PK IR_ID 
 SELECT @SALARIO = SALARIO FROM IR WHERE IR_ID = @IR_ID 
 
--COM O SALÁRIO ARMAZENADO NA VARIÁVEL DE MEMÓRIA @SALARIO, FAZEMOS A BUSCA DA 
--ALÍQUITA CORRESPONDENTE, E ARMAZENAMOS @ALIQUITA, COM A CONDIÇÃO ABAIXO: 
--WHERE @SALARIO>= VLR_INICIAL AND @SALARIO<= VLR_FINAL 
--LEMBRE-SE QUE O INTERVALO É FECHADO ASSIM TEMOS QUE USAR AND, PARA VALORES 
--EXTREMADOS USAMOS OR EXEMPLO SALARIO > 10000 OR SALARIO < 5000 
 SELECT @ALIQUOTA = ALIQUOTA FROM ALIQUOTA_IR WHERE @SALARIO>= VLR_INICIAL AND @SALARIO<= VLR_FINAL 
 
--AGORA TEMOS O QUE PRECISAMOS PARA FAZER OS CÁLCULOS NECESSÁRIO ASSIM VAMOS 
--UTILIZAR OS VALORES ZERADOS DE DESCONTO_IR,ALIQUOTA E SALARIO_LIQUIDO MUITO 
--IMPORTANTE É QUE AGORA VOLTAMOS PARA A TABELA IR PARA FAZER O UPDATE, E PARA 
--NÃO ALTERAR O REGISTRO ERRADO USAMOS A PK, POR ISSO QUE ARMAZENAMOS A PK NA 
--VARIÁVEL DE MEMÓRIA @IR_ID ANTES DE IR PARA A TABELA DE IR 
 UPDATE IR SET DESCONTO_IR = SALARIO * @ALIQUOTA/100, ALIQUOTA = @ALIQUOTA, SALARIO_LIQUIDO = SALARIO - SALARIO * @ALIQUOTA/100 
 
--FEITO A ATUALIZAÇÃO NA LINHA APONTADA DO CURSO VAMOS AGORA PARA A PRÓXIMA, 
--NOTE QUE É O MESMO COMANDO QUE ESTÁ ANTES DO INÍCIO DO LAÇO DE REPETIÇÃO 
--WHILE (SEMPRE SERÁ ASSIM) 
 FETCH PESQUISA_IR INTO @IR_ID 
 END 
--APÓS O FIM DO LAÇO DE REPETIÇÃO PODEMOS FECHAR (CLOSE) O CURSO QUE 
--PREVIAMENTE ABRIAMOS (OPEN) 
 CLOSE PESQUISA_IR 
 
--POR FIM TIREMOS O CURSOR DA MEMÓRIA 
 DEALLOCATE PESQUISA_IR 
 
--ESTE SELECT É SOMENTE PARA VER O RESULTADO APÓS O PROCESSAMENTO DA PROCEDURE 
--DEPOIS O RETIRAREMOS 
SELECT * FROM IR WHERE ANO = @ANO AND MES = @MES 
 
 
 
--SELECT * FROM FUNCIONARIO 
--SP_CALCULA_IR 
--SELECT * FROM IR 
 
--CREATE TABLE ALIQUOTA_IR 
--(ALIQUOTA_ID INT IDENTITY PRIMARY KEY NOT NULL, 
--ALIQUOTA INT NULL, 
--VLR_INICIO NUMERIC(18,2) NULL, 
--VLR_FINAL NUMERIC(18,2) NULL) 
 
--INSERT INTO ALIQUOTA_IR VALUES (0,1,3000) 
--INSERT INTO ALIQUOTA_IR VALUES (10,3001,6000) 
--INSERT INTO ALIQUOTA_IR VALUES (15,6001,10000) 
--INSERT INTO ALIQUOTA_IR VALUES (20,10001,100000) 
 
--CREATE TABLE dbo.IR( 
--IR_ID int IDENTITY(1,1) NOT NULL, 
--NM_FUNCIONARIO varchar(80) NULL, 
--SALARIO numeric(18, 2) NULL, 
--ALIQUOTA INT NULL, 
--DESCONTO_IR numeric(18, 2) NULL, 
--SALARIO_LIQUIDO numeric(18, 2) NULL) 
 
 
 
ALTER PROCEDURE SP_CALCULA_IR 
AS 
SET NOCOUNT ON 
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE name = 'IR') 
 BEGIN 
 TRUNCATE TABLE IR 
 END 
ELSE 
 BEGIN 
 CREATE TABLE IR (IR_ID INT IDENTITY PRIMARY KEY NOT NULL, 
 NM_FUNCIONARIO VARCHAR(80) NULL, 
 SALARIO NUMERIC(18,2) NULL, 
 DESCONTO_IR NUMERIC(18,2), 
 SALARIO_LIQUIDO NUMERIC(18,2)) 
 END 
 
INSERT INTO IR SELECT NM_FUNCIONARIO,SALARIO,0,0,0 FROM FUNCIONARIO 
 
DECLARE @INICIO INT,@FIM INT,@IR_ID INT,@SALARIO NUMERIC(18,2),@ALIQUOTA INT 
 
SELECT @INICIO=MIN(IR_ID),@FIM=MAX(IR_ID) FROM IR 
 
 
WHILE @INICIO <= @FIM 
 BEGIN 
 SELECT @IR_ID=IR_ID, @SALARIO = SALARIO FROM IR WHERE IR_ID = @INICIO 
 SELECT @ALIQUOTA=ALIQUOTA FROM ALIQUOTA_IR WHERE VLR_INICIO <= @SALARIO AND VLR_FINAL >= @SALARIO 
 UPDATE IR SET DESCONTO_IR = SALARIO * @ALIQUOTA / 100,SALARIO_LIQUIDO = SALARIO - (SALARIO * @ALIQUOTA / 100),ALIQUOTA=@ALIQUOTA WHERE IR_ID = @IR_ID 
 SET @INICIO = @INICIO + 1 
 END 
 
SELECT * FROM IR 
 
 
CREATE PROCEDURE SP_CURSOR_FUNCIONARIO_IR 
AS 
DECLARE @IR_ID INT,@ALIQUOTA INT,@SALARIO NUMERIC(18,2) 
 
 
DECLARE PESQUISA_FUNCIONARIO CURSOR 
FOR 
SELECT IR_ID,SALARIO FROM IROPEN PESQUISA_FUNCIONARIO 
FETCH PESQUISA_FUNCIONARIO INTO @IR_ID,@SALARIO 
 
WHILE @@FETCH_STATUS = 0 
 BEGIN 
 SELECT @SALARIO=SALARIO FROM IR WHERE IR_ID = @IR_ID 
 
 SELECT @ALIQUOTA=ALIQUOTA FROM ALIQUOTA_IR WHERE VLR_INICIO<= 
 @SALARIO AND VLR_FINAL >= @SALARIO 
 
 UPDATE IR SET DESCONTO_IR = SALARIO * @ALIQUOTA/100, 
 SALARIO_LIQUIDO = SALARIO - (SALARIO * @ALIQUOTA/100), 
 ALIQUOTA=@ALIQUOTA WHERE IR_ID = @IR_ID 
 
 
 FETCH PESQUISA_FUNCIONARIO INTO @IR_ID,@SALARIO 
 END 
CLOSE PESQUISA_FUNCIONARIO 
DEALLOCATE PESQUISA_FUNCIONARIO 
 
-- 
 
alter PROCEDURE SP_IR 
@MES INT, @ANO INT 
AS 
SET NOCOUNT ON 
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'IR') 
 BEGIN 
 DELETE IR WHERE MES = @MES AND ANO = @ANO 
 END 
ELSE 
 BEGIN 
 CREATE TABLE IR 
 (IR_ID INT IDENTITY PRIMARY KEY NOT NULL, 
 NM_FUNCIONARIO VARCHAR(80) NULL, 
 SALARIO NUMERIC (18,2) NULL, 
 ALIQUOTA INT NULL, 
 DESCONTO_IR NUMERIC (18,2) NULL, 
 SALARIO_LIQUIDO NUMERIC (18,2) NULL, 
 MES INT NULL, 
 ANO INT NULL) 
 END 
 
DECLARE @IR_ID INT,@SALARIO NUMERIC (18,2), @ALIQUOTA INT 
 
INSERT INTO IR SELECT NM_FUNCIONARIO, SALARIO,0,0,0,@MES, @ANO FROM FUNCIONARIO 
 
DECLARE PESQUISA_IR CURSOR 
FOR SELECT IR_ID FROM IR WHERE ANO = @ANO AND MES = @MES 
OPEN PESQUISA_IR 
 
FETCH PESQUISA_IR INTO @IR_ID 
 
WHILE @@FETCH_STATUS = 0 
 BEGIN 
 SELECT @SALARIO = SALARIO FROM IR WHERE IR_ID = @IR_ID 
 SELECT @ALIQUOTA = ALIQUOTA FROM ALIQUOTA_IR WHERE @SALARIO>= VLR_INICIAL AND @SALARIO<= VLR_FINAL 
 UPDATE IR SET DESCONTO_IR = SALARIO * @ALIQUOTA/100, ALIQUOTA = @ALIQUOTA, SALARIO_LIQUIDO = SALARIO - SALARIO * @ALIQUOTA/100 
 FETCH PESQUISA_IR INTO @IR_ID 
 END 
 CLOSE PESQUISA_IR 
 DEALLOCATE PESQUISA_IR 
SELECT * FROM IR WHERE ANO = @ANO AND MES = @MES