Scribd
Upload
3 views2 pages

PL/SQL Employee Salary Calculation

The document outlines a PL/SQL assignment that includes creating an 'Employee2' table and a PL/SQL block for processing employee data. It calculates total hours worked, adjusts salaries based on hours, and identifies the employee with the highest salary. Additionally, it retrieves and displays information for a specific employee based on their SSN, handling cases where the employee is not found.

Uploaded by

ahmad
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
3 views2 pages

PL/SQL Employee Salary Calculation

The document outlines a PL/SQL assignment that includes creating an 'Employee2' table and a PL/SQL block for processing employee data. It calculates total hours worked, adjusts salaries based on hours, and identifies the employee with the highest salary. Additionally, it retrieves and displays information for a specific employee based on their SSN, handling cases where the employee is not found.

Uploaded by

ahmad
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

PL/SQL Assignment Solution

1. Table Creation
CREATE TABLE Employee2 (
Fname VARCHAR2(50),
Lname VARCHAR2(50),
SSN VARCHAR2(9),
Salary NUMBER(10,2),
Salary2 NUMBER(10,2),
Dsc VARCHAR2(10)
);

2. PL/SQL Block

DECLARE
CURSOR emp_cursor IS SELECT [Link], [Link], [Link], [Link],
(SELECT SUM(Hours) FROM WORKS_ON W WHERE [Link] = [Link]) AS total_hours
FROM EMPLOYEE E;

v_Fname [Link]%TYPE;
v_Lname [Link]%TYPE;
v_SSN [Link]%TYPE;
v_Salary [Link]%TYPE;
v_Salary2 [Link]%TYPE;
v_Desc VARCHAR2(10);
v_total_hours NUMBER;

v_maxSalary [Link]%TYPE := 0;
v_maxFname [Link]%TYPE;
v_maxSSN [Link]%TYPE;

v_searchFname [Link]%TYPE;
v_searchSalary [Link]%TYPE;
BEGIN
FOR emp IN emp_cursor LOOP
v_Fname := [Link];
v_Lname := [Link];
v_SSN := [Link];
v_Salary := [Link];
v_total_hours := emp.total_hours;

IF v_total_hours > 40 THEN


v_Salary2 := v_Salary * 1.10;
v_Desc := 'Good';
ELSE
v_Salary2 := v_Salary;
v_Desc := 'Bad';
END IF;

INSERT INTO Employee2 VALUES (v_Fname, v_Lname, v_SSN, v_Salary, v_Salary2,


v_Desc);

IF v_Salary > v_maxSalary THEN


v_maxSalary := v_Salary;
v_maxFname := v_Fname;
v_maxSSN := v_SSN;
END IF;
END LOOP;

DBMS_OUTPUT.PUT_LINE('Highest Salary Employee: ' || v_maxFname || ', SSN: ' ||


v_maxSSN);

BEGIN
SELECT Fname, Salary INTO v_searchFname, v_searchSalary
FROM EMPLOYEE
WHERE SSN = '111997788';

DBMS_OUTPUT.PUT_LINE('Employee with SSN 111997788: ' || v_searchFname || ',


Salary: ' || v_searchSalary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found');
END;
END;

You might also like