Bind Variable in Oracle SQL

Bind variables are the substitution variables that are used in place of literals.
This will be more clear with an example.

Lets take few queries

select department_id from employees where employee_id = 100
select department_id from employees where employee_id = 101
select department_id from employees where employee_id = 102

Here as you can notice that the query is same however the literal values(100,101,102) are changing.

so what will be the impact of this. If a user has to execute these line he will run these codes and each time
oracle has to parse the code and fetch the result from the database. this is cpu consuming. So in order to reduce this extensive timing what we can do it that we can define a bind variable instead of literals , so now our code will look like this

select department_id from employees where employee_id = :empId;

Here :empId is the bind variable.

Now once you execute this code that is it , one execution plan will be created in the oracle and then we need not do compiling and parsing the query again and again, this will save a lot of cpu and will improve the performance significantly

you can use bind variable as follows

variable empId number
exec :empId := 10
select department_id from employees where employee_id = :empId;

What we have done is that we have removed the literal variable with the bind variable.
We are defining a variable and we are using the same in the query.

In case of a pl/sql every reference to a PL/SQL variable is in fact a bind variable.

At this juncture you should also know about dynamic sql and how to use bind variable.

Dynamic SQL is a SQL statement which is constructed and executed at program execution time. Dynamic SQL provides more flexibility than the static sql.Dynamic SQL, allows you to execute a string containing SQL using the EXECUTE IMMEDIATE command.You cannot use EXECUTE IMMEDIATE for multiple-row queries.

declare
P_SQL varchar2(100);
p_ename EMPLOYEES.FIRST_NAME%type := 'bob';
begin
P_SQL := 'SELECT FIRST_NAME FROM employees WHERE employee_id = 100';
execute immediate P_SQL into P_ENAME;
DBMS_OUTPUT.PUT_LINE(P_ENAME);
end;
/

We can not define a DDL statement inside a procedure directly.Dynamic SQL allows us to run database definition language (DDL) statements and flexible programs like adhoc query systems and times when you are not sure of the full text of sql statement during the compilation time.