PL/SQL Variables

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

PL/SQL Variable Naming Convention

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

• The variable name must be less than 31 characters. Try to make it as meaningful as possible within 31 characters.
• The starting of a variable must be an ASCII letter. It can be either lowercase or uppercase. Note that PL/SQL is not case-sensitive.
• A variable name can contain numbers, underscore, and dollar sign characters followed by the first character.  Again, do not make your variables hard to read and understand to make it easier to maintain in the future.

It is recommended that you should follow the naming conventions in the table 1.1 below to name variables to make it obvious in PL/SQL programs:

Prefix	Data Type
v_	VARCHAR2
n_	NUMBER
t_	TABLE
r_	ROW
d_	DATE
b_	BOOLEAN

For example, if you want to declare a variable to hold the first name of employee with the data type VARCHAR2 the variable name should be v_first_name.
  
PL/SQL Variable Declaration-
To declare a variable, you type a variable name followed by the data type and terminated by a semicolon (;). You can also explicitly add length constraint to the data type in a set of parentheses. Here is an example of declaring some variables in an anonymous block:

PL/SQLcode

1. DECLARE

2.    v_first_name VARCHAR2(20);

3.    v_last_name VARCHAR2(20);

4.    n_employee_id NUMBER;

5.    d_hire_date DATE;

6. BEGIN

7.    NULL;

8. END;

 PL/SQL Variable Anchors

In PL/SQL program, you select different values from columns of a database table into a set of variables. There are new enhancements so the data type of column of the table changed therefore you have to change the PL/SQL program also to make the type of variable compatible with the new changes. PL/SQL provides you a very useful feature called variable anchors. It refers to the use of keyword %TYPE to declare a variable with the data type is the column data type in a table.

Let’s take a look at the employees table in HR sample database below:

Employees Table

PL/SQL code

1. DECLARE

2.   v_first_name  EMPLOYEES.FIRST_NAME%TYPE;

3.   v_last_name   EMPLOYEES.LAST_NAME%TYPE;

4.   n_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE;

5.   d_hire_date   EMPLOYEES.HIRE_DATE%TYPE;

6. BEGIN

7.   NULL;

8. END;

9. /

PL/SQL Variable Assignment

To assign a value or a variable to a variable in PL/SQL, you use the assignment operator ( := ) which is a colon( : ) followed by an equal sign( = ). See the code listing below to have a better understanding:

PL/SQL variables assignment

1. DECLARE

2.    v_first_name EMPLOYEES.FIRST_NAME%TYPE;

3.    v_last_name EMPLOYEES.LAST_NAME%TYPE;

4.    n_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE;

5.    d_hire_date EMPLOYEES.HIRE_DATE%TYPE;

6. BEGIN

7.    v_first_name := 'Mary';

8.    v_last_name := 'Dose';

9.    d_hire_date := TO_DATE('19700101','YYYYMMDD');

10. END;

11. /

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

PL/SQL Initializing Variables

1. SET SERVEROUTPUT ON SIZE 1000000;

2. DECLARE

3.    v_first_name EMPLOYEES.FIRST_NAME%TYPE;

4.    v_last_name EMPLOYEES.LAST_NAME%TYPE;

5.    n_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE;

6.    d_hire_date EMPLOYEES.HIRE_DATE%TYPE;

7. BEGIN

8.    SELECT employee_id,

9.           first_name,

10.           last_name,

11.           hire_date

12.    INTO n_employee_id,

13.         v_first_name,

14.         v_last_name,

15.         d_hire_date

16.    FROM employees

17.    WHERE employee_id = 200;

19.    DBMS_OUTPUT.PUT_LINE(v_first_name);

20.    DBMS_OUTPUT.PUT_LINE(v_last_name);

21.    DBMS_OUTPUT.PUT_LINE(d_hire_date);

22. END;

23. /

Initializing Variables

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

PL/SQL Initialize Variables

1. DECLARE

2.   n_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE :=200;

3.   d_hire_date EMPLOYEES.HIRE_DATE%TYPE:=TO_DATE('19700101','YYYYMMDD');

4. BEGIN

5.    NULL;

6. END;

7. /

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

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