PL SQL Variables

Learn PL SQL variables, its declaration and types etc with examples.

• In PL/SQLvariables are used to store data temporarily  for calculations and data manipulations without accessing the data base every time.
• Variables can be reused repeatedly if once declared
• variables allocate storage space according its data type definition.
• Default or NOT NULL constraint can be appled for a variable.
• Values can be reassigned to a variable at the execution section.

PL/SQL Variable Types

• Scalar
• Composite
• Reference
• LOB (large objects)

Scalar Data Types

• This data types holds a single value
• supports boolean variables
• This data types are equivalent to the column specified data types in a table.

Composite Data Types

• Stores more than one scalar variables.
• This data types are similar to structures in ‘C’ and ‘C#’
• keeps all the related data items  together as a single collection. 

Reference Data Types

• This data types acts like a pointers which holds the values of other programmed items.
• This data types are useful when performing manipulation on collection of data items. 

LOB Data Types

• This data types holds the location of large objects that are stored out of line in PL/SQL program.

PL SQL Variables Declaration & Syntax 

[variable_name] [constant] [data_type] [NOT NULL] : = [DEFAULT] [EXPR]

Below is description of above syntax

• variable name– it is an identifier to identify the relevant variable
• data_type – refers to type of data stored in the variable 
• DEFAULT – sets the default value to a variable
• Constant  used not to change the value assigned to a variable during the program execution. Value must be initialized during the variable declaration else it raises the exception.
• NOT NULL-  raises exception it finds storing a NULL value in the variable
• EXPR –  refers a value that stored into the variable 

Here is an example of declaring a variable
Name VARCHAR2(50);

Example of Variable Declaration with default Value
The below syntax shows the declaring a variable and assigning a value at declarative section.
Syntax
[variable_name] [data type]:=value;

Example:
Name VARCHAR2(50) := ‘RAKESH’;

Example of declaring variable first, then initialize the variable

Syntax:

[variable_name] [data type];
[variable_name]:=value;

Example:
Name VARCHAR2(50) ;
Name:= ‘RAKESH’;

More  PL/SQL Example to declare and assign a value to a variable. 

Example:

DECLARE
    --declaration of variable with VARCHAR DATA TYPE
    -- WITH 20 BYTES of SIZE
    VAR1 VARCHAR2(20);

BEGIN
    --Assigning string to a variable at the execution part.
    VAR1 :='HELLO WORLD';
    --its an output function to print the data on the screen
    DBMS_OUTPUT.PUT_LINE(VAR1);

END;

Output:

HELLO WORLD