Oracle数据库应用培训使用SQLzilus工具

第3章使用SQL*Plus工具本章要点：

了解SQL*Plus工具的功能。掌握SQL*Plus连接与断开数据库的多种方式。熟练掌握DESCRIBE命令的使用。熟练掌握各种编辑命令。掌握临时变量和已定义变量的使用。掌握格式化查询结果的设置。掌握简单报表的创建。23.1SQL*Plus概述本节将对SQL*Plus工具的主要功能进行简单的介绍，另外介绍如何使用SQL*Plus连接和断开数据库。33.1.1SQL*Plus的主要功能SQL*Plus工具主要用于数据查询和数据处理。利用SQL*Plus可以将SQL与Oracle专有的PL/SQL结合起来进行数据查询和处理。SQL*Plus工具具备以下功能：对数据表可以执行插入、修改、删除、查询操作，以及执行SQL、PL/SQL块。查询结果的格式化、运算处理、保存、打印以及输出Web格式。显示任何一个表的字段定义，并与终端用户交互。连接数据库，定义变量。完成数据库管理。运行存储在数据库中的子程序或包。启动/停止数据库实例。43.1.2SQL*Plus连接与断开数据库1．启动SQL*Plus，连接到默认数据库(1) 执行“开始”→“程序”→“Oracle–OraDb11g_home1”→“应用程序开发”→“SQLPlus”命令，打开SQLPlus窗口，显示登录界面。(2) 在登录界面中将提示输入用户名，根据提示输入相应的用户名和口令(例如system和admin)后按Enter键，SQL*Plus将连接到默认数据库。(3) 连接到数据库之后，显示SQL>提示符，可以输入相应的SQL命令。例如执行SELECTnameFROMV$DATABASE语句，查看当前数据库名称。如图3-1所示。53.1.2SQL*Plus连接与断开数据库2．从命令行连接数据库要从命令行启动SQL*Plus，可以使用sqlplus命令。sqlplus命令的一般使用形式如下：sqlplus[user_name[/password][@connect_identifier]] [AS{SYSOPER|SYSDBA|SYSASM}]|/NOLOG]下面以system用户连接数据库，在DOS窗口中输入sqlplussystem/admin@orcl命令，按Enter键后提示连接到orcl数据库，如图3-2所示。63.1.2SQL*Plus连接与断开数据库3．使用SQL*Plus命令连接与断开数据库在SQL*Plus中连接数据库时，可以使用CONNECT命令指定不同的登录用户，连接数据库后，SQL*Plus维持数据库会话。CONNECT命令的一般语法形式如下：CONN[ECT][{user_name[/password][@connect_identifier]} [AS{SYSOPER|SYSDBA|SYSASM}]]73.2使用SQL*Plus命令Oracle的SQL*Plus是与Oracle进行交互的客户端工具。在SQL*Plus中，可以运行SQL*Plus命令和SQL*Plus语句。执行这些语句后，都可以保存在一个被称为SQLBUFFER的内存区域中，但是只能保存一条最近执行的SQL语句。除了SQL*Plus语句，在SQL*Plus中执行的其他语句称之为SQL*Plus命令。SQL*Plus命令执行后，不保存在SQLBUFFER内存区域中，一般用来对输出的结果进行格式化显示，以便于制作报表。83.2.1使用DESCRIBE命令查看表结构DESCRIBE命令可以返回数据库中所存储的对象的描述。对于表和视图等对象来说，DESCRIBE命令可以列出各个列以及各个列的属性，除此之外，该命令还可以输出过程、函数和程序包的规范。DESCRIBE命令的语法如下：DESC[RIBE]{[schema.]object[@connect_identifier]}使用DESCRIBE命令查看表的结构时，如果存在指定的表，则显示该表的结构。在显示表结构时，将按照“名称”、“是否为空？”和“类型”这3列进行显示。其中——名称：表示列的名称。是否为空？：表示对应列的值是否可以为空。如果不可以为空，则显示NOTNULL；否则不显示任何内容。类型：表示列的数据类型，并且显示其精度。93.2.2使用SQL*Plus语句快速编辑SQL语句Oracle数据库提供了一些SQL*Plus语句，这些语句由相应的命令来实现，例如APPEND(可以简写为A)命令，如表3-3所示。命

