sqlplus用户指南和参考

1. 配置文件
sqlplus的配置文件主要有2个，全局配置文件glogin.sql和用户配置文件login.sql。
顾名思义glogin.sql是所有使用sqlplus的用户都会受到影响的配置文件，位于$ORACLE_HOME/sqlplus/admin。
而用户配置文件会在当前目录下查找，接着查找SQLPATH路径下的。
执行顺序是先执行全局配置文件，之后执行用户配置文件。
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 这种方式可以嵌入到脚本当中根据需要显示时间
13. sqlplus调优
主要涉及到几个参数的配置，比如禁用输出等等可以提够性能。
14. sqlplus安全

使用PUP（product_user_profile）可以配置用户在访问本地数据库的时候限制使用的命令。

-The End-