oracle 日历

　　今天看到qq群里有人在用sql写日历，自己也写了个，作下记录；

 

 create or replace procedure get_Calender(choosedate in date) is
/*
       功能：   根据入参日期打印当月的日历（星期天在第一天）
*/
  cursor cur_Calender is
    select nvl(max(decode(wk, 1, calender, null)), '--') as "SUN",
           nvl(max(decode(wk, 2, calender, null)), '--') as "MON",
           nvl(max(decode(wk, 3, calender, null)), '--') as "TUE",
           nvl(max(decode(wk, 4, calender, null)), '--') as "WEN",
           nvl(max(decode(wk, 5, calender, null)), '--') as "THU",
           nvl(max(decode(wk, 6, calender, null)), '--') as "FRI",
           nvl(max(decode(wk, 7, calender, null)), '--') as "SAT"
      from (select calender, wk, decode(wk, 1, wn + 1, wn) as wn --要把星期日放在第一位，让星期日变为下一行的组
              from (select to_char(trunc(choosedate, 'mm') + rownum - 1, 'dd') as calender,
                           to_char(trunc(choosedate, 'mm') + rownum - 1, 'd') as wk,
                           case --因为to_char(iw)在一年年初的时候会得到上一年的星期数，如2011-01-01是2010年的第52个星期，作了处理
                             when rownum <=
                                  (7 - (trunc(choosedate, 'mm') -
                                  trunc(trunc(choosedate, 'mm'), 'iw'))) then
                              to_char(to_char(trunc(choosedate, 'mm') + rownum - 1 + 7,
                                              'iw') - 1)
                             else
                              to_char(trunc(choosedate, 'mm') + rownum - 1,
                                      'iw')
                           end as wn
                      from dual
                    connect by rownum <
                               last_day(choosedate) - trunc(choosedate, 'mm') + 1))
     group by wn
     order by wn;
begin
  dbms_output.put_line(to_char(choosedate, 'yyyy-mm') || '''s  Calender' ||CHR(13));
  dbms_output.put_line('SUN' || CHR(9) || 'MON' || CHR(9) || 'TUE' ||
                       CHR(9) || 'WEN' || CHR(9) || 'THU' || CHR(9) ||
                       'FRI' || CHR(9) || 'SAT');
  for x in cur_Calender loop
    dbms_output.put_line(x.SUN || CHR(9) || x.MON || CHR(9) || x.TUE ||
                         CHR(9) || x.WEN || CHR(9) || x.THU || CHR(9) ||
                         x.FRI || CHR(9) || x.SAT);
  end loop;
end get_Calender;