SQL 如何在 Oracle 中填充日历表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8374959/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to populate calendar table in Oracle?
提问by terrific
I want to maintain a calender table in Oracle DB which I want to populate with all the days of the year starting from 2011 to 2013 (it may be till any year). How can I do that?
我想在 Oracle DB 中维护一个日历表，我想用从 2011 年到 2013 年（可能一直到任何一年）开始的一年中的所有日子进行填充。我怎样才能做到这一点？
Consider my DB table has columns and example dataset is:
考虑我的数据库表有列，示例数据集是：
S.No Cal_Dt DayName
1 01-01-2011 Monday
2 02-01-2011 Tuesday
3 03-01-2011 Wednesday
and so on.
等等。
I am more concerned with the Cal_Dt only here (DayName is optional).
我只在这里更关心 Cal_Dt（DayName 是可选的）。
回答by Alessandro Rossi
This is a simple and easy way to do it
这是一个简单易行的方法
with calendar as (
select :startdate + rownum - 1 as day
from dual
connect by rownum < :enddate - :startdate
)
select rownum as "S.No", to_date(day,'dd_mm_yyyy') as "Cal_Dt", to_char(day,'day') as "DayName"
from calendar
回答by Ryan
with calendar as (
select rownum - 1 as daynum
from dual
connect by rownum < sysdate - to_date('1-jan-2010') + 1
)
select to_date('1-jan-2010') + daynum as monthdate
from calendar
;
回答by Ben
declare
v_date date := to_date('20110101','yyyymmdd');
begin
while v_date < sysdate + 720 loop
insert into calender
values ( v_date, to_char(v_date,'DAY'));
v_date := v_date + 1;
end loop;
commit;
end;
/
This is not best practice and you should use Allesandro Rossi's solution. This may onlybe useful if you're using Oracle 9i or earlier and populating a large table.
这不是最佳实践，您应该使用Allesandro Rossi 的解决方案。这可能仅在您使用 Oracle 9i 或更早版本并填充大表时才有用。
