[导入]SQLPLUS 操作大全

附录B SQL*PLUS
Sql*plus 中使用绑定变量：
sql> variable x number;
sql> exec :x := 7788;
sql> SELECT empno,ename from scott.emp where empno=:x;
SQL*PLUS 是Oracle提供的一个工具程序，它不仅可以用于测试，运行SQL语句和PL/SQL块，而且还可以用于管理Oracle数据库，
1.启动sql*plus
为了使用sql*plus，必须首先要启动sql*plus。Oracle不仅提供了命令行和图形界面的sql*plus，而且还可以在web浏览器中
运行.
(1)在命令运行sql*plus
在命令行运行sql*plus是使用sqlplus命令来完成的，该命令适用于任何操作系统平台，
语法如下:
   sqlplus [username]/[password][@server]
如上所示:username用于指定数据库用户名，password用于指定用户口令，server则用于指定主机字符串(网络服务名).
当连接到本地数据时，不需要提供网络服务名,如果要连接到远程数据库，则必须要使用网络服务名.
(2)在windows环境中运行sql*plus
如果在windows环境中安装了oralce数据库产品，那么可以在窗口环境中运行sql*plus
具体方法: "开始->程序->oracle-oradb10g_home1->application development->sql*plus"
2.连接命令
(1)conn[ect]
   该命令用于连接到数据库。注意，使用该命令建立新会话时，会自动断开先前会话，示例如下:
   sql>conn scott/yhai1981@demo
(2)disc[onnect]
   该命令用于断开已经存在的数据库连接。注：该命令只是断开连接会话，而不会退出sql*plus，示例如下:
   sql>disc
(3)passw[ord]
   该命令用于修改用户的口令。注，任何用户都可以使用该命令修改其自身口令，但如果要修改其他用户的口令时，
则必须以DBA身份(sys和system)登录，在sql*plus中，当修改用户口令时，可以使用该命令取代sql命令alter user,
   示例如下:
sql>passw
更改scott的口令
旧口令:******
新口令:******
重新键入新口令:******
口令已更改
sql>
(4)exit
该命令用于退出 sql*plus,另外你也可以使用quit命令退出sql*plus.使用该命令不仅会断开连接，而且也会退出sql*plus
注:默认情况下，当执行该命令时会自动提交事务。
3，编辑命令
(1)l[ist]
   该命令用于列出sql缓冲区的内容，使用该命令可以列出sql缓冲某行，某几行或所有行的内容。在显示结果中,数据字为具体
的行号，而"*"则表示当前行。
示例一:列出sql缓冲区所有内容
     sql>l
示例二:列出sql缓冲区首行内容:
     sql>l1
(2)a[ppend]
   该命令用于在sql缓冲区的当前行尾部添加内容。注:该命令将内容追加到标记为"*"的行的尾部，示例如下:
   sql>l
     1 select empno,ename,sal,hiredate,comm,deptno
     2 from emp
     3* where deptno=10
   sql>a and job='CLERK'
   sql>l
   SQL> list
    1 select empno,ename,sal,hiredate,comm,deptno
    2 from emp
    3* where deptno=10 and job='CLERK'
(3)c[hange]
   该命令用于修改sql缓冲区的内容。如果在编写sql语句时写错了某个词，那么使用该命令可以进行修改，
    sql>select ename from temp where deptno=10;
    SQL> c /temp/emp
      1* select ename from emp where deptno=10
(4)del
   该命令用于删除sql缓冲区中内容，使用它可以删除某行，某几行或所有行，在默认情况下，当直接执行
del时，只删除当前行的内容，示例如下:
   SQL> l
   1 select ename
   2 from emp
   3* where deptno=20
   sql>del
   SQL> l
   1 select ename
   2* from emp
   如果一次要删除多行，则指定起始行号和终止行号，例如"del 3 5"
(5)i[nput]
   该命令用于在sql缓冲区的当前行后新增加一行。示例如下:
   SQL> l
    1 select ename
    2* from emp
   sql>i where deptno=30
   如果要在首行前增加内容，则使用"0文本"
   sql>0 create table temp as
   SQL> l
