SQLPlus环境设置

一、替换变量
二、SET命令
三、格式控制命令
四、配置环境脚本

************************************
替换变量
************************************
一、替换变量

1.当我们在SQL*Plus中执行命令时，可以使用替换变量，让用户每次执行语句前，为语句传送不同的数据。例如：

gyj@OCM> select * from t1 where id=&id;
Enter value for id: 1 --Oracle首先让我们输入id的值，此处输入1
old 1: select * from t1 where id=&id
new 1: select * from t1 where id=1

　 ID NAME
---------- ----------

1 gyj1

在你输入值后，Oracle将&id换为你所输入的值，然后，按你所输入的值执行。这就是替换变量。同样的一条语句，你可以每次输入不同的值，返回的就是不同的结果。其实替换变量类似于编程语言中的变量的简单形式。

2.一条语句中，可以有多个替换变量，而且，一条语句中，除了开头第一个单词，语句中的任何一部分，都可以是替换变量。比如：

gyj@OCM> select * from t1 where &a &b &c;
Enter value for a: id
Enter value for b: =
Enter value for c: 1
old 1: select * from t1 where &a &b &c
new 1: select * from t1 where id = 1

　　ID NAME
---------- ----------

1 gyj1

（显示结果和上面一样）

3.除了语句的第一个单词外，我甚至可以将整个语句，都定为替换变量：

gyj@OCM> select &a &b &c &d &e &f &g;
Enter value for a: *
Enter value for b: from
Enter value for c: t1
Enter value for d: where
Enter value for e: id
Enter value for f: =
Enter value for g: 1
old 1: select &a &b &c &d &e &f &g
new 1: select * from t1 where id = 1

　　ID NAME
---------- ----------

1 gyj1

注意，SELECT一定不可以是替换变量。语句中第一个单词不可以是替换变量，其他部分都可以。因此替换变量的使用是非常灵活的。

4.对于字符型的数据，要注意单引号的问题，我想显示姓名等于某个人的行，可以使用如下语句：

gyj@OCM> select * from t1 where name='&n';
Enter value for n: gyj1
old 1: select * from t1 where name='&n'
new 1: select * from t1 where name='gyj1'

　　ID NAME
---------- ----------

1 gyj1

注意:我在’&n’外加的有单引号，那么，我在为n输入值时，就不必再gyj1的外面，加单引号。

如果&n的外面，我没有加单引号的话，如下：

gyj@OCM> select * from t1 where name=&n;
Enter value for n: 'gyj1'
old 1: select * from t1 where name=&n
new 1: select * from t1 where name='gyj1'

　　 ID NAME
---------- ----------

1 gyj1

（显示结果同上）
注意: &n外面没有单引号，那么在输入gyj1时，就要在gyj1之外，加单引号。

二、替换变量的定义和取消
我们可以使用define 变量 = 值，事先定义替换变量。如SQL> define a=1 ，这条语句后面可以加“;”，也可以不加“;”号。

定义变量后，可以在任何地方通过&a来引用变量，如：

gyj@OCM> define a=1
gyj@OCM> select * from t1 where id=&a;
old 1: select * from t1 where id=&a
new 1: select * from t1 where id=1

　　 ID NAME
---------- ----------

1 gyj1

和以前使用替换变量相比，少了“输入 a 的值: ”，这次，a的值在前面已经用define定义过了，不必再输入。
变量a可以返复使用，直到使用undefine 变量命令取消它。下面试一下。

我先用如下语句输出a的值：

gyj@OCM> select &a from dual;
old 1: select &a from dual
new 1: select 1 from dual

　　1
----------

a的值目前还存在。在使用时，不须先为a输入值。下面我取消a。
SQL> undefine a

取消之后，我再次执行和上面同样的命令，输出a的值：

gyj@OCM> undefine a
gyj@OCM> select &a from dual;
Enter value for a:
（这次要求我输入a的值了，因为a已经被取消）
还一点要说明一下，就是替换变量只针对一个会话，在A会话中定义的值的变量，在B会话访问不到它的值。下面我们试一下：
在会话1：SQL> define a=1
在会话1：SQL> select &a from dual;
old 1: select &a from dual
new 1: select 1 from dual

　　1
----------

已经可以使用变量a了。下面换到会话2：
在会话2：SQL> select &a from dual;
输入 a 的值:（要求你重新为a输入值，在会话1中为变量定义的值，在会话2中访问不到）

