sqlplus 配置方法及相关命令

浏览数：34 / 时间：2015年06月12日

1.配置文件

1.1 全局模式
什么叫全局模式呢：当我们配置完sqlplus工具加载配置文件后，无论在哪个目录下登陆数据库，您设置【sqlplus提示符样子，在任何目录下进入sqlplus工具都会加载此提示符样子】的效果都可以呈现出来
1.glogin.sql 配置文件的位置
答：$ORACLE_HOME/sqlplus/admin/glogin.sql
vim $ORACLE_HOME/sqlplus/admin/glogin.sql
2.添加的字符串，取默认变量
定位到这个文件的最后一行，回车另起一行的开头添加如下字符串
set sqlprompt "_user‘@‘_connect_identifier> "
或者 set sqlprompt ‘_user@&_connect_identifier> ‘
含义：_user 代表当前登陆用户名，_connect_identifier 代表当前数据库连接串名，在glogin.sql中设置全局sqlplus提示符样子user和connect identifier是sqlplus的默认变量
注：此时设置的是在所有目录下登陆sqlplus工具都会生效，@替换成【￥/#/&都可以】，从别的目录进入sqlplus也加载提示符变量
3.保存&退出
4.sqlplus / as sysdba
看现在的sqlplus提示符已经变化了，sys表示当前登录的是管理员用户，mdsoss是我的数据库连接串名，这样的话我们就可以在登录若干个数据库的时候不会发生混淆的情况了。
sys@mdsoss>

1.2 局部模式
什么叫局部模式呢：只在当前目录有效，例如我们在当前目录下配置login.sql文件，那么就在当前目录下进入sqlplus工具会加载login.sql文件提示符会生效，其他目录下登陆不生效
1.在当前目录下创建login.sql文件
例子 vim /home/oracle/login.sql
2.编辑login.sql文件，添加一行字符串
set sqlprompt "_user‘@‘_connect_identifier> "
或者 set sqlprompt ‘_user@&_connect_identifier> ‘
含义：_user 代表当前登陆用户名，_connect_identifier 代表当前数据库连接串名，在glogin.sql中设置全局sqlplus提示符样子user和connect identifier是sqlplus的默认变量
注：此时设置的是在当前目录下登陆sqlplus工具提示符生效，@替换成【￥/#/&都可以】，从别的目录进入sqlplus不加载提示符变量
3.保存&退出
4.sqlplus / as sysdba
sys@mdsoss>
5.局部生效测试版
如果我们在其他目录下登录sqlplus，这是我们已经发现了不同点，恢复到原始状态
sqlplus / as sysdba
SQL>

1.3 指定用户名和实例名方法
1.编辑glogin.sql和login.sql文件，在最后面添加如下内容
set term off
define user_name=""
define instance_name=""
column user_name new_value user_name
column instance_name new_value instance_name
select lower(user) user_name, instance_name instance_name from v$instance;
set sqlprompt ‘&user_name@&instance_name>‘
注释：--user_name 代表当前登陆的用户名，instance_name 代表当前登陆的实例名
sys@mdsoss> @可替换【￥/#/&都可以】
2.使用sqlplus登录数据库
这种方式在登陆和切换用户的时候，可指定用户名和实例名
enter value for user: liusheng
enter value for instance_name:leonarding
liusheng@leonarding>
小结：使用上述方法可以让我们在数据库海洋里游刃有余，平时练习的时候也可以加上，形成习惯，这样在连接不同的数据库时就不会导致不知道连接的是哪个库了。

2. 帮助
2.1. sqlplus命令程序帮助
sqlplus -V 显示版本信息
sqlplus -help
一个有意思的选项是-S，这个选项设置无提示模式, 该模式隐藏命令的 SQL*Plus 标帜, 提示和回显的显示。这个选项在报表输出或者spool的时候特别有用，不显示sqlplus的banner之类的信息。
2.2. sqlplus命令行帮助
help index 显示全部帮助主题，4列显示。
help topics 显示全部帮助主题。
help show 显示show命令帮助主题。

3. 显示系统变量(SET变量)
当你需要查看当前使用的SET设置的变量情况的时候，比如show pagesize显示当前的页面大小设置。

