Bind variables are used in many database systems to interface 3GL code to the 4GL RDBMS (Relational Database Management System).
CONCEPTS
- Allow values (simple and complex) to be passed into SQL, DML (Data Manipulation Language), and PL/SQL statements
- Never used with DDL (Data Definition Language) statements
- Bind variables mean statements remain constant, even though the values may change
- This allows oracle to cache how to execute the statement when used again – even if the values differ
- Bind variables are much better than using dynamically constructed statements
- Dynamic statements are not reusable (as each value makes the statement different)
- This causes the Oracle cache to be flushed more often as Oracle cannot tell what is reusable
- Fox promotes the use of bind variables implicitly
- However, avoid using dynamic SQL in PL/SQL
EXAMPLES
- SQL Plus bind variable usage
SQL> SELECT *
2 FROM scott.emp
3 WHERE ename = &1;
Enter value for 1: 'KING'
old 3: WHERE ename = &1
new 3: WHERE ename = 'KING'
EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO
----- ----- --------- --- --------- ---- ------
7839 KING PRESIDENT 17-NOV-81 5000 10
SELECT *
FROM scott.emp
WHERE name = :1