CREATE OR REPLACE PROCEDURE P_REPOST_WEEK_QF (STARTDATE in varchar2)IS FIRSTDAYDATE DATE; LASTDAYDATE DATE; MONDAY DATE; SUNDAY DATE; STARTHALF DATE; ENDHALF DATE; MAXDATE DATE; I INTEGER; J INTEGER; YEAR INTEGER; BEGIN SELECT TO_DATE(STARTDATE,'yyyyMMdd') INTO FIRSTDAYDATE FROM DUAL; SELECT LAST_DAY(ADD_MONTHS(FIRSTDAYDATE,11)) INTO LASTDAYDATE FROM DUAL; MONDAY:=FIRSTDAYDATE; MAXDATE:=MONDAY; SELECT TRUNC(MONDAY,'iw') INTO SUNDAY FROM DUAL; SELECT substr(STARTDATE,4) INTO YEAR FROM DUAL; I:=1; J:=1; WHILE (MAXDATE <= LASTDAYDATE) LOOP SELECT TRUNC(SUNDAY,'iw')+6 INTO MONDAY FROM DUAL; SELECT TRUNC(MONDAY,'iw')+6 INTO MAXDATE FROM DUAL; SELECT TRUNC(SUNDAY,'iw')+2 INTO ENDHALF FROM DUAL; SELECT TRUNC(SUNDAY,'iw')+3 INTO STARTHALF FROM DUAL; IF MAXDATE>LASTDAYDATE THEN return; END IF; INSERT INTO REPORT_WEEK_HALF(WD_YEAR,WD_NATURAL_WEEK,WD_START_DATE,WD_END_DATE)VALUES (YEAR,J,to_char(SUNDAY,'yyyyMMdd'),to_char(ENDHALF,'yyyyMMdd'));
J:=J+1; INSERT INTO REPORT_WEEK_HALF(WD_YEAR,to_char(STARTHALF,to_char(MONDAY,'yyyyMMdd'));
INSERT INTO REPORT_WEEK_ALL(WD_YEAR,WD_END_DATE)VALUES (YEAR,I,'yyyyMMdd')); SELECT TRUNC(SUNDAY,'iw')+7 INTO SUNDAY FROM DUAL; I:=I+1; J:=J+1; END LOOP; COMMIT; END P_REPOST_WEEK_QF; (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|