03 Oracle导入SQL脚本执行和常用命令大全.docx

1、03 Oracle导入SQL脚本执行和常用命令大全技术文档模板（v 130310）作者王卫锋审核分类Oracle子类DateBase 10gR2更新时间2013-03-10关键字Oracle DataBase 10gR2摘要Oracle导入SQL脚本执行和常用命令大全主要适用环境Oracle DataBase 10gR2版本说明版本号拟制/修改责任人拟制/修改日期修改内容/理由V130310王卫锋2013-03-10新建目录版本说明 21 系统和软硬件环境说明 42 执行外部脚本 53执行一个SQL脚本文件 54对当前的输入进行编辑 55重新运行上一次运行的sql语句 66将显示的内容输出到指

2、定文件 67关闭spool输出 68显示一个表的结构 69 COL命令 610屏蔽掉一个列中显示的相同的值 911在上面屏蔽掉一个列中显示的相同的值的显示中，每当列值变化时在值变化之前插入n个空行 1012显示对BREAK的设置 1013删除之前8、9的设置 1014 set命令 1015修改sql buffer中的当前行中，第一个出现的字符串 1416编辑sql buffer中的sql语句 1417显示sql buffer中的sql语句，list n显示sql buffer中的第n行，并使第n行成为当前行 1418在sql buffer的当前行下面加一行或多行 1519将指定的文本加到sql

3、 buffer的当前行后面 1520将sql buffer中的sql语句保存到一个文件中 1521将一个文件中的sql语句导入到sql buffer中 1622再次执行刚才已经执行的sql语句 1623执行一个存储过程 1624在sql*plus中连接到指定的数据库 1625设置每个报表的顶部标题 1626设置每个报表的尾部标题 1627写一个注释 1628将指定的信息或一个空行输出到屏幕上 1729将执行的过程暂停，等待用户响应后继续执行 1730将一个数据库中的一些数据拷贝到另外一个数据库(如将一个表的数据拷贝到另一个数据库) 1731不退出sql*plus，在sql*plus中执行一个操

4、作系统命令 1732在sql*plus中，切换到操作系统命令提示符下，运行操作系统命令后，可以再次切换回sql*plus 1733显示sql*plus命令的帮助 1834显示sql*plus系统变量的值或sql*plus环境变量的值 1835查询一个用户下的对象 1936查询一个用户下的所有的表 1937查询一个用户下的所有的索引 1938定义一个用户变量 1939定义一个绑定变量 2140 &与&的区别 2141在输入sql语句的过程中临时先运行一个sql*plus命令 2242 SQLPlus中的快速复制和粘贴技巧 231 系统和软硬件环境说明硬件环境：x86服务器软件环境：RedHat

5、Enterprise Linux 5.5，Oracle DataBase 10gR22 执行外部脚本在SQL_PLUS里面执行：sqlfull_path/test.sql; 例：sqlD:/test.sql;不需要commit; 一般都是在test.sql 里面最后加上一个commit;但是需要注意的是：在test.sql文件里面每条语句后必须加上“;”,代表一条语句的执行。3执行一个SQL脚本文件SQLstart file_nameSQL file_name我们可以将多条sql语句保存在一个文本文件中，这样当要执行这个文件中的所有的sql语句时，用上面的任一命令即可，这类似于dos中的批处理

6、。与的区别是什么？等于start命令，用来运行一个sql脚本文件。命令调用当前目录下的，或指定全路径，或可以通过SQLPATH环境变量搜寻到的脚本文件。该命令使用是一般要指定要执行的文件的全路径，否则从缺省路径(可用SQLPATH变量指定)下读取指定的文件。用在sql脚本文件中，用来说明用执行的sql脚本文件与所在的文件在同一目录下，而不用指定要执行sql脚本文件的全路径，也不是从SQLPATH环境变量指定的路径中寻找sql脚本文件，该命令一般用在脚本文件中。如：在c:temp目录下有文件start.sql和nest_start.sql，start.sql脚本文件的内容为：nest_start

7、.sql，相当于 c:tempnest_start.sql则我们在sql*plus中，这样执行：SQL c:tempstart.sql4对当前的输入进行编辑SQLedit5重新运行上一次运行的sql语句SQL/6将显示的内容输出到指定文件SQL SPOOL file_name在屏幕上的所有内容都包含在该文件中，包括你输入的sql语句。7关闭spool输出SQL SPOOL OFF只有关闭spool输出，才会在输出文件中看到输出的内容。8显示一个表的结构SQL desc table_name 9 COL命令主要格式化列的显示形式。该命令有许多选项，具体如下：COLUMN column|expr

8、option .Option选项可以是如下的子句:ALIAS aliasCLEARFOLD_AFTERFOLD_BEFOREFORMAT formatHEADING textJUSTIFYLIKE expr|aliasNEWLINENEW_VALUE variableNOPRINT|PRINTNULL textOLD_VALUE variableON|OFFWRAPPED|WORD_WRAPPED|TRUNCATED1).改变缺省的列标题COLUMN column_name HEADING column_headingFor example:Sqlselect * from dept; DEP

