PLUE2_CHANGE_DEPT_ID.sql

create or replace FUNCTION PLUE2_CHANGE_DEPT_ID (
  p_first_name employees.first_name%type 
) RETURN BOOLEAN AS
  p_dept employees.department_id%type;
  p_count NUMBER;
BEGIN
  SELECT count(*) INTO p_count FROM employees WHERE first_name = p_first_name;
  IF p_count = 0 THEN
    RETURN FALSE;
  END IF;
  
  SELECT department_id INTO p_dept FROM (
    SELECT department_id, rownum as r FROM employees
    WHERE first_name = p_first_name
    ORDER BY employee_id
  ) WHERE r = 1;
  
  UPDATE employees SET department_id = p_dept;
  
  RETURN TRUE;
END PLUE2_CHANGE_DEPT_ID;

PLUE2_CHANGE_DEPT_ID_TEST.sql

create or replace PROCEDURE PLUE2_CHANGE_DEPT_ID_TEST AS 
  v_test_return BOOLEAN;
  v_test_dept_id_before employees.department_id%type;
  v_test_dept_id_after employees.department_id%type;
  v_test_employee_id employees.employee_id%type := 111;
BEGIN
  UPDATE employees SET department_id = 10, first_name = 'Steven' WHERE employee_id = 100;
  UPDATE employees SET department_id = 20 WHERE employee_id = 111;

  SELECT department_id INTO v_test_dept_id_before FROM employees WHERE employee_id = v_test_employee_id;
  -- Run with invalid name
  v_test_return := PLUE2_CHANGE_DEPT_ID('INVALID NAME');
  
  SELECT department_id INTO v_test_dept_id_after FROM employees WHERE employee_id = v_test_employee_id;
  
  -- Invalid name -> returns false
  IF v_test_return = FALSE THEN
    dbms_output.put_line('SUCCESS: Return value was false for invalid name');
  ELSE
    dbms_output.put_line('ERROR: Return value was true even though an invalid name was passed');
  END IF;

  -- Invalid name -> department ID must stay unchanged
  IF v_test_dept_id_before = v_test_dept_id_after THEN
    dbms_output.put_line('SUCCESS: Invalid name was passed, department ID was not changed');
  ELSE
    dbms_output.put_line('ERROR: Invalid name was passed and department ID was changed anyway');
  END IF;
  
  -- Run with valid name
  v_test_return := PLUE2_CHANGE_DEPT_ID('Steven');
  SELECT department_id INTO v_test_dept_id_after FROM employees WHERE employee_id = v_test_employee_id;
  
  -- Valid name -> returns true
  IF v_test_return = TRUE THEN
    dbms_output.put_line('SUCCESS: Return value was true for valid name');
  ELSE
    dbms_output.put_line('ERROR: Return value was false even though a valid name was passed');
  END IF;

  -- Valid name -> department ID must be changed
  IF v_test_dept_id_before != v_test_dept_id_after THEN
    dbms_output.put_line('SUCCESS: Valid name was passed, department ID was changed');
  ELSE
    dbms_output.put_line('ERROR: Valid name was passed but department ID was not changed');
  END IF;
  
  -- Valid name -> department ID must be changed to the correct value
  IF v_test_dept_id_after = 10 THEN
    dbms_output.put_line('SUCCESS: Department ID was changed to the correct value');
  ELSE
    dbms_output.put_line('ERROR: Department ID was not changed to the correct value');
    dbms_output.put_line('ERROR: Expected: 10, Actual: ' || v_test_dept_id_after);
  END IF;
  
  -- Update employee 101 to be called Steven as well (but with another department ID)
  UPDATE employees SET first_name = 'Steven', department_id = 30 WHERE employee_id = 101;
  v_test_return := PLUE2_CHANGE_DEPT_ID('Steven');
  SELECT department_id INTO v_test_dept_id_after FROM employees WHERE employee_id = v_test_employee_id;
  
  IF v_test_dept_id_after = 10 THEN
    dbms_output.put_line('SUCCESS: Department ID was changed to the correct value (10), even with multiple Stevens');
  ELSE
    dbms_output.put_line('ERROR: Department ID was not changed to the correct value');
    dbms_output.put_line('ERROR: Expected: 10, Actual: ' || v_test_dept_id_after);
  END IF;
  
  ROLLBACK;
