PROCEDURE

 

                                                                     PROCEDURE  

 

 CREATE OR REPLACE PROCEDURE  CheckDept (PDeptno  IN Dept.Deptno%TYPE,Status OUT BOOLEAN)

IS

E_Deptno EXCEPTION;

BEGIN

IF DeptIsPresent(PDeptno) THEN

   Status  := TRUE;

            DBMS_OUTPUT.PUT_LINE('Department Checked Successfully’);

ELSE

Status  := FALSE;

RAISE  E_Deptno;

END IF;

EXCEPTION

WHEN E_Deptno THEN

DBMS_OUTPUT.PUT_LINE(' Department no.' ||  PDeptno || ' is not Regitered ..Please Contact with Addmin and Try angain ..Thank you for using this software');

END;

/

CREATE OR REPLACE PROCEDURE 

CheckJob(PJob   IN Emp.Job%TYPE,Status OUT BOOLEAN)

IS

E_Job EXCEPTION;

BEGIN

IF IsJobAccept(PJob) THEN

   Status  := TRUE;

DBMS_OUTPUT.PUT_LINE('Designation  Checked Successfully’);

ELSE

Status  := FALSE;

RAISE  E_Job;

END IF;

EXCEPTION

WHEN E_Job THEN

DBMS_OUTPUT.PUT_LINE(' Designation ' ||  PJob || ' is not Allowed ..Please Contact with Addmin and Try angain ..Thank you for using this software');

END;

/

CREATE OR REPLACE PROCEDURE

CheckMgr(PMgr   IN Emp.Mgr%TYPE,Status OUT BOOLEAN)

IS

E_Mgr EXCEPTION;

BEGIN

IF IsMgrAccept(PMgr) THEN

   Status  := TRUE;

            DBMS_OUTPUT.PUT_LINE('Manager IDs Checked Successfully’);

ELSE

Status  := FALSE;

RAISE E_Mgr;

END IF;

EXCEPTION

WHEN E_Mgr THEN

DBMS_OUTPUT.PUT_LINE( PMgr || ' is an Invalid manager ID... Try gain with a valid managerial ID ID..Thank you for using this software');

END;

/

CREATE OR REPLACE PROCEDURE 

CheckSal (PSal   IN Emp.Sal%TYPE,Status OUT BOOLEAN)

IS

E_Sal EXCEPTION;

BEGIN

IF PSal BETWEEN 800 AND 5000 THEN

   Status  := TRUE;

DBMS_OUTPUT.PUT_LINE('Salary Checked Successfully’);

ELSE

Status  := FALSE;

RAISE E_Sal;

END IF;

EXCEPTION

WHEN E_Sal THEN

DBMS_OUTPUT.PUT_LINE(        'Salary ' || PSal  || ' for an Employee  is not in the company policy ..Try Again' );

END;

/

CREATE OR REPLACE PROCEDURE 

CheckComm(PComm   IN Emp.Comm%TYPE,PJob IN Emp.Job%TYPE,Status OUT BOOLEAN)

IS

E_Comm EXCEPTION;

BEGIN

IF (PComm IS NOT NULL AND PJob = 'SALESMAN') OR  (PComm IS  NULL AND PJob <> 'SALESMAN' ) THEN

   Status  := TRUE;

            DBMS_OUTPUT.PUT_LINE('Commission  Checked Successfully’);

ELSE  

Status  := FALSE;

RAISE E_Comm;

END IF;

EXCEPTION

WHEN E_Comm THEN

DBMS_OUTPUT.PUT_LINE(        'Commision is not allowd for '|| PJob || '''s  Please Try Again ');

END;

CREATE OR REPLACE PROCEDURE 

CheckEmpID(PEmpno IN Emp.Empno%TYPE ,Status OUT BOOLEAN )

IS

E_Empno EXCEPTION;

BEGIN

IF IsEmpIDAccept(PEmpno) THEN

            Status := FALSE;

            RAISE E_Empno;   

ELSE

            Status := TRUE;     

            DBMS_OUTPUT.PUT_LINE('Employee ID Checked Successfully’);

END IF;

EXCEPTION

WHEN E_Empno THEN

            DBMS_OUTPUT.PUT_LINE(        ' Employe Id ' || PEmpno || ' is already registered for another Employee . Please Try again with a new employe ID');

END;

/

CREATE OR REPLACE PROCEDURE 

CheckHireDate(PHireDate   IN Emp.Hiredate%TYPE,Status OUT BOOLEAN)

IS

E_HireDate EXCEPTION;

BEGIN

IF TO_DATE(PHiredate,'DD-MON-YY') = TO_DATE(SYSDATE,'DD-MON-YY')  THEN

            Status  := TRUE;

            DBMS_OUTPUT.PUT_LINE('Hiredate  Checked Successfully’);

ELSE

            Status  := FALSE;

            RAISE E_HireDate;

END IF;

EXCEPTION

WHEN E_HireDate THEN

            DBMS_OUTPUT.PUT_LINE(        PHireDate || ' is not match with server Date' );

END;

/

/*****************************************************************************

  ** ******         Employee information in 3 tire architecture           ********             

 *****************************************************************************

 1.Cursor Defination in Package(BackEnd)                      

  2.Open The Cursor for Every client and give the content area to an out Mode variable of the client  (MiddleLayer )

3.Stored the data into a plsql table returned from the procedure  out mode variable and use the table for client level operation(Front end)

*****************************************************************************/

*****************************************************************************

**********            1.Cursor Defination in Package(BackEnd)                   *********

*****************************************************************************/

CREATE OR REPLACE PACKAGE EmployeeResult

AS

TYPE EmpRefCursor IS REF CURSOR;

PROCEDURE EmpCsrProc(IN_EName IN VARCHAR2,OUT_EmpCursor  OUT EmpRefCursor);

END EmployeeResult;

/******************************************************************************

  2.Open The Cursor for Every client and give the content area to an out Mode variable of the client  (MiddleLayer )

******************************************************************************/

CREATE OR REPLACE PACKAGE BODY EmployeeResult

AS

PROCEDURE EmpCsrProc(IN_EName IN VARCHAR2,OUT_EmpCursor  OUT EmpRefCursor)

IS

BEGIN

OPEN OUT_EmpCursor FOR

SELECT E.Empno,E.Ename,E.Job,D.Deptno,D.Dname,E.Sal,S.Grade

FROM Emp E,Dept D,SalGrade S

WHERE Ename LIKE('%'|| IN_EName || '%' )

              AND E.Deptno=D.Deptno

              AND E.Sal BETWEEN S.LoSal AND S.HiSal;

END EmpCsrProc;

END EmployeeResult;

/

/******************************************************************************

3.Stored the data into a plsql table returned from the procedure  out mode variable and use the table for client level operation(Front end)

******************************************************************************/

DECLARE

TYPE EmpType IS RECORD

(  

Empno                      Emp.Empno%TYPE,

Ename                       Emp.Ename%TYPE,

Job                              Emp.Job%TYPE,

Deptno                      Dept.Deptno%TYPE,

Dname                      Dept.Dname%TYPE,

Sal                              Emp.Sal%TYPE,

Grade                        SalGrade.Grade%TYPE

);

Employee EmpType;

TYPE Emp_Table_Type

IS TABLE OF Employee%TYPE

INDEX BY BINARY_INTEGER;

V_EmpTable Emp_Table_Type;

V_MatchPattern         VARCHAR2(10) := UPPER('&pattern');

V_EmpCursor  EmployeeResult.EmpRefCursor;

E_NotFound EXCEPTION;

BEGIN

EmployeeResult.EmpCsrProc(V_MatchPattern ,V_EmpCursor);

FETCH V_EmpCursor BULK COLLECT INTO V_EmpTable;

IF V_EmpTable.COUNT <> 0 THEN

DBMS_OUTPUT.PUT_LINE('Employee details matched with employee name of the pattern ''' || V_MatchPattern || ''' are shown below');

DBMS_OUTPUT.PUT_LINE(LPAD('*',80,'*'));

DBMS_OUTPUT.PUT_LINE('Empno     Ename       Job        Deptno    DName         Sal     Grade');

DBMS_OUTPUT.PUT_LINE(LPAD('-',80,'-'));

FOR MyIndex IN V_EmpTable.FIRST .. V_EmpTable.LAST

LOOP

            DBMS_OUTPUT.PUT_LINE(LPAD(V_EmpTable(MyIndex).Empno,6) ||'     '||RPAD(V_EmpTable(MyIndex).Ename ,10)|| '  '   || RPAD(V_EmpTable(MyIndex).Job,10)

                                                         || '   ' || LPAD(V_EmpTable(MyIndex).Deptno,4)  ||'    ' || RPAD(V_EmpTable(MyIndex).DName,10)   || '  ' || LPAD(V_EmpTable(MyIndex).Sal,6)

                                                         || '  '  || LPAD(V_EmpTable(MyIndex).Grade,4));

END LOOP;

DBMS_OUTPUT.PUT_LINE(LPAD('-',80,'-'));

DBMS_OUTPUT.PUT_LINE(V_EmpTable.LAST|| ' Employe name  is matched with the matching character');

ELSE

            RAISE E_NotFound;

END IF;

EXCEPTION

WHEN E_NotFound  THEN

DBMS_OUTPUT.PUT_LINE('Sorry .No  match found  for  the pattern ''' || V_MatchPattern || '''  with employee name in Database'); 

END;

/