IF ELSE CONDITION in ORACLE QUERY for checking REFCURSOR IS NULL

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;

No comments:

Post a Comment