Bind variables are "substitution" variables that are used as place of literals (such as 111, 234, 567, etc.) that have the effect of sending exactly the same SQL to Oracle every time the query is executed to avoid hard parses. Basically its a way to use the same execution plan regardless of a literal value.
Example:
Instead of doing this, we would do this:
Code: Select all
SQL> select * from emp where empno = 25;
SQL> select * from emp where empno = 26;
SQL> select * from emp where empno = 27;
[code]
We would do this:
[code]
SQL> variable empno number
SQL> exec :empno := 25
SQL> select * from emp where empno = :empno;
SQL> exec :empno := 26
SQL> select * from emp where empno = :empno;
SQL> exec :empno := 27
SQL> select * from emp where empno = :empno;