Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE cargo (cd_cargo NUMBER(3) primary key,
- nm_cargo VARCHAR(10),
- salario NUMBER(10,2));
- BEGIN
- INSERT INTO cargo VALUES (1,'DBA',18500);
- INSERT INTO cargo VALUES (2,'Analista',7200);
- INSERT INTO cargo VALUES (3,'Estagiario',2500);
- END;
- CREATE TABLE funcionario (cd_fun NUMBER(3) primary key,
- nm_fun VARCHAR(10),
- cargo_fk references cargo);
- BEGIN
- INSERT INTO funcionario VALUES (10,'Marcel',1);
- INSERT INTO funcionario VALUES (20,'Silvania',1);
- INSERT INTO funcionario VALUES (30,'Ana',2);
- INSERT INTO funcionario VALUES (40,'Carlos',3);
- END;
- CREATE OR REPLACE PROCEDURE calculo IS CURSOR c_aumentosalario
- IS SELECT salario FROM cargo;
- v_naotrab NUMBER(5,2) := 2.00 ; --Insira a quantidade de dias não trabalhados
- v_diasmes NUMBER(5,2) := 30 ; --Insira a quantidade de dias no mês
- BEGIN
- FOR v_sal IN c_aumentosalario
- LOOP
- IF v_sal.salario <= 2500 THEN
- UPDATE cargo SET salario = salario * 1.125 WHERE salario = v_sal.salario;
- ELSIF v_sal.salario > 2500 AND v_sal.salario <= 7800 THEN
- UPDATE cargo SET salario = salario * 1.08 WHERE salario = v_sal.salario;
- ELSIF v_sal.salario > 7800 AND v_sal.salario <= 15000 THEN
- UPDATE cargo SET salario = salario * 1.025 WHERE salario = v_sal.salario;
- ELSIF v_sal.salario > 15000 THEN
- UPDATE cargo SET salario = salario * 1 WHERE salario = v_sal.salario;
- END IF;
- COMMIT;
- END LOOP;
- IF v_naotrab < ((v_diasmes/100) * 10) THEN
- UPDATE cargo SET salario = salario + 100;
- END IF;
- END calculo;
- DECLARE
- CURSOR c_display IS SELECT f.cd_fun, f.nm_fun, f.cargo_fk, c.salario, c.nm_cargo, c.cd_cargo FROM funcionario f inner join cargo c ON f.cargo_fk = c.cd_cargo;
- v_sal cargo.salario%TYPE;
- v_func funcionario.nm_fun%TYPE;
- v_salantigo NUMBER(7,2);
- BEGIN
- FOR v_cargos IN c_display
- LOOP
- DBMS_OUTPUT.PUT_LINE('Nome: '||v_cargos.nm_fun||' - Salário Antigo: R$ '||v_cargos.salario);
- END LOOP;
- calculo;
- DBMS_OUTPUT.PUT_LINE('____________________________________________________________________');
- FOR v_display IN c_display
- LOOP
- DBMS_OUTPUT.PUT_LINE('Nome: '||v_display.nm_fun||' - Salário Novo R$: '||v_display.salario);
- END LOOP;
- END;
- SELECT * FROM cargo;
- SELECT * FROM funcionario;
- DROP TABLE funcionario;
- DROP TABLE cargo;
- DROP PROCEDURE calculo;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement