Login

Username:

Password: 

Join us Now |  Forgot Password? | Forgot UserName?

PL/SQL

Learn step by step



PL/SQL Variables

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:

PrefixData 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:


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 := 'Ramesh';
v_last_name := 'Patel';
d_hire_date := to_date('20140624','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.

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.

DECLARE
n_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE :=200;
d_hire_date EMPLOYEES.HIRE_DATE%TYPE:=to_date('20140624','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.


Related Videos