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:
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:
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, 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:
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; /
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:
[codesyntax lang="plsql" title="PL/SQL variables assignment" bookmarkname="PL/SQL variables assignment"]
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 := 'Dose'; d_hire_date := to_date('19700101','YYYYMMDD'); END; /
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.
[codesyntax lang="plsql" title="PL/SQL Initializing Variables" bookmarkname="PL/SQL Initializing 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 by assigning it a value in declaration section.
[codesyntax lang="plsql" title="PL/SQL Initialize Variables" bookmarkname="PL/SQL Initialize Variables"]
DECLARE n_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE :=200; d_hire_date EMPLOYEES.HIRE_DATE%TYPE:=to_date('19700101','YYYYMMDD'); BEGIN NULL; END; /
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.