Creating cursor conditionally?

Question

I want to restrict the cursor result set based on Employee ID array passed to the function, otherwise if array iks null I want all the records.

Here is the stuff i tried

First created the array type

    create or replace type p_emp_arr as table of number   

Function is

    create or replace
    FUNCTION getEmployee_func ( empID IN Number, empId_arr IN p_emp_arr)
    RETURN number IS
       total number(2) := 0;

      BEGIN 

      IF(empId_arr is null)
       THEN
        CURSOR empCursor IS
          SELECT * FROM Employee ;
       ELSE
        CURSOR empCursor IS
          SELECT * FROM Employee where empId in (p_emp_arr);
      END IF;

        ....
        RETURN total;
     END;

But getting below error

   Error(12,12): PLS-00103: Encountered the symbol "empCursor" when expecting one of the following:     := . ( @ % ; 

Answer 1

You can use REFCURSOR;

Syntax would be like,

OPEN EMP_CURSOR FOR
  'SELECT * FROM Employee
       where empId in SELECT COLUMN_VALUE FROM TABLE(:empId_arr)'
   USING empId_arr ;

Full block including the FETCH:

create or replace
FUNCTION getEmployee_func ( empID IN Number, empId_arr IN p_emp_arr)
RETURN number IS
  total number(2) := 0;
  MYREC Employee%ROWTYPE;

  EMP_CURSOR SYS_REFCURSOR;
  BEGIN 

  IF(empId_arr is null)
   THEN
    OPEN EMP_CURSOR FOR
      'SELECT * FROM Employee' ;
   ELSE
    OPEN EMP_CURSOR FOR
      'SELECT * FROM Employee
          where empId in SELECT COLUMN_VALUE FROM TABLE(:empId_arr)'
      USING empId_arr ;
  END IF;

  LOOP
     FETCH EMP_CURSOR INTO MYREC;
     EXIT WHEN EMP_CURSOR%NOTFOUND;
     .....
  END;
    ....
  RETURN total;
 END;