4. 语句、脚本的执行
4.1. 可以运行的脚本
sqlplus一共可以运行一下3类命令：
sql、plsql语句块和sqlplus程序的命令（设置sqlplus，编辑和保存sql和plsql语句）。
执行过的命令将被保存到缓存区当中，但是不保存sqlplus命令和执行命令的斜号和分号。
4.2 执行命令的结束符
也有3个结束符
分号";"，任何你想结束输入并运行语句的地方。
斜号"/"，只能在结束输入后，另起的单独一行输入以运行语句。
空格" "，结束输入，但是不运行语句。
点号"."，用于结束plsql语句块输入并执行。
"/"和"run"，用于执行缓存区当中的最新的脚本或者语句。
4.3 执行外部脚本
@ file
运行指定的脚本，可以是本地也可以是url上的脚本。
@@ file
运行指定的脚本，可以是本地也可以是url上的脚本，与@的区别，常常用于脚本当中，递归调用相同目录下的脚本。
start
同@ file
4.4. 在启动sqlplus的时候执行脚本

5. 执行操作系统命令
可以在sqlplus当中执行操作系统命令，这个功能非常使用，比如在系统诊断做个trace的时候，需要查看相应的文件，需要退出sqlplus到操作系统当中查看，这个就非常麻烦了低效了。其实这类情况下我们可以直接在sqlplus当中执行相应的操作系统命令，只要在命令前使用 host 或者 !都可以。
[oracle@gtlions ~]$ sqlplus store/store

SQL*Plus: Release 11.2.0.1.0 Production on 星期日 7月 31 21:34:22 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

store@TEST11G> select count(*) from dual;

COUNT(*)
----------
1

store@TEST11G> ! cat 1.sql
select * from emp;
@@2&3

store@TEST11G> host cat 2.sql
select * from dept;

store@TEST11G> host pwd
/home/oracle

store@TEST11G>
注意在这里执行的任何操作系统环境变量设置都不会影响当前会话。

6. 使用脚本
6.1. 设置和使用编辑器
创建和执行脚本是基本的操作，sqlplus会调用默认的操作系统的文本编辑器来编辑脚本。windows当中默认是notepad，linuxm默认是vi，可以设置在操作系统环境变量、sqlplus配置参数中。
检查默认的编辑器
store@TEST11G> define
DEFINE _DATE           = "31-7月 -11" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "TEST11G" (CHAR)
DEFINE _USER           = "STORE" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
方法1，通过设置操作系统环境变量
sys@TEST11G> ! cat .bash_profile
# .bash_profile
.。。省略N行

export EDITOR=vi #设置sqlplus默认编辑器为vi
方法2，设置登录脚本
define_editor=D:\Program Files\Green\Notepad++\ansi\notepad++.exe
我们可以在sqlplus中使用edit 1来启动编辑器编辑脚本，默认会在当前路径下创建一个1.sql文件，sql是默认扩展名。
注意：每条sql语句必须使用分号结束；可以在脚本中使用多条sql语句；可以在脚本中编写plsql语句块，测试中，使用2块语句的时候只执行了最后一个语句块。

7. 缓存区

7.1. list
列出缓存区的命令，可用简写l
store@TEST11G> list 列出缓存区所有行
1 select
2 ename,empno
3 from emp
4 where 1=1
5 and 2=2
6* and 3=3
store@TEST11G> list 5 列出第5行
5* and 2=2
store@TEST11G> list 2 4 列出第2-4行
2 ename,empno
3 from emp
4* where 1=1
store@TEST11G> list * 列出当前行
4* where 1=1
store@TEST11G> list 1 * 列出第1行到当前行
1 select
2 ename,empno
3 from emp
4* where 1=1
store@TEST11G> list * 5 列出当前行到第5行
4 where 1=1
5* and 2=2
store@TEST11G> list * last 列出当前行到最后一行
5 and 2=2
6* and 3=3
store@TEST11G> list last 列出最后一行
6* and 3=3
7.2. append
向当前行追加，可用简写a。
7.3. change
修改当前行，可用简写c。
store@TEST11G> list
1 select
2 ename,empno
3 from emp
4 where 1=1
5 and 2=2
6* and 3=3
store@TEST11G> list 4
4* where 1=1
store@TEST11G> append 1 --注意在第1个空格之后就是你要追加的内容，如果你追加的内容需要空格的时候就会注意到这点
4* where 1=1 1
store@TEST11G> append =1
4* where 1=1 1=1
store@TEST11G> change /1=1 1=1/1=1 and 1=1/
4* where 1=1 and 1=1
store@TEST11G> change / and 1=1/--注意如果没有新增的话就变成的输出指定文本的功能
4* where 1=1
change查找第1次匹配到的字符并修改。
7.3.1. 快速修改当前行
可以输入要行号加空格接着输入要替换本行的内容。
7.4. del
删除当前行,这个命令还有与list一样的参数。
7.5. input
在当前行之后添加行，可用简写i。有2种方式：i text，这个方式只是追加，并不会执行追加后的脚本；i 回车 text，如果以分号作为输入结束，这个情况下会追加并执行最终版本的脚本，如果不想要执行追加后的版本，使用回车（2个）结束输入。
store@TEST11G> l
1 select
2 ename,empno
3 from emp
4 where 1=1
5 and 2=2
6* and 3=3
store@TEST11G> input
7 and 4=5;

