Tuesday, May 9, 2017

Use records to improve readability and flexibility of your code

Suppose I've created a table to keep track of hominids:

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?

5 comments:

  1. Hello Steven,
    I also find it much clearer if you use such "constructor" functions.
    But you have forgotten to declare the "hominids_info_rt" record type (SUBTYPE hominids_info_rt IS plch_hominids%ROWTYPE;).
    And if you only have one position where such initializations are needed you even don't need to create a function for the initialization but can include it in the anonymous block:
    ----- start of code -----
    DECLARE
    SUBTYPE hominids_info_rt IS plch_hominids%ROWTYPE;

    FUNCTION new_hominid (
    name_in IN plch_hominids.hominid_name%TYPE,
    home_territory_in IN plch_hominids.home_territory%TYPE,
    brain_size_cm_in IN plch_hominids.brain_size_cm%TYPE)
    RETURN hominids_info_rt;

    l_bonobo hominids_info_rt := new_hominid ('Bonobo', NULL, 500);
    l_gorilla hominids_info_rt := new_hominid ('Gorilla', NULL, 750);
    l_neanderthal hominids_info_rt := new_hominid ('Neanderthal', NULL, 1800);

    FUNCTION new_hominid (
    name_in IN plch_hominids.hominid_name%TYPE,
    home_territory_in IN plch_hominids.home_territory%TYPE,
    brain_size_cm_in IN plch_hominids.brain_size_cm%TYPE)
    RETURN hominids_info_rt
    IS
    l_return plch_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 new_hominid;

    BEGIN
    DBMS_OUTPUT.put_line (l_neanderthal.brain_size_cm);
    END;
    /
    ----- end of code -----

    Kind regards, Niels Hecker

    ReplyDelete
    Replies
    1. Shouldn't l_return in new_hominid() be declared as a hominids_info_rt variable instead of hominids%ROWTYPE?

      Delete
    2. :-) Hard to keep it all straight. Thank the heavens for compilers!

      Delete
  2. Argh. Thanks, Niels. Actually, that was a copy/paste mistake. Originally (as in the original quiz I took this from) I created a record type with just two of the columns, but then decided not to use that here, for simplicity's sake. I will correct that typo, but thanks for the code you provided, plus the great use of a forward declaration. :-)

    ReplyDelete
  3. Anchored types one of best features of PL/SQL, amazing idea. But i think we shold always, and always store this data in tables, (hominids_type) with all default valuses.:)

    ReplyDelete