1 create table temp as
2 select ename
3 from emp
4* where deptno=30
(6) n
该数值用于定位sql缓冲区的当前行，示例如下:
(7)edi[t]
   该命令用于编辑sql缓冲区的内容。当运行该命令时，在windows平台中会自动启动"记事本",以编辑sql缓冲区
(8)run和/
   run的/命令都可以用于运行sql缓冲区中的sql语句。注:当使用run命令时，还会列出sql缓冲区内容,eg：
   SQL> run
   1* select ename from emp where deptno=20
   4.文件操纵命令
   (1)save
   该命令用于将当前sql缓冲区的内容保存到sql脚本中。当执行该命令时，默认选项为create，即建立新文件。
   eg:
   SQL> save c:\a.sql create
   已创建 file c:\a.sql
    当执行命令之后，就会建立新脚本文件a.sql，并将sql缓冲区内容存放到该文件中。如果sql已经存在，使用
   replace选项可以替撚已存在的sql脚本，如果要给已存在的sql脚本追加内容，可以使用append选项。
   (2)get
    该命令与save命令作用恰好相反，用于将sql脚本中的所有内容装载到sql缓冲区中。
    eg:
    SQL> get c:\a.sql
    1* select ename from emp where deptno=20
   (3)start和@
    start和@命令用于运行sql脚本文件。注:当运行sql脚本文件时，应该指定文件路径.eg:
    SQL> @c:\a.sql
    ENAME
    ----------
    SMITH
    JONES
    SCOTT
    ADAMS
    FORD
   (4)@@
    该命令与@命令类似，也可以运行脚本文件，但主要作用是在脚本文件中嵌套调用其它的脚本文件。当使用该命令
    嵌套脚本文件时，可在调用文件所在目录下查找相应文件名。
   (5)ed[it]
    该命令不仅可用于编辑sql缓冲区内容，也可以用于编辑sql脚本文件。当运行该命令时，会启动默认的系统编辑
    器来编辑sql脚本。运行方法为:
    sql>edit c:/a.sql
   (6)spool
    该命令用于将sql*plus屏幕内容存放到文本文件中。执行该命令时，应首先建立假脱机文件，并将随后sql*plus
    屏幕的所有内容全部存放到该文件中，最后使用spool off命令关闭假脱机文件。eg:
    sql>spool c:\a.sql
    5.格式命令
     sql*plus不仅可以用于执行sql语句、pl/sql块，而且还可以根据select结果生成报表。使用sql*plus的格式命令
    可以控制报表的显示格式，例如使用column命令可以控制列的显示格式，使用ttitle命令可以指定页标题;使用
    btitle命令可以指定页脚注。
    (1)col[umn]
     该命令用于控制列的显示格式。column命令包含有四个选项，其中clear选项用于清除已定义列的显示格式:
     heading选项用于指定列的显示标题;justify选项用于指定列标题的对齐格式(left,center,right);format选项用于
     指定列的显示格式，其中格式模型包含以下一些元素。
     An:设置char,varchar2类型列的显示宽度;
     9: 在number类型列上禁止显示前导0;
     0: 在number类型列上强制显示前导0;
     $: 在number类型列前显示美元符号;
     L: 在number类型列前显示本地货币符号;
     .: 指定number类型列的小数点位置;
     ,: 指定number类型列的千分隔符;
    eg1:使用column设置列显示格式
     sql>col ename heading 'name' format a10
     sql>col sal heading 'sal' format L99999.99
     sql>select ename,sal,hiredate from emp
     sql>where empno=7788;
     name                       sal HIREDATE
     ---------- ------------------- -------------------
     SCOTT                ￥3000.00 04/19/1987 00:00:00
     sql>col ename clear
     sql>col sal clear
     sql>select ename,sal,hiredate from emp
     sql>where empno=7788;
     (2)title
     该命令用于指定页标题，页标题会自动显示在页的中央。如果页标题由多个词组成，则用单引号引住。如果要将页
    标题分布在多行显示，则用"|"分开不同单词。如果不希望显示页标题，则使用"ttitle off"命令，禁止显示,eg:
SQL> set linesize 40
SQL> ttitle 'employee report'
SQL> select ename,sal,hiredate from emp where empno=7788;