未选定行

store@TEST11G> input and 5=5
store@TEST11G> l
1 select
2 ename,empno
3 from emp
4 where 1=1
5 and 2=2
6 and 3=3
7 and 4=5
8* and 5=5
store@TEST11G> input and 1=1;
store@TEST11G> l
1 select
2 ename,empno
3 from emp
4 where 1=1
5 and 2=2
6 and 3=3
7 and 4=5
8 and 5=5
9* and 1=1
store@TEST11G> list 2
2* ename,empno
store@TEST11G> input 3=7
store@TEST11G> l
1 select
2 ename,empno
3 3=7
4 from emp
5 where 1=1
6 and 2=2
7 and 3=3
8 and 4=5
9 and 5=5
10* and 1=1
添加成第1行
store@TEST11G> l
1 select
2 ename,empno
3 3=7
4 from emp
5 where 1=1
6 and 2=2
7 and 3=3
8 and 4=5
9 and 5=5
10 and 1=1
11 and 1=1
12* change /3/
store@TEST11G> 0 select
store@TEST11G> l
1 select
2 select
3 ename,empno
4 3=7
5 from emp
6 where 1=1
7 and 2=2
8 and 3=3
9 and 4=5
10 and 5=5
11 and 1=1
12 and 1=1
13* change /3/
7.6. 注意
除了带参数的list和del，其他的命令只对当前行生效。
7.7. 如何决定当前行
list n，则当前行为第n行。
list列出和run执行当前缓存区命令，则当前行为最后一行。注意使用"/"执行脚本的时候，不会改变当前行。
出现错误，则当前行为错误所在的行，这样是为了方便编辑错误行的内容。
8. 注释
注释有3种方式：remark、/**/、--。
store@TEST11G> edit 1

remark 注释1
remark 注释1
remark 不能跨行,不能用在单个sql语句之间
/* 注意在"/*"之后要有一个空格，可以跨
行，但是不能被嵌套，可以用在任何行上。
*/
-- 单独行的注释，可以用在任何行上。
remark 注释1
select * from
dual
/* 注释
2*/
where --注释3
1=1;
remark 注释4

store@TEST11G> @1

DUM
---
X

store@TEST11G> l
1 select * from
2 dual
3 /* 注释
4 2*/
5 where --注释3
6* 1=1

9. 使用交互式命令

9.1. 定义用户变量 define
store@TEST11G> define df1
SP2-0135: 符号 df1 未定义
store@TEST11G> define df1=1
store@TEST11G> define
DEFINE _DATE           = "02-8月 -11" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "TEST11G" (CHAR)
DEFINE _USER           = "STORE" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1102000100" (CHAR)
DEFINE GNAME           = "store@TEST11G" (CHAR)
DEFINE DF1             = "1" (CHAR)
store@TEST11G> select * from dual where 1=&df1;
原值    1: select * from dual where 1=&df1
新值    1: select * from dual where 1=1

DUM
---
X
使用define可以查看所有的define变量，注意define和variable的区别。另外在引用的时候不管是使用&还是&&都无需再次输入值。
如果要附加字符，用点号：
store@TEST11G> select * from dual where 100=&x.0;
输入 x 的值: 10
原值    1: select * from dual where 100=&x.0
新值    1: select * from dual where 100=100

DUM
---
X

