Week 10: PL/SQL Advanced Solution
This time we are again going to use HR database for answering following questions
Procedure
a) Modify the following to a procedure. Execute and invoke the procedure. Then Drop the procedure.
DECLARE
v_dept_name departments.department_name%TYPE:= ‘Education’;
v_dept_id NUMBER;
v_max_deptno NUMBER;
BEGIN
SELECT MAX(department_id) INTO v_max_deptno FROM departments;
DBMS_OUTPUT.PUT_LINE(‘The maximum department_id is : ‘ ||
v_max_deptno);
v_dept_id := 10 + v_max_deptno;
INSERT INTO departments (department_id, department_name, location_id)
VALUES (v_dept_id,v_dept_name, NULL);
DBMS_OUTPUT.PUT_LINE (‘ SQL%ROWCOUNT gives ‘ || SQL%ROWCOUNT);
END;
/
b) Modify the procedure created above so that it takes a parameter (v_name). Print this parameter with ‘Hello’. Create an anonymous block to invoke greet procedure.
CREATE PROCEDURE greet IS
v_today DATE:=SYSDATE;
v_tomorrow v_today%TYPE;
BEGIN
v_tomorrow:=v_today +1;
DBMS_OUTPUT.PUT_LINE(‘ Hello World ‘);
DBMS_OUTPUT.PUT_LINE(‘TODAY IS : ‘|| v_today);
DBMS_OUTPUT.PUT_LINE(‘TOMORROW IS : ‘ || v_tomorrow);
END;
c) Create, compile and invoke a procedure called ADD_JOB to insert a new job into the JOBS table. Provide ID and job title using two parameters. Check the procedure using IT_DBA as Job ID and DATABASE ADMIN as job title.
CREATE OR REPLACE PROCEDURE add_job (
p_jobid jobs.job_id%TYPE,
p_jobtitle jobs.job_title%TYPE) IS
BEGIN
INSERT INTO jobs (job_id, job_title)
VALUES (p_jobid, p_jobtitle);
COMMIT;
END add_job;
EXECUTE add_job (‘IT_DBA’, ‘Database Administrator’)
SELECT * FROM jobs WHERE job_id = ‘IT_DBA’;
d) Create a procedure called UPD_JOB to insert a new job into the JOBS table. Provide ID and job title using two parameters. Handle exception when job id not found.
CREATE OR REPLACE 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;
/
e) Create a procedure that returns a value from the SALARY and JOB_ID columns for a specified employee ID. Execute the procedure using host variables with two OUT parameters-one for salary and other for job ID.
CREATE OR REPLACE PROCEDURE get_employee
(p_empid IN employees.employee_id%TYPE,
p_sal OUT employees.salary%TYPE,
p_job OUT employees.job_id%TYPE) IS
BEGIN
SELECT salary, job_id
INTO p_sal, p_job
FROM employees
WHERE employee_id = p_empid;
END get_employee;
Function
a) Create a function GET_JOB that take jobid as parameter and 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;
b) Create a function that return annual salary of an employee and take salary and commission as input using following formula:
[Salary*12+commission=annual salary]
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;
/
c) Use the above function in a select statement against 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
/
d) Create a function that validate a particular department id and return true if department exist otherwise false. Create a procedure that add an employee to Employees table. Row should be only added if function for validation return true, otherwise alert the user with an appropriate message. Call the procedure with the name “Jane Harris” in department 18 and email jaharris.
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;
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;
/
EXECUTE add_employee(‘Jane’, ‘Harris’, ‘JAHARRIS’, p_deptid=> 15)