PL/SQL Variables

A variable is a meaningful name of a storage area that our program can manipulate. Variables can have a datatype, such as CHAR, DATE, NUMBER, BOOLEAN or PLS_INTEGER.

Before using a variable, you need to declare it first in the declaration section of a PL/SQL block. By default, variable names are not case-sensitive.

You cannot use a reserved PL/SQL keyword as a variable.

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.
  • Followed by the first character are any number, underscore ( _), and dollar sign ( $) characters.

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 the employee with the VARCHAR2 data type, the variable name should be v_first_name.

General Syntax to declare a variable is

variable_name datatype

[NOT NULL := value | DEFAULT initial_value];

  • variable_name is the name of the variable.
  • datatype is a valid PL/SQL datatype.
  • NOT NULL is an optional specification on the variable.
  • value or DEFAULT value is also an optional specification, where you can initialize a variable.
  • Each variable declaration is a separate statement and must be terminated by a semicolon.
n_emp_no   NUMBER(6);
v_emp_name VARCHAR2(20);
b_active   BOOLEAN;
n_salary   NUMBER(6,2);
v_designation VARCHAR2(10)
DEFAULT 'Associate';

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( = ).

DECLARE
            n_emp_no NUMBER(6);
            v_emp_name VARCHAR2(20);
            b_active BOOLEAN;
            n_salary NUMBER(6,2);
            v_designation VARCHAR2(10) DEFAULT 'Associate';
BEGIN
            n_emp_no := 1193;
            v_emp_name := 'Scott';
            dbms_output.put_line(v_emp_name || ' ' || 'employ id is' || ' '|| n_emp_no);
END;
/

Variable Scope in PL/SQL

PL/SQL allows the nesting of blocks, i.e., the Execution section of an outer block can contain inner blocks.

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
            message VARCHAR2(20) := 'Global';
            value number := 100; 
       BEGIN
            dbms_output.put_line(message || 'variable value is: ' || value);

       DECLARE
            -- Local variables
            message_local VARCHAR2(20) := 'Local';
            value_local number := 50;
BEGIN
            dbms_output.put_line(message || 'variable value is: ' || value);
            dbms_output.put_line(message_local || 'variable value is: ' || value_local);
END;
END;
/

When the above code is executed, it produces the following result

Global variable value is: 100
Global variable value is: 100
Local variable value is: 50

PL/SQL procedure successfully completed.

Assigning SQL Query Results to PL/SQL Variables

You can use the SELECT INTO statement of SQL to assign values to PL/SQL variables. For each item in the SELECT list, there must be a corresponding, type-compatible variable in the INTO list. The following example illustrates the concept.

%TYPE

The %TYPE attribute provides the datatype of a variable or database column. This is particularly useful when declaring variables that will hold database values. For example, assume there is a column named last_name in a table named employee. To declare a variable named v_last_name that has the same datatype as column title, use dot notation and the %TYPE attribute, as follows

v_last_name employee.last_name%TYPE;

DECLARE
            n_salary employee.salary%type;
            n_emp_id employee.emp_id%type := 1193;
        BEGIN
            SELECT salary
            INTO n_salary
            FROM employee
            WHERE emp_id = n_emp_id;
            dbms_output.put_line(n_salary);
            dbms_output.put_line('The employee ' || n_emp_id || ' has salary ' || n_salary);
END;
/

When the above code is executed, it produces the following result.

20000

The employee 1193 has salary 20000

 

PL/SQL procedure successfully completed.

Constants

A constant holds a value that once declared, doesn’t change in the program. A constant declaration specifies its name, datatype, and value and allocates storage for it.

Declaring constant

A constant is declared using a CONSTANT keyword. It requires an initial value and does not allow that value to change.

set SERVEROUTPUT ON;
DECLARE
            hike_per constant number := 0.3;
            n_salary employee.salary%type;
            n_emp_id employee.emp_id%type := 1193;
            new_sal number;
      BEGIN
            SELECT salary
            INTO n_salary
            FROM employee
            WHERE emp_id = n_emp_id;
            new_sal := n_salary + ( n_salary * hike_per);
            dbms_output.put_line('Previous salary ' || n_salary);
            dbms_output.put_line('New salary ' || new_sal);
END;
/

When the above code is executed, it produces the following result.

Previous salary 20000
New salary 26000

PL/SQL procedure successfully completed.

PL/SQL Literals.

A Literal is an explicit numeric, string or Boolean values not represented by an identifier. For examples, 999, TRUE, NULL, ‘learndba’ are literal types number, Boolean or string. PL/SQL literals are case sensitive. PL/SQL supports following literals.

S.No Literal Type & Example
1 Numeric Literals

0030 6 -14 0 +32767

6.6667 0.0 -12.0 3.14159 +8300.00 .5 25.

2E5 1.0E-7 3.14159e0 -1E38 -9.5e-3

2 Character Literals

‘Z’ ‘%’ ‘7’ ‘ ‘ ‘z’ ‘(‘

3 String Literals

‘Hello, world!’
‘XYZ Corporation’

‘Learn DBA’

’12-DEC-12′
‘$1,000,000’

4 BOOLEAN Literals

TRUE, FALSE, and NULL.

5 Date and Time Literals

DATE ‘1978-12-25’;

TIMESTAMP ‘2012-10-29 12:01:01’;

To embed single quotes within a string literal, place two single quotes to each other as shown in below program.

set SERVEROUTPUT ON;
            DECLARE
            message varchar2(20) := 'It''s time to learn';
BEGIN
            dbms_output.put_line(message);
END;
/

When the above code is executed, it produces the following result.

It's time to learn
PL/SQL procedure successfully completed.
Advertisements