Return to Contents
9 Substitution Variable Examples
9.1 Setting a Substitution Variable's Value
A substitution variable can be set in several ways. The common ways are given below.
1. The DEFINE command sets an explicit value:
define myv = 'King'
This creates a character variable "myv" set to the value "King".
2. The ACCEPT command:
accept myv char prompt 'Enter a last name: '
prompts you for a value and creates a character variable "myv" set to the text you enter.
3. Using "&&" before an undefined variable prompts you for a value and uses that value in the
statement:
select first_name from employees where last_name = '&&myuser';
If the substitution variable "myuser" is not already defined then this statement creates "myus
sets it to the value you enter.
4. Using COLUMN NEW_VALUE to set a substitution variable to a value stored in the databas
column last_name new_value mynv
select last_name from employees where employee_id = 100;
This creates a substitution variable "mynv" set to the value in the "last_name" column.
9.2 Using a Substitution Variable
Once a substitution variable has a value, it can be referenced by prefixing the variable name with a
ampersand (&).
If the variable "myv" is already defined it can be used like:
select employee_id from employees where last_name = '&myv';
9.3 Finding All Defined Substitution Variables
The DEFINE command with no parameters shows all defined substitution variables, their values, a
their types. For example:
define
页码,1/8sub_var9
2010-6-26http://www.oracle.com/technology/support/tech/sql_plus/htdocs/sub_v...