Should I use a reference cursor here?

Question

I'm writing a function which will eventually use one of several possible cursors, for simplicity sake I'll use two here. In the body of the function I want to be able to use a single variable to describe whichever cursor gets used. Do I use a reference cursor for this or something else?

In the example below, I want the FOR LOOP to use either C1 or C2 depending on the value of myChar. So instead of putting an IF (or CASE statement) around the entire FOR LOOP, I want to make what is currently C1 a variable. Is this doable?

CREATE OR REPLACE FUNCTION MY_FUNCTION (myChar IN CHAR) RETURN INTEGER AS

CURSOR C1 IS SELECT FIRST_NAME FROM EMPLOYEES;

CURSO C2 IS SELECT LAST_NAMES FROM EMPLOYEES;

BEGIN

FOR X IN C1 LOOP -- DO STUFF END LOOP; RETURN 1;

END MY_FUNCION;

Answer 1

Yes, I didn't test this, but it might work:

CREATE OR REPLACE FUNCTION MY_FUNCTION (myChar IN CHAR) RETURN INTEGER AS
  type r_cursor is REF CURSOR;
  c1 r_cursor;
begin
  IF myChar = "1" THEN
      open c1 for SELECT FIRST_NAME FROM EMPLOYEES;
  ELSE 
      open c1 for SELECT LAST_NAMES FROM EMPLOYEES;
  END IF;

  loop
      fetch c1 into c1recs;

      -- DO STUFF

      exit when c1%notfound;
  end loop;
  close c1;

  RETURN 1;
end;