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
v_	VARCHAR2
n_	NUMBER
t_	TABLE
r_	ROW
d_	DATE
b_	BOOLEAN

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:

DECLARE
   v_first_name varchar2(20);
   v_last_name varchar2(20);
   n_employee_id number;
   d_hire_date date;
BEGIN
   NULL;
END;

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

DECLARE
  v_first_name  EMPLOYEES.FIRST_NAME%TYPE;
  v_last_name   EMPLOYEES.LAST_NAME%TYPE;
  n_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE;
  d_hire_date   EMPLOYEES.HIRE_DATE%TYPE;
BEGIN
  NULL;
END;
/

The v_first_name variable has data type that is the same as the data type of the first_name column in the emloyees table. In 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:

DECLARE
   v_first_name EMPLOYEES.FIRST_NAME%TYPE;
   v_last_name EMPLOYEES.LAST_NAME%TYPE;
   n_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE;
   d_hire_date EMPLOYEES.HIRE_DATE%TYPE;
BEGIN
   v_first_name := 'Mary';
   v_last_name := 'Jane';
   d_hire_date := to_date('19700101','YYYYMMDD');
END;
/

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.

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
   v_first_name EMPLOYEES.FIRST_NAME%TYPE;
   v_last_name EMPLOYEES.LAST_NAME%TYPE;
   n_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE;
   d_hire_date EMPLOYEES.HIRE_DATE%TYPE;
BEGIN
   SELECT employee_id,
          first_name,
          last_name,
          hire_date
   INTO n_employee_id,
        v_first_name,
        v_last_name,
        d_hire_date
   FROM employees
   WHERE employee_id = 200;

   DBMS_OUTPUT.PUT_LINE(v_first_name);
   DBMS_OUTPUT.PUT_LINE(v_last_name);
   DBMS_OUTPUT.PUT_LINE(d_hire_date);
END;
/

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:

DECLARE
  n_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE :=200;
  d_hire_date EMPLOYEES.HIRE_DATE%TYPE:=to_date('19700101','YYYYMMDD');
BEGIN
   NULL;
END;
/

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.