oracle英文单词意思是"神谕"，据说当时取这个名字是希望oracle数据库能像神谕一样为他们指点迷津，快速找到所需要的信息。

sqlplus我们程序员"聆听神谕"的一个重要工具，掌握好这个工具对于提高我们工作效率有着重要的意义。

最近一段时间由于工作需要，经常使用sqlplus进行数据查询。我总结了一些经验，在此与大家分享：

一、 变量

很多人都知道可以在sqlplus中，可以使用define语句定义一个变量，然后使用&来引用它。关于定义变量，还有很多相关的知识点：

1 &和&&的区别

&与&&均可以定义变量， 但是&定义的变量是临时性的，每次遇到这个变量时，都会提示你输入一个值。 而&&定义的变量赋一次值后，再遇到此变量不会要求重新输入该变量的值。

sys@ORCL>select &var_tmp, &var_tmp, &&var, &&var from dual;

sys@ORCL>select &var_tmp, &var_tmp, &&var, &&var from dual;输入 var_tmp 的值: 1输入 var_tmp 的值: 1输入 var 的值: 2原值 1: select &var_tmp, &var_tmp, &&var, &&var from dual新值 1: select 1, 1, 2, 2 from dual  1 1 2 2---------- ---------- ---------- ---------- 1 1 2 2;

默认情况下sqlplus会显示替换前后的sql语句，但大多数情况下我们不需要看到这些，可以用set verify off来关闭这个显示

sys@ORCL>set verify offsys@ORCL>select &&var from dual;  2---------- 2

&&定义的变量有时候我们也需要重新修改，这个时候可以通过UNDEFINE

sys@ORCL>undefine varsys@ORCL>select &&var from dual;输入 var 的值: 1  1---------- 1

2 关闭变量

有时候我们需要往数据库插入含有'&'的数据，这个时候sqlplus的变量功能就会给我们造成麻烦了。这个时候我们可以选择使用set define来修改标识变量开始的前缀字符或关闭变量功能。

sys@ORCL>set define #sys@ORCL>select '&var_first', '#var_second' from dual;输入 var_second 的值: 1原值 1: select '&var_first', '#var_second' from dual新值 1: select '&var_first', '1' from dual '&VAR_FIRS '---------- -&var_first 1 sys@ORCL>set define offsys@ORCL>select '&var_first', '#var_second' from dual; '&VAR_FIRS '#VAR_SECON---------- -----------&var_first #var_second

但偶尔也有一行语句有的需要绑定变量，有的不需要的情况。这个时候我们可以通过转义字符来处理。

sys@ORCL>set escape \sys@ORCL>select '\&var', &var_tmp from dual;输入 var_tmp 的值: 2原值 1: select '\&var', &var_tmp from dual新值 1: select '&var', 2 from dual '&VA 2---- ----------&var 2

3 关于变量赋值方法

我们平时比较常见的变量赋值方法是通过define语句给变量赋值，或者由用户在前台输入。但是如果要求变量从sql查询结果中获取，或者对用户输入的变量格式有限制（例如只能输入数字）怎么办呢？ 答案是使用column和accept命令。

下面演示使用column命令从查询结果中获得数据库实例名。

sys@ORCL>--定义global_name列值赋给变量gnamesys@ORCL>column global_name new_value gnamesys@ORCL>col global_name for a30sys@ORCL>---执行sql 获取实例名sys@ORCL>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------------------------------sys@ORCL sys@ORCL>--显示gname的值sys@ORCL>define gname;DEFINE GNAME = "sys@ORCL" (CHAR)

ACCEPT 命令作用是读取一行用户输入赋值给变量，其用法是：

ACC[EPT] variable [NUM[BER] | CHAR | DATE | BINARY_FLOAT | BINARY_DOUBLE] [FOR[MAT] format] [DEF[AULT] default] [PROMPT text|NOPR[OMPT]] [HIDE]

下面演示要求用户输入数字的用法：

sys@ORCL>acc num number format 9 def 2 prompt 'Please Enter the Num:'Please Enter the Num:2sys@ORCL>--注意这里使用了.号连接字符串ToChinese_is，如果不喜欢这个符号，可以使用SET CONCAT [. | c | ON | OFF}?修改sys@ORCL>select '&num.ToChinese_is:' || value from num_to_Chinese where num = &# '2TOCHINESE_IS:'||VALUE------------------------------------------------------- 2ToChinese_is:二

4 使用绑定变量

通过define或column定义的变量，在实际运行时sqlplus是在客户端使用变量的值替换了sql语句后再发到服务器执行的（从v$sql我们可以验证）。

sys@ORCL>select distinct sql_text from v$sql where sql_text like '%&key_word%';输入 key_word 的值: hchtestnow SQL_TEXT------------------------------------------------------------------------------------------------------------------------------------------------------select distinct sql_text from v$sql where sql_text like '%hchtestnow%'sys@ORCL>--从结果可以看出，我们输入的变量值会直接传到数据库执行

