SQL-Plus的使用

SQL*Plus的使用1. SQL*Plus介绍SQL*Plus是oracle提供的一个工具程序，既可以在oracle服务器使用，也可以在oracle客户端使用。

SQL*Plus是一个最常用的工具，具有很强的功能，主要有：1). 数据库的维护，如启动，关闭等2). 执行SQL语句、执行PL/SQL程序3). 执行SQL脚本4). 数据的导出，报表5). 应用程序开发、测试SQL/PLSQL6). 生成新的SQL脚本7). 供应用程序调用，如安装程序中进行脚本的安装2. dual表dual是一张系统表，同时也被定义成了public同义词。

它只有一个字段和一条记录。

该表本身的结构和数据没有什么意义，主要是借助该表进行其它操作。

Dual表的使用例如获取函数值、计算表达式的值：说明：不要对dual表进行DDL与DML操作，只进行查询操作。

3. SQL*Plus使用3.1 启动SQL*Plus及连接数据库1). 不连接数据库> sqlplus /nolog2). 连接数据库> sqlplus username/password3). 使用连接字符串连接数据库> sqlplus username/password@connect_identifier4). 使用操作系统权限连接数据库> sqlplus / as sysdba3.2 SQL*Plus的命令3.2.1 帮助命令1). help命令格式：help 命令名，用于知道某个具体命令的帮助信息。

如：SQL> help connect2). describe命令用户查看表的结构；获取函数，存储过程和包的描述。

如：SQL> desc user_tables说明：在SQL*Plus中，所有命令都可以用前面的四个字母作为整个命令。

3.2.2 编辑命令SQL*Plus会将上一次执行过的SQL命令（包括SQL语句和PL/SQL语句，包括一行或多行）保存到缓存区中，可以对缓存区中信息进行编辑。

编辑后可以通过“/ ”命令执行修改后的缓存区中的命令，如果不修改，则是执行原有命令。

1). list [n] 命令显示上一条命令中的第n行，如果不指定n，则显示上一命令的所有行，这样当前行就是最后一行。

如：2). change命令编辑当前行的内容，先用list命令指定当前行。

语法为：change /被修改字串/修改后的串如：3). 增加新行在第一行插入一行，方法为：输入0，在0后输入文本。

在当前行后插入一行，方法为：输入input（或i），回车，输入新行，再回车，会提示再输入新行，如不想输入，输入点号，回车。

如：4). 删除行del --删除缓存区当前行，执行前先用list命令指定当前行del n --删除缓存区指定的行如：5). 使用操作系统编辑器编辑在SQL*Plus中输入edit命令，会自动打开系统的缺省的文本编辑器（Windows下为notepad，Linux下为ed，可以通过“define _EDITOR=…”更换编辑器），缓存区中内容被装到文本编辑器中，这时可以对其中的内容进行编辑（这时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/@ 命令SQL> start/@ 脚本文件名这样可以将相关的SQL/PLSQL语句，SQL*Plus的命令写在脚本中，从而执行。

最常用的就是安装脚本，升级脚本。

如：SQL> start e:\\test.sqlSQL> @e:\\test.sql3.2.5 设置SQL*Plus环境（set命令）可以通过设置参数来改变SQL*Plus的一些属性，如显示等。

可在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 。

在查询时，通常一条记录会显示一行，如果一行显示不下，则会自动换行。

用户输入数据时，当一行输入的值超过一行的最大值时，也会自动换行。

设置打开(on)或关闭(off)查询结果页的头信息，如列标题。

比如，想输出SQL语句存储到文件中时，就需要把这关闭。

如：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包的详细信息在以后介绍。

需要说明的是，该设置只是在当前会话有效，一旦重新连接后，又恢复为默认值。

可以在未连接数据库的情况下设置，设置后连接后有效，但一旦重新连接就恢复默认值了。

如：设置为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时，换行显示。

比如一个字段定义了varchar2类型，长度很大，但实际字段值宽度很少，如果不加设置，在SQL*Plus查询时，会占用定义的宽度，这样看起来不方便。

如：2). 设置数字的显示宽度和格式如：注意下面SAL字段值的变化3. 清除列的设置col 列名clear --清除指定列的格式设置clear columns --清除所有列的格式设置3.2.8 host命令通过host命令，可以在SQL*Plus中执行操作系统命令。

如：SQL> host mkdir e:\\temp3.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 选项效果一样。