如果只有一行数据的话
SQL> select * from t_date;CDATE1 CDATE2
---------- ----------
2004-01-01 2004-01-10SQL> select cdate1+rownum-1 from all_source,t_date where cdate1+rownum-1<=cdate2;CDATE1+ROW
----------
2004-01-01
2004-01-02
2004-01-03
2004-01-04
2004-01-05
2004-01-06
2004-01-07
2004-01-08
2004-01-09
2004-01-10已选择10行。
SQL> select * from t_date;CDATE1 CDATE2
---------- ----------
2004-01-01 2004-01-10SQL> select cdate1+rownum-1 from all_source,t_date where cdate1+rownum-1<=cdate2;CDATE1+ROW
----------
2004-01-01
2004-01-02
2004-01-03
2004-01-04
2004-01-05
2004-01-06
2004-01-07
2004-01-08
2004-01-09
2004-01-10已选择10行。
as
cursor t_sor is
select col_date1,col_date2 from table_name where id=p_id;
num number;
begin
for v_sor in t_sor loop
num:=v_sor.col_date2-v_sor.col_date1;
for i in 0..num-1 loop
dbms_output.put_line(col_date+i);
end loop;
end loop;
end;
/
CURSOR cur IS SELECT a.ROWID ROWID,a.num num ,a.n_date1 n_date1,a.n_date2 n_date2
FROM tab_test a
WHERE (to_number(TO_char(a.date2,'yyyymmdd'))-to_number(a.to_char(date1,'yyyymmdd')))>1;
n_num NUMBER;
n_date1 NUMBER;
n_date2 NUMBER;
BEGIN
FOR rec IN cur LOOP
n_num := rec.num;
n_date1 := to_number(TO_char(date2,'yyyymmdd'));
n_date2 := to_number(to_char(date1,'yyyymmdd'));
DELETE FROM tab_test WHERE ROWID=rec.ROWID;
FOR n IN n_date1..n_date2
LOOP
INSERT INTO tab_test VALUES(to_date(n,'yyyymmdd'),to_date(n,'yyyymmdd'),n_num);
COMMIT;
END LOOP;
END LOOP;
END;
/我也写了一个,可以参考一下
TAB_TEST原先是如下记录
DATE1 DATE2 NUM
----------- ----------- ----------
2003-5-2 0: 2003-5-11 0 50
2003-4-30 0 2003-4-30 0 50执行后如下
DATE1 DATE2 NUM
----------- ----------- ----------
2003-4-30 0 2003-4-30 0 50
2003-5-2 2003-5-2 50
2003-5-3 2003-5-3 50
2003-5-4 2003-5-4 50
2003-5-5 2003-5-5 50
2003-5-6 2003-5-6 50
2003-5-7 2003-5-7 50
2003-5-8 2003-5-8 50
2003-5-9 2003-5-9 50
2003-5-10 2003-5-10 50
2003-5-11 2003-5-11 50
CURSOR cur IS SELECT a.ROWID id,a.num num ,a.date1 n_date1,a.date2 n_date2
FROM tab_test a
WHERE (to_number(TO_char(a.date2,'yyyymmdd'))-to_number(to_char(a.date1,'yyyymmdd')))>1;
n_num NUMBER;
n_date1 NUMBER;
n_date2 NUMBER;
BEGIN
FOR rec IN cur LOOP
n_num := rec.num;
n_date1 := to_number(TO_char(rec.n_date1,'yyyymmdd'));
n_date2 := to_number(to_char(rec.n_date2,'yyyymmdd'));
DELETE FROM tab_test WHERE ROWID=rec.ID;
FOR n IN n_date1..n_date2
LOOP
INSERT INTO tab_test VALUES(to_date(n,'yyyymmdd'),to_date(n,'yyyymmdd'),n_num);
COMMIT;
END LOOP;
END LOOP;
END;
呵呵,游标的字段名有问题,改正