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 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:
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:
v_first_name := 'Ramesh';
v_last_name := 'Patel';
d_hire_date := to_date('20140624','YYYYMMDD');
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.
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.
n_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE :=200;
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.