Lab 4 Script

SET SERVEROUTPUT ON;

-- CREATE VIEW --
CREATE OR REPLACE VIEW EMPLOYEE_RECORD AS
SELECT
    EMP.EMPLOYEE_ID,
    EMP.FIRST_NAME || ' ' || EMP.LAST_NAME AS EMPLOYEE_NAME,
    EMP.HIRE_DATE,
    EMP.SALARY AS BASE_SALARY,
    NVL(EMP.COMMISSION_PCT, 0) AS COMMISSION,
    DEPT.DEPARTMENT_NAME,
    LOC.STREET_ADDRESS,
    LOC.CITY,
    LOC.STATE_PROVINCE,
    LOC.POSTAL_CODE,
    NVL(MAN.FIRST_NAME, 'Manager') || ' ' || NVL(MAN.LAST_NAME, 'Not Found') AS MANAGER_NAME
FROM EMPLOYEES EMP
LEFT JOIN DEPARTMENTS DEPT
    ON EMP.DEPARTMENT_ID = DEPT.DEPARTMENT_ID
LEFT JOIN EMPLOYEES MAN
    ON EMP.MANAGER_ID = MAN.EMPLOYEE_ID
LEFT JOIN LOCATIONS LOC
    ON LOC.LOCATION_ID = DEPT.LOCATION_ID;

-- CREATE PACKAGE --
CREATE OR REPLACE PACKAGE emp_package AS
    PROCEDURE employeeInfo(emp_id EMPLOYEES.EMPLOYEE_ID%TYPE);
    PROCEDURE giveRaise(emp_id EMPLOYEES.EMPLOYEE_ID%TYPE, raise_pct NUMBER);
    PROCEDURE removeEmployee(emp_id EMPLOYEES.EMPLOYEE_ID%TYPE);
    PROCEDURE addEmployee(emp_id NUMBER, f_name VARCHAR2, l_name VARCHAR2, email VARCHAR2, phone VARCHAR2, hire DATE, job_id VARCHAR2, salary NUMBER, c_pct NUMBER, m_id NUMBER, d_id NUMBER);
END emp_package;
/

-- CREATE PACKAGE BODY --
CREATE OR REPLACE PACKAGE BODY emp_package AS

    -- FIND EMPLOYEE PROCEDURE --
    PROCEDURE employeeInfo(emp_id EMPLOYEES.EMPLOYEE_ID%TYPE) IS

    employeeRecord EMPLOYEE_RECORD%ROWTYPE;

    BEGIN
        SELECT * INTO employeeRecord
        FROM EMPLOYEE_RECORD
        WHERE EMPLOYEE_ID = emp_id;

    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || employeeRecord.EMPLOYEE_NAME);
    DBMS_OUTPUT.PUT_LINE('Hire Date: ' || employeeRecord.HIRE_DATE);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || employeeRecord.BASE_SALARY);
    DBMS_OUTPUT.PUT_LINE('Commission Percentage: ' || employeeRecord.COMMISSION);
    DBMS_OUTPUT.PUT_LINE('Department Name: ' || employeeRecord.DEPARTMENT_NAME);
    DBMS_OUTPUT.PUT_LINE('Street Address: ');
    DBMS_OUTPUT.PUT_LINE(employeeRecord.STREET_ADDRESS);
    DBMS_OUTPUT.PUT_LINE(employeeRecord.CITY || ', ' || employeeRecord.STATE_PROVINCE || ' ' || employeeRecord.POSTAL_CODE);
    DBMS_OUTPUT.PUT_LINE('Manager Name: ' || employeeRecord.MANAGER_NAME);

    END employeeInfo;

    -- RAISE PROCEDURE --
    PROCEDURE giveRaise(emp_id EMPLOYEES.EMPLOYEE_ID%TYPE, raise_pct NUMBER) IS

    calculatedRaise NUMBER;

    BEGIN
        SELECT SALARY INTO calculatedRaise
        FROM EMPLOYEES
        WHERE EMPLOYEE_ID = emp_id;

        DBMS_OUTPUT.PUT_LINE('Current Salary: ' || calculatedRaise);

        calculatedRaise := calculatedRaise + (calculatedRaise * raise_pct);

        DBMS_OUTPUT.PUT_LINE('New Salary: ' || calculatedRaise);

        UPDATE EMPLOYEES
            SET SALARY = calculatedRaise
        WHERE EMPLOYEE_ID = emp_id;

    END giveRaise;

    -- DELETE EMPLOYEE --
    PROCEDURE removeEmployee(emp_id EMPLOYEES.EMPLOYEE_ID%TYPE) IS

    BEGIN
        DELETE FROM EMPLOYEES
        WHERE EMPLOYEE_ID = emp_id;

        DBMS_OUTPUT.PUT_LINE('Employee #' || emp_id || ' was Deleted Successfully!');

    END removeEmployee;

    -- ADD EMPLOYEE --
    PROCEDURE addEmployee(emp_id NUMBER, f_name VARCHAR2, l_name VARCHAR2, email VARCHAR2, phone VARCHAR2, hire DATE, job_id VARCHAR2, salary NUMBER, c_pct NUMBER, m_id NUMBER, d_id NUMBER) IS

    BEGIN
        INSERT INTO EMPLOYEES
        VALUES (emp_id, f_name, l_name, email, phone, hire, job_id, salary, c_pct, m_id, d_id);

        DBMS_OUTPUT.PUT_LINE('Employee #' || f_name || ' ' || l_name || ' was Added Successfully!');

    END addEmployee;
END emp_package;
/

-- EXECUTE PACKAGES --
EXEC emp_package.employeeInfo(&emp_id);
EXEC emp_package.giveRaise(&emp_id, &raise_pct);
EXEC emp_package.removeEmployee(&emp_id);
EXEC emp_package.addEmployee(&emp_id, &f_name, &l_name, &email, &phone, &hire, &job_id, &salary, &c_pct, &m_id, &d_id);