The Oracle stored procedure returns (N) workdays Based on the specified date.

The Oracle stored procedure returns (N) workdays 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 -- date calculated) is dayOfWeek number: = 0; -- number of the week dates number: = date_number; -- initialize the number of days forward or backward (including the working day) 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; -- returns 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 future 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;