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_DATArefer 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
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; /
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
v_last_name variable, and result of the
to_date function to
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; /
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; /
NULL means an unknown value so it has some special characteristics as follows:
NULLis not equal to anything, even itself
NULLis not greater than or less than anything else, even
- You cannot use logical operator equal (
=) or (
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.