END PLUE2_CHANGE_DEPT_ID_TEST;

PLUE2_GET_NAMES.sql

CREATE OR REPLACE PROCEDURE PLUE2_GET_NAMES (
  p_oldest_employees OUT SYS_REFCURSOR
  
) AS
  v_cursor SYS_REFCURSOR;
  v_min employees.hire_date%type;
  v_count NUMBER;
BEGIN
  SELECT count(*) INTO v_count FROM employees;
  IF v_count = 0 THEN RETURN; END IF;

  SELECT min(hire_date) INTO v_min FROM employees;

  OPEN v_cursor FOR
    SELECT first_name, last_name FROM employees
    WHERE hire_date = v_min;

  p_oldest_employees := v_cursor;
END PLUE2_GET_NAMES;

PLUE2_GET_NAMES_TEST.sql

create or replace PROCEDURE PLUE2_GET_NAMES_TEST AS 
  v_cursor SYS_REFCURSOR;
  v_first_name employees.first_name%type;
  v_last_name employees.last_name%type;
  v_min employees.hire_date%type;
  v_counter NUMBER := 0;
  v_check_hire_date employees.hire_date%type;
BEGIN
  SELECT min(hire_date) INTO v_min FROM employees;
  
  -- Ensuring employee 100 has the lowest hire date
  UPDATE employees SET hire_date = v_min WHERE employee_id = 100;
  
  -- Deleting all employees with the smallest hire date, except employee 100 
  DELETE FROM employees WHERE hire_date = v_min AND employee_id != 100;
  
  PLUE2_GET_NAMES(v_cursor);
  
  LOOP
    FETCH v_cursor INTO v_first_name, v_last_name;
    EXIT WHEN v_cursor%NOTFOUND;
    v_counter := v_counter + 1;
    
    SELECT hire_date INTO v_check_hire_date FROM (
      SELECT hire_date, rownum AS r FROM employees
      WHERE first_name = v_first_name AND last_name = v_last_name AND hire_date = v_min
    ) WHERE r = 1;
    
    IF v_check_hire_date = v_min THEN
      dbms_output.put_line('SUCCESS: There is an employee with this name that has the lowest hire date');
    ELSE
      dbms_output.put_line('ERROR: There is no employee with this name that has the lowest hire date');
    END IF;
    
  END LOOP;
  
  IF v_counter = 1 THEN
    dbms_output.put_line('SUCCESS: There was one single output row');
  ELSE
    dbms_output.put_line('ERROR: There was not one single output row');
    dbms_output.put_line('ERROR: Expected: 1, Actual: ' || v_counter);
  END IF;
  
  -- Set employee 101's and 102's hire dates to the lowest hire date (so we have three 'oldest' employees)
  UPDATE employees SET hire_date = v_min WHERE employee_id = 101;
  UPDATE employees SET hire_date = v_min WHERE employee_id = 102;
  
  v_counter := 0;
  PLUE2_GET_NAMES(v_cursor);
  
  LOOP
    FETCH v_cursor INTO v_first_name, v_last_name;
    EXIT WHEN v_cursor%NOTFOUND;
    v_counter := v_counter + 1;
    
    SELECT hire_date INTO v_check_hire_date FROM (
      SELECT hire_date, rownum AS r FROM employees
      WHERE first_name = v_first_name AND last_name = v_last_name AND hire_date = v_min
    ) WHERE r = 1;
    
    IF v_check_hire_date = v_min THEN
      dbms_output.put_line('SUCCESS: There is an employee with the name '
        || v_first_name || ' ' || v_last_name || ' that has the lowest hire date');
    ELSE
      dbms_output.put_line('ERROR: There is no employee with the name '
        || v_first_name || ' ' || v_last_name || ' that has the lowest hire date');
    END IF;
    
  END LOOP;
  
  IF v_counter = 3 THEN
    dbms_output.put_line('SUCCESS: There were 3 output rows');
  ELSE
    dbms_output.put_line('ERROR: There was not 3 output rows');
    dbms_output.put_line('ERROR: Expected: 3, Actual: ' || v_counter);
  END IF;
  
  ROLLBACK;
END PLUE2_GET_NAMES_TEST;