oracle数据库获取多少天后的下一个工作日（工作日维护）

最新推荐文章于 2022-04-26 15:29:28 发布

cqwuliu

最新推荐文章于 2022-04-26 15:29:28 发布

阅读量738

点赞数

分类专栏：数据库知识文章标签： sql

本文链接：https://blog.csdn.net/weixin_44873668/article/details/107994117

版权

数据库知识专栏收录该内容

12 篇文章 0 订阅

订阅专栏

话不多说直接上代码

-- Create table
create table TIMERECORD
(
  jjr_id         VARCHAR2(36) not null,
  jjr_time       DATE not null,
  jjr_state      NUMBER(2) default 0,
  jjr_remark     VARCHAR2(200),
  jjr_creattime  TIMESTAMP(6) default sysdate not null,
  jjr_creatuser  VARCHAR2(36) not null,
  jjr_modified   TIMESTAMP(6) default sysdate,
  jjr_modifyuser VARCHAR2(36)
)
tablespace DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns 
comment on column TIMERECORD.jjr_time
  is '需要维护的时间 yyyy/MM/dd';
comment on column TIMERECORD.jjr_state
  is '0-无效 1-非正常上班   2-非正常休息';
comment on column TIMERECORD.jjr_remark
  is '说明';
-- Create/Recreate indexes 
create index IDX_TIMERECORD_JJR_TIME on TIMERECORD (TRUNC(JJR_TIME,'fmdd'))
  tablespace DATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

create or replace function next_Workdate(workdate  in number) return number is
v_date number(10);
add_date number(10);
v_num number(2);
v_f1 number(2);
workdatenew number(10);

begin
  add_date:=0;
   select count(*) into v_num  from TIMERECORD td where td.jjr_state=1
            and to_char(td.jjr_time,'yyyy-MM-dd')=to_char(sysdate+workdate,'yyyy-MM-dd');
            if (to_char(sysdate+workdate, 'DY', 'nls_date_language=american') in
                    ('SUN','SAT'))  and v_num<>1
                    then v_f1:=1;
                    end if;
              select count(*) into v_num  from TIMERECORD td where td.jjr_state=2
            and to_char(td.jjr_time,'yyyy-MM-dd')=to_char(sysdate+workdate,'yyyy-MM-dd');

            if v_num=1 then
              v_f1:=1;
                    end if;
              add_date:=workdate;

           while  v_f1=1 loop
             add_date:=add_date+1;

              select count(*) into v_num  from TIMERECORD td where td.jjr_state=2
            and to_char(td.jjr_time,'yyyy-MM-dd')=to_char(sysdate+add_date,'yyyy-MM-dd');
            if  (to_char(sysdate+add_date, 'DY', 'nls_date_language=american') not in
                    ('SUN','SAT'))  and v_num<>1 then
              v_f1:=2;
             end if;


             select count(*) into v_num  from TIMERECORD td where td.jjr_state=1
            and to_char(td.jjr_time,'yyyy-MM-dd')=to_char(sysdate+add_date,'yyyy-MM-dd');
            if (to_char(sysdate+add_date, 'DY', 'nls_date_language=american') in
                    ('SUN','SAT'))  and v_num=1
                    then v_f1:=2;
                    end if;

           end loop;
    return  add_date;
    end;