三、“&&”与替换变量：

一个“&”（念and）号的替换变量，如果以前没有Define定义过。在本次使用完后，将自动取消。而双“&&”号的替换变量，在本次使用完后，输入的值将一直保持，直到用Undefine取消为止。测试如下：

gyj@OCM> select &&a from dual;
Enter value for a: 1
old 1: select &&a from dual
new 1: select 1 from dual

　　1
----------

第一次为&&a输入值为1，再次显示a的值，无论是select &&a from dual; 还是select &a from dual; 都不需要再为a输入值。直到undefine。

四、SET VERIFY ON | OFF
在替换变量使用过程中，每次都会显示一个“原值”，然后显示被命令替换成了“新值”，使用SET VERIFY，可以打开或关闭这个提示信息。默认状态是打开。我把它关闭看看效果：
SQL> set verify off
　说明一下，普通的命令，在结尾处都应有一个“;”分号，而Define、Undefine和SET开头的命令，这些命令专门针对SQL*Plus，在其他环境中不能使用。这些专属于SQL*Plus的命令，不必在结尾处加分号。当然如果结尾加分号了，也不会报错。

　我已经把SET VERIFY设为了OFF，再使用一次a变量：

gyj@OCM> set verify off
gyj@OCM> select &a from dual;

　　1
----------

果然，没有了“原值”，“新值”这样的提示信息。像SET VERIFY这样的用来设置SQL*Plus状态的命令，还有很多，我们下面介绍。

*************************************
SET命令
*************************************
一、SET和SHOW命令
在SQL*Plus中，有一些状态变量，控制一些命令的输出格式，例如，VERIFY就是一个状态变量。它控制在使用替换变量时，是否显示的替换有关的提示性信息。我们可以使用SET命令，可以针对某一会话，设置SQL*Plus的状态变量。改变的状态只针对发出SET命令的会话，对其他会话没有任何的影响。使用SHOW命令，可以显示状态变量当前的取值。例如：
gyj@OCM> show verify
verify OFF
我显示了VERIFY状态变量的当前值，它的值是OFF。除了VERIFY之外，我们再说几个比较常用的状态变量。

二、FEEDBACK 回馈信息

gyj@OCM> show FEEDBACK;
FEEDBACK ON for 6 or more rows
gyj@OCM> select * from t1;

　ID NAME
---------- ----------
2 gyj2
3 gyj3
4 gyj4
5 gyj5
6 gyj6
1 gyj1

6 rows selected.

最后有个“已选择6行”，这个信息，被称为回馈信息。而FEEDBACK，就是针对这个信息的。它的初始值是6。就是当你显示出来的行数大于等6时，才会显示这个回信息。

下面我显示一个5行的表：

gyj@OCM> delete from t1 where id=6;

1 row deleted.

gyj@OCM> commit;

Commit complete.

gyj@OCM> select * from t1;

　ID NAME
---------- ----------
2 gyj2
3 gyj3
4 gyj4
5 gyj5

1 gyj1

没有回馈信息。因为显示的行数没有达到要求的6。

下面我们把这个状态变量设制的小一点：

gyj@OCM> set feedback 3
gyj@OCM> select * from t1;

　ID NAME
---------- ----------
2 gyj2
3 gyj3
4 gyj4
5 gyj5
1 gyj1

5 rows selected．--已经有了回馈信息。

很多时候，我们可能要选择关闭这个回馈信息。关闭命令如下：set feedback off 。而set feedback on，则是重新显示此回馈信息。
关闭回馈信息，在生成一些脚本时将很有用，自动生成备份脚本

用spool举个例子:

gyj@OCM> spool test.sql
gyj@OCM> select * from t1;

　 ID NAME
---------- ----------
2 gyj2
3 gyj3
4 gyj4
5 gyj5
7 gyj7
6 gyj6
1 gyj1
gyj@OCM> spool off;
gyj@OCM> !pwd

三、TERMOUT的使用

gyj@OCM>　show termout
termout ON --用＠运行脚本想在sql*plus中显示一下运行后结果
gyj@OCM> @/home/oracle/test.sql

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

