Sams Teach Yourself SQL in 21 Days, Fourth Edition

Using Variables in SQL*Plus

Without actually getting into a procedural language, you can still define variables in your SQL statement. You can use special options in SQL*Plus (covered in this section) to accept input from the user to pass parameters into your SQL program.

Substitution Variables (&)

An ampersand (&) is the character that calls a value for a variable within a SQL script. If the variable has not previously been defined, the user is prompted to enter a value.

SQL> select *
  2  from &TBL
  3  /
							Enter value for tbl: products

The user entered the value "products." old 2: from &TBL new 2: from products PRO PRODUCT_NAME UNIT_COST --- ------------------------------ --------- P01 MICKEY MOUSE LAMP 29.95 P02 NO 2 PENCILS - 20 PACK 1.99 P03 COFFEE ...