oraclesqlplus执⾏脚本_sqlplus使⽤经验谈
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, &&默认情况下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但偶尔也有⼀⾏语句有的需要绑定变量，有的不需要的情况。这个时候我们可以通过转义字符来处理。
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 d 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>---执⾏ACCEPT 命令作⽤是读取⼀⾏⽤户输⼊赋值给变量，其⽤法是：
ACC[EPT] variable [NUM[BER] | CHAR | DATE | BINARY_FLOAT | BINARY_DOUBLE] [FOR[MAT] format] [DEF[AULT] default] [PROMPT text|NOPR[OMP
下⾯演⽰要求⽤户输⼊数字的⽤法：
网页版文档编辑sys@ORCL>acc num number format 9 def 2 prompt 'Please Enter the Num:'Please Enter the Num:2sys@ORCL>--注意这⾥使⽤了.号连接字符串ToChinese_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--------------------------------------------这样⽆可避免会造成sql硬解析。有时候在⼤量运⾏同⼀条sql时，为了减少硬解析，我们可以在sqlplus中使⽤绑定变量;
sys@ORCL>variable n varchar2(32);sys@ORCL>EXECUTE :n:='newtesting'; PL/SQL 过程已成功完成。 sys@ORCL>print n; N--------------------------------newtestin 5 ⼀些特殊变量
sqlplus登录时，⾃动会设置好⼀些变量，如下：
sys@ORCL>defineDEFINE _DATE = "2012-09-10 19:02:06" (CHAR)DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)DEFINE _USER = "SYS" (CHAR)D
除此之外，如果是在sqlplus调⽤脚本输⼊参数，在脚本中可以通过特殊变量&n (n是正整数)来获取相应的参数
python属于哪种程序设计语言sys@ORCL>--我这⾥是windows平台，如果是unix平台，下⾯的$需要替换成!sys@ORCL>$Microsoft Windows [版本 6.1.7601]版权所有 (c) 2009 Microsoft Corpora ⼀、 运⾏脚本
1 @和@@的区别
@和@@都可以⽤来调⽤⼀个sql脚本，它们的区别是：
@命令调⽤当前⽬录下的，或指定全路径，或可以通过SQLPATH环境变量搜寻到的脚本⽂件。
@@⽤在脚本⽂件中，⽤来指定⽤@@执⾏的⽂件与@@所在的⽂件在同⼀⽬录，⽽不⽤指定全路径，也不从SQLPATH环境变量指定的路
径中寻⽂件。
2 通过环境变量调⽤脚本
在调⽤脚本时，可以通过系统设置的环境变量指定脚本路径：
C：甥敳獲Administrator>set MYPATH=C: C：甥敳獲Administrator>sqlplus -S / as sysdba @%MYPATH%/test.sql HCHSAY--------Hi! Man~
有两个环境变量值得⼀提，就是ORACLE_HOME和 ORACLE_SID。由于这两个变量⽐较常⽤，sqlplus提供了两个特别符号做为它们的guid是什么意思
简写，分别是'?'和'@';
sys@ORCL>$Microsoft Windows [版本 6.1.7601]版权所有 (c) 2009 Microsoft Corporation。保留所有权利。 C：甥敳獲Administrator>echo %ORACLE_HOME%/%O 从上⾯结果可以看出，sqlplus使⽤(想想输出awr报告⽤的命令：@?/rdbms/admin/awrrpt 现在你知道这⾥的@?的含义了吧?)
3 让sqlplus⾃动设置好登录环境
有没有办法让sqlplus登录时先⾃动执⾏指定的脚本呢？ 答案是有的：⼀共有三类脚本，sqlplus在登录时会⾸先运⾏。
mysql语句的执行顺序它们分别是glogin.sql、login.sql和启动命令⾏中指定的sql⽂件。
下⾯摘录⼀段介绍glogin.sql和login.sql的⽂章
SQL*PLUS在启动时会⾃动运⾏两个脚本：glogin.sql、login.sql。⽂件glogin.sql是ORACLE为所有的数据库⽤户建⽴的、默认的SQLPLUS设置，存放在⽬录$ORACL
sqlplus执⾏启动脚本的顺序是：glogin.sql->login.sql->命令⾏指定的脚本。执⾏完这些后才显⽰sql提⽰符。
C：甥敳獲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.
4 常⽤login.sql
在这⾥贴⼀个之前参加ngboss割接项⽬时经常使⽤的⼀个login.sql，并对其中的设置做简单说明
--设置多少⾏加⼀个页头 0表⽰不加页头set pagesize 9999--设置⼀⾏有多少个字符set linesize 150--boss对账常⽤的⼏个列格式设置col PACKAGEID format 999999⼆、 使⽤sqlplus进⾏⼤量数据导出
oracle提供exp(dp)命令以⼆进制形式导出库表数据，但是有时候我们需要导出数据以⽂本形式存放，这个时候就得借助sqlplus的spool功
能了。这⾥介绍⼏个参数，在导出⼤量数据时可能会⽤上。
1 set arraysize n
通过set arraysize n可以指定sqlplus⼀次从数据库获取查询返回的条数，默认是15⾏。因为我们⼀个数据块中的记录数⼀般都会超过15
⾏，所以如果按照15⾏扫描⼀次，
php删代码跑路程序那么⼀个数据块可能就会重复扫描多次。加⼤这个参数，可以减少物理读和逻辑读，提⾼导数速度。(这个原理与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------------------------------|---------------
虽然这样能解决⼤部分问题，但是有时候查询的数据⾥⾯有换⾏，这个时候⽆论设置什么分隔符都会出现混乱。这个时候我们可以使⽤
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;
ascii码值大小写字母之差OWNER TABLE_NAME
SYSSYS ICOL$
SYSSYS CON$
SYSSYS UNDO$
可以看出，查询输出的结果是以html表格代码格式输出的。将输出结果保存成html⽂件，⽤浏览器打开，显⽰如下：
三、 结束语
关于sqlplus的参数本⽂就介绍到此，对于sqlplus的参数有进兴趣⼀步了解的同学，建议⾃⼰抽空运⾏sqlplus –H (命令⾏中)，show all，help set等命令，查看sqlplus各个参数并结合⾃动动⼿实践及⽹上
的资料进⾏研究。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。