How do I loop through a cursor in PLSQL?
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
edited Dec 31 '18 at 12:33
APC
118k15118229
asked Dec 31 '18 at 7:01
RZ246RZ246
31
add a comment |
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
edited Dec 31 '18 at 12:33
APC
118k15118229
asked Dec 31 '18 at 7:01
RZ246RZ246
31
add a comment |
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
edited Dec 31 '18 at 12:33
APC
118k15118229
asked Dec 31 '18 at 7:01
RZ246RZ246
31
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
oracle plsql
edited Dec 31 '18 at 12:33
APC
118k15118229
asked Dec 31 '18 at 7:01
RZ246RZ246
31
edited Dec 31 '18 at 12:33
APC
118k15118229
asked Dec 31 '18 at 7:01
RZ246RZ246
31
edited Dec 31 '18 at 12:33
APC
118k15118229
edited Dec 31 '18 at 12:33
APC
118k15118229
edited Dec 31 '18 at 12:33
APC
118k15118229
118k15118229
asked Dec 31 '18 at 7:01
RZ246RZ246
31
asked Dec 31 '18 at 7:01
RZ246RZ246
31
asked Dec 31 '18 at 7:01
RZ246RZ246
31
31
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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.
answered Dec 31 '18 at 9:33
Kaushik NayakKaushik Nayak
18.9k41330
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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.
answered Dec 31 '18 at 9:33
Kaushik NayakKaushik Nayak
18.9k41330
add a comment |
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.
answered Dec 31 '18 at 9:33
Kaushik NayakKaushik Nayak
18.9k41330
add a comment |
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.
answered Dec 31 '18 at 9:33
Kaushik NayakKaushik Nayak
18.9k41330
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.
answered Dec 31 '18 at 9:33
Kaushik NayakKaushik Nayak
18.9k41330
edited Jan 1 at 4:56
answered Dec 31 '18 at 9:33
Kaushik NayakKaushik Nayak
18.9k41330
answered Dec 31 '18 at 9:33
Kaushik NayakKaushik Nayak
18.9k41330
answered Dec 31 '18 at 9:33
Kaushik NayakKaushik Nayak
18.9k41330
18.9k41330
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
