So it is always good to be reminded:
Do as much work as you can in SQL, and then finish up in PL/SQL.
Where "finish up" means do whatever is appropriate within the database, and then make that available to whatever language is being used to write the UI!
A recent quiz on the PL/SQL Challenge explored this topic, and I offer it below as a learning exercise via blog post.
Suppose I have a table and dataset as follows:
CREATE TABLE plch_animals ( animal_id INTEGER PRIMARY KEY, animal_name VARCHAR2 (100) ) / BEGIN INSERT INTO plch_animals (animal_id, animal_name) VALUES (1, 'Bonobo'); INSERT INTO plch_animals (animal_id, animal_name) VALUES (2, 'Cockatoo'); INSERT INTO plch_animals (animal_id, animal_name) VALUES (3, 'Spider'); COMMIT; END; /
And I need to write a program that produces the following output:
Animals in Alphabetical Order Bonobo Cockatoo Spider
Let's take a look at some ways to achieve this objective (some of them distinctly sub-optimal, holding off the best till last):
#1. Accidental Success Through Bad Test Data
DECLARE l_count INTEGER; l_name plch_animals.animal_name%TYPE; BEGIN DBMS_OUTPUT.put_line ('Animals in Alphabetical Order'); SELECT COUNT (*) INTO l_count FROM plch_animals; FOR indx IN 1 .. l_count LOOP SELECT animal_name INTO l_name FROM plch_animals WHERE animal_id = indx; DBMS_OUTPUT.put_line (l_name); END LOOP; END; /
You see the desired output, but only because the animal IDs happen to ascend in exactly the same sequence as an alphabetical ordering of the animal names.(see line in blue).
You certainly do not want to ever assume this will be the case with real data. Even if you look at the data today and can confirm that pattern. That's just for today. A warning sign that this code is problematic is the SELECT COUNT(*).
You should generally not need to do a separate query just to control the number of iterations of a loop. A cursor FOR loop will usually take care of that for you.
#2. Too Much SQL, Not Enough Data
DECLARE TYPE animal_ids_t IS TABLE OF plch_animals.animal_id%TYPE; l_animal_ids animal_ids_t; l_name plch_animals.animal_name%TYPE; BEGIN DBMS_OUTPUT.put_line ('Animals in Alphabetical Order'); SELECT animal_id BULK COLLECT INTO l_animal_ids FROM plch_animals; FOR indx IN 1 .. l_animal_ids.COUNT LOOP SELECT animal_name INTO l_name FROM plch_animals WHERE animal_id = indx; DBMS_OUTPUT.put_line (l_name); END LOOP; END; /
Oh my. This choice is similar to #1, in terms of the basic, flawed assumptions (the animal IDs happen to ascend in exactly the same sequence as an alphabetical ordering of the animal names).
But it's even worse because I fetch all the animal IDs via BULK COLLECT into an array, then when I loop through the array, I execute a single row fetch to get the name.
And then it's even worse worse because the only reason this works is that the ID values happen to align with names in alphabetical order. So the results "look" OK when the test is run with this pathetic set of test data, but in the real world? Ugh.
That's a waste of code, PGA memory and CPU cycles.
#3. Having Too Much Fun With Collections
DECLARE TYPE animal_ids_t IS TABLE OF plch_animals.animal_id%TYPE INDEX BY plch_animals.animal_name%TYPE; l_animal_ids animal_ids_t; l_index plch_animals.animal_name%TYPE; l_name plch_animals.animal_name%TYPE; BEGIN DBMS_OUTPUT.put_line ('Animals in Alphabetical Order'); FOR rec IN ( SELECT * FROM plch_animals ORDER BY animal_name DESC) LOOP l_animal_ids (rec.animal_name) := rec.animal_id; END LOOP; l_index := l_animal_ids.FIRST; WHILE l_index IS NOT NULL LOOP DBMS_OUTPUT.put_line (l_index); l_index := l_animal_ids.NEXT (l_index); END LOOP; END; /
I very much enjoy collections (PL/SQL's version of arrays) and use them all over my code. And I especially appreciate string-indexed associative arrays, like I use above. But, really, there is such a thing as too much.
I use a cursor FOR loop to grab the rows from the animals table, and load the ID into my array, using the name as the index value. Then I use a WHILE loop to iterate through that array, simply displaying the index value. I never even use the ID!
It gets the job done, and sure performance will be fine unless you are looking at a very large volume, but this code is downright bewildering.
#4. SQL Simple
SELECT animal_name FROM plch_animals ORDER BY animal_name
Doesn't get much simpler than that. And if you need it inside PL/SQL....
#5. PL/SQL Simple
BEGIN DBMS_OUTPUT.put_line ('Animals in Alphabetical Order'); FOR rec IN (SELECT animal_name FROM plch_animals ORDER BY animal_name) LOOP DBMS_OUTPUT.put_line (rec.animal_name); END LOOP; END;
1. Let SQL do the heavy-lifting, as much as possible (not that there was anything very "heavy" to lift in this exercise!)
2. Don't over-complicate matters.
3. Make sure your test data has enough volume and variety to truly exercise your algorithm.
4. If you find yourself thinking "Does it have to be this complicated?", almost certainly the answer is a resounding "No!" and you should take a step back, challenge your assumptions, and see how you can simplify your code.
And don't forget:
a. Follow me on Twitter: @sfonplsql
b. Subscribe to my YouTube channel: PracticallyPerfectPLSQL
c. Check out the PL/SQL home page: oracle.com/plsql