这样无可避免会造成sql硬解析。有时候在大量运行同一条sql时，为了减少硬解析，我们可以在sqlplus中使用绑定变量;

sys@ORCL>variable n varchar2(32);sys@ORCL>EXECUTE :n:='newtesting'; PL/SQL 过程已成功完成。 sys@ORCL>print n; N--------------------------------newtesting sys@ORCL>select :n from dual; :N--------------------------------newtesting sys@ORCL>select distinct sql_text from v$sql where sql_text like :n; 未选定行

5 一些特殊变量

sqlplus登录时，自动会设置好一些变量，如下：

sys@ORCL>defineDEFINE _DATE = "2012-09-10 19:02:06" (CHAR)DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)DEFINE _USER = "SYS" (CHAR)DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)DEFINE _EDITOR = "vi" (CHAR)DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)DEFINE _O_RELEASE = "1102000100" (CHAR)

除此之外，如果是在sqlplus调用脚本输入参数，在脚本中可以通过特殊变量&n (n是正整数)来获取相应的参数

sys@ORCL>--我这里是windows平台，如果是unix平台，下面的$需要替换成!sys@ORCL>$Microsoft Windows [版本 6.1.7601]版权所有 (c) 2009 Microsoft Corporation。保留所有权利。 C:\Users\Administrator>cat > test.sqlselect '&1', '&2' from dual;^Z C:\Users\Administrator>rem unix请用^D(按住ctrl键,再按字母D) C:\Users\Administrator>exit sys@ORCL>@test.sql hch demo原值 1: select '&1', '&2' from dual新值 1: select 'hch', 'demo' from dual 'HC 'DEM--- ----hch demo 

一、 运行脚本

1 @和@@的区别

@和@@都可以用来调用一个sql脚本，它们的区别是：

@命令调用当前目录下的，或指定全路径，或可以通过SQLPATH环境变量搜寻到的脚本文件。

@@用在脚本文件中，用来指定用@@执行的文件与@@所在的文件在同一目录，而不用指定全路径，也不从SQLPATH环境变量指定的路径中寻找文件。

2 通过环境变量调用脚本

在调用脚本时，可以通过系统设置的环境变量指定脚本路径：

C:\Users\Administrator>set MYPATH=C:\ C:\Users\Administrator>sqlplus -S / as sysdba @%MYPATH%/test.sql HCHSAY--------Hi! Man~

有两个环境变量值得一提，就是ORACLE_HOME和 ORACLE_SID。由于这两个变量比较常用，sqlplus提供了两个特别符号做为它们的简写，分别是'?'和'@';

sys@ORCL>$Microsoft Windows [版本 6.1.7601]版权所有 (c) 2009 Microsoft Corporation。保留所有权利。 C:\Users\Administrator>echo %ORACLE_HOME%/%ORACLE_SID%C:\app\Administrator\product\11.2.0\dbhome_1/%ORACLE_SID% C:\Users\Administrator>exit sys@ORCL>@?/@SP2-0310: 无法打开文件 "C:\app\Administrator\product\11.2.0\dbhome_1/orcl.sql"

从上面结果可以看出，sqlplus使用（想想输出awr报告用的命令：@?/rdbms/admin/awrrpt 现在你知道这里的@?的含义了吧?）

3 让sqlplus自动设置好登录环境

有没有办法让sqlplus登录时先自动执行指定的脚本呢？ 答案是有的：一共有三类脚本，sqlplus在登录时会首先运行。

它们分别是glogin.sql、login.sql和启动命令行中指定的sql文件。

下面摘录一段介绍glogin.sql和login.sql的文章

SQL*PLUS在启动时会自动运行两个脚本：glogin.sql、login.sql。文件glogin.sql是ORACLE为所有的数据库用户建立的、默认的SQLPLUS设置，存放在目录$ORACLE_HOME/sqlplus/admin/下。而用户可以将个人喜欢的常用设置放在login.sql文件中，每次sqlplus启动时会自动加载。SQL*PLUS在启动时先运行glogin.sql文件，再运行login.sql文件。查找glogin.sql只是在默认路径下找。查找login.sql文件的顺序是：先在当前路径下查找，如果找到则运行，运行后停止查找。如果没找到，再查找是否设置了SQLPATH环境变量，如果已经设置了该环境变量，就在该变量所对应的路径下查找，如果找到则运行，运行后停止查找。如果没有找到也会停止查找，不会再继续查找。如果没有设置SQLPATH环境变量同样停止查找，不会再继续查找。

sqlplus执行启动脚本的顺序是：glogin.sql->login.sql->命令行指定的脚本。执行完这些后才显示sql提示符。

C:\Users\Administrator>sqlplus / as sysdba @test.sql SQL*Plus: Release 11.2.0.1.0 Production on 星期一 9月 10 22:50:48 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved.  连接到:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options sys@ORCL>select 'hello world!' from dual; 'HELLOWORLD!------------hello world!