令说

明A[PPEND]text将text附加到当前行之后C[HANGE]/old/new将当前行中的old替换为newC[HANGE]/text/删除当前行中指定的text文本CL[EAR]BUFF[ER]清除缓存区中的所有行I[NPUT]插入不定数量的命令行I[NPUT]text插入指定的文本textDEL删除当前行DELn删除第n行(行号从1开始)DELmn删除从第m行到第n行之间的命令行L[IST]列出缓冲区中所有的行L[IST]n列出第n行R[UN]或/显示缓冲区中保存的语句，并运行这些语句n将第n行作为当前行ntext使用text文本替代第n行信息0text在第一行之前插入text文本10使用SAVE命令将将缓冲区内内容保存到到文件使用SAVE命令可可以将当前前缓冲区的的内容保存存到文件中中，这样，，即使缓冲冲区中的内内容被覆盖盖，也保留留有前面的的执行语句句。SAVE命令的的语法如下下所示：SAV[E][FILE]file_name[CRE[ATE]|REP[LACE]|APP[END]]语法法说说明明如如下下。。file_name：：表表示示将将SQL*Plus缓缓冲冲区区的的内内容容保保存存到到由由file_name指指定定的的文文件件中中。。CREATE：：表表示示创创建建一一个个file_name文文件件，，并并将将缓缓冲冲区区中中的的内内容容保保存存到到该该文文件件。。该该选选项项为为默默认认值值。。APPEND：：如如果果file_name文文件件已已经经存存在在，，则则将将缓缓冲冲区区中中的的内内容容追追加加到到file_name文文件件的的内内容容之之后后；；如如果果该该文文件件不不存存在在，，则则创创建建该该文文件件。。REPLACE：：如如果果file_name文文件件已已经经存存在在，，则则覆覆盖盖file_name文文件件的的内内容容；；如如果果该该文文件件不不存存在在，，则则创创建建该该文文件件。。11使使用用GET命命令令读读取取文文件件内内容容到到缓缓冲冲区区使用用GET命命令令的的语语法法如如下下：：GET[FILE]file_name[LIST|NOLIST]语法法说说明明如如下下。。file_name：：表表示示一一个个指指定定文文件件，，将将该该文文件件的的内内容容读读入入SQL*Plus缓缓冲冲区区中中。。LIST：：列列出出缓缓冲冲区区中中的的语语句句。。NOLIST：：不不列列出出缓缓冲冲区区中中的的语语句句。。【例例3.5】】将scott_emp_query.sql文文件件的的内内容容读读入入到到缓缓冲冲区区中中，，并并且且输输出出和和显显示示这这些些内内容容，，如如下下：：SQL>GETscott_emp_query.sqlLIST1SELECTempno,job,mgr,sal,deptno2FROMscott.emp3*whereempno>7800将文文件件的的内内容容读读入入到到缓缓冲冲区区后后，，就就可可以以使使用用编编辑辑命命令令对对这这些些内内容容进进行行操操作作了了。。12使用用START命命令令读读取取并并运运行行文文件件内内容容START命命令令可可以以读读取取文文件件中中的的内内容容到到缓缓冲冲区区中中，，然然后后在在SQL*Plus中中运运行行这这些些内内容容。。START命命令令的的语语法法如如下下：：STA[RT]{url|file_name}语法法说说明明如如下下。。url：用用来来指指定定一一个个URL地址址，，例例如如http://host.domain/script.sql。file_name：指指定定一一个个文文件件。。该该命命令令将将file_name文件件的的内内容容读读入入SQL*Plus缓冲冲区区中中，，然然后后运运行行缓缓冲冲区区中中的的内内容容。。13使用用START命命令令读读取取并并运运行行文文件件内内容容【例例3.6】】使用用START命令令读读取取并并运运行行scott_emp_query.sql文件件，，如如下下：：SQL>STARTscott_emp_query.sqlEMPNOJOBMGRSALDEPTNO7839PRESIDENT5000107844SALESMAN76981500307876CLERK77881100207900CLERK7698950307902ANALYST75663000207934CLERK7782130010已选择6行。。上述输出结果果表示执行START命命令后，运行行了保存在scott_emp_query.sql文件件之中的内容容。14使使用EDIT命令编编辑缓冲区内内容或文件内内容使用EDIT命令，可以以将SQL*Plus缓缓冲区的内容容复制到一个个名为afiedt.buf的文件件中，然后启启动操作系统统中默认的编编辑器打开这这个文件，并并且对于文件件内容能够进进行编辑。在在Windows操作系系统中，默认认的编辑器是是Notepad(记事事本)。语法法形式如下：：ED[IT][file_name]其中，file_name默认为afiedt.buf，，也可以指定定一个其他的的文件。15使使用EDIT命令编编辑缓冲区内内容或文件内内容【例3.7】】在SQL*Plus中执执行EDIT命令，如下下：SQL>EDIT已写入fileafiedt.buf这时，将打开开一个记事本本文件afiedt.buf，在该该文件中显示示缓冲区中的的内容，文件件的内容以斜斜杠(/)结结束，如图3-4所示。。16使使用SPOOL命令令复制输出结结果到文件使用SPOOL命令实现现将SQL*Plus中中的输出结果果复制到一个个指定的文件件中，或者把把查询结果发发送到打印机机中，直到使使用SPOOLOFF命令为止。。SPOOL命令的语法法如下：SPO[OL][file_name[CRE[ATE]|REP[LACE]|APP[END]]|OFF|OUT]语法说明如下下。file_name：指指定一个操作作系统文件。。CREATE：创建一个个指定的file_name文件。。REPLACE：如果指指定的文件已已经存在，则则替换该文件件。APPEND：将内容附附加到一个已已经存在的文文件中。OFF：停止止将SQL*Plus中中的输出结果果复制到file_name文件中中，并关闭该该文件。OUT：启动动该功能，将将SQL*Plus中的的输出结果复复制到file_name指定的文文件中。17使使用临时时变量在Oracle数据库中，可可以使用变量量来编写通用用的SQL语句，在运行行SQL语句句时，为变量量输入值，就就会在SQL语句中将变变量替换成这这些值。1．使用&符符号表示临时时变量在SQL语句句中，如果在在某个变量前前面使用了&符号，那么么就表示该变变量是一个临临时变量。执执行SQL语语句时，系统统会提示用户户为该变量提提供一个具体体的数据。2．使用&&符号表示临临时变量在SQL语句句中，如果希希望重新使用用某个变量并并且不希望重重新提示输入入值，那么可可以使用&&符号来定义义临时变量。。18使使用临时时变量3．使用SETVERIFY和SETDEFINE命令在使用临时变变量时，还可可以使用SETVERIFY命令和SETDEFINE命令，其中——SETVERIFY[ON|OFF]：用来指定是是否输出原值值和新值信息息。SETDEF[INE]：用于指定一一个除字符&之外的字符，，作为定义变变量的字符。。4．在脚本文文件中使用临临时变量在创建的脚本本文件中也可可以使用临时时变量。在运运行该脚本文文件时，需要要为临时变量量传递相应的的变量值。19使使用已定定义变量1．使用DEFINE命令令DEFINE命令用来创创建一个数据据类型为CHAR的变量量，该命令的的语法形式如如表3-4所所示。命

