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_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:
| PREFIX |
DATA 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
PL/SQL variables must be declared in the declaration section or in a package as a global variable. 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 syntax for declaring a variable is:
variable_name [CONSTANT] datatype [NOT NULL] [:= |DEFAULT initial_value]
Where,
- variable_name is a valid identifier in PL/SQL,
- datatype must be a valid PL/SQL data type or any user defined data type.
Some valid variable declarations along with their definition are shown below:
COUNT NUMBER(10, 2);
name VARCHAR2(20);
address VARCHAR2(100);
pi CONSTANT DOUBLE PRECISION := 3.1415;
When you provide a size, scale or precision limit with the data type, it is called a constrained declaration. Constrained declarations require less memory than unconstrained declarations.
For example:
COUNT NUMBER(10, 2);
name VARCHAR2(20);
address VARCHAR2(100);
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;
/
Initializing Variables in PL/SQL
Whenever you declare a variable, PL/SQL assigns it a default value of NULL. If you want to initialize a variable with a value other than the NULL value, you can do so during the declaration, using either of the following:
The DEFAULT keyword
The assignment operator
For example:
counter BINARY_INTEGER := 0;
greetings VARCHAR2(20) DEFAULT 'Have a Nice Day';
You can also specify that a variable should not have a NULL value using the NOT NULL constraint. If you use the NOT NULL constraint, you must explicitly assign an initial value for that variable.
It is a good programming practice to initialize variables properly otherwise, sometimes program would produce unexpected result. Try the following example which makes use of various types of variables:
DECLARE
a INTEGER := 10;
b INTEGER := 20;
c INTEGER;
f REAL;
BEGIN
c := a + b;
dbms_output.Put_line('Value of c: ' || c);
f := 70.0 / 3.0;
dbms_output.Put_line('Value of f: ' || f);
END;
/
When the above code is executed, it produces the following result:
Value OF c: 30
Value OF f: 23.333333333333333333
PL/SQL procedure successfully completed.
Variable Scope in PL/SQL
PL/SQL allows the nesting of Blocks, i.e., each program block may contain another inner block. If a variable is declared within an inner block, it is not accessible to the outer block. However, if a variable is declared and accessible to an outer Block, it is also accessible to all nested inner Blocks. There are two types of variable scope:
Following example shows the usage of Local and Global variables in its simple form:
DECLARE
-- Global variables
num1 NUMBER := 95;
num2 NUMBER := 85;
BEGIN
dbms_output.Put_line('Outer Variable num1: ' || num1);
dbms_output.Put_line('Outer Variable num2: ' || num2);
DECLARE
-- Local variables
num1 NUMBER := 195;
num2 NUMBER := 185;
BEGIN
dbms_output.Put_line('Inner Variable num1: ' || num1);
dbms_output.Put_line('Inner Variable num2: ' || num2);
END;
END;
/
When the above code is executed, it produces the following result:
Outer variable num1: 95
Outer variable num2: 85
Inner variable num1: 195
Inner variable num2: 185
PL/SQL procedure successfully completed.
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:
 |
| Employees Table |
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 employees 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.
