Chapter 3: 常用SQL * PLUS命令
当前语句存储在SQL缓冲区中，我们可以操作这条记录。
desc emp;
SQL> desc emp;
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(4)
ENAME VARCHAR2(10) Y
JOB VARCHAR2(9) Y
MGR NUMBER(4) Y
HIREDATE DATE Y
SAL NUMBER(7,2) Y
COMM NUMBER(7,2) Y
DEPTNO NUMBER(2) Y
SQL> desc dept;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14) Y
LOC VARCHAR2(13) Y
SET LINE[SIZE]{80|n}命令
eg: set line 100
SQL> select empno, ename, job, sal
2 from dept
3 Where sal >= 1500
4 order by job, sal desc;
Where sal >= 1500
*
第 3 行出现错误:
ORA-00904: "SAL": 标识符无效
SQL> L
1 select empno, ename, job, sal
2 from dept
3 Where sal >= 1500
4* order by job, sal desc
SQL> 2 from emp // n text 命令
SQL> L
1 select empno, ename, job, sal
2 from emp
3 Where sal >= 1500
4* order by job, sal desc
SQL> /
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7902 FORD ANALYST 3000
7566 JONES MANAGER 2975
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7839 KING PRESIDENT 5000
7499 ALLEN SALESMAN 1600
7844 TURNER SALESMAN 1500
已选择7行。
SQL> select ename
2 from emp;
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
KING
TURNER
JAMES
FORD
ENAME
----------
MILLER
已选择12行。
SQL>
SQL> L //List
1 select ename
2* from emp
SQL> 1 // n 设置当前行
1* select ename
SQL> a ,job,sal // a (append)附加
1* select ename,job,sal
SQL> L
1 select ename,job,sal
2* from emp
SQL> / // /orR is 执行
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
ALLEN SALESMAN 1600
WARD SALESMAN 1250
JONES MANAGER 2975
MARTIN SALESMAN 1250
BLAKE MANAGER 2850
CLARK MANAGER 2450
KING PRESIDENT 5000
TURNER SALESMAN 1500
JAMES CLERK 950
FORD ANALYST 3000
ENAME JOB SAL
---------- --------- ----------
MILLER CLERK 1300
已选择12行。
SQL> select empno, ename, job,sal
2 from emp
3 where sal >= 1500
4 order by job, sal desc;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7902 FORD ANALYST 3000
7566 JONES MANAGER 2975
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7839 KING PRESIDENT 5000
7499 ALLEN SALESMAN 1600
7844 TURNER SALESMAN 1500
已选择7行。
SQL> L
1 select empno, ename, job,sal
2 from emp
3 where sal >= 1500
4* order by job, sal desc
SQL> del 4
SQL> L
1 select empno, ename, job,sal
2 from emp
3* where sal >= 1500
SQL> /
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7499 ALLEN SALESMAN 1600
7566 JONES MANAGER 2975
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500
7902 FORD ANALYST 3000
已选择7行。
SQL> select empno, ename, job,sal
2 from dept
3 where sal >= 1500
4 order by job, sal desc;
where sal >= 1500
*
第 3 行出现错误:
ORA-00904: "SAL": 标识符无效
SQL> L
1 select empno, ename, job,sal
2 from dept
3 where sal >= 1500
4* order by job, sal desc
SQL> C /dept/emp
SP2-0023: 未找到字符串
SQL> 2
2* from dept
SQL> C /dept/emp
2* from emp
SQL> L
1 select empno, ename, job,sal
2 from emp
3 where sal >= 1500
4* order by job, sal desc
SQL> /
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7902 FORD ANALYST 3000
7566 JONES MANAGER 2975
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7839 KING PRESIDENT 5000
7499 ALLEN SALESMAN 1600
7844 TURNER SALESMAN 1500
已选择7行。
SQL> 4
4* order by job, sal desc
SQL> C /job,/
4* order by sal desc
SQL> L
1 select empno, ename, job,sal
2 from emp
3 where sal >= 1500
4* order by sal desc
SQL> /
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7839 KING PRESIDENT 5000
7902 FORD ANALYST 3000
7566 JONES MANAGER 2975
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7499 ALLEN SALESMAN 1600
7844 TURNER SALESMAN 1500
已选择7行。
如何生成脚本文件
SQL> select empno, ename, job, sal
2 from emp
3 where sal >= 1500
4 order by job, sal desc;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7902 FORD ANALYST 3000
7566 JONES MANAGER 2975
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7839 KING PRESIDENT 5000
7499 ALLEN SALESMAN 1600
7844 TURNER SALESMAN 1500
已选择7行。
SQL> save D:\SQL\SAMPLE
SP2-0110: 无法创建保存文件 "D:\SQL\SAMPLE.sql"
SQL> save D:\SQL\SAMPLE
已创建 file D:\SQL\SAMPLE.sql
SQL> L
1 select empno, ename, job, sal
2 from emp
3 where sal >= 1500
4* order by job, sal desc
如何编辑脚本文件
SQL> Get D:\Sql\SAMPLE.sql
1 select empno, ename, job, sal
2 from emp
3 where sal >= 1500
4* order by job, sal desc
SQL> L
1 select empno, ename, job, sal
2 from emp
3 where sal >= 1500
4* order by job, sal desc
SQL> /
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7902 FORD ANALYST 3000
7566 JONES MANAGER 2975
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7839 KING PRESIDENT 5000
7499 ALLEN SALESMAN 1600
7844 TURNER SALESMAN 1500
已选择7行。
SQL> ed D:\sql\sample
SQL> @D:\sql\sample.sql
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7902 FORD ANALYST 3000
7566 JONES MANAGER 2975
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7839 KING PRESIDENT 5000
7499 ALLEN SALESMAN 1600
7844 TURNER SALESMAN 1500
已选择7行。
SQL> spool D:\sql\output
SQL> select empno, ename, job, sal
2 from emp
3 where sal >= 1500
4 order by job,sal desc
5 /
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7902 FORD ANALYST 3000
7566 JONES MANAGER 2975
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7839 KING PRESIDENT 5000
7499 ALLEN SALESMAN 1600
7844 TURNER SALESMAN 1500
已选择7行。
SQL> spool off;
当前语句存储在SQL缓冲区中，我们可以操作这条记录。
desc emp;
SQL> desc emp;
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(4)
ENAME VARCHAR2(10) Y
JOB VARCHAR2(9) Y
MGR NUMBER(4) Y
HIREDATE DATE Y
SAL NUMBER(7,2) Y
COMM NUMBER(7,2) Y
DEPTNO NUMBER(2) Y
SQL> desc dept;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14) Y
LOC VARCHAR2(13) Y
SET LINE[SIZE]{80|n}命令
eg: set line 100
SQL> select empno, ename, job, sal
2 from dept
3 Where sal >= 1500
4 order by job, sal desc;
Where sal >= 1500
*
第 3 行出现错误:
ORA-00904: "SAL": 标识符无效
SQL> L
1 select empno, ename, job, sal
2 from dept
3 Where sal >= 1500
4* order by job, sal desc
SQL> 2 from emp // n text 命令
SQL> L
1 select empno, ename, job, sal
2 from emp
3 Where sal >= 1500
4* order by job, sal desc
SQL> /
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7902 FORD ANALYST 3000
7566 JONES MANAGER 2975
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7839 KING PRESIDENT 5000
7499 ALLEN SALESMAN 1600
7844 TURNER SALESMAN 1500
已选择7行。
SQL> select ename
2 from emp;
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
KING
TURNER
JAMES
FORD
ENAME
----------
MILLER
已选择12行。
SQL>
SQL> L //List
1 select ename
2* from emp
SQL> 1 // n 设置当前行
1* select ename
SQL> a ,job,sal // a (append)附加
1* select ename,job,sal
SQL> L
1 select ename,job,sal
2* from emp
SQL> / // /orR is 执行
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
ALLEN SALESMAN 1600
WARD SALESMAN 1250
JONES MANAGER 2975
MARTIN SALESMAN 1250
BLAKE MANAGER 2850
CLARK MANAGER 2450
KING PRESIDENT 5000
TURNER SALESMAN 1500
JAMES CLERK 950
FORD ANALYST 3000
ENAME JOB SAL
---------- --------- ----------
MILLER CLERK 1300
已选择12行。
SQL> select empno, ename, job,sal
2 from emp
3 where sal >= 1500
4 order by job, sal desc;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7902 FORD ANALYST 3000
7566 JONES MANAGER 2975
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7839 KING PRESIDENT 5000
7499 ALLEN SALESMAN 1600
7844 TURNER SALESMAN 1500
已选择7行。
SQL> L
1 select empno, ename, job,sal
2 from emp
3 where sal >= 1500
4* order by job, sal desc
SQL> del 4
SQL> L
1 select empno, ename, job,sal
2 from emp
3* where sal >= 1500
SQL> /
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7499 ALLEN SALESMAN 1600
7566 JONES MANAGER 2975
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500
7902 FORD ANALYST 3000
已选择7行。
SQL> select empno, ename, job,sal
2 from dept
3 where sal >= 1500
4 order by job, sal desc;
where sal >= 1500
*
第 3 行出现错误:
ORA-00904: "SAL": 标识符无效
SQL> L
1 select empno, ename, job,sal
2 from dept
3 where sal >= 1500
4* order by job, sal desc
SQL> C /dept/emp
SP2-0023: 未找到字符串
SQL> 2
2* from dept
SQL> C /dept/emp
2* from emp
SQL> L
1 select empno, ename, job,sal
2 from emp
3 where sal >= 1500
4* order by job, sal desc
SQL> /
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7902 FORD ANALYST 3000
7566 JONES MANAGER 2975
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7839 KING PRESIDENT 5000
7499 ALLEN SALESMAN 1600
7844 TURNER SALESMAN 1500
已选择7行。
SQL> 4
4* order by job, sal desc
SQL> C /job,/
4* order by sal desc
SQL> L
1 select empno, ename, job,sal
2 from emp
3 where sal >= 1500
4* order by sal desc
SQL> /
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7839 KING PRESIDENT 5000
7902 FORD ANALYST 3000
7566 JONES MANAGER 2975
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7499 ALLEN SALESMAN 1600
7844 TURNER SALESMAN 1500
已选择7行。
如何生成脚本文件
SQL> select empno, ename, job, sal
2 from emp
3 where sal >= 1500
4 order by job, sal desc;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7902 FORD ANALYST 3000
7566 JONES MANAGER 2975
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7839 KING PRESIDENT 5000
7499 ALLEN SALESMAN 1600
7844 TURNER SALESMAN 1500
已选择7行。
SQL> save D:\SQL\SAMPLE
SP2-0110: 无法创建保存文件 "D:\SQL\SAMPLE.sql"
SQL> save D:\SQL\SAMPLE
已创建 file D:\SQL\SAMPLE.sql
SQL> L
1 select empno, ename, job, sal
2 from emp
3 where sal >= 1500
4* order by job, sal desc
如何编辑脚本文件
SQL> Get D:\Sql\SAMPLE.sql
1 select empno, ename, job, sal
2 from emp
3 where sal >= 1500
4* order by job, sal desc
SQL> L
1 select empno, ename, job, sal
2 from emp
3 where sal >= 1500
4* order by job, sal desc
SQL> /
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7902 FORD ANALYST 3000
7566 JONES MANAGER 2975
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7839 KING PRESIDENT 5000
7499 ALLEN SALESMAN 1600
7844 TURNER SALESMAN 1500
已选择7行。
SQL> ed D:\sql\sample
SQL> @D:\sql\sample.sql
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7902 FORD ANALYST 3000
7566 JONES MANAGER 2975
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7839 KING PRESIDENT 5000
7499 ALLEN SALESMAN 1600
7844 TURNER SALESMAN 1500
已选择7行。
SQL> spool D:\sql\output
SQL> select empno, ename, job, sal
2 from emp
3 where sal >= 1500
4 order by job,sal desc
5 /
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7902 FORD ANALYST 3000
7566 JONES MANAGER 2975
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7839 KING PRESIDENT 5000
7499 ALLEN SALESMAN 1600
7844 TURNER SALESMAN 1500
已选择7行。
SQL> spool off;
