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文件的内容。