令说

明DEF[INE]显示所有的已定义变量DEF[INE]variable显示指定变量的名称、值和其数据类型DEF[INE]variable=value创建一个CHAR类型的用户变量，并且为该变量赋初始值20使使用已定定义变量2．使用ACCEPT命令使用ACCEPT命令也可以定定义变量，并并且定制一个个用户提示，，用于提示用用户输入指定定变量的数据据。ACCEPT命令的语法如如下：ACC[EPT]variable[data_type][FOR[MAT]format][DEF[AULT]default][PROMPTtext|NOPR[OMPT]][HIDE]213.3格格式化查询结结果SQL*Plus提供了大量用用来格式化结结构集的命令令。这些命令令包括COLUMN、PAGESIZE、LINESIZE、TTITLE和BTITLE等。在使用格式化化命令时，应应该遵循下面面一些规则：：格式化命令设设置之后，该该命令一直起起作用，直到到会话结束或或者下一个格格式化命令的的设置。每一次报表结结束后，重新新设置SQL*Plus默认值。如果为某个列列指定了别名名，那么必须须引用该别名名，而不能使使用列名。22格格式化列列的显示效果果COLUMN命令可对列列的显示效果果(包括列标标题和列数据据)进行格式式化。命令语语法如下：COL[UMN][{column_name|ALI[AS]alias}[options]]语法说明如下下。column_name：指定列名。。ALIASalias：指定要格格式化的列的的别名。Options：指定用用于格式化列列或别名的一一个或者多个个选项。23设设置一页页显示多少行行数据使用PAGESIZE命命令，可以设设置每一页的的大小，从而而控制每一页页显示的数据据量。PAGESIZE命令的语法法如下：SETPAGESIZEn其中，参数n表示每一页页大小的正整整数，最大值值可以为50000，默默认值为14。【例3.19】使用PAGESIZE命命令，设置一一页显示20行信息。然然后再执行前前面的SELECT语句句，如下：SQL>SETPAGESIZE20SQL>/员工编号员员工姓名上上级编号受受雇日期员员工工资

