SQLPlus基础.

第3章SQL*Plus基础第3章SQL*Plus基础3.1SQL*Plus简介3.2设置SQL*Plus环境3.3格式化查询结果3.4SQL*Plus编辑器的编辑命令3.5假脱机输出3.6联机帮助3.7增强功能3.8小结习题三上机实验三第3章SQL*Plus基础3.1SQL*Plus简介1．SQL*Plus的概念SQL*Plus是Oracle提供的访问数据库服务器的客户端软件，是Oracle的核心产品。SQL*Plus中的SQL是指StructuredQueryLanguage，即结构化查询语言，而Plus是指Oracle将标准SQL语言进行扩展，它提供了另外一些Oracle服务器能够接受和处理的命令。开发者和DBA可以通过SQL*Plus直接存取Oracle数据库，包括数据提取、数据库结构的修改和数据库对象的管理，它所用的命令和函数都是基于SQL语言的。第3章SQL*Plus基础SQL*Plus完成的主要工作如下：(1)输入、编辑、存取和运行SQL命令。(2)测试SQL程序段的正确性。(3)调试PL/SQL程序段的正确性。(4)对查询结构进行格式化，计算、存储、打印或生成网络输出。(5)向其他客户端用户发送消息或接收反馈信息。(6)管理和维护数据库。第3章SQL*Plus基础2．启动SQL*Plus在操作系统界面选择“开始”→“程序”→“Oracle-OraHome90”→“ApplicationDevelopment”→“SQLPlus”，出现如图3-1所示的“注册”界面。在“注册”界面中，输入分配给你的用户名和口令，以及连接到服务器的主机字符串，然后单击【确定】按钮，即可进入如图3-2所示的SQL*Plus的工作环境。第3章SQL*Plus基础图3-1SQL*Plus的“注册”界面第3章SQL*Plus基础用户名和口令可以向系统管理员申请获得，也可以使用Oracle系统提供的sys和system默认的口令。sys和system默认的口令分别为change_on_install和manager。主机字符串是全局数据库名或由网络配置助手建立的网络服务名。第3章SQL*Plus基础图3-2SQL*Plus的工作环境第3章SQL*Plus基础3.2设置SQL*Plus环境1．命令方式使用SET命令可以改变SQL*Plus环境参数的值。SET命令格式：SET选项值或开关状态其中：选项：环境参数的名称；值或开关状态：参数被设置成ON或OFF，或是某个具体的值。系统提供了几十个环境参数，使用SHOW命令可以显示SQL*Plus环境参数的值。SHOW命令格式一：SQLSHOWALL功能：显示所有参数的当前设置。SHOW命令格式二：SHOW参数功能：显示指定参数的当前设置。第3章SQL*Plus基础2．对话框方式在SQL*Plus的环境下，单击菜单栏中的“选项”→“环境”，可得到如图3-5所示的“环境”对话框。图3-5“环境”对话框第3章SQL*Plus基础3．常用的主要参数1) LINESIZE和PAGESIZE命令SETLINESIZE用来指定页宽是多少，最常用的设置为80和132。【例3.1】设置行宽为60，设置页的长度为30。SQLSETLINESIZE60SQLSETPAGESIZE30命令SETPAGESIZE命令用来设置页的长度是多少，常用设置是55和60。第3章SQL*Plus基础2) ECHO命令SETECHO可以设置执行命令文件时命令是否显示在屏幕上。【例3.2】在SQL*Plus的环境中执行如下命令。SQLSETECHOON该设置表明，在SQL*Plus的环境下执行命令文件时，命令本身显示在屏幕上。SQLSETECHOOFF该设置表明，在SQL*Plus的环境下执行命令文件时，命令本身不显示在屏幕上。第3章SQL*Plus基础3) PAUSE当命令SETPAUSE设置为ON时，表示SQL*Plus在每页输出的开始处停止，按回车键后继续滚动。4) TIME当命令SETTIME设置为ON时，表示在每个命令提示前显示当前时间。5) NUMFORMAT命令SETNUMFORMAT后面接着数字，以设置查询结果中显示数字的缺省格式。第3章SQL*Plus基础3.3格式化查询结果3.3.1替换变量在SQL*Plus中可以用变量来替代列名或表达式，该变量称为替换变量。执行带替换变量的语句时，用户会得到提示，要求输入具体的值。用户输入的值存储在预定义的变量中。替换变量可以用在单条SQL语句中，也可以用在SQL*Plus脚本中。第3章SQL*Plus基础1．&替换变量替换变量是在查询中定义和使用的。在SELECT语句中，如果某个变量前面使用了&符号，则表示该变量是替换变量。在执行SELECT语句时，系统会提示用户输入变量的值。1)替换变量【例3.5】创建一个替换变量cont_id，在查询中用&符号说明。在图3-6中输入代码：SQLselectemployee_id,first_name,last_namefromhr.employeeswhereemployee_id=&cont_id;第3章SQL*Plus基础图3-6使用 & 替换变量第3章SQL*Plus基础2)有日期和字符值的替换变量当给变量输入不同类型的数据时，其格式是不一样的。如必须在单引号内说明字符或日期的值，而数值则不需要引号。因此，接受字符或日期值的替换变量要放在单引号内。第3章SQL*Plus基础【例3.6】接受用户的日期的SQL语句。在图3-7中输入代码：SQLselectemployee_id,first_name,last_name,hire_datefromhr.employeeswherehire_date=‘&hire_id’;当点击【执行】按钮时，用户将被提示输入日期。如果在定义替换变量时没有加单引号，则在输入值时必须加单引号。说明：函数可以和替换变量一起使用，如UPPER('&Name')第3章SQL*Plus基础图3-7使用带日期的替换变量第3章SQL*Plus基础3)列名、表达式和文本的替换变量列名、ORDERBY子句、表名、整个WHERE子句表达式等均可以使用替换变量。【例3.7】替换变量用于列名。SELECT&Col_NameFROMEmployees;执行此代码时，用户被要求输入列名。输入的列可以是任意多个，列与列之间用逗号分隔。第3章SQL*Plus基础【例3.8】替换变量用于表达式。SQLSELECTNameFROMEmployeesWHERE&var;执行此代码时，用户应该输入一个表达式，如name='李林'。需要注意的是，不可以在SQL语句开头使用替换变量。变量只在定义的会话中是可用的。第3章SQL*Plus基础2．DEFINE和UNDEFINE命令1) DEFINE为了预定义替换变量的值，可以使用DEFINE命令。DEFINE命令的用法和描述如表3-1所示。第3章SQL*Plus基础表3-1DEFINE命令的用法和描述命令定义DEFINEVariable=value创建定义变量并赋值DEFINEVariable显示变量、变量的值和数据类型DEFINE显示所有预定义变量第3章SQL*Plus基础【例3.9】显示“珠海”的雇员的姓名、性别、电话号码以及珠海籍员工的工资。需要以同一个城市作为条件查询两个表。定义一个能用于查询的变量，并给它赋值“珠海”。第3章SQL*Plus基础SQLDEFINECity=珠海执行DEFINE命令后，可以在下面SQL语句中使用同样的变量。SQLSELECTName,sex,phonenumberFROMEmployeesWHEREAddress='&City';SQLSELECTName,EmployeeID,IncomeFROMEmployeese,SalarysWHEREe.EmployeeID=s.EmployeeIDANDe.cCity='&City';如果想要查看此变量的值，则可以使用命令：SQLDEFINECity；如果想要查看所有预定义变量的值，则可以使用命令：DEFINE；第3章SQL*Plus基础2) UNDEFINE使用UNDEFINE命令可以删除替换变量City。例如，SQLUNDEFINECity;【例3.10】定义数值变量，在SQL语句中使用变量，使用完后将变量删除。SQLDEFINESalary=2000;SQLSELECTEmployeeID，IncomeFROMEmployeesWHEREIncome&Salary；SQLUNDEFINESalary;这段代码将显示工资高于2000的员工代码。第3章SQL*Plus基础3．双&符号替换变量在SELECT语句中，如果希望重新使用某个变量并且不希望重新提示输入该值，则可以使用双 & 符号替换变量(&&)。【例3.11】分析以下代码。SQLselect&&fieldname2fromdual;输入fieldname2的值:sysdateSQLselect&&fieldname2fromdual;SQLdefinefieldname2;第3章SQL*Plus基础4．VERIFY命令为了在执行替换变量时显示如何执行替换的值，可以使用VERIFY命令。也就是说，可以通过观察替换变量的值来说明前后的SQL语句。可用SETVERIFYON/OFF命令设置此参数。若设置值为ON，则此功能可用，可以用来验证输入的值是否正确；若设置值为OFF，则该功能禁用。默认值为ON。第3章SQL*Plus基础【例3.12】分析以下语句。SQLSETVERIFYONSQLSELECTNameFROMEmployeesWHEREEmployeeID=&EMP_ID;输入了EMP_ID的值后，系统将显示该变量的新旧值。如输入值000002，则其输出结果如下：Old1：SELECTNameFROMEmployeesWHEREEmployeeCode=&EMP_ID;New1：SELECTNameFROMEmployeesWHEREEmployeeCode=000002;第3章SQL*Plus基础3.3.2格式化查询输出使用SQL*Plus提供的各种格式化命令可为查询的输出结果定制格式。1．COLUMN命令使用COLUMN命令可以格式化实际的表列数据，设置列标题，为用户提供形式简单、意义清晰明了的标题。语法格式：COL[UMN]column_name[option]说明：option：可以用在COLUMN命令中，COLUMN命令的选项及定义见表3-2。第3章SQL*Plus基础表3-2COLUMN命令的选项及定义选项定义CLE[AR]除去列格式HEA[DING]text为列设置标题FOR[MAT]format改变列数据的外观NOPRI[NT]隐藏列NUL[L]text为空值分配要显示的文本PRI[NT]显示列第3章SQL*Plus基础fromat：指定该列的格式字符和宽度。例如，对于字符和日期列，A10表示该列是由字母数字组成的列，宽度为10。对于数字字段，有一些格式掩码，使用的符号见表3-3，这些格式掩码与to_char选项的格式掩码基本一致。格式化列既可以使用format选项，也可使用select语句的to_char选项。第3章SQL*Plus基础表3-3format掩码的符号符号定义格式输出样本9单个零禁止数字999965780加前导零0999906578$在数值前加美元前缀$9999$6578,千位分隔符9,9996,578第3章SQL*Plus基础表3-4COLUMN命令命令定义COL[UMN]column显示指定列的当前列的设置COL[UMN]显示所有列的当前列的设置COL[UMN]CLE[AR]清除指定列的设置CLE[AR]COL[UMN]清除所有列的设置第3章SQL*Plus基础【例3.13】为列Name设置标题为“LastName”。SQLCOLUMNNameHEADI