Oracle数据库系统应用开发实用教程电子课件第3章 使用SQLPLUS.ppt

Oracle数据库系统应用开发 1 2 第3章使用SQL PLUS SQL Plus概述 SQL Plus是Oracle提供的一个重要的用于访问数据库服务器的交互式管理工具 是Oracle的核心产品 SQL Plus不仅可以执行标准SQL语言 并且还对SQL命令进行扩展 提供了另外一些Oracle服务器能够接受和处理的命令 开发者和DBA可以通过SQL Plus直接灵活地存取Oracle数据库 完成相应的管理与开发任务 SQL Plus可以完成的主要工作 数据库的管理和维护 输入 编辑 存取和运行SQL命令 检查表和数据库对象定义 对查询结构进行格式化 计算 存储 打印或生成网络输出 应用程序开发 调试PL SQL程序段 执行sql脚本 生成新的sql脚本 供应用程序调用 如安装程序中进行脚本的安装 本阶段任务是要求熟练使用SQL Plus的常用命令 包括 连接命令 环境设置命令 格式化命令 编辑命令 文件操作命令 交互式命令等 5 工作情景 了解SQL Plus的常用命令 熟练使用SQL Plus的常用命令 任务分析 任务分解 任务1 使用连接或断开Oracle数据库 退出SQL Plus环境的命令 任务2 使用SQL Plus环境设置及保存环境变量命令 任务3 使用SQL Plus的各种格式化命令为查询输出结果定制格式 任务4 使用SQL Plus编辑器和编辑命令编辑SQL语句或PL SQL程序块 任务5 使用文件操作命令保存 加载和执行脚本文件 任务6 使用替换变量 绑定变量和交互通信命令完成用户与程序的交互操作 任务7 使用帮助命令 假脱机命令等其他命令完成更灵活的操作 学习目标 目标1 掌握连接或断开Oracle数据库 退出SQL Plus环境的命令 目标2 掌握SQL Plus环境设置及保存环境变量命令的使用方法 目标3 掌握使用SQL Plus的各种格式化命令为查询输出结果定制格式 目标4 掌握使用SQL Plus编辑器和编辑命令编辑SQL语句或PL SQL程序块 目标5 掌握使用文件操作命令保存 加载和执行脚本文件 目标6 掌握使用替换变量 绑定变量和交互通信命令完成用户与程序的交互操作 目标7 掌握使用帮助命令 假脱机命令等其他命令完成更灵活的操作 8 任务1 使用CONNECT命令连接或者切换到指定的数据库 使用DISCONNECT命令断开与数据库的连接 任务1 使用连接或断开Oracle数据库 退出SQL Plus环境的命令 9 任务1 1 CONN ECT 命令功能 先断开当前连接 然后建立新的连接 语法格式 conn ect username password connect identifier 例 SQL connscott tiger orcl 带ASSYSDBA或ASSYSOPER选项 则以特权用户身份连接 例 SQL connsys sys psw orclassysdba 10 任务1 2 DISC ONNECT 命令功能 断开与数据库的连接 但不退出SQL Plus环境 例 SQL disc3 EXIT或QUIT命令功能 退出SQL Plus环境 例 SQL exit 实训1 实训项目 连接数据库 断开数据库 进入 退出SQL PLUS环境 实训步骤 1 在操作系统环境下进入SQL PLUS环境 P102 2 使用CONNECT命令连接orcl数据库 3 使用DISCONNECT命令断后与orcl数据库的连接 4 使用EXIT或QUIT命令退出SQL PLUS环境 P102 12 任务2 利用环境参数控制SQL Plus的输出格式 两种方式设置参数 对话框图形工具 使用SET命令 任务2 使用SQL Plus环境设置及保存环境变量命令 13 环境设置命令 1 对话框方式在SQL Plus的环境下 单击菜单栏中的 选项 环境 在如图所示的设置环境对话框中可以进行环境设置 14 环境设置命令 2 命令方式使用SET命令改变SQL Plus环境参数的值 命令格式 SET其中 是指环境参数的名称 是指参数被设置成ON或OFF 或是某个具体的值 15 环境设置命令 系统提供了几十个环境参数 使用SHOW命令可以显示SQL Plus环境参数的值 SHOW命令格式一 SQL SHOWALL功能 将显示所有参数的当前设置 SHOW命令格式二 SHOW功能 将显示指定参数的当前设置 16 环境设置命令 3 常用的主要参数 1 LINESIZE和PAGESIZESETLINESIZE指定页宽是多少 SETPAGESIZE用来设置页的长度是多少 例3 1 设置行宽为60 设置页的长度为30 SQL SETLINESIZE60 SQL SETPAGESIZE30 17 环境设置命令 2 AUTOCOMMIT功能 设置是否自动提交DML语句 当设置为ON时 每次用户执行DML操作时都会自动提交 例3 2 显示或设置当前系统是否自动提交DML命令 SQL showautocommit autocommitOFFSQL setautocommiton 18 环境设置命令 3 PAUSE功能 当SETPAUSE设置为ON时 以引起SQL Plus在每页输出的开始处停止 在按回车键后继续滚动 4 TIME功能 当SETTIME设置为ON时 表示在每个命令提示前显示当前时间 5 NUMFORMAT功能 SETNUMFORMAT后面接着数字 以设置查询结果中显示数字的缺省格式 19 环境设置命令 4 保存系统环境变量命令使用STORE命令在主机操作系统文件中保存当前的系统变量 命令格式 STORESETfile name 例3 3 将SQL Plus系统变量的当前值保存到新命令文件plusstore sql中 SQL STORESETplusstore sql 20 环境设置命令 恢复保存的系统变量使用的命令格式 STARTfile name 例3 4 从命令文件中恢复系统变量 SQL STARTplusstore sql 将系统环境变量的值直接保存到当前目录中的login sql文件中 可以实现登录后自动加载环境变量 login sql存放在 bin目录下 会在SQL Plus启动时执行 实训2 实训项目 设置环境参数 保存 恢复变量 实训步骤 1 使用对话框方式设置环境参数 2 使用SET命令改变环境参数的值 3 使用SET命令设置页宽和页高 设置系统自动提交 4 练习使用PAUSE TIME NUNFORMAT命令 5 练习使用STORE START命令 22 任务3 设置各式各样的报表 执行各种查询等 都要为输出结果定制格式 任务3 使用SQL Plus的各种格式化命令为查询输出结果定制格式 23 格式化命令 1 列格式COLUMN命令功能 格式化实际的表列数据 设置列标题 语法格式 COL UMN column name option 说明 Option 可以用在COLUMN命令中的一些选项及定义 24 格式化命令 COLUMN命令的选项 25 格式化命令 Fromat 指定该列的格式字符和宽度 FORMAT掩码的符号 26 格式化命令 显示或清除当前COLUMN设置的命令 如表所示 27 格式化命令 例3 5 为列ename设置标题为 empname SQL COLUMNenameHEADING empname 若标题 empname 分两行显示 在emp和name之间插入 符号 SQL COLUMNenameHEADING emp name 例3 6 为列ename设置长度为6 SQL COLUMNenameFORMATa6 28 格式化命令 例3 7 为sal列定制格式 要求在每个值前加 符号作为前缀 并保留两个小数位 SQL COLUMNsalJUSTIFYRIGHTFORMAT 99 999 00 例3 8 用 0 替换所有空值 SQL COLUMNcommFORMAT9999NULL0 29 格式化命令 例3 9 显示comm的当前设置 SQL COLUMNcomm 例3 10 清除comm列的设置 SQL COLUMNcommCLEAR 30 格式化命令 2 分组显示命令BREAK日常工作中 常遇到生成报表时某字段出现重复值 为抑止重复值 把行分为几个部分 可用BREAK命令 BREAK命令和ORDERBY命令一起使用效果最好 语法格式 BREAKONColumn Name 31 格式化命令 例3 11 清除重复值 以查询scott emp表中的数据为例 SQL selectjob empno ename mgr hiredate sal commfromemporderbyjob 32 格式化命令 为了避免重复值 使用BREAK命令 输出结果如图所示 SQL breakonjob SQL selectjob empno ename mgr hiredate sal commfromemporderbyjob 33 格式化命令 3 TTITLE BTITLE REPHEADER REPFOOTER命令TTITLE命令用于设置报表中每页的顶部标题 BTITLE用于设置报表中每页的底部标题 REPHEADER用于设置报表的总标题 REPFOOTER用于设置报表的脚注 语法格式 TTITLE text OFF ON BTITLE text OFF ON 34 格式化命令 TTITLE和BTITLE命令一般分别用于创建页眉和页脚 若页眉选项设置为ON 报表输出时将显示用户指定的标题 系统日期和页码等 例3 12 给出报表的标题为 REPORTHEADER 页脚为 REPORTFOOTER SQL TTITLE REPORTHEADER SQL BTITLE REPORTFOOTER 例3 13 关闭报表标题和页脚命令SQL TTITLEOFF SQL BTITLEOFF 实训3 实训项目 使用命令格式化查询结果 实训内容与步骤 1 使用格式化命令 P105 COLUMN BREAK CLEAR2 报表输出练习 P105 打印雇员工资单 36 任务4 任务4 使用SQL Plus编辑器和编辑命令编辑SQL语句或PL SQL程序块 37 编辑命令 1 编辑器两种编辑器 缓冲区编辑器和外部编辑器 缓冲区编辑器是在SQL提示符下交互式的输入和修改SQL命令或PL SQL程序 又称为SQL缓冲区 外部编辑器如Notepad 在Windows系统中 可以通过DEFINE EDITOR命令定义或查看系统默认的外部编辑器 例如 SQL define editor notepadSQL define editorDEFINE EDITOR notepad CHAR Oracle10g的特点 使用图形方式先定义外部编辑器 然后再调用 定义方法 编辑 编辑程序 定义编辑程序 输入编辑器的名称 然后按 确定 按钮 调用方法 编辑 编辑程序 调用编辑器 38 39 编辑命令 2 编辑命令SQL Plus中常用的编辑命令及功能如表所示 实训4 实训项目 使用编辑器编辑SQL语句和程序块 实训内容与步骤 1 定义编辑器 P93 使用命令行方式在缓冲区编辑器中编辑命令 2 调用外部编辑器 P94 41 任务5 任务5 使用文件操作命令保存 加载和执行脚本文件 文件操作命令 1 生成脚本文件语法格式 SAVEfilename功能 将当前缓冲区的内容存储到脚本文件filename sql中 缺省文件扩展名为 sql 2 加载脚本文件语法格式 GETfilename sql功能 把脚本文件filename sql中的内容加载到缓冲区中3 执行脚本文件语法格式 STARTfilename sql功能 装载并执行filename sql文件中的命令 42 实训5 实训项目 文件操作命令的使用 实训内容与步骤 1 文件操作命令的使用 P103 SAVE DEL GET START 44 任务6 任务6 使用替换变量 绑定变量和交互式通信命令完成用户与程序的交互式操作 交互式命令 SQL PLUS允许自己定义变量来替换SQL语句中的实际值 称为替换变量的变量用来替换SQL语句中的值 在查询或脚本文件中定义和使用 替换变量很容易识别 前面使用 符号 在执行SELECT语句时 系统会提示用户输入变量的值 45 交互式命令 1 替换变量 1 使用替换变量 数值类型值的替换变量 例3 14 用 46 交互式命令 字符类型或日期类型值的替换变量 如果替换变量为字符类型或日期类型列数据 则需在SQL语句中将替换变量用单引号引起来 例3 15 字符类型的替换变量用单引号引起来 SQL selectename job salfromempwhereename 47 交互式命令 列名 表达式替换变量对于列名 ORDERBY子句 表名 整个WHERE子句表达式等均可以使用替换变量 例3 16 替换变量用于列名 SELECT执行此代码时 用户被要求输入列名 输入的列可以是任意多个 列与列之间用逗号分隔 48 交互式命令 例3 17 替换变量用于表达式 SQL SELECTenameFROMEmpWHERE var 49 交互式命令 2 定义替换变量为了预定义替换变量的值 可以使用define命令 例3 18 使用define命令预定义替换变量的值 SQL definen 7499 50 交互式命令 3 查看替换变量通过DEFINE命令可以查看当前SQL PLUS环境中保留的所有替换变量 4 清除替换变量UNDEFINE 例3 19 使用UNDEFINE命令 可以删除替换变量n SQL UNDEFINEn 51 交互式命令 2 绑定变量绑定变量也称为主机变量 在SQL PLUS环境中声明 匿名块不带任何参数 绑定变量可以作为参数传递给过程和参数 声明绑定变量的语法是 VARIABLEvariablenamedatatype例 SQL variablegnonumber 52 交互式命令 当用VARIABLE命令声明一个数字变量时 不使用精度和标度值 声明varchar2类型的变量时 不使用长度 在SQL PLUS环境中用PRINT命令显示主机变量的值 Oracle能够重复利用执行计划的方法就是采用绑定变量 绑定变量的实质就是用于替代sql语句中的常量的替代变量 绑定变量能够使得每次提交的sql语句都完全一样 53 交互式命令 普通sql语句 含绑定变量的sql语句 54 交互式命令 3 与用户交互通信命令 1 PROMPT功能 prompt命令用来在屏幕上显示指定的字符串 命令格式 prompt字符串例 SQL promptI maprogrammerI maprogrammer 55 交互式命令 2 ACCEPT功能 接收用户的键盘输入 把用户输入的数据存放到指定的变量中 一般与prompt命令配合使用 命令格式 accept变量名变量类型prompt提示信息选项例 SQL acceptxzynumberprompt请输入变量xyz的值 default0请输入变量xyz的值 100 56 交互式命令 3 PAUSE功能 暂停输出页的滚动 命令格式 pause文本其中文本是在暂停时向用户显示的提示信息 57 交互式命令 例3 20 构造一个文本文件 演示这几条命令的用法 文本文件iotest sql的功能是统计某个部门的员工工资 部门号需要用户从键盘输入 文本文件的内容如下 58 实训6 实训项目 定义 使用替换变量 使用交互通信命令 实训内容与步骤 1 使用替换变量 P103 掌握替换变量的使用 2 与用户通信掌握通信命令PROMPT PAUSE ACCEPT的使用 60 任务7 任务7 使用帮助命令 假脱机命令等其他命令完成更灵活的操作 help是获取帮助的命令 describe是获取表 函数 过程和包的描述信息 其他命令 1 help命令 例3 21 使用help命令可以得到联机的命令帮助信息 SQL help 显示所有命令的帮助信息 在help命令后加上具体命令名 可以快速了解一个命令的用法说明 SQL helpspool 61 其他命令 2 假脱机语法格式 Spoolspool file name假脱机 spooling 是将信息写到磁盘文件的一个过程 例3 22 将Employees表和Departments表的查询结果输出到文件d spool test prn中 62 其他命令 3 DESC RIBE 命令功能 列出表的结构 列出有关函数 过程以及包的信息 例3 23 列出emp的表结构SQL descemp 6