SQL Plus使用入门
转载自：
http://blog.csdn.net/wjbaby/article/details/1663550
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文件的内容。
