How do I loop through a cursor in PLSQL?












0















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










share|improve this question





























    0















    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










    share|improve this question



























      0












      0








      0








      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










      share|improve this 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







      oracle plsql






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Dec 31 '18 at 12:33









      APC

      118k15118229




      118k15118229










      asked Dec 31 '18 at 7:01









      RZ246RZ246

      31




      31
























          1 Answer
          1






          active

          oldest

          votes


















          3














          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.






          share|improve this answer

























            Your Answer






            StackExchange.ifUsing("editor", function () {
            StackExchange.using("externalEditor", function () {
            StackExchange.using("snippets", function () {
            StackExchange.snippets.init();
            });
            });
            }, "code-snippets");

            StackExchange.ready(function() {
            var channelOptions = {
            tags: "".split(" "),
            id: "1"
            };
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function() {
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled) {
            StackExchange.using("snippets", function() {
            createEditor();
            });
            }
            else {
            createEditor();
            }
            });

            function createEditor() {
            StackExchange.prepareEditor({
            heartbeatType: 'answer',
            autoActivateHeartbeat: false,
            convertImagesToLinks: true,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: 10,
            bindNavPrevention: true,
            postfix: "",
            imageUploader: {
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            },
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            });


            }
            });














            draft saved

            draft discarded


















            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53984561%2fhow-do-i-loop-through-a-cursor-in-plsql%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown

























            1 Answer
            1






            active

            oldest

            votes








            1 Answer
            1






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            3














            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.






            share|improve this answer






























              3














              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.






              share|improve this answer




























                3












                3








                3







                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.






                share|improve this answer















                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.







                share|improve this answer














                share|improve this answer



                share|improve this answer








                edited Jan 1 at 4:56

























                answered Dec 31 '18 at 9:33









                Kaushik NayakKaushik Nayak

                18.9k41330




                18.9k41330






























                    draft saved

                    draft discarded




















































                    Thanks for contributing an answer to Stack Overflow!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid



                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.


                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53984561%2fhow-do-i-loop-through-a-cursor-in-plsql%23new-answer', 'question_page');
                    }
                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    Popular posts from this blog

                    Monofisismo

                    Angular Downloading a file using contenturl with Basic Authentication

                    Olmecas