Saturday, 31 March 2012
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;
/
FUNCTION
FUNCTION
CREATE OR REPLACE FUNCTION DeptIsPrsent(PDeptno Dept.Deptno%TYPE)
RETURN BOOLEAN
IS
V_Deptno Dept.Deptno%TYPE;
BEGIN
SELECT DISTINCT Job INTO V_Deptno
FROM Emp
WHERE Job= PDeptno;
IF SQL%FOUND THEN
RETURN TRUE;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
END;
CREATE OR REPLACE FUNCTION IsJobAccept(PJob Emp.Job%TYPE)
RETURN BOOLEAN
IS
V_Job Emp.Job%TYPE;
BEGIN
SELECT DISTINCT Job INTO V_Job
FROM Emp
WHERE Job= PJob;
IF SQL%FOUND THEN
RETURN TRUE;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
END;
CREATE OR REPLACE FUNCTION IsMgrAccept(PMgr Emp.Mgr%TYPE)
RETURN BOOLEAN
IS
V_Mgr Emp.Mgr%TYPE;
BEGIN
SELECT Empno INTO V_Mgr
FROM Emp
WHERE Empno=PMgr;
IF SQL%FOUND THEN
RETURN TRUE;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
END;
CREATE OR REPLACE FUNCTION IsEmpIDAccept(PEmpno Emp.Empno%TYPE )
RETURN BOOLEAN
IS
V_Empno Emp.Empno%TYPE;
BEGIN
SELECT Empno INTO V_Empno
FROM Emp
WHERE Empno= PEmpno;
IF SQL%FOUND THEN
RETURN TRUE;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
END;
/************************************************
******* Function using Ref Cursor ***********
************************************************/
CREATE OR REPLACE PACKAGE
EmpResultSet
AS
TYPE EmpRefCursor IS REF CURSOR;
END EmpResultSet;
/
CREATE OR REPLACE FUNCTION EmplResultSet
RETURN EmpResultSet.EmpRefCursor
IS
V_EmpResultSet EmpResultSet.EmpRefCursor;
Emp_SQLSTMNT VARCHAR2(200);
BEGIN
Emp_SQLSTMNT := 'SELECT * FROM Emp';
OPEN V_EmpResultSet FOR Emp_SQLSTMNT;
RETURN V_EmpResultSet;
END EmplResultSet;
CREATE OR REPLACE FUNCTION
EmplResultSet(QSqlStmnt VARCHAR2)
RETURN EmpResultSet.EmpRefCursor
IS
V_EmpResultSet EmpResultSet.EmpRefCursor;
Emp_SQLSTMNT VARCHAR2(200);
BEGIN
Emp_SQLSTMNT := QSqlStmnt;
OPEN V_EmpResultSet FOR Emp_SQLSTMNT;
RETURN V_EmpResultSet;
END EmplResultSet;
/
Subscribe to:
Posts (Atom)