PL/SQL Variables

Summary: in this tutorial, you will learn about PL/SQL variables that help you manipulate data in PL/SQL programs.

In PL/SQL, a variable is a meaningful name of a temporary storage location that supports a particular data type in a program. Before using a variable, you need to declare it first in the declaration section of a PL/SQL block.

PL/SQL variables naming rules

Like other programming languages, a variable in PL/SQL must follow the naming rules as follows:

  • The variable name must be less than 31 characters. Try to make it as meaningful as possible within 31 characters.
  • The variable name must begin with an ASCII letter. It can be either lowercase or uppercase. Notice that PL/SQL is case-insensitive, which means v_data and V_DATA refer to the same variable.
  • Followed by the first character are any number, underscore ( _), and dollar sign ( $) characters. Once again, do not make your variables hard to read and difficult to understand.

PL/SQL variables naming convention

It is highly recommended that you should follow the naming conventions listed in the following table to make the variables obvious in PL/SQL programs:

Prefix Data Type
r_ ROW

Each organization has its own development naming convention guidelines. Make sure that you comply with your organization’s naming convention guidelines.

For example, if you want to declare a variable that holds the first name of employee with the VARCHAR2 data type, the variable name should be v_first_name.

PL/SQL Variables Declaration

To declare a variable, you use a variable name followed by the data type and terminated by a semicolon ( ;). You can also explicitly add a length constraint to the data type within parentheses. The following illustrates some examples of declaring variables in a PL/SQL anonymous block:

PL/SQL variable anchors

In PL/SQL program, one of the most common tasks is to select values from columns in a table into a set of variables. In case the data types of columns of the table changes, you have to change the PL/SQL program to make the types of the variables compatible with the new changes.

PL/SQL provides you with a very useful feature called variable anchors. It refers to the use of  the  %TYPE  keyword to declare a variable with the data type is associated with a column’s data type of a particular column in a table.

Let’s take a look at the employees table in HR sample database provided by Oracle:

Employees Table - PL/SQL Variables
Employees Table

The v_first_name variable has data type that is the same as the data type of the first_name column in the  emloyees  tableIn case the data type of the first_name column changes, the  type of the v_first_name variable is automatically inherits the new data type of the column.

PL/SQL variable assignment

In PL/SQL, to assign a value or a variable to a variable, you use the assignment operator ( := ) which is a colon( : ) followed by the equal sign( = ).

Please see the code listing below to get a better understanding:

In the example above, we assigned Mary to v_first_name variable, Jane to v_last_name variable, and result of the to_date function to d_hire_date variable.

You can use INTO of the SQL SELECT statement to assign a value to a variable. The INTO clause moves the values from the SELECT query’s column list into corresponding PL/SQL variables.

Initializing variables

When you declare a variable, its value is uninitialized and hence is NULL. You can initialize variable a value in declaration section by using variable assignment.

See the following example:

In PL/SQL, NULL means an unknown value so it has some special characteristics as follows:

  • NULL is not equal to anything, even itself NULL.
  • NULL is not greater than or less than anything else, even NULL.
  • You cannot use logical operator equal ( =) or ( <>) with NULL. You must use the SQL IS NULL or IS NOT NULL to test the NULL values.

In this tutorial, we have shown you how to declare, assign and initialize PL/SQL variables. We also walked you through how to declare PL/SQL variables using variable anchors to make your code more flexible and adaptable with the changes in columns of  the database tables.