Datasheet

50 Chapter 1

Introducing SQL
To clear a defined variable, you can use the UNDEFINE command. Let’s edit the ex01.sql
file to make it look like this:
SELECT &&COL1, &&COL2
FROM &TABLE
WHERE &COL1 = ‘&VAL’
ORDER BY &COL2
/
Enter value for col1: first_name
Enter value for col2: last_name
old 1: SELECT &&COL1, &&COL2
new 1: SELECT first_name, last_name
Enter value for table: employees
old 2: FROM &TABLE
new 2: FROM employees
Enter value for val: John
old 3: WHERE &COL1 = ‘&VAL’
new 3: WHERE first_name = ‘John’
old 4: ORDER BY &COL1
new 4: ORDER BY first_name
FIRST_NAME LAST_NAME
-------------------- -------------------------
John Chen
John Russell
John Seo
UNDEFINE COL1 COL2
Using Positional Notation for Variables
Instead of variable names, you can use positional notation, where each variable is identified
by
&1, &2, and so on. The values are assigned to the variables by position. Do this by put-
ting an ampersand (
&), followed by a numeral, in place of a variable name. Consider the
following query:
SQL> SELECT department_name, department_id
2 FROM departments
3 WHERE &1 = &2;
Enter value for 1: DEPARTMENT_ID
Enter value for 2: 10
old 3: WHERE &1 = &2
new 3: WHERE DEPARTMENT_ID = 10
95127c01.indd 50 2/18/09 6:37:11 AM