SQLPLUS使用技巧.docx

1、SQLPLUS使用技巧sql* plus使用技巧集锦: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/passwordserver如上所示:username用于指定数

2、据库用户名，password用于指定用户口令，server则用于指定主机字符串(网络服务名).当连接到本地数据时，不需要提供网络服务名,如果要连接到远程数据库，则必须要使用网络服务名.(2)在windows环境中运行sql*plus如果在windows环境中安装了oralce数据库产品，那么可以在窗口环境中运行sql*plus具体方法: “开始-程序-oracle-oradb10g_home1-application development-sql*plus”2.连接命令(1)connect该命令用于连接到数据库。注意，使用该命令建立新会话时，会自动断开先前会话，示例如下:sqlconn sc

3、ott/yhai1981demo(2)disconnect该命令用于断开已经存在的数据库连接。注：该命令只是断开连接会话，而不会退出sql*plus，示例如下:sqldisc(3)password该命令用于修改用户的口令。注，任何用户都可以使用该命令修改其自身口令，但如果要修改其他用户的口令时，则必须以DBA身份(sys和system)登录，在sql*plus中，当修改用户口令时，可以使用该命令取代sql命令alter user,示例如下:sqlpassw更改scott的口令旧口令:*新口令:*重新键入新口令:*口令已更改sql(4)exit该命令用于退出 sql*plus,另外你也可以使用q

4、uit命令退出sql*plus.使用该命令不仅会断开连接，而且也会退出sql*plus注:默认情况下，当执行该命令时会自动提交事务。3，编辑命令(1)list该命令用于列出sql缓冲区的内容，使用该命令可以列出sql缓冲某行，某几行或所有行的内容。在显示结果中,数据字为具体的行号，而”*”则表示当前行。示例一:列出sql缓冲区所有内容sqll示例二:列出sql缓冲区首行内容:sqll1(2)append该命令用于在sql缓冲区的当前行尾部添加内容。注:该命令将内容追加到标记为”*”的行的尾部，示例如下:以下是引用片段：sqll 1 select empno,ename,sal,hiredate

5、,comm,deptno 2 from emp 3* where deptno=10 sqla and job=CLERK sqll SQL list 1 select empno,ename,sal,hiredate,comm,deptno 2 from emp 3* where deptno=10 and job=CLERK (3)change该命令用于修改sql缓冲区的内容。如果在编写sql语句时写错了某个词，那么使用该命令可以进行修改，以下是引用片段：sqlselect ename from temp where deptno=10; SQL c /temp/emp 1* select

6、 ename from emp where deptno=10 (4)del该命令用于删除sql缓冲区中内容，使用它可以删除某行，某几行或所有行，在默认情况下，当直接执行del时，只删除当前行的内容，示例如下:以下是引用片段：SQL l 1 select ename 2 from emp 3* where deptno=20 sqldel SQL l 1 select ename 2* from emp 如果一次要删除多行，则指定起始行号和终止行号，例如”del 3 5(5)input该命令用于在sql缓冲区的当前行后新增加一行。示例如下:以下是引用片段：SQL l 1 select enam

7、e 2* from emp sqli where deptno=30 如果要在首行前增加内容，则使用”0文本”以下是引用片段：sql0 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)edit该命令用于编辑sql缓冲区的内容。当运行该命令时，在windows平台中会自动启动”记事本”,以编辑sql缓冲区(8)run和/run的/命令都可以用于运行sql缓冲区中的sql语句。注:当使用run命令