星期二 5月 20                第    1
            employee report

ENAME             SAL
---------- ----------
HIREDATE
-------------------
SCOTT            3000
04/19/1987 00:00:00
   (3)btitle
该命令用于指定页脚注，页脚注会自动显示在页的中央。如果页脚注由多个词组成，则用单引号引注。如果要将页脚注
分布在多行显示，则用"|"分开不同单词。如果不希望显示页脚注，则使用"btitle off"命令，禁止显示。eg:
   SQL> btitle 'page end'
   SQL> select ename,sal,hiredate from emp where empno=7788
ENAME             SAL
---------- ----------
HIREDATE
-------------------
SCOTT            3000
04/19/1987 00:00:00
         page end
(4)break
该命令用于禁止显示重复行，并将显示结果分隔为几个部分，以表现更友好的显示结果，通常应该在order by 的排序列上
使用该命令。eg:
SQL> set pagesize 40
SQL> break on deptno skip 1
SQL> select deptno,ename,sal from emp order by deptno
2 ;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 CLARK            2450
           KING             5000
           MILLER           1300

        20 JONES            2975
           FORD             3000
           ADAMS            1100
           SMITH             800
           SCOTT            3000

        30 WARD             1250
           TURNER           1500
           ALLEN            1600
           JAMES             950
           BLAKE            2850
           MARTIN           1250
   6.交互式命令
    如果经常要执行某些sql语句和sql*plus命令，可以将这些语句和命令存放到sql脚本中。通过使用sql脚本，
   一方面可以降低命令输入量，另一方面可以避免用户的输入错误。为了使得sql脚本可以根据不同输入获得
   不同结果，需要在sql脚本中包含交互式命令。通过使用交互式命令，可以在sql*plus中定义变量，并且在运行
   sql脚本时可以为这些变量动态输入数据。下面介绍sql*plus的交互命令，以及引用变量所使用的标号。
(1)&
    引用替代变量(substitution variable)时，必须要带有该标号。如果替代变量已经定义，则会直接使用其数据，
    如果替代变量没有定义，则会临时定义替代变量(该替代变量只在当前语句中起作用),并需要为其输入数据。
    注:如果替代变量为数字列则提供数据，则可以直接引用;如果替代变量为字符类型列或日期类型列提供数据，
    则必须要用单引号引注。eg:
SQL> select ename,sal from emp where deptno=&no and job='&job';
输入 no 的值: 20
输入 job 的值: CLERK
原值    1: select ename,sal from emp where deptno=&no and job='&job'
新值    1: select ename,sal from emp where deptno=20 and job='CLERK'
   (2)&&
   该标号类似于单个&标号。但需要注意，&标号所定义的替代变量只在当前语句中起作用;而&&标号所定义的变量
会在当前sql*plus环境中一直生效。eg:
SQL> select ename,sal from emp where deptno=&&no and job='&&job' --定义了no变量
输入 no 的值: 20
输入 job 的值: CLERK
原值    1: select ename,sal from emp where deptno=&&no and job='&&job'
新值    1: select ename,sal from emp where deptno=20 and job='CLERK'
SQL> select ename,sal from emp where deptno=&no;           
原值    1: select ename,sal from emp where deptno=&no             --直接引用no变量
新值    1: select ename,sal from emp where deptno=20
ENAME             SAL
---------- ----------
SMITH             800
JONES            2975
SCOTT            3000
ADAMS            1100
FORD             3000
如例所示，当第一次引用no变量时，使用&&标号需要为其输入数据;当第二次引用no变量时，
使用&标号直接引用其原有值，而不需要输入数据。
(3)define
该命令用于定义类型为char的替代变量，而且该命令的定义的替代变量只在当前sql*plus环境中起作用。
当使用该命令定义变量时，如果变量值包含空格或区分大小写，则用引号引注。另外，使用"define变量名"可以检查变量
是否已经定义。eg:
sql>set verify off
sql>define title=CLERK
sql>select ename,sal from where job='&title';
(4)accept
该命令可以用于定义char,number和date类型的替代变量。与define命令相比，accept命令更加灵活。当使用该命令定义替代
变量时，还可以指定变量输入提示、变量输入格式、隐藏输入内容。
eg1:指定变量输入提示
SQL> accept title prompt '请输入岗位:'
请输入岗位:CLERK
SQL> select ename,sal from emp where job='&title';
原值    1: select ename,sal from emp where job='&title'
新值    1: select ename,sal from emp where job='CLERK'

