What are variables?
Variables in oracle are used by programmers in the declare block to store data temporarily during execution of the code. The name of a PL/SQL variable consists of a letter optionally followed by dollar signs, underscores, and number signs or other characters and should not exceed 30 characters. Syntax of declaring variable is:-
Variable_name datatype [NOT NULL: = value];
- Variable_name is the
name of the variable.
- Datatype is a valid PL/SQL datatype.
- NOT
NULL is an optional specification on the variable. If you declare a
variable as NOT NULL then best practice would be to initialize the
variable while declaring it.
- Value or DEFAULT value is also an optional
specification where you can initialize a variable.
- If
you are declaring variable then you must terminate it by using semicolon
at the end.
Suppose
you want to declare a variable called V_EMPNO with NUMBER data type which will
store number values:-
DECLARE
V_EMPNO
NUMBER;
If
you want to declare a variable called V_EMPNAME with VARCHAR2 data type which
will store character values:-
DECLARE
V_EMPNAME
VARCHAR2 (10);
Default value:-
You can declare a variable and provide default value in it:-
DECLARE
V_EMPNO
NUMBER:=123456789;
The (:-) sign is to provide default value.
Suppose
you want to declare a variable called V_DEPTNO with NUMBER datatype which will
store NUMBER values and your requirement is this variable should not be NULL
then you can do this by below method:-
DECLARE
V_DEPTNO
NUMBER NOT NULL: =10;
Here
you have initialized the V_DEPTNO variable with 10. So whenever you execute
this variable and will not provide any value then oracle will consider value
for this variable as 10.
Above
is hard coded declaration of variable. If there is any type mismatch, variable
assignments and comparisons may not work as expected. Hence, instead of hard
coding the type of a variable, we should use the %TYPE operator.
Suppose,
above we have declared V_EMPNO as NUMBER and we have to store data from
EMPLOYEE table to the variable V_EMPNO, we can also declare it as:-
DECLARE
V_EMPNO
EMPLOYEE.EMPNO%TYPE;
If you want
to store whole row of any table let’s say EMPLOYEE table. You can do it by
%ROWTYPE operator. Suppose, you need to store whole row of EMPLOYEE table then
you can do this by below method:-
DECLARE
V_EMPNO
EMPLOYEE.EMPNO%ROWTYPE;
How these variables can be used in
pl/sql block?
Suppose
you want to store a value in variable, you can declare and use this variable
like shown below:-
Like shown
above, you can use this variable for various manipulations.
What are bind variables?
Bind
variables are variables you create in pl/sql
and then reference in pl/sql or sql.
If you create a bind variable in pl/sql,
you can use the variable as you would a declared variable in your pl/sql
subprogram and then access the variable from pl/sql.
How to create BIND VARIABLES?
You can
create bind variable by using VARIABLE keyword like below:-
VARIABLE
VARIABLE_NAME DATATYPE;
For
example if you want to declare a bind variable you can do this like:-
How to reference bind variable?
You can reference bind variables in PL/SQL by typing a colon (:) followed immediately by the name of the variable. For example, to change this bind variable in pl/sql, you must enter a PL/SQL block. For example:
You can print
the bind variable by using PRINT keyword:-
What is constant?
Constant is a value of a pl/sql
variable which is declared once, and cannot change its value at run time.
If you provide a default value for variable and specify keyword CONSTANT before datatype, it will be treated as constant and this value will not be changed throughout the program. If you are declaring variable as a constant, you must provide value to the variable.
Syntax to create a constant is:-
CONSTANT_NAME CONSTANT DATATYPE: = VALUE;
DECLARE V_EMPNO CONSTANT NUMBER: =1234567890;
Let’s have
an example:-
Read Also:- Anonymous Block
