Oracle stored procedure returns (N) working days based on the specified date
I have never written any Oracle stored procedures. Today I have a sudden demand: Calculate the first N working days of the specified date or the next N working days (excluding weekends, which cannot be calculated on statutory holidays ), then I studied Oracle's time functions and cycle methods. The specific implementation method is as follows, and there is no difficulty. I haven't studied the database, and I don't know how efficient the following writing is.
Or is there any better way of writing. O (too many) o alas!
Create or replace procedure proc_CalculationWorkDate
(
Plan_date in date, -- logon date
Flag in number, -- 1 forward date, 0 backward date
Date_number in number, -- days
Out_date out date -- the calculated date
)
Is
DayOfWeek number: = 0; -- number of the week
Dates number: = date_number; -- initialize the previous or subsequent days (including the workday) to give it equal to the number of days
I int: = 0;
J int: = 0;
Begin
If flag = 1 then -- calculate the forward date
While I <date_number + 1 loop
SELECT to_number (to_char (sysdate + I + j, 'D') into dayOfWeek FROM DUAL; -- return the value of the week
If dayOfWeek = 1 or dayOfWeek = 7 then -- Saturday and Sunday
Dates: = dates + 1;
I: = I;
J: = j + 1;
Else
I: = I + 1;
End if;
End loop;
Select plan_date + dates into out_date from dual;
-- DBMS_OUTPUT.PUT_LINE (dates );
End if;
If flag = 0 then -- calculate the following date
While I <date_number + 1 loop
SELECT to_number (to_char (sysdate-I-j, 'D') into dayOfWeek FROM DUAL;
If dayOfWeek = 1 or dayOfWeek = 7 then
Dates: = dates + 1;
I: = I;
J: = j + 1;
Else
I: = I + 1;
End if;
End loop;
Select plan_date-dates into out_date from dual;
-- DBMS_OUTPUT.PUT_LINE (dates );
End if;
End;