--  Bryan Ritter
--  DBA 220

SET SERVEROUTPUT ON;

-- Problem 1, ADD_JOB --

-- Problem 1, part a --
CREATE OR REPLACE PROCEDURE ADD_JOB(
	v_job_id IN jobs.job_id%TYPE,
	v_job_title IN jobs.job_title%type
) AS
BEGIN
	INSERT INTO jobs(job_id, job_title) 
	VALUES (v_job_id, v_job_title) ;
END ADD_JOB ;
/

-- Problem 1, part b --
DECLARE
  v_job_id jobs.job_id%TYPE := 'IT_DBA' ;
  v_job_title jobs.job_title%type := 'Database Administrator' ;
  v_job_id_count NUMBER ;
BEGIN
	SELECT COUNT(*) INTO v_job_id_count FROM jobs WHERE job_id = v_job_id ;
	IF (v_job_id_count = 0) THEN 
    ADD_JOB(v_job_id, v_job_title) ;
  ELSE
    DBMS_OUTPUT.PUT_LINE(v_job_id || ' is already in use') ;
  END IF ;
END ;
/

SELECT * FROM JOBS ;


-- Problem 2, UP_JOB --

-- Problem 2, part a --
CREATE OR REPLACE PROCEDURE UPD_JOB(
  v_job_id jobs.job_id%TYPE , v_job_title jobs.job_title%type ) AS
	no_jobs_to_update EXCEPTION ;
	v_job_id_count NUMBER ;
  BEGIN
    SELECT COUNT(*) INTO v_job_id_count FROM jobs WHERE job_id = v_job_id ;
    IF (v_job_id_count > 0) THEN 
      UPDATE jobs
      SET job_title = v_job_title
      WHERE job_id = v_job_id ;
    ELSE
      RAISE no_jobs_to_update ;
    END IF ;
  EXCEPTION
    WHEN no_jobs_to_update THEN
      DBMS_OUTPUT.PUT_LINE('No Jobs With the ID of ''' || v_job_id || ''' available to update') ;
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('An Unprepared For Error is Preventing Update') ;
  END UPD_JOB ;
/
-- Problem 2, part b --
DECLARE
  v_job_id jobs.job_id%TYPE := 'IT_DBA' ;
  v_job_title jobs.job_title%type := 'Data Administrator' ;
BEGIN
	UPD_JOB(v_job_id, v_job_title) ;
END;
/

-- Problem 2, part c --

SELECT * FROM JOBS;

BEGIN
  UPD_JOB('IT_WEB', 'Web Master');
END;
/

-- Problem 3 --

-- Problem 3, part a --
CREATE OR REPLACE PROCEDURE DEL_JOB(
  v_job_id jobs.job_id%TYPE ) AS
	no_jobs_to_delete EXCEPTION ;
	v_job_id_count NUMBER ;
  BEGIN
    SELECT COUNT(*) INTO v_job_id_count FROM jobs WHERE job_id = v_job_id ;
    IF (v_job_id_count > 0) THEN 
      DELETE FROM jobs
      WHERE job_id = v_job_id ;
    ELSE
      RAISE no_jobs_to_delete ;
    END IF ;
  EXCEPTION
    WHEN no_jobs_to_delete THEN
      DBMS_OUTPUT.PUT_LINE('No Jobs available With the ID of ''' || v_job_id || ''' to delete') ;
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('An Unprepared For Error is Preventing Delete') ;
  END DEL_JOB ;
/

-- Problem 3, part b --
DECLARE
  v_job_id jobs.job_id%TYPE := 'IT_DBA' ;
BEGIN
		DEL_JOB(v_job_id) ;
END;
/

-- Problem 3, part c --

SELECT * FROM JOBS;
BEGIN
  DEL_JOB('IT_WEB');
END;
/

-- Problem 4 ---

CREATE OR REPLACE PROCEDURE GET_EMPLOYEE(
  v_employee_id IN OUT employees.employee_id%TYPE,
  v_salary OUT employees.employee_id%TYPE,
  v_job_id OUT jobs.job_id%TYPE
  ) AS
	no_jobs_to_retrieve EXCEPTION ;
	--v_job_id_count NUMBER ;
  BEGIN
    SELECT employee_id, salary, job_id INTO v_employee_id, v_salary, v_job_id FROM employees NATURAL JOIN jobs WHERE employee_id = v_employee_id ;
    DBMS_OUTPUT.PUT_LINE('employee id ' || v_employee_id || ' has a salary of ' || v_salary || ' and a job id of ' || v_job_id ) ;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Unprepared For Error is Preventing Retrieval') ;
  END GET_EMPLOYEE ;
/

DECLARE
  v_employee_id employees.employee_id%TYPE:=120 ;
  v_salary employees.employee_id%TYPE ;
  v_job_id jobs.job_id%TYPE ;
BEGIN
  GET_EMPLOYEE(v_employee_id, v_salary, v_job_id) ;
  DBMS_OUTPUT.PUT_LINE('Retrieved values of ' || v_salary || ' for salary and ' || v_job_id || ' for job id for the employee with the job id of ' || v_employee_id) ;
END;
/

