DAMG6210 - Assignment 4

record_upsert_procedure.sql

CREATE OR REPLACE PROCEDURE MANAGE_DEPARTMENT (
    p_dept_name IN VARCHAR2,
    p_dept_location IN VARCHAR2
) IS
    v_dept_id NUMBER;
    v_dept_name VARCHAR2(50);
BEGIN
    IF p_dept_name IS NULL OR LENGTH(p_dept_name) = 0 THEN
        -- Validate Department Name (Question D)
        RAISE_APPLICATION_ERROR(-20999, 'Department Name cannot be empty');
    ELSIF LENGTH(p_dept_name) > 20 THEN
        -- Length of Dept Name cannot be more than 20 chars (Question H)
        RAISE_APPLICATION_ERROR(-20996, 'Department Name cannot be longer than 20 chars');
    ELSIF REGEXP_LIKE(p_dept_name, '^\d+$') THEN
        -- Department Name shouldn't be a number (inside a VARCHAR) (Question E)
        RAISE_APPLICATION_ERROR(-20998, 'Department Name cannot be a number');
    ELSE
        -- Convert Dept Name as CamelCase (Question I)
        v_dept_name := INITCAP(TRIM(p_dept_name));
    END IF;

    -- Accepted Locations check (Question F)
    IF p_dept_location NOT IN ('MA', 'TX', 'IL', 'CA', 'NY', 'NJ', 'NH', 'RH') THEN
        RAISE_APPLICATION_ERROR(-20997, 'Location not present in the accepted list of location codes');
    END IF;

    BEGIN
        SELECT dept_id INTO v_dept_id
        FROM DEPARTMENT
        WHERE dept_name = v_dept_name;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            v_dept_id := NULL;
            DBMS_OUTPUT.PUT_LINE('No records found for: ' || v_dept_name);
    END;

    IF v_dept_id IS NULL THEN
        -- Insert the dept name if it doesn't exist (Question B)
        -- Dept ID should be auto-generated (created from the last / max record) (Question G)
        SELECT COALESCE(MAX(dept_id), 0) + 1 INTO v_dept_id FROM DEPARTMENT;

        INSERT INTO DEPARTMENT(dept_id, dept_name, dept_location)
        VALUES (v_dept_id, v_dept_name, p_dept_location);
        DBMS_OUTPUT.PUT_LINE('Department: ' || v_dept_name || ' inserted into table');
    ELSE
        -- Update the dept location if dept name exists (Question C)
        UPDATE DEPARTMENT
        SET dept_location = p_dept_location
        WHERE dept_id = v_dept_id;
        DBMS_OUTPUT.PUT_LINE('Department: ' || v_dept_name || '''s location updated ');
    END IF;
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;

table_creation_insertion.sql

CREATE TABLE DEPARTMENT
(
    dept_id       number(5)   NOT NULL PRIMARY KEY,
    dept_name     varchar(40) NOT NULL,
    dept_location varchar(40) NOT NULL
);

INSERT INTO DEPARTMENT (dept_id, dept_name, dept_location)
    WITH d AS (
        SELECT 1, 'Human Resources', 'TX' FROM DUAL UNION ALL
        SELECT 2, 'IT', 'NY' FROM DUAL UNION ALL
        SELECT 3, 'Sales', 'NH' FROM DUAL UNION ALL
        SELECT 4, 'R&D', 'NH' FROM DUAL UNION ALL
        SELECT 5, 'Accounting', 'IL' FROM DUAL UNION ALL
        SELECT 6, 'Marketing', 'RH' FROM DUAL
  ) SELECT * FROM d;

-- Subquestion J (Ensure dept_name is unique)
ALTER TABLE DEPARTMENT ADD CONSTRAINT dept_name_unique_constraint UNIQUE (dept_name);

test_record_upsert_procedure.sql

-- Subquestion B
BEGIN
    MANAGE_DEPARTMENT('Diversity', 'IL');
    MANAGE_DEPARTMENT('Product', 'MA');
    MANAGE_DEPARTMENT('C-Level', 'NH');
END;

-- Subquestion C
BEGIN
    MANAGE_DEPARTMENT('Human Resources', 'TX');
    MANAGE_DEPARTMENT('Sales', 'NY');
    MANAGE_DEPARTMENT('Marketing', 'MA');
END;

-- Subquestion D
BEGIN
    MANAGE_DEPARTMENT('', 'TX');
    MANAGE_DEPARTMENT(NULL, 'NY');
END;

-- Subquestion E
BEGIN
    MANAGE_DEPARTMENT('132465', 'TX');
END;

-- Subquestion F
BEGIN
    MANAGE_DEPARTMENT('Sales - MA', 'MA');
    MANAGE_DEPARTMENT('Sales - TX', 'TX');
    MANAGE_DEPARTMENT('Sales - IL', 'IL');
    MANAGE_DEPARTMENT('Sales - CA', 'CA');
    MANAGE_DEPARTMENT('Sales - NY', 'NY');
    MANAGE_DEPARTMENT('Sales - NJ', 'NJ');
    MANAGE_DEPARTMENT('Sales - NH', 'NH');
    MANAGE_DEPARTMENT('Sales - RH', 'RH');
    MANAGE_DEPARTMENT('Sales - CT', 'CT');
END;

-- Subquestion H, I
BEGIN
    MANAGE_DEPARTMENT('A very long department name', 'TX');
END;