一、替换变量
二、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
----------
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
----------
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
----------
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
----------
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(*)
----------
7
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:dbathink@hotmail.com
尖峰官网：
尖峰淘宝：
WEIBO：
尖峰OCP认证考试群297227448
尖峰OCM认证考试群99606943
尖峰MySQL研究院群314746420
尖峰JAVA研究院群
315405063
尖峰Hadoop研究院群366294602
尖峰线上技术分享群252296815
尖峰SQL优化研究院群250057366
阅读(1829) | 评论(0) | 转发(0) |
