What`s Code Without Variables? - There is more to data than columns in a table
(Page 2 of 4 )
Use SUBTYPEs to declare program-specific and derived datatypes.
Problem: Lizbeth learns her lesson but then cannot apply it.
No doubt about it, Lizbeth has learned her lesson: even when she is told to hurry, she will take her time and use %TYPE and %ROWTYPE to declare her variables.
Sure enough, Sunita is soon back at her cubicle, asking her to write another program. This time, she needs to display the names of all the people who have requested flimsy excuses. The name of a person must be displayed in the form “LAST, FIRST.” The excuser (a person who makes excuses) table has these columns, among others:
CREATE TABLE excuser (
id INTEGER
, first_name VARCHAR2(50)
, last_name VARCHAR2(100)
, ... and many more columns ...
);
She then starts writing the function to construct the full name:
FUNCTION full_name (
last_name IN excuser.last_name%TYPE
, first_name IN excuser.first_name%TYPE
)
RETURN VARCHAR2
But then it is time to declare a local variable to hold the full name, and she tries to write something like this:
IS
l_fullname excuser.???%TYPE;
But what can she use for the column name? There is no column for “full name”: it is a derived value. Lizbeth sighs. Will she just have to hardcode another maximum length and run into another bug years from now?
Solution: Create a new datatype with SUBTYPE and anchor to that.
The SUBTYPE statement allows you to create “aliases” for existing types of information, in effect creating your own specially named datatypes. Use SUBTYPE when you want to standardize on a set of named datatypes that aren’t anchorable back to the database. You can then anchor to those new datatypes instead, and achieve the same, desired goal: if a change must be made to, or takes place in, a datatype, you will have to make that change in only one place.
Let’s apply this technique to Lizbeth’s challenge. Stepping back for a moment, the full_name function really is an encapsulation of a business rule: how to construct the full name for an excuser.
Rather than write a standalone, schema-level function to return that full name, it would make much more sense to create a separate package to hold all the rules-related activity for an excuser. So, Lizbeth can create a package specification like this:
PACKAGE excuser_rp
IS
FUNCTION full_name (
last_name_in IN excuser.last_name%TYPE
, first_name_in IN excuser.first_name%TYPE
)
RETURN VARCHAR2;
END excuser_rp;
In addition, she can create a new datatype that is designed to hold full names:
PACKAGE excuser_rp
IS
SUBTYPE full_name_t IS VARCHAR2(1000);
FUNCTION full_name (
last_name_in IN excuser.last_name%TYPE
, first_name_in IN excuser.first_name%TYPE
)
RETURN full_name_t;
END excuser_rp;
The SUBTYPE command simply defines another name, an alias, for VARCHAR2(1000). She can then use that type as the return type of the function to clearly document what type of string is being returned.
Now attention shifts to the package body:
PACKAGE BODY excuser_rp
IS
FUNCTION full_name (
last_name_in IN excuser.last_name%TYPE
, first_name_in IN excuser.first_name%TYPE
)
RETURN full_name_t
IS
l_fullname full_name_t;
BEGIN
l_fullname := last_name_in || ',' || first_name_in;
RETURN l_fullname;
END full_name;
END excuser_rp;
And when Lizbeth calls this function, she will also use the full name type:
DECLARE
l_my_name excuser_rp.full_name_t;
BEGIN
l_my_name := excuser_rp.full_name (
l_person.last_name, l_person.first_name);
Notice that Lizbeth no longer hardcodes her datatype in the declaration; she simply refers back to her subtype. If 1,000 characters are not enough, she can change the definition of that subtype in the package specification and recompile. Everything will automatically adjust to the new size.
Clearly, Lizbeth could have written this function without declaring a local full name variable altogether, but the example illustrates an important point. Note, however, that you will certainly run into this requirement with much more complex code in which local variables will be required.
Next: I take exception to your declaration section >>
More Database Articles Articles
More By O'Reilly Media
|
 This article is excerpted from chapter four of the book Oracle PL/SQL Best Practices, Second Edition, written by Steven Feuerstein (O'Reilly, 2007; ISBN: 0596514107). Check it out today at your favorite bookstore. Buy this book now.
|
|