SQL>set termout off --用＠运行脚本时不想在sql*plus中显示运行后的结果
gyj@OCM> @/home/oracle/test.sql
gyj@OCM>
说明set termout on/off 是控制@方式执行之返回的
它的作用，仍然是利用SQL*Plus生成一些脚本时使用。利用SQL*Plus自动生成备份或管理脚本。（参照spool）

四、HEADING 标头

在输出表时，如下：

gyj@OCM> select * from t1;

　　ID NAME
---------- ----------
2 gyj2

ID NAME
---------- ----------

这一部分，就是标头。HEADING控制着是否显示标头。它有两个值，ON和OFF，默认是ON。

gyj@OCM> set HEADING off;
gyj@OCM> select * from t1;

　　　　2 gyj2

它的作用，仍然是利用SQL*Plus生成一些脚本时使用。利用SQL*Plus自动生成备份或管理脚本。（参照spool）

******************************
格式控制
******************************
格式控制类命令和SET命令类似，对SQL*Plus的输出显示格式，进行一些控制。它只影响发出命令的会话，对其他会话没有任何作用。
一、控制列格式： COL[UMN] [{列名} [选项] ]
这个命令的全称是COLUMN，但在使用时，可以将UMN省略，简写为COL。选项的取值有：
CLE[AR]: 清除任何列格式
HEA[DING] text: 设置列标题
FOR[MAT] format: 用一个格式化模板改变列的显示
NOPRINT | PRINT
NULL
先从设置列标题开始：

1.HEA[DING] text: 设置列标题

gyj@OCM> select * from t1;

　　 ID NAME
---------- ----------
2 gyj2
3 gyj3

4 gyj4

比如，我现在想将id列的标头部分，显示为“xh”，可以如下设置：

gyj@OCM> col id hea xh
gyj@OCM> select * from t1;

　 xh NAME
---------- ----------
2 gyj2
3 gyj3

4 gyj4

使用“col id hea xh ”命令后，显示t1表，id的列标题已经发生了变化。它的作用有点像我们已前讲过的为列定义别名，如，我用别名的方式达到同样的效果：

gyj@OCM> select id xh,name xm from t1;

　XH XM
---------- ----------
2 gyj2
3 gyj3

4 gyj4

“name xm”，这其中xm，就是为name起的别名。别名的方式，只能在命令中指定，而COL命令，是在命令之外定义，它改变的仅仅是显示结果。

而别名不同，它不但改变了显示效果，而且在一些地方，别名可以代替原来的列名。如：

gyj@OCM> select id xh,name xm from t1 order by xh;

　 XH XM
---------- ----------
1 gyj1
2 gyj2

3 gyj3

我们可以在Order by 中使用别名。但如下：

gyj@OCM> col id heading xh
gyj@OCM> select * from t1 order by xh;
select * from t1 order by xh
*
ERROR at line 1:

ORA-00904: "XH": invalid identifier

这就不行了，因为“顾客编号”不是为列起的别名，它仅仅是改变了显示在标头中的字符。这是使用COL和列别名的根本区别。

2．FOR[MAT] format 改变列的长度或格式

改变列长度非常简单，使用的也非常多，例如：

gyj@OCM> select file_id,file_name from dba_data_files;

　FILE_ID FILE_NAME
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10 /u01/app/oracle/oradata/ocm/undotbs02.dbf
5 /u01/app/oracle/oradata/ocm/example01.dbf
4 /u01/app/oracle/oradata/ocm/tp3.dbf
3 /u01/app/oracle/oradata/ocm/undotbs01.dbf
2 /u01/app/oracle/oradata/ocm/sysaux01.dbf
1 /u01/app/oracle/oradata/ocm/system01.dbf
7 /u01/app/oracle/oradata/ocm/tp1_1.dbf
6 /u01/app/oracle/oradata/ocm/tp128.dbf

8 /u01/app/oracle/oradata/ocm/tp512.dbf

我们可以看到，file_name列占据很大的空间，但file_name列实际上并没有那么多内容，我们可以将file_name列设置的窄一些：

gyj@OCM> col file_name for a50;

将file_name列的宽度，定为50个字节。再显示dba_data_files表：

gyj@OCM> select file_id,file_name from dba_data_files;