4 常用login.sql

在这里贴一个之前参加ngboss割接项目时经常使用的一个login.sql，并对其中的设置做简单说明

--设置多少行加一个页头 0表示不加页头set pagesize 9999--设置一行有多少个字符set linesize 150--boss对账常用的几个列格式设置col PACKAGEID format 9999999999999999999col oid format 9999999999999999999col subsid format 9999999999999999999col prodid format 9999999999999999999col SUBSPRODOID format 9999999999999999999--设置数据超过16个字符才显示成科学记数(默认是10个，在boss中显得比较小)set numwidth 16--设置数据显示格式--set numf 99999999999999.99--设置LONG, CLOB, NCLOB和XMLType等类型数据的显示字节数set long 9999column plan_plus_exp format a80--从数据库中查出当前实例名column global_name new_value gname--暂时关闭脚本输出set termout offdefine gname = gaoyjcolumn global_name new_value gnameselect lower(user)||'@'||substr(global_name,1,decode(dot,0,length(global_name),dot-1)) global_namefrom (select global_name,instr(global_name,'.') dot from global_name);--将实例名作为sql提示符set sqlprompt '&gname>'--修改默认时间输出格式alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';--恢复脚本输出set termout on--显示DBMS_OUTPUT调用的输出set serveroutput on--设置屏幕/SPOOL是否输出行尾的空格set trimspool on--设置允许一条sql语句之间存在空行set SQLBLANKLINES on --下面是一些dba工作中常见的列格式设置col host format a30col db_link format a30col host format a30col db_link format a30col member format a60col name format a60col owner format a15col what format a90col DEST_NAME format a30col OS_USERNAME format a15col USERNAME format a15col USERHOST format a15col TERMINAL format a15col OBJ_NAME format a30col SQL_TEXT format a60col EVENT format a70col status format a60col MESSAGE format a100col path format a30col FILE_NAME format a60col DESTINATION format a50col OBJECT_NAME format a30 --设置执行sql脚本时，是否显示正在运行的sqlset echo on

二、 使用sqlplus进行大量数据导出

oracle提供exp(dp)命令以二进制形式导出库表数据，但是有时候我们需要导出数据以文本形式存放，这个时候就得借助sqlplus的spool功能了。这里介绍几个参数，在导出大量数据时可能会用上。

1 set arraysize n

通过set arraysize n可以指定sqlplus一次从数据库获取查询返回的条数，默认是15行。因为我们一个数据块中的记录数一般都会超过15行，所以如果按照15行扫描一次，

那么一个数据块可能就会重复扫描多次。加大这个参数，可以减少物理读和逻辑读，提高导数速度。（这个原理与plsql的select bulk collect是相同的）

2 set term off 和 SET FLUSH OFF

众所周知，程序打印文本到屏幕比打印文本到文件所消耗的时间多很多。所以在导数过程中，我们要尽量减少打印屏幕的消耗。

FLUSH参数的作用是决定sqlplus是否缓冲屏幕输出，只在查询结果或缓冲区满时才进行屏幕输出，减少屏幕打印的次数，默认为on，表示不缓冲，在查询返回数据比较多时，需要设置为off以提高性能。

term参数作用是关闭脚本的屏幕输出，配合spool使用，只将查询结果输出到spool指定的文件，只输出到文件，不在屏幕显示，可以节省超过一半的IO。

3 设置输出格式

默认情况下，sqlplus查询结果是按空格分列的，但是有时候我们的数据本身有空格，按空格分列会导致数据混乱。这个时候我们可以使用set colsep c(c代表任意一个字符)命令来修改分隔符号）

sys@ORCL>set colsep |sys@ORCL>select owner, table_name from dba_tables where rownum < 2; OWNER |TABLE_NAME------------------------------|------------------------------SYS |ICOL$ sys@ORCL>

虽然这样能解决大部分问题，但是有时候查询的数据里面有换行，这个时候无论设置什么分隔符都会出现混乱。这个时候我们可以使用 sqlplus的html输出功能

sys@ORCL>set markup html onsys@ORCL>select owner||chr(13)||chr(10)||'SYS' owner, table_name from dba_tables where rownum < 4;<br><p><table border='1' width='90%' align='center' summary='Script output'><tr><th scope="col">OWNER</th><th scope="col">TABLE_NAME</th></tr><tr><td>SYSSYS</td><td>ICOL$</td></tr><tr><td>SYSSYS</td><td>CON$</td></tr><tr><td>SYSSYS</td><td>UNDO$</td></tr></table><p>

可以看出，查询输出的结果是以html表格代码格式输出的。将输出结果保存成html文件，用浏览器打开，显示如下：

三、 结束语

关于sqlplus的参数本文就介绍到此，对于sqlplus的参数有进兴趣一步了解的同学，建议自己抽空运行sqlplus –H (命令行中)，show all，help set等命令，查看sqlplus各个参数并结合自动动手实践及网上的资料进行研究。