Set serveroutput on;

DECLARE

E_DUE_FOR_RAISE EXCEPTION;

V_HIREDATE EMPLOYEES.HIRE_DATE%TYPE;

V_ENAME EMPLOYEES.LAST_NAME%TYPE: = INITCAP('& B_ENAME');

V_SAL EMPLOYEES.SALARY%TYPE;

V_YEARS NUMBER(2);

BEGIN

SELECT LAST_NAME, SALARY, HIRE_DATE

INTO V_ENAME, V_SAL, V_HIREDATE

FROM employees WHERE last_name = V_ENAME;

V_YEARS: = MONTHS_BETWEEN(SYSDATE, V_HIREDATE)/12;

IF V_SAL < 3500 AND V_YEARS > 5 THEN

RAISE E_DUE_FOR_RAISE;

ELSE

DBMS_OUTPUT.PUT_LINE (' not due for a raise');

END IF;

EXCEPTION

WHEN E_DUE_FOR_RAISE THEN

BEGIN

DBMS_OUTPUT.PUT_LINE (V_NAME || ' due for a raise');

INSERT INTO ANALYSIS(ENAME, YEARS, SAL)

VALUES (V_ENAME, V_YEARS, V_SAL);

END;

END;

/

SELECT * FROM analysis;

6)

Create, compile, and invoke the ADD_JOB procedure and review the results.

a) Create a procedure called ADD_JOB to insert a new job into the JOBS table.

Provide the ID and job title using two parameters.

Note: You can create the procedure (and other objects) by entering the code in the

SQL Worksheet area, and then click the Run Script (F5) icon. This creates and

compiles the procedure. To find out whether or not the procedure has any errors,

click the procedure name in the procedure node, and then select Compile from the

pop-up menu.

b) Invoke the procedure with IT_DBA as the job ID and Database

Administrator as the job title. Query the JOBS table and view the results.

c) Invoke your procedure again, passing a job ID of ST_MAN and a job title of

Stock Manager. What happens and why?

a)

CREATE OR REPLACE PROCEDURE add_job(

p_jobid jobs.job_id%type,

p_jobtitle jobs.job_title%type) IS

BEGIN

INSERT INTO job(job_id, job_title)

VALUES(p_jobid, p_jobtitle);

COMMIT;

END add_job;

/

b)

EXECUTE add_job('IT_DBA', 'Database Administrator');

SELECT * FROM jobs;

c)

An exception occurs because there is a Unique key integrity constraint on the

JOB_ID column.

7)

7a. Create a procedure called UPD_JOB to update the job title. Provide the job ID and

a new title using two parameters. Include the necessary exception handling if no

update occurs.

CREATE OR REPLACE PROCEDURE upd_job(

p_jobid jobs.job_id%type,

p_jobtitle jobs.job_title%type) IS

BEGIN

UPDATE job

SET job_title = p_jobtitle

WHERE job_id = p_jobid;

IF sql%NOTFOUND THEN

RAISE_APPLICATION_ERROR(-20000, 'No job updated');

ELSE

COMMIT;

END IF;

END upd_job;

/

7b) Invoke the procedure to change the job title of the job ID IT_DBA to Data Administrator. Query the JOBS table and view the results.

EXECUTE add_job('IT_DBA', 'Data Administrator');

SELECT * FROM jobs;

7c) Test the exception-handling section of the procedure by trying to update a job that does not exist. You can use the job ID IT_WEB and the job title Web Master.

EXECUTE upd_job('IT_WEB', 'Web Master');

SELECT * FROM jobs where job_id = 'IT_WEB';

8) Create a procedure called GET_EMPLOYEE to query the EMPLOYEES table, retrieving the salary and job ID for an employee when provided with the employee ID.

a) Create a procedure that returns a value from the SALARY and JOB_ID columns for a specified employee ID. Remove syntax errors, if any, and then recompile the code.

9)

Create and invoke the GET_JOB function to return a job title.

9a) Create and compile a function called GET_JOB to return a job title.

CREATE OR REPLACE FUNCTION get_job (p_jobid IN

jobs.job_id%type)

RETURN jobs.job_title%type IS

v_title jobs.job_title%type;

BEGIN

SELECT job_title

INTO v_title

FROM jobs

WHERE job_id = p_jobid;

RETURN v_title;

END get_job;

/

9b)Create a VARCHAR2 host variable called b_title, allowing a length of 35

characters. Invoke the function with job ID SA_REP to return the value in the

host variable, and then print the host variable to view the result.

VARIABLE b_title VARCHAR2(35)

EXECUTE: b_title: = get_job ('SA_REP');

PRINT b_title

10) Create a function called GET_ANNUAL_COMP to return the annual salary computed

from an employee’s monthly salary and commission passed as parameters.

10 a) Create the GET_ANNUAL_COMP function, which accepts parameter values for the

monthly salary and commission. Either or both values passed can be NULL, but

the function should still return a non-NULL annual salary. Use the following basic

formula to calculate the annual salary:

(salary*12) + (commission_pct*salary*12)

CREATE OR REPLACE FUNCTION get_annual_comp(

p_sal IN employees.salary%TYPE,

p_comm IN employees.commission_pct%TYPE)

RETURN NUMBER IS

BEGIN

RETURN (NVL(p_sal, 0) * 12 + (NVL(p_comm, 0) * nvl(p_sal, 0)

* 12));

END get_annual_comp;

/

10b) Use the function in a SELECT statement against the EMPLOYEES table for

employees in department 30.

SELECT employee_id, last_name,

get_annual_comp(salary, commission_pct) " Annual

