Pl/Sql Block Example

Pl/Sql Block Example

(a) PL/SQL Code to Calculate Gross and Net Salary After Incrementing Basic Salary by 11%

To update the employee’s salary and calculate their gross and net salary, we will first need to increment the basic salary by 11% and then calculate the gross and net salary. The gross and net salary can be calculated using the following assumptions:

  • Gross Salary = Basic Salary + DA (Dearness Allowance) + HRA (House Rent Allowance)
  • Net Salary = Gross Salary – Tax
DECLARE
    v_empno employee.empno%TYPE;
    v_basic employee.basic%TYPE;
    v_da employee.da%TYPE;
    v_hra employee.hra%TYPE;
    v_tax employee.tax%TYPE;
    v_gross employee.gross%TYPE;
    v_net employee.net%TYPE;
BEGIN
    -- Get employee number from user input (for example)
    v_empno := &empno;  -- this will prompt for empno value

    -- Retrieve employee details based on empno
    SELECT basic, da, hra, tax
    INTO v_basic, v_da, v_hra, v_tax
    FROM employee
    WHERE empno = v_empno;

    -- Increment the basic salary by 11%
    v_basic := v_basic * 1.11;

    -- Calculate the gross salary
    v_gross := v_basic + v_da + v_hra;

    -- Calculate the net salary
    v_net := v_gross - v_tax;

    -- Update the employee record with the new basic, gross, and net salary
    UPDATE employee
    SET basic = v_basic,
        gross = v_gross,
        net = v_net
    WHERE empno = v_empno;

    -- Commit the changes
    COMMIT;

    -- Output the updated information
    DBMS_OUTPUT.PUT_LINE('Employee ' || v_empno || ' salary updated:');
    DBMS_OUTPUT.PUT_LINE('New Basic: ' || v_basic);
    DBMS_OUTPUT.PUT_LINE('New Gross: ' || v_gross);
    DBMS_OUTPUT.PUT_LINE('New Net: ' || v_net);
END;

(b) PL/SQL Code to Display the First Five Senior Employees

Here, the “senior employees” likely refers to the employees with the longest service. Typically, seniority is based on the dob (date of birth), where older employees are considered senior. The PL/SQL block can use a cursor to fetch the first five senior employees based on their dob.

DECLARE
    CURSOR senior_employees_cursor IS
        SELECT empno, ename, dob
        FROM employee
        ORDER BY dob ASC;  -- Assuming that older employees are more senior

    v_empno employee.empno%TYPE;
    v_ename employee.ename%TYPE;
    v_dob employee.dob%TYPE;
    v_counter INT := 0;
BEGIN
    -- Open the cursor to fetch senior employees
    OPEN senior_employees_cursor;

    -- Fetch and display the first 5 senior employees
    LOOP
        FETCH senior_employees_cursor INTO v_empno, v_ename, v_dob;
        EXIT WHEN senior_employees_cursor%NOTFOUND OR v_counter = 5;

        v_counter := v_counter + 1;

        -- Output employee details
        DBMS_OUTPUT.PUT_LINE('Employee ' || v_counter || ': ' || v_ename || ', Emp No: ' || v_empno || ', DOB: ' || v_dob);
    END LOOP;

    -- Close the cursor
    CLOSE senior_employees_cursor;
END;