　 FILE_ID FILE_NAME
---------- --------------------------------------------------
10 /u01/app/oracle/oradata/ocm/undotbs02.dbf
5 /u01/app/oracle/oradata/ocm/example01.dbf
4 /u01/app/oracle/oradata/ocm/tp3.dbf
3 /u01/app/oracle/oradata/ocm/undotbs01.dbf
2 /u01/app/oracle/oradata/ocm/sysaux01.dbf
1 /u01/app/oracle/oradata/ocm/system01.dbf
7 /u01/app/oracle/oradata/ocm/tp1_1.dbf
6 /u01/app/oracle/oradata/ocm/tp128.dbf
8 /u01/app/oracle/oradata/ocm/tp512.dbf

gyj@OCM> col file_name for a10;
gyj@OCM> select file_id,file_name from dba_data_files;

FILE_ID FILE_NAME
---------- ----------
10 /u01/app/o
racle/orad
ata/ocm/un
dotbs02.db
f

　　 5 /u01/app/o
racle/orad
ata/ocm/ex
ample01.db
　　　 f

　　 4 /u01/app/o
racle/orad
ata/ocm/tp

3.dbf

如果设的列的值设的比实际值小就会换行显示，这样就很难看了．．．

对于数字型的列，我们不能使用col for a10定义宽度，但是可以在FOR之后，可以使用格式字符，下面，看一个实验：

gyj@OCM> col salary for L99,999,999.99
gyj@OCM> select * from t2;

　　xh xm SALARY
---------- ---------- ------------------------
1 gyj1 $5,000.00
2 gyj2 $10,000.00
2 gyj2 $15,000.00

2 gyj2 $80,900,900.00

使用col salary for L99,999,999.99命令，将salary（薪水）列，的格式定为L99,999,999.99，L的作用是在数字前面显示本国货币符号，“9”的个数，决定了salary列的宽度。如果“9”的个数小于实际数据的宽度，数据将被显示为“#”号：

gyj@OCM> col salary for L999,999.99
gyj@OCM> select * from t2;

　　 xh xm SALARY
---------- ---------- ---------------------
1 gyj1 $5,000.00
2 gyj2 $10,000.00
2 gyj2 $15,000.00

2 gyj2 #####################

这一次，我将salary的显示格式定为了“L999,999.99”，整数部分，只有8个9，超过10000000的列数据就被显示为“#”号了。

3．CLE[AR]: 清除列格式

清除列格式，命令非常简单,命令“col salary cle”将salary的格式清除，显示结果又恢复了原样。

gyj@OCM> col salary cle
gyj@OCM> select * from t2;

　　ID NAME SALARY
---------- ---------- ----------
1 gyj1 5000
2 gyj2 10000
2 gyj2 15000
2 gyj2 80900900

4．NOPRINT | PRINT 是否显示指定列

PRINT是显示，ONPRINT是不显示。比如：

gyj@OCM> col salary noprint
gyj@OCM> select * from t2;

　　ID NAME
---------- ----------
1 gyj1
2 gyj2
2 gyj2

2 gyj2

“col salary noprint”，它的作用就是不显示salary列。“col salary print”将使此列重新显示。

5．NULL 文本：为NULL定义显示的文本,NULL通常是不显示的。比如说：

gyj@OCM> select * from t2;

　ID NAME SALARY
---------- ---------- ----------
1 gyj1 5000
6 gyj6
7 gyj7
8 gyj8
2 gyj2 8000
3 gyj3
4 gyj4

5 gyj5

SALARY列中的空值，都没有显示，下面我将这些NULL，显示为10000。

gyj@OCM> col salary null 10000
gyj@OCM> select * from t2;

ID NAME SALARY
---------- ---------- ----------
1 gyj1 5000
6 gyj6 10000
7 gyj7 10000
8 gyj8 10000
2 gyj2 8000
3 gyj3 10000
4 gyj4 10000
5 gyj5 10000

好了，列格式的控制，我们就说到这里。

二、BREAK ON 列名禁止连接重复值

就像我们上面的例子，select * from t2; ，它显示了很多10000。使用BREAK ON，可以让这些连接的重复值只显示一个：

gyj@OCM> break on salary
gyj@OCM> select * from t2;

　ID NAME SALARY
---------- ---------- ----------
1 gyj1 5000
6 gyj6 10000
7 gyj7
8 gyj8
2 gyj2 8000
3 gyj3 10000
4 gyj4
5 gyj5

我们再试一个例子，显示t3表：

gyj@OCM> select * from t3;

