Use temporary variables in Oracle

Use temporary variables in Oracle

In Oracle databases, you can use variables to compile general SQL statements. When running SQL statements, input values for variables will replace the variables with these values in SQL statements.

Temporary variables are only valid in SQL statements that use them, and their values cannot be retained. Temporary variables are also called replacement variables. In an SQL statement, if the & symbol is used before a variable, the variable is a temporary variable for a long time. When you execute an SQL statement, the system will prompt you to provide a specific data for the variable.

For example, run the following command in SQL * plus:
SQL> select * from dept where deptno> & temp;
Input temp value: 30
Original Value 1: select * from dept where deptno> & temp
New value 1: select * from dept where deptno> 30
DEPTNO DNAME LOC
-------------------------------------
40 OPERATIONS BOSTON
SQL>
You can also use multiple temporary variables, for example:

SQL> select & column_name, dname, loc from dept where & column_name> 20;
Enter the value of column_name: deptno
Enter the value of column_name: deptno
Original Value 1: select & column_name, dname, loc from dept where & column_name> 20
New value 1: select deptno, dname, loc from dept where deptno> 20

DEPTNO DNAME LOC
-------------------------------------
30 SALES CHICAGO
40 OPERATIONS BOSTON
In SQL statements, if you want to re-use a variable and do not want to re-prompt the input, you can use the & symbol to define the temporary variable. As follows:
SQL> select & column_name, dname, loc from dept where & column_name> 10;
Enter the value of column_name: deptno
Original Value 1: select & column_name, dname, loc from dept where & column_name> 10
New value 1: select deptno, dname, loc from dept where deptno> 10

DEPTNO DNAME LOC
-------------------------------------
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON