CREATE TABLE hominids ( hominid_name VARCHAR2 (100), home_territory VARCHAR2 (100), brain_size_cm INTEGER ) /
I might then write code like this:
DECLARE l_b_hominid_name VARCHAR2 (100) := 'Bonobo'; l_b_brain_size_cm INTEGER := 500; l_g_hominid_name VARCHAR2 (100) := 'Gorilla'; l_g_brain_size_cm INTEGER := 750; l_n_hominid_name VARCHAR2 (100) := 'Neanderthal'; l_n_brain_size_cm INTEGER := 1800;
What do you think?
I find the little voice of Relational Theory inside my head rebelling.
"All that repetition! All that denormalization! All that typing (or copy-pasting, which is even worse)!"
Surely if I should avoid having redundant data in rows of my tables, I should avoid redundant code, too?
Yes, I should. I don't like to see long lists of declarations, especially when the names are very similar and follow a pattern.
A fine way to avoid this kind of code is to use record types to group related variables together within a named context: the record variable. So I could rewrite the declaration section above to:
DECLARE l_bonobo hominids%ROWTYPE; l_gorilla hominids%ROWTYPE; l_neanderthal hominids%ROWTYPE; BEGIN l_bonobo.hominid_name := 'Bonobo'; l_bonobo.brain_size_cm := 500; l_gorilla.hominid_name := 'Gorilla'; l_gorilla.brain_size_cm := 750; l_neanderthal.hominid_name := 'Neanderthal'; l_neanderthal.brain_size_cm := 1800;
Notice that I now move the initializations of the variable (well, record.field) values to the executable section. That's because PL/SQL does not yet offer a built-in function (in object-oriented lingo, a constructor method) for record types.
So I no longer have six declarations - just three. And, of course, if my table had 15 columns and I had declared a separate variable for each of those, I would have been able to shrink down my declarations from 45 to 3!
Still, I don't like putting all that initialization code in the main body of my block. How about if I create my own "record constructor" function, and then call that:
CREATE OR REPLACE FUNCTION new_hominid ( name_in IN hominids.hominid_name%TYPE, home_territory_in IN hominids.home_territory%TYPE, brain_size_cm_in IN hominids.brain_size_cm%TYPE) RETURN hominids%ROWTYPE IS l_return hominids%ROWTYPE; BEGIN l_return.hominid_name := name_in; l_return.home_territory := home_territory_in; l_return.brain_size_cm := brain_size_cm_in; RETURN l_return; END; / DECLARE l_bonobo hominids%ROWTYPE := new_hominid ('Bonobo', NULL, 500); l_gorilla hominids%ROWTYPE := new_hominid ('Gorilla', NULL, 750); l_neanderthal hominids%ROWTYPE := new_hominid ('Neanderthal', NULL, 1800); BEGIN DBMS_OUTPUT.put_line (l_neanderthal.brain_size_cm); END; /
Ahhhhh. Just three declarations. Default values assigned in the declaration section. All the details of the assignments hidden away behind the function header.
And when I add a new column to the table (or generally a field to a record), I can add a parameter to my new_hominid function, along with a default value of NULL, and none of my existing code needs to change (unless that new column or field is needed).
Yes, I like that better.
How about you?