PL/SQL - Variables

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:

 

• Local variables - variables declared in an inner block and not accessible to outer blocks.
• Global variables - variables declared in the outermost block or a package.

 

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.