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:

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 the 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;Code language: SQL (Structured Query Language) (sql)

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 employeestable in HRsample database provided by Oracle:

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;
/Code language: SQL (Structured Query Language) (sql)

The v_first_name variable has a data type that is the same as the data type of the first_namecolumn in the  emloyees  table. In case the data type of the first_namecolumn changes, the type of the v_first_namevariable automatically inherits the new data type of the column.

PL/SQL variable assignment

In PL/SQL, to assign a value or a variable to another, 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;
/Code language: SQL (Structured Query Language) (sql)

In the example above, we assigned Mary to v_first_namevariable, Janeto v_last_namevariable, and result of the to_datefunction to d_hire_datevariable.

You can use INTOof the SELECT statement to assign a value to a variable. The INTOclause moves the values from the SELECTquery’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;
/Code language: SQL (Structured Query Language) (sql)

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;
/Code language: SQL (Structured Query Language) (sql)

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

• NULLis not equal to anything, even itself NULL.
• NULLis 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 to the changes in columns of the database tables.