Write a PL/SQL block. I have completed this but my professor wants amount to be

Question

Write a PL/SQL block. I have completed this but my professor wants amount to be variable rather than hard coded value.

I have completed this but my professor wants amount to be variable rather than hard coded value.

DECLARE

v_worked DATE;

v_first_name employees.first_name%TYPE;
v_last_name employees.last_name%TYPE;
v_job_id employees.job_id%TYPE;
BEGIN
SELECT hire_date,first_name,last_name,job_id
INTO v_worked,v_first_name,v_last_name,v_job_id
FROM employees
WHERE employee_id= 120;
IF (sysdate - v_worked)/365 BETWEEN 1 AND 5 AND v_job_id = 'SA_REP' THEN
dbms_output.put_line(' The yearly bonus for employee ' || v_first_name || ' ' || v_last_name || ' is $260');
ELSIF (sysdate - v_worked)/365 BETWEEN 1 AND 5 AND v_job_id != 'SA_REP' THEN
dbms_output.put_line(' The yearly bonus for employee ' || v_first_name || ' ' || v_last_name || ' is $360.');
ELSIF (sysdate - v_worked)/365 BETWEEN 6 AND 10 AND v_job_id = 'SA_REP' THEN
dbms_output.put_line(' The yearly bonus for employee ' || v_first_name || ' ' || v_last_name || ' is $360.');
ELSIF (sysdate - v_worked)/365 BETWEEN 6 AND 10 AND v_job_id != 'SA_REP' THEN
dbms_output.put_line(' The yearly bonus for employee ' || v_first_name || ' ' || v_last_name || ' is $460.');
ELSIF (sysdate - v_worked)/365 BETWEEN 11 AND 15 AND v_job_id = 'SA_REP' THEN
dbms_output.put_line(' The yearly bonus for employee ' || v_first_name || ' ' || v_last_name || ' is $460.');
ELSIF (sysdate - v_worked)/365 BETWEEN 11 AND 15 AND v_job_id != 'SA_REP' THEN
dbms_output.put_line(' The yearly bonus for employee ' || v_first_name || ' ' || v_last_name || ' is $560.');
ELSIF (sysdate - v_worked)/365 >= 16 AND v_job_id = 'SA_REP' THEN
dbms_output.put_line(' The yearly bonus for employee ' || v_first_name || ' ' || v_last_name || ' is $560.');
ELSE --(sysdate - v_worked)/365 >= 16 AND v_job_id != 'SA_REP' THEN
dbms_output.put_line(' The yearly bonus for employee ' || v_first_name || ' ' || v_last_name || ' is $660.');
END IF;
END;
/

Explanation / Answer

DECLARE

v_worked DATE;
v_bonus number(10);
v_first_name employees.first_name%TYPE;
v_last_name employees.last_name%TYPE;
v_job_id employees.job_id%TYPE;
BEGIN
SELECT hire_date,first_name,last_name,job_id
INTO v_worked,v_first_name,v_last_name,v_job_id
FROM employees
WHERE employee_id= 120;
IF (sysdate - v_worked)/365 BETWEEN 1 AND 5 AND v_job_id = 'SA_REP' THEN
v_bonus=260;
ELSIF (sysdate - v_worked)/365 BETWEEN 1 AND 5 AND v_job_id != 'SA_REP' THEN
v_bonus=360;
ELSIF (sysdate - v_worked)/365 BETWEEN 6 AND 10 AND v_job_id = 'SA_REP' THEN
v_bonus=360;
ELSIF (sysdate - v_worked)/365 BETWEEN 6 AND 10 AND v_job_id != 'SA_REP' THEN
v_bonus=460;
ELSIF (sysdate - v_worked)/365 BETWEEN 11 AND 15 AND v_job_id = 'SA_REP' THEN
v_bonus=460;
ELSIF (sysdate - v_worked)/365 BETWEEN 11 AND 15 AND v_job_id != 'SA_REP' THEN
v_bonus=560;
ELSIF (sysdate - v_worked)/365 >= 16 AND v_job_id = 'SA_REP' THEN
v_bonus=560;
ELSIF(sysdate - v_worked)/365 >= 16 AND v_job_id != 'SA_REP' THEN
v_bonus=660;
END IF;
dbms_output.put_line(' The yearly bonus for employee ' || v_first_name || ' ' || v_last_name || ' is $'||v_bonus);
END;
/

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.