PL/SQL Exercise 7

Practice 12
1. Create a package specification and body called JOB_PACK. (You can save the package body and specification in two separate files.)
This package contains your ADD_JOB, UPD_JOB, and DEL_JOB procedures, as well as your Q_JOB function.
Note: Use the code in your previously saved script files when creating the package.
a. Make all the constructs public.
Note: Consider whether you still need the stand-alone procedures and functions you just
packaged.
b. Invoke your ADD_JOB procedure by passing values IT_SYSAN and SYSTEMS ANALYST as parameters.
c. Query the JOBS table to see the result.


CREATE OR REPLACE PACKAGE JOB_PACK AS
PROCEDURE ADD_JOB(Id IN jobs.job_id%type,title IN jobs.job_title%type);
PROCEDURE DEL_JOB(jobId jobs.job_id%type);
PROCEDURE UPD_JOB(jobId jobs.job_id%type,title jobs.job_title%type);
FUNCTION Q_JOB RETURN VARCHAR2;
END JOB_PACK;

create or replace
PACKAGE BODY JOB_PACK AS
--delete job
PROCEDURE DEL_JOB(jobId jobs.job_id%type) IS
counter jobs.max_salary%type :=0;
JOB_ID_NOT_FOUND EXCEPTION;
CURSOR C1 is select JOB_ID from JOBS;
BEGIN
FOR jobsid IN C1
LOOP
IF jobsid.job_id =jobId THEN
counter :=counter+1;
END IF;
END LOOP;
IF counter=1 THEN
DELETE FROM JOBS WHERE JOB_ID=JOBID;
DBMS_OUTPUT.PUT_LINE('DELETED Job Id IS '||JOBID);
ELSE
RAISE JOB_ID_NOT_FOUND;
END IF;
EXCEPTION
WHEN JOB_ID_NOT_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Job Id not found in Table');
END;
-------Update job
PROCEDURE UPD_JOB(jobId jobs.job_id%type,title jobs.job_title%type) IS
counter jobs.max_salary%type :=0;
JOB_ID_NOT_FOUND EXCEPTION;
CURSOR C1 is select JOB_ID from JOBS;
BEGIN
FOR jobsid IN C1
LOOP
IF jobsid.job_id =jobId THEN
counter :=counter+1;
END IF;
END LOOP;
IF counter=1 THEN
update jobs
SET JOB_TITLE=title
WHERE JOB_ID=JOBID;
ELSE
RAISE JOB_ID_NOT_FOUND;
END IF;
EXCEPTION
WHEN JOB_ID_NOT_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Job Id not found in Table');
END;
----------------Query Job
FUNCTION Q_JOBS(G_TITLE jobs.job_id%type) RETURN VARCHAR2 IS
title jobs.job_title%type;
BEGIN
select job_title INTO title from jobs where job_id=G_TITLE;
Return title;
END;
-----addjob
PROCEDURE ADD_JOB(Id IN jobs.job_id%type,title IN jobs.job_title%type) IS
BEGIN
INSERT INTO JOBS values(Id,title,'1222','123');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Some Other Errors');
END;

END JOB_PACK;

SET SERVEROUTPUT ON
BEGIN
job_pack.add_job('IT_SYSAN','SYSTEMS ANALYST');
END;











2. Create and invoke a package that contains private and public constructs.
a. Create a package specification and package body called EMP_PACK that contains your
NEW_EMP procedure as a public construct,
and your VALID_DEPTID function as a private construct. (You can save the specification and body into separate files.)
b. Invoke the NEW_EMP procedure, using 15 as a department number. Because the department ID 15 does not exist in the DEPARTMENTS table,
you should get an error message as specified in the exception handler of your procedure.
c. Invoke the NEW_EMP procedure, using an existing department ID 80.
If you have time:


CREATE OR REPLACE PACKAGE BODY EMP_PACK AS
---insert new employee
PROCEDURE NEW_EMP IS
vale BOOLEAN;
Invalid_Dept_ID EXCEPTION;
BEGIN
vale :=VALID_DEPTID(15);
IF vale=FALSE THEN
RAISE Invalid_Dept_ID;
ELSE
DBMS_OUTPUT.PUT_LINE('true');
INSERT INTO employees values(EMPLOYEES_SEQ.nextVal,'arpit','rahi','arahi',7316117,sysdate,'SA_REP',1000,'',145,30);
DBMS_OUTPUT.PUT_LINE('true');
END IF;
EXCEPTION
WHEN Invalid_Dept_ID THEN
DBMS_OUTPUT.PUT_LINE('Invalid department id');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SOME OTHER ERRORS');
END;
--complete
--validate employee
FUNCTION VALID_DEPTID(dept employees.department_id%type) RETURN BOOLEAN IS
dept_id employees.department_id%type;
BEGIN
select count(*) INTO dept_id from employees where department_id=dept;
IF
dept_id =0 THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END;
END EMP_PACK;




CREATE OR REPLACE PACKAGE EMP_PACK AS
PROCEDURE NEW_EMP;
FUNCTION VALID_DEPTID(dept employees.department_id%type) RETURN BOOLEAN;
END EMP_PACK;


