加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 (https://www.lidatong.com.cn/)- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置: 首页 > 百科 > 正文

oracle存储过程

发布时间:2020-12-12 16:42:51 所属栏目:百科 来源:网络整理
导读: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(STAR

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;

(编辑:李大同)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    推荐文章
      热点阅读