I have worked on this for a while but the code did not work and I could not figure out the correct solution. Did I miss something from the code? Thank you.
— Question – The company wants to calculate the employees’ annual salary: –The first year of employment, the amount of salary is the base salary which is $10,000. –Every year after that, the salary increases by 5%. –Write a stored procedure named calculate_salary which gets an employee ID and –for that employee calculates the salary based on the number of years the employee has –been working in the company. (Use a loop construct to calculate the salary). –The procedure calculates and prints the salary. –Sample output: –First Name: first_name –Last Name: last_name –Salary: $9999,99 –If the employee does not exists, the procedure displays a proper message.
CREATE OR REPLACE PROCEDURE calculate_salary(EMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID%TYPE) AS increase FLOAT := 1.05; base_salary NUMBER := 10000; TENURE NUMBER; SALARY NUMBER; EMP_ID EMPLOYEES.EMPLOYEE_ID%TYPE; FIRST_NAME EMPLOYEES.FIRST_NAME%TYPE; LAST_NAME EMPLOYEES.FIRST_NAME%TYPE; BEGIN SELECT EMPLOYEE_ID, ROUND((SYSDATE - HIRE_DATE)/365,0), FIRST_NAME, LAST_NAME INTO EMP_ID,TENURE, FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID = EMP_ID; FOR i IN 0..TENURE LOOP SALARY := base_salary * i; END LOOP; DBMS_OUTPUT.PUT_LINE ('First Name: '||FIRST_NAME); DBMS_OUTPUT.PUT_LINE ('Last Name: '||LAST_NAME); DBMS_OUTPUT.PUT_LINE ('Salary: '||TO_CHAR(SALARY,'$99,999.99')); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('No Data Found!'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('Error!'); END; / BEGIN calculate_salary(1); END; /
Advertisement
Answer
The calculation in the FOR
loop is wrong. In the first loop iteration you are setting SALARY
to zero. In the second iteration, you are setting SALARY
equal to base_salary
. In the third iteration you are setting SALARY
to double base_salary
, etc. Also, in PL/SQL, FOR
loop limits are inclusive. Hence your loop should start at 1 (one) and not 0 (zero).
The below code calculates the salary assuming that the increase is based on the current salary and not the base salary. Changes to your code are indicated by comments at the end of the changed line.
CREATE OR REPLACE PROCEDURE calculate_salary(EMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID%TYPE) AS increase FLOAT := 1.05; base_salary NUMBER := 10000; TENURE NUMBER; SALARY NUMBER; EMP_ID EMPLOYEES.EMPLOYEE_ID%TYPE; FIRST_NAME EMPLOYEES.FIRST_NAME%TYPE; LAST_NAME EMPLOYEES.FIRST_NAME%TYPE; BEGIN SELECT EMPLOYEE_ID, ROUND((SYSDATE - HIRE_DATE)/365,0), FIRST_NAME, LAST_NAME INTO EMP_ID,TENURE, FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID = EMP_ID; SALARY := base_salary; -- Added this line. FOR i IN 1..TENURE LOOP -- Changed this line. SALARY := SALARY * increase; -- Changed this line. END LOOP; DBMS_OUTPUT.PUT_LINE ('First Name: '||FIRST_NAME); DBMS_OUTPUT.PUT_LINE ('Last Name: '||LAST_NAME); DBMS_OUTPUT.PUT_LINE ('Salary: '||TO_CHAR(SALARY,'$99,999.99')); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('No Data Found!'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('Error!'); END;