Substitution Variables
We can use substitution variables to prompt users to supply their own values to restrict the range of data returned. Substitution variable can be used in single query or even in sql script file.
(&) Single ampersand and (&&) Double ampersand are used to store temporary values for substitution.
Substitution variable are used to support
- WHERE condition
- ORDER BY clause
- Table name
- column expression
- or even entire SELECT Statements
Example for & substitution
SELECT employee_id,first_name,department_id,hire_date from employees WHERE employee_id = &Enter_Empid ;
Prompt for Employee id to user
and when 100 is passed as parameter we get data for employee id 100 as shown below.
Using && substitution
The only difference between & and && is that when we use && we are prompted only for the first time and the value is saved for subsequent executions.
SELECT employee_id,first_name,department_id,hire_date from employees WHERE employee_id = &&Enter_Empid ;
