Java 企业数据库Oracle 应用


目录 第一章 走进 Oracle .......................................................................................................... 1 第二章 SQL 数据操作和查询............................................................................................23 第三章 常用函数、事务和锁 ...........................................................................................51 第四章 表空间、数据库对象 ...........................................................................................71 第五章 数据库设计 .........................................................................................................89 第六章 PL/SQL 程序设计 ............................................................................................... 103 第七章 子程序和程序包 ................................................................................................ 131 第八章 游标、内置程序包 ............................................................................................ 153 第九章 触发器、数据字典 ............................................................................................ 173 走进 Oracle 1 第 1 章 走进 Oracle 主要内容  Oracle 安装  Oracle 创建用户和角色  客户端链接 Oracle 服务器 第 1 章 走进 Oracle 2 1. Oracle 简介 在第一学期已经接触过关系型数据库 SQL Server,对数据库、表、记录、表的增删改查 操作等这些基本的概念已经了解,接下来我们进入当今主流数据库 Oracle 的学习。Oracle 是基于对象的关系型数据库,Oracle 也是用表的形式对数据存储和管理,并且在 Oracle 的操 作中添加了一些面向对象的思想。 Oracle 数据库是 Oracle(中文名称叫甲骨文)公司的核心产品,Oracle 数据库是一个适 合于大中型企业的数据库管理系统。在所有的数据库管理系统中(比如:微软的 SQL Server, IBM 的 DB2 等),Oracle 的市场占有率最高,显然 Oracle 的主要用户涉及面非常广,包括: 银行、电信、移动通信、航空、保险、金融、电子商务和跨国公司等。Oracle 产品是免费的, 可以在 Oracle 官方网站上下载到安装包,但是 Oracle 服务是收费的。在 Java 开发中,Oracle 数据库是非常理想的选择。 Oracle 公司成立以来,从最初的数据库版本到 Oracle7、Oracle8i、Oracle9i,Oracle10g 到 Oracle11g,虽然每一个版本之间的操作都存在一定的差别,但是 Oracle 对数据的操作基 本上都遵循 SQL 标准。因此对 Oracle 开发来说版本之间的差别不大。 很多人没有学习 Oracle 就开始发怵,因为人们在误解 Oracle,认为 Oracle 太难学了, 认为 Oracle 不是一般人用的数据库,其实任何数据库对于程序研发人员来说,都是大同小 异,因为目前多数数据库都支持标准的 SQL,SQL 是程序研发人员对数据库操作的工具。在 Oracle 这本书中,我们能学习到:  Oracle 的安装  Oracle 数据管理  Oracle 锁和事务  PL/SQL 编程  Oracle 子程序  游标和触发器  Oracle 基本管理 由于在第一学期已经接触了 SQL Server,所以学习 Oracle 不会很难,主要是实践,因此 在本书的学习中,认真的完成上机练习是学习好本书的基础。 接下来我们先从 Oracle 安装开始,接触一些 Oracle 中基本的概念。 第 1 章走进 Oracle 3 2. Oracle 安装 Oracle 数据库产品是免费的,我们可以从 Oracle 的官方网站(http://www.oracle.com)下 载到程序安装包,Oracle 在 windows 下的安装非常方便,安装开始后,一直点击安装程序的 “下一步”即可。 1. 下载 Oracle10g 后,解压到一个文件夹下,单击“setup.exe”文件即可启动安装界 面。如下图: 图 1 Oracle 安装启动界面 Oracle 主目录位置就是 Oracle 准备安装的位置,称为“Oracle_Home”,一般 Oracle 根 据当前计算机的硬盘大小默认给出一个合适的位置。Oracle 安装时可以只安装 Oracle 软件, 然后单独创建数据库,也可以在上图中选中“创建启动数据库”复选框,在安装 Oracle 产 品时,同时创建一个数据库,对初学者来说,推荐这样安装。填写全局数据库名,以及管理 员的密码。全局数据库名是数据库在服务器网络中的唯一标识。 2. 点击“下一步”,就会出现如下图内容,开始对 Oracle 服务器进行环境检查,主要 查看服务器是否符合 Oracle 安装的条件,比如操作系统是否支持、系统内存是否符 合 Oracle 安装的最低要求等。 第 1 章 走进 Oracle 4 图 2 Oracle 安装前环境检查 3. Oracle 检查通过后,单击“下一步”,就会列出所有安装 Oracle 过程中的默认选项。 图 3 Oracle 默认安装设置 4. 单击“安装”按钮,进入安装界面,这一过程经历时间比较长,根据计算机的性能 不同有很大差别。 第 1 章走进 Oracle 5 图 4 Oracle 安装 5. 上图完成后,进入了各种 Oracle 工具的安装阶段,包括网络配置向导,iSQL*plus 等(后面课程中讲解)。如下图所示: 图 5 Oracle 各种工具的安装 第 1 章 走进 Oracle 6 6. 接下来自动启动 DBCA(Database Configuration Assistant)进入创建默认数据库阶段。 图 6 DBCA 下安装数据库 Oracle 中的数据库主要是指存放数据的文件,这些文件在 Oracle 安装完成后,在计算机 硬盘上都能找到,包括数据文件,控制文件和数据库日志文件。 数据库创建后会有一系列为该数据库提供服务的内存空间和后台进程,称为该数据库的 实例。每一个数据库至少会有一个实例为其服务。实例中的内存结构称为系统全局区(SGA), 系统会根据当前计算机系统的性能给 SGA 分配非常可观的内存空间。 Oracle 创建数据库不能像 SQL Server 那样用一个简单的 create database 命令就能完成, 在创建数据库的过程中还需要配置各种参数。虽然有 DBCA 工具向导,但是仍然需要进行比 较麻烦的配置。 虽然一个 Oracle 数据库服务器中可以安装多个数据库,但是一个数据库需要占用非 常大的内存空间,因此一般一个服务器只安装一个数据库。每一个数据库可以有很 多用户,不同的用户拥有自己的数据库对象(比如:数据库表),一个用户如果访问 其他用户的数据库对象,必须由对方用户授予一定的权限。不同的用户创建的表, 只能被当前用户访问。因此在 Oracle 开发中,不同的应用程序只需使用不同的用户 访问即可。 第 1 章走进 Oracle 7 7. 数据库创建完毕后,需要设置数据库的默认用户。Oracle 中为管理员预置了两个用 户分别是 sys 和 system。同时 Oracle 为程序测试提供了一个普通用户 scott,口令管 理中,可以对数据库用户设置密码,设置是否锁定。 Oracle 客户端使用用户名和密 码登录 Oracle 系统后才能对数据库操作。 图 7 DBCA 下的口令管理 图 8 为 system,sys,scott 用户设置密码 默认的用户中,sys 和 system 用户是没有锁定的,安装成功后可以直接使用,scott 用户 默认为锁定状态,因此不能直接使用,需要把 scott 用户设定为非锁定状态才能正常使用。 第 1 章 走进 Oracle 8 这一步完成后,Oracle 系统安装成功。 Oracle 数据库中,默认情况下,所有系统的数据,SQL 关键字等都是大写的,在操作 过程中,Oracle 会自动把这些内容转换为大写,因此用户操作时不需考虑大小写问题, 一般情况下,为了良好的程序风格,程序中建议关键字用大写,非关键字可以使用 小写。 3. Oracle 客户端工具 Oracle 服务器安装成功后,就可以通过客户端工具连接 Oracle 服务器了,可以到 Oracle 官方下载 Oracle 专用的客户端软件,大多客户端工具都是基于 Oracle 客户端软件的。接下 来介绍几种常用的 Oracle 客户端工具。  SQL*Plus 工具 该工具是 Oracle 系统默认安装下,自带的一个客户端工具。在 windows 命令行中输入 “sqlplusw”命令,就能够启动该工具了。 图 9 SQL*Plus 工具 输入用户名和密码后,如果 SQL*Plus 与数据库服务器在同一台计算机上,并且当前服 务器下只有一个数据库实例,那么“主机字符串”可以不用填写。 第 1 章走进 Oracle 9 SQL*Plus 工具虽然是 Oracle 自带的工具,但是在现实开发中,基于该环境对开发不 方便,因此很少使用。 SQL*Plus 连接成功后就如图所示: 图 10 SQL*Plus 工具登录后  SQL*Plus 命令行工具 该命令行工具,提供了与数据库交互的能力和维护数据库的能力,包括了 Oracle 自带 的 SQL*Plus 工具的全部功能,在 Oracle 管理中经常使用。在命令行中输入:“sqlplus /nolog” 即可启动该工具。如下图: 图 11 启动 SQL*Plus 命令行工具 输入“sqlplus/nolog”命令后,只是启动了一个客户端进程,并没有与服务器连接,连 接到 Oracle 服务器的命令是: 第 1 章 走进 Oracle 10 conn 用户名/密码 @服务器连接字符串 as 连接身份 说明: 1. 连接身份:表示该用户连接后拥有的权限。  sysdba: 即数据库管理员,权限包括:打开数据库服务器、关闭数据库服务 器、备份数据库、恢复数据库、日志归档、会话限制、管理功能、创建数据库。 sys 用户必须用 sysdba 身份才能登录,system 用户可以用普通身份登录。  sysyoper:即数据库操作员,权限包括:打开数据库服务器、关闭数据库服务 器、备份数据库、恢复数据库、日志归档、会话限制。  normal:即普通用户,权限只有查询某些数据表的数据。默认的身份是 normal 用户。 2. 客户端工具可以根据“服务器连接字符串”对服务器进行连接,有了连接字符串后 客户端就可以像操作本机一样操作远程数据库,因此“服务器连接字符串”的配置 也叫本地网络服务配置,如果 SQL*Plus 工具启动在服务器上,并且服务器上只有一 个数据库实例的情况下,连接字符串可以缺省,在连接字符串中包括连接服务器的 协议,服务器的地址,服务器的端口等设置,Oracle 服务名等,该配置文件在 Oracle 安装目录下的: network/ADMIN/ tnsnames.ora。该文件是一个文本文件,用记事本 打开后如下所示: 图 12 服务器连接字符串配置  配置本地网络服务名 本地网络服务名,即客户端与服务器的连接字符串,本地网络服务名是客户端的配置, Oracle 客户端安装后,可以使用客户端自带的网络配置向导(Net Configuration Assistant)进 行配置: 第 1 章走进 Oracle 11 1. 启动 Net Configuration Assistant。选择“本地 Net 服务名配置”选项。如下图所示: 图 13 启动 Net Configuration Assistant 2. 选择“下一步”,本步骤可以对本地网络服务名进行添加,删除,测试是否正常连 接等操作,选择“添加”选项。 图 14 Net Configuration Assistant 第 1 章 走进 Oracle 12 3. 点击“下一步”,填写服务名,该服务名就是 Oracle 安装时(图 1),为数据库取 的全局数据库名。 图 15 服务名配置 4. 点击“下一步”,选择服务需要的协议,默认是 TCP 协议。推荐使用默认的 TCP 协 议。 图 16 选择协议 第 1 章走进 Oracle 13 5. 点击“下一步”,输入主机名,主机名可以是计算机名称,也可以是一个 IP 地址, 主机如果是本机,可以使用本机计算机名称、“localhost”、“127.0.0.1”、或者 本机的 IP 地址。 图 17 输入主机名和端口 6. 单击“下一步”,选择“是,进行测试”选项。进入下图界面。 图 18 测试成功 第 1 章 走进 Oracle 14 在测试时,默认采用的用户名和密码是 system/manager 进行测试,如果用户 system 的 密码不是“manager”,有可能测试通不过,更改登录后,输入正确的用户名和密码后再进 行测试即可。 7. 测试成功后,单击“下一步”,出现如下界面,这一步是为本地网络服务命名,即 图 12 中的服务器连接字符串名。 图 19 为网络服务名命名 点击“下一步”,配置就完成了,进入 tnsnames.ora 文件中查看,就出现了如图 12 中 的内容。  PL/SQL Developer 工具 在实际 Oracle 开发中,经常使用一个功能强大的第三方工具:“PL/SQL Developer”工 具。PL/SQL Developer 基本上可以实现 Oracle 开发中的任何操作。它运行在客户端时必须先 安装 Oracle 客户端,并且通过网络配置向导配置网络服务名后才能正常与服务器连接。 第 1 章走进 Oracle 15 图 20 PL/SQL Developer 4. Oracle 服务 Oracle 在 windows 中安装完成后,会安装很多服务,下面介绍几个主要的服务。 图 21 Oracle 服务  OracleService+服务名,该服务是数据库启动的基础,只有该服务启动了,Oracle 数 据库才能正常启动。这是必须启动的服务。  OracleOraDb10g_home1TNSListener,该服务是服务器端为客户端提供的监听服务, 只有该服务在服务器上正常启动,客户端才能连接到服务器。该监听服务接收客户 第 1 章 走进 Oracle 16 端发出的请求,然后将请求传递给数据库服务器。一旦建立了连接,客户端和数据 库服务器就能直接通信了。  OracleOraDb10g_home1iSQL*Plus,该服务提供了用浏览器对数据库中数据操作的方 式。该服务启动后,就可以使用浏览器进行远程登录并进行数据库操作了。如下图 所示: 图 22 iSQL*Plus  OracleDBConsole+服务名,Oracle10g 中的一个新服务。在 Oracle9i 之前,Oracle 官 方提供了一个基于图形界面的企业管理器(EM),从 Oracle10g 开始,Oracle 提供了 一个基于 B/S 的企业管理器,在操作系统的命令行中输入命令:emctl start dbconsole, 就可以启动 OracleDbConsole 服务,如下图所示: 图 23 EM 服务的启动 第 1 章走进 Oracle 17 服务启动之后,就可以在浏览器中输入上图中进入 EM 的地址,使用 B/S 方式管理 Oracle 服务器。 5. Oracle 启劢和关闭 OracleService 启动后,就可以对数据库进行管理了,Oracle 的启动和关闭是最基本的命 令,在 SQL*Plus 中,启动 Oracle 必须是 sys 用户,命令格式是: startup open 图 24 Oracle 服务启动 Oracle 服务关闭用命令:shutdown immediate 图 25 Oracle 服务关闭 第 1 章 走进 Oracle 18 6. Oracle 用户和权限 Oracle 中,一般不会轻易在一个服务器上创建多个数据库,在一个数据库中,不同的项 目由不同的用户访问,每一个用户拥有自身创建的数据库对象,因此用户的概念在 Oracle 中非常重要。Oracle 的用户可以用 CREATE USER 命令来创建。其语法是: CREATE USER 用户名 IDENTIFIED BY 口令 [ACCOUNT LOCK|UNLOCK] 说明: LOCK|UNLOCK 创建用户时是否锁定,默认为锁定状态。锁定的用户无法正常的登录进 行数据库操作。 比如创建一个用户 jerry,密码是 tom,默认非锁定,格式是: SQL> CREATE USER jerry 2 IDENTIFIED BY tom 3 ACCOUNT UNLOCK; Oracle 在 SQL*Plus 中的命令以分号(;)结尾,代表命令完毕并执行,系 统同时会把该命令保存在缓存中,缓存中只保存最近执行过的命令,如 果重新执行缓存中的命令,直接使用左斜杠符号(/)。如果命令不以分 号结尾,该命令只是写入缓存保存起来,但并不执行。 尽管用户成功创建,但是还不能正常的登录 Oracle 数据库系统,因为该用户还没有任 何权限。如果用户能够正常登录,至少需要 CREATE SESSION 系统权限。 Oracle 用户对数据库管理或对象操作的权利,分为系统权限和数据库对象权限。系统权 限比如:CREATE SESSION,CREATE TABLE 等,拥有系统权限的用户,允许拥有相应的系统操 作。数据库对象权限,比如对表中的数据进行增删改操作等,拥有数据库对象权限的用户可 以对所拥有的对象进行对应的操作。 还有一个概念就是数据库角色(role),数据库角色就是若干个系统权限的集合。下面 介绍几个常用角色:  CONNECT 角色:主要应用在临时用户,特别是那些不需要建表的用户,通常只赋予 他们 CONNECT role。CONNECT 是使用 Oracle 的简单权限, 拥有 CONNECT 角色的 用户,可以与服务器建立连接会话(session,客户端对服务器连接,称为会话)。 第 1 章走进 Oracle 19  RESOURCE 角色:更可靠和正式的数据库用户可以授予 RESOURCE role。RESOURCE 提供给用户另外的权限以创建他们自己的表、序列、过程(procedure)、触发器 (trigger)、索引(index)等。  DBA 角色:DBA role 拥有所有的系统权限----包括无限制的空间限额和给其他用户授 予各种权限的能力。用户 system 拥有 DBA 角色。 一般情况下,一个普通的用户(如 scott),拥有 CONNECT 和 RESOURCE 两个角色即可进 行常规的数据库开发工作。 可以把某个权限授予某个角色,可以把权限、角色授予某个用户。系统权限只能由 DBA 用户授权,对象权限由拥有该对象的用户授权,授权语法是: GRANT 角色|权限 TO 用户(角色) 比如给 Jerry 用户授权 CONNECT 和 RESOURCE 的方法是: SQL> GRANT CONNECT TO jerry; 授权成功。 SQL> GRANT RESOURCE TO jerry; 授权成功。 SQL> 回收权限的语法是: REVOKE 角色|权限 FROM 用户(角色) 修改用户的密码语法是: ALTER USER 用户名 IDENTIFIED BY 新密码。 修改用户处于锁定(非锁定)状态 ALTER USER 用户名 ACCOUNT LOCK|UNLOCK 第 1 章 走进 Oracle 20 7. 本章总结  Oracle 是基于对象的关系型数据库,Oracle 产品免费,服务收费。  Oracle 安装后默认会有两个管理员用户(system,sys)和一个普通用户 scott。  Sql*plus 是 Oracle 管理和数据操作的客户端工具。  客户端链接服务器前,服务器要启动监听服务,并且客户端工具要安装 Oracle 客 户端,并且在客户端要建立本地网络服务名。  Oracle 服务和监听启动后才能对数据库进行操作。  用 startup 命令启动数据库,用 shutdown 命令关闭数据库。  Oracle 的角色包括了一系列系统权限和普通对象权限,可以把权限授权给角色,把 权限或者角色授权给用户。 第 1 章走进 Oracle 21 8. 本章练习 1. 描述 Oracle 安装过程中的关键点。 2. 描述创建本地网络服务名的步骤。 3. 描述 Oracle 主要服务的作用。 4. Oracle 使用什么命令才能启动和关闭。 5. 什么是 Oracle 权限和角色?他们的关系是什么? 6. 创建一个用户,并授权 CONNECT 和 RESOURCE。 第 1 章 走进 Oracle 22 章节知识结构图 SQL 数据操作和查询 23 第 2 章 SQL 数据操作和查询 主要内容  Oracle 数据类型  SQL 建表和约束  SQL 对数据增删改  SQL 查询  Oracle 伪列 第 2 章 SQL 数据操作和查询 24 1. SQL 简介 在第一学期的 SQL Server 学习中,已经知道,SQL 是结构化查询语言(Structured Query Language),专门用于数据存取、数据更新及数据库管理等操作。并且已经学习了用 SQL 语 句对数据库的表进行增删改查的操作。 在 Oracle 开发中,客户端把 SQL 语句发送给服务器,服务器对 SQL 语句进行编译、执 行,把执行的结果返回给客户端。Oracle SQL 语句由如下命令组成:  数据定义语言(DDL),包括 CREATE(创建)命令、ALTER(修改)命令、DROP(删 除)命令等。  数据操纵语言(DML),包括 INSERT(插入)命令、UPDATE(更新)命令、DELETE (删除)命令、SELECT … FOR UPDATE(查询)等。  数据查询语言(DQL),包括基本查询语句、Order By 子句、Group By 子句等。  事务控制语言(TCL),包括 COMMIT(提交)命令、SAVEPOINT(保存点)命令、 ROLLBACK(回滚)命令。  数据控制语言(DCL), GRANT(授权)命令、REVOKE(撤销)命令。 目前主流的数据库产品(比如:SQL Server、Oracle)都支持标准的 SQL 语句。数据定义 语言,表的增删改操作,数据的简单查询,事务的提交和回滚,权限的授权和撤销等,Oracle 与 SQL Server 在操作上基本一致。 2. Oracle 数据类型 Oracle 数据库的核心是表,表中的列使用到的常见数据类型如下: 类型 含义 CHAR(length) 存储固定长度的字符串。参数 length 指定了长度,如果存储的字符串长 度小于 length,用空格填充。默认长度是 1,最长不超过 2000 字节。 VARCHAR2(length) 存储可变长度的字符串。length 指定了该字符串的最大长度。默认长度 是 1,最长不超过 4000 字符。 VARCHAR(length) varchar和 varchar2 目前没有区别,不过ocacle 以后的版本就不支持 varchar 类型,如果想新版本的数据库兼容就不要用 varchar,如果想和其它数据库 兼容就不要用 varchar2。 NUMBER(p,s) 既可以存储浮点数,也可以存储整数,p 表示数字的最大位数(如果是 小数包括整数部分和小数部分和小数点,p 默认是 38 位), s 是指小数位 第 2 章 SQL 数据操作和查询 25 数。 DATE 存储日期和时间,存储纪元、4 位年、月、日、时、分、秒,存储时间 从公元前 4712 年 1 月 1 日到公元后 4712 年 12 月 31 日。 TIMESTAMP 不但存储日期的年月日,时分秒,以及秒后 6 位,同时包含时区。 CLOB 存储大的文本,比如存储非结构化的 XML 文档 BLOB 存储二进制对象,如图形、视频、声音等。 表 1 Oracle 的部分数据类型 对应 NUMBER 类型的示例: 格式 输入的数字 实际的存储 NUMBER 1234.567 1234.567 NUMBER(6,2) 123.4567 123.46 NUMBER(4,2) 12345.67 输入的数字超过了所指定的精度,数据库不能存储 表 2 Number 示例 对于日期类型,可以使用 sysdate 内置函数可以获取当前的系统日期和时间,返回 DATE 类型,用 systimestamp 函数可以返回当前日期、时间和时区。 图 1 sysdate 和 sysTimestamp Oracle 的查询中,必须使用“select 列„ from 表”的完整语法,当查询单行函数的时 候,from 后面使用 DUAL 表,dual 表在系统中只有一行一列,该表在输出单行函数时为了 select…from 的语法完整性而使用。 第 2 章 SQL 数据操作和查询 26 3. 创建表和约束 Oracle 创建表使用 CREATE TABLE 命令。创建约束使用如下命令: ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束内容。 不论创建表还是约束,与 SQL Server 基本相同,注意:在 Oracle 中 default 是一个值, 而 SQL Server 中 default 是一个约束,因此 Oracle 的 default 设置可以在建表的时候创建。比 如创建一个学生信息表的约束。 代码演示:Oracle 创建表和约束 CREATE TABLE INFOS ( STUID VARCHAR2(7) NOT NULL, --学号 学号=‘S’+班号+2位序号 STUNAME VARCHAR2(10) NOT NULL, --姓名 GENDER VARCHAR2(2) NOT NULL, --性别 AGE NUMBER(2) NOT NULL, --年龄 SEAT NUMBER(2) NOT NULL, --座号 ENROLLDATE DATE, --入学时间 STUADDRESS VARCHAR2(50) DEFAULT '地址不详', --住址 CLASSNO VARCHAR2(4) NOT NULL --班号 班号=学期序号+班级序号 ) / ① ALTER TABLE INFOS ADD CONSTRAINT PK_INFOS PRIMARY KEY(STUID) ② / ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_GENDER CHECK(GENDER = '男' OR GENDER = '女') ③ / ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_SEAT CHECK(SEAT >=0 AND SEAT <=50) ④ / ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_AGE CHECK(AGE >=0 AND AGE<=100) ⑤ / ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_CLASSNO 第 2 章 SQL 数据操作和查询 27 CHECK((CLASSNO >='1001' AND CLASSNO<='1999') OR (CLASSNO >='2001' AND CLASSNO<='2999')) ⑥ / ALTER TABLE INFOS ADD CONSTRAINTS UN_STUNAME UNIQUE(STUNAME) ⑦ / 代码解析: ① 在 Oracle 代码中,“/”执行缓存区中的语句,由于缓冲区中只存储一条刚刚保存 过语句,由于每条语句没有用分号结尾,只是保存在缓冲区,因此每条语句后面都 有单独一行“/”。 ② 创建一个主键约束。 ③ 与 ④ ⑤ ⑥ ⑦一起创建各种 check 约束。其中⑦是唯一约束,表示该列值是唯一 的,列中的值不能重复。 Oracle 中创建外键约束与 SQL Server 相同。比如:现有成绩表定义如下: 代码演示:Oracle 创建表和约束 CREATE TABLE SCORES ( ID NUMBER , --ID ① TERM VARCHAR2(2), --学期 S1或S2 STUID VARCHAR2(7) NOT NULL, --学号 EXAMNO VARCHAR2(7) NOT NULL, --考号 E+班号+序号 WRITTENSCORE NUMBER(4,1) NOT NULL, --笔试成绩 LABSCORE NUMBER(4,1) NOT NULL --机试成绩 ) ALTER TABLE SCORES ADD CONSTRAINT CK_SCORES_TERM CHECK(TERM = 'S1' OR TERM ='S2') / ALTER TABLE SCORES ADD CONSTRAINT FK_SCORES_INFOS_STUID FOREIGN KEY(STUID) REFERENCES INFOS(STUID) ② / 代码解析: 第 2 章 SQL 数据操作和查询 28 ① SQL Server 中可以使用 identify 创建自动增长列,但是 Oracle 中的自动增长需要借助 序列(Sequence)完成,在后面章节中讲解。 ② Oracle 中的外键约束定义。 4. 数据操纵语言(DML) 数据操纵语言(DML)用于对数据库的表中数据进行添加、修改、删除和 Select…For Update(后面专门学习该查询)操作。对比一期学习过的 SQL Server 操作,接下来一一介绍在 Oracle 中的操作。  简单查询 数据查询是用 SELECT 命令从数据库的表中提取信息。SELECT 语句的语法是: SELECT *|列名|表达式 FROM 表名 WHERE 条件 ORDER BY 列名 说明: 1. *表示表中的所有列。 2. 列名可以选择若干个表中的列名,各个列表中间用逗号分隔。 3. 表达式可以是列名、函数、常数等组成的表达式。 4. WHERE 子句是查询的条件。 5. ORDER BY 要求在查询的结果中排序,默认是升序。 第 2 章 SQL 数据操作和查询 29 图 2 数据查询 Oracle 中可以把查询的结果根据结果集中的表结构和数据形成一张新表。语法是: CREATE TABLE 表名 AS SELECT 语句。 代码演示:根据结果集创建表 SQL> CREATE TABLE INFOS1 AS SELECT * FROM INFOS; TABLE CREATED 使用上面命令创建的新表中,不存在任何约束,并且把查询的数据一起插入到新表中。 如果只复制表结构,只需使查询的条件不成立(比如 where 1=2),就不会查询出任何数据, 从而复制一个表结构。 代码演示:复制表结构 SQL> CREATE TABLE INFOS2 AS SELECT * FROM INFOS WHERE 1=2; TABLE CREATED  数据插入 第 2 章 SQL 数据操作和查询 30 用 INSERT 命令完成对数据的插入。INSERT 命令的语法是: INSERT INTO 表名(列名 1,列名 2……) VALUES (值 1,值 2……) 说明: 1. 列名可以省略。当省略列名时,默认是表中的所有列名,列名顺序为表定义中列的 先后顺序。 2. 值的数量和顺序要与列名的数量和顺序一致。值的类型与列名的类型一致。 代码演示:向 INFOS 表和 SCORES 表中插入数据 SQL> INSERT INTO INFOS VALUES ( ① 2 's100102', '林冲', '男', 22, 2, 3 TO_DATE('2009-8-9 06:30:10',' YYYY-MM-DD HH24:MI:SS '), ② 4 '西安', '1001' 5 ) 6 / 1 row inserted SQL> INSERT INTO INFOS VALUES ( 's100104','阮小二','男',26,3,SYSDATE,default,'1001'); ③ 1 row inserted SQL>COMMIT; ④ 代码解析: ① 表名后面缺省了列名,默认是表 Infos 中的所有列名,values 中的值要与表中列一一 对应,包括顺序和数据类型的对应。在 SQL*Plus 中一条语句可以写在多行,那么从 第二行开始,sqlplus 会为每一行前面给出行号。 ② 在 Oracle 中,日期是国际化的,不同的区域安装的数据库,默认的日期格式不同, 因此为了程序便于移植,日期的输入要使用 TO_DATE 函数对日期格式化后输入,采 用格式化字符串对日期进行格式化时,格式化字符串中字符不区分大小写,常见的 格式化字符如下: 1. yyyy 表示四位年份 2. mm 表示两位月份,比如 3 月表示为 03 3. dd 表示两位日期 4. hh24 表示小时从 0-23,hh12 也表示小时从 0-11。 5. mi 表示分钟 6. ss 表示秒 第 2 章 SQL 数据操作和查询 31 ③ 在遇到存在默认值的列时,可以使用 default 值代替。 ④ commit 是把用户操作(添加、删除、修改操作)提交,只有提交操作后,数据才能 真正更新到表中,否则其他用户无法查询到当前用户操作的结果。 在 Oracle 中,一个 INSERT 命令可以把一个结果集一次性插入到一张表中。使用的语句 是:INSERT INTO 表 SELECT 子句,如下示例: 代码演示:INSERT 向表中插入一个结果集 SQL> INSERT INTO INFOS2 SELECT * FROM INFOS; 5 rows inserted 在这种语法下,要求结果集中每一列的数据类型必须与表中的每一列的数据类型一致, 结果集中的列的数量与表中的列的数量一致。比如表 INFOS2,该表的结构与 INFO 表一样, 那么可以把 INFO 表中的所有记录一次性插入到 INFOS2 表中。 Oracle 的简单查询和 SQL Server 一样都可以在查询列中使用常量,如图: 图 3 Select 中的常量 可以使用刚才的做法,把该结果集中的数据插入到表 INFOS 中。 代码演示:INSERT 向表中插入一个常量结果集 第 2 章 SQL 数据操作和查询 32 SQL> INSERT INTO INFOS SELECT 's100106','卢俊义','男',23,5, TO_DATE('2009-8-9 08:00:10','YYYY-MM-DD HH24:MI:SS'), '青龙寺','1001' FROM DUAL; 1 rows inserted SQL>COMMIT;  更新数据 Oracle 在表中更新数据的语法是: UPDATE 表名 SET 列名 1=值,列名 2=值…… WHERE 条件 代码演示:Update 操作 SQL> UPDATE INFOS SET CLASSNO='1002',STUADDRESS='山东莱芜' WHERE STUNAME='阮小二'; 1 rows updated SQL> commit;  删除数据 Oracle 在表中删除数据的命令是:DELETE,语法是: DELETE FROM 表名 WHERE 条件。 符合条件的记录将被删除。 代码演示:Delete 操作 SQL> DELETE FROM INFOS WHERE STUID='s100103'; 1 ROW DELETED SQL> COMMIT; 第 2 章 SQL 数据操作和查询 33  TRUNCATE 在数据库操作中, TRUNCATE 命令(是一个 DDL 命令)可以把表中的所有数据一次性 全部删除,语法是: TRUNCATE TABLE 表名。 TRUNCATE 和 DELETE 都能把表中的数据全部删除,他们的区别是: 1. TRUNCATE 是 DDL 命令,删除的数据不能恢复;DELETE 命令是 DML 命令,删除后的 数据可以通过日志文件恢复。 2. 如果一个表中数据记录很多,TRUNCATE 相对 DELETE 速度快。 由于 TRUNCATE 命令比较危险,因此在实际开发中,TRUNCATE 命令慎用。 5. 操作符 Oracle 开发中,依然存在算术运算,关系运算,和逻辑运算。  算术运算 Oracle 中的算术运算符,没有 Java 中的算术运算符丰富,只有+、-、*、/四个,其中除 号(/)的结果是浮点数。求余运算只能借助函数:MOD(x,y):返回 x 除以 y 的余数。 示例:每名员工年终奖是 2000 元,请显示基本工资在 2000 元以上的员工的月工资,年 总工资。 代码演示:查询中的算术运算 SQL> SELECT ENAME,SAL,(SAL*12+2000) FROM EMP WHERE SAL>2000; Oracle 默认安装中,已经创建了一个 scott 用户,默认密码是:tiger,该用户下有四 张表分别是:雇员表(EMP),部门表(DEPT),工资登记表和奖金表,请参考本章 后面的附表。接下来很多操作都是在该用户下完成的。 第 2 章 SQL 数据操作和查询 34 ENAME SAL (SAL*12+2000) JONES 2975 37700 BLAKE 2850 36200 CLARK 2450 31400 SCOTT 3000 38000 KING 5000 62000 FORD 3000 38000 6 rows selected  关系运算和逻辑运算 Oracle 中 Where 子句中经常见到关系运算和逻辑运算,常见的关系运算有: 运算符 说明 运算符 说明 = 等于 > 大于 <>或者!= 不等于 <= 小于或者等于 < 小于 >= 大于或者等于 表 3 Oracle 的关系运算符 逻辑运算符有三个:AND、OR、NOT 关系运算和逻辑运算与前面 SQL Server 学习过的一致。  字符串连接操作符(||) 在 Oracle 中,字符串的连接用双竖线(||)表示。比如,在 EMP 表中,查询工资在 2000 元以上的姓名以及工作。 代码演示:字符串连接 SQL> SELECT (ENAME || 'is a ' || JOB) AS "Employee Details" ① 2 FROM EMP 3 WHERE SAL>2000; 第 2 章 SQL 数据操作和查询 35 Employee Details ------------------------ JONESis a MANAGER BLAKEis a MANAGER CLARKis a MANAGER SCOTTis a ANALYST KINGis a PRESIDENT FORDis a ANALYST 6 rows selected 代码解析: ① Oracle 中字符串可以用单引号,也可以用双引号,在别名中存在空格时,必须用双 引号。在表名、列名时用双引号。 6. 高级查询 在第一期学习过 SQL 的简单查询和连接查询。现在学习一些新的 SQL 操作符。  消除重复行 在 Oracle 查询中结果中,可能出现若干行相同的情况,那么可以使用 DISTINCT 消除重 复行。具体的用法如示例: 代码演示:DISTINCT 消除重复行 SQL> SELECT DISTINCT DEPTNO FROM EMP; DEPTNO ------------ 30 20 10  NULL 操作 第 2 章 SQL 数据操作和查询 36 如果某条记录中有缺少的数据值,就是空值(NULL 值)。空值不等于 0 或者空格,空值 是指未赋值、未知或不可用的值。任何数据类型的列都可以包括 NULL 值,除非该列被定义 为非空或者主键。 代码演示:EMP 中的 NULL 值 SQL> SELECT ENAME,JOB,SAL,COMM FROM EMP WHERE SAL<2000; ENAME JOB SAL COMM SMITH CLERK 800 ALLEN SALESMAN 1600 300 WARD SALESMAN 1250 500 MARTIN SALESMAN 1250 1400 TURNER SALESMAN 1500 0 ADAMS CLERK 1100 JAMES CLERK 950 7 rows selected 在查询条件中 NULL 值用 IS NULL 作条件,非 NULL 值用 IS NOT NULL 做条件。 示例:查 询 EMP 表中没有发奖金的员工。 代码演示:NULL 值查询 SQL> SELECT ENAME,JOB,SAL,COMM FROM EMP 2 WHERE SAL<2000 AND COMM IS NULL; ENAME JOB SAL COMM SMITH CLERK 800 ADAMS CLERK 1100 JAMES CLERK 950 MILLER CLERK 1300  IN 操作 第 2 章 SQL 数据操作和查询 37 在 Where 子句中可以使用 IN 操作符来查询其列值在指定的列表中的行。比如:查询出 工作职责是 SALESMAN、PRESIDENT 或者 ANALYST 的员工。条件有两种表示方法: 1. Where job= 'SALESMAN ' or job= 'PRESIDENT ' or job= 'ANALYST ' 2. Where job in ('SALESMAN', 'PRESIDENT', 'ANALYST') 代码演示:IN 操作 SQL> SELECT ENAME,JOB,SAL FROM EMP 2 Where job in ('SALESMAN', 'PRESIDENT', 'ANALYST'); ENAME JOB SAL ALLEN SALESMAN 1600 WARD SALESMAN 1250 MARTIN SALESMAN 1250 SCOTT ANALYST 3000 KING PRESIDENT 5000 TURNER SALESMAN 1500 FORD ANALYST 3000 7 rows selected 对应 IN 操作的还有 NOT IN,用法一样,结果相反。  BETWEEN…AND… 在 Where 子句中,可以使用 BETWEEN 操作符来查询列值包含在指定区间内的行。比如, 查询工资从 1000 到 2000 之间的员工。可以使用传统方法: WHERE SAL>=1000 AND SAL<=2000 也可以使用: WHERE SAL BETWEEN 1000 AND 2000 BWTWEEN 操作所指定的范围也包括边界。 代码演示:BETWEEN 操作 第 2 章 SQL 数据操作和查询 38 SQL> SELECT ENAME,JOB,SAL FROM EMP WHERE SAL BETWEEN 1000 AND 2000; ENAME JOB SAL ALLEN SALESMAN 1600 WARD SALESMAN 1250 MARTIN SALESMAN 1250 TURNER SALESMAN 1500 ADAMS CLERK 1100 MILLER CLERK 1300 6 rows selected  LIKE 模糊查询 在一些查询时,可能把握不准需要查询的确切值,比如百度搜索时输入关键字即可查询 出相关的结果,这种查询称为模糊查询。模糊查询使用 LIKE 关键字通过字符匹配检索出所 需要的数据行。字符匹配操作可以使用通配符“%”和“_”:  %:表示零个或者多个任意字符。  _:代表一个任意字符。 语法是:LIKE '字符串'[ESCAPE '字符']。 匹配的字符串中,ESCAPE 后面的“字符”作为转义字符。与一期 SQLServer 中 ESCAPE 用法相同。 比如: 通配符表达式 说明 'S%' 以 S 开头的字符串。 '_S%' 第二个字符为 S 的字符串。 '%30\%%' escape '\' 包含“30%”的字符串,“\”指转义字符,“\%”在字符串 中表示一个字符“%”。 表 4 通配符示例 示例:显示员工名称以 J 开头以 S 结尾的员工的姓名、工作和工资。 代码演示:LIKE 操作 SQL> SELECT ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE 'J%S'; 第 2 章 SQL 数据操作和查询 39 ENAME JOB SAL ---------- --------- --------- JONES MANAGER 2975.00 JAMES CLERK 950.00  集合运算 集合运算就是将两个或者多个结果集组合成为一个结果集。集合运算包括:  INTERSECT(交集),返回两个查询共有的记录。  UNION ALL(并集),返回各个查询的所有记录,包括重复记录。  UNION(并集),返回各个查询的所有记录,不包括重复记录。  MINUS(补集),返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩 余的记录。 当使用集合操作的时候,要注意:所有查询所返回的列数以及列的类型必须匹配,列名 可以不同。 示例:查询出 dept 表中哪个部门下没有员工。只需求出 dept 表中的部门号和 emp 表中 的部门号的补集即可。 代码演示:求补运算 SQL> SELECT DEPTNO FROM DEPT 2 MINUS 3 SELECT DEPTNO FROM EMP; DEPTNO ----------- 40 前面学习过可以通过 insert into …select 把一个结果集插入到另一张结构相同的表中,因 此可以使用 union 把若干条记录一次性插入到一张表中。 代码演示:用 union 插入多条数据 第 2 章 SQL 数据操作和查询 40 SQL> INSERT INTO DEPT 2 SELECT 50,'公关部','台湾' FROM DUAL 3 UNION 4 SELECT 60,'研发部','西安' FROM DUAL 5 UNION 6 SELECT 70,'培训部','西安' FROM DUAL 7 / 3 rows inserted  连接查询 在 SQL Server 中已经学习过内联接(inner join)、外联接(outer join),外联接又分为左外联 接(left outer join)和右外联接(right outer join)。Oracle 中对两个表或者若干表之间的外联接用 (+)表示。 示例 1:请查询出工资大于 2000 元的,员工姓名,部门,工作,工资。 由于部门名称在 dept 中,其他的信息在 emp 表中,需要内联接才能完成。 代码演示:内联接 SQL> SELECT e.ENAME,e.JOB,e.SAL,d.DNAME 2 FROM EMP e,DEPT d 3 WHERE e.DEPTNO=d.DEPTNO 4 AND e.SAL>2000 5 / ENAME JOB SAL DNAME JONES MANAGER 2975 RESEARCH BLAKE MANAGER 2850 SALES CLARK MANAGER 2450 ACCOUNTING SCOTT ANALYST 3000 RESEARCH KING PRESIDENT 5000 ACCOUNTING FORD ANALYST 3000 RESEARCH 6 rows selected 第 2 章 SQL 数据操作和查询 41 也可以使用 SQL/92 标准中的内联接: SELECT e.ENAME,e.JOB,e.SAL,d.DNAME FROM EMP e INNER JOIN DEPT d ON e.DEPTNO=d.DEPTNO WHERE e.SAL>2000 这里 INNER JOIN 中,关键字 INNER 可以省略。 示例 2:请查询出每个部门下的员工姓名,工资。 Emp 表用外键 deptno 引用 Dept 表中的 deptno,在 Dept 表中如果有某些部门没有员工, 那么用内联接,没有员工的部门将无法显示,因此必须以 Dept 表为基准的外联接。 代码演示:外联接 SQL> SELECT e.ENAME,e.JOB,e.SAL,d.DNAME 2 FROM EMP e ,DEPT d 3 WHERE e.DEPTNO(+)=d.DEPTNO ① 4 / ENAME JOB SAL DNAME SMITH CLERK 800 RESEARCH ALLEN SALESMAN 1600 SALES WARD SALESMAN 1250 SALES JONES MANAGER 2975 RESEARCH MARTIN SALESMAN 1250 SALES BLAKE MANAGER 2850 SALES CLARK MANAGER 2450 ACCOUNTING SCOTT ANALYST 3000 RESEARCH KING PRESIDENT 5000 ACCOUNTING TURNER SALESMAN 1500 SALES ADAMS CLERK 1100 RESEARCH JAMES CLERK 950 SALES FORD ANALYST 3000 RESEARCH MILLER CLERK 1300 ACCOUNTING 第 2 章 SQL 数据操作和查询 42 公关部 研发部 培训部 OPERATIONS 18 rows selected 代码解析: ① (+):Oracle 专用的联接符,在条件中出现在左边指右外联接,出现在右边指左外联 接。 也可以使用 SQL/92 标准的写法: SELECT e.ENAME,e.JOB,e.SAL,d.DNAME FROM EMP e RIGHT OUTER JOIN DEPT d ON e.DEPTNO=d.DEPTNO 这里 RIGHT OUTER JOIN 中,关键字 OUTER 可以省略。  子查询 子查询可以在 SELECT、UPDATE、DELETE 语句内部可以出现 SELECT 语句。内部的 SELECT 语句结果可以作为外部语句中条件子句的一部分,也可以作为外部查询的临时表。子查询的 类型有: 1. 单行子查询:不向外部返回结果,或者只返回一行结果。 2. 多行子查询:向外部返回零行、一行或者多行结果。 示例 1:查询出销售部(SALES)下面的员工姓名,工作,工资。 该问题可以用联接查询实现,由于所需的结果信息都在 Emp 表中,可以先从 Dept 表中 查询出销售部对应的部门号,然后根据当前部门号再到 Emp 表中查询出符合该部门的员工 记录即可。从销售表中查询出的结果可以作为 Emp 表中查询的条件,SQL 语句实现如下。 虽然 Oracle 自身的联接查询语法比较好写,同时容易理解,但是为了程序便于移植, 推荐使用 SQL/92 表中的联接查询。同时也可以与 SQL Server 获得一致。 第 2 章 SQL 数据操作和查询 43 代码演示:单行子查询 SQL> SELECT ENAME,JOB,SAL FROM EMP 2 WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='SALES') ① 3 / ENAME JOB SAL ALLEN SALESMAN 1600 WARD SALESMAN 1250 MARTIN SALESMAN 1250 BLAKE MANAGER 2850 TURNER SALESMAN 1500 JAMES CLERK 950 6 rows selected 代码解析: ① 内部查询的结果作为外部查询的条件。需要注意:  如果内部查询不返回任何记录,则外部条件中字段 DEPTNO 与 NULL 比较永远 为假,也就是说外部查询不返还任何结果。  在单行子查询中外部查询可以使用=、>、<、>=、<=、<>等比较运算符。  内部查询返回的结果必须与外部查询条件中的字段(DEPTNO)匹配。  如果内部查询返回多行结果则出现错误。 示例 2:查询出 Emp 表中比任意一个销售员(“SALESMAN”)工资低的员工姓名、工作、 工资。 销售员在 Emp 表中有很多条记录,每个人工资不相等,如果返回“比任意员工的工资 还低”的条件,返回比“最高工资还低”即可。如果用子查询做,子查询中就会返回多条记 录。用普通的关系符(>、<等)运行就会出错。这时候需要用关键字 ANY。ANY 放在比较运 算符后面,表示“任意”的意思。 代码演示:ANY 子查询 SQL> SELECT ENAME,JOB,SAL FROM EMP 2 WHERE SALany 表示比子查询结果中最小的还大。 示例 3:查询出比所有销售员的工资都高的员工姓名,工作,工资。 ANY 可以表示任意的,但本案例中要求比所有销售员工资都高,那么就要使用另外一个 关键字 ALL。ALL 与关系操作符一起使用,表示与子查询中所有元素比较。 代码演示:ALL 子查询 SQL> SELECT ENAME,JOB,SAL FROM EMP 2 WHERE SAL>ALL (SELECT SAL FROM EMP WHERE JOB='SALESMAN') ① 3 / ENAME JOB SAL JONES MANAGER 2975 BLAKE MANAGER 2850 CLARK MANAGER 2450 SCOTT ANALYST 3000 KING PRESIDENT 5000 FORD ANALYST 3000 6 rows selected 代码解析: ① >ALL:比子查询结果中所有值还要大,也就是说,比子查询结果中最大值还要大。 SELECT ROWID,ENAME FROM EMP WHERE SAL>2000; ROWID ENAME AAAMgzAAEAAAAAgAAD JONES AAAMgzAAEAAAAAgAAF BLAKE AAAMgzAAEAAAAAgAAG CLARK AAAMgzAAEAAAAAgAAH SCOTT AAAMgzAAEAAAAAgAAI KING AAAMgzAAEAAAAAgAAM FORD 6 rows selected  ROWNUM 在查询的结果集中,ROWNUM 为结果集中每一行标识一个行号,第一行返回 1,第二 行返回 2,以此类推。通过 ROWNUM 伪列可以限制查询结果集中返回的行数。 第 2 章 SQL 数据操作和查询 46 示例 1:查询出员工表中前 5 名员工的姓名,工作,工资。 代码演示:ROWNUM SQL> SELECT ROWNUM,ENAME,JOB,SAL FROM EMP WHERE ROWNUM<=5; ROWNUM ENAME JOB SAL 1 SMITH CLERK 800 2 ALLEN SALESMAN 1600 3 WARD SALESMAN 1250 4 JONES MANAGER 2975 5 MARTIN SALESMAN 1250 示例 2:查询出工资最高的前 5 名员工的姓名、工作和工资。 “工资最高的前 5 名”需要先降序排序,再取前 5 名,但是生成 ROWNUM 操作比排序 要早,排序时已经连同 ROWNUM 一起排序了,因此不能直接在示例 1 的语句中直接加上 Order by 就行,而是需要对排序的结果重新做二次查询,产生新的 ROWNUM 才能作为查询 的条件依据。 代码演示:ROWNUM 应用 SQL> SELECT ROWNUM,T.* FROM ① 2 (SELECT ENAME,JOB,SAL 3 FROM EMP ORDER BY SAL DESC) T ② 4 WHERE ROWNUM<=5 5 / ROWNUM ENAME JOB SAL 1 KING PRESIDENT 5000 2 SCOTT ANALYST 3000 ROWNUM 与 ROWID 不同,ROWID 是插入记录时生成,ROWNUM 是查询 数据时生成。ROWID 标识的是行的物理地址。ROWNUM 标识的是查询结 果中的行的次序。 第 2 章 SQL 数据操作和查询 47 3 FORD ANALYST 3000 4 JONES MANAGER 2975 5 BLAKE MANAGER 2850 代码解析: ① T 是子查询②的别名,这里的 ROWNUM 是第二次查询后的 ROWNUM。 示例 3:查询出表 EMP 中第 5 条到第 10 条之间的记录。 这是分页的应用。在查询条件中,如果查询条件中 ROWNUM 大于某一正整数,则不返 还任何结果。 代码演示:ROWNUM 分页 SQL> SELECT * FROM 2 (SELECT ROWNUM R,ENAME,JOB,SAL ① 3 FROM EMP WHERE ROWNUM<=10) ② 4 WHERE R>5 ③ 5 / R ENAME JOB SAL 6 BLAKE MANAGER 2850 7 CLARK MANAGER 2450 8 SCOTT ANALYST 3000 9 KING PRESIDENT 5000 10 TURNER SALESMAN 1500 代码解析: ① 内部查询中得到 ROWNUM 并且用别名 R 记录,供外层条件③使用。 ② 内部查询的 ROWNUM,与外出的 ROWNUM 列是平等的两列。 ③ 使用的 R 是内层产生的 ROWNUM,在外层看来,内层查询的 ROWNUM 是正常的一 列。 第 2 章 SQL 数据操作和查询 48 8. 本章总结  Oracle SQL 语句中有数据操纵语言(DML)、数据定义语言(DDL)、数据控制语言 (DCL)、事务控制语言(TCL)等等。  DML 语句包括增删改查语句,DDL 语句包括数据库对象创建、修改和删除语句,数 据控制命令包括 GRANT、REVOKE 等,事务控制命令有 COMMIT、ROLLBACK 等。  数据库中建表常用的类型有:数字类型number(p,s),可变字符串varchar2(length), 日期 date。  Oracle 中 default 是一个值,在 Oracle 中不存在 default 约束。  Oracle 的增删改语句与 SQL Server 基本一致,都是使用 INSERT、UPDATE、DELETE 完成。  Oracle 高级查询中要注意:DISTINCT、NULL、IN、BETWEEN…AND…。  集合操作有:UNION、UNION ALL、INTESECT、MINUS。  联接查询有内联接和外联接。  子查询中有返回单行的子查询和返回多行的子查询。  Oracle 中存在 ROWID、ROWNUM 等伪列。 第 2 章 SQL 数据操作和查询 49 9. 本章练习 1. 创建一查询,显示与 Blake 在同一部门工作的雇员的项目和受雇日期,但是 Blake 不包含在内。 2. 显示位置在 Dallas 的部门内的雇员姓名、变化以及工作。 3. 显示被 King 直接管理的雇员的姓名以及工资。 4. 创建一查询,显示能获得与 scott 一样工资和奖金的其他雇员的姓名、受雇日期以 及工资。 附表 1:scott 表中的 EMP 表:员工表 序号 列名 类型 说明 1 EMPNO NUMBER(4) 员工编号,EMP 表主键 2 ENAME VARCHAR2(10) 员工姓名 3 JOB VARCHAR2(9) 员工工作 4 MGR NUMBER(4) 员工的领导编号,引用 EMPNO 5 HIREDATE DATE 入职日期 6 SAL NUMBER(7,2) 员工工资 7 COMM NUMBER(7,2) 员工奖金 8 DEPTNO NUMBER(2) 员工部门编号,是表 DEPT 的外键。 附表 2:scott 表中的 DEPT 表:部门表 序号 列名 类型 说明 1 DEPTNO NUMBER(2) 部门编号,主键 2 DNAME VARCHAR2(14) 部门名称 3 LOC VARCHAR2(13) 部门位置 附表 3:scott 表中的 SALGRADE 表:工资等级表 序号 列名 类型 说明 1 GRADE NUMBER 等级 2 LOSAL NUMBER 此等级下最低工资 3 HISAL NUMBER 此等级下最高工资 第 2 章 SQL 数据操作和查询 50 章节知识结构图 常用函数、事务和锁 51 第 3 章 常用函数、事务和锁 主要内容  转换函数和日期函数  事务操作  锁的概念 第 3 章 常用函数、事务和锁 52 1. Oracle 函数 Oracle SQL 提供了用于执行特定操作的专用函数。这些函数大大增强了 SQL 语言的功能。 函数可以接受零个或者多个输入参数,并返回一个输出结果。Oracle 数据库中主要使用两种 类型的函数: 1. 单行函数:对每一个函数应用在表的记录中时,只能输入一行结果,返回一个结果, 比如:MOD(x,y)返回 x 除以 y 的余数(x 和 y 可以是两个整数,也可以是表中的整 数列)。常用的单行函数有:  字符函数:对字符串操作。  数字函数:对数字进行计算,返回一个数字。  转换函数:可以将一种数据类型转换为另外一种数据类型。  日期函数:对日期和时间进行处理。 2. 聚合函数:聚合函数同时可以对多行数据进行操作,并返回一个结果。比如 SUM(x) 返回结果集中 x 列的总合。  字符函数 字符函数接受字符参数,这些参数可以是表中的列,也可以是一个字符串表达式。下表 列出了常用的字符函数。 函数 说明 ASCII(x) 返回字符 x 的 ASCII 码。 CONCAT(x,y) 连接字符串 x 和 y。 INSTR(x, str [,start] [,n]) 在 x 中查找 str,可以指定从 start 开始,也可以指定从第 n 次开始。 LENGTH(x) 返回 x 的长度。 LOWER(x) x 转换为小写。 UPPER(x) x 转换为大写。 LTRIM(x[,trim_str]) 把 x 的左边截去 trim_str 字符串,缺省截去空格。 RTRIM(x[,trim_str]) 把 x 的右边截去 trim_str 字符串,缺省截去空格。 TRIM([trim_str FROM] x) 把 x 的两边截去 trim_str 字符串,缺省截去空格。 REPLACE(x,old,new) 在 x 中查找 old,并替换为 new。 SUBSTR(x,start[,length]) 返回 x 的字串,从 start 处开始,截取 length 个字符,缺 省 length,默认到结尾。 表 1 字符函数 第 3 章常用函数、事务和锁 53 示例 示例结果 SELECT ASCII('a') FROM DUAL 97 SELECT CONCAT('Hello', ' world') FROM DUAL Hello world SELECT INSTR('Hello world','or') FROM DUAL 8 SELECT LENGTH('Hello') FROM DUAL 5 SELECT LOWER('hElLO') FROM DUAL; hello SELECT UPPER('hello') FROM DUAL HELLO SELECT LTRIM('===HELLO===', '=') FROM DUAL HELLO=== SELECT '=='||LTRIM(' HELLO===') FROM DUAL ==HELLO=== SELECT RTRIM('===HELLO===', '=') FROM DUAL ===HELLO SELECT '='||TRIM(' HELLO ')||'=' FROM DUAL =HELLO= SELECT TRIM('=' FROM '===HELLO===') FROM DUAL HELLO SELECT REPLACE('ABCDE','CD','AAA') FROM DUAL ABAAAE SELECT SUBSTR('ABCDE',2) FROM DUAL BCDE SELECT SUBSTR('ABCDE',2,3) FROM DUAL BCD 表 2 字符函数示例  数字函数 数字函数接受数字参数,参数可以来自表中的一列,也可以是一个数字表达式。 函数 说明 示例 ABS(x) x 绝对值 ABS(-3)=3 ACOS(x) x 的反余弦 ACOS(1)=0 COS(x) 余弦 COS(1)=1.57079633 CEIL(x) 大于或等于 x 的最小值 CEIL(5.4)=6 FLOOR(x) 小于或等于 x 的最大值 FLOOR(5.8)=5 LOG(x,y) x 为底 y 的对数 LOG(2,4)=2 MOD(x,y) x 除以 y 的余数 MOD(8,3)=2 POWER(x,y) x 的 y 次幂 POWER(2,3)=8 ROUND(x[,y]) x 在第 y 位四舍五入 ROUND(3.456,2)=3.46 SQRT(x) x 的平方根 SQRT(4)=2 TRUNC(x[,y]) x 在第 y 位截断 TRUNC(3.456,2)=3.45 表 3 数字函数 第 3 章 常用函数、事务和锁 54 说明: 1. ROUND(X[,Y]),四舍五入。 在缺省 y 时,默认 y=0;比如:ROUND(3.56)=4。 y 是正整数,就是四舍五入到小数点后 y 位。ROUND(5.654,2)=5.65。 y 是负整数,四舍五入到小数点左边|y|位。ROUND(351.654,-2)=400。 2. TRUNC(x[,y]),直接截取,不四舍五入。 在缺省 y 时,默认 y=0;比如:TRUNC (3.56)=3。 y 是正整数,就是小数点后 y 位。TRUNC (5.654,2)=5.65。 y 是负整数,小数点左边|y|位。TRUNC (351.654,-2)=300。  日期函数 日期函数对日期进行运算。常用的日期函数有: 1. ADD_MONTHS(d,n),在某一个日期 d 上,加上指定的月数 n,返回计算后的新日期。 d 表示日期,n 表示要加的月数。 图 1 ADD_MONTHS 函数示例 2. LAST_DAY(d),返回指定日期当月的最后一天。 第 3 章常用函数、事务和锁 55 图 2 LAST_DAY 函数示例 3. ROUND(d[,fmt]),返回一个以 fmt 为格式的四舍五入日期值,d 是日期,fmt 是格式 模型。默认 fmt 为 DDD,即月中的某一天。  如果 fmt 为“YEAR”则舍入到某年的 1 月 1 日,即前半年舍去,后半年作为下 一年。  如果 fmt 为“MONTH”则舍入到某月的 1 日,即前半月舍去,后半月作为下 一月。  默认为“DDD”,即月中的某一天,最靠近的天,前半天舍去,后半天作为第 二天。  如果 fmt 为“DAY”则舍入到最近的周的周日,即上半周舍去,下半周作为下 一周周日。 图 3 ROUND 函数示例 与 ROUND 对应的函数 TRUNC(d[,fmt])对日期的操作,TRUNC 与 ROUND 非常相似,只是 不对日期进行舍入,直接截取到对应格式的第一天。 第 3 章 常用函数、事务和锁 56 4. EXTRACT(fmt FROM d),提取日期中的特定部分。 fmt 为:YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。其中 YEAR、MONTH、DAY 可以为 DATE 类型匹配,也可以与 TIMESTAMP 类型匹配;但是 HOUR、MINUTE、SECOND 必 须与 TIMESTAMP 类型匹配。 HOUR 匹配的结果中没有加上时区,因此在中国运行的结果小 8 小时。 图 4 EXTRACT 函数示例  转换函数 转换函数将值从一种数据类型转换为另外一种数据类型。常用的转换函数有: 1. TO_CHAR(d|n[,fmt]) 把日期和数字转换为指定格式的字符串。fmt 是格式化字符串,日期的格式化字符串前 面已经学习过。 代码演示:TO_CHAR 对日期的处理 SQL> SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24:MI:SS') "date" ① 2 FROM DUAL; date ----------------------- 2009年08月11日 12:06:00 第 3 章常用函数、事务和锁 57 代码解析: ① 在格式化字符串中,使用双引号对非格式化字符进行引用。 针对数字的格式化,格式化字符有: 参数 示例 说明 9 999 指定位置处显示数字。 . 9.9 指定位置返回小数点 , 99,99 指定位置返回一个逗号 $ $999 数字开头返回一个美元符号 EEEE 9.99EEEE 科学计数法表示 L L999 数字前加一个本地货币符号 PR 999PR 如果数字是负数则用尖括号进行表示 表 4 数字格式化字符 代码演示:TO_CHAR 对数字的处理 SQL> SELECT TO_CHAR(-123123.45,'L9.9EEEEPR') "date" 2 FROM DUAL 3 / date -------------------- <¥1.2E+05> 2. TO_DATE(x [,fmt]) 把一个字符串以 fmt 格式转换为一个日期类型,前面已经学习过。 3. TO_NUMBER(x[,fmt]) 把一个字符串以 fmt 格式转换为一个数字。fmt 格式字符参考表 3。 第 3 章 常用函数、事务和锁 58 代码演示:TO_NUM 函数 SQL> SELECT TO_NUMBER('-$12,345.67','$99,999.99') "NUM" 2 FROM DUAL 3 / NUM --------------- -12345.67  其他单行函数 1. NVL(x,value) 如果 x 为空,返回 value,否则返回 x。 示例:对工资是 2000 元以下的员工,如果没有发奖金,每人奖金 100 元。 代码演示:NVL 函数 SQL> SELECT ENAME,JOB,SAL,NVL(COMM,100) FROM EMP WHERE SAL<2000; ENAME JOB SAL NVL(COMM,100) SMITH CLERK 800 100 ALLEN SALESMAN 1600 300 WARD SALESMAN 1250 500 MARTIN SALESMAN 1250 1400 TURNER SALESMAN 1500 50 ADAMS CLERK 1100 100 JAMES CLERK 950 100 7 rows selected 2. NVL2(x,value1,value2) 如果 x 非空,返回 value1,否则返回 value2。 示例:对 EMP 表中工资为 2000 元以下的员工,如果没有奖金,则奖金为 200 元,如果 有奖金,则在原来的奖金基础上加 100 元。 第 3 章常用函数、事务和锁 59 代码演示:NVL2 函数 SQL> SELECT ENAME,JOB,SAL,NVL2(COMM,comm+100,200) "comm" 2 FROM EMP WHERE SAL<2000; ENAME JOB SAL comm SMITH CLERK 800 200 ALLEN SALESMAN 1600 400 WARD SALESMAN 1250 600 MARTIN SALESMAN 1250 1500 TURNER SALESMAN 1500 150 ADAMS CLERK 1100 200 JAMES CLERK 950 200 MILLER CLERK 1300 200 8 rows selected  聚合函数 聚合函数同时对一组数据进行操作,返回一行结果,比如计算一组数据的总和,平均值 等。 名称 作用 语法 AVG 平均值 AVG(表达式) SUM 求和 SUM(表达式) MIN、MAX 最小值、最大值 MIN(表达式)、MAX(表达式) COUNT 数据统计 COUNT(表达式) 表 5 聚合函数 示例 1:求本月所有员工的基本工资总和 代码演示:SUM 函数 SQL> select sum(sal) from emp; SUM(SAL) ---------------- 29025 第 3 章 常用函数、事务和锁 60 示例 2:求不同部门的平均工资。 代码演示:AVG 函数下的分组查询 SQL> SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO; DEPTNO AVG(SAL) --------- ---------- 30 1566.66666 20 2175 10 2916.66666 2. 事务(transaction) 事务是作为单个逻辑工作单元执行一系列操作,即把若干条 SQL 语句在放在同一个单元 模块中,形成一个不可分割的工作单元。如果事务成功提交,则该事务中所有的操作更改均 会提交;如果事务遇到异常取消或者回滚,则所有操作均失败。 案例:有一张银行账户表,小美忘记了自己的账户中有 1000 元钱。阿聪的账户中有 1500 元钱,阿聪为了买一台电脑,需要从小美处借款 1200 元,小美答应后就到银行转账。实现 结果如下: 代码演示:转账 SQL> CREATE TABLE BANK 2 ( 3 ID NUMBER NOT NULL, 4 BNAME VARCHAR2(20), 5 CURRENTMONEY NUMBER(9) 6 ) 7 / TABLE CREATED SQL> ALTER TABLE BANK 2 ADD CONSTRAINT PK_ID PRIMARY KEY (ID) 3 / 第 3 章常用函数、事务和锁 61 TABLE ALTERED SQL> ALTER TABLE BANK 2 ADD CONSTRAINT CHECK_MONEY CHECK (CURRENTMONEY>=0) 3 / TABLE ALTERED SQL> INSERT INTO BANK VALUES (1,'小美',1000) 2 / 1 ROW INSERTED SQL> INSERT INTO BANK VALUES (2,'阿聪',1500) 2 / 1 ROW INSERTED SQL> UPDATE BANK SET CURRENTMONEY=CURRENTMONEY+1200 WHERE BNAME=' 阿聪' 2 / 1 ROW UPDATED SQL> UPDATE BANK SET CURRENTMONEY=CURRENTMONEY-1200 WHERE BNAME='小 美' 2 / UPDATE BANK SET CURRENTMONEY=CURRENTMONEY-1200 WHERE BNAME='小美' ORA-02290: 违反检查约束条件 (SCOTT.CHECK_MONEY) ① SQL> SELECT * FROM BANK ② 2 / ID BNAME CURRENTMONEY ---------- -------------------- -------------------------- 1 小美 1000 2 阿聪 2700 SQL> 代码解析: 第 3 章 常用函数、事务和锁 62 ① 违反约束,当前 update 操作失败,针对②的查询,中发现阿聪的账号中已经多了 1200 元,而小美的金额没有变化。 上述案例转账前后的数据明显不一致。事务操作可以把两个 update 语句作为一个独立 的工作单元,在提交时,或者两条语句都成功,或者两条语句都失败,使操纵前后数据永远 处于一致状态。 事务是一种操作数据的机制,是数据库处理的最小工作单元,事务有四个属性,称为事 务的 ACID 属性,分别是:  原子性(Atomicity):事务是一个独立的单元。一个事务中的若干语句是不可分的(原 子的)。也就是说,事务中的所有语句必须作为一个整体提交或者回滚。如果事务 中的任何操作失败,则整个事务将失败。  一致性(Consistency):当事务结束时,数据必须处于一致状态。在 Oracle 数据库中, 事务结束时,所有的内部数据结构(如 B 树索引或双向链表等)和数据(如:表 中的记录)都必须是正确的。  持久性(Durability):事务结束之后,对于系统的影响是永久的。也就是说,事务一 旦提交,数据库表中的数据将被更新,即便系统出现故障重新启动,数据库中更新 的数据也不会丢失。  隔离性(Isolation):对数据进行操作的若干并发事务之间彼此隔离。也就是说,事 务是独立的,一个事务内的操作不会依赖和影响其它事务。 在并发事务中,如果多个事务同时访问某一资源,(比如同时修改表中的某一行),可能 出现如下问题:  脏读取(Dirty Reads):一个事务更新了某数据但没有能够及时提交,而此时另一 个事务读取了该数据,然后使用了该数据。  不可重复读取(Non-repeatable Reads):一个事务对同一行数据重复读取两次,但 是却得到了不同的结果。例如,在两次读取数据的中间,有另外一个事务对该数据 进行了修改,并提交。  更新丢失(Lost updates):不可重复读取的特例。有两个并发事务同时读取同一行 数据,然后其中一个对它进行修改提交,而另一个也进行了修改提交。这就会造成 第一次写操作失效。  幻像读(Phantom Reads):事务在操作过程中进行两次查询,第二次查询的结果包 含了第一次查询中未出现的数据(这里并不要求两次查询的 SQL 语句相同)。这是 因为在两次查询过程中有另外一个事务插入数据造成的。 第 3 章常用函数、事务和锁 63 为了避免上面出现的几种情况,需要对并发事务中共享的资源(比如表中的某行数据) 进行锁定,一个事务锁定资源之后,该资源可以根据不同的锁类型确定是否允许被别的事务 读写。 按照锁定资源的访问级别划分,锁分为共享锁和排他锁。共享锁锁定的资源,其他的事 务也可以访问;排他锁锁定的资源只能被当前事务访问,直到该事务结束。锁定的资源在读 写中,又细分为:共享读锁、共享写锁、排他读锁和排他写锁,在标准 SQL 规范中,定义了 4 个事务隔离级,不同的隔离级别对事务的处理不同。  未提交读取(Read Uncommitted):允许脏读取,但不允许更新丢失。如果一个事 务已经开始写数据,另外一个数据则不允许同时进行写操作,但允许其他事务读操 作。该隔离级别可以通过“共享读锁”和“排他写锁”实现。  提交读取(Read Committed):允许不可重复读取,但不允许脏读取。这可以通过 “共享读锁”和“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数 据,但是未提交的写事务将会禁止其它事务访问该行。  可重复读取(Repeatable Read):禁止不可重复读取和脏读,但是有时可能出现幻 影数据。这可以通过“共享读锁”和“排他写锁”实现。读取数据的事务将会禁止 写事务(但允许读事务),写事务则禁止任何其他事务访问。  序列化(Serializable):提供严格的事务隔离。它要求事务序列化执行,事务只能 一个接着一个地执行,但不能并发执行。如果仅仅通过“行级锁”是无法实现事务 序列化的,必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问 到。 隔离级别越高,越能保证数据的完整性和一致性,但是对并发性能的影响也越大,对于 多数应用程序,可以优先考虑把数据库系统的隔离级别设为 Read Committed,它能够避免脏 读取,但是而且具有较好的并发性。尽管它会导致不可重复读、幻像读和更新丢失这些并发 问题,这些问题一般不影响对数据的正确性和一致性。特殊情况下,可以在可能出现这类问 题的场合,由应用程序采用悲观锁或乐观锁来控制。 悲观锁和乐观锁是应用程序对共享资源访问时的一种分类方法,悲观锁类似排他锁,乐 观锁类似共享锁。 第 3 章 常用函数、事务和锁 64 Oracle 中的事务中的锁由 Oracle 系统完成,Oracle 开发过程中,只需要关注事务的提交 命令(COMMIT),回滚命令(ROLLBACK)以及检查点保存(SAVEPOINT)来对事务进行操作。  COMMIT:提交事务后事务结束,默认状态下,新事务启动。  ROLLBACK:回滚事务,撤销事务中所有的操作。  SAVEPOINT:执行过程中的标记点,可以配合 ROLLBACK 使用,使一个长事务回滚 到特定的保存点。 代码演示:事务操作 SQL> UPDATE EMP SET MGR=7369 WHERE EMPNO=7499 2 / 1 ROW UPDATED SQL> SAVEPOINT M1 ① 2 / SAVEPOINT CREATED SQL> DELETE FROM EMP WHERE EMPNO=7934 2 / 1 ROW DELETED SQL> SAVEPOINT M2 ② 2 / SAVEPOINT CREATED SQL> UPDATE EMP SET COMM=COMM+300 2 / 13 ROWS UPDATED SQL> ROLLBACK TO SAVEPOINT M1 ③ 2 / ROLLBACK COMPLETE SQL> COMMIT ④ 2 / COMMIT COMPLETE 代码解析: ① 在当前位置定义一个标记 M1。 ② 在当前位置定义一个标记 M2。 ③ 回滚事务到 M1,如果只有 ROLLBACK 关键字,默认回滚当前所有的事务。 ④ 提交事务。 第 3 章常用函数、事务和锁 65 3. 锁 在事务并发时已经提到了锁的概念,锁是为了保证数据的并发性、完整性和一致性而引 入的一种机制。前面事务中已经认识了共享锁和排他锁是按照并发事务对资源访问级别划分 的。按照锁定的不同对象划分,又分为行级锁和表级锁。  行级锁:只对用户正在访问的行进行锁定。  表级锁:对整个表进行锁定。 Oracle 中实现加锁机制主要有两种方式:自动加锁和人工加锁。  自劢加锁: 在一般情况下,Oracle 会根据要求获得资源的同时,自动的使用不同类型的锁来控制数 据的并行存取,以防止用户之间的破坏性干扰。默认情况下,更新数据时 Oracle 会自动提 供排他锁,使不同用户更新表中同一记录时不会发生冲突。 用户在执行 INSERT、UPDATE、DELETE、DCL 语句、DDL 语句时,Oracle 系统会自动加锁。 在一个事务中,系统会自动锁定某一个数据库对象,只有该事务提交或者回滚后,系统释放 资源,其他用户才能使用该资源。 例如:不同用户对 EMP 表进行更新操作,第一个用户 update 后,第二个用户也进行更 新操作,在第一个用户没有提交或者回滚事务时,第二个用户的操作将一直无法正常运行。 图 5 行级锁 第 3 章 常用函数、事务和锁 66 使用:SELECT … FOR UPDATE OF 列 [WAIT n | NOWAIT]查询时也可以自动锁定查询的行。 只是更新时如果行中有锁。那么就会等待,可以使用 WAIT 或 NOWAIT 选择等待的时间或者 不等待。 比如:在 emp 表中正在修改的情况下运行下面语句。查询时如果资源被锁定,那么等 待 3 秒钟之后,资源仍然被锁定则自动放弃该操作。 代码演示:SELECT…FOR UPDATE SQL> SELECT * FROM EMP FOR UPDATE WAIT 3; SELECT * FROM EMP FOR UPDATE WAIT 3 ORA-30006: 资源已被占用; 执行操作时出现 WAIT 超时 当事务获得行级锁后,此事务也将自动获得表级共享锁,这时其他用户不能执行 DML 语句对该表进行操作,只能查询表中的数据。  人工加锁 除了系统自动加锁外,在数据库的管理中,也可以人工强制对表加锁。使用命令是: LOCK TABLE 表名 IN 锁类型 MODE [NOWAIT] NOWAIT 为了防止无限等待其他用户释放锁。 主动加锁的锁类型有:行共享锁(ROW SHARE),行排他锁(ROW EXCLUSIVE),共享更新锁 (SHARE UPDATE),共享锁(SHARE),共享排他锁(SHARE EXCLUSIVE),排他锁(EXCLUSIVE)。 代码演示:手工加锁:排他锁定 EMP 表 SQL> LOCK TABLE EMP IN EXCLUSIVE MODE NOWAIT; Table(s) locked 在 Oracle 实际开发中,一般不需要对 Oracle 的进行特殊的处理,都是系统自动完成 的。手工加锁一般都是数据库管理员为了数据库管理而加的锁。 第 3 章常用函数、事务和锁 67 4. 本章总结  Oracle 常用函数有字符相关的函数、数字相关的函数、日期相关的函数、转换函数 等。  EXTRACT 函数能够获取日期中的某个字段的值。  TO_CHAR 函数能够把数字和日期转换成固定的字符串格式。TO_DATE 函数能够把 固定格式的字符串转换为日期类型。  事务的四个属性是:原子性、一致性、持久性和隔离性,简称为 ACID。  事务的原子性表现在同一个事务的不同操作不可分割,或者全部成功,或者全部失 败。  事务的一致性表现在事务执行前后数据库的数据是正确的。  事务的持久性表现在事务一旦成功提交,更新的数据将永远保存,即便系统重启。  事务的隔离性表现在多个事务并发执行时,相互之间是隔离的。事务之间不会相互 依赖。  事务并发执行时如果占用同一个资源,将出现各种问题,比如:脏读、不可重复读、 更新丢失、幻像读等。解决的方法就是事务的隔离级别。隔离级别越高,越能保证 数据的完整性和一致性,但是对并发性能的影响也越大。  锁是多个用户同时对数据库操作时保证数据完整性和一致性的一种机制。从锁定资 源的级别上可以把锁分为:共享锁和排他锁;从应用程序对资源的使用来说可以把 锁分为:乐观锁和悲观锁;从锁定资源的对象来说锁可以分为行级锁和表级锁。当 执行 DML 语句和 SELECT…FOR UPDATE 操作时,系统自动加锁。在数据库管理时, 数据库管理员可以使用 LOCK 命令手工加锁。 第 3 章 常用函数、事务和锁 68 5. 本章练习 1. 描述 TO_CHAR 和 TO_DATE 函数的用法。 2. 描述 EXTRACT 函数的用法。 3. 你知道有哪些关于日期函数的用法? 4. 描述事务的 ACID 属性是什么意思。 5. 请描述并发事务中容易出现的问题。 6. 请描述什么是事务的隔离级别? 7. 描述锁在数据库中的作用。 第 3 章常用函数、事务和锁 69 章节知识结构图 第 3 章 常用函数、事务和锁 70 表空间、数据库对象 71 第 4 章 表空间、数据库对象 主要内容  同义词概念  序列的应用  视图的概念  索引的概念  表空间的概念 第 4 章 表空间、数据库对象 72 1. Oracle 数据库对象 数据库对象是数据库的组成部分,常常用 CREATE 命令进行创建,可以使用 ALTER 命令 修改,用 DROP 执行删除操作。前面已经接触过的数据库对象有表、用户等。 今天将学习更多的 Oracle 数据库对象:  同义词:就是给数据库对象一个别名。  序列:Oracle 中实现增长的对象。  视图:预定义的查询,作为表一样的查询使用,是一张虚拟表。  索引:对数据库表中的某些列进行排序,便于提高查询效率。 2. 同义词 同义词(Synonym)是数据库对象的一个别名,Oracle 可以为表、视图、序列、过程、 函数、程序包等指定一个别名。同义词有两种类型:  私有同义词:拥有 CREATE SYNONYM 权限的用户(包括非管理员用户)即可创建私 有同义词,创建的私有同义词只能由当前用户使用。  公有同义词:系统管理员可以创建公有同义词,公有同义词可以被所有用户访问。 创建同义词的语法是: CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema.]synonym_name FOR [schema.]object_name 说明: ① CREATE OR REPLACE:表示在创建同义词时,如果该同义词已经存在,那么就用 新创建的同义词代替旧同义词。 ② PULBIC:创建公有同义词时使用的关键字,一般情况下不需要创建公有同义词。 ③ Oracle 中一个用户可以创建表、视图等多种数据库对象,一个用户和该用户下 的所有数据库对象的集合称为 Schema(中文称为模式或者方案),用户名就是 Schema 名。一个数据库对象的全称是:用户名.对象名,即 schema.object_name。 如果一个用户有权限访问其他用户对象时,就可以使用全称来访问。比如: 第 4 章表空间、数据库对象 73 代码演示:system 用户访问 scott 用户的 Emp 表 SQL> conn system/manager@orcl; Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 Connected as system SQL> SELECT ENAME,JOB,SAL FROM SCOTT.EMP WHERE SAL>2000; ① ENAME JOB SAL JONES MANAGER 2975 BLAKE MANAGER 2850 CLARK MANAGER 2450 SCOTT ANALYST 3000 KING PRESIDENT 5000 FORD ANALYST 3000 6 rows selected 代码解析: ① 管理员用户可以访问任何用户的数据库对象,system 用户访问 SCOTT 用户的 EMP 表时,必须使用 SCOTT.EMP 案例:创建一个用户 XiaoMei,该用户拥有 CONNECT 角色和 RESOURCE 角色。为 SCOTT 用户的 EMP 表创建同义词,并通过同义词访问该 EMP 表。 代码演示:创建同义词并访问 SQL> CONN system/manager@orcl; Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 Connected as system SQL> CREATE USER XiaoMei IDENTIFIED BY XiaoMei; ① User created SQL> GRANT CONNECT TO XiaoMei; Grant succeeded SQL> GRANT RESOURCE TO XiaoMei; Grant succeeded 第 4 章 表空间、数据库对象 74 SQL> GRANT CREATE SYNONYM TO XiaoMei; Grant succeeded SQL> CONN XiaoMei/XiaoMei@ORCL; Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 Connected as XiaoMei SQL> CREATE SYNONYM MyEmp FOR SCOTT.EMP; ② Synonym created SQL> SELECT * FROM MYEMP; ③ SELECT * FROM MYEMP ORA-00942: 表或视图不存在 SQL> CONNECT SCOTT/tiger@ORCL Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 Connected as SCOTT SQL> GRANT ALL ON EMP TO XiaoMei; ④ Grant succeeded SQL> CONNECT XiaoMei/XiaoMei@ORCL; Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 Connected as XiaoMei SQL> SELECT ENAME,JOB,SAL FROM MyEmp WHERE SAL>2000; ⑤ ENAME JOB SAL JONES MANAGER 2975 BLAKE MANAGER 2850 CLARK MANAGER 2450 SCOTT ANALYST 3000 KING PRESIDENT 5000 FORD ANALYST 3000 6 rows selected 代码解析: 第 4 章表空间、数据库对象 75 ① 在管理员用户下创建用户 XiaoMei,对用户 XiaoMei 授予 CONNECT 和 RESOURCE 角 色。为了 XiaoMei 能够创建序列,必须授予系统权限:CREATE SYNONYM。 ② 在 XiaoMei 用户下,为 SCOTT.EMP 创建私有同义词 MyEmp,同义词 MyEmp 只能在 XiaoMei 用户下使用。访问 MyEmp 就是访问 SCOTT.EMP 对象。 ③ 访问 MyEmp 对象出错:对象不存在。因为 XiaoMei 如果访问 MyEmp,就相当于访 问 SCOTT.EMP 对象,那么 SCOTT 用户必须对 XiaoMei 授予相应的权限。 ④ SCOTT 用户下,把 EMP 表的所有权限(增删改查)授予 XiaoMei。 ⑤ 对 MyEmp 执行查询操作。MyEmp 就可以像在本地的表一样使用。 删除同义词使用的语法是: DROP [PUBLIC] SYNONYM [schema.]sysnonym_name 说明: ① PUBLIC:删除公共同义词。 ② 同义词的删除只能被拥有同义词对象的用户或者管理员删除。 ③ 此命令只能删除同义词,不能删除同义词下的源对象。 3. 序列 序列(Sequence)是用来生成连续的整数数据的对象。序列常常用来作为主键中增长列,序 列中的可以升序生成,也可以降序生成。创建序列的语法是: CREATE SEQUENCE sequence_name [START WITH num] [INCREMENT BY increment] [MAXVALUE num|NOMAXVALUE] [MINVALUE num|NOMINVALUE] [CYCLE|NOCYCLE] [CACHE num|NOCACHE] 说明: ① START WITH:从某一个整数开始,升序默认值是 1,降序默认值是-1。 ② INCREMENT BY:增长数。如果是正数则升序生成,如果是负数则降序生成。升序默认值 是 1,降序默认值是-1。 第 4 章 表空间、数据库对象 76 ③ MAXVALUE:指最大值。 ④ NOMAXVALUE:这是最大值的默认选项,升序的最大值是:1027,降序默认值是-1。 ⑤ MINVALUE:指最小值。 ⑥ NOMINVALUE:这是默认值选项,升序默认值是 1,降序默认值是-1026。 ⑦ CYCLE:表示如果升序达到最大值后,从最小值重新开始;如果是降序序列,达到最小 值后,从最大值重新开始。 ⑧ NOCYCLE:表示不重新开始,序列升序达到最大值、降序达到最小值后就报错。默认 NOCYCLE。 ⑨ CACHE:使用 CACHE 选项时,该序列会根据序列规则预生成一组序列号。保留在内存中, 当使用下一个序列号时,可以更快的响应。当内存中的序列号用完时,系统再生成一组 新的序列号,并保存在缓存中,这样可以提高生成序列号的效率。Oracle 默认会生产 20 个序列号。 ⑩ NOCACHE:不预先在内存中生成序列号。 示例,创建一个从 1 开始,默认最大值,每次增长 1 的序列,要求 NOCYCLE,缓存中有 30 个预先分配好的序列号。 代码演示:生成序列号 SQL> CREATE SEQUENCE MYSEQ 2 MINVALUE 1 3 START WITH 1 4 NOMAXVALUE 5 INCREMENT BY 1 6 NOCYCLE 7 CACHE 30 8 / Sequence created 序列创建之后,可以通过序列对象的 CURRVAL 和 NEXTVAL 两个“伪列”分别访问该序 列的当前值和下一个值。 代码演示:序列使用 SQL> SELECT MYSEQ.NEXTVAL FROM DUAL; NEXTVAL 第 4 章表空间、数据库对象 77 ---------- 1 SQL> SELECT MYSEQ.NEXTVAL FROM DUAL; NEXTVAL ---------- 2 SQL> SELECT MYSEQ.CURRVAL FROM DUAL; CURRVAL ---------- 2 使用 ALTER SEQUENCE 可以修改序列,在修改序列时有如下限制: 1. 不能修改序列的初始值。 2. 最小值不能大于当前值。 3. 最大值不能小于当前值。 使用 DROP SEQUENCE 命令可以删除一个序列对象。 代码演示:序列修改和删除 SQL> ALTER SEQUENCE MYSEQ 2 MAXVALUE 10000 3 MINVALUE -300 4 / SEQUENCE ALTERED SQL> DROP SEQUENCE MYSEQ; SEQUENCE DROPPED 4. 视图 视图(View)实际上是一张或者多张表上的预定义查询,这些表称为基表。从视图中查 询信息与从表中查询信息的方法完全相同。只需要简单的 SELECT…FROM 即可。 视图具有以下优点: 1. 可以限制用户只能通过视图检索数据。这样就可以对最终用户屏蔽建表时底层的基 表。 第 4 章 表空间、数据库对象 78 2. 可以将复杂的查询保存为视图。可以对最终用户屏蔽一定的复杂性。 3. 限制某个视图只能访问基表中的部分列或者部分行的特定数据。这样可以实现一定 的安全性。 创建视图的语法是: CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW view_name AS SELECT 查询 [WITH READ ONLY CONSTRAINT] 说明: 1. OR REPLACE:如果视图已经存在,则替换旧视图。 2. FORCE:即使基表不存在,也可以创建该视图,但是该视图不能正常使用,当基表 创建成功后,视图才能正常使用。 3. NOFORCE:如果基表不存在,无法创建视图,该项是默认选项。 4. WITH READ ONLY:默认可以通过视图对基表执行增删改操作,但是有很多在基表上 的限制(比如:基表中某列不能为空,但是该列没有出现在视图中,则不能通过视 图执行 insert 操作),WITH READ ONLY 说明视图是只读视图,不能通过该视图进行 增删改操作。现实开发中,基本上不通过视图对表中的数据进行增删改操作。 代码演示:视图 SQL> CREATE OR REPLACE VIEW EMPDETAIL 2 AS 3 SELECT EMPNO,ENAME,JOB,HIREDATE,EMP.DEPTNO,DNAME 4 FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO 5 WITH READ ONLY 6 / VIEW CREATED SQL> SELECT * FROM EMPDETAIL; ① EMPNO ENAME JOB HIREDATE DEPTNO DNAME 7369 SMITH CLERK 17-12 月-80 20 RESEARCH 7499 ALLEN SALESMAN 20-2 月 -81 30 SALES 7521 WARD SALESMAN 22-2 月 -81 30 SALES 第 4 章表空间、数据库对象 79 7566 JONES MANAGER 02-4 月 -81 20 RESEARCH 7654 MARTIN SALESMAN 28-9 月 -81 30 SALES 7698 BLAKE MANAGER 01-5 月 -81 30 SALES 7782 CLARK MANAGER 09-6 月 -81 10 ACCOUNTING 7788 SCOTT ANALYST 19-4 月 -87 20 RESEARCH 7839 KING PRESIDENT 17-11 月-81 10 ACCOUNTING 7844 TURNER SALESMAN 08-9 月 -81 30 SALES 7876 ADAMS CLERK 23-5 月 -87 20 RESEARCH 7900 JAMES CLERK 03-12 月-81 30 SALES 7902 FORD ANALYST 03-12 月-81 20 RESEARCH 7934 MILLER CLERK 23-1 月 -82 10 ACCOUNTING 14 ROWS SELECTED 代码解析: ① 对视图可以像表一样进行查询。该视图中隐藏了员工的工资。 删除视图可以使用“DROP VIEW 视图名称”,删除视图不会影响基表的数据。 5. 索引 当我们在某本书中查找特定的章节内容时,可以先从书的目录着手,找到该章节所在的 页码,然后快速的定位到该页。这种做法的前提是页面编号是有序的。如果页码无序,就只 能从第一页开始,一页页的查找了。 数据库中索引(Index)的概念与目录的概念非常类似。如果某列出现在查询的条件中, 而该列的数据是无序的,查询时只能从第一行开始一行一行的匹配。创建索引就是对某些特 定列中的数据排序,生成独立的索引表。在某列上创建索引后,如果该列出现在查询条件中, Oracle 会自动的引用该索引,先从索引表中查询出符合条件记录的 ROWID,由于 ROWID 是 记录的物理地址,因此可以根据 ROWID 快速的定位到具体的记录,表中的数据非常多时, 引用索引带来的查询效率非常可观。 第 4 章 表空间、数据库对象 80  如果表中的某些字段经常被查询并作为查询的条件出现时,就应该考 虑为该列创建索引。  当从很多行的表中查询少数行时,也要考虑创建索引。有一条基本的 准则是:当任何单个查询要检索的行少于或者等于整个表行数的 10% 时,索引就非常有用。 Oracle 数据库会为表的主键和包含唯一约束的列自动创建索引。索引可以提高查询的效 率,但是在数据增删改时需要更新索引,因此索引对增删改时会有负面影响。 创建索引的语法是: CREATE *UNIQUE+ INDEX index_name ON table_name(column_name*,column_name…+) 说明: 1. UNIQUE:指定索引列上的值必须是唯一的。称为唯一索引。 2. index_name:指定索引名。 3. tabl_name:指定要为哪个表创建索引。 4. column_name:指定要对哪个列创建索引。我们也可以对多列创建索引;这种索引 称为组合索引。 案例:为 EMP 表的 ENAME 列创建唯一索引,为 EMP 表的工资列创建普通索引,把 JOB 列先变为小写再创建索引。 代码演示:创建索引 SQL> CREATE UNIQUE INDEX UQ_ENAME_IDX ON EMP(ENAME); ① Index created SQL> CREATE INDEX IDX_SAL ON EMP(SAL); ② Index created SQL> CREATE INDEX IDX_JOB_LOWER ON EMP(LOWER(JOB)); ③ Index created 代码解析: ① 为 SCOTT.EMP 表的 ENAME 列创建唯一索引。 ② 为 SCOTT.EMP 表的 SAL 列创建索引。 ③ 在查询中可能经常使用 job 的小写作为条件的表达式,因此创建索引时,可以先对 JOB 列中的所有值转换为小写后创建索引,而这时需要使用 lower 函数,这种索引 称为基于函数的索引。 第 4 章表空间、数据库对象 81 在 select 语句查询时,Oracle 系统会自动为查询条件上的列应用索引。索引就是对某一 列进行排序,因此在索引列上,重复值越少,索引的效果越明显。 Oracle 可以为一些列值重复非常多且值有限的列(比如性别列)上创建位图索引。关于 Oracle 更多的索引类型(比如反向键索引等),请参考 Oracle 官方文档。 6. 表空间 在数据库系统中,存储空间是较为重要的资源,合理利用空间,不但能节省空间,还可 以提高系统的效率和工作性能。Oracle 可以存放海量数据,所有数据都在数据文件中存储。 而数据文件大小受操作系统限制,并且过大的数据文件对数据的存取性能影响非常大。同时 Oracle 是跨平台的数据库,Oracle 数据可以轻松的在不同平台上移植,那么如何才能提供统 一存取格式的大容量呢?Oracle 采用表空间来解决。 表空间只是一个逻辑概念,若干操作系统文件(文件可以不是很大)可以组成一个表空 间。表空间统一管理空间中的数据文件,一个数据文件只能属于一个表空间。一个数据库空 间由若干个表空间组成。如图所示: 图 1 数据空间、表空间和数据文件 Oracle 中所有的数据(包括系统数据),全部保存在表空间中,常见的表空间有:  系统表空间:存放系统数据,系统表空间在数据库创建时创建。表空间名称为 system。 存放数据字典和视图以及数据库结构等重要系统数据信息,在运行时如果 system 空间不足,对数据库影响会比较大,虽然在系统运行过程中可以通过命令扩充空间, 第 4 章 表空间、数据库对象 82 但还是会影响数据库的性能,因此有必要在创建数据库时适当的把数据文件设置大 一些。  TMEP 表空间:临时表空间,安装数据库时创建,可以在运行时通过命令增大临时 表空间。临时表空间的重要作用是数据排序。比如当用户执行了诸如 Order by 等命 令后,服务器需要对所选取数据进行排序,如果数据很大,内存的排序区可能装不 下太大数据,就需要把一些中间的排序结果写在硬盘的临时表空间中。  用户表自定义空间:用户可以通过 CREATE TABLESPACE 命令创建表空间。 创建表空间需要考虑数据库对分区(Extent,一个 Oracle 分区是数据库文件中一段连续 的空间,Oracle 分区是 Oracle 管理中最小的单位)的管理,比如当一个表创建后先申请一个 分区,在 Insert 执行过程中,如果分区数据已满,需要重新申请另外的分区。如果一个数据 库中的分区大小不一,创建表空间时需要考虑一系列问题。因此在 Oracle8i 之后,创建表空 间都推荐使用“本地管理表空间”,这种表空间中的分区是一个固定大小的值,创建表空间 的语法是: CREATE TABLESPACE 空间名称 DATAFILE '文件名 1' SIZE 数字 M [,'文件名 2' SIZE 数字….+ EXTENT MANAGEMENT LOCAL UNIFORM SIZE 数字 M 说明: 1. 文件名包括完整路径和文件名,每个数据文件定义了文件的初始大小,初始大小一 般以“M”为单位。一个表空间中可以有多个数据文件。 2. EXTENT MANAGEMENT LOCAL 指明表空间类型是:本地管理表空间。本地管理表空 间要求 Oracle 中的数据分区(Extent)大小统一。 3. UNIFORM SIZE:指定每个分区的统一大小。 示例:创建一个表空间,包含两个数据文件大小分别是 10MB,5MB,要求 extent 的大 小统一为 1M。 代码演示:创建表空间 SQL> CREATE TABLESPACE MYSPACE 2 DATAFILE 'D:/A.ORA' SIZE 10M, 3 'D:/B.ORA' SIZE 5M 4 EXTENT MANAGEMENT LOCAL 第 4 章表空间、数据库对象 83 5 UNIFORM SIZE 1M 6 / Tablespace created 必须是管理员用户才能创建表空间,当表空间的空间不足时可以使用 ALTER TABLESPACE 命令向表空间中追加数据文件扩充表空间。 代码演示:扩充表空间 SQL> ALTER TABLESPACE MYSPACE 2 ADD DATAFILE 'D:/C.ORA' SIZE 10M 3 / Tablespace altered 表空间可以在不使用时删除,使用 DROP TABLESPACE 命令。 数据库的所有数据全部在某一表空间中存放,在创建用户时,可以为用户指定某一表空 间,那么该用户下的所有数据库对象(比如表)默认都存储在该空间中。 代码演示:为某一用户指定默认表空间 SQL> CREATE USER ACONG IDENTIFIED BY ACONG 2 DEFAULT TABLESPACE MYSPACE 3 / User created 在创建表时,表中数据存放在用户的默认表空间中,也可以通过 tablespace 子句为表指 定表中数据存放在其他表空间中。 代码演示:为表指定表空间 SQL> CREATE TABLE SCORES 2 ( 3 ID NUMBER , 第 4 章 表空间、数据库对象 84 4 TERM VARCHAR2(2), 5 STUID VARCHAR2(7) NOT NULL, 6 EXAMNO VARCHAR2(7) NOT NULL, 7 WRITTENSCORE NUMBER(4,1) NOT NULL, 8 LABSCORE NUMBER(4,1) NOT NULL 9 ) 10 TABLESPACE MYSPACE 11 / Table created 创建索引时也可以为索引指定表空间。 代码演示:为索引指定表空间 SQL> CREATE INDEX UQ_ID ON SCORES(ID) 2 TABLESPACE MYSPACE; Index created 表和索引一旦创建,表空间无法修改。 第 4 章表空间、数据库对象 85 7. 本章总结  Oracle 数据库对象都是使用 CREATE 命令创建的。  同义词就是数据库对象的一个别名。同义词的类型有公有同义词和私有同义词。只 有管理员可以创建共有同义词。创建同义词的命令是:CREATE SYNONYM。  序列能够产生一个连续不重复的整数。经常作为数据库的主键生成器。创建序列的 命令是 CREATE SEQUENCE。  序列的访问使用两个“伪列”,CURRVAL 表示序列的当前值,NEXTVAL 表示序列的 下一个值。  视图就是一个预处理的查询语句,可以从若干表中过滤数据。  索引就是在查询中经常使用的列进行排序。常见的索引有:普通索引、唯一序列、 组合索引以及基于函数的索引。此外还有位图索引、反向键索引等。  表空间是数据库的一个逻辑概念,表空间由若干个数据文件组成。为数据库对象和 数据提供统一的空间管理。 第 4 章 表空间、数据库对象 86 8. 本章练习 1. 产生一个用于 DEPT 表的主键值的序列,序列起始值是 100,最大值是 500,增长步 长是 10。 2. 用序列产生 DEPT 表的主键,向 DEPT 表中插入 3 条记录。 3. 为 DEPT 表创建一个同义词。 4. 创建一个视图包括 EMP 表的 EMPNO,ENAME,JOB,部门表的 DNAME 列,只能包含 销售部的记录。 5. 为 EMP 表的 ENAME 列创建唯一索引。 6. 为 EMP 表的 SAL 列创建一个普通索引。 7. 以学期和学生姓名为名称比如(S2XiaoMei)创建一个表空间,该表空间是以学生姓 名为用户的默认表空间。 第 4 章表空间、数据库对象 87 章节知识结构图 第 4 章 表空间、数据库对象 88 数据库设计 89 第 5 章 数据库设计 主要内容  数据库设计的步骤  数据库的 E-R 图  数据规范化 第 5 章 数据库设计 90 1. 数据库设计概述 在前面的数据库学习过程中,我们只是对数据库进行建表操作,并对表进行添加、修改、 删除以及各种各样的查询,并没有提到数据库设计的概念。在前面学习时使用的数据表,都 非常简单,不需要太多的设计;但是遇到大项目,可能需要上百张表,表和表之间的关系非 常复杂,如果不对数据库进行精心的设计,不但影响查询效率,影响数据库的可操作性,同 时对程序性能也会有很大的影响。 数据库设计是指根据用户的需求,规划数据库中的数据对象以及数据对象之间的关系, 设计数据库的结构和建立数据库的过程。数据库设计非常重要,糟糕的数据库设计会使系统 效率低下、不利于更新和检索数据;良好的数据库设计可以提升应用系统的运行和开发效率、 便于扩展。 2. 设计数据库的步骤 软件开发的步骤大体上分为: 1. 需求分析:分析客户的业务和数据处理的需求。 2. 概要设计:在项目团队内部、设计人员和客户之间进行 ,确认需求信息的正确性 和完整性。此阶段,数据库设计方面要设计出各种实体、属性,并以 E-R 图的形式 表示出来。 3. 详细设计:对项目的具体实现进行规划,程序中的类以及类中的方法名都能在这个 阶段进行设计。此阶段,数据库设计方面要将 E-R 图转换为表,并对表添加各种约 束。 4. 代码编写:基于详细设计进行代码实现。 5. 运行测试:对项目进行各种测试,包括单元测试、集成测试和系统测试等。 6. 部署发行:对项目进行部署,并且开始商业应用。 数据库设计是在代码编写前完成的,需求分析阶段重点是调查、收集并分析客户业务数 据需求、处理需求、安全性与完整性需求。业务数据需求是创建数据库表的依据;数据处理 需求是创建存储过程(函数)等数据库对象以及对表进行 CRUD 的依据;完整性需求是创建 各种约束的依据。 数据库的设计过程大致可分为 5 个步骤: 第 5 章数据库设计 91 1. 需求分析:在本阶段主要是得到准确并且完整的用户需求信息,包括数据及其处理。 在该阶段中首先要进行调查,调查的方法有:在客户的公司跟班实习、组织召开调 查会、邀请专人介绍、调查问卷、查阅客户业务相关的数据。根据调查的信息进行 分析,整理出客户与研发人员都能明白的文档(系统需求说明书)。该阶段是整个 设计过程的基础,是最困难、最耗费时间的一步。 2. 概念设计:通过对用户需求进行综合、归纳与抽象,形成一个独立于具体 DBMS 的 概念模型。通过系统需求说明书中的描述,标识出系统需要的各种对象(实体)、 各种对象的具体属性和各种对象之间的联系。一般本阶段要形成整个系统的 E-R 图 (实体关系图)。是整个数据库设计的关键。 3. 逻辑设计:将概念结构转换为某个 DBMS 所支持的数据模型,包括各种数据库对象, 表中数据的数据类型等。这里的表一般是由 E-R 转换过来的。可以借助各种辅助工 具进行。比如 ORACLE Designer 2000、SYBASE PowerDesigner 等。 4. 物理设计:为逻辑数据模型选取一个最适合应用环境的物理结构(包括存储结构和 存取方法、索引结构等)。 5. 运行验证:在上述设计的基础上,收集数据并具体建立一个数据库,运行一些典型 的应用任务来验证数据库设计的正确性和合理性。一个大型数据库的设计过程往往 需要经过多次循环反复。当设计中的某一步聚发生问题时,可能就需要返回到前面 去进行修改。因此,在做上述数据库设计时就应考虑到今后修改设计的可扩展性和 方便性。 3. 概念设计 概念设计实际上就是把需求分析中的数据进行转化,形成程序员视图的过程。其中的关 键点有:标识对象、确定对象的属性、标识对象之间的关系、形成 E-R 图。下面用案例来说 明每一步的设计过程。 案例:现在要做一个论坛,功能包括:用户注册、用户发帖和跟帖(回帖),各版块间 需要一名注册用户作为版主。  标识对象 第 5 章 数据库设计 92 在数据搜集、明确需求之后,必须标识出数据库所需的关键对象或实体。在第一学期我 们已经学习过对象,我们知道对象可以是有形的事物(比如人、产品等),也可以是无形的 事物(比如交易、Bug、管理等),在系统中标识出这些对象后,与他们相关的对象才能条理 清楚。上面的案例中,根据业务确定的业务对象有:  论坛用户:包括普通发帖、跟帖用户以及各版块的版主。  发帖信息:用户发的主帖。  回帖信息:用户回复的信息。  版块:论坛的各个版块信息。 数据库中的每个不同对象都拥有与其对应的表,也就是说,数据库中至少会有四张表, 论坛用户表、主贴表、跟帖表和版块表。  标识每个对象的属性 下一步就是标识每个对象的详细信息,也称为对象的属性。这些属性将组成表的列。也 就是说,需要细分出每个对象包含的成员信息。在本案例中,发帖和回帖的信息有很大不同, 因此可以分为两个对象。各个对象以及详细信息如下图: 图 1 案例中的各个对象详细信息 第 5 章数据库设计 93  标识对象之间的关系 在实际中,以上的各业务对象并不是互相孤立的,而是存在一定的联系,如下:  发帖和回帖有主从关系,需要在回帖中表明他是哪个主贴的回帖。  版块管理中,根据版主 ID 可以查出版主在用户表中的详细信息。  版块与发帖之间是主从关系,需要表明发帖时贴子是属于哪个版块的。  版块和回帖也有主从关系,需要表明回帖时贴子是属于哪个版块的。 确定了对象以及对象的属性之后,就可以站在研发人员的角度对系统进行概念描述,可 以使用各种描述工具进行,常见的是 E-R 图(Entity-Relationship 实体关系图)。 4. E-R 图 在需求分析完成后,我们对数据库有了一个概念上的认识,接下来我们需要在研发人员 内部进行沟通,讨论数据库的设计是否满足客户的业务和数据处理需求,与建筑施工图一样, 数据库的设计也需要图形化的方式进行描述,这就是实体关系图(Entity Relationship,E-R 图),它包括一些特定的符号,每个符号表示特定的含义。 在第一期学习中,我们已经知道描述一个数据库需要实体、属性、关系等。实体就是表 中的一条记录,表是实体的结构,属性就是实体的特征,关系就是两个或者多个实体之间的 联系。假设有实体集 X 和 Y,他们之间映射关系如下:  一对一:X 中的一个实体最多与 Y 中的一个实体关联,并且 Y 中的一个实体最多与 X 中的一个实体关联。比如在 BBS 案例中,如果加上一个版块只能有一个用户担任 版主,一个版主只能担任一个版块的版主,那么用户和板块之间就是一对一关系。  一对多:X 中的一个实体可以与 Y 中的任意数量实体关联。Y 中的一个实体最多与 X 中的一个实体关联。一个主贴可以有多个跟帖,一个回帖只能是某个主贴的跟帖, 那么主贴与跟帖之间就是一对多的关联。  多对一:就是一对多的反例,如果 X 对 Y 是一对多,那么 Y 对 X 是多对一。  多对多:X 中的一个实体可以与 Y 中任意数量的实体关联,Y 中的一个实体也可以 与 X 中的任意数量的实体关联。一个用户如果允许担任多个版块的版主,而且一个 版块可以有多位版主,那么用户与板块之间就是多对多关系。 第 5 章 数据库设计 94 E-R 图中常用的符号如下表: 符号 说明 矩形表示数据库中的实体。 椭圆表示实体的属性。 菱形表示实体之间的关系。 直线用来连接实体和属性。 实体之间一对一连接关系(不同教材表达不同,本书约定箭头表示) 实体之间一对多连接关系(不同教材表达不同,本书约定箭头表示) 箭头指向的对象对应的是主表 实体之间的多对多连接关系(不同教材表达不同,本书约定直线表示) 表 1 E-R 图图示 比如,在 BBS 案例中,跟帖和主贴之间的关系跟随主从关系,如下图所示: 用户 用户积分 性别 用户等级 备注信息 注册日期 密码 状态 昵称 电子邮件 生日 版块 版块名称 版块格言 发帖数 点击率 版主 发帖 所在版块 跟帖 管理 发 表 属 亍 属亍 跟随 发表 帖子编号 状态 正文 发帖人 标题 发帖时间 点击率 回复数量 发帖表情 最后回复时间 标题 发帖人 帖子编号 正文 点击率 发帖时间 最后回复时间 发帖表情所在版块 图 2 BBS 数据库的 E-R 图 第 5 章数据库设计 95 概要设计中已经对客户的需求进行了转化,已经明确了实体、实体属性以及实体之间的 关系。并得出了实体关系图。那么如果才能把 E-R 图转化为数据表呢? 在把 E-R 图转换为表之前,需要对 E-R 图中的内容进行审核各实体是否符合规范,关于 数据的规范将在下一节讲解。把 E-R 图转换为表,需要如下步骤: 1. 首先要先确定 DBMS(比如:Oracle,SQL Server 等),因为不同的数据库对表以及 表中的列等要求存在差异。 2. 将各实体转换为对应的表,将各属性转换为各表对应的列。需要注意的是各列的数 据类型,在不同数据库中稍有不同。 3. 数据库库设计的关键是数据的完整性和规范性。第一期学习中我们已经学习了数据 库的完整性分为:实体完整性、引用完整性和域完整性等。因此接下来要确定的是 完整性:  实体完整性:就是确定一个表的主键。表中能够唯一标识一个实体的列,都可 以选做主键,这些列称为候选键。主键就是在候选键中产生的。在实际设计中, 一般都为各个实体添加一个整数类型的 ID 字段,作为表的主键。  引用完整性:就是实体与实体之间的关系,一般转换为表与表的引用,用外键 来完成引用完整性。  域完整性:确定表中的列是否允许为空、是否有默认值、是否需要 check 约束 等。 在把 E-R 图转换为表时,为了便于修改和扩展,一般先借助于各种工具完成本阶段的工 作。本书采用 Power Designer 12.5 完成。 第 5 章 数据库设计 96 图 3 BBS 数据库的表设计 我们发现,在 BBS 的 E-R 图中表示的属性和最后设计中的列有一些差别(比如版块中的 发帖数在表中没有该列),原因是因为,在把 E-R 图转换为表的过程中,还需要进行规范性 的设计,接下来学习什么是数据设计范式。 5. 数据库设计范式 在数据库设计中,不同的人对相同需求会设计出不同的数据库结构。糟糕的数据库设 计会使得数据库数据大量冗余、插入异常、更新异常、删除异常。数据库的设计范式(Normal Formate)是数据库设计所需要满足的规范,满足这些规范的数据库是简洁的、结构明晰 的,能够减少冗余,节省存储空间。 数据库的设计主要有 3 大设计范式,称之为第一范式(1NF)、第二范式(2NF)、第 三范式(3NF),它们逐步严格的。满足第二范式时必须首先满足第一范式,满足第三范 式时必须先满足第二范式。  第一范式(1NF) 第一范式要求单个表中每个列必须是原子列(每一列都是不可再分的最小数据单元)、 第 5 章数据库设计 97 列不存在重复属性、每个实体的属性也不存在多个数据项。  原子性 表 2 是学生的通讯录,分析其设计。 学号(PK) 住址 邮编 s100101 河南洛阳 471943 s100102 陕西西安 710000 表 2 列非原子性的表 表 2 的设计不符合第一范式,因为住址列可以拆分成省、市两列,不符合原子性的要 求。将表 2 改进为表 3,表 3 符合第一范式。 学号(PK) 省 市 邮编 s100101 河南 洛阳 471943 s100102 陕西 西安 710000  复合属性 表 4 是学生的课程和成绩信息,分析其设计。 1 1 SQL 76 2 ASP.NET 78 2 1 SQL 74 2 ASP.NET 85 表 4 具有重复属性的表 表 4 的设计不符合第一范式,因为表中存在重复属性,课程编号 1 与课程编号 2,课 程名称 1 与课程名称 2,课程成绩 1 与课程成绩 2。可以将表 4 改进为表 5,可以消除符 合属性。  多个数据项 将表 4 改进为表 5。 学号 课程编号 1 课程名称 1 课程成绩 1 课程编号 1 课程名称 1 课程成绩 1 第 5 章 数据库设计 98 学号 课程编号 课程名称 课程成绩 s100101 1,2 SQL,ASP.NET 76,78 s100102 1,2 SQL,ASP.NET 74,85 表 5 具有多个数据项的表 表 5 不符合第一范式,因为实体的课程名称和课程成绩存在两个数据项。对表 5 再次 改进为表 6。表 6 中没有重复属性,实体的属性也没有多个数据项,并且每一列都具有原 子性,因此符合第一范式。 学号 课程编号 课程名称 课程成绩 s100101 1 SQL 76 s100101 2 ASP.NET 78 s100102 1 SQL 74 s100103 2 ASP.NET 85 表 6 第一范式  第二范式 第二范式是在满足第一范式的基础上,所有的非主键列都依赖于主键列。 表 7 是订单表。 订单号 产品编号 订购日期 价格 1 1 2009-10-01 38 2 2 2009-10-02 54 表 7 非主键列不依赖于主键列的表 表 7 满足第一范式,但不符合第二范式。订单号是主键,订购日期和价格都与订单号 相关,但产品编号与订单号无关,应该从该表中删除,放入产品表中。这样订单表只描述 一件事情,即订单信息。 从第二范式的要求来看,每个非主键列都与主键相关。 第 5 章数据库设计 99  第三范式 第三范式是在满足第二范式的基础上,每一个非主键字段不传递依赖于主键。例如, 表中有 A、B、C 三列,A 为主键,B 和 C 为非主键。如果非主键 B 依赖于主键 A,非主键 C 依赖于非主键 B,那么就说非主键 B 传递依赖于主键 A。 表 8 是订单表。 订单编号 订购日期 价格 顾客编号 顾客姓名 1 2009-10-01 38 1 林冲 2 2009-10-02 54 2 鲁智深 表 8 非主键传递依赖 表 8 满足第二范式,但“顾客姓名”与“顾客编号”相关,“顾客编号”又和“订单 编号”相关,也就是说,“顾客编号”将其对“订单编号”的依赖传递给了“顾客姓名”, 因此不符合第三范式。为了满足第三范式,应该将“顾客姓名”列放入到顾客表中。 从第三范式的要求来看,每一个非主键列都与主键直接相关。 6. 数据库范式与性能的关系 通过三范式的讲解,我们发现,符合的范式越高,表的个数就越多,关系就越复杂。 由于表的数量多,在查询时连接表的个数也会变多,在添加、修改、删除数据时还要分别 操作不同的表,因此符合高范式设计的表会影响数据的操作性能。 范式和数据操作性能之间是矛盾的。在实际的数据库设计中既要考虑 3 范式,也要兼 顾数据的操作性能,在必要时可以考虑降低范式,允许适当存在一些冗余,而满足数据操 作性能的要求。 第 5 章 数据库设计 100 7. 本章总结  软件开发的步骤大体上分为:需求分析、概要设计、详细设计、代码编写、运行测 试、打包发行。  数据库的设计步骤分为需求分析、概念设计、逻辑设计、物理设计、运行验证。  概念设计就实际是把需求分析中的数据进行量化,形成程序员视图的过程。其中的 关键点有:标识对象、确定对象的属性、标识对象之间的关系、形成 E-R 图。  E-R 关系图式实体与关系图。  数据库设计第一范式是单个表中不存在重复属性、每个实体的属性也不存在多个数 据项。  数据库设计第二范式是在满足第一范式的基础上,每一个非主键字段完全依赖于主 键字段。  数据库设计第三范式是在满足第二范式的基础上,每一个非主键字段不传递依赖于 主键。  数据库设计范式与数据库性能是矛盾的。 第 5 章数据库设计 101 8. 本章练习 1. 描述数据库设计的步骤。 2. 什么是概念设计,它包括哪些过程。 3. E-R 图中有哪些常用的符号,分别表示什么对象。 4. 描述数据库三个范式的具体要求。 5. 下表符合第几范式,并说明原因。 学号(PK) 数学 语文 总成绩 S100101 85 86 171 S100102 80 70 150 第 5 章 数据库设计 102 章节知识结构图 PL/SQL 程序设计 103 第 6 章 PL/SQL 程序设计 主要内容  PL/SQL 数据类型  PL/SQL 条件和循环控制  劢态执行 SQL  PL/SQL 中的异常处理 第 6 章 PL/SQL 程序设计 104 1. PL/SQL 简介 从第一学期到现在,在数据库上一直使用单一的 SQL 语句进行数据操作,没有流程控制, 无法开发复杂的应用。Oracle PL/SQL 语言(Procedural Language/SQL)是结合了结构化查询 与 Oracle 自身过程控制为一体的强大语言,PL/SQL 不但支持更多的数据类型,拥有自身的 变量声明、赋值语句,而且还有条件、循环等流程控制语句。过程控制结构与 SQL 数据处理 能力无缝的结合形成了强大的编程语言,可以创建过程和函数以及程序包。 PL/SQL 是一种块结构的语言,它将一组语句放在一个块中,一次性发送给服务器,PL/SQL 引擎分析收到 PL/SQL 语句块中的内容进行,把其中的过程控制语句由 PL/SQL 引擎自身去执 行,把 PL/SQL 块中的 SQL 语句交给服务器的 SQL 语句执行器执行。如图所示: 图 1 PL/SQL 体系结构 PL/SQL 块发送给服务器后,先被编译然后执行,对于有名称的 PL/SQL 块(比如下一章 讲解的子程序)可以单独编译,永久的存储在数据库中,随时准备执行。PL/SQL 的优点还 有:  支持 SQL SQL 是访问数据库的标准语言,通过 SQL 命令,用户可以操纵数据库中的数据。PL/SQL 支持所有的 SQL 数据操纵命令、游标控制命令、事务控制命令、SQL 函数、运算符和伪列。 同时 PL/SQL 和 SQL 语言紧密集成,PL/SQL 支持所有的 SQL 数据类型和 NULL 值。  支持面向对象编程 PL/SQL 支持面向对象的编程,在 PL/SQL 中可以创建类型,可以对类型进行继承,可以 在子程序中重载方法等。  更好的性能 SQL 是非过程语言,只能一条一条执行,而 PL/SQL 把一个 PL/SQL 块统一进行编译后执 行,同时还可以把编译好的 PL/SQL 块存储起来,以备重用,减少了应用程序和服务器之间 的通信时间,PL/SQL 是快速而高效的。 第 6 章 PL/SQL 程序设计 105  可移植性 使用 PL/SQL 编写的应用程序,可以移植到任何操作系统平台上的 Oracle 服务器,同时 还可以编写可移植程序库,在不同环境中重用。  安全性 可以通过存储过程对客户机和服务器之间的应用程序逻辑进行分隔,这样可以限制对 Oracle 数据库的访问,数据库还可以授权和撤销其他用户访问的能力。 2. PL/SQL 块 PL/SQL 是一种块结构的语言,一个 PL/SQL 程序包含了一个或者多个逻辑块,逻辑块中 可以声明变量,变量在使用之前必须先声明。除了正常的执行程序外,PL/SQL 还提供了专 门的异常处理部分进行异常处理。每个逻辑块分为三个部分,语法是: PL/SQL 块的语法 [DECLARE --declaration statements] ① BEGIN --executable statements ② [EXCEPTION --exception statements] ③ END; 解析: ① 声明部分:声明部分包含了变量和常量的定义。这个部分由关键字 DECLARE 开始, 如果不声明变量或者常量,可以省略这部分。 ② 执行部分:执行部分是 PL/SQL 块的指令部分,由关键字 BEGIN 开始,关键字 END 结尾。所有的可执行 PL/SQL 语句都放在这一部分,该部分执行命令并操作变量。 其他的 PL/SQL 块可以作为子块嵌套在该部分。PL/SQL 块的执行部分是必选的。注 意 END 关键字后面用分号结尾。 ③ 异常处理部分:该部分是可选的,该部分用 EXCEPTION 关键字把可执行部分分成两 个小部分,之前的程序是正常运行的程序,一旦出现异常就跳转到异常部分执行。 PL/SQL 是一种编程语言,与 Java 和 C#一样,除了有自身独有的数据类型、变量声明和 赋值以及流程控制语句外,PL/SQL 还有自身的语言特性: PL/SQL 对大小写不敏感,为了良好的程序风格,开发团队都会选择一个合适的编码标准。 第 6 章 PL/SQL 程序设计 106 比如有的团队规定:关键字全部大写,其余的部分小写。 PL/SQL 块中的每一条语句都必须以分号结束,SQL 语句可以是多行的,但分号表示该语 句结束。一行中可以有多条 SQL 语句,他们之间以分号分隔,但是不推荐一行中写 多条语句。 PL/SQL 中的特殊符号说明: 类型 符号 说明 赋值运算符 := Java 和 C#中都是等号,PL/SQL 的赋值是:= 特殊字符 || 字符串连接操作符。 -- PL/SQL 中的单行注释。 /*,*/ PL/SQL 中的多行注释,多行注释不能嵌套。 <<,>> 标签分隔符。只为了标识程序特殊位置。 .. 范围操作符,比如:1..5 标识从1到5 算术运算符 +,-,*,/ 基本算术运算符。 ** 求幂操作,比如:3**2=9 关系运算符 >,<,>=,<=,= 基本关系运算符,=表示相等关系,不是赋值。 <>,!= 不等关系。 逻辑运算符 AND,OR,NOT 逻辑运算符。 表 1 PL/SQL 中的特殊符号和运算符  变量声明 PL/SQL 支持 SQL 中的数据类型,PL/SQL 中正常支持 NUMBER,VARCHAR2,DATE 等 Oracle SQL 数据类型。声明变量必须指明变量的数据类型,也可以声明变量时对变量初始化,变量 声明必须在声明部分。声明变量的语法是: 变量名 数据类型[ :=初始值] 说明: 数据类型如果需要长度,可以用括号指明长度,比如:varchar2(20)。 代码演示:声明变量 SQL> DECLARE 2 sname VARCHAR2(20) :='jerry'; ① 3 BEGIN 第 6 章 PL/SQL 程序设计 107 4 sname:=sname||' and tom'; ② 5 dbms_output.put_line(sname); ③ 6 END; 7 /jerry PL/SQL procedure successfully completed 代码解析: ① 声明一个变量 sname,初始化值是“jerry”。字符串用单引号,如果字符串中出现 单引号可以使用两个单引号(’’)代替。 ② 对变量 sname 重新赋值,赋值运算符是“:=”。 ③ dbms_output.put_line 是输出语句,可以把一个变量的值输出,在 SQL*Plus 中输出 数据时,可能没有结果显示,可以使用命令:set serveroutput on 设置输出到 SQL*Plus 控制台上。 对变量赋值还可以使用 SELECT…INTO 语句从数据库中查询数据对变量进行赋值。但是 查询的结果只能是一行记录,不能是零行或者多行记录。 代码演示:变量赋值 SQL> DECLARE 2 sname VARCHAR2(20) DEFAULT 'jerry'; ① 3 BEGIN 4 SELECT ename INTO sname FROM emp WHERE empno=7934; ② 5 dbms_output.put_line(sname); 6 END; 7 / MILLER PL/SQL procedure successfully completed 代码解析: ① 变量初始化时,可以使用 DEFAULT 关键字对变量进行初始化。 ② 使用 select…into 语句对变量 sname 赋值,要求查询的结果必须是一行,不能是多行 或者没有记录。  声明常量 常量在声明时赋予初值,并且在运行时不允许重新赋值。使用 CONSTANT 关键字声明常 第 6 章 PL/SQL 程序设计 108 量。 代码演示:声明常量 SQL> DECLARE 2 pi CONSTANT number :=3.14; --圆周率长值 ① 3 r number DEFAULT 3; --圆的半径默认值3 4 area number; --面积。 5 BEGIN 6 area:=pi*r*r; --计算面积 7 dbms_output.put_line(area); --输出圆的面积 8 END; 9 / 28.26 PL/SQL procedure successfully completed 代码解析: ① 声明常量时使用关键字 CONSTANT,常量初值可以使用赋值运算符(:=)赋值,也 可以使用 DEFAULT 关键字赋值。 在 SQL*Plus 中还可以声明 Session(会话,也就是一个客户端从连接到退出的过程称为 当前用户的会话。)全局级变量,该变量在整个会话过程中均起作用,类似的这种变量称为 宿主变量。宿主变量在 PL/SQL 引用时要用“:变量名”引用。 代码演示:宿主变量 SQL> var emp_name varchar2(30); ① SQL> BEGIN 2 SELECT ename INTO :emp_name FROM emp WHERE empno=7499; ② 3 END; 4 / PL/SQL procedure successfully completed emp_name --------- ALLEN 第 6 章 PL/SQL 程序设计 109 SQL> print emp_name; ③ emp_name --------- ALLEN 代码解析: ① 可以使用 var 声明宿主变量。 ② PL/SQL 中访问宿主变量时要在变量前加“:”。 ③ 在 SQL*Plus 中,使用 print 可以输出变量中的结果。 3. PL/SQL 数据类型 前面在建表时,学习过 Oracle SQL 的数据类型,PL/SQL 不但支持这些数据类型,还具备 自身的数据类型。PL/SQL 的数据类型包括标量数据类型,引用数据类型和存储文本、图像、 视频、声音等非结构化的大数据类型(LOB 数据类型)等。下面列举一些常用的类型。  标量数据类型 标量数据类型的变量只有一个值,且内部没有分量。标量数据类型包括数字型,字符型, 日期型和布尔型。这些类型有的是 Oracle SQL 中定义的数据类型,有的是 PL/SQL 自身附加 的数据类型。字符型和数字型又有子类型,子类型只与限定的范围有关,比如 NUMBER 类 型可以表示整数,也可以表示小数,而其子类型 POSITIVE 只表示正整数。 类型 说明 VARCHAR2(长度) 可变长度字符串,Oracle SQL 定义的数据类型,在 PL/SQL 中使用时最 长 32767 字节。在 PL/SQL 中使用没有默认长度,因此必须指定。 NUMBER(精度,小数) Oracle SQL 定义的数据类型,见第二章。 DATE Oracle SQL 定义的日期类型,见第二章。 TIMESTAMP Oracle SQL 定义的日期类型,见第二章。 CHAR(长度) Oracle SQL 定义的日期类型,固定长度字符,最长 32767 字节,默认 长度是 1,如果内容不够用空格代替。 LONG Oracle SQL 定义的数据类型,变长字符串基本类型,最长 32760 字节。 在 Oracle SQL 中最长 2147483647 字节。 第 6 章 PL/SQL 程序设计 110 BOOLEAN PL/SQL 附加的数据类型,逻辑值为 TRUE、FALSE、NULL BINARY_INTEGER PL/SQL 附加的数据类型,介于-231 和 231 之间的整数。 PLS_INTEGER PL/SQL 附加的数 据类 型,介 于-231 和 231 之间的 整数。 类似于 BINARY_INTEGER,只是 PLS_INTEGER 值上的运行速度更快。 NATURAL PL/SQL 附加的数据类型,BINARY_INTEGER 子类型,表示从 0 开始的 自然数。 NATURALN 与 NATURAL 一样,只是要求 NATURALN 类型变量值不能为 NULL。 POSITIVE PL/SQL 附加的数据类型,BINARY_INTEGER 子类型,正整数。 POSITIVEN 与 POSITIVE 一样,只是要求 POSITIVE 的变量值不能为 NULL。 REAL Oracle SQL 定义的数据类型,18 位精度的浮点数 INT,INTEGER,SMALLINT Oracle SQL 定义的数据类型,NUMBERDE 的子类型,38 位精度整数。 SIGNTYPE PL/SQL 附加的数据类型,BINARY_INTEGER 子类型。值有:1、-1、0。 STRING 与 VARCHAR2 相同。 表 2 PL/SQL 中标量数据类型。  属性数据类型 当声明一个变量的值是数据库中的一行或者是数据库中某列时,可以直接使用属性类型 来声明。Oracle 中存在两种属性类型:%TYPE 和%ROWTYPE。  % ROWTYPE 引用数据库表中的一行作为数据类型,即 RECORD 类型(记录类型),是 PL/SQL 附加的 数据类型。表示一条记录,就相当于 Java 中的一个对象。可以使用“.”来访问记录中的属 性。 代码演示:%ROWTYPE 应用 SQL> DECLARE 2 myemp EMP%ROWTYPE; ① 3 BEGIN 4 SELECT * INTO myemp FROM emp WHERE empno=7934; ② 5 dbms_output.put_line(myemp.ename); ③ 6 END; 7 / MILLER PL/SQL procedure successfully completed 第 6 章 PL/SQL 程序设计 111 代码解析: ① 声明一个 myemp 对象,该对象表示 EMP 表中的一行。 ② 从 EMP 表中查询一条记录放入 myemp 对象中。 ③ 访问该对象的属性可以使用“.”  %TYPE 引用某个变量或者数据库的列的类型作为某变量的数据类型。 代码演示:%TYPE 应用 SQL> DECLARE 2 sal emp.sal%TYPE; ① 3 mysal number(4):=3000; 4 totalsal mysal%TYPE; ② 5 BEGIN 6 SELECT SAL INTO sal FROM emp WHERE empno=7934; 7 totalsal:=sal+mysal; 8 dbms_output.put_line(totalsal); 9 END; 10 / 4300 PL/SQL procedure successfully completed 代码解析: ① 定义变量 sal 为 emp 表中 sal 列的类型。 ② 定义 totalsal 是变量 mysal 的类型。 %TYPE 可以引用表中的某列作的类型为变量的数据类型,也可以引用某变量的类型作为 新变量的数据类型。 4. PL/SQL 条件控制和循环控制 PL/SQL 程序可通过条件或循环结构来控制命令执行的流程。PL/SQL 提供了丰富的流程 控制语句,与 Java 一样也有三种控制结构:  顺序结构 第 6 章 PL/SQL 程序设计 112  条件结构  循环结构  条件控制 Java 中的条件控制使用关键字 if 和 switch。PL/SQL 中关于条件控制的关键字有 IF-THEN、 IF-THEN-ELSE、IF-THEN-ELSIF 和多分枝条件 CASE。  IF-THEN 该结构先判断一个条件是否为 TRUE,条件成立则执行对应的语句块,与 Java 中的 if 语 句很相似,具体语法是: Java 中 if 语法 PL/SQL 中 IF 语法 if (条件){ //条件结构体 } IF 条件 THEN --条件结构体 END IF; 表 3 PL/SQL 中条件语法 说明: ① 用 IF 关键字开始,END IF 关键字结束,注意 END IF 后面有一个分号。 ② 条件部分可以不使用括号,但是必须以关键字 THEN 来标识条件结束,如果条件成 立,则执行 THEN 后到对应 END IF 之间的语句块内容。如果条件不成立,则不执行 条件语句块的内容。 ③ Java 结构用一对大括号来包含条件结构体的内容。PL/SQL 中关键字 THEN 到 END IF 之间的内容是条件结构体内容。 ④ 条件可以使用关系运算符合逻辑运算符。 案例:查询 JAMES 的工资,如果大于 900 元,则发奖金 800 元。 代码演示:IF-THEN 应用 DECLARE newSal emp.sal % TYPE; BEGIN SELECT sal INTO newSal FROM emp WHERE ename='JAMES'; IF newSal>900 THEN ① 第 6 章 PL/SQL 程序设计 113 UPDATE emp SET comm=800 WHERE ename='JAMES'; END IF; COMMIT ; ② END; 代码解析: ① 先判断条件,如果条件为 TRUE,则执行条件结构体内部的内容。 ② 在 PL/SQL 块中可以使用事务控制语句,该 COMMIT 同时也能把 PL/SQL 块外没有提 交的数据一并提交,使用时需要注意。  IF-THEN-ELSE 语法: Java 中 if 语法 PL/SQL 中 IF 语法 if (条件){ //条件成立结构体 } else{ //条件不成立结构体 } IF 条件 THEN --条件成立结构体 ELSE --条件不成立结构体 END IF; 表 4 PL/SQL 中条件语法 说明: 把ELSE 与IF-THEN连在一起使用,如果IF条件不成立则执行就会执行ELSE 部分的语句。 案例:查询 JAMES 的工资,如果大于 900 元,则发奖金 800 元,否则发奖金 400 元。 代码演示:IF-THEN-ELSE 应用 DECLARE newSal emp.sal % TYPE; BEGIN SELECT sal INTO newSal FROM emp WHERE ename='JAMES'; 第 6 章 PL/SQL 程序设计 114 IF newSal>900 THEN UPDATE emp SET comm=800 WHERE ename='JAMES'; ELSE UPDATE emp SET comm=400 WHERE ename='JAMES'; END IF; END;  IF-THEN-ELSIF 多重条件判断语句。 Java 中 if 语法 PL/SQL 中 IF 语法 if (条件 2){ //条件成立结构体 } else if(条件 2){ //条件不成立结构体 } else{ //以上条件都不成立结构体 } IF 条件 1 THEN --条件 1 成立结构体 ELSIF 条件 2 THEN --条件 2 成立结构体 ELSE --以上条件都不成立结构体 END IF; 表 5 PL/SQL 中多分枝条件语法 说明: PL/SQL 中的再次条件判断中使用关键字 ELSIF,而 Java 使用 else if。 案例:查询 JAMES 的工资,如果大于 1500 元,则发放奖金 1000 元,如果工作大于 900 元,则发奖金 800 元,否则发奖金 400 元。 代码演示:IF-THEN-ELSIF 应用 DECLARE newSal emp.sal % TYPE; BEGIN 第 6 章 PL/SQL 程序设计 115 SELECT sal INTO newSal FROM emp WHERE ename='JAMES'; IF newSal>1500 THEN UPDATE emp SET comm=1000 WHERE ename='JAMES'; ELSIF newSal>900 THEN UPDATE emp SET comm=800 WHERE ename='JAMES'; ELSE UPDATE emp SET comm=400 WHERE ename='JAMES'; END IF; END;  CASE CASE 是一种选择结构的控制语句,可以根据条件从多个执行分支中选择相应的执行动 作。也可以作为表达式使用,返回一个值。类似于 Java 中的 switch 语句。语法是: CASE [selector] WHEN 表达式 1 THEN 语句序列 1; WHEN 表达式 2 THEN 语句序列 2; WHEN 表达式 3 THEN 语句序列 3; …… [ELSE 语句序列 N]; END CASE; 说明: 如果存在选择器 selector,选择器 selector 与 WHEN 后面的表达式匹配,匹配成功就执 行 THEN 后面的语句。如果所有表达式都与 selector 不匹配,则执行 ELSE 后面的语句。 案例:输入一个字母 A、B、C 分别输出对应的级别信息。 第 6 章 PL/SQL 程序设计 116 代码演示:CASE 中存在 selector,不返回值 DECLARE v_grade CHAR(1):=UPPER('&p_grade'); ① BEGIN CASE v_grade ② WHEN 'A' THEN dbms_output.put_line('Excellent'); WHEN 'B' THEN dbms_output.put_line('Very Good'); WHEN 'C' THEN dbms_output.put_line('Good'); ELSE dbms_output.put_line('No such grade'); END CASE; END; 代码解析: ① & grade 表示在运行时由键盘输入字符串到 grade 变量中。 ② v_grade 分别与 WHEN 后面的值匹配,如果成功就执行 WHEN 后的程序序列。 CASE 语句还可以作为表达式使用,返回一个值。比如:上面的案例中 代码演示:CASE 中存在 selector,作为表达式使用 DECLARE v_grade CHAR(1):=UPPER('&grade'); p_grade VARCHAR(20) ; BEGIN p_grade := ① CASE v_grade WHEN 'A' THEN 'Excellent' WHEN 'B' THEN 'Very Good' WHEN 'C' THEN 'Good' 第 6 章 PL/SQL 程序设计 117 ELSE 'No such grade' END; dbms_output.put_line('Grade:' ||v_grade||',the result is '||p_grade); END; 代码解析: ① CASE 语句可以返回一个结果给变量 p_grade PL/SQL 还提供了搜索 CASE 语句。也就是说,不使用 CASE 中的选择器,直接在 WHEN 后面判断条件,第一个条件为真时,执行对应 THEN 后面的语句序列。 代码演示:搜索 CASE DECLARE v_grade CHAR(1):=UPPER('&grade'); p_grade VARCHAR(20) ; BEGIN p_grade := CASE WHEN v_grade='A' THEN 'Excellent' WHEN v_grade='B' THEN 'Very Good' WHEN v_grade='C' THEN 'Good' ELSE 'No such grade' END; dbms_output.put_line('Grade:' ||v_grade||',the result is '||p_grade); END;  循环结构 PL/SQL 提供了丰富的循环结构来重复执行一些列语句。Oracle 提供的循环类型有: 1. 无条件循环 LOOP-END LOOP 语句 第 6 章 PL/SQL 程序设计 118 2. WHILE 循环语句 3. FOR 循环语句 在上面的三类循环中 EXIT 用来强制结束循环,相当于 Java 循环中的 break。  LOOP 循环 LOOP 循环是最简单的循环,也称为无限循环,LOOP 和 END LOOP 是关键字,语法是: LOOP --循环体 END LOOP; 说明: 1. 循环体在 LOOP 和 END LOOP 之间,在每个 LOOP 循环体中,首先执行循环体中的语 句序列,执行完后再重新开始执行。 2. 在 LOOP 循环中可以使用 EXIT 或者[EXIT WHEN 条件]的形式终止循环。否则该循环 就是死循环。 案例:执行 1+2+3+…+100 的值 代码演示:LOOP 循环 DECLARE counter number(3):=0; sumResult number:=0; BEGIN LOOP counter := counter+1; sumResult := sumResult+counter; IF counter>=100 THEN ① EXIT; END IF; -- EXIT WHEN counter>=100; ② END LOOP; dbms_output.put_line('result is :'||to_char(sumResult)); END; 第 6 章 PL/SQL 程序设计 119 代码解析: ① LOOP 循环中可以使用 IF 结构嵌套 EXIT 关键字退出循环 ② 注释行,该行可以代替①中的循环结构,WHEN 后面的条件成立时跳出循环。  WHILE 循环 与 Java 中的 while 循环很类似。先判断条件,条件成立再执行循环体。 Java 中 while 语法 PL/SQL 中 WHILE 语法 while (条件){ //循环体体 } WHILE 条件 LOOP --循环体 END LOOP; 表 5 PL/SQL 中 LOOP 语法 代码演示:WHILE 循环 DECLARE counter number(3):=0; sumResult number:=0; BEGIN WHILE counter<100 LOOP counter := counter+1; sumResult := sumResult+counter; END LOOP; dbms_output.put_line('result is :'||sumResult); END;  FOR 循环 FOR 循环需要预先确定的循环次数,可通过给循环变量指定下限和上限来确定循环运行 的次数,然后循环变量在每次循环中递增(或者递减)。FOR 循环的语法是: FOR 循环变量 IN [REVERSE] 循环下限..循环上限 LOOP --循环体 END LOOP; 说明: 循环变量:该变量的值每次循环根据上下限的 REVERSE 关键字进行加 1 或者减 1。 第 6 章 PL/SQL 程序设计 120 REVERSE:指明循环从上限向下限依次循环。 代码演示:FOR 循环 DECLARE counter number(3):=0; sumResult number:=0; BEGIN FOR counter IN 1..100 LOOP sumResult := sumResult+counter; END LOOP; dbms_output.put_line('result is :'||sumResult); END;  顺序结构 在程序顺序结构中有两个特殊的语句。GOTO 和 NULL  GOTO 语句 GOTO 语句将无条件的跳转到标签指定的语句去执行。标签是用双尖括号括起来的标示 符,在 PL/SQL 块中必须具有唯一的名称,标签后必须紧跟可执行语句或者 PL/SQL 块。 GOTO 不能跳转到 IF 语句、CASE 语句、LOOP 语句、或者子块中。  NULL 语句 NULL 语句什么都不做,只是将控制权转到下一行语句。NULL 语句是可执行语句。NULL 语句在 IF 或者其他语句语法要求至少需要一条可执行语句,但又不需要具体操作的地 方。比如 GOTO 的目标地方不需要执行任何语句时。 代码演示:GOTO 和 NULL DECLARE sumsal emp.sal%TYPE; BEGIN SELECT SUM(sal) INTO sumsal FROM EMP; IF sumsal>20000 THEN GOTO first_label; ① 第 6 章 PL/SQL 程序设计 121 ELSE GOTO second_label; ② END IF; <> ③ dbms_output.put_line('ABOVE 20000:' || sumsal); <> ④ NULL; END; 代码解析: ① 跳转到程序 first_label 位置,就是②的位置,first_label 是一个标签,用两个尖括号 包含。 ② 无条件跳转到 sedond_label 位置,就是④的位置。④处不执行任何内容,因此是一 个 NULL 语句。 与 Java 一样,在 PL/SQL 中,各种循环之间可以相互嵌套。 5. PL/SQL 中劢态执行 SQL 语句 在 PL/SQL 程序开发中,可以使用 DML 语句和事务控制语句,但是还有很多语句(比如 DDL 语句)不能直接在 PL/SQL 中执行。这些语句可以使用动态 SQL 来实现。 PL/SQL 块先编译然后再执行,动态 SQL 语句在编译时不能确定,只有在程序执行时把 SQL 语句作为字符串的形式由动态 SQL 命令来执行。在编译阶段 SQL 语句作为字符串存在, 程序不会对字符串中的内容进行编译,在运行阶段再对字符串中的 SQL 语句进行编译和执行, 动态 SQL 的语法是: EXECUTE IMMEDIATE 动态语句字符串 [INTO 变量列表] [USING 参数列表] 说明: 如果动态语句是 SELECT 语句,可以把查询的结果保存到 INTO 后面的变量中。如果动态 语句中存在参数,USING 为语句中的参数传值。 动态 SQL 中的参数格式是:[:参数名],参数在运行时需要使用 USING 传值。 第 6 章 PL/SQL 程序设计 122 代码演示:劢态 SQL DECLARE sql_stmt VARCHAR2(200); --动态SQL语句 emp_id NUMBER(4) := 7566; salary NUMBER(7,2); dept_id NUMBER(2) := 90; dept_name VARCHAR2(14) := 'PERSONNEL'; location VARCHAR2(13) := 'DALLAS'; emp_rec emp%ROWTYPE; BEGIN --无子句的execute immediate EXECUTE IMMEDIATE 'CREATE TABLE bonus1 (id NUMBER, amt NUMBER)'; ① ----using子句的execute immediate sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)'; EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location; ② ----into子句的execute immediate sql_stmt := 'SELECT * FROM emp WHERE empno = :id'; EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id; ③ ----returning into子句的execute immediate sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1 RETURNING sal INTO :2'; EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary; ④ EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num' USING dept_id; ⑤ END; 代码解析: ① 动态执行一个完整的 SQL 语句。 ② SQL 语句中存在 3 个参数分别标识为:[:1、:2、:3],因此需要用 USING 关键字对三 个参数分别赋值。 ③ 对动态查询语句可以使用 INTO 子句把查询的结果保存到一个变量中,要求该结果 只能是单行。 ④ 在 Oracle 的 insert,update,delete 语句都可以使用 RETURNING 子句把操作影响的 行中的数据返回,对 SQL 语句中存在 RETURNING 子句时,在动态执行时可以使用 第 6 章 PL/SQL 程序设计 123 RETURNING INTO 来接收。 ⑤ 动态执行参数中可以是:[:数字]也可以是[:字符串]。 6. PL/SQL 的异常处理 在程序运行时出现的错误,称为异常。发生异常后,语句将停止执行,PL/SQL 引擎立 即将控制权转到 PL/SQL 块的异常处理部分。异常处理机制简化了代码中的错误检测。PL/SQL 中任何异常出现时,每一个异常都对应一个异常码和异常信息。比如: 图 1 PL/SQL 中的异常  预定义异常 为了 Oracle 开发和维护的方便,在 Oracle 异常中,为常见的异常码定义了对应的异常 名称,称为预定义异常,常见的预定义异常有: 异常名称 异常码 描述 DUP_VAL_ON_INDEX ORA-00001 试图向唯一索引列插入重复值 INVALID_CURSOR ORA-01001 试图进行非法游标操作。 INVALID_NUMBER ORA-01722 试图将字符串转换为数字 NO_DATA_FOUND ORA-01403 SELECT INTO 语句中没有返回任何记录。 TOO_MANY_ROWS ORA-01422 SELECT INTO 语句中返回多于 1 条记录。 ZERO_DIVIDE ORA-01476 试图用 0 作为除数。 CURSOR_ALREADY_OPEN ORA-06511 试图打开一个已经打开的游标 表 6 PL/SQL 中预定义异常 PL/SQL 中用 EXCEPTION 关键字开始异常处理。具体语法是: 第 6 章 PL/SQL 程序设计 124 BEGIN --可执行部分 EXCEPTION -- 异常处理开始 WHEN 异常名 1 THEN --对应异常处理 WHEN 异常名 2 THEN --对应异常处理 …… WHEN OTHERS THEN --其他异常处理 END; 说明: 异常发生时,进入异常处理部分,具体的异常与若干个 WHEN 子句中指明的异常名匹 配,匹配成功就进入对应的异常处理部分,如果对应不成功,则进入 OTHERS 进行处理。 代码演示:异常处理 SQL> DECLARE 2 newSal emp.sal % TYPE; 3 BEGIN 4 SELECT sal INTO newSal FROM emp; 5 EXCEPTION 6 WHEN TOO_MANY_ROWS THEN 7 dbms_output.put_line('返回的记录太多了'); 8 WHEN OTHERS THEN 9 dbms_output.put_line('未知异常'); 10 END; 11 / 返回的记录太多了 PL/SQL procedure successfully completed  自定义异常。 除了预定义异常外,用户还可以在开发中自定义异常,自定义异常可以让用户采用与 PL/SQL 引擎处理错误相同的方式进行处理,用户自定义异常的两个关键点: 第 6 章 PL/SQL 程序设计 125  异常定义:在 PL/SQL 块的声明部分采用 EXCEPTION 关键字声明异常,定义方法与 定义变量相同。比如声明一个 myexception 异常方法是: myexception EXCEPTION;  异常引发:在程序可执行区域,使用 RAISE 关键字进行引发。比如引发 myexception 方法是: RAISE myexception; 代码演示:自定义异常 SQL> DECLARE 2 sal emp.sal%TYPE; 3 myexp EXCEPTION; ① 4 BEGIN 5 SELECT sal INTO sal FROM emp WHERE ename='JAMES'; 6 IF sal<5000 THEN 7 RAISE myexp; ② 8 END IF; 9 EXCEPTION 10 WHEN NO_DATA_FOUND THEN 11 dbms_output.put_line('NO RECORDSET FIND!'); 12 WHEN MYEXP THEN ③ 13 dbms_output.put_line('SAL IS TO LESS!'); 14 END; 15 / SAL IS TO LESS! PL/SQL procedure successfully completed 代码解析: ① 用 EXCEPTION 定义一个异常变量 myexp ② 在一定条件下用 RAISE 引发异常 myexp ③ 在异常处理部分,捕获异常,如果不处理异常,该异常就抛给程序执行者。  引发应用程序异常 第 6 章 PL/SQL 程序设计 126 在 Oracle 开发中,遇到的系统异常都有对应的异常码,在应用系统开发中,用户自定 义的异常也可以指定一个异常码和异常信息,Oracle 系统为用户预留了自定义异常码,其范 围介于-20000 到-20999 之间的负整数。引发应用程序异常的语法是: RAISE_APPLICATION_ERROR(异常码,异常信息) 代码演示:引发应用系统异常 SQL> DECLARE 2 sal emp.sal%TYPE; 3 myexp EXCEPTION; 4 BEGIN 5 SELECT sal INTO sal FROM emp WHERE ename='JAMES'; 6 IF sal<5000 THEN 7 RAISE myexp; 8 END IF; 9 EXCEPTION 10 WHEN NO_DATA_FOUND THEN 11 dbms_output.put_line('NO RECORDSET FIND!'); 12 WHEN MYEXP THEN 13 RAISE_APPLICATION_ERROR(-20001,'SAL IS TO LESS!'); ① 14 END; 15 / ORA-20001: SAL IS TO LESS! ② ORA-06512: 在 line 14 代码解析: ① 引发应用系统异常,指明异常码和异常信息。 ② 在控制台上显示异常码和异常信息。 如果要处理未命名的内部异常,必须使用 OTHERS 异常处理器。也可以利用 PRAGMA EXCEPTION_INIT 把一个异常码与异常名绑定。 PRAGMA 由编译器控制,PRAGMA 在编译时处理,而不是在运行时处理。EXCEPTION_INIT 告诉编译器将异常名与 ORACLE 错误码绑定起来,这样可以通过异常名引用任意的内部异常, 并且可以通过异常名为异常编写适当的异常处理器。PRAGMA EXCEPTION_INIT 的语法是: PRAGMA EXCEPTION_INIT(异常名,异常码) 第 6 章 PL/SQL 程序设计 127 这里的异常码可以是用户自定义的异常码,也可以是 Oracle 系统的异常码。 代码演示:PRAGMA EXCEPTION_INIT 异常 <> DECLARE null_salary EXCEPTION; PRAGMA EXCEPTION_INIT(null_salary, -20101); ① BEGIN <> ② DECLARE curr_comm NUMBER; BEGIN SELECT comm INTO curr_comm FROM emp WHERE empno = &empno; IF curr_comm IS NULL THEN RAISE_APPLICATION_ERROR(-20101, 'Salary is missing'); ③ ELSE dbms_output.put_line('有津贴'); END IF; END; EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('没有发现行'); WHEN null_salary THEN dbms_output.put_line('津贴未知'); ④ WHEN OTHERS THEN dbms_output.put_line('未知异常'); END; 代码解析: ① 把异常名称 null_salary 与异常码-20101 关联,该语句由于是预编译语句,必须放在 声明部分。也就是说-20101 的异常名称就是 null_salary。 ② 嵌套 PL/SQL 语句块 ③ 在内部 PL/SQL 语句块中引发应用系统异常-20101。 ④ 在外部的 PL/SQL 语句块中就可以用异常名 null_salary 进行捕获。 第 6 章 PL/SQL 程序设计 128 7. 本章总结  PL/SQL 是一种块结构的语言,它将一组语句放在一个块中,一次性发送给服务器, PL/SQL 引擎把接收到 PL/SQL 语句块中的内容进行分析,把其中的过程控制语句由 PL/SQL 引擎自身去执行,把 PL/SQL 语句块中的 SQL 语句交给服务器的 SQL 语句执 行器执行。  PL/SQL 的数据类型包括标量数据类型,引用数据类型和存储文本、图像、视频、 声音等非结构化得大数据类型(LOB 数据类型)等。  Oracle 中存在两种属性类型:%TYPE 和%ROWTYPE。  PL/SQL 程序可通过控制结构来控制命令执行的流程。PL/SQL 中提供三种程序结构: 顺序结构、条件结构和循环结构。  在 PL/SQL 程序开发中,可以使用 DML 语句和事务控制语句,还可以动态执行 SQL 语句,动态执行 SQL 语句的命令是:EXECUTE IMMEDIATE。  在程序运行时出现的错误,称为异常。发生异常后,语句将停止执行,PL/SQL 引 擎立即将控制权转到 PL/SQL 块的异常处理部分。PL/SQL 中任何异常出现时,每一 个异常都对应一个异常码和异常信息。 第 6 章 PL/SQL 程序设计 129 8. 本章练习 1. PL/SQL 有哪些优点? 2. 请描述 PL/SQL 块的结构。 3. 请描述多分枝判断 CASE 的用法。 4. PL/SQL 中有哪些循环控制语句?如何使用它们? 5. 如何执行动态 SQL 语句? 6. 如何自定义异常,如何把自定义异常与异常码绑定? 7. 编写一个程序,输入一个整数,使用循环结构将该数字左右翻转,输出翻转后的结 果。 8. 编写一个程序,在 EMP 表查找姓名为 ALLEN 员工,并获取 TOO_MANY_ROWS 和 NO_DATA_FOUND 异常。 9. 编写一个过程为班级每位同学创建一个用户,用户名和密码都是:“班级号+学号”, 并为每位用户授权:CONNECT 和 RESOURCE。 第 6 章 PL/SQL 程序设计 130 10. 章节知识结构图 子程序和程序包 131 第 7 章 子程序和程序包 主要内容  过程定义和调用  函数定义和调用  子程序参数模式 IN、OUT、IN OUT  自主事务  程序包 第 7 章 子程序和程序包 132 1. 子程序 子程序是已经命名的 PL/SQL 块,他们预编译后存储在数据库中,可以随时从任何数据 库客户端和应用程序中调用它们。命名的 PL/SQL 程序包括过程和函数。程序可以通过过程 名和函数名对他们进行调用,相当于 Java 中的方法,过程相当于就是没有返回值的方法。 函数相当于有返回值的方法,过程和函数都可以有自身的参数,在调用时为其传值。 相对以前学习过的 PL/SQL 块就是匿名 PL/SQL 块,子程序与匿名 PL/SQL 块一样,具有 声明部分、可执行部分、异常处理部分。声明部分包含类型、游标(下一章讲解)、常量、 变量、异常的声明,这些声明项都是局部的,在子程序运行结束将不复存在。可执行部分包 括变量操作、流程控制操作、Oracle SQL 的语句执行操作,以及其他子程序的调用。异常处 理部分包含异常处理程序,负责处理子程序运行中可执行部分出现的异常。 子程序除了拥有 PL/SQL 块的优点外,还有:  模块化:子程序将程序分解为明确的,可管理的逻辑模块。程序包可以将若干个子 程序放入同一个包中,统一管理。  可重用性:子程序在创建并执行后,保存在服务器上,其他子程序或者客户端可以 轻松的进行调用。  安全性:用户可以设置权限,使得访问数据的唯一方式就是子程序,子程序是数据 库对象,用户可以为子程序授予适当的权限控制,限制用户对子程序的调用。这不 仅可以让数据安全,而且还能保证数据正确。 2. 过程  过程的概念 过程是执行一系列 PL/SQL 操作的子程序,从根本上讲,过程就是命名的 PL/SQL 程序库, 他可以拥有参数,过程编译后存储在数据库中,然后由应用程序或者其他的 PL/SQL 块调用。 创建过程的语法是: 创建过程的语法 CREATE [OR REPLACE] PROCEDURE 过程名[(过程参数列表)] ① {IS|AS} ② 第 7 章子程序和程序包 133 --声明部分 ③ BEGIN --可执行部分 [EXCEPTION] --异常处理部分 END [过程名]; ④ 说明: ① OR REPLACE 是可选的,表示如果当前用户下已经有同名的过程名,则覆盖旧的过 程。过程名后面括号中是过程的参数,过程的参数可有可无,如果过程存在参数, 参数列表必须有小括号括起来。如果过程没有参数,不能有小括号。 ② IS 或 AS 标志着 PL/SQL 块开始,IS 和 AS 是等价的。在过程中没有关键字 DECLARE。 ③ 如果过程中需要本地变量、常量等声明部分,可以在这里声明,方法与 PL/SQL 块 中声明变量的方法相同。 ④ 为了便于阅读交流和良好的编程风格,建议在 END 后面加上过程名。  不带参数的过程 案例: EMP 表中员工 ALLEN 在入职时劳动合同条款是每半年工资提升 5%。为调用方 便,请编写过程实现该操作。 代码演示:创建不带参数的过程。 CREATE OR REPLACE PROCEDURE DisplaySal IS newsal emp.sal%TYPE; oldsal emp.sal%TYPE; BEGIN --保存原来的工资 SELECT sal INTO oldsal FROM emp WHERE ename='ALLEN'; --根据参数更新工资 UPDATE emp SET sal=sal+sal*0.05 WHERE ename='ALLEN'; --取得更新后的工资 SELECT sal INTO newsal FROM emp WHERE ename='ALLEN'; --输出工资 dbms_output.put_line('ALLEN 原来的工资是:'||oldsal||',更新后的工资是: '||newsal); 第 7 章 子程序和程序包 134 EXCEPTION WHEN no_data_found THEN dbms_output.put_line('查无此人!'); END DisplaySal; 过程创建成功后,保存到 Oracle 服务器中,作为当前用户的数据库对象存在,过程可 以在 SQL*Plus 命令行下调用,也可以在其他 PL/SQL 块中调用。在命令行下使用命令 EXECUTE 命令调用过程,在其他 PL/SQL 块中直接使用过程名即可调用。 代码演示:SQLPLUS 提示符下调用过程 SQL> set serverout on SQL> EXECUTE DisplaySal ① ALLEN 原来的工资是:1600,更新后的工资是: 1680 PL/SQL procedure successfully completed 代码解析: ① 直接使用 EXECUTE 命令执行过程,如果过程没有参数,调用时不带小括号。 代码演示:PL/SQL 块中调用过程 SQL> BEGIN 2 DisplaySal; ① 3 END; 4 / ALLEN 原来的工资是:1600,更新后的工资是: 1680 PL/SQL procedure successfully completed 代码解析: ① PL/SQL 中直接使用过程名调用过程。 第 7 章子程序和程序包 135  带参数的过程 过程中一般需要使用参数来增强程序的灵活性。在上面案例中可以如下实现。 代码演示:带参数的过程 CREATE OR REPLACE PROCEDURE UpdateSal(per NUMBER,var_empno NUMBER) ① IS en emp.ename%TYPE; --员工姓名 newsal emp.sal%TYPE; --更新后工资 oldsal emp.sal%TYPE; --更新前工资 BEGIN --保存原来的工资 SELECT sal INTO oldsal FROM emp WHERE empno=var_empno; --根据参数更新工资 UPDATE emp SET sal=sal+sal*per WHERE empno=var_empno; --取得更新后的工资 SELECT ename,sal INTO en,newsal FROM emp WHERE empno=var_empno; --输出工资 dbms_output.put_line(en||'原来的工资是:'||oldsal||',更新后的工资是: '||newsal); EXCEPTION WHEN no_data_found THEN dbms_output.put_line('查无此人!'); END UpdateSal; 代码解析: ① 过程中有两个参数,分别是员工姓名和工资增长率。这样就可以对任何员工作任何 涨幅的操作了。子程序中的参数不需要规定参数的长度,比如[ename VARCHAR2] 不需要指定 VARCHAR2 的长度。 客户端调用带参数的存储过程时,参数要用小括号括起来,并且与过程中的参数一一对 应。 代码演示:SQLPLUS 提示符下调用带参数的过程 SQL> EXECUTE UpdateSal(0.1,7499); 第 7 章 子程序和程序包 136 ALLEN原来的工资是:1600,更新后的工资是: 1760 PL/SQL procedure successfully completed  过程的参数模式 调用程序是通过参数向过程传值的,过程中的形参接收调用者传递过来的参数,并且在 过程中可以使用。默认情况下,过程中的参数在过程中只能使用,不能对参数重新赋值。过 程中参数传递共有三种模式:分别是 IN、OUT 和 IN OUT。参数的具体语法是: 参数名称 [IN|OUT|IN OUT] 数据类型 说明: 1. IN:默认模式,表示参数只能由外界输入,不能在过程中对 IN 参数重新赋值。该 参数可以有默认值。 2. OUT:该参数不能有默认值,该参数不能接收数据,在过程中对 OUT 参数进行赋值 后,该值可以被调用者捕获,调用者调用时对 OUT 参数只能使用变量作为参数, 不能使用常量。 3. IN OUT:该参数在过程中可以接收数据,也可以对 IN OUT 参数赋值,赋值后的 IN OUT 参数可以被调用者捕获,调用者调用时对IN OUT 参数只能使用变量作为参数, 不能使用常量。IN OUT 参数不能包含默认值。 代码演示:过程的参数模式 CREATE OR REPLACE PROCEDURE DisplayComm (emp_num IN NUMBER,comm OUT NUMBER) IS emp_sal emp.sal%TYPE; mycomm emp.comm%TYPE; BEGIN SELECT sal,comm INTO emp_sal,mycomm FROM emp WHERE empno=emp_num; IF emp_sal>1500 THEN comm:=mycomm+1000; END IF; EXCEPTION 第 7 章子程序和程序包 137 WHEN no_data_found THEN dbms_output.put_line('查无此人!'); END DisplayComm; 代码解析: ① emp_num 是 IN 参数,只能在过程中接收数据,不能对 emp_num 进行赋值。 ② comm 是 OUT 参数,过程中不能接收参数,可以对 comm 进行赋值。 调用方式: 代码演示:带 OUT 参数的调用 SQL> DECLARE 2 MYCOMM NUMBER; 3 BEGIN 4 DisplayComm(7499,mycomm); ① 5 dbms_output.put_line('实际发放奖金是:'||mycomm); 6 END; 7 / 实际发放奖金是:1300 PL/SQL procedure successfully completed 代码解析: ① 调用带 OUT 参数的过程时,要用一个变量作为参数。调用过程之后,就能返回过程 中对 OUT 参数的最后赋值结果。 代码演示:带 IN OUT 参数 CREATE OR REPLACE PROCEDURE swap(num1 IN OUT NUMBER,num2 IN OUT NUMBER) IS tmp number; BEGIN tmp:=num1; num1:=num2; num2:=tmp; END swap; IN OUT 参数的过程中对参数可以接收数据,也可以对参数进行赋值操作。 第 7 章 子程序和程序包 138  默认值参数 代码演示:默认值参数 CREATE OR REPLACE PROCEDURE DisplayTax (emp_no NUMBER,base_sal NUMBER DEFAULT 1500) ① IS esal NUMBER; tax NUMBER; BEGIN SELECT sal+comm INTO esal FROM emp WHERE empno=emp_no; IF esal>base_sal THEN tax:=esal*0.2; dbms_output.put_line('需要缴税:'||tax); END IF; EXCEPTION WHEN no_data_found THEN dbms_output.put_line('查无此人!'); END DisplayTax; 代码解析: ① 在参数定义同时用 DEFAULT(或者赋值运算符)直接对参数进行初始化,即参数的 默认值,当调用者没有对该参数传值时,过程中就使用参数默认值。一个过程中可 以有若干个默认值参数,但是默认值参数只能放在参数列表的最后。 代码演示:默认值参数调用 SQL> execute displayTax(7499,1500); ① 需要缴税:380 PL/SQL procedure successfully completed SQL> execute displayTax(7499); ② 需要缴税:380 PL/SQL procedure successfully completed 代码解析: ① 正常参数调用,传入两个参数,过程中两个参数分别接收。 第 7 章子程序和程序包 139 ② 传入一个参数被过程中第一个参数接收,过程中第二个参数使用默认值。 3. 函数 函数与过程相似,也是数据库中存储的已命名 PL/SQL 程序块。函数的主要特征是它必 须返回一个值。创建函数时通过 RETURN 子句指定函数返回值数据类型。在函数的任何地方, 都可以通过“RETURN 表达式”语句从函数返回,这里的“表达式”的值与函数定义中指定 的返回数据类型一致。 创建函数的语法 CREATE [OR REPLACE] FUNCTION 函数名[(过程参数列表)] RETURN 数据类型 ① {IS|AS} --声明部分 ③ BEGIN --可执行部分 [EXCEPTION] --异常处理部分 END [函数名]; ④ 说明: ① 创建函数的语法与创建过程语法相似,不同的是,函数中要求必须返回一个值,而 过程中没有。因此在此声明函数的时候需要用 RETURN 子句指明返回的数据类型。 代码演示:函数定义 CREATE OR REPLACE FUNCTION getTax(emp_no NUMBER,base_sal NUMBER default 1500) RETURN NUMBER ① IS esal NUMBER; BEGIN SELECT sal+comm INTO esal FROM emp WHERE empno=emp_no; IF esal>base_sal THEN RETURN base_sal*0.2; ② END IF; RETURN 0; ③ 第 7 章 子程序和程序包 140 EXCEPTION WHEN no_data_found THEN dbms_output.put_line('查无此人!'); END getTax; 代码解析: ① 函数返回 NUMBER 类型,第二个参数默认值是 1500 ② ③ 真的函数声明中的类型返回一个 NUMBER 类型的数据。 函数的调用: 代码演示:PL/SQL 调用函数 SQL> DECLARE 2 myTax number; 3 BEGIN 4 myTax:=getTax(7499); ① 5 dbms_output.put_line('按默认基数我的个人所得税是:'||myTax); 6 myTax:=getTax(7499,1200); ② 7 dbms_output.put_line('按1200元为基数基数我的个人所得税是:'||myTax); 8 END ; 9 / 按默认基数我的个人所得税是:300 按1200元为基数基数我的个人所得税是:240 PL/SQL procedure successfully completed 代码解析: ① 使用默认值参数调用函数,函数有一个返回值,调用时需要用一个变量接收。 ② 调用函数。 使用 SELECT 语句也可以调用函数。 代码演示:SELECT 语句调用函数 SQL> SELECT getTax(7499,1200) FROM DUAL; GETTAX(7499,1200) ----------------- 第 7 章子程序和程序包 141 240 SQL> SELECT getTax(7499) FROM DUAL; GETTAX(7499) ------------ 300 Oracle 函数的参数也有 IN、OUT、IN OUT 之分,用法与过程一致。函数的内容中如果包 含对表进行 DML 操作,包括 INSERT、UPDATE、DELETE 和 SELECT…FOR UPDATE。则调用时有 以下限制  不能在 SELECT 语句中调用包含 DML 操作的函数。  不能在 INESRT 语句的 values 调用包含 DML 操作的函数。  不能在 UPDATE 语句的 SET 值中调用包含 DML 操作的函数。 4. 过程和函数的比较 过程和函数之间有相同的地方,也有存在差异的地方,如下表所示: 过程 函数 可以作为 PL/SQL 语句执行。直接调用。 作为表达式的一部分进行调用。 声明中不包含 RETURN 语句。 必须在声明中包含 RETURN 语句。 不返回任何值。 必须返回一个值。 可以使用 RETURN 语句表示函数终止。RETURN 后面不能有表达式。 必须包含至少一个 RETURN 语句,并且RETURN 后面必须是一个表达式。 表 1 函数和过程的比较 5. 自主事务 在 Oracle 子程序中,一个过程中的事务与调用者在同一个事务中,这样在过程中提交 或者回滚事务时,调用者调用前对数据库的处理也同时会提交或者回滚。当一个子程序调用 另一个过程时,任何提交或回滚语句均将影响这两个子程序中的事务处理。 执行如下两个过程,观察事务在其中的作用。 第 7 章 子程序和程序包 142 代码演示:过程 test2 中存在 commit CREATE OR REPLACE PROCEDURE test2 IS comm NUMBER; BEGIN SELECT NVL(comm,0) INTO comm FROM emp WHERE empno=7369; dbms_output.put_line('原先7369值:'||comm); UPDATE emp SET comm=30 WHERE empno=7369; COMMIT; END test2; 先查询出工号是 7369 的员工的奖金,然后对其降价调整为 30 元,提交事务。然后在过 程 test 中调用过程 test2: 代码演示:过程 test 调用过程 test2 CREATE OR REPLACE PROCEDURE test IS oldcomm number; comm NUMBER; BEGIN SELECT NVL(comm,0) INTO oldcomm FROM emp WHERE empno=7499; UPDATE emp SET comm=300 WHERE empno=7499; test2; ROLLBACK; SELECT comm INTO comm FROM emp WHERE empno=7369; dbms_output.put_line('调用后7369值:'||comm); SELECT comm INTO comm FROM emp WHERE empno=7499; dbms_output.put_line('调用前7499值是'|| to_char(oldcomm)||',调用后7499值是:'||comm); END test; 先把工号为 7499 的员工的奖金调整为 300,然后调用 test2,然后回滚事务,接下来分 别对员工 7369 和 7499 进行奖金查询。 第 7 章子程序和程序包 143 代码演示:调用过程 test1 SQL> UPDATE EMP SET COMM=0 WHERE EMPNO IN (7369,7499); 2 rows updated SQL> COMMIT; Commit complete SQL> EXECUTE test; 原先7369值:0 调用后7369值:30 调用前7499值是0,调用后7499值是:300 PL/SQL procedure successfully completed 调用结果如上所示,发现过程 test1 中的数据在过程 test2 中被提交,那么在过程 test2 中能否独立启动一个事务进行处理呢?那么必须使用编译指示命令:PRAGMA AUTONOMOUS_TRANSACTION。该指示放在声明部分,通知 PL/SQL 引擎将过程、函数、PL/SQL 块标识为自主独立事务。先对过程 Test2 进行修改,把 PRAGMA AUTONOMOUS_TRANSACTION 放在 Test2 的声明部分。 代码演示:独立事务过程 test2 CREATE OR REPLACE PROCEDURE test2 IS comm NUMBER; PRAGMA AUTONOMOUS_TRANSACTION BEGIN SELECT comm INTO comm FROM emp WHERE empno=7369; dbms_output.put_line('原先7369值:'||comm); UPDATE emp SET comm=30 WHERE empno=7369; COMMIT; END test2; 重新调用: 代码演示:调用过程 test1 SQL> UPDATE EMP SET COMM=0 WHERE EMPNO IN (7369,7499); 第 7 章 子程序和程序包 144 2 rows updated SQL> COMMIT; Commit complete SQL> EXECUTE test; 原先7369值:0 调用后9369值:30 调用前7499值是0,调用后7499值是:0 PL/SQL procedure successfully completed 这样在 Test2 中提交的数据不会影响 test1 中的数据操作。但是在自主事务中是一个独 立的事务,这样一定要注意数据更新时的锁,容易造成死锁。 6. 程序包 程序包是一种数据库对象,它对相关的 PL/SQL 类型、子程序、游标、异常、变量和常 量的封装。它包含两部分内容:程序包规范和程序包主体。在程序包规范中,可以声明类型、 变量、常量、异常、游标和子程序,包规范中声明的内容全部都是公共的。程序包主体用于 实现在程序包规范中定义的游标、子程序等,同时在程序包主体中可以声明局部变量和私有 过程,私有过程只能在同一个包内部调用,外部无法调用。  程序包规范 包规范中包含了应用程序所需的程序包中的资源,比如包中包含了类型、变量、常量、 异常、游标和子程序等,程序包规范是与应用程序的接口,只有包规范中定义的内容,应用 程序才能访问。如果程序包规范只声明类型、常量、变量、异常,则不需要有程序主体,只 有包规范中定义了子程序(包括过程、函数)和游标的声明,程序主体需要实现规范中定义 的过程、函数和游标。 定义包规范的语法是: 创建包规范的语法 CREATE [OR REPLACE] PACKAGE 包名 {IS|AS} [公共类型声明、常量、变量、异常和游标等声明] 第 7 章子程序和程序包 145 [子程序声明(只包含过程或函数的定义,并不实现)] END [包名] 在程序包规范声明的项可以在程序包外使用,这些项称为“公共项”。比如: 代码演示:创建程序包 CREATE OR REPLACE PACKAGE my_pack IS PI CONSTANT NUMBER :=3.1415926; --声明常量 ① my_name VARCHAR2(30):='NONAME'; --声明全局变量 ② TYPE myemp IS RECORD ( EMPNO NUMBER(4) , ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2) ); ③ PROCEDURE UpdateSal(per NUMBER,var_empno NUMBER); ④ FUNCTION getTax(emp_no NUMBER,base_sal NUMBER default 1500) RETURN NUMBER; ⑤ END my_pack; 代码解析: ① 声明常量 PI,可以在包外部使用。 ② 声明一个全局变量 my_name。 ③ 定义一个类型,相当于 Java 中的类。创建类型的语法是 CREATE TYPE,在程序包中 用 Type 定义类型,该类型只能在包中使用,该类型与表 EMP 的字段一致,因此该 类型与“EMP%TYPE”等价,都存在若干个属性,类型定义中也可以定义该类型的 子程序(即方法),具体的做法请参考 Oracle 官方文档。 ④ 声明一个过程。但是在包规范中不实现,需要定义包主体实现。 ⑤ 声明一个函数。但是在包规范中不实现,需要定义包主体实现。 第 7 章 子程序和程序包 146 外部应用程序调用时可以采用“包名.成员”的形式对包规范中的成员进行调用,比如 在 PL/SQL 块中访问上面包成员 PI,具体的结果如下: 代码演示:包成员访问 SQL> BEGIN 2 dbms_output.put_line(my_pack.PI); ① 3 my_pack.my_name:='XiaoMei'; ② 4 dbms_output.put_line('My name is '||my_pack.my_name); 5 END; 6 / 3.1415926 My name is XiaoMei PL/SQL procedure successfully completed 代码解析: ① 访问包成员使用“包名.成员”形式,因此访问常量 PI 使用 my_pack.PI。 ② 在外部应用中可以直接访问包规范中的变量。 但是外部应用中如果需要访问包规范中的过程或者函数,必须要求过程和函数已经在包 主体中实现。下面学习程序包主体。  程序包体 程序包主体包含了程序包规范中声明的每个游标和子程序的具体实现。程序包主体中声 明的变量、常量、游标、异常和类型都是私有声明,只能在该包中的内部访问。创建包主体 的语法是: 创建包主体的语法 CREATE [OR REPLACE ] PACKAGE BODY 包名 IS|AS [私有项声明] ① 包规范中规定的子程序和游标的实现 ② [包规范中未规定的私有子程序] ③ [BEGIN] ④ 第 7 章子程序和程序包 147 [包的初始化语句] END [包名] 说明: ① 包主体中可以声明变量、常量、游标、异常和类型,但这些都是私有类型。 ② 必须实现包规范中规定的子程序和游标。 ③ 在程序包主体中可以自定义一些子程序。但这些子程序不能被外部调用,只能被当 前包主体的其他子过程调用。 ④ 包可以有初始化语句,该语句只在每一个会话的第一次访问该包时执行。初始化部 分相当于 Java 中的静态块,不能被程序包调用。初始化部分针对每个会话只执行一 次。 代码演示:实现包主体 CREATE OR REPLACE PACKAGE BODY my_pack AS PROCEDURE UpdateSal(per NUMBER,var_empno NUMBER) IS en emp.ename%TYPE; newsal emp.sal%TYPE; oldsal emp.sal%TYPE; BEGIN --保存原来的工资 SELECT sal INTO oldsal FROM emp WHERE empno=var_empno; --根据参数更新工资 UPDATE emp SET sal=sal+sal*per WHERE empno=var_empno; --取得更新后的工资 SELECT ename,sal INTO en,newsal FROM emp WHERE empno=var_empno; --输出工资 dbms_output.put_line(en||'原来的工资是:' ||oldsal||',更新后的工资是: '||newsal); EXCEPTION WHEN no_data_found THEN dbms_output.put_line('查无此人!'); END UpdateSal; 第 7 章 子程序和程序包 148 FUNCTION getTax(emp_no NUMBER,base_sal NUMBER default 1500) RETURN NUMBER IS esal NUMBER; BEGIN SELECT sal+comm INTO esal FROM emp WHERE empno=emp_no; IF esal>base_sal THEN RETURN base_sal*0.2; END IF; RETURN 0; EXCEPTION WHEN no_data_found THEN dbms_output.put_line('查无此人!'); END getTax; BEGIN dbms_output.put_line('包已经初始化'); END my_pack; 包中的子程序调用需要使用“包名.子程序名”的形式进行调用。 代码演示:包子程序调用 SQL> SELECT getTax(7499,1200) FROM DUAL; GETTAX(7499,1200) ----------------- 240 包已经初始化 SQL> SELECT getTax(7499) FROM DUAL; GETTAX(7499) ------------ 300 第 7 章子程序和程序包 149  程序包的优势 Oracle 包将相关的功能在逻辑上组织在一起,包比单独的过程更具有强大的优势:  模块化 使用程序包,可以把更多的子程序、类型、对象封装起来,使程序管理更方便,同时每 个程序包可以更容易让我们理解应用程序中涉及的概念。使我们可以轻松的使用面向对象思 想编程。  更轻松的应用程序设计 要设计应用程序,首先在程序包规范中指定对象、类型或者子程序。编辑完包规范之后, 再编辑程序包主体中的子程序。因此,在准备好应用程序的规格说明之前,不需要完整定义 程序包主体。  信息隐藏 在程序包主体定义的类型、对象都是私有对象,在程序包主体中定义的子程序出现在包 规范中时,该子程序可以被外部访问,否则不能被外部访问。也就是说,在包规范中定义的 类型、对象和子程序都是公共的,外部可以访问。在程序包主体中定义的子程序在规范中没 有出现的都是私有的,外部不能访问。  子程序重载 在程序包中,可以定义名称相同的子程序,但要求参数不能相同,这种情况称为子程序 重载。子程序重载中要求的参数不同包括:参数个数可以不同,参数的类型可以不同,对返 回值没有要求。 第 7 章 子程序和程序包 150 7. 本章总结  子程序是已经命名的 PL/SQL 块,他们预编译后存储在数据库中,可以随时从任何 数据库客户端和应用程序中调用它们。命名的 PL/SQL 程序包括过程和函数。  过程是执行一系列 PL/SQL 操作的子程序,从根本上讲,过程就是命名的 PL/SQL 程 序块,它可以拥有参数,存储在数据库中,然后由应用程序或者其他的 PL/SQL 块 调用。  函数与过程相似,也是数据库中存储的已命名 PL/SQL 程序块。函数的主要特征是 它必须返回一个值。创建函数时通过 RETURN 子句指定函数返回值数据类型。  子程序中使用编译指示命令:PRAGMA AUTONOMOUS_TRANSACTION。该指示放在 声明部分,通知 PL/SQL 引擎将过程、函数、PL/SQL 块标识为自主独立事务。  程序包是一种数据库对象,它对相关的 PL/SQL 类型、子程序、游标、异常、变量 和常量的封装。它包含两部分内容:程序包规范和程序包主体。  只有包规范中定义的内容,应用程序才能访问。  程序包主体包含了程序包规范中声明的每个游标和子程序的具体实现。程序包主体 中声明的变量、常量、游标、异常和类型都是私有声明,只能在该包中的内部访问。 第 7 章子程序和程序包 151 8. 本章练习 1. 创建名为 ADD_DEPT 的过程,向表 DEPT 中插入一个新纪录,要求过程中存在两个 变量,部门编号和部门名称。 2. 创建调用函数 q_dept,返回某个 DName 的值。 3. 创建名为 ANNUAL_COMP 的函数,接收员工的姓名,在 EMP 表中返回员工的年薪。 年薪=sal*12+comm.*12; 4. 创建一个程序包,dept_pack,该包中包含了三个过程分别向 DEPT 表中添加,修改, 删除记录。 5. Oracle 是基于对象的关系型数据库,在 Oracle 中可以创建类型,类型中包含属性和 方法,创建类型和创建包类似,都需要先创建类型规范和类型主体,创建类型的语 法是 CREATE OR REPLACE TYPE 类型名称 AS OBJECT,请参考 Oracle 官方文档,定义 一个 Emp 表的类型,包括对该表的增删改操作的子程序。 第 7 章 子程序和程序包 152 章节知识结构图 游标、内置程序包 153 第 8 章 游标、内置程序包 主要内容  显式游标  游标操作的步骤  游标的属性  FOR 循环游标应用  引用游标 第 8 章 游标、内置程序包 154 1. 游标 游标(Cursor)在 PL/SQL 中可以增强 SQL 语句的功能,游标是用来查询数据、获取结 果集中记录的指针。它可以让开发者在结果集中访问结果集中的一行。游标以编程的方式访 问数据,从而完成在结果集的每个记录上的操作。也就是说,游标就是结果集中的记录指针, 该指针指向查询结果集中的某一行。 Oracle 中的游标分为两类,即:静态游标和 REF 游标。  静态游标: 在编译时游标与对应的 SELECT 语句绑定在一起,静态游标可以分为隐式游标和显式游 标。  REF 游标(引用游标) 很多时候,游标与对应的查询只有在运行时才能确定,在开发时,可以使用 REF 游标和 游标变量实现。为了使用引用游标,必须声明游标变量。引用游标又分为两种引用游标:强 返回类型游标(简称:强类型游标)和弱返回类型游标(简称:弱类型游标)。 2. 显式游标 PL/SQL 中,对于返回一条记录的查询,通过“SELECT...INTO 变量”就能处理。对于返 回多条记录的查询,需要使用游标逐条处理这些数据。  游标基本操作 游标的操作分为四个步骤。 1. 声明游标 命名游标,为一个查询语句绑定到一个游标上。声明游标时 SELECT 语句并不执行。声 明游标的语法格式是: CURSOR 游标名[(参数)] [RETURN 返回类型] IS SELECT 语句 游标声明中的查询语句中不能带有 INTO 子句。可以使用 Order by 子句。可以为游标指 定一个返回类型,也就是游标打开后的记录类型。同时可以为游标指定一个参数。 第 8 章游标、内置程序包 155 2. 打开游标 OPEN 命令可以打开游标,打开游标时,与游标绑定的 SELECT 语句开始执行,并且查询 的结果集中,游标指针自动指向第一条记录。语法是: OPEN 游标名称[(参数)] 如果游标定义中需要参数,则打开时需要为游标传递实际参数。 3. 从游标中提取数据 使用 FETCH 语句可以从打开的游标中检索数据,每次检索一行数据,每次 FETCH 之后, 游标自动定位到下一行数据。FETCH 的语法是: FETCH 游标名 INTO 变量名 FETCH 命令常常写在循环中。用于从结果集中每次检索一行数据。如果查询结果是多列, 需要多个变量名,或者一个记录类型的变量接收。可以使用%ROWTYPE 定义,也可以使用 CREATE Type… IS RECORED 定义。 4. 关闭游标 处理完游标操作之后,必须关闭游标,以释放分配给游标的所有资源。用于关闭游标的 语法如下: CLOSE 游标名 案例:对员工发放奖金,按照员工工资,最低的工资发放 100 元,以后每人在前人基础 上增加 50%。用游标进行实现。 代码演示:递增发放员工奖金 DECLARE addSal INT:=100; CURSOR myemp IS SELECT ename FROM emp ORDER BY sal; ① myname emp.ename%TYPE; num INT:=1; BEGIN OPEN myemp; ② LOOP FETCH myemp INTO myname; ③ 第 8 章 游标、内置程序包 156 EXIT WHEN myemp%NOTFOUND; ④ addSal:=ROUND(addSal+addSal*0.5); IF num=1 THEN addSal:=100; END IF; num:=num+1; UPDATE emp SET comm=NVL(comm,0)+addSal WHERE ename=myname; END LOOP; dbms_output.put_line('工资已经递增更新:'||myemp%ROWCOUNT||'人'); ⑤ CLOSE myemp; ⑥ END; 代码解析: ① 声明游标 myemp,直接与一个 SQL 语句关联。 ② 打开游标,执行查询操作,游标定位在结果集的第一行。 ③ 提取数据。由于只查询一列,因此一个基本变量就可以存放。如果提取多列的值, 需要由多个变量进行接收,或者使用一个记录型对象接收。 ④ %NOTFOUND 是游标的一个属性,当游标指针指向最后一条后,返回 TRUE。 ⑤ %ROWCOUNT 是游标的一个属性,返回游标提取的次数。 ⑥ 关闭游标,关闭的游标可以重新打开。  游标属性 前面的例子中,使用到了“游标名%NOTFOUND”,“%NOTFOUND”是一个游标属性, Oracle 中,游标有四大属性: 1. %FOUND 在一个结果集中,游标 FETCH 数据成功,则%FOUND 属性返回 TRUE。如果一个结果集 中没有任何记录,则%FOUND 返回 FALSE。 2. %NOTFOUND %NOTFOUND 属性与%FOUND 相反,如果一个结果集中没有返回任何结果,则返回 TRUE, 当游标指向结果集中最后一条记录,并且无法成功 FETCH 数据,则返回 TRUE,其他的都返 回 FALSE。 3. %ROWCOUNT 返回在结果集中,游标提取的行数。%ROWCOUNT 为整数值属性。在第一次获取之 前,%ROWCOUNT 为零。每 FETCH 成功一次,该属性加 1。 第 8 章游标、内置程序包 157 4. %ISOPEN 如果当前游标已经打开,返回 TRUE,否则返回 FALSE。  带返回值的游标 游标定义时,可以为游标指定一个返回类型。在结果集中如果存在若干列,使用一个自 定义类型把所有返回结果进行封装,可以使程序设计变得方便。 代码演示:为游标指定一个返回参数 DECLARE addSal INT:=100; TYPE emp_ty IS RECORD ( EMPNO NUMBER(4) , ENAME VARCHAR2(10), JOB VARCHAR2(9), SAL NUMBER(7,2), COMM NUMBER(7,2) ); ① myemp emp_ty; ② num INT:=1; CURSOR emp_cur RETURN emp_ty IS SELECT empno,ename,job,sal,comm FROM emp ORDER BY sal; ③ BEGIN OPEN emp_cur; LOOP FETCH emp_cur INTO myemp; ④ EXIT WHEN emp_cur%NOTFOUND; addSal:=ROUND(addSal+addSal*0.5); IF num=1 THEN addSal:=100; END IF; num:=num+1; UPDATE emp SET comm=NVL(comm,0)+addSal WHERE ename=myemp.ename; ⑤ 第 8 章 游标、内置程序包 158 END LOOP; dbms_output.put_line('工资已经递增更新:'||emp_cur%ROWCOUNT||'人'); CLOSE emp_cur; END; 代码解析: ① 定义一个类型 emp_ty,该类型与游标查询语句的返回结果的列一致,因此可以再③ 处作为游标的返回类型。 ② 为自定义类型 emp_ty 定义一个变量。 ③ 游标的 SELECT 语句返回的列与自定义类型 emp_ty 中的成员对应一致,因此 emp_ty 可以作为游标的返回类型。游标提取一行数据可以直接 INTO 到 emp_ty 类型变量中 比如④处。 ④ 游标提取一行数据可以直接 INTO 到 emp_ty 类型变量。 ⑤ 使用“变量名.属性”访问属性。  带参数的显式游标 L/SQL 允许显式游标接受输入参数,从而增强显式游标的灵活性。 案例:针对不同部门进行发放奖金。 代码演示:带参数的游标 DECLARE addSal INT:=100; TYPE emp_ty IS RECORD ( EMPNO NUMBER(4) , ENAME VARCHAR2(10), JOB VARCHAR2(9), SAL NUMBER(7,2), COMM NUMBER(7,2) ); myemp emp_ty; num INT:=1; CURSOR emp_cur(dept_no NUMBER) RETURN emp_ty IS ① 第 8 章游标、内置程序包 159 SELECT empno,ename,job,sal,comm FROM emp WHERE deptno=dept_no ORDER BY sal; BEGIN OPEN emp_cur(&dept_no); ② LOOP FETCH emp_cur INTO myemp; EXIT WHEN emp_cur%NOTFOUND; addSal:=ROUND(addSal+addSal*0.5); IF num=1 THEN addSal:=100; END IF; num:=num+1; UPDATE emp SET comm=NVL(comm,0)+addSal WHERE ename=myemp.ename; END LOOP; dbms_output.put_line('工资已经递增更新:'||emp_cur%ROWCOUNT||'人'); CLOSE emp_cur; END; 代码解析: ① 定义带参数的游标 ② 游标打开时需要对游标参数传值。  显式游标的数据删除或者更新 使用游标时,如果处理过程中需要删除或更新当前游标所在的行。在定义游标时,必须 使用SELECT…FOR UPDATE 语句先对查询结果锁定,而在执行DELETE 和UPDATE 时使用ROWID 或者 Current OF 子句,作为条件进行更新。声明更新游标的语法如下: CURSOR 游标名 IS SELECT 语句 FOR UPDATE [OF 列名] 查询操作中要查询出 ROWID 列,在查询或删除时可以如下使用: UPDATE 表名 SET 列名=值 WHERE ROWID=查询出的 ROWID DELETE FROM 表名 WHERE ROWID=查询出的 ROWID 第 8 章 游标、内置程序包 160 或者: UPDATE 表名 SET 列名=值 WHERE CURRENT OF 游标名 DELETE FROM 表名 WHERE CURRENT OF 游标名 UPDATE 和 DELETE 语句只有在打开游标并提取特定行之后才能使用。 代码演示:游标行更新 DECLARE addSal INT:=100; row_id ROWID; ① num INT:=1; CURSOR emp_cur(dept_no NUMBER) IS SELECT ROWID FROM emp WHERE deptno=dept_no ORDER BY sal FOR UPDATE OF comm; ② BEGIN OPEN emp_cur(10); LOOP FETCH emp_cur INTO row_id; ③ EXIT WHEN emp_cur%NOTFOUND; addSal:=ROUND(addSal+addSal*0.5); IF num=1 THEN addSal:=100; END IF; num:=num+1; UPDATE emp SET comm=NVL(comm,0)+addSal WHERE rowid=row_id; ④ --UPDATE emp SET comm=NVL(comm,0)+addSal --WHERE CURRENT OF emp_cur; END LOOP; dbms_output.put_line('工资已经递增更新:'||emp_cur%ROWCOUNT||'人'); CLOSE emp_cur; END; 代码解析: ① 定义一个 ROWID 类型的变量。 ② 游标对应的查询中要输出 rowID 列,以备后用。 ③ 提取 ROWID 数据。 第 8 章游标、内置程序包 161 ④ 用 ROWID 作为条件进行操作。这里也可以使用“CURRENT OF 游标名”作为条件。 3. FOR 循环游标 可以使用循环游标简化显式游标的处理,循环游标隐式打开,自动从结果集中获取行, 然后在处理完所有操作后自动关闭游标。循环游标自动创建%ROWTYPE 类型的变量并将此变 量作为记录索引变量。循环游标的语法是: FOR 记录索引变量 IN 游标名称 LOOP --游标处理。 END LOOP; 说明: 记录索引变量:不需要声明,自动为%ROWTYPE 类型,作用域在 FOR 循环之内,每次 循环自动从当前游标中获取出记录对象赋值给该索引对象。 FOR 循环游标自动打开,被提取完数据后,自动关闭,每次循环处理结果集中当前游标 行记录。如果在提取数据后%NOTFOUND 返回 TRUE 则终止循环。如果结果集中没有返回行, 则不进入循环。 代码演示:FOR 循环游标 DECLARE CURSOR mycur IS SELECT * FROM emp; ind NUMBER:=0; BEGIN FOR rsc IN mycur ① LOOP ind:=ind+1; dbms_output.put_line('查询员工名称:'||rsc.ename); ② END LOOP; dbms_output.put_line('总共有:'||ind); END; 代码解析: 第 8 章 游标、内置程序包 162 ① For 循环游标自动打开,自动提取数据到 rsc 变量中,提取结束后自动关闭。 ② 访问提取的数据 rsc 时,可以使用“.”来操作。 4. 隐式游标 PL/SQL 为所有 SQL 数据操纵语句隐式声明游标,称为隐式游标,用户不能直接命名和 控制隐式游标。当用户在 PL/SQL 中使用 DML 语言时,Oracle 预先定义一个名为 SQL 的隐式 游标,通过检查隐式游标的属性可以获取与最近执行的 SQL 语句相关的信息。DML 操作之 后,隐式游标返回四个属性信息。  %FOUND :DML 语句返回一行或者多行时,%FOUND 属性才返回 TRUE。  %NOTFOUND:与 %FOUND 相反,如果 DML 语句没有返回影响任何行,则返回 TRUE。  %ROWCOUNT:返回 DML 语句影响的行数。如果没有返回任何行,则返回 0。  %ISOPEN:表示游标是否打开。在 DML 语句执行后自动关闭游标,因此在隐式游 标中,%ISOPEN 永远为 FALSE。 代码演示:隐式游标示例 SQL> BEGIN 2 INSERT INTO DEPT 3 SELECT 50,'销售部','中国' FROM DUAL 4 UNION 5 SELECT 60,'公关部','日本' FROM DUAL 6 UNION 7 SELECT 70,'生产部','中国' FROM DUAL; 8 IF sql%FOUND THEN 9 dbms_output.put_line('插入成功'); 10 END IF; 11 IF sql%NOTFOUND THEN 12 dbms_output.put_line('插入失败'); 13 END IF; 14 dbms_output.put_line('插入了'||sql%ROWCOUNT||'行'); 15 END; 16 / 插入成功 第 8 章游标、内置程序包 163 插入了3行 PL/SQL procedure successfully completed 5. REF 游标 隐式游标和显式游标都是静态定义的,在 PL/SQL 块编译时候查询语句已经确定。如果 用户需要在运行时动态决定执行何种查询,可以使用 REF 游标和游标变量来完成。 创建游标变量需要两个步骤: 1. 声明 REF CURSOR 类型。前面学习中已经认识了 RECORD 的类型定义,还可以用 REF CURSOR 声明引用游标类型。声明游标类型的语法是: TYPE 引用游标类型名 IS REF CURSOR [RETURN 记录类型] 声明的游标类型可以使用“RETURN 记录类型”指明该引用类型的变量需要返回一个 记录类型。包括返回类型的引用游标称为强类型引用游标。不包括返回类型的游标称为弱类 型引用游标。强类型引用游标是限制游标中查询语句的查询结果,只能用在固定的查询结果 集上,弱类型引用游标可以用于任何查询结果集。 2. 使用声明的 REF CURSOR 类型定义游标变量。定义游标变量的语法与正常变量的声 明相同。 引用游标的操作与前面的静态游标一样也需要打开(OPEN)、提取(FETCH)、关闭(CLOSE) 等步骤。不同的是,静态游标在声明时就为游标绑定了查询语句,而引用游标在打开时为游 标绑定语句。打开引用游标的语法是:OPEN 游标名 FOR 查询语句 案例:根据输入的字符串(不能确定该字符串是员工姓名还是部门名称),求出该字符 串对应的部门名称。 代码演示:引用游标 CREATE OR REPLACE FUNCTION getEmpNo(v_name VARCHAR2) RETURN NUMBER IS TYPE EMP_TY IS RECORD ( ENAME VARCHAR2(10), 第 8 章 游标、内置程序包 164 DEPTNO NUMBER:=-1 ); ① TYPE ty_EMP_REFCURSOR is REF CURSOR RETURN EMP_TY; ② cur_emp ty_EMP_REFCURSOR; my_emp EMP_TY; ③ counter number; BEGIN SELECT count(1) INTO counter FROM emp WHERE ename=v_name; if counter>0 THEN OPEN cur_emp FOR SELECT ename,deptno FROM EMP WHERE ename=v_name; ④ END IF; SELECT count(1) INTO counter FROM dept WHERE dname=v_name; IF counter>0 THEN OPEN cur_emp FOR SELECT dname,deptno FROM dept WHERE dname=v_name; ⑤ END IF; IF cur_emp%ISOPEN THEN FETCH cur_emp INTO my_emp; ⑥ CLOSE cur_emp; END IF; RETURN my_emp.DEPTNO; END; 代码解析: ① 声明一个记录类型 EMP_TY。 ② 声明一个游标类型,规定该游标返回 EMP_TY 类型。 ③ 根据声明的游标类型定义游标变量。 ④ ⑤ 打开游标时根据不同条件为游标指定不同的查询语句,同时执行查询。在⑥处 可以提取数据到 EMP_TY 类型的变量。 REF 游标可以处理动态查询 SQL 语句,实现动态 SQL 的 REF 游标声明方法与普通 REF 游 标相同,只是在 OPEN 时指定一个动态 SQL 字符串。这时打开 REF 游标的语法是: OPEN 游标名 FOR 动态 SQL 字符串 [USING 参数]。 如果动态 SQL 字符串中存在参数,则使用 USING 子句进行传值。 第 8 章游标、内置程序包 165 案例:用存储过程显示员工薪水大于平均值的员工的姓名,工作和工资。 代码演示:引用游标执行劢态 SQL CREATE OR REPLACE PROCEDURE displayAboveAVG IS TYPE EMP_TY IS RECORD ( ENAME VARCHAR2(10), JOB VARCHAR2(9), SAL NUMBER(7,2) ); TYPE EMP_CUR_TY IS REF CURSOR; ① my_emp EMP_TY ; cur_emp EMP_CUR_TY; avg_sal number; BEGIN SELECT ROUND(AVG(sal)) INTO avg_sal FROM emp ; OPEN cur_emp FOR 'select ename,job,sal from emp where sal>:1 order by sal' USING avg_sal; ② LOOP FETCH cur_emp INTO my_emp; EXIT WHEN cur_emp%NOTFOUND; dbms_output.put_line('Ename:' ||my_emp.ENAME ||',JOB:'||my_emp.JOB ||',SAL:'||my_emp.SAL); END LOOP; CLOSE cur_emp; END displayAboveAVG; 代码解析: ① 声明一个引用游标,如果引用游标指向动态 SQL 语句,则不能在声明时制定返回类 型。 ② 动态 SQL 语句中参数“:1”可以使用 USING 对其进行传值。 第 8 章 游标、内置程序包 166 引用游标可以使用游标变量,游标变量功能强大,可以简化程序开发。游标变量的优点 有:  游标变量可以从不同的结果集中提取记录。  游标变量可作为过程的参数进行传递。  游标变量可以引用游标的所有属性。  游标变量可以进行赋值运算。 但是使用游标变量时也有一些限制,常用的有:  FOR UPDATE 子句不能与游标变量一起使用。  不允许在程序包中声明游标变量。  远程子程序不能接受游标变量参数的值。  不能将 NULL 值赋给游标变量。  游标变量不能使用关系运算符。  数据库列不能使用游标变量。 6. 游标在程序包中应用 在程序包中可以定义和使用游标,程序包中游标的定义分为游标规范和游标主体两部分。 更改游标主体时,无需改变游标规范。此外,在包规范中声明游标规范必须通过 RETURN 子 句指定游标的返回类型。 RETURN 子句是从游标获取的记录数据类型,实际上,该数类型由该游标的 SELECT 语句 确定,SELECT 语句仅出现在包主体中,而不出现在规范中。游标规范必须包含程序使用游 标所需的所有信息,因此需要返回数据类型。 RETURN 子句可以使用%ROWTYPE 属性根据数据库表定义的记录,也可以根据程序员自 己定义的记录类型。 代码演示:带游标的包规范定义 CREATE OR REPLACE PACKAGE EMP_PACK IS TYPE emp_ty IS RECORD ( EMPNO NUMBER(4) , ENAME VARCHAR2(10), 第 8 章游标、内置程序包 167 JOB VARCHAR2(9), SAL NUMBER(7,2), COMM NUMBER(7,2) ); ① CURSOR cur_emp RETURN emp_ty; ② PROCEDURE UpdateComm; ③ END EMP_PACK; 代码解析: ① 自定义一个 RECORD 的记录类型。 ② 在包规范中定义一个游标,返回自定义的记录类型 emp_ty。 ③ 定义一个过程。 对包规范进行实现,包括对游标的实现和对子程序的实现。 代码演示:带游标的包规范定义 CREATE OR REPLACE PACKAGE BODY EMP_PACK IS CURSOR cur_emp RETURN emp_ty IS SELECT empno,ename,job,sal,comm FROM emp ORDER BY sal; ① PROCEDURE UpdateComm IS myemp emp_ty; num INT:=1; addSal INT:=100; BEGIN OPEN cur_emp; LOOP FETCH cur_emp INTO myemp; EXIT WHEN cur_emp%NOTFOUND; addSal:=ROUND(addSal+addSal*0.5); IF num=1 THEN addSal:=100; END IF; num:=num+1; 第 8 章 游标、内置程序包 168 UPDATE emp SET comm=NVL(comm,0)+addSal WHERE ename=myemp.ename; END LOOP; dbms_output.put_line('工资已经递增更新:'||cur_emp%ROWCOUNT||'人'); CLOSE cur_emp; END UpdateComm; ② END EMP_PACK; 代码解析: ① 实现游标。 ② 实现过程。 Oracle 子程序可以通过引用游标返回一个结果集。 代码演示:引用游标通过包可以返回一个结果集 CREATE OR REPLACE PACKAGE Emp_PACK IS TYPE EMP_CUR_TY IS REF CURSOR RETURN Emp%ROWTYPE; ① FUNCTION getEmp(emp_sal NUMBER) RETURN EMP_CUR_TY; ② END Emp_PACK; 代码解析: ① 定义一个引用游标 ② 在函数中返回一个引用游标类型。 包体中只用把打开的游标返回即可返回一个结果集 代码演示:引用游标通过包可以返回一个结果集 CREATE OR REPLACE PACKAGE BODY Emp_PACK IS FUNCTION getEmp(emp_sal NUMBER) RETURN EMP_CUR_TY IS cur_emp EMP_CUR_TY; BEGIN OPEN cur_emp FOR SELECT * FROM EMP WHERE SAL>emp_sal; 第 8 章游标、内置程序包 169 RETURN cur_emp; ① END getEmp; END Emp_PACK; 代码解析: ① 返回打开的游标相当于返回结果集。 7. 内置程序包 Oracle 提供了许多内置程序包,他们用于扩展数据库的功能。在开发应用程序时,可以 利用这些程序包。这些程序包都是由 scott 用户创建,为了便于访问,Oracle 为每一个程序 包定义为公共同义词,可以被所有用户访问。常见的内置程序包有: 包名 说明 STANDARD 和 DBMS_STANDARD 定义和扩展 PL/SQL 语言环境。包中的内容不用引包即可直 接调用。 DBMS_LOB 提供对 Oracle LOB 数据类型进行操作的功能 DBMS_LOCK 用户定义锁 DBMS_OUTPUT 在 PL/SQL 中输入调试信息 DBMS_RANDOM 提供随机数生成器 DBMS_SQL 运行用户使用动态 SQL,构造和执行任意 DML 和 DDL 语句。 DBMS_JOB 提交和管理在数据库中执行的定时任务。 DBMS_XMLDOM 用 DOM 模型读写 XML 类型的数据。 UTL_FILE 用 PL/SQL 程序来读写系统文件。 表 1 Oracle 中常用内置程序包 常用的方法:  DBMS_OUTPUT.PUT_LINE:PL/SQL 中,输出一个数据。  DBMS_RANDOM.RANDOM:返回一个 8 位的随机整数,该整数的正负也是随机的。  DBMS_SQL.EXECUTE:动态执行 SQL 语句。  Dbms_JOB.SUBMIT:提交一个定时作业。 第 8 章 游标、内置程序包 170 8. 本章总结  在 PL/SQL 中,游标(Cursor)可以增强 SQL 语句的功能,游标是用来查询数据、 获取结果集中记录的指针。  Oracle 中的游标分为两类,即:静态游标和 REF 游标。静态游标又分为隐式游标和 显式游标,引用游标又分为强类型游标和弱类型游标。  游标的基本操作:声明游标、打开游标、提取数据、关闭游标。  游标有四个属性:%FOUND、%NOTFOUND、%ROWCOUND、%ISOPEN。  可以使用循环游标简化显式游标的处理,循环游标隐式打开游标,自动从结果集中 获取行,然后在处理完所有操作后自动关闭游标。  当用户在 PL/SQL 中使用 DML 语言时,Oracle 预先定义一个名为 SQL 的隐式游标, 通过检查隐式游标的属性可以获取与最近执行的 SQL 语句相关的信息。  使用 REF 游标和游标变量,可以完成在运行的时候动态决定执行的查询  定义引用游标的语法:TYPE 引用游标类型名 IS REF CURSOR [RETURN 记录类型]。  在程序包中可以定义和使用游标,游标的定义分为游标规范和游标主体两部分。更 改游标主体时,无需该表游标规范。此外,在包规范中声明游标规范是必须通过 RETURN 子句指定游标的返回类型。  Oracle 提供了许多内置程序包,他们用于扩展数据库的功能。 第 8 章游标、内置程序包 171 9. 本章练习 1. 接受一个部门编号,如果该部门下没有员工,使用游标则删除该部门操作。 2. 描述游标的分类。 3. 请描述游标的工作过程。 4. 什么是显式游标,什么是隐式游标? 5. 显式游标的属性有哪些?他们的作用都是什么? 6. FOR 循环游标具有哪些特点? 7. 如何定义、使用参数游标? 8. 游标在程序包中如何使用? 第 8 章 游标、内置程序包 172 章节知识结构图 触发器、数据字典 173 第 9 章 触发器、数据字典 主要内容  触发器概念  触发器作用  行级触发器 第 9 章 触发器、数据字典 174 1. 触发器 触发器是特殊的 PL/SQL 块,该 PL/SQL 块是在特定的事件出现时(比如:增删改数据时、 创建删除用户时、对数据库进行启动关闭时等)自动调用的。触发器与子程序的区别在于: 子程序由用户或应用程序显式调用,而触发器不能被用户或应用程序直接调用,Oracle 会在 事件发生时自动触发触发器执行对应的代码。 通常说的触发器与特定的表或者视图相绑定(特殊情况下还可以与 Schema 甚至是数据 库相绑定),用于检查对表或者视图所做的数据修改。当 INSERT、UPDATE、DELETE 等事件 发生在表或者视图时,就会激发触发器执行自身的代码(称为激活触发器)。触发器的语法 是: 触发器语法 CREATE [OR REPLACE] TRIGGER 触发器名称 {BEFORE|AFTER|INSTEAD OF} {INSERT|DELETE|UPDATE [OF 列1,列2…]} [OR {INSERT|DELETE|UPDATE [OF 列1,列2…]}] ON [schema.]表或者视图名 [REFERENCING [NEW AS 别名] [OLD AS 别名]] [FOR EACH ROW] [WHEN (条件)] [DECLARE 变量声明] BEGIN 触发器主体 [EXCEPTION 异常处理] END [触发器名称] 说明: ① BEFORE|AFTER 表示触发器触发的时机,说明触发器在触发事件发生前或者发生后 激活触发器。 ② INSTEAD OF 视图上创建的触发器,表示可以使用触发器代码代替触发器调用的事件, 比如在视图上 INSERT 操作,使用 INSTEAD OF 触发器就是用触发器代码操作代替视 图上的 INSERT 操作。 第 9 章触发器、数据字典 175 ③ INSERT、UPDATE、DELETE:指定触发器事件的数据操纵类型。只要是用户执行了 INSERT、UPDATE、DELETE 操作,对应的触发器就会被激活。UPDATE 触发器还可以 指定列,表示更新具体列时才激活触发器。 ④ REFERENCING:触发器对 INSERT、UPDATE、DELETE 操作存在新数据旧数据的情况, 新数据采用 NEW 对象表示,旧数据采用 OLD 对象来表示, a) 在 INSERT 中,NEW 表示要插入的数据,此时 OLD 对象没有意义。 b) 在 DELETE 中,OLD 表示已经删除的数据,此时 NEW 对象没有意义。 c) 在 UPDATE 中,NEW 表示已经更新的新数据。OLD 表示更新前的旧数据。 OLD 和 NEW 是记录对象,是宿主变量。REFERENCING 子句可以对 NEW 和 OLD 指定 一个别名。 ⑤ FOR EACH ROW:指定对受影响的每行都激发触发器,即行级触发器,如果不使用 此子句,则表示为语句级触发器,不论用户的操作影响了数据库多少行,只执行一 次触发器程序。 ⑥ WHEN:限制执行触发器的条件,该条件包括 NEW 或者 OLD 数据值的检查。 ⑦ DECLARE…END:是一个标准的 PL/SQL 块。 代码演示:触发器 SQL> set serverout on; SQL> CREATE OR REPLACE TRIGGER hello_BONUS 2 BEFORE 3 INSERT OR UPDATE OR DELETE 4 ON BONUS 5 BEGIN 6 dbms_output.put_line('hello,触发器hello_emp自动被调用'); 7 END hello_BONUS; 8 / Trigger created SQL> insert into bonus values ('jerry','sellerman',800,1200); hello,触发器hello_emp自动被调用 1 row inserted SQL> insert into bonus values ('jerry','sellerman',800,1200); hello,触发器hello_emp自动被调用 1 row inserted SQL> update bonus set sal=900 where ename='jerry'; hello,触发器hello_emp自动被调用 第 9 章 触发器、数据字典 176 2 row updated SQL> delete from bonus where ename='jerry'; hello,触发器hello_emp自动被调用 2 row deleted SQL> commit; Commit complete 上述案例中是在表 Bonus 中定义一个触发器,在 INSERT、UPDATE、DELETE 之前进行激 发。一个触发器的关键点是:触发器语句、触发器限制、触发器操作。  触发器语句 触发器语句是那些可以导致触发器执行的事件,即在表或者视图上执行的 INSERT、 DELETE、UPDATE 之类的 DML 语句。(在 Schema 对象上执行 DDL 语句或者数据库事件也可 以激发对应的触发器)。在上面案例中的触发器语句是: … BEFORE INSERT OR UPDATE OR DELETE ON BONUS … 也就是说,触发器会在这些事件之前(BEFORE),隐式执行触发器中的代码。但是触发 器是否真正执行还需要检查触发器限制条件,只有满足限制条件,才能执行。  触发器限制 触发器限制条件包括一个布尔表达式,该表达式的值为“真”才能激发触发器。如果该 值为“假”或者该值“未知”则不允许触发器。Oracle 触发器中使用 WHEN 子句实现触发器 的限制条件。 代码演示:触发器中的条件 CREATE OR REPLACE TRIGGER hello_BONUS BEFORE 第 9 章触发器、数据字典 177 INSERT OR UPDATE ON BONUS FOR EACH ROW WHEN (NEW.SAL>900) BEGIN dbms_output.put_line('hello,触发器hello_emp自动被调用'); END hello_emp; 该触发器规定,只有插入的数据中 sal 值大于 900 时,触发器才能被触发。使用 NEW 和 OLD 时,可以使用“.”对 NEW 或者 OLD 中的字段进行操作。  触发器操作 触发器操作是触发器的主体,包含以下 SQL 语句和代码,这些代码在执行触发器语句且 触发器限制条件的值为“真”时运行。 2. 触发器类型 Oracle 具有不同类型的触发器,可以实现不同的任务。这些触发器类型包括:  行级触发器:针对 SQL 语句对数据影响的每一行都激发。  语句级触发器:即表级触发器。针对 SQL 语句对数据的操作,不论影响多少条数据, 触发器只激发一次。  INSTEAD OF 触发器:针对数据库视图的触发器。  Schema 触发器:针对 CREATE、ALTER、DROP 等语句的触发器。  数据库触发器:针对数据库改变时的触发器,比如数据库启动或者关闭。 3. 行级触发器 行级触发器对 DML 语句影响的每一个行执行一次。比如 UPDATE 和 DELETE 操作时经常 会影响数据库多条记录,行级触发器会对每一行数据都激活一次触发器。行级触发器是触发 器中最常用的一种,通常用于数据库审计和实现复杂的业务逻辑。可以在 CREATE TRIGGER 命令中指定 FOR EACH ROW 子句创建行级触发器。 第 9 章 触发器、数据字典 178 在行级触发器中,由于针对每一行激发一次触发器,因此可以轻松获取每一次的行信息, 只有在行级触发器中 NEW 和 OLD 才能起作用。 案例:一个学生表中的主键使用序列实现插入时的自动增长。并且要求插入的年龄必须 在 18 岁以上。 代码演示:触发器实现自劢增长 SQL> CREATE SEQUENCE stu_seq; ① Sequence created SQL> CREATE TABLE TblStudent 2 ( 3 stuid NUMBER PRIMARY KEY, 4 stuname VARCHAR2(20), 5 stuage NUMBER(3) 6 ); ② Table created SQL> CREATE OR REPLACE TRIGGER student_seq_trg 2 BEFORE 3 INSERT OR UPDATE OF stuid ③ 4 ON TblStudent 5 FOR EACH ROW 6 WHEN (NEW.stuage>=18) ④ 7 BEGIN 8 IF INSERTING THEN ⑤ 9 SELECT stu_seq.nextval INTO :NEW.stuid FROM DUAL; ⑥ 10 ELSE 11 RAISE_APPLICATION_ERROR(-20002,'不允许更新ID值'); ⑦ 12 END IF; 13 END; 14 / Trigger created SQL> INSERT INTO TblStudent values(default,'Jerry',20); ⑧ 1 row inserted SQL> INSERT INTO TblStudent(stuname,stuage) VALUES('Tom',21); ⑪ 1 row inserted SQL> UPDATE TblStudent set stuage=stuage+1; 第 9 章触发器、数据字典 179 2 rows updated SQL> UPDATE TblStudent set stuid=3 where stuname='Jerry'; ⑨ UPDATE TblStudent set stuid=3 where stuname='Jerry' ORA-20002: 不允许更新ID值 ORA-06512: 在 "SCOTT.STUDENT_SEQ_TRG", line 5 ORA-04088: 触发器 'SCOTT.STUDENT_SEQ_TRG' 执行过程中出错 SQL> commit; Commit complete SQL> SELECT * FROM TblStudent; ⑩ STUID STUNAME STUAGE ---------- -------------------- ------------ 1 Jerry 21 2 Tom 22 SQL> 代码解析: ① 使用默认值创建一个序列 stu_seq,从 1 开始,每次增长 1,没有最大值。 ② 创建一个表 TblStudent,stuid 是主键,并且为 NUMBER 类型。 ③ 创建一触发器,在对表 TblStudent 的添加和修改 Stuid 时激发。 ④ 在行级触发器中可以使用 WHEN 子句,WHEN 子句不能应用在表级触发器中。 ⑤ 由于触发器可以在 INSERT 和 UPDATE 时激活,在 PL/SQL 块中区分具体的操作可以 使用 INSERTING、UPDATING、DELETING 等条件谓词进行判断具体的哪一种数据操作。 INSERTING 为“真”表示该次操作是 INSERT 操作,如果 UPDATING 为真表示该次操 作是 UPDATE 操作,DELETING 为真表示该次操作是 DELETE 操作。条件谓词只能在 触发器主体中引用。 ⑥ NEW 表示 INSERT 的新纪录,在插入数据之前,用序列中的下一个值对 Insert 语句 中的 StuId 进行赋值,使 Stuid 的值变成下一个序列值。由于 NEW 和 OLD 是宿主变 量,因此在 PL/SQL 中引用 NEW 需要使用“:NEW”,WHEN 子句不是 PL/SQL 语句 块中内容,因此在 WHEN 条件中,使用 NEW 或者 OLD 可以直接引用。在 INSERT 触 发器中引用 OLD 没有意义,在 DELETE 触发器中引用 NEW 没有意义。 ⑦ 在触发器中不能使用事务处理语句进行提交或者回滚,如果在触发器中遭遇异常, 只能引发异常。 ⑧ 由于在触发器中对主键的值用序列值进行了修改,因此在外部 INSERT 操作时,stuId 的值可以是任意的 number 值,这种情况下通常使用“default”来代替,但是该值 默认下不能为省略。可以使用⑾处的 INERT 语句进行插入操作。 ⑨ 修改主键引发触发器,在触发器中抛出异常。 ⑩ 查询插入的结果,正常显示。 第 9 章 触发器、数据字典 180 4. 语句级触发器 在行级触发器中,如果 UPDATE 语句影响了表中 10 条记录,那么触发器就执行 10 次, 但是在某些程序中,针对一条 DML 语句不论影响任意多的记录,触发器却执行一次。如果 触发器的创建过程中没有“FOR EACH ROW”子句,那么该触发器就是语句级触发器,语句 级触发器不常用于与数据相关的操作,通常用于强制实施在表上执行操作的额外安全性措施。 案例:创建一个触发器,要求对于 DEPT 表,只能由 scott 用户访问。 代码演示:语句级触发器 SQL> CREATE OR REPLACE TRIGGER lockDept 2 BEFORE 3 INSERT OR UPDATE OR DELETE 4 ON dept 5 BEGIN 6 IF USER NOT IN ('SCOTT') THEN 7 RAISE_APPLICATION_ERROR(-20008,'你无权操作该表中的数据'); 8 END IF; 9 END lockDept; 10 / ① Trigger created SQL> GRANT ALL ON DEPT TO xiaomei; ② Grant succeeded SQL> CONN XiaoMei/xiaomei; Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 Connected as XiaoMei SQL> select * from scott.dept where deptno=20; ③ DEPTNO DNAME LOC ------ -------------- ------------- 20 RESEARCH DALLAS SQL> INSERT INTO SCOTT.dept VALUES (80,'测试部','新展'); ④ 第 9 章触发器、数据字典 181 INSERT INTO SCOTT.dept VALUES (80,'测试部','新展') ORA-20008: 你无权操作该表中的数据 ORA-06512: 在 "SCOTT.LOCKDEPT", line 3 ORA-04088: 触发器 'SCOTT.LOCKDEPT' 执行过程中出错 SQL> DELETE SCOTT.dept; DELETE SCOTT.dept ORA-20008: 你无权操作该表中的数据 ORA-06512: 在 "SCOTT.LOCKDEPT", line 3 ORA-04088: 触发器 'SCOTT.LOCKDEPT' 执行过程中出错 SQL> UPDATE SCOTT.dept SET loc='西安'; UPDATE SCOTT.dept SET loc='西安' ORA-20008: 你无权操作该表中的数据 ORA-06512: 在 "SCOTT.LOCKDEPT", line 3 ORA-04088: 触发器 'SCOTT.LOCKDEPT' 执行过程中出错 代码解析: ① 创建一个基于 Dept 表的语句级触发器。 ② 把 Dept 表的所有操作全部授权给用户 Xiaomei。 ③ XiaoMei 用户对 scott.Dept 表正常查询操作 ④ XiaoMei 用户对 scott.Dept 表进行增删改操作均有异常抛出。 当然,在某些应用系统中,必须使用语句级触发器,比如在一个表中创建一个 UPDATE 触发器,如果在触发器中进一步对该表进行了 Update 操作,这时就必须用语句级触发器。 5. INSTEAD OF 触发器 INSTEAD OF 触发器是定义在视图上而不是表上的触发器,它用来替换在视图上进行的 INSERT、UPDATE、DELETE 操作的触发器。 视图的主要功能是查询方便,但是有时候也需要对视图进行增删改操作,对视图进行 DML 操作时有一定的限制: 1. 如果基表中的某列不在视图中,同时该列规定不能为空,那么对该视图进行 INSERT 操作时就会失败。 2. 如果视图是若干表查询的结果,不能对视图进行一次 INSERT 操作而影响所有的基表。 第 9 章 触发器、数据字典 182 INSTEAD OF 触发器可以用于克服 Oracle 在任何视图上进行 INSERT、UPDATE、DELETE 操 作的限制。对于不能直接使用 DML 语句修改的视图,可以创建 INSTEAD OF 触发器。INSTEAD OF 在应用中存在一定的限制:  只能是行级触发器,不能是表级触发器。  只能用在视图,不能用在表。 案例:对基于 EMP,DEPT 的视图进行添加操作。 代码演示:基于 EMP,DEPT 表创建视图,对视图做 INSERT 操作 SQL> set serverout on; SQL> SQL> CREATE OR REPLACE VIEW EMP_VIEW AS 2 SELECT e.empno,e.ename,e.job,e.mgr,e.hiredate, 3 e.sal,e.comm,d.deptno,d.dname,d.loc 4 FROM emp e JOIN dept d ON e.deptno=d.deptno; ① View created SQL> SQL> CREATE OR REPLACE TRIGGER view_emp_trg 2 INSTEAD OF 3 INSERT 4 ON EMP_VIEW 5 REFERENCING NEW AS n ② 6 FOR EACH ROW ③ 7 DECLARE 8 my_dept_no number; 9 BEGIN 10 SELECT COUNT(1) INTO my_dept_no FROM dept WHERE deptno=NVL(:n.deptno,-1); --查看是否存在部门 11 --如果部门不存在,插入数据到两个表中。否则只插入EMP表。 12 IF my_dept_no=0 THEN 13 insert into dept(deptno,dname,loc) values (:n.deptno,:n.dname,:n.loc); 14 insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) 15 values (:n.empno,:n.ename,:n.job,:n.mgr, :n.hiredate,:n.sal,:n.comm,:n.deptno); 16 ELSE 第 9 章触发器、数据字典 183 17 insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) 18 values (:n.empno,:n.ename,:n.job,:n.mgr, :n.hiredate,:n.sal,:n.comm,:n.deptno); 19 END IF; 20 END; 21 / Trigger created SQL> SQL> INSERT INTO EMP_VIEW VALUES (7997,'XiaoMei','SALESMAN',7369, 2 TO_DATE('1980-4-16','YYYY-MM-DD'), 3 1000,200,20,'RESEARCH', 'DALLAS') 4 / ④ 1 row inserted SQL> SQL> INSERT INTO EMP_VIEW VALUES (7998,'ACONG','SALESMAN',7369, 2 TO_DATE('1981-5-26','YYYY-MM-DD'), 3 1000,200,90,'培训部', '西安') 4 / 1 row inserted SQL> SQL> INSERT INTO EMP_VIEW (empno,ename,job,deptno) 2 VALUES (7987,'Jerry','SALESMAN',30) 3 / 1 row inserted SQL> SELECT empno,ename,job FROM emp 2 WHERE empno in (7997,7998,7987); ⑤ EMPNO ENAME JOB 7987 Jerry SALESMAN 7997 XiaoMei SALESMAN 7998 ACONG SALESMAN SQL> SELECT * FROM dept 2 WHERE deptno IN(20,90,30); 第 9 章 触发器、数据字典 184 DEPTNO DNAME LOC 20 RESEARCH DALLAS 30 SALES CHICAGO 90 培训部 西安 代码解析: ① 创建一个基于 EMP 和 DEPT 两张表的视图 EMP_VIEW。 ② 创建的触发器中,对 NEW 定义别名为 n。 ③ 定义为行级触发器。 ④ 向视图中插入 3 条数据,第一条是存在部门的新员工,第二条数据是新部门、新员 工,第三条数据是只有部分字段值的数据。 ⑤ 在表中查看输入的数据是否正确。正确的数据是插入了三个员工,一个新部门。 6. Schema 级触发器 可以在 Schema 级别上建立数据库,比如 CREATE、ALTER、DROP、TRUNCATE 等 DDL 语 句发生时,激发 Schema 级触发器。Schema 级触发器主要应用于监控 DDL 操作以及在 DDL 操作时提供额外的安全监控。当在表、视图、过程、函数、索引、程序包、序列、同义词等 数据库 Schema 对象上执行 CREATE、DROP 和 ALTER 命令(称为触发事件)时,会激活 DDL 触发器。 创建 Schema 触发器的语法是: Schema 触发器语法 CREATE [OR REPLACE] TRIGGER 触发器名称 {BEFORE|AFTER } 触发器事件 ON [schema.]SCHEMA [WHEN (条件)] [DECLARE 变量声明] BEGIN 触发器主体 [EXCEPTION 异常处理] 第 9 章触发器、数据字典 185 END [触发器名称] 案例:创建一个日志表,对用户的 DROP 操作进行日志记录。 代码演示:用户 Drop 日志记录 SQL> CREATE TABLE LOG_OBJ 2 ( 3 obj_name VARCHAR2(30), 4 obj_type VARCHAR2(30), 5 ddl_date DATE 6 ); ① SQL> CREATE OR REPLACE TRIGGER LOG_LBJ_TRG 2 AFTER DROP ON SCHEMA 3 BEGIN 4 INSERT INTO LOG_OBJ 5 VALUES (ORA_DICT_OBJ_NAME,ORA_DICT_OBJ_TYPE,SYSDATE); 6 END LOG_OBJ_TRG; 7 / ② Trigger created SQL> CREATE TABLE TESTTRG(X CHAR); Table created SQL> DROP TABLE TESTTRG; ③ Table dropped SQL> CREATE SEQUENCE MY_SEQ; Sequence created SQL> DROP SEQUENCE MY_SEQ; Sequence dropped SQL> SELECT * FROM LOG_OBJ; ④ OBJ_NAME OBJ_TYPE DDL_DATE TESTTRG TABLE 07-9月 -09 MY_SEQ SEQUENCE 07-9月 -09 代码解析: ① 创建一个表用来保存用户的操作信息 第 9 章 触发器、数据字典 186 ② 创建一个触发器,在用户只需 Drop 操作时,把操作信息记录下来,其中用到了触 发器中常见的属性函数,常用的属性函数有: 名称 说明 ORA_CLIENT_IP_ADDRESS 返回客户端的 IP 地址 ORA_DATABASE_NAME 返回当前数据库名 ORA_DES_ENCRYPTED_PASSWORD 返回 DES 加密后的用户口令 ORA_DICT_OBJ_NAME 返回 DDL 操作所对应的数据库对象名 ORA_DICT_OBJ_OWNER 返回 DDL 操作所对应的对象的所有者名 OrA_DICT_OBJ_TYPE 返回 DDL 操作所对应的数据库对象的类型 ORA_LOGIN_USER 返回登录用户名 表 1 触发器中常用的系统信息 ③ 连续删除两张表 ④ 查看日志表中 LOG_OBJ 中的信箱,有两条信息。 7. 数据库级触发器 可以创建在数据库事件上的触发器,包括启动、关闭、服务器错误、登录和注销等。这 些事件都是实例范围的,不与特定的表或者视图关联。可以使用这种类型的触发器自动进行 数据库维护等活动。 示例:启动数据库的触发器: 代码演示:启劢数据库 CREATE OR REPLACE TRIGGER system_startup AFTER STARTUP ON DATABASE BEGIN --启动后的操作 END system_startup; 第 9 章触发器、数据字典 187 8. 触发器管理 触发器一旦创建就会立即生效,有时可能需要临时禁用触发器,用于禁用和启用触发器 的语法如下: ALTER TRIGGER 触发器名称 {ENABLE|DISABLE} 禁用一张表上的所有触发器 ALTER TABLE 表名 DISABLE ALL TRIGGERS; 启用一张表中所有的触发器 ALTER TABLE 表名 ENABLE ALL TRIGGERS; 删除触发器的语法是: DROP TRIGGER 触发器名称 9. 数据字典 数据字典是描述用户数据、数据库对象信息、系统信息的集合。Oracle 的数据字典就是 由 Oracle 服务器创建和维护的一组只读系统表。数据字典中存放了有关数据库和数据库对 象的信息,Oracle 服务器依赖这些信息来管理和维护 Oracle 数据库。 数据字典中存储了的数据库信息有:  数据库的逻辑结构和物理结构,如表空间和数据文件信息等。  所有数据库对象定义的信息。这些对象包括:表、索引、视图、序列号、同义词、 过程、函数、程序包和触发器等。  Oracle 用户信息,包括用户名,权限、角色等。  完整性约束信息  列的默认值等 由于数据字典中的信息用户无法操作,为了系统效率,Oracle 服务器以最简洁、最快的 方式来操作数据字典系统表,这些系统表中的信息用户查看起来非常困难。为了用户能够清 晰的查看系统信息,Oracle 提供了一系列的数据字典视图供用户查看,许多数据字典视图包 含相似的信息,数据字典视图分为三大类。他们用前缀 USER_、ALL_、DBA_来区别: 第 9 章 触发器、数据字典 188  USER_*:有关用户拥有的对象信息,即用户自己创建的对象的信息。  ALL_*:有关用户可以访问的对象的信息,即用户自己创建的对象的信息、该用户有 权访问的其他用户创建的对象信息。  DBA_*:有关整个数据库中对象的信息。 这里的*可以为 TABLES、INDEXS、OBJECTS 等。 数据字典视图中一般包含的信息有:  对象的拥有者。  用户所拥有的权限。  对象创建的时间。  对象存储参数设置。  对象存储空间的使用情况。 案例查看 SCOTT 用户下的所有表信息。 代码演示:查看 scott 用户下的表 SQL> SELECT TABLE_NAME,TABLESPACE_NAME,STATUS,NUM_ROWS 2 FROM USER_TABLES; TABLE_NAME TABLESPACE_NAME STATUS NUM_ROWS BANK USERS VALID 0 DEPT USERS VALID 7 EMP USERS VALID 14 BONUS USERS VALID 0 SALGRADE USERS VALID 5 INFOS USERS VALID 3 SCORES USERS VALID 0 INFOS2 USERS VALID 0 MY_DIAGRAMS USERS VALID 0 BONUS1 USERS VALID 0 TBLSTUDENT USERS VALID 11 rows selected 常见的数据字典视图有(括号中表示拥有的前缀): 第 9 章触发器、数据字典 189 名称 说明 GLOBAL_NAME 数据库中全程名信息。 INDEXES(ALL,DBA,USER) 数据库中所引信息。 OBJECTS(ALL,DBA,USER) 数据库中所有对象信息。 ROLE_PRIVS(DBS,USER) 授给一个用户的角色信息。 ROLES(DBA) 数据库中角色信息。 SYNONYMS(ALL,DBA,USER) 数据库中的同义词信息。 SYS_PRIVS(DBA,USER) 授予一个用户的系统权限。 TABLES(ALL,DBA,USER) 数据库中表信息。 TABLESPACES(ALL,DBA,USER) 数据库中表空间信息。 TRIGGERS(ALL,DBA,USER) 数据库中触发器信息。 USERS(ALL,DBA,USER) 数据库中的用户信息。 VIEWS(ALL,DBA,USER) 数据库中的视图信息。 Oracle 数据库中除了以上介绍的静态数据字典视图外,还有另一大类视图,他们称为动 态性能视图。动态性能视图是一组虚表。在 Oracle 数据库运行期间,这些虚表存在于内存 中。在整个数据库运行期间,Oracle 服务器将当前数据库的活动记录在这组虚表中。因此动 态性能视图中的信息实时的反应了数据库运行的状态。scott 用户拥有所有的动态性能视图。 它们的名称都以“V$”开头。利用动态性能视图可以获得类似如下信息:  会话活动的信息。  对象打开或关闭的信息。  对象在线或离线的信息等 数据库管理员经常使用动态性能视图来监测 Oracle 数据库的运行。 代码演示:查看劢态性能表 SQL> SELECT NAME,CREATED,LOG_MODE,OPEN_MODE 2 FROM V$DATABASE; NAME CREATED LOG_MODE OPEN_MODE --------- ------------ --------------- ----------------- ORCL 2009-8-24 8 NOARCHIVELOG READ WRITE 第 9 章 触发器、数据字典 190 10. 本章总结  触发器是特殊的 PL/SQL 块,该 PL/SQL 块是在特定的事件出现时自动调用的。  BEFORE|AFTER 表示触发器触发的时机,分别表示触发器在触发事件的前或者后激 活触发器。  FOR EACH ROW:指定是否对受影响的每行都执行触发器,即行级触发器,如果不 使用此子句,则表示为语句级触发器,表示不论用户的操作影响了数据库多少行, 只执行一次触发器程序。  一个触发器的关键点是:触发器语句、触发器限制、触发器操作。  触发器的类型有:行级触发器,语句级触发器,INSTEAD OF 触发器,Schema 触发器,数 据库触发器。  行级触发器对 DML 语句影响的每一个行执行一次。语句级触发器针对一条 DML 语 句不论影响任意多的记录,触发器只执行一次。  INSTEAD OF 触发器是定义在视图上而不是表上的触发器,它用来替换所使用实际 语句的触发器。  Schema 级触发器主要应用于监控 DDL 操作以及在 DDL 操作时提供额外的安全监控。  禁用一张表上的所有触发器语法是:ALTER TABLE 表名 DISABLE ALL TRIGGERS。启 用一张表中所有的触发器语法是:ALTER TABLE 表名 ENABLE ALL TRIGGERS。  数据字典中存放了有关数据库和数据库对象的信息,Oracle 服务器就是依赖这些信 息来管理和维护 Oracle 数据库的。数据字典视图一般以 USER_、DBA_、ALL_开头。  动态性能表是建立在内存中的一组虚表,它们的名称都以“V$”开头,实时的反 应了数据库运行的状态。 第 9 章触发器、数据字典 191 11. 本章练习 1. 触发器有哪些类型?请描述他们的作用。 2. 什么是行级触发器? 3. 什么是语句级触发器,他有哪些特点? 4. 什么是 INSTEAD OF 触发器? 5. 什么是数据字典? 6. 什么是静态性能视图? 7. 创建一个触发器,无论用户是插入 EMP 表中的数据,还是更新该表中 JOB 列,都 转化为大写保存。 8. 子程序与数据库触发器之间的区别。 9. 描述 NEW 和 OLD 的作用。 第 9 章 触发器、数据字典 192 章节知识结构图
还剩193页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 8 金币 [ 分享pdf获得金币 ] 1 人已下载

下载pdf

pdf贡献者

04041035

贡献于2015-07-05

下载需要 8 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf