Oracle国家节假日以及交易日筛选的存储过程

需求：筛选出今天之前的三个交易日，排除周六周日以及国家法定节假日

一、建立节假日表

create table HOLIDAY
(
  holiday_date NUMBER(10) not null,
  holiday_name VARCHAR2(255) not null,
  holiday_type CHAR(1) not null,
  time_stamp   DATE default sysdate
)
tablespace TS_DW
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 80K
    next 16K
    minextents 1
    maxextents unlimited
  );
comment on column HOLIDAY.holiday_date
  is '节假日期(yyyyMMdd)';
comment on column HOLIDAY.holiday_name
  is '节假日名称';
comment on column HOLIDAY.holiday_type
  is '是否放假';
comment on column HOLIDAY.time_stamp
  is '时间戳';

注意：节假日信息需要手动维护，每年根据国家公布的节假日信息进行更新即可。

insert into HOLIDAY (holiday_date, holiday_name, holiday_type)  values  (20170214, '情人节', '1');

insert into HOLIDAY (holiday_date, holiday_name, holiday_type)  values  (20171001, '国庆节、中秋节', '0');

二、存储过程

create or replace procedure holiday_test is
  i                number default 0;
  todayDate        date;--今天
  num              number;
  h_type           char;
  flag             number;--1是节假日   0为不是节假日
  week              number default 0;
  one              date;--昨天
  two              date;--前天
  oneNum           number;
  twoNum           number;
  todayNum         number;
  begin
    --初始化为系统当前日期
    todayDate        := sysdate;
   
    --判断今天是不是周末
    select to_char(todayDate, 'd') into week from dual;

    todayNum := to_char(todayDate, 'yyyymmdd');

    --判断今天是不是节假日
    select count(*) into num FROM holiday   where holiday_date = todayNum;
    if num > 0 then
      --说明有记录
      select holiday_type into h_type FROM holiday where holiday_date = todayNum;
      if h_type != '0' then
        --holiday_type 不等于 0 说明不是节假日
        flag := 0;
      else
        flag := 1;
      end if;
    else
      flag := 0;
    end if;

    -- begin 处理
    --今天不是周六且不是周末且不是节假日  如果今天是交易日则处理，否则不做任何处理
    if (week <> 1) and (week <> 7) and (flag = 0) then

      --昨天
      one := todayDate - 1;--今天的前一天

      i   := 0;--初始化循环参数
      while i < 20 LOOP

        select to_char(one, 'd') into week from dual;--判断是否是周末

        oneNum := to_char(one, 'yyyymmdd');
        --判断是否是节假日
        select count(*) into  num  FROM holiday   where holiday_date = oneNum;
        if num > 0 then--说明有记录
          select holiday_type into h_type FROM holiday where holiday_date = oneNum;
          if h_type != '0' then
            --holiday_type 不等于 0 说明不是节假日
            flag := 0;
          else
            flag := 1;
          end if;
        else
          flag := 0;
        end if;
        --开始处理
        if (week not in (1, 7)) and (flag = 0 ) then
          --跳出当前循环
          exit;
        end if;
        one := one - 1;
        i   := i + 1;
      END LOOP;

      --前天
      i   := 0; --初始化循环参数
      two := one - 1;
      while i < 20 LOOP

        select to_char(two, 'd') into week from dual;--判断是否是周末

        twoNum := to_char(two, 'yyyymmdd');
        --判断是否是节假日
        select count(*) into  num  FROM holiday   where holiday_date = twoNum;
        if num > 0 then--说明有记录
          select holiday_type into h_type FROM holiday where holiday_date = twoNum;
          if h_type != '0' then
            --holiday_type 不等于 0 说明不是节假日
            flag := 0;
          else
            flag := 1;
          end if;
        else
          flag := 0;
        end if;
        --开始处理
        if (week not in (1, 7)) and (flag = 0 ) then
          --跳出当前循环
          exit;
        end if;
        two := two - 1;
        i   := i + 1;
      END LOOP;
      dbms_output.put_line('today is '||todayNum);
      dbms_output.put_line('one is '||oneNum);
      dbms_output.put_line('two is '||twoNum);
      end if;
end holiday_test;

运行此存储过程，即可筛选出今天之前的三个交易日。