Oracle-04-SQL工具-sqlplus环境变量与命令

最新推荐文章于 2021-05-04 11:42:59 发布

原创最新推荐文章于 2021-05-04 11:42:59 发布 · 511 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sqlplus环境变量与命令

Oracle 专栏收录该内容

38 篇文章

订阅专栏

本文详细介绍了SQLPlus环境中各种高级特性的使用方法，包括替代变量的设置与使用、环境变量的控制、显示格式的定制、查询结果的处理等。通过实际操作示例，帮助读者深入理解如何高效利用SQLPlus进行数据查询与管理。

替代变量：

在select后

select &col1 from emp;

Enter value for col1: ename
old   1: select &col1 from emp
new   1: select ename from emp

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

14 rows selected.

Elapsed: 00:00:00.02

在from后

select * from &tab;

Enter value for tab: emp
old   1: select * from &tab
new   1: select * from emp

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

Elapsed: 00:00:00.01

在where后

select * from emp where &con;

Enter value for con: ename like 'S%'
old   1: select * from emp where &con
new   1: select * from emp where ename like 'S%'

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

Elapsed: 00:00:00.00

在order by 后

select * from emp order by &col;

Enter value for col: ename
old   1: select * from emp order by &col
new   1: select * from emp order by ename

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

14 rows selected.

Elapsed: 00:00:00.01

&&符号替代变量，隐含会设置环境变量，第一次会询问变量值，第二次在环境变量里面找，如果找到了就不会再次输入，直接用环境变量里面的值

第一次执行

select * from &&tab;

Enter value for tab: emp
old   1: select * from &&tab
new   1: select * from emp

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

Elapsed: 00:00:00.00

第二次输入

select * from &&tab;

old   1: select * from &&tab
new   1: select * from emp

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

Elapsed: 00:00:00.00

第二次输入就不会让用户再次输入&&tab的值，而是直接显示查询结果

查看环境变量

define

DEFINE _DATE           = "26-APR-18" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "sundb" (CHAR)
DEFINE _USER           = "ORACLE" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR         = "vim" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1102000100" (CHAR)
DEFINE TAB1            = "emp" (CHAR)
DEFINE TAB             = "emp" (CHAR)

发现环境变量中存在tab的值，如果要取消这个环境变量，下次让用户再次输入时可以用undefine命令

undefine tab

ORACLE@ sundb>define
DEFINE _DATE           = "26-APR-18" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "sundb" (CHAR)
DEFINE _USER           = "ORACLE" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR         = "vim" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1102000100" (CHAR)
DEFINE TAB1            = "emp" (CHAR)

当然也可以直接用define命令定义环境变量

SQLPLUS环境命令

select * from &tab;

Enter value for tab: emp
old   1: select * from &tab
new   1: select * from emp

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

Elapsed: 00:00:00.00

我们会发现这里会显示新旧值得对比，这个就是SQLPLUS环境变量控制的

show verify

verify ON

可以用set命令修改环境变量

set verify off

select * from &tab;

Enter value for tab: emp

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

Elapsed: 00:00:00.00

linesize每行的显示长度

show linesize

linesize 120

pagesize每页显示多少行，这里需要注意的是每页包括列头的两行和页尾的空行，所以显示的行数会比设置的少3行

show pagesize

pagesize 200

timing在查询的结果集后显示每次查询消耗的时间

show timing

timing ON

sqlprompt设置SQLPLUS的提示符

show sqlprompt

sqlprompt "_user@ _connect_identifier>"

arraysize查询结果集的预取结果条数

show arraysize

arraysize 15

feedback在结果集后打印一共查询到数据的行数

show feedback

FEEDBACK ON for 6 or more rows

heading在打印结果集时是否显示表头

show heading

heading ON

long显示long类型或者lob类型的显示长度，默认sqlplus字符只显示84个，超出的字符会被截断

show long

long 50000

column可以格式化一列的显示长度，数字格式用9做占位符，默认留出一位做负号和小数点显示位，column也可以修改列名

col ename for a10

col sal for 999999

col ename heading 'e_|name'

select * from emp;

           e_
     EMPNO name       JOB              MGR HIREDATE      SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80     800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81    1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81    1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81    2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81    1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81    2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81    2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87    3000                    20
      7839 KING       PRESIDENT            17-NOV-81    5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81    1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87    1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81     950                    30
      7902 FORD       ANALYST         7566 03-DEC-81    3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82    1300                    10

14 rows selected.

Elapsed: 00:00:00.00

这里 | 起到了列头换行的作用

同样可以用column ename来查看格式化的内容

column ename

COLUMN   ename ON
HEADING  'e_|name' headsep '|'
FORMAT   a10

可以用column ename clear取消之前的格式化内容

column ename clear

column sal justify left确定列头的对齐方式

column sal justify left format L999,999.99

select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE  SAL                         COMM     DEPTNO
---------- ---------- --------- ---------- --------- --------------------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80               $800.00                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81             $1,600.00        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81             $1,250.00        500         30
      7566 JONES      MANAGER         7839 02-APR-81             $2,975.00                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81             $1,250.00       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81             $2,850.00                    30
      7782 CLARK      MANAGER         7839 09-JUN-81             $2,450.00                    10
      7788 SCOTT      ANALYST         7566 19-APR-87             $3,000.00                    20
      7839 KING       PRESIDENT            17-NOV-81             $5,000.00                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81             $1,500.00          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87             $1,100.00                    20
      7900 JAMES      CLERK           7698 03-DEC-81               $950.00                    30
      7902 FORD       ANALYST         7566 03-DEC-81             $3,000.00                    20
      7934 MILLER     CLERK           7782 23-JAN-82             $1,300.00                    10

14 rows selected.

Elapsed: 00:00:00.00

column comm null 0

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800 0                  20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975 0                  20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850 0                  30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450 0                  10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000 0                  20
      7839 KING       PRESIDENT no manager 17-NOV-81       5000 0                  10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100 0                  20
      7900 JAMES      CLERK           7698 03-DEC-81        950 0                  30
      7902 FORD       ANALYST         7566 03-DEC-81       3000 0                  20
      7934 MILLER     CLERK           7782 23-JAN-82       1300 0                  10

14 rows selected.

Elapsed: 00:00:00.00

break on设置重复值列的连续显示

break on mgr

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN             22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER              09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT no manager 17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

Elapsed: 00:00:00.00

所有mgr相同的行mgr只显示一次

取消可以用

clear break

breaks cleared

run（/）运行缓冲区里面的最后一条sql语句

list 查看缓冲区里面的sql语句

append 在缓冲区sql后添加字符

save保存缓冲区里面的sql

save 1.sql

Created file 1.sql

get 查看文件内容

@执行sql脚本，@1和@1.sql是一样的

spool可以将屏幕上显示的信息保存到文件里，用spool off命令结束，后面可以跟append或者replace追加或者替换之前的文件内容

spool 1.txt

select *from emp where rownum=1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

Elapsed: 00:00:00.00