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