SQL Plus 一些使用技巧，HERE!

hanlei 2007-6-11 14:18

Sql*plus的使用 Sql*plus的使用 Sql*plus介绍 Sql*plus是oracle提供的一个工具程序，既可以在oracle服务器使用，也可以在oracle客户端使用。在windows下分两种，sqlplus.exe是命令行程序，sqlplusw.exe是窗体程序，通常我们在开始菜单中启动的是后者，两者的功能是一致的。 Sql*plus是一个最常用的工具，具有很强的功能，主要有： 1.        数据库的维护，如启动，关闭等，这一般在服务器上操作。 2.        执行sql语句执行pl/sql。 3.        执行sql脚本。 4.        数据的导出，报表。 5.        应用程序开发、测试sql/plsql。 6.        生成新的sql脚本。 7.        供应用程序调用，如安装程序中进行脚本的安装。 2        dual表 dual是一张系统表，同时也被定义成了public同义词。它只有一个字段和一条记录。该表本身的结构和数据没有什么意义，主要是借助该表进行其它操作。如： select  sysdate  from dual;  --获取函数值 select 21+15*3 from dual;  --计算表达式的值 说明：不要对dual表进行ddl与dml操作，只进行查询操作。 3        sql*plus使用 3.1        启动sql*plus 1.        不带参数启动 启动sqlplusw.exe程序，会弹出登陆框，让输入用户名、密码和连接字符串，在用户名中输入“/nolog”，表示先进入sql>提示符，先不连接数据库，下面可以利用connect命令连接数据库。启动sqlplus.exe程序，会提示输入用户名与密码。如果用户名输入 2.        带参数启动 下面列举一些最常见的方式，还有很多可选参数。 1)        不连接数据库 sqlplus /nolog 2)        连接数据库 sqlplus username/password 3)        使用net8连接字符串连接数据库 sqlplus username/password@connstr 4)        连接后执行filename指定的sql脚本,sql脚本中是sql命令和sql*plus的设置命令 sqlplus username/password[@connstr] @filename 3.2        sql*plus的命令 3.2.1        帮助命令 1.        help命令 格式：help 命令名 ，用于知道某个具体命令的帮助信息。         如：help connect 2.        describe命令 用户查看表的结构；获取函数，存储过程和包的描述。这是非常有用和常用的一个命令。 如：desc user_tables 说明：在sql*plus中，所有命令都可以用前面的四个字母作为整个命令。 3.2.2        编辑命令 sql*plus会将上一次执行过的sql命令（包括sql语句和pl/sql语句，包括一行或多行）保存到缓存区中，可以对缓存区中信息进行编辑。 编辑后可以通过 “/ ”命令执行修改后的缓存区中的命令，如果不休改，则是执行原有命令。 1.        list [n] 命令 显示上一条命令中的第n行，如果不指定n，则显示上一命令的所有行，这样当前行就是最后一行。其它操作会对当前行进行操作，所以其它操作需要先执行list命令。 如： begin         insert into test values(1); end; / list 2.        change命令 编辑当前行的内容，先用list命令指定当前行。语法为： change  /被修改字串/修改后的串 如： list 2 change /(1)/(20) 3.        增加新行 在第一行插入一行，方法为：输入0，在0后输入文本。如： 0 insert into test values(2); 在当前行后插入一行，方法为：输入input（或i），回车，输入新行，再回车，会提示再输入新行，如不想输入，输入点号，回车。如： 4.        删除行 del   --删除缓存区当前行，执行前先用list命令指定当前行 del n  删除缓存区指定的行 5.        使用操作系统编辑器编辑命令 在 sql*plus中输入edit命令，会自动打开系统的缺省的文本编辑器（windows下为notepad），缓存区中内容被装到文本编辑器中，这时可以对其中的内容进行编辑（这时sql*plus处于等待状态），修改完毕后，保存文件后。被修改的内容就会被写入缓存区。这对于修改错误命令很方便。 6.        save命令 格式：save 文件名 [replace | append] save命令的作用是将缓存区中内容保存到指定文件中。如果指定的文件不存在，将会创建，但如果文件目录不存在，将会失败。如果指定文件名的文件已存在，不指定replace或append参数将会失败。指定replace表示将覆盖原文件内容，指定append表示将缓存区内容加到文件后。 7.        get命令 格式：get 文件名 get命令的作用是将指定文件的内容加载到缓存区中，以供编辑或执行。 3.2.3        spool命令 sql> spool 文件名 执行该命令后，如果指定的文件不存在，则会按指定的文件名创建一个空文本文件，如果目录不存在，会失败。如果指定的文件已存在，则文件内容将会被清空。         执行上述命令后，此命令后的所有输出（包括命令、输出提示信息、错误信息等）都会被写入指定的文件。需要说明的时，并不是每输出一行信息，就会立即写入文件，有个缓存过程。 Sql>spool off 上述命令就是停止存储，将前面所有输出立即写入文件。 Sql>spool out 除完成spool off命令的功能外，还打印输出的信息。 说明：spool off/out命令必须与spool命令一一对应。 Spool命令的用途主要有如下： 1）        导出数据 2）        记录脚本的执行日志 3）        生成新的sql脚本 3.2.4        start/@ 命令 格式：start/@ 脚本文件名 这样可以将相关的sql/plsql语句，sql*plus的命令写在脚本中，从而执行。最常用的就是安装脚本，升级脚本。 如： sql> start e:/test.sql sql> @e:/test.sql 3.2.5        设置sql*plus环境（set命令） 可以通过设置参数来改变sql*plus的一些属性，如显示等。如果运行的是sqlplusw.exe程序，通过菜单“选项|环境”可以通过界面改变这些参数的默认值。也可在sql>提示符下输入set命令来改变参数的值。 查看参数的当前设置值的命令是：show 参数名 。  常见的设置参数命令有： 1.        set pagesize [n] 用于设置每页的行数，范围为1~ 50000，如果为0，则表示不分页，不带n，表示为0。否则，缺省情况下，当查询结果的行数超过一页的行数时，就会分页显示，每页的开头会显示列标题信息。 2.        set newpage [n] 该命令与pagesize结合使用，用来设置每一页的顶行的空行数，范围为0~ 999，不带n，表示为0。缺省值为1。 3.        set linesize n 设置每行能容纳的字符数，范围为1~32767 。在查询时，通常一条记录会显示一行，如果一行显示不下，则会自动换行。用户输入数据时，当一行输入的值超过一行的最大值时，也会自动换行。 4.        set heading off|on 设置打开(on)或关闭(off)查询结果页的头信息，如列标题。比如，想输出sql语句存储到文件中时，就需要把这关闭。如： select 'insert into test1 values('||id||');' from test; 5.        set feedback off|on 设置为on，当执行insert,update，pl/sql等操作时，会提示执行的结果。如果设置为off，则不显示。 6.        set termout off|on 设置为off，执行的信息就不会在屏幕上显示。需要说明的是，该选项只有在脚本中设置，执行脚本时才有效。 7.        set trimspool off|on 设置为on，查询结果输入到文件中时，对于查询结果的后面的空格，将被截掉。 8.        set serveroutput off|on 设置on，在pl/sql中使用dbms_output包输出调试信息时sql*plus中可以显示出来，否则不显示。缺省为off。关于dbms_output包的详细信息在以后介绍。需要说明的是，该设置只是在当前会话有效，一旦重新连接后，又恢复为默认值。可以在未连接数据库的情况下设置，设置后连接后有效，但一旦重新连接就恢复默认值了。 如： begin dbms_output.put_line('hello'); end; 9.        set timing off|on 设置为on，每执行一sql或pl/sql，都会显示该执行所需要的时间，通过这可以查看sql语句的执行效率。 10.        set autocommit on|off|n 在sql*plus中，执行dml语句后，需要commit后或者执行了dcl或ddl语句后才会被提交。本命令可以设置让sql*plus自动提交。         其中on表示每执行一sql/plsql，都自动提交一下。而off只是当sql*plus退出时才自动提交一下。n表示执行n条sql/plsql语句后就自动提交一下。 11.        set echo on|off 设置为on，sql*plus执行脚本时，都会将每一条执行的sql语句输出来，这样如果执行出错，便于定位。缺省为off 。 3.2.6        show命令 通过show 参数名 ，可以看到当前sql*plus的一些环境参数的设置。还可以查看其它信息，如： show user 查看当前登陆的用户 show error 查看sql执行出错的详细信息，因为创建pl/sql对象时即使出错，sql*plus不会报error，只会报warning，而且无法看到详细错务信息，通过show error就可以看到。 3.2.7        column(col)命令 该命令可用于设置列的显示属性，常见格式如： 1.        col 列名 format an [truncate] 上面命令用于设置列的显示宽度，n为宽度。Truncate表示如果列值宽度超过n时，就截去超长的部分。不加Truncate，如果列值宽度超过n时，换行显示。 如：col s format a20 比如一个字段定义了varchar2类型，长度很大，但实际字段值宽度很少，如果不加设置，在sql*plus查询时，会占用定义的宽度，这样看起来不方面。 2.        设置数字的显示宽度 create table test(id number); insert into test values(888888812345678); SQL> select * from test;         ID ---------- 8.8889E+14 SQL> select to_char(id) from test; TO_CHAR(ID) ---------------------------------------- 888888812345678 col id format 999999999999999999999999  --设置数字的显示宽度 SQL> select * from test;                        ID -------------------------           888888812345678 3.        清除列的设置 col 列名 clear    --清除指定列的格式设置 clear columns  --清除所有列的格式设置 3.2.8        host命令 通过host命令，可以在sql*plus中执行操作系统命令。如： sql>host mkdir e:/temp 3.2.9        退出sql*plus 语法：exit [n] 说明：通过exit命令退出sql*plus，退出可以带一个错务码。主要是供父进程使用。 3.3        执行失败的处理 当一个脚本中存在大量的sql/plsql语句时，执行该脚本时，如果其中有一条sql语句执行失败，可能很难发现。如果能让碰到错误时，让sql*plus退出，再结合spool命令查看日志，就很容易知道脚本的执行情况。这可以通过whenever命令来实现。 格式一：whenever sqlerror exit [success | failure | warning | n] [commit | rollback | none] 说明：一旦脚本中该语句之后的某sql语句执行出错，就会停止出错，sql*plus就会自动退出。其中success | failure | warning | n 为设定sql*plus出错后退出带出的错务码，success | failure | warning为固定的常量，n为指定任意数值。如果加了commit选项，则出错后，sql*plus在退出前，会自动执行commit一下；如果加了rollback选项，则出错后，sql*plus在退出前，会自动执行rollback一下；如果加了none选项或什么都不加，则退出不会做任何事，但实际上默认设置下sql*plus在退出前会commit一下，所以这种情况与加了commit选项效果一样。 例：whenever sqlerror exit sql.sqlcode --保证了出现错误，立即退出，并返回错误码 例：***test.sql*** spool e:/test.txt whenever sqlerror exit select * from test1233; select * from dual; spool off 格式二：whenever sqlerror continue [commit | rollback | none] 说明：一旦脚本中该语句之后的某sql语句执行出错，会继续往下执行，sql*plus不会自动退出，默认就是这样的设置。如果加了commit选项，则出错后，执行下一条语句前，sql*plus会自动执行commit一下；如果加了rollback选项，则出错后，执行下一条语句前，sql*plus会自动执行rollback一下；如果什么都没加或加了none选项，则出错后，对前面执行过的语句不会自动添加执行任何commit或rollback语句，是否commit或rollback，看后面的语句。 例：***test.sql*** spool e:/test.txt whenever sqlerror continue select * from test12; select * from dual; spool off 说明：当创建一个pl/sql对象时，如存储过程，函数，触发器，包等。即使代码写的有问题，在sql*plus执行时不会报error，只会报warning。这样通过whenever sqlerror是无法获取到的。而且sql*plus不直接给出出错的详细信息，这可以通过show error命令看到出错的详细信息。 3.4        固化对sql*plus的环境设置 通过上面的介绍我们可以知道，可以通过命令设置sql*plus运行环境参数。但sql*plus一旦关闭重新打开，这些参数又恢复成默认值，又需要重新设置，这非常不方便。好的是，oracle提供一种方法可以使这些设置固定下来。         如果%ORACLE_HOME%/sqlplus/admin目录下有glogin.sql文件（不同的系统目录和文件名可能不确定），则启动sql*plus后（如果有初始连接，则连接后），sql*plus会自动执行该脚本，这样就可以在该脚本对sql*plus的参数进行设置，还可以加上特定的sql/plsql语句。         可以看出，每次启动sql*plus，不管是谁启动，都会执行glogin.sql脚本。Oracle同时提供一种机制让不同的操作系统用户启动sql*plus执行自己特定的脚本。方法是，在当前目录下（即运行sql*plus程序时的当前目录）编写一个文件名为login.sql的脚本，将自己特定的设定写入文件，这样启动sql*plus时首先会执行login.sql，然后再执行公共的glogin.sql。这在windows系统下显的不是很方面，因为只有在命令行下启动sql*plus时才有效，且必须先设置当前目录。而在unix下就非常有用，因为unix下每个登陆用户都有自己的主目录，而登陆后当前目录就是主目录，不同的用户可以将login.sql文件放在自己的主目录下。 4        日期类型的显示 在sql*plus中执行：select sysdate from dual; 发现查询结果是以字符串显示的，但格式不好看。原因是，oracle对日期类型它会自动的按照缺省格式隐式的转换为字符串类型。 可以通过t_char函数和to_date函数来实现日期与字符串之间的显示转转。如： select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) from dual; create table test(s date); insert into test values(to_date(‘2005-01-25 10:20:22’,’ yyyy-mm-dd hh24:mi:ss’)); 也可以通过执行sql命令，来改变当前会话的日期格式。如： alter session set nls_date_format=’ yyyy-mm-dd hh24:mi:ss’; select sysdate from dual; insert into test values(‘2005-01-25 10:20:22’); 可以直接将字符串插入到日期类型中，原因是oracle对数据类型会做自动隐式的转换。 说明：该设置只在当前会话有效，重新登陆后，又恢复为默认值。 5        脚本中变量 5.1        替换变量 变量前加一个&符号，sql*plus在命令中遇到替换变量时，用真实值去代替，相当于c语言中的宏定义。真实值来源于三个地方： 1）        脚本参数带入 2）        脚本中直接定义 3）        用户动态输入 如： select &num from dual; sql*plus中有几个环境参数将影响替换变量，为： set define off | c off表示关闭替换变量功能。c为定义替换字符（缺省为&），同时启用替换变量功能。 set escape off | c 定义转义字符。即&前面如果有指定的c字符，则作为普通字符处理。默认是off，即没有定义转义字符。如： set escape / select ‘/&hello’ from dual; set ver off|on 如果为on，在替换前后会列出命令文件的每一行，缺省为on。 set concat c 设置替换变量与其后的其它字符的分隔符，缺省为句点（.）。如： select ‘&hello.good’ from dual; -- 句点不作为输出的一部分 等价于：select  ‘&hello’||’good’ from dual; select ‘&hello good’ from dual; --空格也起到分隔作用，单空格作为输出的一部分 select  ‘&hello’||’.good’ from dual; 5.1.1        脚本带参数 脚本可以带参数，在脚本中通过&n来引用参数，n为1表示为第一个参数，2表示第二个参数，依次类推。如： /****test.sql****/ set ver off; connect omc/&1 insert into test values(&2); commit; 执行该脚本的方法是：sql>@e:/test.sql omc self 45 如果参数是字符串，且字符串有空格，应该用双引号或单引号扩起，如果字符串中有双引号，则只能用单引号扩起，如果字符串中要输入单引号，则只能用双引号扩起，且输入两个单引号才代表一个单引号。如： /********test.sql********/ set ver off; connect omc/&1@&2 create table test(s varchar2(20)); insert into test values('&3'); commit; 执行： sql>@e:/test.sql omc self  hello sql>@e:/test.sql omc self  “hello world” sql>@e:/test.sql omc self  hello world’ sql>@e:/test.sql omc self  “hello ‘’zte’’world” sql>@e:/test.sql omc self  ‘hello “zte” world’ 正常情况下，slq*plus执行时，碰到&符号，就会作为参数来处理，如果&符号后跟的是数字，就会从命令行中取相应的值替换&n，如果找不到，如n为3，但执行时只带了2个参数，sql*plus就会在屏幕上提示输入参数。如果&后跟的不是数字，而是其它字符，则sql*plus会把&及其后的字符串（截止到空格为止）当作一个参数提示输入。 如： select ‘hello&good china’ from dual; 那么如何将&作为普通字符处理呢？除了前面介绍的定义escape环境参数外，另一方法是，&符号后紧跟单引号（之间可以跟空格）。如： select ‘hello&’||’good china’ from dual; select ‘hello&  ’||’good china’ from dual; 所以，一般情况下在sql*plus下，执行sql语句时或脚本时，对&字符需要特殊处理一下，因为它是sql*plus中的特殊字符。 5.1.2        脚本中定义 格式：define 标识符 = 值 如： define n=12 define s=’hello’ --是否加引号没有关系 通过加&引用,如： select &n from dual; select ‘&s’ from dual; --注意必须要加引号 begin dbms_output.put_line('&s'); end; 取消定义的方法是：undefine标识符 一旦取消定义后，如果在通过&引用，则sql*plus会提示输入。 5.1.3        接收用户交互式输入 很多时候，在执行脚本时，我们希望有些信息根据脚本的提示，让用户动态输入，从而在下面的sql语句中使用。 语法为：accept var_name typename prompt ‘hint’ [hide]         说明：hide表示以密文方式让用户输入。typename只能是char,number,date三种。 如： accept user_name char prompt ‘please input username:’ accept passwd char prompt ‘please input password:’ hide 通过变量前加&引用：connect &user_name/&passwd 5.2        捆绑变量 一般情况下，我们都是在pl/slq中定义变量，如： delclare         s varchar2(10); begin         s:=’hello’;         insert into test values(s); end; 上面的程序段中，定义了变量s，但它只在该程序段有效。那如何定义在整个sql*plus中都有效的变量（这里称为捆绑变量）呢？方法如： var g_str varchar2(10) begin --赋值只能在pl/sql中进行 :g_str:='hello'; --注意前面要加冒号 end; select :g_str from dual; --引用捆绑变量，注意前面要加冒号 print g_str  --显示捆绑变量，注意前面不要加冒号 declare --在pl/sql中引用捆绑变量 s varchar2(10); begin dbms_output.put_line(:g_str); s:=:g_str; dbms_output.put_line(s); insert into test values(:g_str); end; 6        脚本例子 6.1        生成表的备份脚本 编写存储过程，该存储过程生成一个select语句，执行结果为输出指定表的所有记录的insert语句。 create or replace function BuildSelSql(tablename in varchar2) return varchar2 is   cursor cur(tabname varchar2) is    select column_name,data_type from user_tab_columns where table_name=upper(tabname) order by COLUMN_ID;   tmp varchar2(4000);   msg varchar2(4000);   len number;   num number; begin   tmp:='select ''insert into '||tablename||' values(''';   for re in cur(tablename) loop     if re.data_type='CHAR' or re.data_type='DATE' or re.data_type='VARCHAR2'       or re.data_type='RAW' then       tmp:=tmp||'|'||'|''''''''';       tmp:=tmp||'|'||'|'||re.column_name||'|'||'|'||''''''',''';     else       --tmp:=tmp||'|'||'|'||'decode('||re.column_name||',null,''null'','||re.column_name||')'||'|'||'|'||''',''';       tmp:=tmp||'|'||'|'||'nvl('||re.column_name||',0)'||'|'||'|'||''',''';     end if;   end loop;      len:=length(tmp);   msg:=substr(tmp,1,len-3);      msg:=msg||''');'' from '||tablename ||';';   return msg; end; 执行函数例子： select BuildSelSql('test') from dual; --test为表名 /*******sql脚本如下 test.sql*******/ set trimout on set trimspool on set heading off set feedback off set termout off set ver off set linesize 4000 set pagesize 0 whenever sqlerror exit set serveroutput on spool e:/test1.sql select BuildSelSql('test') from dual; spool off set pagesize 6 btitle left 'commit;' --分批提交 spool e:/test.txt   start e:/test1.sql spool off 执行：         sql>@e:/test.sql 执行后查看e:/test.txt文件的内容。