PL/SQL 101: Declaring Variables and Constants
PL/SQL 101: Declaring Variables and Constants
In this post about PL/SQL basics, we take a look at the ins and outs (and power) of declaring variables in constants in this SQL language. Read on for the goods.
Join the DZone community and get the full member experience.Join For Free
Running out of memory? Learn how Redis Enterprise enables large dataset analysis with the highest throughput and lowest latency while reducing costs over 75%!
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.
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!
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
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
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!
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
- 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;
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.
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."
Published at DZone with permission of Steven Feuerstein , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.