Chinaunix首页 | 论坛 | 博客
  • 博客访问: 845334
  • 博文数量: 72
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 435
  • 用 户 组: 普通用户
  • 注册时间: 2013-04-27 20:07
个人简介

郭一军,网名guoyJoe,尖峰在线培训创始人、Oracle OCM、ITPUB论坛Oracle认证版版主、2013最佳精华贴、Senior DBA、Oracle OCT、 ACOUG MEMBER、CSDN 认证专家、RHCE、在线技术分享的倡导者和实践者。 http://www.jianfengedu.com http://jianfengedu.taobao.com

文章分类
文章存档

2014年(72)

分类: Oracle

2014-06-17 09:39:00

一、替换变量
二、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


阅读(1742) | 评论(0) | 转发(0) |
0

上一篇:工作中使用了一些触发器

下一篇:函数

给主人留下些什么吧!~~