9.2 定义用户变量 variable
store@TEST11G> variable v1 number;
store@TEST11G> exec :v1:=1;

PL/SQL 过程已成功完成。

store@TEST11G> print v1

        V1
----------
         1

store@TEST11G> select :v1 from dual;

       :V1
----------
         1

store@TEST11G> declare
2 begin
3 select 23 into :v1 from dual;
4 end;
5 /

PL/SQL 过程已成功完成。

store@TEST11G> print v1

        V1
----------
        23

define相当于定义一个字符常量，通过define定义之后，在通过&或者&&引用的时候不需要输入了。oracle在执行的时候自动用值进行了替换；
variable定义的是绑定变量。
9.3 &和&&
&在当前sql语句生效，每次执行的时候都需要提示用户输入新值；&&在当前session当中一直有效，无需每次输入。
9.4 替换变量相关的系统变量
set define 替换变量的字符，默认是&
set esacpe 转移字符，默认是\
set verify on 是否列出替换变量的前后提示行
set concat 与变量连接的字符，默认是.
9.5 使用START命令提供值
store@TEST11G> l
1* select * from dba_tables where owner in (‘&1‘, ‘&2‘)
store@TEST11G> start 1.sql sys system
原值    1: select * from dba_tables where owner in (‘&1‘, ‘&2‘)
新值    1: select * from dba_tables where owner in (‘sys‘, ‘system‘)
可以在start filename 之后带上参数值，注意匹配数量。
9.6 交互输入
prompt和accept，prompt主要用于提示用户输入一个值，而accept用于接收prompt定义的用户的输入值。
store@TEST11G> edit 1

prompt 请输入报表名称
accept rptitle prompt ‘报表名称：‘
TTITLE rptitle skip 2
accept empnonew prompt ‘请输入要过滤的EMPNO值：‘
select empno,ename from emp where empno>&empnonew;
store@TEST11G> @1
请输入报表名称
报表名称：职员表
请输入要过滤的EMPNO值：7000
原值    1: select empno,ename from emp where empno>&empnonew
新值    1: select empno,ename from emp where empno>7000

职员表

     EMPNO ENAME
---------- ------------------------------
      7369 SMITH
      7499 ALLEN

10. 使用绑定变量
store@TEST11G> var ref1 refcursor
store@TEST11G> declare
2 begin
3 open :ref1 for select empno,ename from emp where rownum<5;
4 end;
5 /

PL/SQL 过程已成功完成。

store@TEST11G> print ref1;

职员表

     EMPNO ENAME
---------- ------------------------------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES

store@TEST11G> var v1 number;
store@TEST11G> exec :v1:=100;

PL/SQL 过程已成功完成。

store@TEST11G> print v1

职员表

        V1
----------
       100

store@TEST11G>

11. 格式化sqlplus
11.1. 格式化列
store@TEST11G> column empno heading ‘emp|id number‘--可以用|来换行
store@TEST11G> column ename heading ‘emp name‘
store@TEST11G> select empno,ename from emp where rownum<3;

职员表

       emp
id number emp name
---------- ------------------------------
      7369 SMITH
store@TEST11G> !cat 1.sql
set linesize 120
--设置被换行之后使用-标记
set recsep wrapped
set recsepchar "-"
--定义列标题
column dname heading ‘所在部门‘
column empno heading ‘员工编号‘
column ename heading ‘员工名称‘
column sal heading ‘收入情况‘
--定义列格式
column dname format a9 word_wrapped
column sal format $99999.990
--复制dname的列格式
column loc like dname heading ‘部门所在|城市‘
select a.dname,a.loc,b.empno,b.ename,b.sal from dept a,emp b where a.deptno=b.deptno;
--column【列名】显示列格式定义，column【列名】clear清除列格式定义

store@TEST11G> @1.sql

          部门所在
