第3章Oracle资料学习

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.sqlLIST1SELECTempno,job,mgr,sal,deptno2FROMscott.emp3*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.sqlEMPNOJOBMGRSALDEPTNO-------------------------------------------7839PRESIDENT5000107844SALESMAN76981500307876CLERK77881100207900CLERK7698950307902ANALYST75663000207934CLERK7782130010已选择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|