第一次写的存储过程

/**

*需求：当为日报时，stype=‘day’传入一个字符串日期修改当前的报表日期为昨天的报表（时间归属时间为topDate-1）

*当为周报时，stype=‘week’计算传入日期这一周的周一为周报日期并修改相应的报表产生时间

*/

CREATE OR REPLACE PROCEDURE proc_update_top_date(topDate IN VARCHAR2, stype IN varchar2)
 AS
 v_top_date VARCHAR2(50);
 v_year VARCHAR2(4);
 v_month VARCHAR2(2);
 v_day VARCHAR2(2);
 v_dstype VARCHAR2(10):='day';
 v_wstype VARCHAR2(10):='week';
 v_mstype VARCHAR2(10):='month';
 BEGIN
     IF stype = v_dstype THEN
         SELECT (to_char(sysdate,'YYYY')||'-'|| to_char(sysdate,'MM')||'-'||to_char(sysdate,'DD')) INTO v_top_date FROM dual;
         SELECT to_char(sysdate,'yyyy') INTO v_year FROM dual;
         SELECT to_char(sysdate,'mm') INTO v_month FROM dual;
         SELECT to_char(sysdate,'DD') INTO v_day FROM dual;
         UPDATE mln_course_study_reports t SET t.top_date=v_top_date ,t.y=v_year, t.m=v_month, t.d=v_day WHERE t.top_date = topDate AND t.stype=stype;
     END IF;
     IF stype = v_wstype THEN 
          SELECT (to_char((trunc(to_date(topDate,'yyyy-mm-dd'),'iw')),'yyyy')||'-'||to_char((trunc(to_date(topDate,'yyyy-mm-dd'),'iw')),'mm')||'-'||to_char((trunc(to_date(topDate,'yyyy-mm-dd'),'iw')),'dd')) INTO v_top_date FROM dual;
          SELECT to_char((trunc(to_date(topDate,'yyyy-mm-dd'),'iw')),'yyyy') INTO v_year FROM dual;
          SELECT to_char((trunc(to_date(topDate,'yyyy-mm-dd'),'iw')),'mm') INTO v_month FROM dual;
          SELECT to_char((trunc(to_date(topDate,'yyyy-mm-dd'),'iw')),'dd') INTO v_day FROM dual;
          UPDATE mln_course_study_reports t SET t.top_date=v_top_date ,t.y=v_year, t.m=v_month, t.d=v_day WHERE t.top_date = topDate AND t.stype=stype;
     END IF;
     COMMIT;
 END proc_update_top_date;


 
 
 BEGIN
 proc_update_top_date('2015-10-19','week');
 END;