Oracle存储过程

存储过程

超级全的生成时间的表的存储过程

create or replace procedure create_d_day is
  v_thedate         DATE;
  v_themonth        VARCHAR2(20);
  v_theyear         varchar(20);
  v_dayofweek       varchar(12);
  v_dayofmonth      varchar(20);
  v_weekofyear      varchar(20);
  v_monthofyear     varchar(10);
  v_quarter         varchar(20);
  adddays           int;
  v_timename        varchar(100);
  v_month_days      varchar(10);
  i                 number;
  v_weekofyear_temp varchar(20); --年的第几周 临时变量
  v_weekofmonth     varchar(5); --当月第几周格式：第1周
  v_quarterofyear   varchar(5); --当年第几季度格式：1季度
  --set serveroutput on
begin

  adddays   := 1;
  v_thedate := to_date('01/01/2000', 'mm/dd/yyyy');

  --清空表记录，为从新插入数据作准备。
  execute IMMEDIATE 'truncate table d_day';

  WHILE (v_thedate < to_date('12/29/'||to_char(sysdate,'yyyy'), 'mm/dd/yyyy')) loop
    v_dayofweek   := to_char(v_thedate, 'day'); --星期几
    v_theyear     := to_char(v_thedate, 'yyyy') || '年'; --年
    v_themonth    := v_theyear || to_char(v_thedate, 'mm') || '月'; --月份
    v_dayofmonth  := to_char(v_thedate, 'dd') || '日'; --日(字符型)
    v_weekofyear  := v_theyear || to_char(v_thedate, 'iw') || '周'; --年的第几周
    v_monthofyear := to_char(v_thedate, 'mm') || '月'; --月(数字型)
    v_quarter     := v_theyear || to_char(v_thedate, 'q') || '季度'; --季度
    v_timename    := to_char(v_thedate, 'yyyy') || '年' ||
                     to_char(v_thedate, 'MM') || '月' ||
                     to_char(v_thedate, 'dd') || '日';
    v_month_days  := to_char(last_day(v_thedate), 'dd');
  
    v_weekofyear_temp := TO_CHAR(v_thedate, 'IW'); --年的第几周 临时变量
    if to_char(v_thedate, 'mm') = '12' and v_weekofyear_temp = '01' then
      v_weekofyear := (to_char(v_thedate, 'yyyy') + 1) || '年' ||
                      v_weekofyear_temp || '周'; --年的第几周
    elsif to_char(v_thedate, 'mm') = '01' and v_weekofyear_temp = '53' then
      v_weekofyear := (to_char(v_thedate, 'yyyy') - 1) || '年' ||
                      v_weekofyear_temp || '周'; --年的第几周
    end if;
  
    --v_weekofmonth   := '第' || to_char(v_thedate, 'W') || '周'; --当月第几周格式：第1周
    select case
             when to_char(trunc(v_thedate, 'mm'), 'IW') +
                  to_char(v_thedate, 'W') = to_char(v_thedate, 'IW') then
              '第' || (to_number(to_char(v_thedate, 'W')) + 1) || '周'
             else
              '第' || to_char(v_thedate, 'W') || '周'
           end
      into v_weekofmonth
      from dual; --当月第几周格式：第1周
    v_quarterofyear := to_char(v_thedate, 'q') || '季度'; --当年第几季度格式：1季度
  
    INSERT INTO d_day
      (day_id,
       the_date,
       the_month,
       the_year,
       day_of_week,
       day_of_month,
       week_of_year,
       month_of_year,
       QUARTER,
       TIME_NAME,
       days,
       week_of_month,
       quarter_of_year)
    VALUES
      (to_char(v_thedate, 'yyyymmdd'),
       v_thedate,
       v_themonth,
       v_theyear,
       v_dayofweek,
       v_dayofmonth,
       v_weekofyear,
       v_monthofyear,
       v_quarter,
       v_timename,
       v_month_days,
       v_weekofmonth,
       v_quarterofyear);
  
    v_thedate := v_thedate + adddays;
  END loop;
  commit;
end create_d_day;

结果样式：