　ID NAME
---------- ----------
1 gyj1
1 gyj11
2 gyj2
2 gyj22
3 gyj3
3 gyj33
4 gyj4
4 gyj44

id也有连续的重复值，下面我用BREAK，取消id中连接的重复值：

gyj@OCM> break on id
gyj@OCM> select * from t3;

ID NAME
---------- ----------
1 gyj1
gyj11
2 gyj2
gyj22
3 gyj3
gyj33
4 gyj4
gyj44

很多时候，一些报表需要以这样的格式输出。

使用如下命令，将清除所有的BREAK设置：

gyj@OCM> clear break
breaks cleared
gyj@OCM> select * from t3;

　 ID NAME
---------- ----------
1 gyj1
1 gyj11
2 gyj2
2 gyj22
3 gyj3
3 gyj33
4 gyj4
4 gyj44

这个清除，将清除针对所有列进行的BREAK设置，Oracle没有提供只清楚某一列BREAK设置的命令。

三、TTITLE 、 BTITLE 和页眉与页脚
1．TTI[TLE] [text|OFF|ON]
设置页眉，text指定页眉内容，OFF的作用是关闭页眉，ON是恢复页眉的显示。
下面我来试一下：

sid=38 pid=17> tti '商品明细表'

gyj@OCM> tti 'student information'
gyj@OCM> select * from t1;

Tue Feb 19 page 1
student information
　 ID NAME
---------- ----------
2 gyj2
3 gyj3
4 gyj4
5 gyj5
7 gyj7
6 gyj6
1 gyj1

2．BTITLE [text|OFF|ON]
BTITLE 设置页脚，这个我不再试了。
页眉和页脚的显示，使用的非常少。因为使用SQL*Plus生成的报表，通常满足不了需要。

**********************************
　　配置环境脚本
**********************************
cd $ORACLE_HOME/sqlplus/admin
vi glogin.sql

define _editor=vi
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 1000
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
set termout off
define gname=idle
column global_name new_value gname
select lower(user) || '@' || substr( global_name, 1, decode( dot, 0, length(global_name), dot-1) ) global_name
from (select global_name, instr(global_name,'.') dot from global_name );
set sqlprompt '&gname> '
set termout on

下面对这些脚本做些说明：
define _editor=vi：设置sql*plus使用的默认编辑器。可以把默认编辑器设置为你最喜欢的文本编辑（而不是字处理器），如记事本（Notepad）或emacs。
set serveroutput on size 1000000:这会默认地打开dbms_output（这样就不必每次再键入这个命令了）。另外也将默认缓冲区大小设置得尽可能大。
set trimspool on：假脱机输出文本时，会去除文本行两端的空格，而且行宽不定。如果设置为OFF（默认设置），假脱机输出的文本行宽度则等于所设置的LINESIZE。
set long 5000：设置选对LONG或CLOB列时显示的默认字节数
linesize 1000：设置sql*plus显示的文本宽为1000字符。
set pagesize 9999：pagesize可以控制sql*plus多久打印一次标题，这里将pagesize设置为一个很大的数（所以每页只有一组标题）。
column plan_plus_exp format a80：设置由autotrace得出的解释计划输出(explain plan output)的默认宽度。A80通常足以放下整个计划。
set termout on/off:是控制@方式执行之返回的
define gname=idle:定义一个变量gname,值为idle。
column global_name new_value gname：告诉sql*plus取得global_name列中的最后一个值，并将这个值赋给替换变量gname。
select lower(user) || '@' || substr( global_name, 1, decode( dot, 0, length(global_name), dot-1) ) global_name
from (select global_name, instr(global_name,'.') dot from global_name );　取得global_name的值

set sqlprompt '&gname> '　：通常用来设置SQL提示符的方法

**********本博客所有内容均为原创，如有转载请注明作者和出处!!!**********
QQ: 252803295
Email:[email protected]
尖峰官网：http://www.jianfengedu.com
尖峰淘宝：http://jianfengedu.taobao.com
WEIBO：http://weibo.com/guoyJoe0218
尖峰OCP认证考试群297227448
尖峰OCM认证考试群99606943
尖峰MySQL研究院群314746420
尖峰JAVA研究院群315405063
尖峰Hadoop研究院群366294602
尖峰线上技术分享群252296815
尖峰SQL优化研究院群250057366