So far, all
the SQL statements were executed with predetermined columns, conditions and
their values. Suppose that you want a query that lists the employees with
various jobs and not just those whose job_ID is SA_REP. You can edit the WHERE
clause to provide a different value each time you run the command, but there is
also an easier way.
By using a
substitution variable in place of the exact values in the WHERE clause, you can
run the same query for different values.
You can
create reports that prompt users to supply their own values to restrict the
range of data returned, by using substitution variables. You can embed substitution
variables in a command file or in a single SQL statement. A variable can be
thought of as a container in which values are temporarily stored. When the
statement is run, the stored value is substituted.
You can use
single-ampersand (&) substitution variables to temporarily store values.
You can
also predefine variables by using the DEFINE command. DEFINE creates and
assigns a value to a variable.
Restricted
Ranges of Data: Examples
•
Reporting
figures only for the current quarter or specified date range
•
Reporting
on data relevant only to the user requesting the report
•
Displaying
personnel only within a given department
Other
Interactive Effects
Interactive
effects are not restricted to direct user interaction with the WHERE clause.
The same principles can also be used to achieve other goals, such as:
•
Obtaining
input values from a file rather than from a person
•
Passing
values from one SQL statement to another
Note: Both SQL Developer and SQL* Plus
support the substitution variables and the DEFINE/UNDEFINE commands. Though SQL
Developer or SQL* Plus does not support validation checks (except for data
type) on user input.
Using
the Single-Ampersand Substitution Variable
When
running a report, users often want to restrict the data that is returned
dynamically. SQL*Plus or SQL Developer provides this flexibility with user
variables. Use an ampersand (&) to identify each variable in your SQL
statement. However, you do not need to define the value of each variable.
Use a
variable prefixed with an ampersand (&) to prompt the user for a value:
SELECT
employee_id, last_name, salary, department_id
FROM employees
WHERE employee_id = &employee_num ;
When SQL
Developer detects that the SQL statement contains an ampersand, you are
prompted to enter a value for the substitution variable that is named in the
SQL statement.
After you
enter a value and click the OK button, the results are displayed in the Results
tab of your SQL Developer session.
Character
and Date Values with Substitution Variables
In a WHERE
clause, date and character values must be enclosed with single quotation marks.
The same rule applies to the substitution variables.
Enclose the
variable with single quotation marks within the SQL statement itself.
Use single quotation marks for date
and character values:
SELECT
last_name, department_id, salary*12
FROM employees
WHERE job_id = '&job_title' ;
Specifying
Column Names, Expressions, and Text
You can use
the substitution variables not only in the WHERE clause of a SQL statement, but
also as substitution for column names, expressions, or text.
Example:
The example
displays the employee number, last name, job title, and any other column that
is specified by the user at run time, from the EMPLOYEES table. For each
substitution variable in the SELECT statement, you are prompted to enter a
value, and then click OK to proceed.
If you do
not enter a value for the substitution variable, you get an error when you
execute the preceding statement.
Note: A substitution variable can be used
anywhere in the SELECT statement, except as the first word entered at the
command prompt.
SELECT
employee_id, last_name, job_id,&column_name
FROM employees
WHERE &condition
ORDER
BY &order_column ;
Use double ampersand (&&) if
you want to reuse the variable value without prompting the user each time:
SELECT employee_id, last_name, job_id,
&&column_name
FROM employees
ORDER
BY &column_name ;
You can use
the double-ampersand (&&) substitution variable if you want to reuse
the variable value without prompting the user each time. The user sees the
prompt for the value only once. In the example above, the user is asked to give
the value for the variable, column_name, only once. The value that is supplied
by the user (department_id) is used for both display and ordering of data. If
you run the query again, you will not be prompted for the value of the variable.
SQL
Developer stores the value that is supplied by using the DEFINE command; it
uses it again whenever you reference the variable name. After a user variable
is in place, you need to use the UNDEFINE command to delete it:
UNDEFINE column_name
