Variable:
Variable is the name of reserved memory location. Each variable has a specific data type which determines the range of values and set of operations for that variable.
PL/SQL variables naming rules:
A variable name can’t contain more than 30 characters. A variable name must start with an ASCII letter followed by any number, underscore (_) or dollar sign ($). PL/SQL is case-insensitive i.e. var and VAR refer to the same variable.
How to declare variable in PL/SQL:
We have to declare a PL/SQL variable in the declaration section or in a package as a global variable. After declaration PL/SQL allocates memory for the variable and variable name is used to identify the storage location.
Syntax:
variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value] |
Where: variable_name is a valid identifier name. datatype is a valid PL/SQL datatype.
Initializing Variables in PL/SQL:
When we declare a variable PL/SQL assigns it NULL as default value. If we want to initialize a variable with a non-NULL value, we can do it during the declaration. We can use any one of the following methods: 1. The DEFAULT keyword Num1 binary_integer := 0;
2. The assignment operator siteName varchar2(20) DEFAULT ‘w3spoint’;
Example:
DECLARE var1 integer := 20; var2 integer := 40; var3 integer; var4 real; BEGIN var3 := var1 + var2; dbms_output.put_line('Value of var3: ' || var3); var4 := 50.0/3.0; dbms_output.put_line('Value of var4: ' || var4); END; / |
Output
Value of var3: 60 Value of var4: 16.66666666666666666666666666666666666667 |
Variable Scope in PL/SQL:
As we discussed in earlier tutorial that PL/SQL allows the nesting of blocks i.e. blocks with blocks. Based on the nesting structure PL/SQL variables can be divide into following categories: Local variables – Those variables which are declared in an inner block and not accessible to outer blocks are known as local variables. Global variables – Those variables which are declared in the outer block or a package and accessible to itself and inner blocks are known as global variables.
Example:
DECLARE -- Global variables num1 number := 10; num2 number := 20; BEGIN dbms_output.put_line('Outer Variable num1: ' || num1); dbms_output.put_line('Outer Variable num2: ' || num2); DECLARE -- Local variables num3 number := 30; num4 number := 40; BEGIN dbms_output.put_line('Outer variable in inner block num1: ' || num1); dbms_output.put_line('Outer variable in inner block num2: ' || num2); dbms_output.put_line('Inner Variable num3: ' || num3); dbms_output.put_line('Inner Variable num4: ' || num4); END; END; / |
Output:
Outer Variable num1: 10 Outer Variable num2: 20 Outer variable in inner block num1: 10 Outer variable in inner block num2: 20 Inner Variable num3: 30 Inner Variable num4: 40 |
