Skip to main content

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

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).

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 hard-coding 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, an "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. constant declaration always specifies the name and data type of the constantDifferently 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 18c, if you want to declare an associative array or record as a constant, you can now take advantage of qualified expressions (similar to constructor functions) to do so. Prior to 18c, you will need to build your own function to return a value of the correct type. 
  • If the value of your variables is not going to change in your block, take an extra moment and ten extra key strokes 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

Comments

  1. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. I have not given such thought. Been thinking about other things. :-)

      So you want to, say, add "_vc" to the name to indicate it is a string?

      Delete
    2. This comment has been removed by the author.

      Delete
    3. Wellllll....I don't find myself attracted to the idea of embedding the base datatype (string, boolean, etc.) into the name. I don't see that generally adding lots of value to my understanding of the code. I do use suffixes (mostly) for types, usually.

      But:

      1. If you get value out of it, go for it. And perhaps post your naming conventions for others to use as well, if they like them.

      2. The most important thing is to be consistent.

      Delete
  2. Steven, there is a way of view all the global variables values of each session, in some catalog view ?

    ALL_IDENTIFIERS show the global variables, but not the values

    ReplyDelete
  3. There are "constructor" functions for these datatypes => There are *no* "constructor" functions for these datatypes

    BTW, thanks for your great material.

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. Thanks for pointing this out. Time to update for 18.1 and qualified expressions! :-)

      Delete

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel