SELECT * FROM jobs

b. Disable the trigger SECURE_DMLbefore invoking the procedure. Invoke the procedure to add a new job with job ID SY_ANAL, job title System Analyst, and minimum salary of 6,000.

ALTER TRIGGER secure_employees DISABLE;

EXECUTE add_jobs (’SY_ANAL’, ’System Analyst’, 6000)

c. Verify that a row was added and remember the new job ID for use in the next exercise.

Commit the changes.

SELECT * FROM jobs

WHERE job_id = ’SY_ANAL’;

Part A: Additional Practice 16 Solutions

16. In this practice, create a program to add a new row to the JOB_HISTORYtable, for an existing employee.

Note: Disable all triggers on the EMPLOYEES, JOBS, and JOB_HISTORYtables before invoking the procedure in part b. Enable all these triggers after executing the procedure.

a. Create a stored procedure called ADD_JOB_HISTto enter a new row into the JOB_HISTORY table for an employee who is changing his job to the new job ID that you created in question 15b.

Use the employee ID of the employee who is changing the job and the new job ID for the employee as parameters. Obtain the row corresponding to this employee ID from the EMPLOYEES table and insert it into the JOB_HISTORY table. Make hire date of this employee as start date and today’s date as end date for this row in the JOB_HISTORY table.

Change the hire date of this employee in the EMPLOYEES table to today’s date. Update the job ID of this employee to the job ID passed as parameter (Use the job ID of the job created in question 15b) and salary equal to minimum salary for that job ID + 500.

Include exception handling to handle an attempt to insert a nonexistent employee.

CREATE OR REPLACE PROCEDURE add_job_hist (p_empid IN employees.employee_id%TYPE,

p_jobid IN jobs.job_id%TYPE) IS

BEGIN

INSERT INTO job_history

SELECT employee_id, hire_date, SYSDATE, job_id, department_id FROM employees

WHERE employee_id = p_empid;

UPDATE employees

SET hire_date = SYSDATE, job_id = p_jobid,

salary = (SELECT min_salary+500 FROM jobs

WHERE job_id = p_jobid) WHERE employee_id = p_empid;

DBMS_OUTPUT.PUT_LINE (’Added employee ’ ||p_empid||

’ details to the JOB_HISTORY table’);

DBMS_OUTPUT.PUT_LINE (’Updated current job of employee ’

Part A: Additional Practice 16 Solutions (continued)

b. Disable triggers. (See the note at the beginning of this question.)

Execute the procedure with employee ID 106and job ID SY_ANALas parameters.

Enable the triggers that you disabled.

ALTER TABLE employees DISABLE ALL TRIGGERS;

ALTER TABLE jobs DISABLE ALL TRIGGERS;

ALTER TABLE job_history DISABLE ALL TRIGGERS;

EXECUTE add_job_hist(106, ’SY_ANAL’)

ALTER TABLE employees ENABLE ALL TRIGGERS;

ALTER TABLE jobs ENABLE ALL TRIGGERS;

ALTER TABLE job_history ENABLE ALL TRIGGERS;

c. Query the tables to view your changes, and then commit the changes.

SELECT * FROM job_history WHERE employee_id = 106;

SELECT job_id, salary FROM employees WHERE employee_id = 106;

Part A: Additional Practice 17 Solutions

17. In this practice, create a program to update the minimum and maximum salaries for a job in the JOBStable.

a. Create a stored procedure called UPD_SALto update the minimum and maximum salaries for a specific job ID in the JOBStable.

Pass three parameters to the procedure: the job ID, a new minimum salary, and a new maximum salary for the job. Add exception handling to account for an invalid job ID in the JOBStable.

Also, raise an exception if the maximum salary supplied is less than the minimum salary. Provide an appropriate message that will be displayed if the row in the JOBStable is locked and cannot be changed.

CREATE OR REPLACE PROCEDURE upd_sal (p_jobid IN jobs.job_id%type,

IF (p_maxsal < p_minsal) THEN

DBMS_OUTPUT.PUT_LINE(’ERROR. MAX SAL SHOULD BE > MIN SAL’);

RAISE sal_error;

END IF;

SELECT ’’

INTO v_dummy FROM jobs

WHERE job_id = p_jobid

FOR UPDATE OF min_salary NOWAIT;

UPDATE jobs

SET min_salary = p_minsal, max_salary = p_maxsal WHERE job_id = p_jobid;

EXCEPTION

WHEN e_resource_busy THEN

RAISE_APPLICATION_ERROR (-20001, ’Job information is

Part A: Additional Practice 17 and 18 Solutions

b. Execute the procedure. You can use the following data to test your procedure:

EXECUTE upd_sal (’SY_ANAL’, 7000, 140) EXECUTE upd_sal (’SY_ANAL’, 7000, 14000)

c. Query the JOBStable to view your changes, and then commit the changes.

SELECT * FROM jobs

WHERE job_id = ’SY_ANAL’;

18. In this practice, create a procedure to monitor whether employees have exceeded their average salary limits.

a. Add a column to the EMPLOYEEStable by executing the following command:

(labaddA_4.sql)

ALTER TABLE employees

ADD (sal_limit_indicate VARCHAR2(3) DEFAULT ’NO’

CONSTRAINT emp_sallimit_ck CHECK

(sal_limit_indicate IN (’YES’, ’NO’)));

b. Write a stored procedure called CHECK_AVG_SALwhich checks each employee’s average salary limit from the JOBStable against the salary that this employee has in the EMPLOYEES table and updates the SAL_LIMIT_INDICATEcolumn in the EMPLOYEEStable when this employee has exceeded his average salary limit.

Create a cursor to hold employee Ids, salaries, and their average salary limit. Find the average salary limit possible for an employee’s job from the JOBStable. Compare the average salary limit possible per employee to their salary and if the salary is more than the average salary limit, set the employee’s SAL_LIMIT_INDICATEcolumn to YES; otherwise, set it to NO. Add exception handling to account for a record being locked.

Part A: Additional Practice 18 Solutions (continued)

CREATE OR REPLACE PROCEDURE check_avg_sal IS

v_avg_sal NUMBER;

CURSOR emp_sal_cur IS

SELECT employee_id, job_id, salary FROM employees