4. Variables and Program Data

Contents:
Identifiers
Scalar Datatypes
NULLs in PL/SQL
Variable Declarations
Anchored Declarations
Programmer-Defined Subtypes
Tips for Creating and Using Variables

Almost every PL/SQL program you write contains internal data stored as variables, constants, records, or tables. This chapter refers to these various types of storage as variables. Variables may be used for many purposes; for example, they may store information retrieved from columns in a table or may hold calculated values for use only in the program. Variables may be scalar (made up of a single value) or composite (made up of multiple values or components).

The attributes of a variable are its name, datatype, and value (or values, in a complex datatype like a record). The name indicates the part of memory you want to access or change. The datatype determines the type of information you can store in the variable. The value (or values, in the case of a composite datatype) is the set of bits stored in the variable's memory location.

This chapter describes the kinds of names you can give PL/SQL elements and the different types of scalar variables you can declare and use in your programs. It also offers tips on how best to use program data in your code.

4.1 Identifiers

Identifiers are the names given to PL/SQL elements such as variables or nested tables or cursors. Identifiers:

Can be up to 30 characters in length
Must start with a letter
Can then be composed of any of the following: letters, numerals, $, #, and _

A named constant is a special kind of variable. A named constant has a name, datatype, and value, just like a regular variable. However, unlike a regular variable, the value of a named constant must be set when the constant is declared and may not change thereafter. Its value is constant. Unless otherwise mentioned, the information provided below for variables also applies to named constants.

NOTE: An unnamed constant is a literal value, such as 2 or Bobby McGee. A literal does not have a name, though it does have an implied (undeclared) datatype.

4.1.1 Choose the Right Name

The name of your identifier should describe as accurately and concisely as possible what the identifier represents. Let's take a look at choosing the name for a variable. Outside of the actual use or context of a variable, the name is all the variable's got. And if the name is bad, the context is often distorted by the bad choice of a moniker.

The first step towards choosing an accurate name is to have a clear idea of how the variable is to be used. You might even take a moment to write down -- in noncomputer terms -- what the variable represents. You can then easily extract an appropriate name from this statement. For example, if a variable represents the "total number of calls made about lukewarm coffee," a good name for that variable would be total_calls_on_cold_coffee -- or tot_cold_calls, if you are allergic to five-word variable names. A bad name for that variable would be "totcoffee" or t_#_calls_lwcoff, both of which are too cryptic to get the point across.

You can also give each variable a name that describes the business problem that variable will be used to solve. The name should be much more than a description of the internal or computer-oriented function of the variable. If you need a Boolean variable to indicate when "the total order amount exceeds existing balance," a good name would be total_order_exceeds_balance. A poorly chosen name would be float_overflow or group_sum_too_big. You, the developer, know that you had to perform a SUM with a GROUP BY to calculate the total order, but no one else needs to know about that.

4.1.2 Select Readable Names

PL/SQL lets you use 30 characters, including very clear separators such as the underscore character ( _ ), to name your variables. That gives you lots of room to come up with unambiguous names. In fact, if you have variable names of fewer than five characters in length, they are probably too short to be accurate and useful.

Resist the temptation to name a variable which stores the current date as curdat or now. Instead use the more descriptive current_date or even system_date (a logical expansion on the SQL function SYSDATE, which usually provides the value).

An excellent way to check the quality of your variable names (as well as the names of your other objects, particularly module names) is to ask for feedback from another developer on the same project. The code should, to a large extent, make sense if the variables and other identifiers are named in ways that describe the action, and not the cute programming tricks you used to meet the specifications.