EXCEPTION

BEGIN

DECLARE

V_Empno       Emp.Empno%TYPE := &gEmpno;

V_Ename       Emp.Ename%TYPE := UPPER('&gEName');

V_Job              Emp.Job%TYPE := UPPER('&gJob');

V_Deptno      Emp.Deptno%TYPE := &gDeptno;

V_Hiredate   Emp.HireDate%TYPE := UPPER('&gHireDate');

V_Mgr             Emp.Mgr%TYPE := &gMgrNo;

V_Sal               Emp.Sal%TYPE := &gSal;

V_Comm          Emp.Comm%TYPE := &gCommission;

E_HireDate   EXCEPTION;

E_Sal               EXCEPTION;

E_Comm         EXCEPTION;

E_Unk               EXCEPTION;

PRAGMA EXCEPTION_INIT(E_Unk,-1);

V_Emp                        Emp%ROWTYPE;

E_ErrorNo          NUMBER;

E_ErrorMsg        VARCHAR2(255);   

BEGIN

SELECT Deptno  INTO V_Emp.Deptno

FROM Dept

WHERE Deptno=V_Deptno;

IF SQL%FOUND THEN

            --DBMS_OUTPUT.PUT_LINE(' Department Checked Successful');  

            BEGIN

            SELECT DISTINCT Job  INTO V_Emp.Job

            FROM Emp

            WHERE Job= V_Job;

            IF SQL%FOUND THEN

                        --DBMS_OUTPUT.PUT_LINE('Designation Checked Successful');               

                        BEGIN

                        SELECT Empno  INTO V_Emp.Empno

                        FROM Emp

                        WHERE Empno=V_Mgr;

                        IF SQL%FOUND THEN

                                    --DBMS_OUTPUT.PUT_LINE('Manager ID Checked Successful');

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

                                                --DBMS_OUTPUT.PUT_LINE('HireDate Checked Successful');

                                                IF V_Sal BETWEEN 800 AND 5000 THEN

                                                            --DBMS_OUTPUT.PUT_LINE('Salary Checked Successful');

                                                            IF (V_Comm IS NOT NULL AND V_Job = 'SALESMAN') OR  (V_Comm IS  NULL AND V_Job <> 'SALESMAN' ) THEN

                                                                        --DBMS_OUTPUT.PUT_LINE('Commission  Checked Successful');

                                                                        INSERT INTO Emp

                                                                        VALUES (V_Empno,V_Ename,V_Job,V_Mgr,V_Hiredate,V_Sal,V_Comm,V_Deptno);

                                                                        DBMS_OUTPUT.PUT_LINE('EMpno  EName  Job    MgrID  HireDate  Salary  Comm  Deptno' );

                                                                        DBMS_OUTPUT.PUT_LINE(Lpad('-',60,'-'));

                                                                        DBMS_OUTPUT.PUT_LINE( V_Empno  || ' '  || V_Ename || ' ' || V_Job || '  ' || V_Mgr  || '  ' || V_Hiredate  ||  '  ' || V_Sal|| '   ' || NVL(TO_CHAR(V_Comm),'NULL')  || '   ' ||V_Deptno );

                                                                        DBMS_OUTPUT.PUT_LINE('Inserted into Employee Table Successfully');

                                                            ELSE

                                                                        RAISE E_Comm;

                                                            END IF;

                                                ELSE

                                                            RAISE E_sal;

                                                END IF;

                                    ELSE

                                                RAISE E_HireDate;

                                    END IF;

                        END IF;

                        EXCEPTION

                        WHEN NO_DATA_FOUND THEN

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

                        WHEN E_HireDate THEN

                        DBMS_OUTPUT.PUT_LINE(           V_HireDate || ' is not today''s  Date' );

                        WHEN E_Sal THEN

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

                        WHEN E_Unk THEN

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

                        WHEN E_Comm THEN

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

                        WHEN OTHERS THEN

                        E_ErrorNo := SQLCODE;

                        E_ErrorMsg   := SQLERRM;

                        DBMS_OUTPUT.PUT_LINE('Error Code is  :' || E_ErrorNo);

                        DBMS_OUTPUT.PUT_LINE('Error Message is  is  :' || E_ErrorMsg);

                        END;

            END IF;

            EXCEPTION

            WHEN NO_DATA_FOUND THEN

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

            WHEN OTHERS THEN

            E_ErrorNo := SQLCODE;

            E_ErrorMsg   := SQLERRM;

            DBMS_OUTPUT.PUT_LINE('Error Code is  :' || E_ErrorNo);

            DBMS_OUTPUT.PUT_LINE('Error Message is  is  :' || E_ErrorMsg);

            END;  

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

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

