What is a quick way of working out if a year happens to be a leap year?
Here’s my first attempt
SQL> create or replace
2 function f1(y number) return boolean is
3 x date;
4 begin
5 x := to_date('2902'||y,'ddmmyyyy');
6 return true;
7 exception
8 when others then return false;
9 end;
10 /
Function created.
However, whatever the context, relying on an exception handler to determine a result can often have performance consequences. Let’s give that routine a hammering
SQL> set timing on
SQL> declare
2 l boolean;
3 begin
4 for i in 1 .. 1000 loop
5 for j in 1600 .. 2300 loop
6 l := f1(j);
7 end loop;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:11.28
If possible, avoiding the “break and jump to an exception” will be a more efficient way of doing things in PL/SQL. Here’s an alterative version taking advantage of the LAST_DAY function
SQL> create or replace
2 function f2(y number) return boolean is
3 begin
4 return to_char(last_day(to_date('0102'||y,'ddmmyyyy')),'DD') = '29';
5 end;
6 /
Function created.
SQL> declare
2 l boolean;
3 begin
4 for i in 1 .. 1000 loop
5 for j in 1600 .. 2300 loop
6 l := f2(j);
7 end loop;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.00
Then again, sometimes its best to use good old simple mathematics rather than date functions 😁
SQL>
SQL> create or replace
2 function f3(y number) return boolean is
3 begin
4 return mod(y,4)=0 and ( mod(y,100) != 0 or mod(y,400) = 0 );
5 end;
6 /
Function created.
SQL> set timing on
SQL> declare
2 l boolean;
3 begin
4 for i in 1 .. 1000 loop
5 for j in 1600 .. 2300 loop
6 l := f3(j);
7 end loop;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.17
SQL>
Moral of the story
1 – EXCEPTIONS are designed for exceptional cases, not the norm
2 – If you are calculating leap years 1000s of times per second, I need to ask what your business requirement is. 😁
Ho Ho Ho! Merry Christmas!
Learning is not a spectator sport 
Got some thoughts? Leave a comment