PL/SQL Variables

Read previous tutorial: PL/SQL Data Types 

In the last tutorial, we learnt few things about variables in PL/SQL. Now, we shall look at the constraint definitions and other related operations.

PL/SQL Variables

Constraints are associated with the variables defined in the code block. A constraint is a condition that is placed on the variable.

Two frequently used constraints in PL/SQL are:


Constant – This constraint will cause Oracle Engine to ensure the value is not changed after a value is initially assigned to a variable. If a statement tries to change the variable value, an error will be displayed.

Not Null – This constraint will cause Oracle Engine to ensure that the variable always contains a value. If the statement attempts to assign an empty or a null value to that particular variable, the program will be error prone and will get abnormal termination of the program or the exception section will execute, if included in the program code.

Example

PI constant number(9,8) := 3.14159265;
dob not null date := '12-DEC-94';

How to Assign Values to Variables

A PL/SQL procedure is of no use if there isn’t any method to associate values with the variables. But, PL/SQL provides us with the following ways to achieve this.

:=
This sign includes a ‘colon’ with a succeeding ‘equal to’ sign. This particular sign assigns the parameter on the right hand side of the sign to the parameter or the variable on the left hand side of the sign.

Into
The ‘Into’ keyword is used in a ‘Fetch’ or a ‘Select’ statement. For a Select statement, it assigns the value in the Select section to the variables following the ‘Into’ keyword. When used with the Fetch statement, it assigns the cursor values to the variables that follow the ‘Into’ keyword.

Lets see a program now.

DECLARE 
Var1 integer := 35; 
Var2 integer := 55; 
Var3 integer; 
Var4 real; 
BEGIN 
Var3 := Var1 + Var2; 
dbms_output.put_line('Value of Var3: ' || Var3); 
Var4 := 70.0/3.0; 
dbms_output.put_line('Value of Var4: ' || Var4); 
END; 
/

Output
Value of Var3: 90 
Value of Var4: 23.333333333333333333 
PL/SQL procedure successfully completed.

Here, we have declared and defined the 4 variables in the declaration block. The declaration block just holds the variable to be declared in the the current program code. The execution and calculation is done in the BEGIN block. We can access the variables declared in the DECLARE block here.

Scope of Variables

Scope defines the visibility of a particular variable in a program code. PL/SQL gives us the option to develop nesting of blocks which means that every block can contain another block within itself. However, the outer block won’t be accessible.

There are two types of variable scope.

1. Local Variables
2. Global Variables

The next example will help you understand the terms local variables and global variables clearly.

DECLARE 
-- Global variables 
num1 number := 100; 
num2 number := 200; 
BEGIN 
dbms_output.put_line('Outer Block Variable num1: ' || num1); 
dbms_output.put_line('Outer Block Variable num2: ' || num2); 
DECLARE 
-- Local variables 
num1 number := 300; 
num2 number := 400; 
BEGIN 
dbms_output.put_line('Inner Block Variable num1: ' || num1); 
dbms_output.put_line('Inner Block Variable num2: ' || num2); 
END; 
END; 
/

Output
Outer Block Variable num1: 100 
Outer Block Variable num2: 200
Inner Block Variable num1: 300 
Inner Block Variable num2: 400 
PL/SQL procedure successfully completed.

This program properly shows that a BEGIN block can contain other blocks as well and can get the correct output. Here we have inner blocks and outer blocks which have limited scope according to their visibility.