Compensation"

FROM employees

WHERE department_id=30

/

11)

Create a procedure, ADD_EMPLOYEE, to insert a new employee into the

EMPLOYEES table. The procedure should call a VALID_DEPTID function to check

whether the department ID specified for the new employee exists in the

DEPARTMENTS table.

11a) Create a function called VALID_DEPTID to validate a specified department ID

and return a BOOLEAN value of TRUE if the department exists.

CREATE OR REPLACE FUNCTION valid_deptid(

p_deptid IN departments.department_id%TYPE)

RETURN BOOLEAN IS

v_dummy PLS_INTEGER;

BEGIN

SELECT 1

INTO v_dummy

FROM departments

WHERE department_id = p_deptid;

RETURN TRUE;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RETURN FALSE;

END valid_deptid;

11b) Create the ADD_EMPLOYEE procedure to add an employee to the EMPLOYEES

table. The row should be added to the EMPLOYEES table if the VALID_DEPTID

function returns TRUE; otherwise, alert the user with an appropriate message.

Provide the following parameters:

- first_name

- last_name

- email

- job: Use 'SA_REP' as the default.

- mgr: Use 145 as the default.

- sal: Use 1000 as the default.

- comm: Use 0 as the default.

- deptid: Use 30 as the default.

- Use the EMPLOYEES_SEQ sequence to set the employee_id column.

- Set the hire_date column to TRUNC(SYSDATE).

CREATE OR REPLACE PROCEDURE add_employee(

p_first_name employees.first_name%TYPE,

p_last_name employees.last_name%TYPE,

p_email employees.email%TYPE,

p_job employees.job_id%TYPE DEFAULT

'SA_REP',

p_mgr employees.manager_id%TYPE DEFAULT 145,

p_sal employees.salary%TYPE DEFAULT 1000,

p_comm employees.commission_pct%TYPE DEFAULT 0,

p_deptid employees.department_id%TYPE DEFAULT 30)

IS

BEGIN

IF valid_deptid(p_deptid) THEN

INSERT INTO employees(employee_id, first_name,

last_name, email, job_id, manager_id, hire_date, salary, commission_pct,

department_id)

VALUES (employees_seq.NEXTVAL, p_first_name,

p_last_name, p_email,

p_job, p_mgr, TRUNC(SYSDATE), p_sal, p_comm,

p_deptid);

ELSE

RAISE_APPLICATION_ERROR (-20204, 'Invalid department ID.

Try again.');

END IF;

END add_employee;

11c) Call ADD_EMPLOYEE for the name 'Jane Harris' in department 15,

leaving other parameters with their default values. What is the result?

EXECUTE add_employee('Jane', 'Harris', 'JAHARRIS', p_deptid=> 15);

11d) Add another employee named Joe Harris in department 80, leaving the remaining

parameters with their default values. What is the result?

EXECUTE add_employee('Joe', 'Harris', 'JAHARRIS',

p_deptid=> 80)

12)

1) Create a package specification and body called JOB_PKG, containing a copy of your

ADD_JOB, UPD_JOB, and DEL_JOB procedures as well as your GET_JOB function.

Note: Use the code from your previously saved procedures and functions when

creating the package. You can copy the code in a procedure or function, and then

paste the code into the appropriate section of the package.

a) Create the package specification including the procedures and function headings

as public constructs.

CREATE OR REPLACE PACKAGE job_pkg IS

PROCEDURE add_job (p_jobid jobs.job_id%TYPE, p_jobtitle

jobs.job_title%TYPE);

PROCEDURE del_job (p_jobid jobs.job_id%TYPE);

FUNCTION get_job (p_jobid IN jobs.job_id%type) RETURN

jobs.job_title%type;

PROCEDURE upd_job(p_jobid IN jobs.job_id%TYPE, p_jobtitle

IN jobs.job_title%TYPE);

END job_pkg;

/

SHOW ERRORS

b) Create the package body with the implementations for each of the subprograms.

CREATE OR REPLACE PACKAGE BODY job_pkg IS

PROCEDURE add_job (

p_jobid jobs.job_id%TYPE,

p_jobtitle jobs.job_title%TYPE) IS

BEGININSERT INTO jobs (job_id, job_title)

VALUES (p_jobid, p_jobtitle);

COMMIT;

END add_job;

PROCEDURE del_job (p_jobid jobs.job_id%TYPE) IS

BEGIN

DELETE FROM jobs

WHERE job_id = p_jobid;

IF SQL%NOTFOUND THEN

RAISE_APPLICATION_ERROR(-20203, 'No jobs

deleted.');

END IF;

END DEL_JOB;

FUNCTION get_job (p_jobid IN jobs.job_id%type)

RETURN jobs.job_title%type IS

v_title jobs.job_title%type;

BEGIN

SELECT job_title

INTO v_title

FROM jobs

WHERE job_id = p_jobid;

RETURN v_title;

END get_job;

PROCEDURE upd_job(

p_jobid IN jobs.job_id%TYPE,

p_jobtitle IN jobs.job_title%TYPE) IS

BEGIN

UPDATE jobs

SET job_title = p_jobtitle

WHERE job_id = p_jobid;

IF SQL%NOTFOUND THEN

RAISE_APPLICATION_ERROR(-20202, 'No job updated.');

END IF;

END upd_job;

END job_pkg;

/

c) Invoke your ADD_JOB package procedure by passing the values IT_SYSAN and

SYSTEMS ANALYST as parameters.

EXECUTE job_pkg.add_job('IT_SYSAN', 'Systems Analyst');

13)