PL/SQL 101: Declaring Variables and Constants

PL/SQL is, in almost all ways, a straightforward and deceptively simple programming language. The "deception" lies in how simplicity can sometimes mask capability. It is easy to learn the basics of PL/SQL, and you can become productive very quickly. And then you also quickly see how powerful and capable is PL/SQL.

So I offer another post on PL/SQL fundamentals, this one offering key points to remember when declaring constants and variables.

Some General Points

Point #1: You can only have declarations in the declaration section, which is found between the IS | AS | DECLARE keyword and the BEGIN keyword (which kicks off the executable section) or END keyword if declaring elements at the package level.

/* Anonymous and nested blocks*/
DECLARE
   ...declarations...
BEGIN

/* Procedures and functions */
PROCEDURE my_proc (...)
IS | AS
   ...declarations...
BEGIN

/* Package specification and body */
PACKAGE my_pkg
IS | AS
   ...declarations...
END;

So, to be clear: There is an explicit DECLARE section only for unnamed blocks.

Other languages let you declare variables anywhere, right when you need them. You can get a similar effect with nested anonymous blocks, as in:

BEGIN
   ... lots of code ...

   DECLARE
      l_newvar INTEGER;
   BEGIN
      ... use and then discard ...
   END;
END;

Notice that the nested block also means that you do not have to "front load" all declarations for a big procedure or function at the top. Instead, you can defer declaring elements until they are needed (either within the nested block or inside a nested subprogram).

Point #2: You can only define one variable or constant per declaration. Suppose, for example, that I need to declare two integer variables.

This works:

DECLARE
   l_var1 INTEGER;
   l_var2 INTEGER;

While neither of these will compile:

DECLARE
   l_var1, l_var2 INTEGER; -- WRONG!
   INTEGER l_var2, v_var2; -- WRONG!

Anchored Declarations

I am deeply attached to the DRY principle: Don't Repeat Yourself. I also like to think of this more positively as SPOD: Single Point of Definition.

When you building code on top of your data structures, as you do with PL/SQL, pretty clearly your most important "point of definition" are those structures: tables and views.

So if you need to declare a variable or constant with the same type as a (and usually to hold a value from) column in a table, you should literally declare it that way with the %TYPE anchor:

DECLARE
   l_name employees.last_name%TYPE;
   c_hdate CONSTANT employees.hire_date%TYPE;

If you need to declare a record with the same structure as an entire row in a table or view, go with %ROWTYPE:

DECLARE
   l_employee employees%ROWTYPE;

Not only do you avoid copying and hardcoding the datatype (most critically, the maximum length of your VARCHAR2 string), but whenever the object to which you anchored changes, the program unit containing the anchoring will be marked invalid and recompiled automatically by the PL/SQL engine. 

And after that recompilation, the datatype for your declarations will be updated to match the underlying structure. Check out my LiveSQL script for a demonstration of this wonderfulness.

Smart, tightly integrated database programming languages do a lot of fine work on our behalf!

Variables

You declare a variable when you need to manipulate it (set, change, and use its value) in your block.

A variable declaration always specifies the name and data type of the variable. For most data types, a variable declaration can also specify an initial value. If you include the NOT NULL constraint in the declaration, then you must provide an initial value (as with a constant — see below).

The variable name must be a valid user-defined identifier. The data type can be any PL/SQL data type. The PL/SQL data types include the SQL data types. A data type is either scalar (without internal components) or composite (with internal components).

Here are some examples:

DECLARE
   /* Initial value set to NULL by default */
   l_max_salary  NUMBER;

   /* Assigning an initial static value */
   l_min_salary  NUMBER := 10000;

   /* Assigning an initial value with a function call */
   l_hire_date   DATE := SYSDATE;

And here, you see what happens when I declare a variable to be NOT NULL but do not provide an initial value:

DECLARE
   l_date DATE NOT NULL;
BEGIN
   l_date := DATE '2011-10-30';
END;

PLS-00218: a variable declared NOT NULL must have an initialization assignment

Tips for Variables

• Use consistent naming conventions for your variables and constants. For example, I generally use a g_ prefix on global variables (declared at the package level), l_ for local variables, and c_ for constants.
• If you find yourself declaring a whole lot of variables with similar names, they probably belong "together" — in which case, consider declaring a user-defined record type. Here's an example:

/* Instead of this... */

DECLARE
   l_name1           VARCHAR2 (100);
   l_total_sales1    NUMBER;
   l_deliver_pref1   VARCHAR2 (10);
   --
   l_name2           VARCHAR2 (100);
   l_total_sales2    NUMBER;
   l_deliver_pref2   VARCHAR2 (10);
BEGIN

/* Try something like this... */

DECLARE
   TYPE customer_info_rt IS RECORD (
      name           VARCHAR2 (100),
      total_sales    NUMBER,
      deliver_pref   VARCHAR2 (10)
   );

   l_customer1   customer_info_rt;
   l_customer2   customer_info_rt;

Constants

A constant is a variable whose value cannot be changed after it is declared. A constant declaration always specifies the name and data type of the constant. Differently from a variable, you must assign a value to that identifier right in the declaration itself.

This works:

DECLARE
   c_date CONSTANT DATE := DATE '2011-10-30';
BEGIN

This does not work:

DECLARE
   c_date CONSTANT DATE;
BEGIN
   c_date := DATE '2011-10-30';
END;

PLS-00322: declaration of a constant 'C_DATE' must contain an initialization assignment

The expression to the right of the assignment in a constant declaration does not have to be a literal. It can be any expression that evaluates, implicitly or explicitly, to the correct datatype.

Tips for Constants

• As of Oracle Database 12c Release 2 (12.2), if you want to declare an associative array or record as a constant, you will need to build your own function to return a value of the correct type. There are "constructor" functions for these datatypes (as there are, say, for object types and nested tables).
• If the value of your variables is not going to change in your block, take an extra moment and ten extra keystrokes to declare it as a constant. That serves as a message to anyone maintaining your code later: "This identifier should not be modified."

Resources

• Oracle PL/SQL Language Reference: Declarations

• Oracle PL/SQL Language Reference:  Identifier Naming Guidelines