ORACLE存储过程循环问题，请高人指点。

Question

我有一张年月对应天数表A，表中就2个字段，记录例如下
month     day
201105    31
201106    30
201107    31
201108    30
............
我要写一个循环存储过程，根据传入的month来判断需要循环30天还是31天次。
我是这样写的，但运行报错，请高人指教
例如传入month是201106则
select day from A where month='201106'
DECLARE   date1   date; 
date1   := to_date( '20110601', 'yyyymmdd'); 
WHILE   date1 <=to_date( '20110630','yyyymmdd')   LOOP --循环里需要执行的
insert into zcrj_queue (queuedate,queuestate,queuecreatetime) values ('20110601','0','20110601')
date1:=date1+1; 
--循环结束
END   LOOP;

Answer 1

scott@TBWORA> CREATE TABLE a(month varchar2(20), day number(18,0));表已创建。scott@TBWORA>
scott@TBWORA> insert into a(month,day) values('201105',31);已创建 1 行。scott@TBWORA> insert into a(month,day) values('201106',31);已创建 1 行。scott@TBWORA> insert into a(month,day) values('201107',31);已创建 1 行。scott@TBWORA> insert into a(month,day) values('201108',31);已创建 1 行。scott@TBWORA>
scott@TBWORA> commit;提交完成。scott@TBWORA> CREATE TABLE b(month varchar2(20), cdate date);表已创建。scott@TBWORA> DECLARE
  2    v_month a.month%type;
  3    cursor cur is SELECT a.month from a;
  4  BEGIN
  5    FOR i in cur loop
  6      v_month := i.month;
  7      insert into b(month,cdate)
  8      select v_month, to_date(v_month,'yyyymm')+level-1
  9      from dual
 10      connect by level<=last_day(to_date(v_month,'yyyymm'))-to_date(v_month,'yyyymm')+1;
 11      commit;
 12    END LOOP;
 13  END;
 14  /PL/SQL 过程已成功完成。scott@TBWORA> select * from b;MONTH                                    CDATE
---------------------------------------- -------------------
201105                                   2011-05-01 00:00:00
201105                                   2011-05-02 00:00:00
201105                                   2011-05-03 00:00:00
201105                                   2011-05-04 00:00:00
201105                                   2011-05-05 00:00:00
201105                                   2011-05-06 00:00:00
201105                                   2011-05-07 00:00:00
201105                                   2011-05-08 00:00:00
201105                                   2011-05-09 00:00:00
201105                                   2011-05-10 00:00:00
201105                                   2011-05-11 00:00:00
201105                                   2011-05-12 00:00:00
201105                                   2011-05-13 00:00:00
201105                                   2011-05-14 00:00:00
201105                                   2011-05-15 00:00:00
201105                                   2011-05-16 00:00:00
201105                                   2011-05-17 00:00:00
201105                                   2011-05-18 00:00:00
201105                                   2011-05-19 00:00:00
201105                                   2011-05-20 00:00:00
201105                                   2011-05-21 00:00:00
201105                                   2011-05-22 00:00:00
201105                                   2011-05-23 00:00:00
201105                                   2011-05-24 00:00:00
201105                                   2011-05-25 00:00:00
201105                                   2011-05-26 00:00:00
201105                                   2011-05-27 00:00:00
201105                                   2011-05-28 00:00:00
201105                                   2011-05-29 00:00:00
201105                                   2011-05-30 00:00:00
201105                                   2011-05-31 00:00:00
201106                                   2011-06-01 00:00:00
201106                                   2011-06-02 00:00:00
201106                                   2011-06-03 00:00:00
201106                                   2011-06-04 00:00:00
...

Answer 2

scott@TBWORA> CREATE TABLE a(month varchar2(20), day number(18,0));表已创建。scott@TBWORA>
scott@TBWORA> insert into a(month,day) values('201105',31);已创建 1 行。scott@TBWORA> insert into a(month,day) values('201106',30);已创建 1 行。scott@TBWORA> insert into a(month,day) values('201107',31);已创建 1 行。scott@TBWORA> insert into a(month,day) values('201108',30);已创建 1 行。scott@TBWORA>
scott@TBWORA> commit;提交完成。scott@TBWORA>
scott@TBWORA> CREATE TABLE b(month varchar2(20), cdate date);表已创建。scott@TBWORA>
scott@TBWORA> DECLARE
  2    v_month a.month%type;
  3    cursor cur is SELECT a.month from a;
  4  BEGIN
  5    FOR i in cur loop
  6      v_month := i.month;
  7      insert into b(month,cdate)
  8      select v_month, to_date(v_month,'yyyymm')+level-1
  9      from dual
 10      connect by level<=last_day(to_date(v_month,'yyyymm'))-to_date(v_month,'yyyymm')+1;
 11      commit;
 12    END LOOP;
 13  END;
 14  /PL/SQL 过程已成功完成。scott@TBWORA>
scott@TBWORA> select * from b
  2  where month='201105'
  3  order by cdate;MONTH                                    CDATE
---------------------------------------- -------------------
201105                                   2011-05-01 00:00:00
201105                                   2011-05-02 00:00:00
201105                                   2011-05-03 00:00:00
201105                                   2011-05-04 00:00:00
201105                                   2011-05-05 00:00:00
201105                                   2011-05-06 00:00:00
201105                                   2011-05-07 00:00:00
201105                                   2011-05-08 00:00:00
201105                                   2011-05-09 00:00:00
201105                                   2011-05-10 00:00:00
201105                                   2011-05-11 00:00:00
201105                                   2011-05-12 00:00:00
201105                                   2011-05-13 00:00:00
201105                                   2011-05-14 00:00:00
201105                                   2011-05-15 00:00:00
201105                                   2011-05-16 00:00:00
201105                                   2011-05-17 00:00:00
201105                                   2011-05-18 00:00:00
201105                                   2011-05-19 00:00:00
201105                                   2011-05-20 00:00:00
201105                                   2011-05-21 00:00:00
201105                                   2011-05-22 00:00:00
201105                                   2011-05-23 00:00:00
201105                                   2011-05-24 00:00:00
201105                                   2011-05-25 00:00:00
201105                                   2011-05-26 00:00:00
201105                                   2011-05-27 00:00:00
201105                                   2011-05-28 00:00:00
201105                                   2011-05-29 00:00:00
201105                                   2011-05-30 00:00:00
201105                                   2011-05-31 00:00:00已选择31行。

Answer 3

--定义参数
datestart date;
dateend date;
--参数赋值   
datestart := to_date(yyyymm || '01', 'yyyy-mm-dd');
dateend := add_months(datestart, 1) - 1;
--循环
while  (datestart <= dateend) 
loop 
insert into zcrj_queue(queuedate, queuestate, queuecreatetime) values(to_char(datestart, 'yyyymmdd'), '0', yyyymm || '01');
datestart := datestart + 1;
end loop; 

Answer 4

一条SQL就可以实现，每个月最后一天有一个函数LAST_DAY