WHEN OTHERS THEN

E_ErrorNo := SQLCODE;

E_ErrorMsg   := SQLERRM;

DBMS_OUTPUT.PUT_LINE('Error Code is  :' || E_ErrorNo);

DBMS_OUTPUT.PUT_LINE('Error Message is  is  :' || E_ErrorMsg);

END;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Error Code is  :' || SQLCODE);

DBMS_OUTPUT.PUT_LINE('Error Message is  is  :' || SQLERRM);

END;

CURSOR (WHILE LOOP)

===============================================================

                                                            CURSOR (WHILE LOOP)

===============================================================

DECLARE

V_EmpRCount     NUMBER(2);

MyIndex   NUMBER(2) :=1 ;

TYPE Emp_Table_Type  IS TABLE OF Emp%ROWTYPE

INDEX BY BINARY_INTEGER;

Employees  Emp_Table_Type;

CURSOR  EmpCursor IS

SELECT * FROM Emp;

BEGIN

OPEN EmpCursor;

FETCH  EmpCursor INTO Employees(MyIndex);

WHILE EmpCursor%FOUND

LOOP

     MyIndex := MyIndex + 1;

     FETCH  EmpCursor INTO Employees(MyIndex);    

END LOOP;

CLOSE EmpCursor;

MyIndex :=1;

DBMS_OUTPUT.PUT_LINE('====================Employee informations are shown below ======================');

DBMS_OUTPUT.PUT_LINE(CHR(9) || CHR(9) ||RPAD('-',45,'-'));

DBMS_OUTPUT.PUT_LINE('EmpNo    EName     Job       Deptno    HireDate   MgrNo    Salary     Comm ');

DBMS_OUTPUT.PUT_LINE(RPAD('=',80,'='));

WHILE Employees.EXISTS(MyIndex)

LOOP

 DBMS_OUTPUT.PUT_LINE( LPAD(Employees(1).Empno,6) || CHR(9) ||

    RPAD(Employees(MyIndex).EName,8)||'  '||

    RPAD(Employees(MyIndex).Job,9) || '  ' ||

    LPAD(Employees(MyIndex).Deptno,3)|| '      ' ||

    RPAD(Employees(MyIndex).HireDate,10) || ' ' ||

   LPAD(NVL(TO_CHAR(Employees(MyIndex).Mgr),'NC'),6) || '    ' ||

    LPAD(Employees(MyIndex).Sal,6)|| '   ' ||    LPAD(NVL(TO_CHAR(Employees(MyIndex).Comm),'NA'),6));

    MyIndex := MyIndex + 1;

END LOOP;

DBMS_OUTPUT.PUT_LINE(CHR(10) || Employees.COUNT || '  recode is displayd on Screen....');

END;

=================================================================                                          Employee salary summery using procedure

=================================================================

DECLARE

V_EmpRCount     NUMBER(2);

V_TotalSal            NUMBER := 0 ;

V_Dept10Sal        NUMBER := 0 ;

V_Dept20Sal        NUMBER := 0 ;

V_Dept30Sal        NUMBER := 0 ;

MyIndex   NUMBER(2) :=1 ;

TYPE Emp_Table_Type  IS TABLE OF Emp%ROWTYPE

INDEX BY BINARY_INTEGER;

Employees  Emp_Table_Type;

