Sometimes we will be needed to implement the IF ELSE CONDITION in an oracle stored procedure in a scenario where we need to test the OUTPUT of a query and if it fails we need to implement another query.
Below is the sample stored procedure which shows the implementation of IF ELSE CONDITION in ORACLE QUERY for checking REFCURSOR IS NULL
This also shows the implementation of Procedure in Oracle .
PROCEDURE:
PROCEDURE GET_EMPLOYEES_SAL_RANGE
(
--INPUT PARAMETER DECLARATION
I_DEPARTMENTID IN NUMBER,
--OUT PUT REFCURSOR
PR_RESULTSET OUT SYS_REFCURSOR
)
IS
--DECLARE A VARIABLE TO CAPTURE A NUMBER COUNT
L_COUNT NUMBER;
BEGIN
-- THIS QUERY WILL RETURN 1 IF RETURNS ANY DATA OR 0 ON FAILURE
SELECT COUNT(1) INTO L_COUNT FROM (SELECT EMPID FROM EMPLOYEE WHERE EMPLOYEESALARY > 500000 WHERE DEPARTMENTID=I_DEPARTMENTID);
IF L_COUNT <> 0 THEN
OPEN PR_RESULTSET FOR
SELECT EMPID FROM EMPLOYEE WHERE EMPLOYEESALARY > 900000 WHERE DEPARTMENTID=I_DEPARTMENTID;
ELSE
OPEN PR_RESULTSET FOR
SELECT EMPID FROM EMPLOYEE WHERE EMPLOYEESALARY < 900000 WHERE DEPARTMENTID=I_DEPARTMENTID;
END IF;
END GET_EMPLOYEES_SAL_RANGE;
(
--INPUT PARAMETER DECLARATION
I_DEPARTMENTID IN NUMBER,
--OUT PUT REFCURSOR
PR_RESULTSET OUT SYS_REFCURSOR
)
IS
--DECLARE A VARIABLE TO CAPTURE A NUMBER COUNT
L_COUNT NUMBER;
BEGIN
-- THIS QUERY WILL RETURN 1 IF RETURNS ANY DATA OR 0 ON FAILURE
SELECT COUNT(1) INTO L_COUNT FROM (SELECT EMPID FROM EMPLOYEE WHERE EMPLOYEESALARY > 500000 WHERE DEPARTMENTID=I_DEPARTMENTID);
IF L_COUNT <> 0 THEN
OPEN PR_RESULTSET FOR
SELECT EMPID FROM EMPLOYEE WHERE EMPLOYEESALARY > 900000 WHERE DEPARTMENTID=I_DEPARTMENTID;
ELSE
OPEN PR_RESULTSET FOR
SELECT EMPID FROM EMPLOYEE WHERE EMPLOYEESALARY < 900000 WHERE DEPARTMENTID=I_DEPARTMENTID;
END IF;
END GET_EMPLOYEES_SAL_RANGE;
No comments:
Post a Comment