ENAME             SAL
---------- ----------
SMITH             800
ADAMS            1100
JAMES             950
MILLER           1300
eg2:隐藏用户输入
sql>accept pwd hide
(5)undefine
该命令用于清除替代变量的定义。eg:
sql>undefine pwd
SQL> disc
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开
SQL> conn scott/&pwd
输入 pwd 的值: yhai1981
已连接
(6)prompt的pause
prompt命令用于输出提示信息，而pause命令则用于暂停脚本执行。在sql脚本中结合使用这两条命令，可以控制sql脚本
的暂停的执行。假定在a.sql脚本中包含以下命令：
prompt '按<Return>键继续'
pause
当运行该sql脚本时，会暂停执行，eg:
sql>@c:\a.sql
'按<Return>键继续'
(7)variable
该命令用于在sql*plus中定义绑定变量。当在sql语句或pl/sql块中引用绑定变量时，必须要在绑定变量前加冒号(:);
当直接给绑定变量赋值时，需要使用execute命令(类似于调用存储过程).示例如下:
sql>var no number
sql>exec :no:=7788
sql>select ename from emp where empno=:no;
ename
------------------
scott
(8)print
该命令用于输出绑定变量结果,eg:
SQL> print no

        NO
----------
      7788
7.显示和设置环境变量
使用sql*plus的环境变量可以控制其运行环境，例如设置行显示宽度，设置每页显示的行数、
设置自动提交标记、设置自动跟踪等等。使用show命令可以显示当前sql*plus的环境变量设置
:使用set命令可以修改当前sql*plus的环境变量设置。下面介绍常用的sql*plus环境变量。
(1)显示所有环境变量
为了显示sql*plus的所有环境变量，必须要使用show all命令。示例如下:
SQL> show all
appinfo 为 OFF 并且已设置为 "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFF 为下一条 SELECT 语句的前几个字符
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK 为 ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
editfile "afiedt.buf"
embedded OFF
escape OFF
用于 6 或更多行的 FEEDBACK ON
flagger OFF
flush ON
heading ON
headsep "|" (hex 7c)
instance "local"
linesize 80
lno 4
loboffset 1
logsource ""
long 80
longchunksize 80
markup HTML OFF HEAD "<style type='text/css'> body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} p {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0px 0px 0px 0px;} h1 {font:16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} a {font:9pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}</style><title>SQL*Plus Report</title>" BODY "" TABLE "border='1' width='90%' align='center' summary='Script output'" SPOOL OFF ENTMAP ON PREFORMAT OFF
newpage 1
null ""
numformat ""
numwidth 10
pagesize 14
PAUSE 为 OFF
pno 1
recsep WRAP
recsepchar " " (hex 20)
release 1002000100
repfooter OFF 为 NULL
repheader OFF 为 NULL
serveroutput OFF
shiftinout INVISIBLE
showmode OFF
spool ON
sqlblanklines OFF
sqlcase MIXED
sqlcode 0
sqlcontinue "> "
sqlnumber ON
sqlpluscompatibility 10.2.0
sqlprefix "#" (hex 23)
sqlprompt "SQL> "
sqlterminator ";" (hex 3b)
suffix "sql"
tab ON
termout ON
timing OFF
trimout ON
trimspool OFF
ttitle OFF 为下一条 SELECT 语句的前几个字符
underline "-" (hex 2d)
USER 为 "SCOTT"
verify ON
wrap : 将换至下一行
SQL> spool off

(2)arraysize
该环境变量用于指定数组提取尺寸，其默认值为15.该值越大，网络开销将会越低，但占用内存会增加。假定使用默认值，
如果查询返回行数为50行，则需要通过网络传送4将数据;如果设置为25，则网络传送次数只有两次。eg:

