Query Execution Result
In this code, I aimed to calculate and display the bonus of an employee based on their salary and commission percentage.
I wanted to calculate the bonus for a specific employee by using two key factors: their salary and their commission percentage.
I needed to break down the bonus calculation into two parts:
Declaring Variables: I started by declaring variables to store the data I would be working with: the employee's name (EMP_NAME), their salary (SAL), their commission percentage (COMM), and the calculated bonus (BONUS).
Retrieving Data: I used a SELECT INTO statement to fetch the necessary data (employee name, salary, and commission percentage) for a specific employee (with EMPLOYEE_ID = 157). This data was retrieved from the EMPLOYEES table and stored in the declared variables.
Calculating the Bonus: After fetching the data, I used simple arithmetic to calculate the bonus:
These two values were added together to compute the total bonus.
Displaying the Result: Finally, I used the DBMS_OUTPUT.PUT_LINE procedure to display the result in the output window. I concatenated the employee’s name (EMP_NAME) and the calculated bonus (BONUS) into a readable string.
To ensure the output was displayed correctly, I enabled the DBMS Output panel in SQL Developer and made sure to click the green "+" icon to enable output for the session. After running the PL/SQL block, I was able to view the output in the DBMS Output tab at the bottom of the window.
DECLARE
-- Declare variables
BONUS NUMBER;
SAL NUMBER;
COMM NUMBER;
EMP_NAME VARCHAR2(20);
BEGIN
-- Retrieve employee data from the database
SELECT LAST_NAME, SALARY, COMMISSION_PCT
INTO EMP_NAME, SAL, COMM
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 157;
-- Calculate the bonus
BONUS := (SAL * 0.10) + (COMM * 0.15);
-- Output the result
DBMS_OUTPUT.PUT_LINE('EMPLOYEE ' || EMP_NAME || ' EARNS A NEW BONUS OF ' || BONUS);
END;