所在部门城市        员工编号员工名称                          收入情况
--------- --------- ---------- ------------------------------ -----------
ACCOUNTIN NEW YORK        7782 CLARK                            $2450.000
G
------------------------------------------------------------------------------------------------------------------------
ACCOUNTIN NEW YORK        7839 KING                             $5000.000
G
------------------------------------------------------------------------------------------------------------------------
ACCOUNTIN NEW YORK        7934 MILLER                           $1300.000
G
------------------------------------------------------------------------------------------------------------------------
RESEARCH DALLAS          7566 JONES                            $2975.000
RESEARCH DALLAS          7902 FORD                             $3000.000
RESEARCH DALLAS          7876 ADAMS                            $1100.000
RESEARCH DALLAS          7369 SMITH                             $800.000
RESEARCH DALLAS          7788 SCOTT                            $3000.000
SALES     CHICAGO         7521 WARD                             $1250.000
SALES     CHICAGO         7844 TURNER                           $1500.000
SALES     CHICAGO         7499 ALLEN                            $1600.000
SALES     CHICAGO         7900 JAMES                             $950.000
SALES     CHICAGO         7698 BLAKE                            $2850.000
SALES     CHICAGO         7654 MARTIN                           $1250.000

已选择14行。
11.2. 中断和合计
11.2.1. 中断break和计算compute
--第1行是设置deptno和job，当遇到相同值的时候中断另起一行；另外设置了report中断
--第2-4行是设置上述3个中断的合计值，注意必须要有break之后才能compute【sum maxinum mininum avg count number std variance】
--break显示所有的中断设置，break 列名显示指定列的中断设置，clear breaks清除中断设置；compute一样的语法
store@TEST11G> break on deptno skip 1 on job skip 1 on report
store@TEST11G> compute sum label deptsal of sal on deptno
store@TEST11G> compute sum label jobsal of sal on job
store@TEST11G> compute sum label totalsal of sal on report
store@TEST11G> select deptno,job,ename,sal from emp order by 1,2;

    DEPTNO JOB                         ENAME                                 SAL
---------- --------------------------- ------------------------------ ----------
        10 CLERK                       MILLER                               1300
           ***************************                                ----------
           jobsal                                                           1300

           MANAGER                     CLARK                                2450
           ***************************                                ----------
           jobsal                                                           2450

           PRESIDENT                   KING                                 5000
           ***************************                                ----------
           jobsal                                                           5000

**********                                                            ----------
deptsal                                                                     8750

        20 ANALYST                     SCOTT                                3000
                                       FORD                                 3000
           ***************************                                ----------
           jobsal                                                           6000

           CLERK                       ADAMS                                1100
                                       SMITH                                 800
           ***************************                                ----------
           jobsal                                                           1900

           MANAGER                     JONES                                2975
           ***************************                                ----------
           jobsal                                                           2975

**********                                                            ----------
deptsal                                                                    10875

        30 CLERK                       JAMES                                 950
           ***************************                                ----------
           jobsal                                                            950

           MANAGER                     BLAKE                                2850
           ***************************                                ----------
           jobsal                                                           2850

           SALESMAN                    MARTIN                               1250
                                       WARD                                 1250
                                       ALLEN                                1600
                                       TURNER                               1500
           ***************************                                ----------
           jobsal                                                           5600

**********                                                            ----------
deptsal                                                                     9400

                                                                      ----------
totalsal                                                                   29025

已选择14行。
11.3. 定义页报表标题
store@TEST11G> ttitle left "员工报表页头" SQL.PNO
store@TEST11G> btitle skip 1 left "员工报表页尾"
store@TEST11G> repheader center "报表头" skip 1 center "小标题"
store@TEST11G> repfooter "报表尾"

store@TEST11G> ttitle off
store@TEST11G> btitle off
store@TEST11G> repheader off
store@TEST11G> repfooter off

12. sqlplus调优
12.1. 跟踪执行计划
store@TEST11G> set autotrace autotrace
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

SET AUTOTRACE OFF 不生成 AUTOTRACE报告。这是默认值。
SET AUTOTRACE ON AUTOTRACE报告中即包括优化器执行路径也包括SQL语句执行统计。
SET AUTOTRACE ON EXPLAIN AUTOTRACE报告中只显示优化器执行路径。
SET AUTOTRACE ON STATISTICS AUTOTRACE报告中只显示SQL语句执行统计。
SET AUTOTRACE TRACEONLY 类似于 SET AUTOTRACE ON，但禁止显示用户的查询输出（如果有的话）。如果启用 STATISTICS 的话，仍然会提取数据但并不打印。
set autot trace stat 组合使用
12.2. 显示时间
set timing on 在每次执行输出结果之后显示本次执行时间
timing start t1
show
stop 这种方式可以嵌入到脚本当中根据需要显示时间