Using Variables With SQL Command Line

Prompting for a Variable Value in a Query

You can use & to identify a variable that you want to define dynamically. In Example: Prompting for a Variable Value in SQL Command Line, including the &employee_id variable causes the SQL statement to prompt for a value when the statement is executed. You can then enter a value for the employee_id that corresponds to the employee information that you want to display, such as employee ID 125. Note that you can use any name for the variable, such as &my_variable.

-- prompt for employee_id in a query, you need to enter a valid ID such as 125
SELECT employee_id, last_name, job_id FROM employees 
  WHERE employee_id = &employee_id;

When you run the previous SELECT statement, the output is similar to:

Reusing a Variable Value in a Query

You can use && to identify a variable that you want to define dynamically multiple times, but only want to prompt the user once. In Example: Reusing a Variable Value in SQL Command Line, including the &&column_name variable causes the SQL statement to prompt for a value when the statement is executed. The value that is entered is substituted for all remaining occurrences of &&column_name in the SQL statement.

-- prompt for a column name, such as job_id, which is then substituted in the
-- remaining identical substitution variables prefixed with &&
SELECT employee_id, last_name, &&column_name FROM employees
  ORDER BY &&column_name;

Defining a Variable Value for a Query

In Example: Defining a Variable for a Query in SQL Command Line, the &job_id variable is defined before running the SQL statement with the DEFINE command, and the defined value is substituted for the variable when the statement is executed. Because the variable has already been defined, you are not prompted to enter a value.

-- define a variable value for a query as follows
DEFINE job_id = "ST_CLERK"
-- run a query using the defined value for job_id (ST_CLERK)
SELECT employee_id, last_name FROM employees WHERE job_id = '&job_id';