CURSOR  EmpCursor IS

SELECT * FROM Emp;

BEGIN

OPEN EmpCursor;

FETCH  EmpCursor INTO Employees(MyIndex);

WHILE EmpCursor%FOUND

LOOP

         MyIndex := MyIndex + 1;

     FETCH  EmpCursor INTO Employees(MyIndex); 

END LOOP;

CLOSE EmpCursor;

MyIndex :=1;

DBMS_OUTPUT.PUT_LINE(RPAD('==================Employee informations are shown below ',80,'='));

DBMS_OUTPUT.PUT_LINE(CHR(9) || CHR(9) ||RPAD('-',45,'-'));

DBMS_OUTPUT.PUT_LINE(' EName      Job        Deptno   Salary  ');

DBMS_OUTPUT.PUT_LINE(RPAD('=',40,'='));

WHILE Employees.EXISTS(MyIndex)

LOOP

 DBMS_OUTPUT.PUT_LINE( RPAD(Employees(MyIndex).EName,8)||'  '||

                                    RPAD(Employees(MyIndex).Job,9) || '  ' ||

                                    LPAD(Employees(MyIndex).Deptno,3)|| '      ' ||

                                    LPAD(Employees(MyIndex).Sal,6));

 V_TotalSal :=V_TotalSal + Employees(MyIndex).Sal;

 IF Employees(MyIndex).Deptno =10 THEN

            V_Dept10Sal  :=V_Dept10Sal  + Employees(MyIndex).Sal;

ELSIF Employees(MyIndex).Deptno =20 THEN

            V_Dept20Sal  :=V_Dept20Sal  + Employees(MyIndex).Sal;

ELSIF Employees(MyIndex).Deptno =30 THEN

            V_Dept30Sal  :=V_Dept30Sal  + Employees(MyIndex).Sal;

END IF;

 MyIndex := MyIndex + 1;

END LOOP;

DBMS_OUTPUT.PUT_LINE(CHR(10) || Employees.COUNT || '  recode is displayd on Screen....');

DBMS_OUTPUT.PUT_LINE(CHR(10) || 'Employee salary Summary ' || CHR(10) );

DBMS_OUTPUT.PUT_LINE('Total Investment of the company (Monthly)  for Department 10 is  '  || TO_CHAR(V_Dept10Sal,'99G999D99'));

DBMS_OUTPUT.PUT_LINE('Total Investment of the company (Yearly)   for Department 10 is '  || TO_CHAR(V_Dept10Sal*12,'9G99G999D99'));

DBMS_OUTPUT.PUT_LINE('Total Investment of the company (Monthly)  for Department 20 is  '  || TO_CHAR(V_Dept20Sal,'99G999D99'));

DBMS_OUTPUT.PUT_LINE('Total Investment of the company (Yearly)   for Department 20 is '  || TO_CHAR(V_Dept20Sal*12,'9G99G999D99'));

DBMS_OUTPUT.PUT_LINE('Total Investment of the company (Monthly)  for Department 30 is  '  || TO_CHAR(V_Dept30Sal,'99G999D99'));

DBMS_OUTPUT.PUT_LINE('Total Investment of the company (Yearly)   for Department 30 is '  || TO_CHAR(V_Dept30Sal*12,'9G99G999D99'));

DBMS_OUTPUT.PUT_LINE('Total Investment of the company (Monthly) '  || TO_CHAR(V_TotalSal,'99G999D99'));

DBMS_OUTPUT.PUT_LINE('Total Investment of the company (Yearly) '  || TO_CHAR(V_TotalSal*12,'9G99G999D99'));

END;

=================================================================

                                                Department wise employee data

================================================================= DECLARE

V_EmpRCount     NUMBER(2);

V_Deptno                  NUMBER(2) := &GDeptno;

MyCounter               NUMBER(2) :=0;

MyIndex   NUMBER(2) :=1 ;

TYPE Emp_Table_Type  IS TABLE OF Emp%ROWTYPE

INDEX BY BINARY_INTEGER;

