Create a Function Library - Decrease Intellectual Angst and Increase Efficiency

Just like your typical code monkey, I'm constantly looking for ways to reduce my required intellectual output while increasing my overall efficiency. For example, right now, I'm walking on a treadmill, listening to my wedding rehearsal dinner play list to make sure it's all the same feel/volume (aka sans cuss words:)), and updating a blog that I haven't written on in 4 months (I'm very sorry).

As far as coding goes, I have been doing a lot of work with schools who are moving with increasing speed and fervor toward data-based decision making. This requires a much more analytical approach to reporting than your typical "students by major" list, which, in turn requires more database resources and involves a great deal more "logic" with regard to your institution's business rules (often the hardest part). To help aid me in this transition, I have started to compile a library of functions. For the most part, the functions are relatively simple, I just add one whenever I need to answer a new question. For example, I was recently asked by a client what percentage of their new freshmen this fall enrolled full time. My old "hack" self would have sat down at a computer for a couple of hours and written the necessary query to gather that information. BUT WAIT! This time, I wrote a database function that took as parameters term, student type, and time status. Now, to get my pct, this is the query:

select f_enrl_count('201010','N','FT') FT_NewFreshmen,
          f_enrl_count('201010','N','%') Total_NewFreshmen,
          f_enrl_count('201010','N','FT')/
          f_enrl_count('201010','N','%')*100 Pct_New_FT
  from dual;

It's now that simple!  Not only is the query simplified, the overall results come back quicker because I am using pre-compiled code and I can use it from any query tool that connects to this database!  Functions can be combined too.  For example, if you're asked the percentage question a lot and you're tired of writing the query above, you could encapsulate that code in a function called f_enrl_pct that would look like this:

create or replace function
f_enrl_pct(in_term varchar2, in_styp varchar2, in_time_status varchar2)
return number as

 v_output number;

begin

select round(f_enrl_count(in_term,in_styp,in_time_status)/
                    f_enrl_count(in_term,in_styp,'%')*100,2) into v_output
  from dual;

return v_output;
end;

Now when you want that percentage, all you have to do is write the following:

select f_enrl_pct('201010','N','FT') from dual

and WALAH, you're done!  How sweet is that.  Now, you're probably chomping at the bit for the code behind the enrollment function.  Unfortunately, because of the variety of architectures (Baseline, ODS, EDW, home grown data marts), I would rather not post the code here.  However, if you're really interested post a comment and I'll think about sharing:).  Also, if you have developed some neat functions and would like to share, I welcome your comments there as well!  Anyway, the moral of today's story is two fold:

1. Writing a blog entry while on the treadmill takes way longer than it's worth
2. Creating a function library may be the smartest thing you ever do as a sql programmer

As a side note, functions can be used for non analytical reports as well! More on that later...:)

Happy Hunting!!!