Well I feel that I’ve been very accomodating to the technically-impaired (so far as my past blogs are concerned). Time to brake the mold.
I spent the last 2-3 days fighting with the SQL function DECODE();
It’s a jolly old boy with lots o’ potential.
The thing that I love about PL/SQL is that I can use the power and flexiblity of the procedural language (such as control structures [i.e. if, else, end if, for loop …] and compilation) while still having EASY and quick access to the Database.
Now although I can embed SQL & HTML into the PL/SQL (and Javascript into the HTML), the SQL itself has it’s own set of rules. For instance, you can’t say “if this, then that, else if this, then that, else if this, then that, etc….”. So sometimes you have to make really long loopy code or multiple procedures to make up for this limitation.
Well today I have gained some most-excellent experience with the SQL function DECODE(); Decode() is the closest thing we have to ‘if’ logic, so it CAN be very powerful.
Decode looks like this:
DECODE ( [variable],
[possibility #1], ‘return value for #1’,
[possibility #2]’, ‘return value for #2’,
[possibility #3]’, ‘return value for #3’,
[possibility #4]’, ‘return value for #4’,
[default if none of the above is found]
)
Do take care to substitute appropriate values and remove all: [ ] ‘
Nerdy, eh?
Anyway, so the cool thing about this is that we USED to make multiple procedures/duplicate LOTS of code if we wanted to … let’s say … order the results on different stuff (and we use a passed in variable to tell us what).
So, instead of two procedures like:
procedure print1 is
begin
for a in (select * from boots order by color) loop
htp.print(a.boot||’\n’);
end loop;
end print1;
procedure print2 is
begin
for a in (select * from boots order by size) loop
htp.print(a.boot||’\n’);
end loop;
end print2;
… now we can make one procedure:
procedure print (porder varchar2) is
begin
for a in (select * from boots
order by decode(porder,
color, boot_color,
size, boot_size,
price
)
) loop
htp.print(a.boot||’\n’);
end loop;
end print;
[I indented it, but HTML isn’t that smart, so it looks ugly, sorry.]
So, you pass porder into the procedure. It the value is ‘color’, then decode() returns boot_color and the list gets sorted by that. If the value is ‘size’, then book_size is returned and used for the order by. However, if porder is neither color, nor size, then decode() returns price and the data is ordered by that. Clean, reasonably easy, fact.
This is obviously a dumb example because of the many technical issues – however I hope you understand the point. Basically this code gives us the ability to order the same cursor by whatever the heck you want to order by.
I’ve utilized this ability in RW2 (Redwolf v2). On the large reports, you have the ability to sort by firstname, lastname or location (ascending or descending). This ‘dynamic order by’ also allowed me to consolidate 2-5 reporting procedures down to 1 or 2 – thereby simplifying the maintenance of my code, and standardizing the report format(s).
There are some darn tricky things about the decode statement, and you can utilize multiple ordered decode’s for some interesting and useful results. Please come ask if you have questions about DECODE(); It can be used in a WHERE clause and therefore it’s relevance to SQL is hugely increased.
PL/SQL – it’s code! Hurray code!
