CODEWITHSUNDEEP
sqloracle-databaseplsql
Charles Flynn
Charles Flynn

Reputation: 59

Returning a PLSQL Cursor

I have a table which contains schedule information. Depending on the situation I may wish to select all schedule information for a driving serial or I may want to select based on start and end dates. This is quite a generic thing to want to do.

I then have a function which requires said schedule information but something inside the method will decide whether a start date is required or not.

So I want something like this:

    getCursor(serial, startdate, enddate)

Inside my function:

    if something or other:
      cursor = getCursor(serial, null, null)
    else
      cursor = getCursor(serial, start, null)

    Loop around cursor

Any idea about how to get this behaviour? I have looked around and seen various different methods but haven't been able to get any of them to work. Is what I want to do event the right approach in PL/SQL? It's not a language I often use...

Upvotes: 0

Views: 79

Answers (2)

Grzegorz Kazior
Grzegorz Kazior

Reputation: 401

It looks like you do not need a special function for you issue. Try this:

-- Retruns all rows that start_date is between p_from and p_to
SELECT *
  FROM table_name t
 WHERE t.serial = p_serial
   AND t.start_date BETWEEN NVL(p_from, t.start_date) 
                        AND NVL(p_to  , t.start_date)

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231651

It's not obvious to me that you need an IF statement or any recursion. It sounds like you just want

CREATE OR REPLACE FUNCTION getSchedule( p_serial IN table_name.serial%type,
                                        p_start_date IN DATE DEFAULT NULL,
                                        p_end_date IN DATE DEFAULT NULL )
  RETURN sys_refcursor
IS
  l_schedule_rc sys_refcursor;
BEGIN
  OPEN l_schedule_rc
   FOR SELECT *
         FROM table_name
        WHERE serial = p_serial
          AND (p_start_date IS NULL or start_date >= p_start_date)
          AND (p_end_date IS NULL   or end_date   <= p_end_date);
  RETURN l_schedule_rc;
END;

This allows the start and end dates to be optional parameters and your query ignores the dates if the parameters input are NULL or are omitted. Of course, this assumes that you have a calling program that knows how to call a PL/SQL function that returns a sys_refcursor...

Upvotes: 3

Related Questions