9、TNO DNAME LOC- - - 10 ACCOUNTING NEW YORKsqlcol LOC heading locationsqlselect * from dept; DEPTNO DNAME location- - - 10 ACCOUNTING NEW YORK2).将列名ENAME改为新列名EMPLOYEE NAME并将新列名放在两行上：sqlselect * from empDepartment name Salary- - - 10 aaa 11 sql COLUMN ENAME HEADING Employee|Namesqlselect * from emp Emp

10、loyeeDepartment name Salary- - - 10 aaa 11note: the col heading turn into two lines from one line.3).改变列的显示长度：FORMAT formatsqlselect empno,ename,job from emp; EMPNO ENAME JOB - - - 7369 SMITH CLERK 7499 ALLEN SALESMAN 7521 WARD SALESMAN sql col ename format a40 EMPNO ENAME JOB- - - 7369 SMITH CLERK

11、7499 ALLEN SALESMAN 7521 WARD SALESMAN4).设置列标题的对齐方式JUSTIFYSQL col ename justify centerSQL / EMPNO ENAME JOB- - - 7369 SMITH CLERK 7499 ALLEN SALESMAN7521 WARD SALESMAN对于NUMBER型的列，列标题缺省在右边，其它类型的列标题缺省在左边5).不让一个列显示在屏幕上NOPRINT|PRINTSQL col job noprintSQL / EMPNO ENAME- - 7369 SMITH 7499 ALLEN7521 WARD6)

12、.格式化NUMBER类型列的显示：SQL COLUMN SAL FORMAT ,990SQL /EmployeeDepartment Name Salary Commission- - - -30 ALLEN ,600 3007).显示列值时，如果列值为NULL值，用text值代替NULL值COMM NULL textSQLCOL COMM NULL text8).设置一个列的回绕方式WRAPPED|WORD_WRAPPED|TRUNCATED COL1-HOW ARE YOU?SQLCOL COL1 FORMAT A5SQLCOL COL1 WRAPPEDCOL1-HOW ARE YOU?S

13、QL COL COL1 WORD_WRAPPEDCOL1-HOWAREYOU?SQL COL COL1 WORD_WRAPPEDCOL1-HOW A9).显示列的当前的显示属性值SQL COLUMN column_name10).将所有列的显示属性设为缺省值SQL CLEAR COLUMNS10屏蔽掉一个列中显示的相同的值BREAK ON break_columnSQL BREAK ON DEPTNOSQL SELECT DEPTNO, ENAME, SAL FROM EMP WHERE SAL BREAK ON DEPTNO SKIP 1SQL /DEPTNO ENAME SAL- - -1

14、0 CLARK 2450MILLER 130020 SMITH 800ADAMS 110012显示对BREAK的设置SQL BREAK13删除之前8、9的设置SQL CLEAR BREAKS14 set命令该命令包含许多子命令：SET system_variable valuesystem_variable value 可以是如下的子句之一：APPINFOARRAYSIZEAUTOCOMMITAUTOPRINTAUTORECOVERY ON|OFFAUTOTRACE EXPLAIN STATISTICSBLOCKTERMINATORCMDSEPCOLSEPCOMPATIBILITYCONCAT

15、COPYCOMMITCOPYTYPECHECKDEFINEDESCRIBE DEPTH LINENUM INDENT ECHOEDITFILE file_name.extEMBEDDEDESCAPEFEEDBACKFLAGGER OFF|ENTRY |INTERMEDIATE|FULLFLUSHHEADINGHEADSEPINSTANCE instance_path|LOCALLINESIZELOBOFFSETLOGSOURCE pathnameLONGLONGCHUNKSIZEMARKUP HTML ON|OFF HEAD text BODY text ENTMAP SPOOL PREFOR

16、MATNEWPAGENULL textNUMFORMAT formatNUMWIDTHPAGESIZEPAUSERECSEPRECSEPCHARSERVEROUTPUT SIZE n FORMAT WRAPPED|WORD_WRAPPED|TRUNCATEDSHIFTINOUTSHOWMODESQLBLANKLINESSQLCASESQLCONTINUE |textSQLNUMBERSQLPREFIXSQLPROMPTSQLTERMINATORSUFFIXTABTERMOUTTIMETIMINGTRIMOUTTRIMSPOOLUNDERLINEVERIFYWRAP1).设置当前session是

17、否对修改的数据进行自动提交SQLSET AUTOCOMMIT ON|OFF|IMMEDIATE| n2).在用start命令执行一个sql脚本时，是否显示脚本中正在执行的SQL语句SQL SET ECHO3).是否显示当前sql语句查询或修改的行数SQL SET FEEDBACK默认只有结果大于6行时才显示结果的行数。如果set feedback 1 ，则不管查询到多少行都返回。当为off 时，一律不显示查询的行数。4).是否显示列标题SQL SET HEADING当set heading off 时，在每页的上面不显示列标题，而是以空白行代替。5).设置一行可以容纳的字符数SQL SET L

