PL/SQL Variables

PL/SQL variable is a relevant name which provide a storage capability during the execution of code. Every PL/SQL variables has a specific data type which defines the size and physical location of the variable memory. Moreover you can specifies initial value of the variable at the time of variable declaration.

You must have to declare the PL/SQL variable in the declaration section. And variable name should not more than 31 characters. Variable name must be begin with ASCII letter. It can be either in uppercase or lowercase.

Followed by the first character are numerals, underscore, and dollar sign characters allowed.

PL/SQL is case-insensitive. It means variable name Length and length refer as the same variable.

Syntax for declaring variable

The general syntax to declaring variable:

variable_name Datatype[Size] [NOT NULL] := [ value ]; 

Explanation

• variable_name is the predefined name of the variable.
• Data type is a valid PL/SQL data type.
• Size is an optional specification of data type size to hold the maximum size value.
• NOT NULL is an optional specification of the variable value can't be accept NULL.
• value is also an optional specification, where you can initialize the initial value of variable.
• Each variable declaration is terminated by a semicolon.

Example of Variable Declaration

In this example variable defining employee number (eno) is NOT NULL(compulsory), employee name and initializing initial value to a variable,

Example

DECLARE
    eno number(5) NOT NULL := 2 -- NOT NULL (value can't be blank), Assign initial value
    ename varchar2(15) := 'Branson Devs'; -- intialize value at the time of declaration
BEGIN
    dbms_output.put_line('Declared Value:');
    dbms_output.put_line(' Employee Number: ' || eno || ' Employee Name: ' || ename);
END;
/

Backward slash '/' indicates to execute the above PL/SQL Program.

Example Result

PL/SQL Placeholders

Placeholders are any of variables, constants or records to store temporary in storage area. Later you can use it to manipulate data during the execution of a PL/SQL block. You can define placeholders with the name and data type. Here are some data types to define placeholders.

NUMBER(p,s), NUMBER(n), CHAR, VARCHAR2, DATE, LONG, LONG RAW, BLOB, CBLOB, BFILE 

Variables Scope

PL/SQL variable scope is identified the region range which you can reference the variable. PL/SQL have two type scopes local scope and global scope,

Local variables - Variables declared in inner block and can't be referenced by the outside blocks.
Global variables - Where as variables declared in a outer block and can be referencing by itself in inner blocks.

Variable Scope Example

In this example declaration two variables (num1 and num2) are in the outer block (Global variable) and another one third variable declared (num_addition) into the inner block (local variable). Variable 'num_addition' declared inner block so can't access in the outer block. But num1 and num2 can be accessed anywhere in the block.

Example

DECLARE
    num1 number := 10;
    num2 number := 20;
BEGIN
    DECLARE 
        num_addition number; 
    BEGIN 
        num_addition := num1 + num2; 
        dbms_output.put_line('Addition is: ' || num_addition);
    END;  -- End of access num_addition variable
END;
/

Example Result

Variable Scope Identifier (OUTER keyword)

This example is also showing a difference between inner block and outer block variable scope. You can use OUTER keyword to access outer block variable inside the inner block. It's called global qualified name space.

Example

DECLARE
    num number := 10;
BEGIN
    DECLARE 
        num number := 10; 
    BEGIN 
        IF num = OUTER.num THEN
          DBMS_OUTPUT.PUT_LINE('Both are same value');
        ELSE
          DBMS_OUTPUT.PUT_LINE('Different value');
        END IF;
    END;  -- End of scope to access num variable
END;
/

Example Result

• « Previous
• Next »