Employees  Emp_Table_Type;

CURSOR  EmpCursor IS

SELECT * FROM Emp;

BEGIN

OPEN EmpCursor;

 FETCH  EmpCursor INTO Employees(MyIndex);

WHILE EmpCursor%FOUND

LOOP

     MyIndex := MyIndex + 1;

     FETCH  EmpCursor INTO Employees(MyIndex);   

END LOOP;

CLOSE EmpCursor;

MyIndex :=1;

DBMS_OUTPUT.PUT_LINE('====================Employee informations are shown below ======================');

DBMS_OUTPUT.PUT_LINE(CHR(9) || CHR(9) ||RPAD('-',45,'-'));

DBMS_OUTPUT.PUT_LINE('EmpNo    EName     Job       Deptno    HireDate   MgrNo    Salary     Comm ');

DBMS_OUTPUT.PUT_LINE(RPAD('=',80,'='));

WHILE Employees.EXISTS(MyIndex)

LOOP

IF Employees(MyIndex).Deptno=V_Deptno  THEN

 DBMS_OUTPUT.PUT_LINE( LPAD(Employees(MyIndex).Empno,6) || CHR(9) ||

    RPAD(Employees(MyIndex).EName,8)||'  '||

    RPAD(Employees(MyIndex).Job,9) || '  ' ||

    LPAD(Employees(MyIndex).Deptno,3)|| '      ' ||

    RPAD(Employees(MyIndex).HireDate,10) || ' ' ||

    LPAD(NVL(TO_CHAR(Employees(MyIndex).Mgr),'NC'),6) || '    ' ||

    LPAD(Employees(MyIndex).Sal,6)|| '   ' ||    LPAD(NVL(TO_CHAR(Employees(MyIndex).Comm),'NA'),6));

            MyCounter  :=MyCounter  +1;

END IF;

 MyIndex := MyIndex + 1;

END LOOP;

DBMS_OUTPUT.PUT_LINE(CHR(10) || MyCounter  || '  recode is displayd on Screen....');

END;

=================================================================

                                                Job wise employee data

================================================================= DECLARE

V_EmpRCount     NUMBER(2);

V_Job              Emp.Job%TYPE := &GJob;

MyCounter               NUMBER(2) :=0;

MyIndex   NUMBER(2) :=1 ;

TYPE Emp_Table_Type  IS TABLE OF Emp%ROWTYPE

INDEX BY BINARY_INTEGER;

Employees  Emp_Table_Type;

CURSOR  EmpCursor IS

SELECT * FROM Emp;

BEGIN

OPEN EmpCursor;

 FETCH  EmpCursor INTO Employees(MyIndex);

WHILE EmpCursor%FOUND

LOOP

     MyIndex := MyIndex + 1;

     FETCH  EmpCursor INTO Employees(MyIndex);   

END LOOP;

CLOSE EmpCursor;

MyIndex :=1;

DBMS_OUTPUT.PUT_LINE('====================Employee informations are shown below ======================');

DBMS_OUTPUT.PUT_LINE(CHR(9) || CHR(9) ||RPAD('-',45,'-'));

DBMS_OUTPUT.PUT_LINE('EmpNo    EName     Job       Deptno    HireDate   MgrNo    Salary     Comm ');

DBMS_OUTPUT.PUT_LINE(RPAD('=',80,'='));

WHILE Employees.EXISTS(MyIndex)

LOOP

IF Employees(MyIndex).Deptno=V_Deptno  THEN

 DBMS_OUTPUT.PUT_LINE( LPAD(Employees(MyIndex).Empno,6) || CHR(9) ||

    RPAD(Employees(MyIndex).EName,8)||'  '||

    RPAD(Employees(MyIndex).Job,9) || '  ' ||

    LPAD(Employees(MyIndex).Deptno,3)|| '      ' ||

    RPAD(Employees(MyIndex).HireDate,10) || ' ' ||

    LPAD(NVL(TO_CHAR(Employees(MyIndex).Mgr),'NC'),6) || '    ' ||

    LPAD(Employees(MyIndex).Sal,6)|| '   ' ||    LPAD(NVL(TO_CHAR(Employees(MyIndex).Comm),'NA'),6));

            MyCounter  :=MyCounter  +1;