18、INESIZE如果一行的输出内容大于设置的一行可容纳的字符数，则折行显示。6).设置页与页之间的分隔SQL SET NEWPAGE当set newpage 0 时，会在每页的开头有一个小的黑方框。当set newpage n 时，会在页和页之间隔着n个空行。当set newpage none 时，会在页和页之间没有任何间隔。7).显示时，用text值代替NULL值SQL SET NULL text8).设置一页有多少行数SQL SET PAGESIZE如果设为0，则所有的输出内容为一页并且不显示列标题。9).是否显示用DBMS_OUTPUT.PUT_LINE包进行输出的信息。SQL SET S

19、ERVEROUTPUT 在编写存储过程时，我们有时会用dbms_output.put_line将必要的信息输出，以便对存储过程进行调试，只有将serveroutput变量设为on后，信息才能显示在屏幕上。10).当SQL语句的长度大于LINESIZE时，是否在显示时截取SQL语句。SQL SET WRAP当输出的行的长度大于设置的行的长度时（用set linesize n命令设置），当set wrap on时，输出行的多于的字符会另起一行显示，否则，会将输出行的多于字符切除，不予显示。11).是否在屏幕上显示输出的内容，主要用与SPOOL结合使用。SQL SET TERMOUT在用spool命

20、令将一个大表中的内容输出到一个文件中时，将内容输出在屏幕上会耗费大量的时间，设置set termspool off后，则输出的内容只会保存在输出文件中，不会显示在屏幕上，极大的提高了spool的速度。12).将SPOOL输出中每行后面多余的空格去掉SQL SET TRIMSOUT 13).显示每个sql语句花费的执行时间SET TIMING14).遇到空行时不认为语句已经结束，从后续行接着读入。SET SQLBLANKLINES ONsql*plus中, 不允许sql语句中间有空行, 这在从其它地方拷贝脚本到sql*plus中执行时很麻烦. 比如下面的脚本:select deptno, emp

21、no, enamefrom empwhere empno = 7788;如果拷贝到sql*plus中执行, 就会出现错误。这个命令可以解决该问题15).设置DBMS_OUTPUT的输出SET SERVEROUTPUT ON BUFFER 20000用dbms_output.put_line(strin_content);可以在存储过程中输出信息，对存储过程进行调试，如果想让dbms_output.put_line( abc);的输出显示为：SQL abc，而不是SQLabc，则在SET SERVEROUTPUT ON后加format wrapped参数。16).输出的数据为html格式SET

22、MARKUP html在8.1.7版本以后, sql*plus中有一个set markup html的命令, 可以将sql*plus的输出以html格式展现。注意其中的spool on, 当在屏幕上输出的时候, 我们看不出与不加spool on有什么区别, 但是当我们使用spool filename 输出到文件的时候, 会看到spool文件中出现了等tag。15修改sql buffer中的当前行中，第一个出现的字符串CHANGE /old_value/new_valueSQL l 1* select * from deptSQL c/dept/emp 1* select * from emp1

23、6编辑sql buffer中的sql语句EDIT17显示sql buffer中的sql语句，list n显示sql buffer中的第n行，并使第n行成为当前行LIST n18在sql buffer的当前行下面加一行或多行INPUT19将指定的文本加到sql buffer的当前行后面APPENDSQL select deptno, 2 dname 3 from dept; DEPTNO DNAME- - 10 ACCOUNTING 20 RESEARCH 30 SALES 40 OPERATIONSSQL L 2 2* dnameSQL a,loc 2* dname,locSQL L 1 se

24、lect deptno, 2 dname,loc 3* from deptSQL / DEPTNO DNAME LOC- - - 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON20将sql buffer中的sql语句保存到一个文件中SAVE file_name21将一个文件中的sql语句导入到sql buffer中GET file_name22再次执行刚才已经执行的sql语句RUN或/23执行一个存储过程EXECUTE procedure_name24在sql*plus中连接到指定的

25、数据库CONNECT user_name/passwddb_alias25设置每个报表的顶部标题TTITLE26设置每个报表的尾部标题BTITLE27写一个注释REMARK text28将指定的信息或一个空行输出到屏幕上PROMPT text29将执行的过程暂停，等待用户响应后继续执行PAUSE textsqlPAUSE Adjust paper and press RETURN to continue.30将一个数据库中的一些数据拷贝到另外一个数据库(如将一个表的数据拷贝到另一个数据库)COPY FROM database | TO database | FROM database TO d

26、atabasedestination_table(column, column, column, .) USING querysqlCOPY FROM SCOTT/TIGERHQ TO JOHN/CHROMEWESTcreate emp_tempUSING SELECT * FROM EMP31不退出sql*plus，在sql*plus中执行一个操作系统命令HOSTsql host hostname该命令在windows下可能被支持。32在sql*plus中，切换到操作系统命令提示符下，运行操作系统命令后，可以再次切换回sql*plus!sql!$hostname$exitsql该命令在windows下不被支持。33显示sql*plus命令的帮助HELP如何安装帮助文件：sql ?sqlplusadminhelphlpbld.sql ?sqlplusadminhelphelpus.sqlsqlhelp index34显示sql*plus系统变量的值或sql*plus环境变量的值syntaxSHOW optionwhere option represents