写一个函数,参数是n in numberselect count(*) from (select to_char(sysdate, 'D') work_day from dual) where work_day not in (1, 7)得出的结果为1表示工作日,为0表示周末写一个循环i if countdays = 1 then nCount := nCount + 1; if nCount = n then return sysdate + i; end if; end if;
with temp as( select level lv from dual connect by level < 100 )select * from ( select to_date('2010-08-16','yyyy-mm-dd')+lv,rownum rn from temp where to_char(to_date('2010-08-16','yyyy-mm-dd')+ lv,'DY') not in ('星期六','星期日') ) where rn = 10
用mod写个函数吧,循环和递归的话效率有点问题.
with temp as( select level lv from dual connect by level < 32)select * from ( select to_date('2010-08-16','yyyy-mm-dd')+lv,rownum rn from temp where to_char(to_date('2010-08-16','yyyy-mm-dd')+ lv,'DY') not in ('星期六','星期日') ) where rn = 10
CREATE OR REPLACE PROCEDURE get_work_date_proc(in_day_count IN NUMBER, out_date_time OUT VARCHAR2, out_message OUT VARCHAR2) IS v_date_time DATE; BEGIN IF in_day_count < 0 THEN out_message := '参数错误'; RETURN; END IF; v_date_time := trunc(SYSDATE); FOR i IN 1..in_day_count LOOP v_date_time := v_date_time + 1; IF to_char(v_date_time,'d') IN ('1','7') THEN v_date_time := v_date_time + 1; END IF;
IF to_char(v_date_time,'d') IN ('1','7') THEN v_date_time := v_date_time + 1; END IF; END LOOP; out_date_time := v_date_time; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('ERROR'); END;
create or replace function func_get_workday(startdate in date,dateINTERVAL IN NUMBER) return date is Result date; begin IF to_char(startdate,'D')='6' THEN RESULT:=startdate+trunc(dateINTERVAL/5)*2+dateINTERVAL+1; ELSIF to_char(startdate,'D')='7' THEN RESULT:=startdate+trunc(dateINTERVAL/5)*2+dateINTERVAL; ELSIF to_char(startdate,'D')>to_char(startdate+mod(dateINTERVAL,5),'D') THEN RESULT:=startdate+trunc(dateINTERVAL/5)*2+dateINTERVAL+2; ELSIF to_char(startdate+mod(dateINTERVAL,5),'D') IN ('6','7') THEN RESULT:=startdate+trunc(dateINTERVAL/5)*2+dateINTERVAL+2; ELSE RESULT:=startdate+trunc(dateINTERVAL/5)*2+dateINTERVAL; END IF; return(Result); end func_get_workday;
SQL> SELECT SYSDATE, func_get_workday(SYSDATE,+1),to_char(SYSDATE,'D'),to_char(func_get_workday(SYSDATE,+1),'D') FROM dual;
更正下,原来7对应的是周六 create or replace function func_get_workday(startdate in date,dateINTERVAL IN NUMBER) return date is Result date; begin IF to_char(startdate,'D')='7' THEN RESULT:=startdate+trunc(dateINTERVAL/5)*2+dateINTERVAL+1; ELSIF to_char(startdate,'D')='1' THEN RESULT:=startdate+trunc(dateINTERVAL/5)*2+dateINTERVAL; ELSIF to_char(startdate,'D')>to_char(startdate+mod(dateINTERVAL,5),'D') THEN RESULT:=startdate+trunc(dateINTERVAL/5)*2+dateINTERVAL+2; ELSIF to_char(startdate+mod(dateINTERVAL,5),'D') IN ('7','1') THEN RESULT:=startdate+trunc(dateINTERVAL/5)*2+dateINTERVAL+2; ELSE RESULT:=startdate+trunc(dateINTERVAL/5)*2+dateINTERVAL; END IF; return(Result); end func_get_workday;
一定要用函数的话,改一下就好啦:CREATE OR REPLACE FUNCTION get_work_date_func(in_day_count IN NUMBER) RETURN VARCHAR2 IS v_date_time DATE; BEGIN v_date_time := trunc(SYSDATE); FOR i IN 1..in_day_count LOOP v_date_time := v_date_time + 1; IF to_char(v_date_time,'d') IN ('1','7') THEN v_date_time := v_date_time + 1; END IF; IF to_char(v_date_time,'d') IN ('1','7') THEN v_date_time := v_date_time + 1; END IF; END LOOP; RETURN(v_date_time); END;
我知道可以直接改过程为函数,其实我是想问有没有其他的实现逻辑,或者如何用sql实现。
28号是周六,下一个工作日应该是30号,没错啊SQL> SELECT to_char(SYSDATE+4,'D'),SYSDATE+4,func_get_workday(SYSDATE+4,1) FROM dual;
CREATE OR REPLACE FUNCTION workday_fun( v_date DATE DEFAULT SYSDATE, -- 日期,默认为当前日期 v_IDay NUMBER -- 距离输入的日期的N个工作日 ) RETURN DATE IS v_nDate DATE; BEGIN v_nDate := v_date+trunc(v_IDay/5)*7+mod(v_IDay,5); IF to_char(v_nDate,'D')='7' THEN v_nDate := v_nDate + 2; ELSIF to_char(v_nDate,'D')='1' THEN v_nDate := v_nDate + 1; END IF; RETURN v_nDate; END; / -- 测试:scott@SZTYORA> select workday_fun(SYSDATE,10) FROM dual;WORKDAY_FUN(SYSDATE ------------------- 2010-09-07 17:25:16scott@SZTYORA> select workday_fun(SYSDATE+3,10) FROM dual;WORKDAY_FUN(SYSDATE ------------------- 2010-09-10 17:25:59scott@SZTYORA> select workday_fun(SYSDATE+4,10) FROM dual;WORKDAY_FUN(SYSDATE ------------------- 2010-09-13 17:26:15scott@SZTYORA> select workday_fun(SYSDATE+5,10) FROM dual;WORKDAY_FUN(SYSDATE ------------------- 2010-09-13 17:26:28
-- 纠正一下: CREATE OR REPLACE FUNCTION workday_fun( v_date DATE DEFAULT SYSDATE, -- 日期,默认为当前日期 v_IDay NUMBER -- 距离输入的日期的N个工作日 ) RETURN DATE IS v_nDate DATE; BEGIN v_nDate := v_date+trunc(v_IDay/5)*7+mod(v_IDay,5); IF v_IDay >= 1 THEN IF to_char(v_nDate,'D')='7' THEN v_nDate := v_nDate + 2; ELSIF to_char(v_nDate,'D')='1' THEN v_nDate := v_nDate + 1; END IF; END IF; RETURN v_nDate; END; /
-- 或者这样(更好) CREATE OR REPLACE FUNCTION workday_fun( v_date DATE DEFAULT SYSDATE, -- 日期,默认为当前日期 v_IDay NUMBER -- 距离输入的日期的N个工作日 ) RETURN DATE IS v_nDate DATE; BEGIN IF v_IDay >= 1 THEN v_nDate := v_date+trunc(v_IDay/5)*7+mod(v_IDay,5); IF to_char(v_nDate,'D')='7' THEN v_nDate := v_nDate + 2; ELSIF to_char(v_nDate,'D')='1' THEN v_nDate := v_nDate + 1; END IF; ELSE v_nDate := v_date; END IF; RETURN v_nDate; END; /
where work_day not in (1, 7)得出的结果为1表示工作日,为0表示周末写一个循环i
if countdays = 1 then
nCount := nCount + 1;
if nCount = n then
return sysdate + i;
end if;
end if;
select level lv from dual connect by level < 100
)select * from (
select to_date('2010-08-16','yyyy-mm-dd')+lv,rownum rn
from temp where to_char(to_date('2010-08-16','yyyy-mm-dd')+ lv,'DY') not in ('星期六','星期日')
) where rn = 10
select level lv from dual connect by level < 32)select * from (
select to_date('2010-08-16','yyyy-mm-dd')+lv,rownum rn
from temp where to_char(to_date('2010-08-16','yyyy-mm-dd')+ lv,'DY') not in ('星期六','星期日')
) where rn = 10
CREATE OR REPLACE PROCEDURE get_work_date_proc(in_day_count IN NUMBER,
out_date_time OUT VARCHAR2,
out_message OUT VARCHAR2) IS
v_date_time DATE;
BEGIN
IF in_day_count < 0 THEN
out_message := '参数错误';
RETURN;
END IF; v_date_time := trunc(SYSDATE);
FOR i IN 1..in_day_count LOOP
v_date_time := v_date_time + 1;
IF to_char(v_date_time,'d') IN ('1','7') THEN
v_date_time := v_date_time + 1;
END IF;
IF to_char(v_date_time,'d') IN ('1','7') THEN
v_date_time := v_date_time + 1;
END IF;
END LOOP; out_date_time := v_date_time;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('ERROR');
END;
Result date;
begin
IF to_char(startdate,'D')='6' THEN
RESULT:=startdate+trunc(dateINTERVAL/5)*2+dateINTERVAL+1;
ELSIF to_char(startdate,'D')='7' THEN
RESULT:=startdate+trunc(dateINTERVAL/5)*2+dateINTERVAL;
ELSIF to_char(startdate,'D')>to_char(startdate+mod(dateINTERVAL,5),'D') THEN
RESULT:=startdate+trunc(dateINTERVAL/5)*2+dateINTERVAL+2;
ELSIF to_char(startdate+mod(dateINTERVAL,5),'D') IN ('6','7') THEN
RESULT:=startdate+trunc(dateINTERVAL/5)*2+dateINTERVAL+2;
ELSE
RESULT:=startdate+trunc(dateINTERVAL/5)*2+dateINTERVAL;
END IF;
return(Result);
end func_get_workday;
SQL> SELECT SYSDATE, func_get_workday(SYSDATE,+1),to_char(SYSDATE,'D'),to_char(func_get_workday(SYSDATE,+1),'D') FROM dual;
SYSDATE FUNC_GET_WORKDAY(SYSDATE,+1) TO_CHAR(SYSDATE,'D') TO_CHAR(FUNC_GET_WORKDAY(SYSDA
----------- ---------------------------- -------------------- ------------------------------
2010-8-24 1 2010-8-25 15:48:19 3 4
SQL> SELECT SYSDATE, func_get_workday(SYSDATE,+2),to_char(SYSDATE,'D'),to_char(func_get_workday(SYSDATE,+2),'D') FROM dual
2 ;
SYSDATE FUNC_GET_WORKDAY(SYSDATE,+2) TO_CHAR(SYSDATE,'D') TO_CHAR(FUNC_GET_WORKDAY(SYSDA
----------- ---------------------------- -------------------- ------------------------------
2010-8-24 1 2010-8-26 15:48:28 3 5
SQL> SELECT SYSDATE, func_get_workday(SYSDATE,+3),to_char(SYSDATE,'D'),to_char(func_get_workday(SYSDATE,+3),'D') FROM dual ;
SYSDATE FUNC_GET_WORKDAY(SYSDATE,+3) TO_CHAR(SYSDATE,'D') TO_CHAR(FUNC_GET_WORKDAY(SYSDA
----------- ---------------------------- -------------------- ------------------------------
2010-8-24 1 2010-8-29 15:48:37 3 1
SQL> SELECT SYSDATE, func_get_workday(SYSDATE,+4),to_char(SYSDATE,'D'),to_char(func_get_workday(SYSDATE,+4),'D') FROM dual;
SYSDATE FUNC_GET_WORKDAY(SYSDATE,+4) TO_CHAR(SYSDATE,'D') TO_CHAR(FUNC_GET_WORKDAY(SYSDA
----------- ---------------------------- -------------------- ------------------------------
2010-8-24 1 2010-8-30 15:48:49 3 2
SQL> SELECT SYSDATE, func_get_workday(SYSDATE,+5),to_char(SYSDATE,'D'),to_char(func_get_workday(SYSDATE,+5),'D') FROM dual;
SYSDATE FUNC_GET_WORKDAY(SYSDATE,+5) TO_CHAR(SYSDATE,'D') TO_CHAR(FUNC_GET_WORKDAY(SYSDA
----------- ---------------------------- -------------------- ------------------------------
2010-8-24 1 2010-8-31 15:48:57 3 3
SQL> SELECT SYSDATE, func_get_workday(SYSDATE,+6),to_char(SYSDATE,'D'),to_char(func_get_workday(SYSDATE,+6),'D') FROM dual;
SYSDATE FUNC_GET_WORKDAY(SYSDATE,+6) TO_CHAR(SYSDATE,'D') TO_CHAR(FUNC_GET_WORKDAY(SYSDA
----------- ---------------------------- -------------------- ------------------------------
2010-8-24 1 2010-9-1 15:49:06 3 4
SQL>
create or replace function func_get_workday(startdate in date,dateINTERVAL IN NUMBER) return date is
Result date;
begin
IF to_char(startdate,'D')='7' THEN
RESULT:=startdate+trunc(dateINTERVAL/5)*2+dateINTERVAL+1;
ELSIF to_char(startdate,'D')='1' THEN
RESULT:=startdate+trunc(dateINTERVAL/5)*2+dateINTERVAL;
ELSIF to_char(startdate,'D')>to_char(startdate+mod(dateINTERVAL,5),'D') THEN
RESULT:=startdate+trunc(dateINTERVAL/5)*2+dateINTERVAL+2;
ELSIF to_char(startdate+mod(dateINTERVAL,5),'D') IN ('7','1') THEN
RESULT:=startdate+trunc(dateINTERVAL/5)*2+dateINTERVAL+2;
ELSE
RESULT:=startdate+trunc(dateINTERVAL/5)*2+dateINTERVAL;
END IF;
return(Result);
end func_get_workday;
v_date_time DATE;
BEGIN v_date_time := trunc(SYSDATE);
FOR i IN 1..in_day_count LOOP
v_date_time := v_date_time + 1;
IF to_char(v_date_time,'d') IN ('1','7') THEN
v_date_time := v_date_time + 1;
END IF; IF to_char(v_date_time,'d') IN ('1','7') THEN
v_date_time := v_date_time + 1;
END IF;
END LOOP; RETURN(v_date_time);
END;
我知道可以直接改过程为函数,其实我是想问有没有其他的实现逻辑,或者如何用sql实现。
TO_CHAR(SYSDATE+4,'D') SYSDATE+4 FUNC_GET_WORKDAY(SYSDATE+4,1)
---------------------- ----------- -----------------------------
7 2010-8-28 1 2010-8-30 16:59:42
SQL>
v_date DATE DEFAULT SYSDATE, -- 日期,默认为当前日期
v_IDay NUMBER -- 距离输入的日期的N个工作日
)
RETURN DATE IS
v_nDate DATE;
BEGIN
v_nDate := v_date+trunc(v_IDay/5)*7+mod(v_IDay,5);
IF to_char(v_nDate,'D')='7' THEN
v_nDate := v_nDate + 2;
ELSIF to_char(v_nDate,'D')='1' THEN
v_nDate := v_nDate + 1;
END IF;
RETURN v_nDate;
END;
/
-- 测试:scott@SZTYORA> select workday_fun(SYSDATE,10) FROM dual;WORKDAY_FUN(SYSDATE
-------------------
2010-09-07 17:25:16scott@SZTYORA> select workday_fun(SYSDATE+3,10) FROM dual;WORKDAY_FUN(SYSDATE
-------------------
2010-09-10 17:25:59scott@SZTYORA> select workday_fun(SYSDATE+4,10) FROM dual;WORKDAY_FUN(SYSDATE
-------------------
2010-09-13 17:26:15scott@SZTYORA> select workday_fun(SYSDATE+5,10) FROM dual;WORKDAY_FUN(SYSDATE
-------------------
2010-09-13 17:26:28
-- 纠正一下:
CREATE OR REPLACE FUNCTION workday_fun(
v_date DATE DEFAULT SYSDATE, -- 日期,默认为当前日期
v_IDay NUMBER -- 距离输入的日期的N个工作日
)
RETURN DATE IS
v_nDate DATE;
BEGIN
v_nDate := v_date+trunc(v_IDay/5)*7+mod(v_IDay,5);
IF v_IDay >= 1 THEN
IF to_char(v_nDate,'D')='7' THEN
v_nDate := v_nDate + 2;
ELSIF to_char(v_nDate,'D')='1' THEN
v_nDate := v_nDate + 1;
END IF;
END IF;
RETURN v_nDate;
END;
/
CREATE OR REPLACE FUNCTION workday_fun(
v_date DATE DEFAULT SYSDATE, -- 日期,默认为当前日期
v_IDay NUMBER -- 距离输入的日期的N个工作日
)
RETURN DATE IS
v_nDate DATE;
BEGIN
IF v_IDay >= 1 THEN
v_nDate := v_date+trunc(v_IDay/5)*7+mod(v_IDay,5);
IF to_char(v_nDate,'D')='7' THEN
v_nDate := v_nDate + 2;
ELSIF to_char(v_nDate,'D')='1' THEN
v_nDate := v_nDate + 1;
END IF;
ELSE
v_nDate := v_date;
END IF;
RETURN v_nDate;
END;
/