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;
No comments:
Post a Comment