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