Oracle 函数返回calendar

转自:[url]http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=515&lngWId=5[/url] --**************************************-- -- Name: calproc.sql-- Description:Creates a 12 month calend-- ar for any given year-- By: Sergio Rueda---- Inputs:year---- Returns:12 month calendar---- Assumes:nothing----This code is copyrighted and has-- limited warranties.Please see http://-- www.Planet-Source-Code.com/vb/scripts/Sh-- owCode.asp?txtCodeId=515&lngWId=5--for details.--**************************************-- UNIX korn shell sample calling program:### creates a calendar FOR a given year## Input:Year## Output: 12 month calendar# stored IN a FILE called CALENDAR.year###clear echo " "echo "Now creating file: calendar.$1"echo " "echo "SET serveroutput ON size 20000 format WORD_WRAPPEDEXECUTE srmcal($1);" | sqlplus -s gemsprod/gemsprod > calendar.$1==================================================PL/SQL PROCEDURE TO CREATE THE CALENDAR-- Written by Sergio Rueda.-- 07/10/2002---- Procedure to create a 12 month calend-- ar for any given year.----CREATE or replace PROCEDURE srmcal(ano IN number) ASldom number(02);line varchar2(26);mname varchar(10);wday number(02); begin FOR mes IN 1 .. 12 loop SELECT substr(last_day(to_date('01-'||TO_CHAR(mes)||'-'||TO_CHAR(ano),'DD-MM-YYYY')),1,2) INTO ldom FROM dual; SELECT to_char(to_date('01-'||TO_CHAR(mes)||'-'||TO_CHAR(ano),'DD-MM-YYYY'), 'Month') INTO mname FROM dual; SELECT to_number( to_char(to_date('01-'||TO_CHAR(mes)||'-'||TO_CHAR(ano),'DD-MM-YYYY'),'D')) INTO wday FROM dual; -- -- dbms_output.put_line('.'); dbms_output.put_line('.'); dbms_output.put_line('. '||mname||' -- '|| ano); dbms_output.put_line('.'); dbms_output.put_line('+ SUNMONTUEWEDTHUFRISAT +'); dbms_output.put_line('+------------------------------------------------+'); -- -- FOR dia IN 1 .. wday - 1 loop dbms_output.put( '|......'); END loop;---- FOR dia IN 1 .. ldom loop IF ( dia < 10) THEN line := '|'||dia||' '; ELSE line := '| '||dia||' '; END if;----IF mod(dia + (wday - 1),7) = 0 THENdbms_output.put(line || '|');dbms_output.new_line;dbms_output.put_line('+------+------+------+------+------+------+------+ ');elsedbms_output.put(line);END if;END loop;dbms_output.new_line;dbms_output.put_line('+------+------+------+------+------+------+------+ '); END loop;end;///执行结果:SQL> set serveroutput ON size 20000 format WORD_WRAPPED ;SQL> exec srmcal(2009);... January -- 2009.+ SUNMONTUEWEDTHUFRISAT ++------------------------------------------------+|......|......|......|......|1 |2 |3 |+------+------+------+------+------+------+------+|4 |5 |6 |7 |8 |9 | 10 |+------+------+------+------+------+------+------+| 11 | 12 | 13 | 14 | 15 | 16 | 17 |+------+------+------+------+------+------+------+| 18 | 19 | 20 | 21 | 22 | 23 | 24 |+------+------+------+------+------+------+------+| 25 | 26 | 27 | 28 | 29 | 30 | 31 |+------+------+------+------+------+------+------++------+------+------+------+------+------+------+... February -- 2009.+ SUNMONTUEWEDTHUFRISAT ++------------------------------------------------+|1 |2 |3 |4 |5 |6 |7 |+------+------+------+------+------+------+------+|8 |9 | 10 | 11 | 12 | 13 | 14 |+------+------+------+------+------+------+------+| 15 | 16 | 17 | 18 | 19 | 20 | 21 |+------+------+------+------+------+------+------+| 22 | 23 | 24 | 25 | 26 | 27 | 28 |+------+------+------+------+------+------+------++------+------+------+------+------+------+------+... March -- 2009.+ SUNMONTUEWEDTHUFRISAT ++------------------------------------------------+|1 |2 |3 |4 |5 |6 |7 |+------+------+------+------+------+------+------+|8 |9 | 10 | 11 | 12 | 13 | 14 |+------+------+------+------+------+------+------+| 15 | 16 | 17 | 18 | 19 | 20 | 21 |+------+------+------+------+------+------+------+| 22 | 23 | 24 | 25 | 26 | 27 | 28 |+------+------+------+------+------+------+------+| 29 | 30 | 31+------+------+------+------+------+------+------+... April -- 2009.+ SUNMONTUEWEDTHUFRISAT ++------------------------------------------------+|......|......|......|1 |2 |3 |4 |+------+------+------+------+------+------+------+|5 |6 |7 |8 |9 | 10 | 11 |+------+------+------+------+------+------+------+| 12 | 13 | 14 | 15 | 16 | 17 | 18 |+------+------+------+------+------+------+------+| 19 | 20 | 21 | 22 | 23 | 24 | 25 |+------+------+------+------+------+------+------+| 26 | 27 | 28 | 29 | 30+------+------+------+------+------+------+------+... May -- 2009.+ SUNMONTUEWEDTHUFRISAT ++------------------------------------------------+|......|......|......|......|......|1 |2 |+------+------+------+------+------+------+------+|3 |4 |5 |6 |7 |8 |9 |+------+------+------+------+------+------+------+| 10 | 11 | 12 | 13 | 14 | 15 | 16 |+------+------+------+------+------+------+------+| 17 | 18 | 19 | 20 | 21 | 22 | 23 |+------+------+------+------+------+------+------+| 24 | 25 | 26 | 27 | 28 | 29 | 30 |+------+------+------+------+------+------+------+| 31+------+------+------+------+------+------+------+... June -- 2009.+ SUNMONTUEWEDTHUFRISAT ++------------------------------------------------+|......|1 |2 |3 |4 |5 |6 |+------+------+------+------+------+------+------+|7 |8 |9 | 10 | 11 | 12 | 13 |+------+------+------+------+------+------+------+| 14 | 15 | 16 | 17 | 18 | 19 | 20 |+------+------+------+------+------+------+------+| 21 | 22 | 23 | 24 | 25 | 26 | 27 |+------+------+------+------+------+------+------+| 28 | 29 | 30+------+------+------+------+------+------+------+... July -- 2009.+ SUNMONTUEWEDTHUFRISAT ++------------------------------------------------+|......|......|......|1 |2 |3 |4 |+------+------+------+------+------+------+------+|5 |6 |7 |8 |9 | 10 | 11 |+------+------+------+------+------+------+------+| 12 | 13 | 14 | 15 | 16 | 17 | 18 |+------+------+------+------+------+------+------+| 19 | 20 | 21 | 22 | 23 | 24 | 25 |+------+------+------+------+------+------+------+| 26 | 27 | 28 | 29 | 30 | 31+------+------+------+------+------+------+------+... August -- 2009.+ SUNMONTUEWEDTHUFRISAT ++------------------------------------------------+|......|......|......|......|......|......|1 |+------+------+------+------+------+------+------+|2 |3 |4 |5 |6 |7 |8 |+------+------+------+------+------+------+------+|9 | 10 | 11 | 12 | 13 | 14 | 15 |+------+------+------+------+------+------+------+| 16 | 17 | 18 | 19 | 20 | 21 | 22 |+------+------+------+------+------+------+------+| 23 | 24 | 25 | 26 | 27 | 28 | 29 |+------+------+------+------+------+------+------+| 30 | 31+------+------+------+------+------+------+------+... September -- 2009.+ SUNMONTUEWEDTHUFRISAT ++------------------------------------------------+|......|......|1 |2 |3 |4 |5 |+------+------+------+------+------+------+------+|6 |7 |8 |9 | 10 | 11 | 12 |+------+------+------+------+------+------+------+| 13 | 14 | 15 | 16 | 17 | 18 | 19 |+------+------+------+------+------+------+------+| 20 | 21 | 22 | 23 | 24 | 25 | 26 |+------+------+------+------+------+------+------+| 27 | 28 | 29 | 30+------+------+------+------+------+------+------+... October -- 2009.+ SUNMONTUEWEDTHUFRISAT ++------------------------------------------------+|......|......|......|......|1 |2 |3 |+------+------+------+------+------+------+------+|4 |5 |6 |7 |8 |9 | 10 |+------+------+------+------+------+------+------+| 11 | 12 | 13 | 14 | 15 | 16 | 17 |+------+------+------+------+------+------+------+| 18 | 19 | 20 | 21 | 22 | 23 | 24 |+------+------+------+------+------+------+------+| 25 | 26 | 27 | 28 | 29 | 30 | 31 |+------+------+------+------+------+------+------++------+------+------+------+------+------+------+... November -- 2009.+ SUNMONTUEWEDTHUFRISAT ++------------------------------------------------+|1 |2 |3 |4 |5 |6 |7 |+------+------+------+------+------+------+------+|8 |9 | 10 | 11 | 12 | 13 | 14 |+------+------+------+------+------+------+------+| 15 | 16 | 17 | 18 | 19 | 20 | 21 |+------+------+------+------+------+------+------+| 22 | 23 | 24 | 25 | 26 | 27 | 28 |+------+------+------+------+------+------+------+| 29 | 30+------+------+------+------+------+------+------+... December -- 2009.+ SUNMONTUEWEDTHUFRISAT ++------------------------------------------------+|......|......|1 |2 |3 |4 |5 |+------+------+------+------+------+------+------+|6 |7 |8 |9 | 10 | 11 | 12 |+------+------+------+------+------+------+------+| 13 | 14 | 15 | 16 | 17 | 18 | 19 |+------+------+------+------+------+------+------+| 20 | 21 | 22 | 23 | 24 | 25 | 26 |+------+------+------+------+------+------+------+| 27 | 28 | 29 | 30 | 31+------+------+------+------+------+------+------+PL/SQL procedure successfully completed. 好玩! 相关资源：oracle 在报表里 实现一段时间的按正常日历展示期间的sql