How do I loop through a cursor in PLSQL?

Question

I'm trying to get the previous working day in PL SQL. I'm not 100% sure if the way I'm trying to do it is correct.

Essentially we have a holiday table and I'm trying to loop an input date through the holiday table and then return the previous working day. I'm using a cursor to get holiday list and then using a while loop. The issue I have is that the code runs, but the cursor is only returning the last value in the holiday list.

Create or Replace Function GetPreviousWorkingDay
  (
      CurrentDate in Date
  )
    Return Date
  As
    PreviousWorkingDay Date;
    Cursor dt_cursor is 
       Select holiday_date 
       from holiday_table;
    HolidayDate date;

  Begin

      PreviousWorkingDay := CurrentDate;

      Open dt_cursor;
      Loop
          Fetch dt_cursor into HolidayDate;
          Exit When dt_cursor%NOTFOUND;
      End Loop;

      While PreviousWorkingDay = HolidayDate
      Loop
          PreviousWorkingDay := PreviousWorkingDay - 1;
          Exit When PreviousWorkingDay <> HolidayDate;
      End Loop; 

      Close dt_cursor;

    Return previousworkingday;

END;

any help would be appreciated. Thanks

Kaushik Nayak · Accepted Answer · 2019-01-01T04:56:01.997

Your logic of traversing the holiday_table using a CURSOR loop would work only if you specify an ORDER BY in the query.

CREATE OR REPLACE FUNCTION getpreviousworkingday (
     currentdate IN DATE
) RETURN DATE AS
     previousworkingday   DATE := currentdate - 1; -- start from previous day
     l_holiday            DATE;
     CURSOR dt_cursor IS SELECT holiday_date
                         FROM holiday_table
                         WHERE holiday_date < currentdate 
     ORDER BY holiday_table DESC;--start with recent holiday before 
                                 --currentdate & keep compare backwards.

BEGIN
     OPEN dt_cursor;

     LOOP

          FETCH dt_cursor INTO l_holiday;

          EXIT WHEN dt_cursor%notfound OR previousworkingday <> l_holiday;
          previousworkingday := previousworkingday - 1;
     END LOOP;
     RETURN previousworkingday;
END;
/

Demo

Note : We can easily get the previous working day using SQL queries without PL/SQL. You can refer answers here . I answered it using loops and cursors because I presume you are probably using those constructs in PL/SQL for learning purpose.

How do I loop through a cursor in PLSQL?

1 Answers1