Defining a Variable

There are three ways to set up a variable: Define, Accept and New_Value.

---

Use Define when you know the value and you want to hard-code it in the query.

Define variable = text

Define DNo1 = 10

Select EmpNo, EName
From Emp
Where Deptno = &DNo1;

  EMPNO ENAME
---------- ----------
   7782 CLARK
   7839 KING
   7934 MILLER

---

Use Accept when you the query operator knows the value and you want them to type it.

Accept variable [NUMBER|CHAR|DATE [FORMAT format] [DEFAULT default] [PROMPT text|NOPROMPT] [HIDE]

The query will pause and the prompt display. The 'Number' option will assure a number is entered. 'Format 99' will make sure only two digits are entered. (I typed in the '20' when the query paused.)

Accept DNo2 Number Format 99 Prompt 'Please enter Department Number: '

Please enter Department Number: 20

Select EmpNo, EName
From Emp
Where DeptNo = &DNo2;

  EMPNO ENAME
---------- ----------
   7369 SMITH
   7566 JONES
   7788 SCOTT
   7876 ADAMS
   7902 FORD

---

Use New_Value when the value can be retrieved from a table.

Column column New_Value variable

Column DeptNo New_Value DNo3

Select Ename, Deptno
From Emp
Where Ename = 'ALLEN';

ENAME          DEPTNO
---------- ----------
ALLEN              30

Select *
From Dept
Where Deptno = &DNo3;

 DEPTNO DNAME          LOC
---------- -------------- -------------
     30 SALES          CHICAGO