SET SERVEROUTPUT ON
EXEC EMP_PACK.NEW_EMP;








3. a. Create a package called CHK_PACK that contains the procedures CHK_HIREDATE and CHK_DEPT_MGR.
Make both constructs public. (You can save the specification and body into separate files.)
The procedure CHK_HIREDATE checks whether an employee’s hire date is within the following range: [SYSDATE - 50 years, SYSDATE + 3 months].
Note:
If the date is invalid, you should raise an application error with an appropriate message indicating why the date value is not acceptable.
Make sure the time component in the date value is ignored.
Use a constant to refer to the 50 years boundary.
A null value for the hire date should be treated as an invalid hire date.
The procedure CHK_DEPT_MGR checks the department and manager combination for a given employee.
The CHK_DEPT_MGR procedure accepts an employee ID and a manager ID.
The procedure checks that the manager and employee work in the same department.
The procedure also checks that the job title of the manager ID provided is
MANAGER.
Note: If the department ID and manager combination is invalid, you should raise an
application error with an appropriate message.



CREATE OR REPLACE PACKAGE CHK_PACK IS
procedure CHK_HIREDATE(P_DATE varchar2);
procedure CHK_DEPT_MGR(P_EMPID EMPLOYEES.EMPLOYEE_ID%type,P_MGR EMPLOYEES.MANAGER_ID%type);
FUNCTION TEST_DATE(P_DATE varchar2) RETURN VARCHAR2;
end CHK_PACK;
/




CREATE OR REPLACE PACKAGE BODY CHK_PACK AS

--Check date

procedure CHK_HIREDATE(P_DATE VARCHAR2) as
CUSTOM_NULL_ERROR EXCEPTION;
type DATECHAR is table of date;
COUNTVAL INTEGER(5,2);
DATECHARS DATECHAR;
RETVAL VARCHAR2(50);
begin
RETVAL :=TEST_DATE(P_DATE);
if RETVAL ='FALSE' then
RAISE CUSTOM_NULL_ERROR;
END IF;
select HIRE_DATE bulk collect into DATECHARS from EMPLOYEES where P_DATE between (sysdate-18000) and (sysdate+90) group by HIRE_DATE;
select COUNT(*) into COUNTVAL from EMPLOYEES where P_DATE between (sysdate-90) and (sysdate+90);
for I in 1..COUNTVAL
LOOP
DBMS_OUTPUT.PUT_LINE('Work MAadi '||COUNTVAL||' '||DATECHARS(I));
end LOOP;
EXCEPTION
when CUSTOM_NULL_ERROR then
DBMS_OUTPUT.PUT_LINE('Either Null data or incorrect date format has been passed,Please supply input date in dd-mon-yy format');
when OTHERS then
DBMS_OUTPUT.PUT_LINE('Date format is not correct');
END;

--End Check Date

---Function

function TEST_DATE(P_DATE varchar2) return varchar2 is
L_DATE DATE;
begin
if P_DATE is null then
RETURN 'FALSE';
END IF;
L_DATE := TO_DATE(P_DATE,'dd-MON-YY');
RETURN 'TRUE';
EXCEPTION
when OTHERS then
RETURN 'FALSE';
end;


---End Function

---Check Manager

procedure CHK_DEPT_MGR(P_EMPID EMPLOYEES.EMPLOYEE_ID%type,P_MGR EMPLOYEES.MANAGER_ID%type) as
INVALID EXCEPTION;
P_deptid EMPLOYEES.EMPLOYEE_ID%type;
M_DEPTID EMPLOYEES.DEPARTMENT_ID%type;
TITLE JOBS.JOB_TITLE%type;
begin
select DEPARTMENT_ID into P_DEPTID from EMPLOYEES where EMPLOYEE_ID=P_EMPID;
select DEPARTMENT_ID into M_DEPTID from EMPLOYEES where EMPLOYEE_ID=P_MGR;
if M_DEPTID=P_DEPTID then
DBMS_OUTPUT.PUT_LINE('Manager ID and Employee ID belong to same department');
select JOB_ID INTO TITLE from employees where EMPLOYEE_ID=P_MGR and JOB_ID LIKE '%MAN';
else
RAISE INVALID;
end if;
EXCEPTION
when INVALID then
DBMS_OUTPUT.PUT_LINE('Manager ID and Employee ID doesnt belong to same department');
when NO_DATA_FOUND then
DBMS_OUTPUT.PUT_LINE('Manager ID doesnt have a manager role');
END;

END CHK_PACK;








Practice 12 (continued)
b. Test the CHK_HIREDATE procedure with the following command:
EXECUTE chk_pack.chk_hiredate('01-JAN-47')
What happens, and why?

PACKAGE BODY CHK_PACK compiled
anonymous block completed

c. Test the CHK_HIREDATE procedure with the following command:
EXECUTE chk_pack.chk_hiredate(NULL)
What happens, and why?

anonymous block completed
Either Null data or incorrect date format has been passed,Please supply input date in dd-mon-yy format


d. Test the CHK_DEPT_MGR procedure with the following command:
EXECUTE chk_pack.chk_dept_mgr(117,100)
What happens, and why?

anonymous block completed
Manager ID and Employee ID doesnt belong to same department