oracle 存储过程生成 每周工作日

 

 

 

--初始化 规划周表 hy_month_week  写成可重复执行的
  PROCEDURE init_week_rep(v_year VARCHAR2) AS
    -- 一年的日期
    CURSOR temp_cursor(date1 VARCHAR2, date2 VARCHAR2) IS
      SELECT to_char(date1, 'yyyyMMdd') date1, to_char(date1, 'yyyy') AS year1, to_char(date1, 'mm') AS month1, to_char(date1, 'dd') AS day1,
             decode(to_char(date1, 'D') - 1, 0, 7, to_char(date1, 'D') - 1) AS week, TRIM(lpad(to_char(date1, 'w'), 6)) AS dy_zj, TRIM(lpad(to_char(date1, 'ww'), 6)) AS dn_zj
        FROM (SELECT to_date(date1, 'yyyymmdd') + LEVEL - 1 AS date1
                 FROM dual
               CONNECT BY LEVEL <= (last_day(to_date(date2, 'yyyymmdd')) - to_date(date1, 'yyyymmdd') + 1));
   
    temp temp_cursor%ROWTYPE;
   
    v_date1 VARCHAR2(8); --开始
    v_date2 VARCHAR2(8); --结束
    v_dy    VARCHAR2(8); --当月
    v_dz    INTEGER := 0; --当前周
  BEGIN
    DELETE hztf.HY_MONTH_WEEK;
    OPEN temp_cursor(v_year || '0101', v_year || '1201');
    LOOP
      FETCH temp_cursor
        INTO temp;
      EXIT WHEN temp_cursor%NOTFOUND;
     
      --当月
      IF v_dy IS NULL
      THEN
        v_dy := temp.year1 || temp.month1;
      END IF;
      --判断是否 进入下月
      IF v_dy != (temp.year1 || temp.month1)
      THEN
        v_dy := temp.year1 || temp.month1;
        v_dz := 0;
      END IF;
     
      --只有 周一到周五进入
      IF temp.week >= 1 AND
         temp.week <= 5
      THEN
        IF temp.week = '1'
        THEN
          v_date1 := temp.date1;
        END IF;
       
        IF temp.week = '5'
        THEN
          v_date2 := temp.date1;
          v_dz    := v_dz + 1;
         
          INSERT INTO hztf.HY_MONTH_WEEK
            (week_id, month1, week_name, week_bm)
          VALUES
            (temp.year1 || temp.dn_zj, temp.year1 || temp.month1, '第' || v_dz || '周',
             REPLACE(to_char(to_date(v_date1, 'yyyyMMdd'), 'MM.dd'), '0', '') || '-' || REPLACE(to_char(to_date(v_date2, 'yyyyMMdd'), 'MM.dd'), '0', ''));
         
        END IF;
      END IF;
     
    END LOOP;
    CLOSE temp_cursor;
    COMMIT;
   
  END init_week_rep;