=================替换变量&===============
使用一个&符号来指定一个变量值，执行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>