Andre Long
CISP 350 Oracle
April 27, 2006
Chapter 4B PSC 

CASE#4a

4a)
SET SERVEROUTPUT ON SIZE 6000
DECLARE
employee_start_date		DATE :=	TO_DATE('05/01/1993', 'MM/DD/YYYY');
current_date			DATE :=	SYSDATE;
years					NUMBER;
bf1						NUMBER;
job_type				VARCHAR2(2):= 'A';
bf2						NUMBER;
extra_merit				BOOLEAN:= False;
current_salary			NUMBER:= 55000;
bf3						NUMBER;
bonus					NUMBER;

BEGIN
years	:=	ROUND( (current_date - employee_start_date)/365.25, 0);
IF	years < 6 THEN
	bf1:=100;
ELSIF	years < 11 THEN
	bf1:=200;
ELSIF	years < 16 THEN
	bf1:=300;
ELSE
		bf1:=500;
END IF;

IF job_type = 'A' THEN
	bf2:=100;
ELSE
	bf2:=200;
END IF;

IF extra_merit = True THEN
	bf3:=current_salary * .05;
ELSE
	bf3:=0;
END IF;

bonus:=bf1 + bf2 + bf3;

DBMS_OUTPUT.PUT_LINE(bonus);

END;

CASE#4b

DECLARE
employee_start_date		DATE :=	TO_DATE('01/05/1982', 'MM/DD/YYYY');
current_date			DATE :=	SYSDATE;
years					NUMBER;
bf1						NUMBER;
job_type				VARCHAR2(2):= 'B';
bf2						NUMBER;
extra_merit				BOOLEAN:= true;
current_salary			NUMBER:= 758000;
bf3						NUMBER;
bonus					NUMBER;

BEGIN
years	:=	ROUND( (current_date - employee_start_date)/365.25, 0);
IF	years < 6 THEN
	bf1:=100;
ELSIF	years < 11 THEN
	bf1:=200;
ELSIF	years < 16 THEN
	bf1:=300;
ELSE
		bf1:=500;
END IF;

IF job_type = 'A' THEN
	bf2:=100;
ELSE
	bf2:=200;
END IF;

IF extra_merit = True THEN
	bf3:=current_salary * .05;
ELSE
	bf3:=0;
END IF;

bonus:=bf1 + bf2 + bf3;

DBMS_OUTPUT.PUT_LINE(bonus);

END;

4c)
DECLARE
employee_start_date		DATE :=	TO_DATE('07/15/2000', 'MM/DD/YYYY');
current_date			DATE :=	SYSDATE;
years					NUMBER;
bf1						NUMBER;
job_type				VARCHAR2(2):= 'A';
bf2						NUMBER;
extra_merit				BOOLEAN:= true;
current_salary			NUMBER:= 37800;
bf3						NUMBER;
bonus					NUMBER;

BEGIN
years	:=	ROUND( (current_date - employee_start_date)/365.25, 0);
IF	years < 6 THEN
	bf1:=100;
ELSIF	years < 11 THEN
	bf1:=200;
ELSIF	years < 16 THEN
	bf1:=300;
ELSE
		bf1:=500;
END IF;

IF job_type = 'A' THEN
	bf2:=100;
ELSE
	bf2:=200;
END IF;

IF extra_merit = True THEN
	bf3:=current_salary * .05;
ELSE
	bf3:=0;
END IF;

bonus:=bf1 + bf2 + bf3;

DBMS_OUTPUT.PUT_LINE(bonus);

END;