=================替换变量&===============
使用一个&符号来指定一个变量值，执行SQL语句时，会提示用户输入一个数值。
SQL> select sal from emp where empno=&emp_no;
输入 emp_no 的值: 7369 SAL
----------
800 SQL> select * from emp where &conditions;
输入 conditions 的值: sal>2000 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 已选择6行。 SQL> select empno,&column_name, sal from emp where &condition order by &order_by_colunm;
输入 column_name 的值: ename
输入 condition 的值: sal>2000
输入 order_by_colunm 的值: sal desc EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000
7902 FORD 3000
7788 SCOTT 3000
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450 已选择6行。
==================&&========================
&&表示每次重复上一次使用的变量值，第二次执行SQL语句时，不会提示用户输入变量值。
SQL> select * from emp where &&condition;
输入 condition 的值: sal>2000 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 已选择6行。 SQL> select * from emp where &&condition; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 已选择6行。
============DEFINE和UNDEFINE========================
使用define命令创建和分配一个变量的值。使用undefine命令来删除一个变量。
SQL> define employee_number=7369;
SQL> select * from emp where empno=&employee_number; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 SQL> undefine employee_number;
SQL> select * from emp where empno=&employee_number;
输入 employee_number 的值: 7369 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 SQL>
======================VERIFY=================
使用verify命令来显示替换变量之前和之后的值。
SQL> set verify on;
SQL> select * from emp where empno=&employee_number;
输入 employee_number 的值: 7369
原值 1: select * from emp where empno=&employee_number
新值 1: select * from emp where empno=7369 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 SQL>