END IF;

 MyIndex := MyIndex + 1;

END LOOP;

DBMS_OUTPUT.PUT_LINE(CHR(10) || MyCounter  || '  recode is displayd on Screen....');

END;

=================================================================    

                                                Top N Employee Details using procedure

================================================================= DECLARE

V_EmpRCount     NUMBER(2);

V_Top                  NUMBER(2) := &GTopEmp;

PrvTopSal           Emp.Sal%TYPE :=0;

MyIndex   NUMBER(2) :=1 ;

TYPE Emp_Table_Type  IS TABLE OF Emp%ROWTYPE

INDEX BY BINARY_INTEGER;

Employees  Emp_Table_Type;

CURSOR  EmpCursor IS

SELECT * FROM Emp

ORDER BY Sal DESC;

BEGIN

OPEN EmpCursor;

FETCH  EmpCursor INTO Employees(MyIndex);

WHILE  EmpCursor%FOUND

LOOP

    MyIndex := MyIndex + 1;

     FETCH  EmpCursor INTO Employees(MyIndex);   

END LOOP;

CLOSE EmpCursor;

MyIndex :=1;

DBMS_OUTPUT.PUT_LINE(RPAD('================Top ' || V_Top ||'  Employee informations are shown below ',80,'='));

DBMS_OUTPUT.PUT_LINE(CHR(9) || CHR(9) ||RPAD('-',45,'-'));

DBMS_OUTPUT.PUT_LINE('EmpNo    EName     Job       Deptno    HireDate   MgrNo    Salary     Comm ');

DBMS_OUTPUT.PUT_LINE(RPAD('=',80,'='));

WHILE Employees.EXISTS(MyIndex) AND  MyIndex <= V_Top

LOOP

  DBMS_OUTPUT.PUT_LINE( LPAD(Employees(MyIndex).Empno,6) || CHR(9) ||

    RPAD(Employees(MyIndex).EName,8)||'  '||

    RPAD(Employees(MyIndex).Job,9) || '  ' ||

    LPAD(Employees(MyIndex).Deptno,3)|| '      ' ||

    RPAD(Employees(MyIndex).HireDate,10) || ' ' ||

    LPAD(NVL(TO_CHAR(Employees(MyIndex).Mgr),'NC'),6) || '    ' ||

    LPAD(Employees(MyIndex).Sal,6)|| '   ' || LPAD(NVL(TO_CHAR(Employees(MyIndex).Comm),'NA'),6));

    IF  PrvTopSal = Employees(MyIndex).Sal  THEN

            V_Top := V_Top+1 ;

                 PrvTopSal := Employees(MyIndex).Sal;

  ELSE

            PrvTopSal := Employees(MyIndex).Sal;

  END IF;

 MyIndex := MyIndex + 1;

END LOOP;

DBMS_OUTPUT.PUT_LINE(CHR(10) ||  (MyIndex - 1 ) || '  recode is displayd on Screen....');

END;

=================================================================    

                                                Top N Employee Details using OLAP

=================================================================

DECLARE

V_EmpRCount     NUMBER(2);

V_Top                  NUMBER(2) := &GTopEmp;

MyIndex   NUMBER(2) :=1 ;

CURSOR  EmpCursor IS

SELECT Empno,EName,Job,Deptno,Sal

            ,DENSE_RANK()

             OVER(ORDER BY Sal DESC) SalRank

FROM Emp;

TYPE Emp_Table_Type  IS TABLE OF EmpCursor%ROWTYPE

INDEX BY BINARY_INTEGER;

Employees  Emp_Table_Type;

BEGIN

OPEN EmpCursor;

FETCH  EmpCursor INTO Employees(MyIndex);

WHILE  EmpCursor%FOUND

LOOP

     MyIndex := MyIndex + 1;

     FETCH  EmpCursor INTO Employees(MyIndex);    

END LOOP;

CLOSE EmpCursor;

MyIndex :=1;

DBMS_OUTPUT.PUT_LINE(RPAD('================Top ' || V_Top ||'  Employee informations are shown below ',80,'='));

DBMS_OUTPUT.PUT_LINE(CHR(9) || CHR(9) ||RPAD('-',45,'-'));

DBMS_OUTPUT.PUT_LINE('EmpNo    EName     Job       Deptno    Salary  ');

DBMS_OUTPUT.PUT_LINE(RPAD('=',50,'='));

WHILE Employees.EXISTS(MyIndex) AND Employees(MyIndex).SalRank <= V_Top

LOOP

  DBMS_OUTPUT.PUT_LINE( LPAD(Employees(MyIndex).Empno,6) || CHR(9) ||

    RPAD(Employees(MyIndex).EName,8)||'  '||

    RPAD(Employees(MyIndex).Job,9) || '  ' ||

    LPAD(Employees(MyIndex).Deptno,3)|| '      ' ||

    LPAD(Employees(MyIndex).Sal,6));   

 MyIndex := MyIndex + 1;

END LOOP;

DBMS_OUTPUT.PUT_LINE(CHR(10) ||  (MyIndex-1)  || '  recode is displayd on Screen....');

END;

=================================================================

                                    Employe manager info with out contacting database

=================================================================

DECLARE

V_EmpRCount     NUMBER(2);

MyIndex   NUMBER(2) :=1 ;

InnerIndex NUMBER(2) :=1;

CURSOR  EmpCursor IS

SELECT *

FROM Emp;

TYPE Emp_Table_Type  IS TABLE OF EmpCursor%ROWTYPE

INDEX BY BINARY_INTEGER;

Employees  Emp_Table_Type;

V_MgrName      Emp.Ename%TYPE;

BEGIN

OPEN EmpCursor;

FETCH  EmpCursor INTO Employees(MyIndex);

WHILE EmpCursor%FOUND

LOOP

     MyIndex := MyIndex + 1;

     FETCH  EmpCursor INTO Employees(MyIndex);   

END LOOP;

CLOSE EmpCursor;

MyIndex :=1;

DBMS_OUTPUT.PUT_LINE(RPAD('================Employee informations are shown below ',80,'='));

DBMS_OUTPUT.PUT_LINE(CHR(9) || CHR(9) ||RPAD('-',45,'-'));

DBMS_OUTPUT.PUT_LINE('EmpNo    EName     Job       Deptno    Salary  MgrName');

DBMS_OUTPUT.PUT_LINE(RPAD('=',60,'='));

WHILE Employees.EXISTS(MyIndex)

LOOP

            IF  Employees(MyIndex).Mgr IS NOT NULL THEN

            WHILE Employees.EXISTS(InnerIndex) 

            LOOP

                        IF Employees(InnerIndex).Empno = Employees(MyIndex).Mgr  THEN

                         EXIT ;

                       ELSE                           

                        InnerIndex := InnerIndex +1;

                      END IF;

            END LOOP;

            V_MgrName :=Employees(InnerIndex).EName;

            ELSE

                        V_MgrName :='NC';

            END IF;

            InnerIndex :=1;

  DBMS_OUTPUT.PUT_LINE( LPAD(Employees(MyIndex).Empno,6) || CHR(9) ||

    RPAD(Employees(MyIndex).EName,8)||'  '||

    RPAD(Employees(MyIndex).Job,9) || '  ' ||

    LPAD(Employees(MyIndex).Deptno,3)|| '      ' ||

    LPAD(Employees(MyIndex).Sal,6) || '   ' ||V_MgrName ); 

 MyIndex := MyIndex + 1;

END LOOP;

DBMS_OUTPUT.PUT_LINE(CHR(10) ||  (MyIndex-1)  || '  recode is displayd on Screen....');

END;

/

/