Oracle数据库基础及应用第05章SQLPlus命令.ppt

第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 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 Set选项及说明 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 setpauseoff 3 SETLINESIZEn选项使用LINESIZE选项 可以设置SQL Plus中一行数据可以容纳的字符数量 n表示每行能够显示的字符数 取值范围为1 32767 默认值为80 若是修改系统默认的每行打印80个字符 当SQL Plus输出LINESIZE指定数量的字符后 随后的数据就会折叠到下一行显示 如果用户窗口特别宽 那么用户就可以设置更宽的LINSIZE 以避免折叠显示SQL showlinesizeLinesize80SQL setlinesize110Linesize110 4 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 title2fromhr jobs3whererownum 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选项及其说明 1 FORMAT选项在SQL Plus中运行SELECT查询命令时 如果有返回结果 则结果会以行和列的形式显示 SQL Plus允许在COLUMN命令中使用FORMAT选项规定列的显示格式 默认情况下 对于日期型和字符型数据 SQL Plus中的列的显示宽度与定义表时指定列的宽度相同 并且左对齐 改变显示长度可以使用FORMATAn 其中A表示格式化之后的结果是字符型数据 n表示的是列的长度 若是指定的列的宽度比列表头小 会将其截断处理 对于数值型数据 SQL Plus中的列显示中右对齐 它还会对数据进行四舍五入操作以满足列的宽度的设置 如果列的设置不正确 会以 来代替数据显示结果 常用的格式字符串 例如 在SQL Plus中查询HR EMPLOYEES表中的Salary列 要求以货币符号 L 开头 则使用的COLUMN命令如下 2 HEADING选项默认情况下 查询语句中的列标题是从数据库中选择相应的列的名称 通过COLUMN命令可以为列指定一个别名 为列指定别名时需要在COLUMN命令中使用HEADING选项 例如 使用下面的命令为查询的各列指定别名 分别为 工作号 姓名和薪金 若是在语句中 用户想要查看某个列的显示属性 可以通过以下命令显示特定的列的显示属性 例如 在查询语句中 显示sal的特定属性 3 设置特定列的显示属性在SQL Plus语句运行中 用户还可以通过ON或OFF来设置某列的显示属性是否起到作用 例如 下面的例子通过OFF禁用了列的显示属性 4 CLEAR选项用户若是想要取消对刚才的列的显示属性的设置 则可以通过CLEAR选项清除设置的显示属性 例如 下面的例子清除了显示属性的salary列的显示属性