PL/SQL Variables

PL/SQL Variables are present with  meaningful name , that can be store a value temporarily which belongs to a particular data type.The value can be change based on the program conditions.Each and every variable will have a specific name,type and size of the memory to store values.Variables are the basic blocks to manipulate PL/SQL programs.


PL/SQL Variables Rules to declare PL/SQL variables:
PL/SQL Variables    The variable name must be less than 31 characters.
PL/SQL Variables    PL/SQL variables are case-insensitive. (Ex: E_NAME and e_name refer to the same variable.)
PL/SQL Variables    Variable name must start with an ASCII letter.
PL/SQL Variables    Best practice is , to follow naming convention to declare a variable .So that it is easy to read and


PL/SQL Variables PL/SQL Variables declaration:

Before you can use a variable In PL/SQL variables must be declared in the declaration section or in a  package as a global variable.PL/SQL allocates memory to the declared variable value and the variable name is used to identify the storage location.
A PL/SQL variable can have any SQL data type (such as CHAR, DATE, or NUMBER) or a PL/SQL only data type (such as BOOLEAN or PLS_INTEGER).



variable_name <data_type><SIZE> <NOT NULL> <:=/DEFAULT initial_value>;
//constant declaration
variable_name <CONSTANT><data_type><SIZE> <NOT NULL> <:=/DEFAULT initial_value>;

variable_name:  name of the variable.
data_type : data type of the variable ,ie NUMBER,VARCHAR2
initial_value : initial value of the variable.
SIZE : Size of the variable.
NOT NULL :(constraint)  The variable value should not be null .


PL/SQL Variables Valid PL/SQL variable declaration :

Constrained declaration: If we provide size,precision limit along with the data type is called constrained declaration.

Unconstrained declaration : This declaration   require more memory than constrained declaration.


PL/SQL Variables PL/SQL Variables initialization :

we can assign a value to a variable as follows.
With assignment operator ( := ).


With DEFAULT keyword.


By selecting database values into PL/SQL variables .

If we declare a variable with no value,PL/SQL assigns it a default value of NULL.But a variable declared with NOT NULL constraint,it is must to assign a value explicitly.


PL/SQL Variables Scope of the PL/SQL variables :

It represents the accessibility of a variable in program blocks.There are two types of variable scope.
PL/SQL Variables   Local variables:  These variables are not accessible from the outer blocks.
PL/SQL Variables Global variables : These variables are accessible from the inner blocks as well as the outer blocks.The declaration is in package level.



Example table:

1 Rose S F 10000 street10,xxx,UK 03-MAR-13 10-MAR-13
2 Tom M 15000 street11,xxx,UK 04-MAY-13 12-MAY-13
3 Jerry F 13000 street122,xxx,UK 05-JAN-13 15-JAN-13

Example Code:

From the above example table, assigning the values to PL /SQL variables.

Result :

Related Posts :
PL/SQL Arrays and nested tables ( Collections )
PL/SQL FOR and WHILE loop controls
PL/SQL Operators
PL/SQL Procedures
PL/SQL Triggers
PL/SQL Functions
   [will not be published]

^ <