jfw.id.au

At work we are all pretty busy with the project known as ‘Phase 2′. A project that the sales and marketing team had decided on a deadline before we even knew that it existed. This should be the last, or at the worst next to last time that something like this happens on such a massive project.

I have no illusions about it not happening again, however we can dream can we not?

Any way, the point of this post is simply to marvel at a lovely way to do some PL/SQL to build a bit of a data layer for the extensions that we are adding. I’ve not done a lot of PL/SQL in the past, and the stuff that I have done is fairly basic so I was quite impressed with the simplicity and functionality of this little block:

PROCEDURE getMenu
   (pDetails    OUT grcDetails,
    pMenuID   IN Menu.MENUID%TYPE DEFAULT NULL) IS
BEGIN
   OPEN pDetails FOR
   SELECT *
   FROM MENU
   WHERE MENUID = DECODE(pMenuID, 0, MENUID, NULL, MENUID, pMenuID);
END getMenu;

All the smarts of the statement are in the DECODE. For those that don’t recognise it, this is a small function written in PL/SQL for Oracle. Basically this function allows us to load all of the records in the MENU table, or the one specified by the pMenuID if it is supplied.

I was just impressed with it’s power and simplicity and had to share.