Oracle 闰年判断
//闰年最直接的判断是:能被4整除且不能被100整除，或者能被400整除的年份 create or replace procedure pro_leap_year(year_in
//闰年最直接的判断是:能被4整除且不能被100整除，或者能被400整除的年份
create or replace procedure pro_leap_year(year_in in number)
as
v_mod1 number(4) :=mod(year_in,4);
v_mod2 number(4) :=mod(year_in,100);
v_mod3 number(4) :=mod(year_in,400);
begin
if ((v_mod1=0 and v_mod20) or v_mod3=0) then
dbms_output.put_line(year_in||' is a leap year');
else dbms_output.put_line(year_in||' is not a leap year');
end if;
exception
when others then
dbms_output.put_line(sqlerrm);
end pro_leap_year;
/
SQL> exec pro_leap_year(2010);
2010 is not a leap year
PL/SQL procedure successfully completed
//
SQL> exec pro_leap_year(2000);
2000 is a leap year
PL/SQL procedure successfully completed
//
//关于闰年平年，下面有一个比较全面的解释：
//在公历（格里历）纪年中，，有闰日的年份叫闰年，一般年份365天，闰年为366天。
//由于地球绕太阳运行周期为365天5小时48分46秒（合365.24219天）即一回归年，公历把一年定为365天。
//所余下的时间约为四年累计一天，加在二月里，所以平常年份每年365天，二月为28天，闰年为366天，二月为29天。
//因此，每400年中有97个闰年，闰年在2月末增加一天，闰年366天。
//闰年的计算方法：公元纪年的年数可以被四整除，即为闰年；
//被100整除而不能被400整除为平年；被100整除也可被400整除的为闰年。
//如2000年是闰年，而1900年不是。
//
//我们所关心的是怎么样计算指定的一个年份是否是闰年：
//从上面的描述中，我们知道：
//如果一年中2月份有29天，那么这一年就是闰年，否则是平年
SQL> drop procedure pro_leap_year;
Procedure dropped
//
create or replace procedure pro_leap_year(year_in in number)
as
v_year_in varchar2(10) :=to_char(year_in)||'0229';
v_date date;
begin
//这里将拼接的字符串转换为日期，并赋值给一个日期类型的变量，
//其实就是为了和日期类型进行比较，隐式的比较，比较你输入的年份中2月是否含有29日这一天
v_date :=to_date(v_year_in,'yyyy-mm-dd');
dbms_output.put_line(year_in||' is a leap year');
exception
when others then
dbms_output.put_line(year_in||' is not a leap year');
end pro_leap_year;
/
SQL> exec pro_leap_year(2010);
2010 is not a leap year
PL/SQL procedure successfully completed
//
SQL> exec pro_leap_year(2000);
2000 is a leap year
PL/SQL procedure successfully completed
//
SQL> exec pro_leap_year(2005);
2005 is not a leap year
PL/SQL procedure successfully completed
//
SQL> exec pro_leap_year(2100);
2100 is not a leap year
PL/SQL procedure successfully completed
//
//我们也可以用一条select语句来判断：
SQL> select case
2 when to_char(last_day(to_date(&year||'02','yyyymm')),'dd')='29'
3 then 'the year you input is a leap year'
4 else 'the year you input is not a leap year'
5 end is_leap_year
6 from dual;
Enter value for year: 2050
old 2: when to_char(last_day(to_date(&year||'02','yyyymm')),'dd')='29'
new 2: when to_char(last_day(to_date(2050||'02','yyyymm')),'dd')='29'
IS_LEAP_YEAR
-------------------------------------
the year you input is not a leap year
//
SQL> /
Enter value for year: 2000
old 2: when to_char(last_day(to_date(&year||'02','yyyymm')),'dd')='29'
new 2: when to_char(last_day(to_date(2000||'02','yyyymm')),'dd')='29'
IS_LEAP_YEAR
---------------------------------
the year you input is a leap year
//
SQL> /
Enter value for year: 2012
old 2: when to_char(last_day(to_date(&year||'02','yyyymm')),'dd')='29'
new 2: when to_char(last_day(to_date(2012||'02','yyyymm')),'dd')='29'
IS_LEAP_YEAR
---------------------------------
the year you input is a leap year
//
SQL> /
Enter value for year: 1998
old 2: when to_char(last_day(to_date(&year||'02','yyyymm')),'dd')='29'
new 2: when to_char(last_day(to_date(1998||'02','yyyymm')),'dd')='29'
IS_LEAP_YEAR
-------------------------------------
the year you input is not a leap year
//