7369SMITH790217-12月-80$800.007499ALLEN769820-2月-81$1,600.00...7902FORD756603-12月-81$3,000.007934MILLER778223-1月-82$1,300.00已选择14行行。从输出结果可可以看出，所所有的数据在在一页内进行行显示。24设设置一行行显示多少个个字符使用LINESIZE命命令可以设置置一行数据可可以容纳的字字符数量，默默认数量为80。如果LINESIZE的值比比较小，那么么表中一行数数据，有可能能在屏幕上需需要分多行显显示；如果LINESIZE的值设设置大一些，，一行数据就就可以在屏幕幕的一行中进进行显示了。。LINESIZE命令的的语法如下：：SETLINESIZEn其中，n表示示屏幕上一行行数据可以容容纳的字符数数量，有效范范围是1~32767。。25设设置一行行显示多少个个字符【例3.20】对scott用户的表emp执行查查询操作时，，使用LINESIZE命令之前和和之后的输出出结果如图3-6所示。。从图3-6的的输出结果可可以看出，执执行SELECT语句后后的输出结果果中，应该一一行显示的数数据分成两行行进行显示。。26创创建简单单报表1．为报表设设置页眉和页页脚使用TTITLE和BTITLE命命令，分别为为报表设置页页眉和页脚信信息。这两个个命令的使用用方法如表3-7所示。。命

令说

明TTI[TLE][printspec[text|variable]...]|[OFF|ON]指定出现在报表中每一个页面顶端的页眉。其中printspec的可选值有LEFT、CENTER、RIGHT、BOLD、FORMATtext、COLn、S[KIP][n]和TABn。ON选项表示启用设置；OFF选项表示取消设置。还可以带有用户变量(内容由系统来维护)，如SQL.PNO页号、SQL.LNO行号等BTI[TLE][printspec[text|variable]...]|[OFF|ON]指定出现在报表中每一个页面底端的页脚27创创建简单单报表【例3.21】查询scott用户的emp表时，，使用TTITLE和BTITLE命令设置页页眉和页脚信信息。由于需需要使用PAGESIZE和LINESIZE等多条命令令设置输出格格式，所以，，将所有的设设置命令定义义在一个文本本文件report.sql中，如如图3-7所所示。28创创建建简单单报表表2．计计算小小计BRENK和COMPUTE的一一般用用法如如下：：BRE[AK][ONcolumn_name]SKIPnCOMP[UTE]functionLABELlabelOFcolumn_nameONbreak_column_name语法说说明如如下。。column_name：表表示对对哪一一列执执行操操作。。SKIPn：：表示示在指指定列列的值值变化化之前前插入入n个个空行行。function：：表示示执行行的操操作，，例如如SUM(求和和)、、MAXIMUM(最大大值