PL/SQL Bind Variables

Bind variables are session variables, these variables are created at host environment, that’s why these variables are also called as ‘HOST VARIABLE’, these variables are used in SQL, PL/SQL, DYNAMIC SQL, In PL/SQL when a subprogram having ‘OUT’ parameters then those type of subprogram must be executed through bind variables only.

Step1: Creating a Bind Variable, Below is the syntax:

Variable VariableName DataType(Size);

Step2: Using Bind Variable, Below is the syntax:

:VariableName;

Step3: Display value from Bind Variable, Below is the syntax:

PRINT VariableName;

No need to give size for number datatype for varchar2 it is mandatory to give, below is the syntax

Variable 1 Number;

DECLARE

a number(10) := 100;

BEGIN

:i := a/2;

END;

Output=> 50

Bind Variable Using SQL

UPDATE EMP SET sal = sal + 100

WHERE ename = ‘KING’

Anonymous Block Named Block
These blocks does not have name These blocks having a name
These block are not stored permanently into database These blocks are automatically permanently stored in database
We cannot call these blocks in client application We can call these blocks into client application
It compile every time It compile only one time.
We cannot take parameters. We can take parameters