Datasheet

48 Chapter 1

Introducing SQL
to provide a value. The variable will always have the CHAR datatype associated with it.
Here is an example of defining a substitution variable:
SQL> DEFINE DEPT = 20
SQL> DEFINE DEPT
DEFINE DEPT = “20” (CHAR)
SQL> LIST
1 SELECT department_name
2 FROM departments
3* WHERE department_id = &DEPT
SQL> /
old 3: WHERE DEPARTMENT_ID = &DEPT
new 3: WHERE DEPARTMENT_ID = 20
DEPARTMENT_NAME
---------------
Marketing
1 row selected.
SQL>
Using the DEFINE command without any arguments shows all the defined
variables.
A . (dot) is used to append characters immediately after the substitution variable. The
dot separates the variable name and the literal that follows immediately. If you need a dot
to be part of the literal, provide two dots continuously. For example, the following query
appends
_REP to the user input when seeking a value from the JOBS table:
SQL> SELECT job_id, job_title FROM jobs
2* WHERE job_id = ‘&JOB._REP’
SQL> /
Enter value for job: MK
old 2: WHERE JOB_ID = ‘&JOB._REP’
new 2: WHERE JOB_ID = ‘MK_REP’
JOB_ID JOB_TITLE
---------- ------------------------
MK_REP Marketing Representative
1 row selected.
SQL>
95127c01.indd 48 2/18/09 6:37:11 AM