Oracle批量生成日历数据表

目标：生成从2018年01月01日起后延50年的日历数据表，周六日默认是节假日，法定假日需要手动维护。

步骤1：创建表

-- Create table
create table KR_HOLIDAY_MANAGE
(
  sys_id     VARCHAR2(50) not null,
  set_date   VARCHAR2(10) not null,
  year       VARCHAR2(4),
  month      VARCHAR2(2),
  week       VARCHAR2(20),
  year_num   VARCHAR2(3),
  month_num  VARCHAR2(2),
  is_holiday VARCHAR2(2) not null,
  remarks    VARCHAR2(50)
);
-- Add comments to the table 
comment on table KR_HOLIDAY_MANAGE
  is '假日信息表';
-- Add comments to the columns 
comment on column KR_HOLIDAY_MANAGE.sys_id
  is '系统标识';
comment on column KR_HOLIDAY_MANAGE.set_date
  is '日';
comment on column KR_HOLIDAY_MANAGE.year
  is '年';
comment on column KR_HOLIDAY_MANAGE.month
  is '月';
comment on column KR_HOLIDAY_MANAGE.week
  is '星期几';
comment on column KR_HOLIDAY_MANAGE.year_num
  is '年的第几天';
comment on column KR_HOLIDAY_MANAGE.month_num
  is '月的第几天';
comment on column KR_HOLIDAY_MANAGE.is_holiday
  is '是否假日：1是、0否，默认值为0';
comment on column KR_HOLIDAY_MANAGE.remarks
  is '备注';
-- Create/Recreate indexes 
create index HOLEDAY_IDX on KR_HOLIDAY_MANAGE (IS_HOLIDAY)
  tablespace KEYRISK
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index HOLEDAY_IDX_YEAR on KR_HOLIDAY_MANAGE (YEAR);
alter table KR_HOLIDAY_MANAGE add primary key (SYS_ID, SET_DATE);
步骤2： 创建过程

DECLARE
  D_DATE DATE;
  V_BEGIN_DATE VARCHAR2(8);
BEGIN
  V_BEGIN_DATE := '20180101';
  D_DATE := TO_DATE(V_BEGIN_DATE,'YYYYMMDD');
  EXECUTE IMMEDIATE 'TRUNCATE TABLE KR_HOLIDAY_MANAGE';
  WHILE D_DATE < ADD_MONTHS(TO_DATE(V_BEGIN_DATE,'YYYYMMDD'),600) LOOP
  INSERT INTO KR_HOLIDAY_MANAGE 
  SELECT '0',
         TO_CHAR(D_DATE,'YYYYMMDD') SET_DATE,
         TO_CHAR(D_DATE,'YYYY') YEAR,
         TO_CHAR(D_DATE,'MM') MONTH,
         TO_CHAR(D_DATE,'DAY') WEEK,
         TO_CHAR(D_DATE,'DDD') YEAR_NUM,
         TO_CHAR(D_DATE,'DD') MONTH_NUM,
         CASE WHEN TO_CHAR(D_DATE,'D') IN ('1','7') THEN '1' ELSE '0' END IS_HOLIDAY,
         ''
    FROM DUAL;
  
  D_DATE := D_DATE + 1;  
  END LOOP;
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('S');
END;
结果截图：