表名: testdate字段
id 主键 varchar2
dtime date现在是空的,求循环插入此表中当前日期到明年的今天记录的语句
即:
2009-11-27
2009-11-28
2009-11-29
.....
2010-11-26
2010-11-27
id 主键 varchar2
dtime date现在是空的,求循环插入此表中当前日期到明年的今天记录的语句
即:
2009-11-27
2009-11-28
2009-11-29
.....
2010-11-26
2010-11-27
declare
dbegindate date;
denddate date;
begin
dbegindate := sysdate;
denddate := add_months(dbegindate, 12);
while (dbegindate <= denddate)
loop
insert into testdate values(1, dbegindate);
dbegindate := dbegindate + 1;
end loop;
commit;
end;
insert into testdate
select rownum,trunc(sysdate)-1+rownum from dual
connect by rownum<=add_months(sysdate,12)-sysdate+1;如果10g以上,可以变通下
insert into testdate
select rownum,trunc(sysdate)-1+rownum from all_objects
where rownum<=add_months(sysdate,12)-sysdate+1;
INSERT INTO testdate
SELECT LEVEL, to_date(to_char(SYSDATE + LEVEL - 1, 'yyyy-mm-dd'),'yyyy-mm-dd')
FROM dual
CONNECT BY to_char(SYSDATE + LEVEL - 1, 'yyyy-mm-dd') <=
to_char(add_months(SYSDATE, 12), 'yyyy-mm-dd');