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

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.

How to Get a Mutating Table Error

I need to implement this rule on my employees table:
Your new salary cannot be more than 25x th…

Table Functions, Part 1: Introduction and Exploration

Please do feel encouraged to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done!


Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs.

So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into.

Of course, I am not the first to do so. I encourage to check out the documentation, as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall. Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latter part of this seri…

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 perspective…