目标：生成从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;
结果截图：
