Oracle数据库基础及应用第章SQLlus命令

第5章SQLPlus命令

5.1使用SQL*P1us在数据库系统中，可以使用两种方式执行命令：图形化工具使用各种命令在Oracle11g系统中，可以使用SQL语句进行访问，实现查询、插入、修改和删除等操作。SQL*Plus是一个通用的、在各种平台上几乎都完全一致的工具。它既能在Windows机器上使用它，也能在UNIX、Linux机器上使用它。直观、简单、容易记忆；灵活性较差非常灵活、加深对复杂命令的理解、完成图形化工具无法完成的工作；需要记忆具体的命令和语法格式5.1.1启动SQL*P1us在Windows下启动SQL*Plus的步骤是：(1)在开始菜单中，选择“开始”→“所有程序”→“Oracle-OraDblog-homel”→“ApplicationDevelopment”→“SQLPlus”命令,出现SQL*Plus的登录窗口(2)在该窗口中输入用户名称system、口令“admin”，这里用户口令采用的回显的方式，所以是看不见的。然后单击“Enter”按钮，则打开OracleSQL*Plus窗口。在该窗口中显示SQL*Plus的命令提示符“SQL＞”，表明SQL*Plus已经连接到Oracle11g，准备接受命令了。5.1.2登录数据库命令CONNECT命令的作用是连接数据库，若是当前已经有用户连接了数据库，那么将会中断当前的连接，而使用该命令指定的用户进行建立新的连接。CONNECT命令的语法格式为：CONN[ECT][{<username>/<password>[@<connect_identifier>]/}[as{sysdba/sysoper}]其中：username指的是连接数据库的用户名，password指的是密码，如果不省略它们，则会直接登录到SQL*Plus中；如果省略，可以在启动SQL*Plus之后再输入连接数据库的用户名和密码。5.1.3退出SQL*P1us用户如果不需要再使用SQL*Plus命令时，想返回到操作系统，只需在SQL*Plus命令提示符下，输入exit或quit命令，按Enter键即可。在SQL命令后输入exit。如：SQL＞exit。如图5-5所示。或是使用菜单命令，选择“文件”→“退出”命令，也可以退出SQL*Plus。5.2SQL*Plus的运行环境设置SQL*Plus运行环境是SQL*Plus的运行方式和查询语句执行结果显示方式的总称。设置合适的SQL*Plus运行环境，可以使SQL*Plus能够按照用户的要求运行和执行各种操作。5.2.1SET命令选项在Oracle11g系统中，用户可以使用SET命令来设置SQL*Plus的运行环境。SET命令是SQL*Plus内部命令中最重要、使用频率最高的命令。使用SET命令的语法格式为：setsysytem_optionvalue选项描述setautocommit{on|off|immediate}设置事务处理方式，手动/自动提交setautoprint{on|off}设置是否自动打印变量值setautorecovery{on|off}设置为on时，以默认的文件名重做记录，需要恢复时，使用recoverautomaticdatabase语句恢复，否则只能用recoverdatabase语句恢复setautotrace{on|off|trace[only]}[ex[lain][statistics]对正常执行完毕的SQLDML语句自动生成报表信息setblockterminator{O|on|off}定义表示PL/SQL块结束的字符setcolset{_|text}设置列和列之间的分隔字符，默认为空格setcmdset{;|c|on|off}定义命令行区分字符，默认为off。setlinesize{80|n}设置一行中能够显示的总字符数，默认80setlong{80|n}为long型数值设置最大显示宽度，默认80setnewpage{1|n|none}设置每页打印标题前的空行数，默认为1Set选项及说明选项描述setnumformatformat设置数字的默认显示格式setnulltext设置select语句返回null值时显示的字符串setpause{off|on|text}设置输出结果时是否滚动显示setpagesize{14|n}设置每页打印的行数setrecset{wrapped|each|off}显示或打印记录分隔符setspace{1|n}设置输出结果中列和列之间的空格数，默认值为10setsqlcase{mixed|lower|upper}设置在执行sql命令之前是否转换大小写setsqlcontinue{>|text}设置命令提示符settime{off|on}设置当前时间的显示settiming{off|on}用于启动和关闭显示sql语句执行时间setunderline{-|c|on|off}设置是否在列标题下面添加分隔线setwrap{on|off}设置当一个数据项比当前行宽时，是否截断数据项的显示Set选项及说明(续)5.2.2设置运行环环境在Oracle中怎么设置置运行环境境以及设置置后的效果果如何，在在这里可以以通过具体体的示例来来进行演示示。设置运运行环境中中使用频率率较高的操操作如下所所示。1.SETPAGESIZEn选项当执行有返返回结果的的查询语句句时，SQL*Plus首先会显示示用户所选选择数据的的列名，然然后在相应应的列名下下显示数据据，列名之之间的空间间就是SQL*Plus的一页。SQL*Plus的一页多大大，可以使使用命令SHOWPAGESIZE显示SQL*Plus默认的一页页的大小。。可以通过过使用PAGESIZE命令来改变变这个默认认值。例如，设置置PAGESIZE为20后查询HR.EMPLOYEES表，命令如下：：SQL>showpagesizepagesize14SQL>setpagesize20SQL>selectemployee_id,first_name,last_name2fromhr.employees;当PAGESIZE被设设置置为为20后，，SQL*Plus在一一页页内内显显示示了了17行数数据据。。一一页页的的内内容容不不仅仅包包含含查查询询的的数数据据结结果果，，还还包包括括表表的的表表头头、、虚虚线线和和空空白白行行等等。。2.SETPAUSE选项项如果果查查询询语语句句返返回回的的结结果果很很多多时时，，以以至至于于无无法法在在SQL*Plus窗口口中中一一次次显显示示完完，，这这时时SQL*Plus输出出窗窗口口会会快快速速滚滚动动显显示示。。这这样样就就需需要要在在窗窗体体上上对对数数据据进进行行缓缓冲冲，，以以存存储储滚滚动动到到屏屏幕幕以以外外的的数数据据，，以以便便一一页页一一页页地地查查看看查查询询结结果果。。通过过设设置置环环境境变变量量PAUSE为ON来控控制制SQL*Plus在显显示示完完一一页页后后暂暂停停显显示示，，直直到到按按““Enter”键键后后才才继继续续显显示示下下一一页页数数据据。。当设设置置PAUSE命令令为为ON时，，需需注注意意：：当当提提交交查查询询的的时时候候，，SQL*Plus会在在显显示示第第一一页页之之前前就就暂暂停停显显示示。。只只有有按按回回车车键键后后第第一一页页的的内内容容才才会会显显示示。。PAUSE选项项还还可可以以设设置置暂暂停停后后显显示示的的字字符符串串，，以以便便提提示示用用户户。。默默认认情情况况下下，，是是不不启启用用此此功功能能的的。。如如果果想想启启用用它它，，也也可可以以使使用用Text参数数在在该该命命令令后后面面给给出出相相应应的的提提示示信信息息。。关闭闭PAUSE命令令的的形形式式如如下下：：SQL>setpauseoff3.SETLINESIZEn选项项使用LINESIZE选项，可可以设置置SQL*Plus中一行数数据可以以容纳的的字符数数量。n表示每行行能够显显示的字字符数，，取值范范围为1—32767，默认值值为80。若是修改改系统默默认的每每行打印印80个字符。。当SQL*Plus输出LINESIZE指定数量量的字符符后，随随后的数数据就会会折叠到到下一行行显示，，如果用用户窗口口特别宽宽，那么么用户就就可以设设置更宽宽的LINSIZE，以避免免折叠显显示SQL>showlinesizeLinesize80SQL>setlinesize110Linesize1104.SETTIMING[ON/OFF]选项该选项用用于启动动和关闭闭显示SQL语句执行行时间的的功能。。在SQL*Plus中运行SQL命令时，，不同的的SQL命令消耗耗的系统统时间是是不同的的。为了了查看命命令所消消耗的系系统时间间，可以以设置TIMING选项为ON，这时每每当执行行完SQL命令，SQL*Plus就会显示示该命令令所消耗耗的系统统时间。。该命令输输出的时时间单位位是小时时：分：：秒：毫毫秒5.SETTIME[ON/OFF]选项该选项用用于在提提示符前前显示或或不显示示系统时时间。在在SQL*Plus中运行SQL命令时，，为了查查看系统统时间，，可以设设置TIME选项为ON，这时每每当执行行SQL命令，命命令符前前就会显显示系统统时间。。例如：SQL>settimeon该命令执执行后，，就会在在命令提提示符前前显示当当前系统统的时间间。再次次输入命命令SETTIMEOFF就会退出出该命令令，提示示符前就就不会显显示系统统时间了了。6.SETHEADING[ON/OFF]选项该选项用用于设置置是否显显示表头头信息，，ON表示为显显示，OFF表示为不不显示。。默认值值一般为为ON。执行不显显示表头头信息命命令，例例如：SQL>setheadingoffSQL>selectjob_id,job_3whererownum<=8;该命令执执行后，，数据前前的表头头信息将将不再显显示出来来。再次次输入命命令SETHEADINGON就会重新新显示出出表头等等信息。。5.3SQL*Plus定制行为为命令在Oracle11g数据库系系统当中中，SQL*Plus还提供了了很多可可以定制制该工具具行为的的命令。。这些命命令包括括：DESCRIBE、PROMRT、SPOOL等。5.3.1DESCRIBE命令在SQL*Plus的许多命命令中，，DESCRIBE命令可能能是用户户使用最最为频繁繁的命令令。作用：返回对数数据库中中所存储储的对象象的描述述；列出其各各个列的的名称以以及属性性；输出PL/SQL块中的过过程、函函数和程程序包的的规范。。DESCRIBE命令的语语法形式式如下：：DESC[RIBE]object_name;其中，DESCRIBE可以缩写写为DESC，object_name表示将要要描述的的对象名名称。DESCRIBE命令不仅仅可以描描述表、、视图的的结构，，而且还还可以描描述PL/SQL对象，如如下面通通过DESCRIBE命令查看看hr.employees表的结构构，如图图所示。。在SQL*Plus中，如果果输入了了很长一一段SQL语句后，，发现忘忘记了该该表中的的列名，，重新再再输入会会浪费时时间。我我们可以以利用DESCRIBE命令来解解决这个个问题。。只需要要在另一一行以““#”开头，，就可在在输入SQL语句过程程中临时时运行一一个SQL*Plus命令。例如，在在查询HR.EMPLOYEES表中的数数据时，，WHERE子句中忘忘记了某某个列名名，可利利用上述述办法解解决，继继续加载载WHERE语句中剩剩余的内内容。5.3.2PROMPT命令使用PROMPT命令可以以在显示示屏幕上上输出指指定的数数据和空空行，这这种输出出方式非非常有助助于在脚脚本文件件中向用用户传递递相应的的信息。。PROMPT命令的语语法形式式如下：：PRO[MPT][text];其中，TEXT表示用于于指定要要在屏幕幕上显示示的提示示信息，，省略TEST则会输出出一行空空行5.3.3SPOOL命令SPOOL是SQL*Plus中主要完完成以标标准输出出方式输输出SQL*Plus的命令及及执行结结果，一一般可把把查询结结果保存存到文件件中或者者发送到到打印机机中。当当查询语语句的结结果很多多或是要要生成一一个报表表时，通通常会使使用此命命令以生生成一些些查询的的脚本或或者数据据。该命令的的语法格格式如下下：SPO[OL]FILE_NAME[CREATE]|[REPLACE]|[APPEND]|OFF;其中，FILE_NAME参数用于于指定脱脱机文件件的名称称，默认认的文件件扩展名名为.LST。使用CREATE关键字，，表示创创建一个个新的脱脱机文件件；使用用REPLACR关键字，，表示替替代已经经存在的的脱机文文件；使使用APPEND关键字，，表示把把脱机内内容附加加到一个个已经存存在的脱脱机文件件中。例如，，使用SPOOL命令生成成employees.txt文件，并并将查询询hr.employees表的内容容保存到到文件夹夹中，命命令如图图所示。。spool命令执行行的结果果为：从从spool命令开始始，一直直到spooloff或者spoolout命令之间间的查询询结果都都将保存存到D:\employees.txt文件中。。注意，只只有输入入执行完完spooloff命令后，，才能在在文件中中查看到到保存的的内容，，如图所所示。5.3.4HELP命令SQL*Plus的命令很多，，如果不知道道某个具体命命令的用法，，就可以使用用HELPINDEX命令来获取SQL*Plus内建帮助系统统中的相关支支持命令信息息。HELP命令的语法形形式如下：HELP[topic];其中，topic参数表示将要要查询的命令令名称。使用helpindex命令，就会显显示SQL*Plus中所有的命令令列表，如图图所示。5.4缓存区命令在Oracle中，通常所提提到的SQL语句、PL/SQL语句块都是SQL*Plus语句的命令。。这一类命令令是发送到服服务器端执行行的命令，它它们要求以分分号或反斜线线结尾以表示示语句执行完完毕。当它们们执行完成后后，在SQL*Plus的缓存区中用用户就可以重重新调用、编编辑或运行那那些最近输入入的SQL语句。但需需要注意的是是，缓存区中中只存储用户户最近执行的的命令语句。。5.4.1EDIT命令通过在缓冲区区存储这些命命令语句，用用户可以使用用EDIT命令，将缓存存区中的内容容传递到Windows记事本中进行行编辑。编辑辑器只有在SQL缓冲区中有内内容时才能启启动。EDIT命令的语法形形式如下：ED[IT][file_name];EDIT命令自动打开开记事本，用用来编辑缓存存区中的内容容，修改后直直接关闭记事事本，记事本本中的内容就就会存到缓存存区。也可以以在edit后面指定文件件名，编辑完完内容将文件件保存，系统统会自动将文文件读入缓存存区。在SQL*Plus命令行中，输输入内容如如下：selectemployee_idfromhr.employeeswhererownum<=5按Enter键，此时在SQL缓存区中就有有内容存在，，如图所示。。在上图中，输输入edit命令，按enter键。SQL*Plus将保存于SQL缓存区中的内内容保存到Windows记事本，如图图所示。在记事本编辑辑器中直接编编辑(修改)SQL语句，结果会会直接放到缓缓存中，如图图所示。选择“文件””→“保保存”命令，，再选择“文文件”→““退出”命令令，退出编辑辑，退回到当当前的SQL*Plus中，如图所示示。5.4.2SAVE命令使用SAVE命令可以把当当前SQL缓存区中的内内容保存到指指定的文件中中。SAVE命令的语法形形式如下：SAV[E]FILE_NAME[CREATE|REPLACE|APPEND]其中，FILE_NAME为文件名，如如果用户没提提供文件的扩扩展名，则默默认扩展名为为SQL。CREATE选项用于指定定如果文件不不存在，就自自动创建它，，否则用SQL*Plus缓存区中的内内容覆盖文件件中的内容。。APPEND选项则把缓存存区中的内容容追加到文件件的末尾。例如：保存查查询雇佣表信信息的SQL语句语句到D:\employees.sql文件中，使用用的SAVE命令如下图所所示。该语句被保存存的文件在相相应的目录下下存放，如图图所示。说明：SAVE命令默认的保保存路径是Oracle系统安装的主主目录。最好好将SQL文件与Oracle系统文件分开开保存，所以以应在文件名名前加绝对路路径。由于SQL*Plus缓冲区中只能能存放SQL语句，所以可可以使用这种种方法把SQL语句或PL/SQL块保存到指定定的文件中去去，而要保存存SQL*Plus命令及其运行行结果到文件件中，就需要要配合使用INPUT命令，如图所所示。在前面，使用用INPUT命令将SQL语句和其运行行结果一同保保存到文件D:\EMPLOYEES.SQL中，由于在SAVE命令中使用了了REPLACE选项，所以新新添加的内容容将替换原文文件的内容。。替换源文件件之前，通过过CLEARBUFFER命令清除了SQL*Plus缓存区中的内内容。5.4.3RUN命令在SQL*Plus缓冲区中，获获取指定文件件的内容后，，就可对其中中的命令作进进一步的编辑辑。如果该命命令只包含SQL语句或PL/SQL语句块，可以以使用RUN命令或运行反反斜线（“/”）命令或使使用菜单命令令，来执行缓缓存区中的语语句。RUN命令执行缓冲冲区的SQL语句，以及前前斜线命令执执行语句的结结果。R[UN]命令执行缓存存区的SQL语句，以及反反斜线执行语语句的结果如如图所示。RUN命令显示SQL缓存区中的内内容和运行结结果，并使SQL缓存区中最后后一行成为当当前行；反斜线(“/”)命令只显示运运行结果，不不显示缓存区区中的内容，，也不使SQL缓存区中的最最后一行成为为当前行。5.5格式化查询命命令SQL*Plus提供一些命令令用于格式化化查询结果，，这些命令执执行完后，不不保存到SQL缓存中。它们们一般对输出出的结果进行行格式化显示示，也便于制制作用户需要要的报表。使用这些常用用的格式化查查询结果命令令，如：COLUMN命令等，可以以实现重新设设置列的标题题，重新定义义值的显示格格式和显示宽宽度等。5.5格式化查询命命令使用这些格式式化的命令时时，应该遵循循如下规则：：每一次报表结结束时，应该该重新设置SQL*Plus为默认值；格式化命令设设置后，将一一直起作用，，直到该会话话结束或下一一个格式化命命令的设置；；要是为某个个列指定了了别名，就就必须引用用该列的别别名，不能能再使用该该列名。5.5格式化查询询命令通过使用COLUMN命令，可以以控制查询询结果集中中列的显示示格式。COLUMN命令的语法法格式如下下：COL[UMN][[column_name]expr|option]其中：COLUMN_NAME参数用于指指定要控制制的列的名名称。EXPR表达式也可可用于指定定列的别名名。OPTION参数用于指指定某个列列的显示格格式。在关关键字COLUMN后面没有指指定任何参参数的话，，COLUMN命令就会显显示SQL*Plus环境中所有有列的当前前显示属性性；在COLUMN后面面只只有有列列名名，，则则显显示示该该列列的的当当前前属属性性。。OPTION选项及其说说明：选项说明CLEAR清除为该列设置的显示属于，使其使用默认的显示属性COLOR定义列的显示颜色HEADING定义列的标题FORMAT为列指定显示格式JUSTIFY调整列标题的对齐方式PRIN/NOPRINT显示/隐藏列标题NULL指定一个字符串，如果列的值为NULL，则由该字符串代替ON/OFF控制显示属性的状态，OFF表示定义的显示属性不起作用TRUNCATED表示截断字符串尾部WORD_WRAPPED表示从一个完整的字符处折叠WRAPPED当字符串的长度超过显示宽度时，将字符串的超出部分折叠到下一行显示1.FORMAT选项在SQL*Plus中运行SELECT查询命令时时，如果有有返回结果果，则结果果会以行和和列的形式式显示。SQL*Plu