SQL> show arraysize
arraysize 15
SQL> set arraysize 25
(3)autocommit
该环境变量用于设置是否自动提交dml语句，其默认值为off(表示禁止自动提交)。当设置为ON时，每次执行DML
语句都会自动提交。eg:
SQL> show autocommit
autocommit OFF
SQL> set autocommit on
SQL> show autocommit
autocommit IMMEDIATE
(4)colsep
该环境变量用于设置列之间的分隔符，默认分隔符为空格。如果要使用其它分隔符，则使用set命令进行设置。eg:
sql>set colsep |  
SQL> select ename,sal from emp where empno=7788

ENAME     |       SAL
----------|----------
SCOTT     |      3000
(5)feedback
该环境变量用于指定显示反馈行数信息的最低行数，其默认值为6。如果要禁止显示行数反馈信息，则将feedback
设置为off。假设只要有查询结果就返回行数，那么可以将该环境变量设置为1.eg:
sql>set feedback 1
sql>select ename,sal from emp where empno=7788;

ENAME     |       SAL
----------|----------
SCOTT     |      3000
已选择 1 行。
(6)heading
该环境变量用于设置是否显示标题，其默认值为on。如果不显示列标题，则设置为off。eg:
sql>set heading off
sql>select ename,sal from emp where empno=7788
SCOTT     |      3000
(7)linesize
该环境变量用于设置行宽度，默认值为80。在默认情况下，如果数据长度超过80个字符，那么在sql*plus中会折
行显示数据结果。要在一行中显示全部数据，应该设置更大的值。eg:
(8)pagesize
该环境变量用于设置每页所显示的行数,默认值为14
set pagesize 0;　　　//输出每页行数，缺省为24,为了避免分页，可设定为0。
(9)long
该环境变量用于设置long和lob类型列的显示长度。默认值为80,也就是说当查询long或lob列时，只会显示该列的前80个字符，
应该设置更大的值。eg:
sql>show long
long 80
sql>set long 300
(10)serveroutput
该环境变量用于控制服务器输出，其默认值为off，表示禁止服务器输出。在默认情况下，当调用dbms_output包时，
不会在sql*plus屏幕上显示输出结果。在调用dbms_output包时，为了在屏幕上输出结果，必须要将serveroutput设置
为on。eg:
sql>set serveroutput on
sql>exec dbms_output.put_line('hello')
(11)termout
该环境变量用于控制sql脚本的输出，其默认值为ON。当使用默认值时，如果sql脚本有输出结果，则会在屏幕上输出
显示结果，如果设置为OFF，则不会在屏幕上输出sql脚本。eg:
SQL> set termout off
SQL> @c:\a
(12)time
该环境变量用于设置在sql提示符前是否显示系统时间，默认值为off,表示禁止显示系统时间。如果设置为on,
则在sql提示符前会显示系统时间.eg:
SQL> set time on
12:09:59 SQL>
(13)timing
该环境变量用于设置是否要显示sql语句执行时间,默认值为off，表示不会显示sql语句执行时间。如果设置为
ON，则会显示sql语句执行时间。eg:
sql>set timing on
SQL> select count(*) from emp;

COUNT(*)
----------
        14

已选择 1 行。

已用时间: 00: 00: 00.03
(14)trimspool
set trimout on;　　　//去除标准输出每行的拖尾空格，缺省为off
set trimspool on;　　//去除重定向（spool）输出每行的拖尾空格，缺省为off

如果trimspool设置为on，将移除spool文件中的尾部空格 ，trimout同trimspool功能相似，只不过对象是控制台。
If trimspool is set to on, it will remove trailing blanks in spooled files.
See also trimout which does the same thing to the output to the console (terminal).
eg:
set trimspool off
spool c:\temp\trimspool.txt
declare
v_name varchar2(30);
begin
SELECT table_name into v_name
FROM all_tables
WHERE rownum =1;
dbms_output.put_line(v_name);
end;
/
set trimspool on
declare
v_name varchar2(30);
begin
SELECT table_name into v_name
FROM all_tables
WHERE rownum =1;
dbms_output.put_line(v_name);
end;
/
spool off

-- from ITPUB

文章来源:http://blog.163.com/ccbobo_cat/blog/static/32099462200932325331787