8、时，还会列出sql缓冲区内容,eg：SQL run1* select ename from emp where deptno=204.文件操纵命令(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命令作

9、用恰好相反，用于将sql脚本中的所有内容装载到sql缓冲区中。eg:SQL get c:a.sql1* select ename from emp where deptno=20(3)start和start和命令用于运行sql脚本文件。注:当运行sql脚本文件时，应该指定文件路径.eg:SQL c:a.sqlENAME-SMITHJONESSCOTTADAMSFORD(4)该命令与命令类似，也可以运行脚本文件，但主要作用是在脚本文件中嵌套调用其它的脚本文件。当使用该命令嵌套脚本文件时，可在调用文件所在目录下查找相应文件名。(5)edit该命令不仅可用于编辑sql缓冲区内容，也可以用于编辑sql

10、脚本文件。当运行该命令时，会启动默认的系统编辑器来编辑sql脚本。运行方法为:sqledit c:/a.sql(6)spool该命令用于将sql*plus屏幕内容存放到文本文件中。执行该命令时，应首先建立假脱机文件，并将随后sql*plus屏幕的所有内容全部存放到该文件中，最后使用spool off命令关闭假脱机文件。eg:sqlspool c:a.sql5.格式命令sql*plus不仅可以用于执行sql语句、pl/sql块，而且还可以根据select结果生成报表。使用sql*plus的格式命令可以控制报表的显示格式，例如使用column命令可以控制列的显示格式，使用ttitle命令可以指定页

11、标题;使用btitle命令可以指定页脚注。(1)column该命令用于控制列的显示格式。column命令包含有四个选项，其中clear选项用于清除已定义列的显示格式:heading选项用于指定列的显示标题;justify选项用于指定列标题的对齐格式(left,center,right);format选项用于指定列的显示格式，其中格式模型包含以下一些元素。An:设置char,varchar2类型列的显示宽度;9: 在number类型列上禁止显示前导0;0: 在number类型列上强制显示前导0;$: 在number类型列前显示美元符号;L: 在number类型列前显示本地货币符号;.: 指定nu

12、mber类型列的小数点位置;,: 指定number类型列的千分隔符;eg1:使用column设置列显示格式sqlcol ename heading name format a10sqlcol sal heading sal format L99999.99sqlselect ename,sal,hiredate from empsqlwhere empno=7788;name sal HIREDATE- - -SCOTT ￥3000.00 04/19/1987 00:00:00sqlcol ename clearsqlcol sal clearsqlselect ename,sal,hired

13、ate from empsqlwhere empno=7788;(2)title该命令用于指定页标题，页标题会自动显示在页的中央。如果页标题由多个词组成，则用单引号引住。如果要将页标题分布在多行显示，则用”|”分开不同单词。如果不希望显示页标题，则使用”ttitle off”命令，禁止显示,eg:SQL set linesize 40SQL ttitle employee reportSQL select ename,sal,hiredate from emp where empno=7788;星期二 5月 20 第 1employee reportENAME SAL- -HIREDATE-S

14、COTT 300004/19/1987 00:00:00(3)btitle该命令用于指定页脚注，页脚注会自动显示在页的中央。如果页脚注由多个词组成，则用单引号引注。如果要将页脚注分布在多行显示，则用”|”分开不同单词。如果不希望显示页脚注，则使用”btitle off”命令，禁止显示。eg:SQL btitle page endSQL select ename,sal,hiredate from emp where empno=7788ENAME SAL- -HIREDATE-SCOTT 300004/19/1987 00:00:00page end(4)break该命令用于禁止显示重复行，并

15、将显示结果分隔为几个部分，以表现更友好的显示结果，通常应该在order by 的排序列上使用该命令。eg:SQL set pagesize 40SQL break on deptno skip 1SQL select deptno,ename,sal from emp order by deptno2 ;DEPTNO ENAME SAL- - -10 CLARK 2450KING 5000MILLER 130020 JONES 2975FORD 3000ADAMS 1100SMITH 800SCOTT 300030 WARD 1250TURNER 1500ALLEN 1600JAMES 950

16、BLAKE 2850MARTIN 12506.交互式命令如果经常要执行某些sql语句和sql*plus命令，可以将这些语句和命令存放到sql脚本中。通过使用sql脚本，一方面可以降低命令输入量，另一方面可以避免用户的输入错误。为了使得sql脚本可以根据不同输入获得不同结果，需要在sql脚本中包含交互式命令。通过使用交互式命令，可以在sql*plus中定义变量，并且在运行sql脚本时可以为这些变量动态输入数据。下面介绍sql*plus的交互命令，以及引用变量所使用的标号。(1)&引用替代变量(substitution variable)时，必须要带有该标号。如果替代变量已经定义，则会直接使用其数

17、据，如果替代变量没有定义，则会临时定义替代变量(该替代变量只在当前语句中起作用),并需要为其输入数据。注:如果替代变量为数字列则提供数据，则可以直接引用;如果替代变量为字符类型列或日期类型列提供数据，则必须要用单引号引注。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

18、 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 deptn

19、o=20 and job=CLERKSQL 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=20ENAME SAL- -SMITH 800JONES 2975SCOTT 3000ADAMS 1100FORD 3000如例所示，当第一次引用no变量时，使用&标号需要为其输入数据;当第二次引用no变量时，使用&标号直接引用其原有值，而不需要输入数据。(3)de

20、fine该命令用于定义类型为char的替代变量，而且该命令的定义的替代变量只在当前sql*plus环境中起作用。当使用该命令定义变量时，如果变量值包含空格或区分大小写，则用引号引注。另外，使用”define变量名”可以检查变量是否已经定义。eg:sqlset verify offsqldefine title=CLERKsqlselect ename,sal from where job=&title; (4)accept该命令可以用于定义char,number和date类型的替代变量。与define命令相比，accept命令更加灵活。当使用该命令定义替代变量时，还可以指定变量输入提示、变量输

21、入格式、隐藏输入内容。eg1:指定变量输入提示SQL accept title prompt 请输入岗位:请输入岗位:CLERKSQL 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=CLERKENAME SAL- -SMITH 800ADAMS 1100JAMES 950MILLER 1300eg2:隐藏用户输入sqlaccept pwd hide(5)undefine该命令用

22、于清除替代变量的定义。eg:sqlundefine pwdSQL disc从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options 断开SQL conn scott/&pwd输入 pwd 的值: yhai1981已连接(6)prompt的pauseprompt命令用于输出提示信息，而pause命令则用于暂停脚本执行。在sql脚本中结合使用这两条命令，可以控制sql脚本的暂停的执行。假定在a.sql脚本中包含

23、以下命令：prompt 按键继续pause当运行该sql脚本时，会暂停执行，eg:sqlc:a.sql按键继续(7)variable该命令用于在sql*plus中定义绑定变量。当在sql语句或pl/sql块中引用绑定变量时，必须要在绑定变量前加冒号(;当直接给绑定变量赋值时，需要使用execute命令(类似于调用存储过程).示例如下:sqlvar no numbersqlexec :no:=7788sqlselect ename from emp where empno=:no;enamescott(8)print该命令用于输出绑定变量结果,eg:SQL print noNO-77887.显示

24、和设置环境变量使用sql*plus的环境变量可以控制其运行环境，例如设置行显示宽度，设置每页显示的行数、设置自动提交标记、设置自动跟踪等等。使用show命令可以显示当前sql*plus的环境变量设置:使用set命令可以修改当前sql*plus的环境变量设置。下面介绍常用的sql*plus环境变量。(1)显示所有环境变量为了显示sql*plus的所有环境变量，必须要使用show all命令。示例如下:SQL show allappinfo 为 OFF 并且已设置为 “SQL*Plus”arraysize 15autocommit OFFautoprint OFFautorecovery OFFa

25、utotrace OFFblockterminator “.” (hex 2e)btitle OFF 为下一条 SELECT 语句的前几个字符cmdsep OFFcolsep ” ”compatibility version NATIVEconcat “.” (hex 2e)copycommit 0COPYTYPECHECK 为 ONdefine “&” (hex 26)describe DEPTH 1 LINENUM OFF INDENT ONecho OFFeditfile “afiedt.buf”embedded OFFescape OFF用于 6 或更多行的 FEEDBACK ON以下

26、是引用片段：flagger OFF flush ON heading ON headsep “|” (hex 7c) instance “local” linesize 80 lno 4 loboffset 1 logsource “” long 80 longchunksize 80 SPOOL OFF ENTMAP ON PREFORMAT OFF newpage 1 null “” numformat “” numwidth 10 pagesize 14 PAUSE 为 OFF pno 1 recsep WRAP recsepchar ” ” (hex 20) release 10020

27、00100 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 termou

28、t 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 arraysizearraysize 15SQL

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