In an SQL statement, you can define a variable before you use it, and you can use that variable multiple times in the same SQL statement. You can use the Define or accept command to define variables, and you can use the undefine command to delete variables.
Using the Define Command
Define show all defined variables
Define variable displays the name, value, and parameter type of the specified variable
Define Variable=value creates a user variable of type char and assigns an initial value to the variable.
You can also use the Accept command to define variables and to customize a user prompt that prompts the user to enter data for the specified variable. When you define a variable, you can explicitly specify whether the variable is number or date lamp data type. For security reasons, you can also hide the information entered by the user.
The syntax of the Accept command is as follows:
Accept variable [data_type][format][default][prompt text|noprompt][hide]
Define examples are as follows:
Sql> Define Temp=20
Sql> Define Temp
DEFINE TEMP = "(CHAR)"
Sql> SELECT * FROM dept where deptno>&temp
2;
Original Value 1:select * from dept where deptno>&temp
New Value 1:select * from dept where deptno>20
DEPTNO dname LOC
---------- -------------- -------------
SALES CHICAGO
OPERATIONS BOSTON
The following are the examples of accept:
Sql> Accept Test Number format 999 prompt ' Please enter a digit '
Please enter a number 20
Sql> SELECT * FROM dept where deptno>&test;
Original Value 1:select * from dept where deptno>&test
New Value 1:select * from dept where deptno> 20
DEPTNO dname LOC
---------- -------------- -------------
SALES CHICAGO
OPERATIONS BOSTON
Variables in Oracle
