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.