搜索
2第3章使用SQL*Plus工具本章要点：了解SQL*Plus工具的功能。掌握SQL*Plus连接与断开数据库的多种方式。熟练掌握DESCRIBE命令的使用。熟练掌握各种编辑命令。掌握临时变量和已定义变量的使用。掌握格式化查询结果的设置。掌握简单报表的创建。33.1SQL*Plus概述本节将对SQL*Plus工具的主要功能进行简单的介绍，另外介绍如何使用SQL*Plus连接和断开数据库。43.1.1SQL*Plus的主要功能SQL*Plus工具主要用于数据查询和数据处理。利用SQL*Plus可以将SQL与Oracle专有的PL/SQL结合起来进行数据查询和处理。SQL*Plus工具具备以下功能：对数据表可以执行插入、修改、删除、查询操作，以及执行SQL、PL/SQL块。查询结果的格式化、运算处理、保存、打印以及输出Web格式。显示任何一个表的字段定义，并与终端用户交互。连接数据库，定义变量。完成数据库管理。运行存储在数据库中的子程序或包。启动/停止数据库实例。53.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所示。63.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所示。73.1.2SQL*Plus连接与断开数据库3．使用SQL*Plus命令连接与断开数据库在SQL*Plus中连接数据库时，可以使用CONNECT命令指定不同的登录用户，连接数据库后，SQL*Plus维持数据库会话。CONNECT命令的一般语法形式如下：CONN[ECT][{user_name[/password][@connect_identifier]}[AS{SYSOPER|SYSDBA|SYSASM}]]83.2使用SQL*Plus命令Oracle的SQL*Plus是与Oracle进行交互的客户端工具。在SQL*Plus中，可以运行SQL*Plus命令和SQL*Plus语句。执行这些语句后，都可以保存在一个被称为SQLBUFFER的内存区域中，但是只能保存一条最近执行的SQL语句。除了SQL*Plus语句，在SQL*Plus中执行的其他语句称之为SQL*Plus命令。SQL*Plus命令执行后，不保存在SQLBUFFER内存区域中，一般用来对输出的结果进行格式化显示，以便于制作报表。93.2.1使用DESCRIBE命令查看表结构DESCRIBE命令可以返回数据库中所存储的对象的描述。对于表和视图等对象来说，DESCRIBE命令可以列出各个列以及各个列的属性，除此之外，该命令还可以输出过程、函数和程序包的规范。DESCRIBE命令的语法如下：DESC[RIBE]{[schema.]object[@connect_identifier]}使用DESCRIBE命令查看表的结构时，如果存在指定的表，则显示该表的结构。在显示表结构时，将按照“名称”、“是否为空？”和“类型”这3列进行显示。其中——名称：表示列的名称。是否为空？：表示对应列的值是否可以为空。如果不可以为空，则显示NOTNULL；否则不显示任何内容。类型：表示列的数据类型，并且显示其精度。103.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文本113.2.3使用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文件的内容；如果该文件不存在，则创建该文件。123.2.4使用GET命令读取文件内容到缓冲区使用GET命令的语法如下：GET[FILE]file_name[LIST|NOLIST]语法说明如下。file_name：表示一个指定文件，将该文件的内容读入SQL*Plus缓冲区中。LIST：列出缓冲区中的语句。NOLIST：不列出缓冲区中的语句。【例3.5】将scott_emp_query.sql文件的内容读入到缓冲区中，并且输出和显示这些内容，如下：SQLGETscott_emp_query.sqlLIST1SELECTempno,job,mgr,sal,deptno2FROMscott.emp3*whereempno7800将文件的内容读入到缓冲区后，就可以使用编辑命令对这些内容进行操作了。133.2.5使用START命令读取并运行文件内容START命令可以读取文件中的内容到缓冲区中，然后在SQL*Plus中运行这些内容。START命令的语法如下：STA[RT]{url|file_name}语法说明如下。url：用来指定一个URL地址，例如。file_name：指定一个文件。该命令将file_name文件的内容读入SQL*Plus缓冲区中，然后运行缓冲区中的内容。143.2.5使用START命令读取并运行文件内容【例3.6】使用START命令读取并运行scott_emp_query.sql文件，如下：SQLSTARTscott_emp_query.sqlEMPNOJOBMGRSALDEPTNO-------------------------------------------7839PRESIDENT5000107844SALESMAN76981500307876CLERK77881100207900CLERK7698950307902ANALYST75663000207934CLERK7782130010已选择6行。上述输出结果表示执行START命令后，运行了保存在scott_emp_query.sql文件之中的内容。153.2.6使用EDIT命令编辑缓冲区内容或文件内容使用EDIT命令，可以将SQL*Plus缓冲区的内容复制到一个名为afiedt.buf的文件中，然后启动操作系统中默认的编辑器打开这个文件，并且对于文件内容能够进行编辑。在Windows操作系统中，默认的编辑器是Notepad(记事本)。语法形式如下：ED[IT][file_name]其中，file_name默认为afiedt.buf，也可以指定一个其他的文件。163.2.6使用EDIT命令编辑缓冲区内容或文件内容【例3.7】在SQL*Plus中执行EDIT命令，如下：SQLEDIT已写入fileafiedt.buf这时，将打开一个记事本文件afiedt.buf，在该文件中显示缓冲区中的内容，文件的内容以斜杠(/)结束，如图3-4所示。173.2.7使用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指定的文件中。183.2.8使用临时变量在Oracle数据库中，可以使用变量来编写通用的SQL语句，在运行SQL语句时，为变量输入值，就会在SQL语句中将变量替换成这些值。1．使用&符号表示临时变量在SQL语句中，如果在某个变量前面使用了&符号，那么就表示该变量是一个临时变量。执行SQL语句时，系统会提示用户为该变量提供一个具体的数据。2．使用&&符号表示临时变量在SQL语句中，如果希望重新使用某个变量并且不希望重新提示输入值，那么可以使用&&符号来定义临时变量。193.2.8使用临时变量3．使用SETVERIFY和SETDEFINE命令在使用临时变量时，还可以使用SETVERIFY命令和SETDEFINE命令，其中——SETVERIFY[ON|OFF]：用来指定是否输出原值和新值信息。SETDEF[INE]：用于指定一个除字符&之外的字符，作为定义变量的字符。4．在脚本文件中使用临时变量在创建的脚本文件中也可以使用临时变量。在运行该脚本文件时，需要为临时变量传递相应的变量值。203.2.9使用已定义变量1．使用DEFINE命令DEFINE命令用来创建一个数据类型为CHAR的变量，该命令的语法形式如表3-4所示。命令说明DEF[INE]显示所有的已定义变量DEF[INE]variable显示指定变量的名称、值和其数据类型DEF[INE]variable=value创建一个CHAR类型的用户变量，并且为该变量赋初始值213.2.9使用已定义变量2．使用ACCEPT命令使用ACCEPT命令也可以定义变量，并且定制一个用户提示，用于提示用户输入指定变量的数据。ACCEPT命令的语法如下：ACC[EPT]variable[data_type][FOR[MAT]format][DEF[AULT]default][PROMPTtext|