oracle学习资料_oracle资料

第1章  基础入门

走进Oracle

在第一学期我们已经接触过关系型数据库SQL Server，对数据库、表、记录、表的增删改查操作等这些基本的概念已经了解。Oracle是基于对象的关系型数据库，Oracle也是用表的形式对数据存储和管理，并且在Oracle的操作中添加了一些面向对象的思想。

Oracle数据库是Oracle（中文名称叫甲骨文）公司的核心产品，Oracle数据库是一个适合于大中型企业的数据库管理系统。在所有的数据库管理系统中（比如：微软的SQL Server，IBM的DB2等），Oracle的主要用户涉及面非常广，包括：银行、电信、移动通信、航空、保险、金融、电子商务和跨国公司等。Oracle产品是免费的，可以在Oracle官方网站上下载到安装包，另一方面Oracle服务是收费的。

Oracle公司成立以来，从最初的数据库版本到Oracle7、Oracle8i、Oracle9i，Oracle10g到Oracle11g，虽然每一个版本之间的操作都存在一定的差别，但是Oracle对数据的操作基本上都遵循SQL标准。因此对Oracle开发来说版本之间的差别不大。

很多人没有学习Oracle就开始发怵，因为人们在误解Oracle，认为Oracle太难学了，认为Oracle不是一般人用的数据库，其实任何数据库对应用程序研发人员来说，都是大同小异，因为目前多数数据库都支持标准的SQL。在Oracle这本书中，我们能学习到：

Ø  Oracle的安装

Ø  Oracle数据管理

Ø  常用子查询及常用函数

Ø  PL/SQL编程

Ø  Oracle基本管理

由于在第一学期已经接触了SQL Server，Oracle数据库的概念不是很难，主要是实践，因此在本书的学习中，认真的完成上机练习是学习好本书的关键。

接下来我们先从Oracle安装开始，接触一些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安装的最低要求等。

图2  Oracle安装前环境检查

3.       Oracle检查通过后，单击“下一步”，就会列出所有安装Oracle过程中的默认选项。

图3  Oracle默认安装设置

4.       单击“安装”按钮，进入安装界面，这一过程经历时间比较长，根据计算机的性能不同有很大差别。

图4  Oracle安装

5.       上图完成后，进入了各种Oracle工具的安装阶段，包括网络配置向导，iSQL*plus等（后面课程中讲解）。如下图所示：

图5  Oracle各种工具的安装

6.       接下来自动启动DBCA（Database Configuration Assistant）进入创建默认数据库阶段。

图6  DBCA下安装数据库

         Oracle中的数据库主要是指存放数据的文件，这些文件在Oracle安装完成后，在计算机硬盘上都能找到，包括数据文件、控制文件和数据库日志文件。

数据库创建后会有一系列为该数据库提供服务的内存空间和后台进程，称为该数据库的实例。每一个数据库至少会有一个实例为其服务。实例中的内存结构称为系统全局区（SGA），系统会根据当前计算机系统的性能给SGA分配非常可观的内存空间。

Oracle创建数据库不能像SQL Server那样用一个简单的CREATE DATABASE命令就能完成，在创建数据库的过程中还需要配置各种参数。虽然有DBCA工具向导，但是仍然需要进行比较麻烦的配置。

7.       数据库创建完毕后，需要设置数据库的默认用户。Oracle中为管理员预置了两个用户分别是SYS和SYSTEM。同时Oracle为程序测试提供了一个普通用户scott，口令管理中，可以对数据库用户设置密码，设置是否锁定。 Oracle客户端使用用户名和密码登录Oracle系统后才能对数据库操作。

图7  DBCA下的口令管理

图8  为system,sys,scott用户设置密码

         默认的用户中，SYS和SYSTEM用户是没有锁定的，安装成功后可以直接使用，SCOTT用户默认为锁定状态，因此不能直接使用，需要把SCOTT用户设定为非锁定状态才能正常使用。

         这一步完成后，Oracle系统安装成功。

Oracle服务器安装成功后，就可以通过客户端工具连接Oracle服务器了，可以到Oracle官方下载Oracle专用的客户端软件，大多客户端工具都是基于Oracle客户端软件的。接下来介绍几种常用的Oracle客户端工具。

²  SQL*Plus工具

该工具是Oracle系统默认安装下，自带的一个客户端工具。在Windows命令行中输入“sqlplusw”命令，就能够启动该工具了。

图9  SQL*Plus工具

输入用户名和密码后，如果SQL*Plus与数据库服务器在同一台计算机上，并且当前服务器下只有一个数据库实例，那么“主机字符串”可以不用填写。

SQL*Plus连接成功后就如图所示：

图10  SQL*Plus工具登录后

²  SQL*Plus命令行工具

该命令行工具，提供了与数据库交互的能力和维护数据库的能力，包括了Oracle自带的SQL*Plus工具的全部功能，在Oracle管理中经常使用。在命令行中输入：“sqlplus /nolog”即可启动该工具。如下图：

图11  启动SQL*Plus命令行工具

         输入“sqlplus /nolog”命令后，只是启动了一个客户端进程，并没有与服务器连接，连接到Oracle服务器的命令是：

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.       启动Net Configuration Assistant。选择“本地Net服务名配置”选项。如下图所示：

图13  启动Net Configuration Assistant

2.       选择“下一步”，本步骤可以对本地网络服务名进行添加，删除，测试是否正常连接等操作，选择“添加”选项。

图14  Net Configuration Assistant

3.       点击“下一步”，填写服务名，该服务名就是Oracle安装时（图1），为数据库取的全局数据库名。

图15  服务名配置

4.       点击“下一步”，选择服务需要的协议，默认是TCP协议。推荐使用默认的TCP协议。

图16  选择协议

5.       点击“下一步”，输入主机名，主机名可以是计算机名称，也可以是一个IP地址，主机如果是本机，可以使用本机计算机名称、“localhost”、“127.0.0.1”、或者本机的IP地址。

图17  输入主机名和端口

6.       单击“下一步”，选择“是，进行测试”选项。进入下图界面。

图18  测试成功

在测试时，默认采用的用户名和密码是system/manager进行测试，如果用户system的密码不是“manager”，有可能测试通不过，更改登录后，输入正确的用户名和密码后再进行测试即可。

7.       测试成功后，单击“下一步”，出现如下界面，这一步是为本地网络服务命名，即图12中的服务器连接字符串名。

图19  为网络服务名命名

         点击“下一步”，配置就完成了，进入tnsnames.ora文件中查看，就出现了如图12中的内容。

²  PL/SQL Developer工具

在实际Oracle开发中，经常使用一个功能强大的第三方工具：“PL/SQL Developer”工具。PL/SQL Developer基本上可以实现Oracle开发中的任何操作。它运行在客户端时必须先安装Oracle客户端，并且通过网络配置向导配置网络服务名后才能正常与服务器连接。

图20 PL/SQL Developer

Oracle在windows中安装完成后，会安装很多服务，下面介绍几个主要的服务。

图21 Oracle服务

Ø  OracleService+服务名，该服务是数据库启动的基础，只有该服务启动了，Oracle数据库才能正常启动。这是必须启动的服务。

Ø  OracleOraDb10g_home1TNSListener，该服务是服务器端为客户端提供的监听服务，只有该服务在服务器上正常启动，客户端才能连接到服务器。该监听服务接收客户端发出的请求，然后将请求传递给数据库服务器。一旦建立了连接，客户端和数据库服务器就能直接通信了。

Ø  OracleOraDb10g_home1iSQL*Plus，该服务提供了用浏览器对数据库中数据操作的方式。该服务启动后，就可以使用浏览器进行远程登录并进行数据库操作了。如下图所示：

图22 iSQL*Plus

Ø  OracleDBConsole+服务名，Oracle10g中的一个新服务。在Oracle9i之前，Oracle官方提供了一个基于图形界面的企业管理器（EM），从Oracle10g开始，Oracle提供了一个基于B/S的企业管理器，在操作系统的命令行中输入命令：emctl start dbconsole，就可以启动OracleDbConsole服务，如下图所示：

图23 EM服务的启动

         服务启动之后，就可以在浏览器中输入上图中进入EM的地址，使用B/S方式管理Oracle服务器。

OracleService启动动后，就可以对数据库进行管理了，Oracle的启动和关闭是最基本的命令，在SQL*Plus中，启动Oracle必须是sys用户，命令格式是：

startup open

图24 Oracle服务启动

         Oracle服务关闭用命令：shutdown immediate

图25 Oracle服务关闭

Oracle中，一般不会轻易在一个服务器上创建多个数据库，在一个数据库中，不同的项目由不同的用户访问，每一个用户拥有自身创建的数据库对象，因此用户的概念在Oracle中非常重要。Oracle的用户可以用CREATE USER命令来创建。其语法是：

语法结构：创建用户

语法解析：

         LOCK|UNLOCK创建用户时是否锁定，默认为锁定状态。锁定的用户无法正常的登录进行数据库操作。

代码演示：创建用户

         尽管用户成功创建，但是还不能正常的登录Oracle数据库系统，因为该用户还没有任何权限。如果用户能够正常登录，至少需要CREATE SESSION系统权限。

         Oracle用户对数据库管理或对象操作的权利，分为系统权限和数据库对象权限。系统权限比如：CREATE SESSION，CREATE TABLE等，拥有系统权限的用户，允许拥有相应的系统操作。数据库对象权限，比如对表中的数据进行增删改操作等，拥有数据库对象权限的用户可以对所拥有的对象进行对应的操作。

         还有一个概念就是数据库角色（role），数据库角色就是若干个系统权限的集合。下面介绍几个常用角色：

Ø  CONNECT角色，主要应用在临时用户，特别是那些不需要建表的用户，通常只赋予他们CONNECT role。CONNECT是使用Oracle的简单权限，拥有CONNECT角色的用户，可以与服务器建立连接会话（session，客户端对服务器连接，称为会话）。

Ø  RESOURCE角色，更可靠和正式的数据库用户可以授予RESOURCE role。RESOURCE提供给用户另外的权限以创建他们自己的表、序列、过程（procedure）、触发器（trigger）、索引（index）等。

Ø  DBA角色，DBA role拥有所有的系统权限----包括无限制的空间限额和给其他用户授予各种权限的能力。用户SYSTEM拥有DBA角色。

一般情况下，一个普通的用户（如SCOTT），拥有CONNECT和RESOURCE两个角色即可进行常规的数据库开发工作。

         可以把某个权限授予某个角色，可以把权限、角色授予某个用户。系统权限只能由DBA用户授权，对象权限由拥有该对象的用户授权，授权语法是：

语法结构：授权

代码演示：授权

语法结构：其他操作

Ø  Oracle是基于对象的关系型数据库，Oracle产品免费，服务收费。

Ø  Oracle安装后默认会有两个管理员用户（system，sys）和一个普通用户Scott。

Ø  Sql*plus是Oracle管理和数据操作的客户端工具。

Ø  客户端链接服务器前，服务器要启动监听服务，并且客户端工具要安装Oracle客户端，并且在客户端要建立本地网络服务名。

Ø  Oracle服务和监听启动后才能对数据库进行操作。

Ø  用startup命令启动数据库，用shutdown命令关闭数据库。

Ø  Oracle的角色包括了一系列系统权限和普通对象权限，可以把权限授权给角色，把权限或者角色授权给用户。

1.       描述Oracle安装过程中的关键点。

2.       描述创建本地网络服务名的步骤。

3.       描述Oracle主要服务的作用。

4.       Oracle使用什么命令才能启动和关闭。

5.       什么是Oracle权限和角色？他们的关系是什么？

6.       创建一个用户，并授权CONNECT和RESOURCE。

第2章

SQL数据操作和查询

在第一学期的SQL Server学习中，已经知道，SQL是结构化查询语言（Structured Query Language[D1] ），专门用于数据存取、数据更新及数据库管理等操作。并且已经学习了用SQL语句对数据库的表进行增删改查的操作。

在Oracle开发中，客户端把SQL语句发送给服务器，服务器对SQL语句进行编译、执行，把执行的结果返回给客户端。Oracle SQL语句由如下命令组成：

Ø  数据定义语言Data Definition Language（DDL）[D2] ，包括CREATE（创建）命令、ALTER（修改）命令、DROP（删除）命令等。

Ø  数据操纵语言Data Manipulation Language（DML）[D3] ，包括INSERT（插入）命令、UPDATE（更新）命令、DELETE（删除）命令、SELECT … FOR UPDATE（查询）等。

Ø  数据查询语言Data Query Language（DQL），包括基本查询语句、Order By子句、Group By子句等。

Ø  事务控制语言（TCL），包括COMMIT（提交）命令、SAVEPOINT（保存点）命令、ROLLBACK（回滚）命令。

Ø  数据控制语言（DCL），GRANT（授权）命令、REVOKE（撤销）命令。

目前主流的数据库产品（比如：SQL Server、Oracle）都支持标准的SQL语句。数据定义语言，表的增删改操作，数据的简单查询，事务的提交和回滚，权限的授权和撤销等，Oracle与SQL Server在操作上基本一致。

Oracle数据库的核心是表，表中的列使用到的常见数据类型如下：

表1  Oracle的部分数据类型

         对应NUMBER类型的示例：

表2  Number示例

         对于日期类型，可以使用sysdate内置函数可以获取当前的系统日期和时间，返回DATE类型，用systimestamp函数可以返回当前日期、时间和时区。

图1  sysdate和sysTimestamp

         Oracle的查询中，必须使用“select 列… from 表”的完整语法，当查询单行函数的时候，from后面使用DUAL表，dual表在系统中只有一行一列，该表在输出单行函数时为了select…from的语法完整性而使用。

Oracle创建表同SQL Server一样，使用CREATE TABLE命令来完成。创建约束则使用如下命令：

语法格式：ALTERTABLE命令

不论创建表还是约束，与SQL Server基本相同，注意：在Oracle中default是一个值，而SQL Server中default是一个约束[D5] ，因此Oracle的default设置可以在建表的时候创建。

案例1：创建一个学生信息（INFOS）表和约束

代码演示：Oracle创建表和约束

代码解析：

①  在Oracle代码中，“/”执行缓存区中的语句，由于缓冲区中只存储一条刚刚保存过语句，由于每条语句没有用分号结尾，只是保存在缓冲区，因此每条语句后面都有单独一行“/”。

②  创建一个主键约束[D9] 。

③  与 ④ ⑤ ⑥ ⑦一起创建各种check约束。其中⑦是唯一约束，表示该列值是唯一的，列中的值不能重复。

Oracle中创建外键约束与SQL Server相同。比如：现有成绩表定义如下：

案例2：创建一个成绩表（SCORES）表和约束

代码演示：Oracle创建表和约束

代码解析：

①  SQL Server中可以使用identify创建自动增长列，但是Oracle中的自动增长需要借助序列（Sequence）完成，在后面章节中讲解。

②  Oracle中的外键约束定义。

数据操纵语言（DML）用于对数据库的表中数据进行添加、修改、删除和SELECT…For UPDATE(后面专门学习该查询)操作。对比一期学习过的SQL Server操作，接下来一一介绍在Oracle中的操作。

²  简单查询

数据查询是用SELECT命令从数据库的表中提取信息。SELECT语句的语法是：

语法结构：简单查询

语法解析：

1.       *表示表中的所有列。

2.       列名可以选择若干个表中的列名，各个列表中间用逗号分隔。

3.       表达式可以是列名、函数、常数等组成的表达式。

4.       WHERE子句是查询的条件。

5.       ORDER BY 要求在查询的结果中排序，默认是升序[D10] 。

图2  数据查询

         Oracle中可以把查询的结果根据结果集中的表结构和数据形成一张新表。

语法结构：根据结果集创建表

代码演示：根据结果集创建表

         使用上面命令创建的新表中，不存在任何约束，并且把查询的数据一起插入到新表中。如果只复制表结构，只需使查询的条件不成立（比如where 1=2），就不会查询从出任何数据，从而复制一个表结构。

代码演示：复制表结构

²  数据插入

用INSERT命令完成对数据的插入。

语法结构：根据结果集创建表

语法解析：

1.       列名可以省略。当省略列名时，默认是表中的所有列名，列名顺序为表定义中列的先后顺序。

2.       值的数量和顺序要与列名的数量和顺序一致。值的类型与列名的类型一致。

代码演示：向INFOS表和SCORES表中插入数据

代码解析：

①  表名后面缺省了列名，默认是表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表示秒

③  在遇到存在默认值的列时，可以使用default值代替。

④  commit是把用户操作（添加、删除、修改操作）提交，只有提交操作后，数据才能真正更新到表中，否则其他用户无法查询到当前用户操作的结果。

在Oracle中，一个INSERT命令可以把一个结果集一次性插入到一张表中。使用的语句是：INSERT INTO 表 SELECT子句，如下示例：

代码演示：INSERT向表中插入一个结果集

在这种语法下，要求结果集中每一列的数据类型必须与表中的每一列的数据类型一致，结果集中的列的数量与表中的列的数量一致。比如表INFOS2，该表的结构与INFO表一样，那么可以把INFO表中的所有记录一次性插入到INFOS2表中。

Oracle的简单查询和SQL Server一样都可以在查询列中使用常量，如图：

图3  Select中的常量

可以使用刚才的做法，把该结果集中的数据插入到表INFOS中。

代码演示：INSERT向表中插入一个常量结果集

²  更新数据

Oracle在表中更新数据的语法是：

语法结构：UPDATE操作

代码演示：UPDATE操作

²  删除数据

Oracle在表中删除数据的语法是：

语法结构：DELETE操作

代码演示：DELETE操作

²  TRUNCATE

在数据库操作中，TRUNCATE[D11] 命令（是一个DDL命令）可以把表中的所有数据一次性全部删除，语法是：

语法结构：TRUNCATE

TRUNCATE和DELETE都能把表中的数据全部删除，他们的区别是：

1.       TRUNCATE是DDL命令，删除的数据不能恢复；DELETE命令是DML命令，删除后的数据可以通过日志文件恢复。

2.       如果一个表中数据记录很多，TRUNCATE相对DELETE速度快。

由于TRUNCATE命令比较危险，因此在实际开发中，TRUNCATE命令慎用。

Oracle开发中，依然存在算术运算，关系运算，和逻辑运算。

²  算术运算

Oracle中的算术运算符，没有C#中的算术运算符丰富，只有+、-、*、/四个，其中除号(/)的结果是浮点数。求余运算只能借助函数：MOD(x,y)：返回x除以y的余数。

案例3：每名员工年终奖是2000元，请显示基本工资在2000元以上的员工的月工资，年总工资。

该案例的表请参见本章练习的附表1、附表2、附表3，这三张表是ORACLE 10g自带的。

代码演示：查询中的算术运算

²  关系运算和逻辑运算

Oracle中Where子句经中经常见到关系运算和逻辑运算，常见的关系运算有：

表3  Oracle的关系运算符

逻辑运算符有三个：AND、OR、NOT

关系运算和逻辑运算与前面SQL Server学习过的一致。

²  字符串连接操作符（||）

在Oracle中，字符串的连接用双竖线（||）表示。比如，在EMP表中，查询工资在2000元以上的姓名以及工作。

代码演示：字符串连接

代码解析：

①  Oracle中字符串可以用单引号，也可以用双引号，在别名中存在空格时，必须用双引号。在表名、列名时用双引号。

在第一期学习过SQL的简单查询和连接查询。现在学习一些新的SQL操作符。

²  消除重复行

在Oracle查询中结果中，可能出现若干行相同的情况，那么可以使用DISTINCT消除重复行。具体的用法如示例：

代码演示：DISTINCT消除重复行

²  NULL操作

如果某条记录中有缺少的数据值，就是空值（NULL值）。空值不等于0或者空格，空值是指未赋值、未知或不可用的值。任何数据类型的列都可以包括NULL值，除非该列被定义为非空或者主键。

代码演示：EMP中的NULL值

         在查询条件中NULL值用IS NULL作条件，非NULL值用NOT IS NULL做条件。   

案例4：查询EMP表中没有发奖金的员工。

代码演示：NULL值查询

²  IN 操作

在Where子句中可以使用IN操作符来查询其列值在指定的列表中的行。比如：查询出工作职责是SALESMAN、PRESIDENT或者ANALYST的员工。条件有两种表示方法：

1.       WHERE job = 'SALESMAN ' OR job = 'PRESIDENT ' OR job = 'ANALYST '

2.       WHERE job IN ('SALESMAN', 'PRESIDENT', 'ANALYST')

代码演示：IN操作

         对应IN操作的还有NOT IN，用法一样，结果相反。

²  BETWEEN…AND…

在WHERE子句中，可以使用BETWEEN操作符来查询列值包含在指定区间内的行。比如，查询工资从1000到2000之间的员工。可以使用传统方法：

         WHERE SAL>=1000 AND SAL<=2000

         也可以使用：

             WHERE SAL BETWEEN 1000 AND 2000

         BWTWEEN操作所指定的范围也包括边界。

代码演示：BETWEEN操作

²  LIKE模糊查询

在一些查询时，可能把握不准需要查询的确切值，比如百度搜索时输入关键字即可查询出相关的结果，这种查询称为模糊查询。模糊查询使用LIKE关键字通过字符匹配检索出所需要的数据行。字符匹配操作可以使用通配符“%”和“_”:

Ø  %：表示零个或者多个任意字符。

Ø  _：代表一个任意字符。

语法是：LIKE '字符串'[ESCAPE '字符']。匹配的字符串中，ESCAPE[D12] 后面的“字符”作为转义字符。与一期SQLServer中ESCAPE用法相同。

表4  通配符示例

案例5：显示员工名称以J开头以S结尾的员工的姓名、工资和工资。

代码演示：LIKE操作

²  集合运算

集合运算就是将两个或者多个结果集组合成为一个结果集。集合运算包括：

Ø  INTERSECT[D13] (交集)，返回两个查询共有的记录。

Ø  UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。

Ø  UNION(并集)，返回各个查询的所有记录，不包括重复记录。

Ø  MINUS[D14] (补集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。

当使用集合操作的时候，要注意：查询所返回的列数以及列的类型必须匹配，列名可以不同。

         案例6：查询出dept表中哪个部门下没有员工。只需求出dept表中的部门号和emp表中的部门号的补集即可。

代码演示：求补运算

         前面学习过可以通过insert into …select把一个结果集插入到另一张结构相同的表中，因此可以使用union把若干条记录一次性插入到一张表中。

代码演示：用union插入多条数据

²  连接查询

在SQL Server中已经学习过内联接(inner join)、外联接(outer join)，外联接又分为左外联接(left outer join)和右外联接(right outer join)。Oracle中对两个表或者若干表之间的外联接用（+）表示。

案例7：请查询出工资大于2000元的，员工姓名，部门，工作，工资。

由于部门名称在dept中，其他的信息在emp表中，需要内联接才能完成。

代码演示：内联接

         也可以使用SQL/92标准中的内联接：

代码演示：内联接

这里INNER JOIN中，关键字INNER可以省略。

案例8：请查询出每个部门下的员工姓名，工资。

案例分析：

Emp表用外键deptno引用Dept表中的deptno，在Dept表中如果有某些部门没有员工，那么用内联接，没有员工的部门将无法显示，因此必须以Dept表为基准的外联接。

代码演示：外联接

代码解析：

①   (+)：Oracle专用的联接符，在条件中出现在左边指右外联接，出现在右边指左外联接。

         也可以使用SQL/92标准的写法：

代码演示：外联接

         这里RIGHT OUTER JOIN中，关键字OUTER可以省略。

Ø  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。

Ø  联接查询有内联接和外联接。

1.       创建一查询，显示与Blake在同一部门工作的雇员的项目和受雇日期，但是Blake不包含在内。

2.       显示位置在Dallas的部门内的雇员姓名、变化以及工作。

3.       显示被King直接管理的雇员的姓名以及工资。

4.       创建一查询，显示能获得与Scott一样工资和奖金的其他雇员的姓名、受雇日期以及工资。

附表1：Scott表中的EMP表：员工表

附表2：Scott表中的DEPT表：部门表

附表3：Scott表中的SALGRADE表：工资等级表

第3章

子查询和常用函数

子查询在SELECT、UPDATE、DELETE语句内部可以出现SELECT语句。内部的SELECT语句结果可以作为外部语句中条件子句的一部分，也可以作为外部查询的临时表。子查询的类型有：

1.       单行子查询：不向外部返回结果，或者只返回一行结果。

2.       多行子查询：向外部返回零行、一行或者多行结果。

案例1：查询出销售部（SALES）下面的员工姓名，工作，工资。

案例分析

该问题可以用联接查询实现，由于所需的结果信息都在Emp表中，可以先从Dept表中查询出销售部对应的部门号，然后根据当前部门号再到Emp表中查询出符合该部门的员工记录即可。从销售表中查询出的结果可以作为Emp表中查询的条件，SQL语句实现如下：

代码演示：单行子查询

代码解析：

①  内部查询的结果作为外部查询的条件。

需要注意：

Ø  如果内部查询不返回任何记录，则外部条件中字段DEPTNO与NULL比较永远为假，也就是说外部查询不返还任何结果。

Ø  在单行子查询中外部查询可以使用=、>、<、>=、<=、<>等比较运算符。

Ø  内部查询返回的结果必须与外部查询条件中的字段（DEPTNO）匹配。

Ø  如果内部查询返回多行结果则出现错误。

案例2：查询出Emp表中比任意一个销售员(“SALESMAN”)工资低的员工姓名、工作、工资。

案例分析

销售员在Emp表中有很多条记录，每个人工资不相等，如果返回“比任意员工的工资还低”的条件，返回比“最高工资还低”即可。如果用子查询做，子查询中就会返回多条记录。用普通的关系符（>、<等）运行就会出错。这时候需要用关键字ANY。ANY放在比较运算符后面，表示“任意”的意思。

代码演示：ANY子查询

代码解析：

①  <any:比子查询结果中任意的值都小，也就是说，比子查询结果中最大值还小，那么同理>any表示比子查询结果中最小的还大。

案例3：查询出比所有销售员的工资都高的员工姓名，工作，工资。

案例分析

ANY可以表示任意的，但本案例中要求比所有销售员工资都高，那么就要使用另外一个关键字ALL。ALL与关系操作符一起使用，表示与子查询中所有元素比较。

代码演示：ALL子查询

代码解析：

①  >ALL：比子查询结果中所有值还要大，也就是说，比子查询结果中最大值还要大。<ALL表示比最小值还要小。

对于子查询还可以使用IN和NOT IN操作符进行操作。

在Oracle的表的使用过程中，实际表中还有一些附加的列，称为伪列。伪列就像表中的列一样，但是在表中并不存储。伪列只能查询，不能进行增删改操作。接下来学习两个伪列：ROWID和ROWNUM。

²  ROWID

表中的每一行在数据文件中都有一个物理地址，ROWID伪列返回的就是该行的物理地址。使用ROWID可以快速的定位表中的某一行。ROWID值可以唯一的标识表中的一行。由于ROWID返回的是该行的物理地址，因此使用ROWID可以显示行是如何存储的。

代码演示：ROWID

²  ROWNUM

在查询的结果集中，ROWNUM为结果集中每一行标识一个行号，第一行返回1，第二行返回2，以此类推。通过ROWNUM伪列可以限制查询结果集中返回的行数。

案例4：查询出员工表中前5名员工的姓名，工作，工资。

代码演示：ROWNUM

案例5：查询出工资最高的前5名员工的姓名、工资和工资。

案例分析

“工资最高的前5名”需要先降序排序，再取前5名，但是生成ROWNUM操作比排序要早，排序时已经连同ROWNUM一起排序了，因此不能直接在案例1的语句中直接加上Order by就行，而是需要对排序的结果重新做二次查询，产生新的ROWNUM才能作为查询的条件依据。

代码演示：ROWNUM应用

代码解析：

①  T是子查询②的别名，这里的ROWNUM是第二次查询后的ROWNUM。

案例6：查询出表EMP中第5条到第10条之间的记录。

案例分析

这是分页的应用。在查询条件中，如果查询条件中ROWNUM大于某一正整数，则不返还任何结果。

代码演示：ROWNUM分页

代码解析：

①  内部查询中得到ROWNUM 并且用别名R记录，供外层条件③使用。

②  内部查询的ROWNUM，与外出的ROWNUM列是平等的两列。

③  使用的R是内层产生的ROWNUM，在外层看来，内层查询的ROWNUM是正常的一列。

Oracle SQL提供了用于执行特定操作的专用函数。这些函数大大增强了SQL语言的功能。函数可以接受零个或者多个输入参数，并返回一个输出结果。Oracle数据库中主要使用两种类型的函数：

1.         单行函数：对每一个函数应用在表的记录中时，只能输入一行结果，返回一个结果，比如：MOD(x,y)返回x除以y的余数（x和y可以是两个整数，也可以是表中的整数列）。常用的单行函数有：

Ø  字符函数：对字符串操作。

Ø  数字函数：对数字进行计算，返回一个数字。

Ø  转换函数：可以将一种数据类型转换为另外一种数据类型。

Ø  日期函数：对日期和时间进行处理。

2.         聚合函数：聚合函数同时可以对多行数据进行操作，并返回一个结果。比如SUM(x)返回结果集中x列的总合。

²  字符函数

字符函数接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式。下表列出了常用的字符函数。

表1  字符函数

表2  字符函数示例

²  数字函数

数字函数接受数字参数，参数可以来自表中的一列，也可以是一个数字表达式。

表3  数字函数

说明：

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)，返回指定日期当月的最后一天。

图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非常相似，只是不对日期进行舍入，直接截取到对应格式的第一天。

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对日期的处理

代码解析：

①  在格式化字符串中，使用双引号对非格式化字符进行引用。

针对数字的格式化，格式化字符有：

表4  数字格式化字符

代码演示：TO_CHAR对数字的处理

2.         TO_DATE(x [,fmt])

把一个字符串以fmt格式转换为一个日期类型，前面已经学习过。

3.         TO_NUMBER(x[,fmt])

把一个字符串以fmt格式转换为一个数字。fmt格式字符参考表3。

代码演示：TO_NUM函数

²  其他单行函数

1.         NVL(x,value)

如果x为空，返回value，否则返回x。

         案例7：对工资是2000元以下的员工，如果没有发奖金，每人奖金100元。

代码演示：NVL函数

2.         NVL2(x,value1,value2)

如果x非空，返回value1，否则返回value2。

案例8：对EMP表中工资为2000元以下的员工，如果没有奖金，则奖金为200元，如果有奖金，则在原来的奖金基础上加100元。

代码演示：NVL2函数

²  聚合函数

聚合函数同时对一组数据进行操作，返回一行结果，比如计算一组数据的总和，平均值等。

表 5  聚合函数

案例9：求本月所有员工的基本工资总和。

代码演示：SUM函数

案例10：求不同部门的平均工资。

代码演示：AVG函数下的分组查询

Ø  Oracle常用函数有字符相关的函数、数字相关的函数、日期相关的函数、转换函数等。

Ø  EXTRACT函数能够获取日期中的某个字段的值。

Ø  TO_CHAR函数能够把数字和日期转换成固定的字符串格式。TO_DATE         函数能够把固定格式的字符串转换为日期类型。

Ø  子查询中有返回单行的子查询和返回多行的子查询。

Ø  Oracle中存在ROWID、ROWNUM等伪列。

1.       描述TO_CHAR和TO_DATE函数的用法。

2.       描述EXTRACT函数的用法。

3.       你知道有哪些关于日期函数的用法？

第4章

表空间、数据库对象

数据库对象是数据库的组成部分，常常用CREATE命令进行创建，可以使用ALTER命令修改，用DROP执行删除操作。前面已经接触过的数据库对象有表、用户等。

今天将学习更多的Oracle数据库对象：

Ø  同义词：就是给数据库对象一个别名。

Ø  序列：Oracle中实现增长的对象。

Ø  视图：预定义的查询，作为表一样的查询使用，是一张虚拟表。

Ø  索引：对数据库表中的某些列进行排序，便于提高查询效率。

同义词（Synonym）是数据库对象的一个别名，Oracle可以为表、视图、序列、过程、函数、程序包等指定一个别名。同义词有两种类型：

Ø  私有同义词：拥有CREATE SYNONYM权限的用户（包括非管理员用户）即可创建私有同义词，创建的私有同义词只能由当前用户使用。

Ø  公有同义词：系统管理员可以创建公有同义词，公有同义词可以被所有用户访问。

         创建同义词的语法是：

语法结构：同义词

语法解析：

①  CREATE [OR REPLACE：]表示在创建同义词时，如果该同义词已经存在，那么就用新创建的同义词代替旧同义词。

②  PULBIC：创建公有同义词时使用的关键字，一般情况下不需要创建公有同义词。

③  Oracle中一个用户可以创建表、视图等多种数据库对象，一个用户和该用户下的所有数据库对象的集合称为Schema（中文称为模式或者方案），用户名就是Schema名。一个数据库对象的全称是：用户名.对象名，即schema.object_name。

如果一个用户有权限访问其他用户对象时，就可以使用全称来访问。比如：

代码演示：System用户访问Scott用户的Emp表

代码解析：

①  管理员用户可以访问任何用户的数据库对象，SYSTEM用户访问SCOTT用户的EMP表时，必须使用SCOTT.EMP。

案例1：创建一个用户XiaoMei，该用户拥有CONNECT角色和RESOURCE角色。为SCOTT用户的EMP表创建同义词，并通过同义词访问该EMP表。

代码演示：创建同义词并访问

代码解析：

①  在管理员用户下创建用户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就可以像在本地的表一样使用。

删除同义词使用的语法是：

语法结构：删除同义词

语法解析：

①  PUBLIC：删除公共同义词。

②  同义词的删除只能被拥有同义词对象的用户或者管理员删除。

③  此命令只能删除同义词，不能删除同义词下的源对象。

序列(Sequence)是用来生成连续的整数数据的对象。序列常常用来作为主键中增长列，序列中的可以升序生成，也可以降序生成。创建序列的语法是：

语法结构：创建序列

语法解析：

①  START WITH：从某一个整数开始，升序默认值是1，降序默认值是-1。

②  INCREMENT BY：增长数。如果是正数则升序生成，如果是负数则降序生成。升序默认值是1，降序默认值是-1。

③  MAXVALUE：指最大值。

④  NOMAXVALUE：这是最大值的默认选项，升序的最大值是：1027，降序默认值是-1。

⑤  MINVALUE：指最小值。

⑥  NOMINVALUE：这是默认值选项，升序默认值是1，降序默认值是-1026。

⑦  CYCLE：表示如果升序达到最大值后，从最小值重新开始；如果是降序序列，达到最小值后，从最大值重新开始。

⑧  NOCYCLE：表示不重新开始，序列升序达到最大值、降序达到最小值后就报错。默认NOCYCLE。

⑨  CACHE：使用CACHE选项时，该序列会根据序列规则预生成一组序列号。保留在内存中，当使用下一个序列号时，可以更快的响应。当内存中的序列号用完时，系统再生成一组新的序列号，并保存在缓存中，这样可以提高生成序列号的效率。Oracle默认会生产20个序列号。

⑩  NOCACHE：不预先在内存中生成序列号。

案例2：创建一个从1开始，默认最大值，每次增长1的序列，要求NOCYCLE，缓存中有30个预先分配好的序列号。

代码演示：生成序列号

序列创建之后，可以通过序列对象的CURRVAL和NEXTVAL两个“伪列”分别访问该序列的当前值和下一个值。

代码演示：序列使用

         使用ALTER SEQUENCE可以修改序列，在修改序列时有如下限制：

1.       不能修改序列的初始值。

2.       最小值不能大于当前值。

3.       最大值不能小于当前值。

使用DROP SEQUENCE命令可以删除一个序列对象。

代码演示：序列修改和删除

视图（View）实际上是一张或者多张表上的预定义查询，这些表称为基表。从视图中查询信息与从表中查询信息的方法完全相同。只需要简单的SELECT…FROM即可。

视图具有以下优点：

1.       可以限制用户只能通过视图检索数据。这样就可以对最终用户屏蔽建表时底层的基表。

2.       可以将复杂的查询保存为视图。可以对最终用户屏蔽一定的复杂性。

3.       限制某个视图只能访问基表中的部分列或者部分行的特定数据。这样可以实现一定的安全性。

4.       从多张基表中按一定的业务逻辑抽出用户关心的部分，形成一张虚拟表。

语法结构：创建视图

语法解析：

1.       OR REPLACE：如果视图已经存在，则替换旧视图。

2.       FORCE：即使基表不存在，也可以创建该视图，但是该视图不能正常使用，当基表创建成功后，视图才能正常使用。

3.       NOFORCE：如果基表不存在，无法创建视图，该项是默认选项。

4.       WITH READ ONLY：默认可以通过视图对基表执行增删改操作，但是有很多在基表上的限制（比如：基表中某列不能为空，但是该列没有出现在视图中，则不能通过视图执行insert操作），WITH READ ONLY说明视图是只读视图，不能通过该视图进行增删改操作。现实开发中，基本上不通过视图对表中的数据进行增删改操作。

案例3：基于EMP表和DEPT表创建视图

代码演示：视图

代码解析：

①   对视图可以像表一样进行查询。该视图中隐藏了员工的工资。

删除视图可以使用“DROP VIEW 视图名称”，删除视图不会影响基表的数据。

当我们在某本书中查找特定的章节内容时，可以先从书的目录着手，找到该章节所在的页码，然后快速的定位到该页。这种做法的前提是页面编号是有序的。如果页码无序，就只能从第一页开始，一页页的查找了。

数据库中索引（Index）的概念与目录的概念非常类似。如果某列出现在查询的条件中，而该列的数据是无序的，查询时只能从第一行开始一行一行的匹配。创建索引就是对某些特定列中的数据排序，生成独立的索引表。在某列上创建索引后，如果该列出现在查询条件中，Oracle会自动的引用该索引，先从索引表中查询出符合条件记录的ROWID，由于ROWID是记录的物理地址，因此可以根据ROWID快速的定位到具体的记录，表中的数据非常多时，引用索引带来的查询效率非常可观。

Oracle数据库会为表的主键和包含唯一约束的列自动创建索引。索引可以提高查询的效率，但是在数据增删改时需要更新索引，因此索引对增删改时会有负面影响。

语法结构：创建索引

语法解析：

1.       UNIQUE:指定索引列上的值必须是唯一的。称为唯一索引。

2.       index_name：指定索引名。

3.       tabl_name：指定要为哪个表创建索引。

4.       column_name：指定要对哪个列创建索引。我们也可以对多列创建索引；这种索引称为组合索引。

案例4：为EMP表的ENAME列创建创建唯一索引，为EMP表的工资列创建普通索引，把JOB列先变为小写再创建索引。

代码演示：创建索引

代码解析：

①  为SCOTT.EMP表的ENAME列创建唯一索引。

②  为SCOTT.EMP表的SAL列创建索引。

③  在查询中可能经常使用job的小写作为条件的表达式，因此创建索引时，可以先对JOB列中的所有值转换为小写后创建索引，而这时需要使用lower函数，这种索引称为基于函数的索引。

在select语句查询时，Oracle系统会自动为查询条件上的列应用索引。索引就是对某一列进行排序，因此在索引列上，重复值越少，索引的效果越明显。

Oracle可以为一些列值重复非常多且值有限的列（比如性别列）上创建位图索引。关于Oracle更多的索引类型（比如反向键索引等），请参考Oracle官方文档。

在数据库系统中，存储空间是较为重要的资源，合理利用空间，不但能节省空间，还可以提高系统的效率和工作性能。Oracle可以存放海量数据，所有数据都在数据文件中存储。而数据文件大小受操作系统限制，并且过大的数据文件对数据的存取性能影响非常大。同时Oracle是跨平台的数据库，Oracle数据可以轻松的在不同平台上移植，那么如何才能提供统一存取格式的大容量呢？Oracle采用表空间来解决。

表空间只是一个逻辑概念，若干操作系统文件（文件可以不是很大）可以组成一个表空间。表空间统一管理空间中的数据文件，一个数据文件只能属于一个表空间。一个数据库空间由若干个表空间组成。如图所示：

图1  数据空间、表空间和数据文件

         Oracle中所有的数据（包括系统数据），全部保存在表空间中，常见的表空间有：

Ø  系统表空间：存放系统数据，系统表空间在数据库创建时创建。表空间名称为SYSTEM。存放数据字典和视图以及数据库结构等重要系统数据信息，在运行时如果SYSTEM空间不足，对数据库影响会比较大，虽然在系统运行过程中可以通过命令扩充空间，但还是会影响数据库的性能，因此有必要在创建数据库时适当的把数据文件设置大一些。

Ø  TMEP表空间：临时表空间，安装数据库时创建，可以在运行时通过命令增大临时表空间。临时表空间的重要作用是数据排序。比如当用户执行了诸如Order by等命令后，服务器需要对所选取数据进行排序，如果数据很大，内存的排序区可能装不下太大数据，就需要把一些中间的排序结果写在硬盘的临时表空间中。

Ø  用户表自定义空间：用户可以通过CREATE TABLESPACE命令创建表空间。

创建表空间需要考虑数据库对分区（Extent，一个Oracle分区是数据库文件中一段连续的空间，Oracle分区是Oracle管理中最小的单位）的管理，比如当一个表创建后先申请一个分区，在Insert执行过程中，如果分区数据已满，需要重新申请另外的分区。如果一个数据库中的分区大小不一，创建表空间时需要考虑一系列问题。因此在Oracle8i之后，创建表空间都推荐使用“本地管理表空间”，这种表空间中的分区是一个固定大小的值，创建表空间的语法是：

语法结构：创建表空间

语法解析：

1.       文件名包括完整路径和文件名，每个数据文件定义了文件的初始大小，初始大小一般以“M”为单位。一个表空间中可以有多个数据文件。

2.       EXTENT MANAGEMENT LOCAL指明表空间类型是：本地管理表空间。本地管理表空间要求Oracle中的数据分区（Extent）大小统一。

3.       UNIFORM SIZE：指定每个分区的统一大小。

案例5：创建一个表空间，包含两个数据文件大小分别是10MB，5MB，要求extent的大小统一为1M。

代码演示：创建表空间

必须是管理员用户才能创建表空间，当表空间的空间不足时可以使用ALTER TABLESPACE命令向表空间中追加数据文件扩充表空间。

代码演示：扩充表空间

         表空间可以在不使用时删除，使用DROP TABLESPACE命令。

         数据库的所有数据全部在某一表空间中存放，在创建用户时，可以为用户指定某一表空间，那么该用户下的所有数据库对象（比如表）默认都存储在该空间中。

代码演示：为某一用户指定默认表空间

         在创建表时，表中数据存放在用户的默认表空间中，也可以通过tablespace子句为表指定表中数据存放在其他表空间中。

代码演示：为表指定表空间

         创建索引时也可以为索引指定表空间。

代码演示：为索引指定表空间

         表和索引一旦创建，表空间无法修改。

Ø  Oracle数据库对象都是使用CREATE命令创建的。

Ø  同义词就是数据库对象的一个别名。同义词的类型有公有同义词和私有同义词。只有管理员可以创建共有同义词。创建同义词的命令是：CREATE SYNONYM。

Ø  序列能够产生一个连续不重复的整数。经常作为数据库的主键生成器。创建序列的命令是CREATE SEQUENCE。

Ø  序列的访问使用两个“伪列”，CURRVAL表示序列的当前值，NEXTVAL表示序列的下一个值。

Ø  视图就是一个预处理的查询语句，可以从若干表中过滤数据。

Ø  索引就是在查询中经常使用的列进行排序。常见的索引有：普通索引、唯一序列、组合索引以及基于函数的索引。此外还有位图索引、反向键索引等。

Ø  表空间是数据库的一个逻辑概念，表空间由若干个数据文件组成。为数据库对象和数据提供统一的空间管理。

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）创建一个表空间，该表空间是以学生姓名为用户的默认表空间。

第5章

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是快速而高效的。

Ø  可移植性

使用PL/SQL编写的应用程序，可以移植到任何操作系统平台上的Oracle服务器，同时还可以编写可移植程序库，在不同环境中重用。

Ø  安全性

可以通过存储过程对客户机和服务器之间的应用程序逻辑进行分隔，这样可以限制对Oracle数据库的访问，数据库还可以授权和撤销其他用户访问的能力。

PL/SQL是一种块结构的语言，一个PL/SQL程序包含了一个或者多个逻辑块，逻辑块中可以声明变量，变量在使用之前必须先声明。除了正常的执行程序外，PL/SQL还提供了专门的异常处理部分进行异常处理。每个逻辑块分为三个部分，语法是：

语法结构：PL/SQL块的语法

语法解析：

①  声明部分：声明部分包含了变量和常量的定义。这个部分由关键字DECLARE开始，如果不声明变量或者常量，可以省略这部分。

②  执行部分：执行部分是 PL/SQL块的指令部分，由关键字BEGIN开始，关键字END结尾。所有的可执行PL/SQL语句都放在这一部分，该部分执行命令并操作变量。其他的PL/SQL块可以作为子块嵌套在该部分。PL/SQL块的执行部分是必选的。注意END关键字后面用分号结尾。

③  异常处理部分：该部分是可选的，该部分用EXCEPTION关键字把可执行部分分成两个小部分，之前的程序是正常运行的程序，一旦出现异常就跳转到异常部分执行。

PL/SQL是一种编程语言，与Java和C#一样，除了有自身独有的数据类型、变量声明和赋值以及流程控制语句外，PL/SQL还有自身的语言特性：

PL/SQL对大小写不敏感，为了良好的程序风格，开发团队都会选择一个合适的编码标准。比如有的团队规定：关键字全部大些，其余的部分小写。

PL/SQL块中的每一条语句都必须以分号结束，SQL语句可以是多行的，但分号表示该语句结束。一行中可以有多条SQL语句，他们之间以分号分隔，但是不推荐一行中写多条语句。

PL/SQL中的特殊符号说明：

表1  PL/SQL中的特殊符号和运算符

²  变量声明

PL/SQL支持SQL中的数据类型，PL/SQL中正常支持NUMBER,VARCHAR2,DATE等Oracle SQL数据类型。声明变量必须指明变量的数据类型，也可以声明变量时对变量初始化，变量声明必须在声明部分。声明变量的语法是：

语法格式：声明变量

语法解析：

数据类型如果需要长度，可以用括号指明长度，比如：varchar2(20)。

代码演示：声明变量

代码解析：

①  声明一个变量sname，初始化值是“jerry”。字符串用单引号，如果字符串中出现单引号可以使用两个单引号（’’）来表示，即单引号同时也具有转义的作用。

②  对变量sname重新赋值，赋值运算符是“:=”。

③  dbms_output.put_line是输出语句，可以把一个变量的值输出，在SQL*Plus中输出数据时，可能没有结果显示，可以使用命令：set serveroutput on设置输出到SQL*Plus控制台上。

对变量赋值还可以使用SELECT…INTO 语句从数据库中查询数据对变量进行赋值。但是查询的结果只能是一行记录，不能是零行或者多行记录。

代码演示：变量赋值

代码解析：

①  变量初始化时，可以使用DEFAULT关键字对变量进行初始化。

②  使用select…into语句对变量sname赋值，要求查询的结果必须是一行，不能是多行或者没有记录。

²  声明常量

常量在声明时赋予初值，并且在运行时不允许重新赋值。使用CONSTANT关键字声明常量。

代码演示：声明常量

代码解析：

①  声明常量时使用关键字CONSTANT，常量初值可以使用赋值运算符（:=）赋值，也可以使用DEFAULT关键字赋值。

在SQL*Plus中还可以声明Session（会话，也就是一个客户端从连接到退出的过程称为当前用户的会话。）全局级变量，该变量在整个会话过程中均起作用，类似的这种变量称为宿主变量。宿主变量在PL/SQL引用时要用“:变量名”引用。

代码演示：宿主常量

代码解析：

①  可以使用var声明宿主变量。

②  PL/SQL中访问宿主变量时要在变量前加“:”。

③  在SQL*Plus中，使用print可以输出变量中的结果。

前面在建表时，学习过Oracle SQL的数据类型，PL/SQL不但支持这些数据类型，还具备自身的数据类型。PL/SQL的数据类型包括标量数据类型，引用数据类型和存储文本、图像、视频、声音等非结构化的大数据类型（LOB数据类型）等。下面列举一些常用的类型。

²  标量数据类型

标量数据类型的变量只有一个值，且内部没有分量。标量数据类型包括数字型，字符型，日期型和布尔型。这些类型有的是Oracle SQL中定义的数据类型，有的是PL/SQL自身附加的数据类型。字符型和数字型又有子类型，子类型只与限定的范围有关，比如NUMBER类型可以表示整数，也可以表示小数，而其子类型POSITIVE只表示正整数。

表2  PL/SQL中标量数据类型。

²  属性数据类型

当声明一个变量的值是数据库中的一行或者是数据库中某列时，可以直接使用属性类型来声明。Oracle中存在两种属性类型：%TYPE和%ROWTYPE。

Ø  % ROWTYPE

引用数据库表中的一行作为数据类型，即RECORD类型（记录类型），是PL/SQL附加的数据类型。表示一条记录，就相当于C#中的一个对象。可以使用“.”来访问记录中的属性。

代码演示：

代码解析：

①  声明一个myemp对象，该对象表示EMP表中的一行。

②  从EMP表中查询一条记录放入myemp对象中。

③  访问该对象的属性可以使用“.”。

Ø  %TYPE

引用某个变量或者数据库的列的类型作为某变量的数据类型。

代码演示：%TYPE应用

代码解析：

①  定义变量sal为emp表中sal列的类型。

②  定义totalsal是变量mysal的类型。

%TYPE可以引用表中的某列作的类型为变量的数据类型，也可以引用某变量的类型作为新变量的数据类型。

PL/SQL程序可通过条件或循环结构来控制命令执行的流程。PL/SQL提供了丰富的流程控制语句，与C#一样也有三种控制结构：

Ø  顺序结构

Ø  条件结构

Ø  循环结构

²  条件控制

C#中的条件控制使用关键字if和switch。PL/SQL中关于条件控制的关键字有IF-THEN、IF-THEN-ELSE、IF-THEN-ELSIF和多分枝条件CASE。

Ø  IF-THEN

该结构先判断一个条件是否为TRUE，条件成立则执行对应的语句块，与C#中的if语句很相似，具体语法是：

表3  PL/SQL中条件语法

说明：

①  用IF关键字开始，END IF关键字结束，注意END IF后面有一个分号。

②  条件部分可以不使用括号，但是必须以关键字THEN来标识条件结束，如果条件成立，则执行THEN后到对应END IF之间的语句块内容。如果条件不成立，则不执行条件语句块的内容。

③  C#结构用一对大括号来包含条件结构体的内容。PL/SQL中关键字THEN到END IF之间的内容是条件结构体内容。

④  条件可以使用关系运算符合逻辑运算符。

案例1：查询JAMES的工资，如果大于900元，则发奖金800元。

代码演示：IF-THEN应用

代码解析：

①  先判断条件，如果条件为TRUE，则执行条件结构体内部的内容。

②  在PL/SQL块中可以使用事务控制语句，该COMMIT同时也能把PL/SQL块外没有提交的数据一并提交，使用时需要注意。

Ø  IF-THEN-ELSE

语法格式：IF-THEN-ELSE

表4  PL/SQL中条件语法

语法解析：

把ELSE与IF-THEN连在一起使用，如果IF条件不成立则执行就会执行ELSE部分的语句。

案例2：查询JAMES的工资，如果大于900元，则发奖金800元，否则发奖金400元。

代码演示：IF-THEN-ELSE应用

Ø  IF-THEN-ELSIF

语法格式：IF-THEN-ELSIF

表5  PL/SQL中多分枝条件语法

语法解析：

PL/SQL中的再次条件判断中使用关键字ELSIF，而C#使用else if。

案例3：查询JAMES的工资，如果大于1500元，则发放奖金100元，如果工作大于900元，则发奖金800元，否则发奖金400元。

代码演示：IF-THEN-ELSIF应用

Ø  CASE

CASE是一种选择结构的控制语句，可以根据条件从多个执行分支中选择相应的执行动作。也可以作为表达式使用，返回一个值。类似于C#中的switch语句。语法是：

语法格式：CASE

语法解析：

         如果存在选择器selector，选择器selector与WHEN后面的表达式匹配，匹配成功就执行THEN后面的语句。如果所有表达式都与selector不匹配，则执行ELSE后面的语句。

案例4：输入一个字母A、B、C分别输出对应的级别信息。

代码演示：CASE中存在selector，不返回值

代码解析：

①  & grade表示在运行时由键盘输入字符串到grade变量中。

②  v_grade分别于WHEN后面的值匹配，如果成功就执行WHEN后的程序序列。

CASE语句还可以作为表达式使用，返回一个值。

代码演示：CASE中存在selector，作为表达式使用

代码解析：

①  CASE语句可以返回一个结果给变量p_grade

PL/SQL还提供了搜索CASE语句。也就是说，不使用CASE中的选择器，直接在WHEN后面判断条件，第一个条件为真时，执行对应THEN后面的语句序列。

代码演示：搜索CASE

²  循环结构

PL/SQL提供了丰富的循环结构来重复执行一些列语句。Oracle提供的循环类型有：

1.         无条件循环LOOP-END LOOP语句

2.         WHILE循环语句

3.         FOR循环语句

在上面的三类循环中EXIT用来强制结束循环，相当于C#循环中的break。

Ø  LOOP循环

LOOP循环是最简单的循环，也称为无限循环，LOOP和END LOOP是关键字。

语法格式：LOOP循环

语法格式：

1.       循环体在LOOP和END LOOP之间，在每个LOOP循环体中，首先执行循环体中的语句序列，执行完后再重新开始执行。

2.       在LOOP循环中可以使用EXIT或者[EXIT WHEN 条件]的形式终止循环。否则该循环就是死循环。

案例5：执行1+2+3+…+100的值

代码演示：LOOP循环

代码解析：

①  LOOP循环中可以使用IF结构嵌套EXIT关键字退出循环

②  注释行，该行可以代替①中的循环结构，WHEN后面的条件成立时跳出循环。

Ø  WHILE循环

与C#中的while循环很类似。先判断条件，条件成立再执行循环体。

语法格式：WHILE

表5  PL/SQL中LOOP语法

案例6：WHILE循环

代码演示：WHILE循环

Ø  FOR循环

FOR循环需要预先确定的循环次数，可通过给循环变量指定下限和上限来确定循环运行的次数，然后循环变量在每次循环中递增（或者递减）。FOR循环的语法是：

语法格式：FOR循环

语法解析：

循环变量：该变量的值每次循环根据上下限的REVERSE关键字进行加1或者减1。

REVERSE：指明循环从上限向下限依次循环。

案例7：FOR循环

代码演示：FOR循环

²  顺序结构

在程序顺序结构中有两个特殊的语句。GOTO和NULL

Ø  GOTO语句

GOTO语句将无条件的跳转到标签指定的语句去执行。标签是用双尖括号括起来的标示符，在PL/SQL块中必须具有唯一的名称，标签后必须紧跟可执行语句或者PL/SQL块。GOTO不能跳转到IF语句、CASE语句、LOOP语句、或者子块中。

Ø  NULL语句

NULL语句什么都不做，只是将控制权转到下一行语句。NULL语句是可执行语句。NULL语句在IF或者其他语句语法要求至少需要一条可执行语句，但又不需要具体操作的地方。比如GOTO的目标地方不需要执行任何语句时。

案例8：GOGO 和 NULL

代码演示：GOTO和NULL

代码解析：

①  跳转到程序first_label位置，就是②的位置，first_label是一个标签，用两个尖括号包含。

②  无条件跳转到sedond_label位置，就是④的位置。④处不执行任何内容，因此是一个NULL语句。

与C#一样，在PL/SQL中，各种循环之间可以相互嵌套。

在PL/SQL程序开发中，可以使用DML语句和事务控制语句，但是还有很多语句（比如DDL语句）不能直接在PL/SQL中执行。这些语句可以使用动态SQL来实现。

PL/SQL块先编译然后再执行，动态SQL语句在编译时不能确定，只有在程序执行时把SQL语句作为字符串的形式由动态SQL命令来执行。在编译阶段SQL语句作为字符串存在，程序不会对字符串中的内容进行编译，在运行阶段再对字符串中的SQL语句进行编译和执行，动态SQL的语法是：

语法格式：动态SQL

语法解析：

         如果动态语句是SELECT语句，可以把查询的结果保存到INTO后面的变量中。如果动态语句中存在参数，USING为语句中的参数传值。

         动态SQL中的参数格式是：[:参数名]，参数在运行时需要使用USING传值。

       案例9：动态SQL

代码演示：动态SQL

代码解析：

①  动态执行一个完整的SQL语句。

②  SQL语句中存在3个参数分别标识为：[:1、:2、:3]，因此需要用USING关键字对三个参数分别赋值。

③  对动态查询语句可以使用INTO子句把查询的结果保存到一个变量中，要求该结果只能是单行。

④  在Oracle的insert，update，delete语句都可以使用RETURNING子句把操作影响的行中的数据返回，对SQL语句中存在RETURNING子句时，在动态执行时可以使用RETURNING INTO来接收。

⑤  动态执行参数中可以是：[:数字]也可以是[:字符串]。

在程序运行时出现的错误，称为异常。发生异常后，语句将停止执行，PL/SQL引擎立即将控制权转到PL/SQL块的异常处理部分。异常处理机制简化了代码中的错误检测。PL/SQL中任何异常出现时，每一个异常都对应一个异常码和异常信息。比如：

图1  PL/SQL中的异常

²  预定义异常

为了Oracle开发和维护的方便，在Oracle异常中，为常见的异常码定义了对应的异常名称，称为预定义异常，常见的预定义异常有：

表6  PL/SQL中预定义异常

         PL/SQL中用EXCEPTION关键字开始异常处理。具体语法是：

语法格式：异常处理

语法解析：

         异常发生时，进入异常处理部分，具体的异常与若干个WHEN子句中指明的异常名匹配，匹配成功就进入对应的异常处理部分，如果对应不成功，则进入OTHERS进行处理。

         案例10 ：异常处理

代码演示：异常处理

²  自定义异常。

         除了预定义异常外，用户还可以在开发中自定义异常，自定义异常可以让用户采用与PL/SQL引擎处理错误相同的方式进行处理，用户自定义异常的两个关键点：

Ø  异常定义：在PL/SQL块的声明部分采用EXCEPTION关键字声明异常，定义方法与定义变量相同。比如声明一个myexception异常方法是：

myexception EXCEPTION；

Ø  异常引发：在程序可执行区域，使用RAISE关键字进行引发。比如引发myexception方法是：

RAISE myexception;

       案例11：自定义异常

代码演示：自定义异常

代码解析：

①  用EXCEPTION定义一个异常变量myexp

②  在一定条件下用RAISE引发异常myexp

③  在异常处理部分，捕获异常，如果不处理异常，该异常就抛给程序执行者。

²  引发应用程序异常

在Oracle开发中，遇到的系统异常都有对应的异常码，在应用系统开发中，用户自定义的异常也可以指定一个异常码和异常信息，Oracle系统为用户预留了自定义异常码，其范围介于-20000到-20999之间的负整数。引发应用程序异常的语法是：

RAISE_APPLICATION_ERROR(异常码，异常信息)

案例12：引发系统异常

代码演示：引发应用系统异常

代码解析：

①  引发应用系统异常，指明异常码和异常信息。

②  在控制台上显示异常码和异常信息。

如果要处理未命名的内部异常，必须使用OTHERS异常处理器。也可以利用PRAGMA EXCEPTION_INIT把一个异常码与异常名绑定。

PRAGMA由编译器控制，PRAGMA在编译时处理，而不是在运行时处理。EXCEPTION_INIT告诉编译器将异常名与ORACLE错误码绑定起来，这样可以通过异常名引用任意的内部异常，并且可以通过异常名为异常编写适当的异常处理器。PRAGMA EXCEPTION_INIT的语法是：

PRAGMA EXCEPTION_INIT(异常名,异常码)

这里的异常码可以是用户自定义的异常码，也可以是Oracle系统的异常码。

案例13：PRAGMA EXCEPTION_INIT异常

代码演示：PRAGMA EXCEPTION_INIT异常

代码解析：

①  把异常名称null_salary与异常码-20101关联，该语句由于是预编译语句，必须放在声明部分。也就是说-20101的异常名称就是null_salary。

②  嵌套PL/SQL语句块

③  在内部PL/SQL语句块中引发应用系统异常-20101。

④  在外部的PL/SQL语句块中就可以用异常名null_salary进行捕获。

Ø  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中任何异常出现时，每一个异常都对应一个异常码和异常信息。

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章

Oracle应用于.Net平台

ADO.NET是一组用于和数据源进行交互的面向对象类库集，它存在于.Net Framework中。通常情况下，数据源可以是各种类型的数据库，利用ADO.NET可以访问目前几乎所有的主流数据库，如Oracle、SQL Server、DB2、Access等，但数据源同样也能够是文本文件、Excel文件或者XML文件，因此，ADO.NET可以访问的数据源是很多的。

ADO.NET由两部分组成：.Net数据提供程序和数据集。

²  .Net数据提供程序

ADO.NET提供了与常用的各种数据源进行交互的一些公共方法，但是对于不同的数据源由于它们各采用的协议是不一样的，所以会采用不同的类库，这些类库称为数据提供程序。主要的数据提供程序如表1所示：

表1 主要的.NET数据提供程序

ADO.NET主要负责与数据库服务器建立连接通道，并基于此连接通道实现从数据库中检索数据,并将内存中的数据回送到数据库以提交更新或将在内存中拼写好的SQL语句提交到数据库服务器并执行以实现某种数据操作等等。

²  数据集

数据集可以被看作是一个存储在内存中的离线式的数据库，它很像我们最终存储数据的物理数据库，它将很多物理数据库的机制搬到了内存中。

ADO.NET的主要部分已经在之前的章节介绍过了，例如：利用SQL Server.NET链接SQL Server数据库。本章将主要介绍利用ADO.NET连接Oracle数据库，实现数据的存取。ADO.NET的结构如图1所示。

图1  ADO.NET结构图

使用ADO.NET数据访问技术连接Oracle数据库和连接Sql Server数据库的步骤基本相同：

1.         使用Connection对象建立数据库连接。

2.         使用Command对象执行数据库操作。

3.         采用连线或者断线的方式进行数据的存取。

4.         使用Connection对象的Close方法关闭数据库连接。

连接Sql Server数据库和Oracle数据库所使用的数据提供程序是不一样的，连接Sql Server数据库使用的是SqlServer.NET，包括SqlConnection、SqlCommand等数据访问类。而连接Oracle数据库则使用的是Oracle.NET，包括OracleConnection、OracleCommand等数据访问类，包含在System.Data.OracleClient命名空间下，由于该命名空间默认并没有被添加到项目中来，所以在使用前需要如图2所示添加响应的引用才能使用。

图2  添加System.Data.OracleClient引用

案例1：在Oracle数据库中在System用户下，创建UserInfo表，并插入一定数据，使用ADO.NET数据访问技术将UserInfo表中的数据检索出来，并显示在ASP.NET页面中。UserInfo表中的数据如图3所示。

图2  UserInfo表中数据

实现步骤：

1.         使用OracleConnection对象建立与Oracle之间的连接。

代码演示：建立连接

2.         创建命令行对象，准备执行检索数据库操作。

代码演示：建立命令行对象

3.         创建实体类，并遍历结果集，将数据存放到集合中存储。

代码演示：创建实体类

代码演示：遍历结果集

4.         将集合中的数据绑定到GridView控件上显示出来。

代码演示：绑定数据

根据上面的操作，不难看出连接Oracle数据库与连接SqlServer数据库除了数据提供程序方面有所区别之外，从操作步骤和实现原理等方面看区别不大。按照上面步骤操作，在页面中运行的效果如图3所示。增删改操作和检索操作基本相似，在这里就不再叙述了。

图3  案例效果

在设计模式中介绍过抽象工厂设计模式（Abstract Factory），抽象工厂有四种关键角色：抽象工厂、实体工厂、抽象产品、实体产品。抽象工厂模式实现原理强调的是对象组合机制，由在“父工厂”内定义不同的“子工厂”对象来负责不同的目标对象的创建，也就是说利用实体产品由抽象产品来约束，而由实体工厂来创建，实体工厂则由抽象工厂约束，可以有效的发挥工厂模式管理清晰的优点。

         案例2：采用抽象工厂模式实现在学员信息管理系统中支持Access、SQLServer以及Oracle三套数据库的切换（以学员基本信息模块为例），以学员基本信息模块为例给出概要的实现，并实现展示所有学生信息功能。

         案例分析：本例是抽象工厂课堂案例的延续，需要在项目中多添加一个Oracle数据库，实体产品是数据访问对象，三套数据库相当于有三套数据库访问对象，通过三个实体工厂管理三套数据库访问对象，最后使用抽象工厂管理三个实体工厂。

实现步骤：

1.         在Oracle数据库中使用Sql语句新建Infos表，并添加约束和表数据，如图3所示：

图3  Student表数据

2.         在VS2008中创建空白解决方案，命名为Test.sln。

3.         在解决方案中添加表示层，并添加StudentList.aspx页面。

4.         在解决方案中添加模型层，根据上面的表结构新建Student.cs实体类。

代码演示：Student类

5.         在解决方案中添加数据访问层IDAL（抽象产品）。

代码演示：抽象产品

6.         在解决方案中添加数据访问层DAL（实体产品），并利用文件夹将不同的实体产品分类，如图4所示。

图4  实体产品

代码演示：实体产品

7.         在解决方案中添加业务逻辑层，命名为StudentManager.cs。

代码演示：BLL层

8.         在解决方案中添加抽象工厂，并添加相应实体工厂，如图5所示。

图5  抽象工厂

代码演示：抽象工厂

9.         在表示层添加数据展示控件，通过设定属性绑定数据提取方法，实现案例目标。

Ø  ADO.NET是一组用于和数据源进行交互的面向对象类库集，它存在于.Net Framework中。通常情况下，数据源可以是各种类型的数据库，利用ADO.NET可以访问目前几乎所有的主流数据库，如Oracle、SQL Server、DB2、Access等。

Ø  使用ADO.NET技术访问数据库的大致步骤如下：

1.         使用Connection对象建立数据库连接。

2.         使用Command对象执行数据库操作。

3.         采用连线或者断线的方式进行数据的存取。

4.         使用Connection对象的Close方法关闭数据库连接。

Ø  抽象工厂有四种关键角色：抽象工厂、实体工厂、抽象产品、实体产品。

1.         利用本章所学知识，完成如下功能：

1)         在本章完成项目基础上，继续实现对Oracle数据库的增删改操作。

2)         在StudentList.aspx页面添加查看详细链接，编写GetStudentById等方法。

3)         在StudentList.aspx页面中添加删除链接，编写DeleteStudent等方法。

4)         在StudentList.aspx页面中添加修改链接，编写UpdateStudent等方法。

附录

数据库导入导出

Oracle的备份是Oracle操作中常见的工作，常见的备份方案有：逻辑备份（IMP&EXP命令进行备份）、物理文件备份（脱机及联机备份）、利用RMAN(Recovery Manager)的增量物理文件系统备份。ORACLE数据库的逻辑备份分为四种模式：表空间备份(tablespace)、表备份(table)、用户备份(user)和完全备份(full)。Oracle的逻辑备份是使用IMP&EXP命令进行数据导入导出的操作。使用EXP命令导出或者使用IMP命令导入时，需要Create Session系统权限，但是如果要导出其他的表，必须拥有权限：EXP_FULL_DATABASE。

调用导入导出命令时，首先要估计所需的空间。EXP命令导出的文件是二进制文件（*.dmp）只能由对应的IMP命令进行读取恢复。导入导出的用途是：

Ø  备份与恢复

Ø  Oracle平台更换：可以在相同版本之间进行备份与恢复，Oracle较低版本的export数据文件可以import到高版本的Oracle数据库中，但是Oracle的版本只能是相邻的，不能垮版本。

EXP命令可以在交互环境下导出数据库中的数据，也可以在非交互环境下执行命令。交互环境下的命令执行，是一步一步执行的过程。

代码演示：exp的交互环境

代码解析：

①  Exp是导出命令，该命令后面紧跟“用户名/密码@服务器网络连接”。

②  Exp程序导出时使用的缓冲区大小，缓冲区越大，导出速度越快。直接回车代表使用默认值4096B。

③  Exp命令会把所有要到处的数据导出到一个Dmp文件中，该步骤是Exp询问导出的数据文件名称。

④  Exp程序询问导出整个用户还是导出某个表。默认导出整个用户。

⑤  Exp程序询问是否导出每张表的访问权限。默认导出访问权限。

⑥  Exp程序询问是否导出表中的数据。默认导出数据库表中的数据。

⑦  Oracle表中的数据可能来自不同的分区中的数据块，默认导出时会把所有的数据压缩在一个数据块上，IMP导入时，如果不存在连续一个大数据块，则会导入失败。

也可以使用Exp命令时，设置各种参数，使准备就绪的Exp命令不需要与用户交互，按照参数的要求，Exp命令会一次性执行导出工作。要指定参数，您可以使用关键字:

EXP KEYWORD=value 或 KEYWORD=(value1,value2,...,valueN)

例如: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)

表1  EXP参数说明

代码演示：exp的非交互环境

IMP程序导入就是把Exp导出的文件重新导入到数据库的过程。导入时也有一些重要的参数：

Ø  Fromuser:指出导出时dmp文件中记载的用户信息。

Ø  Touser:dmp文件要导入到什么目标用户中。

Ø  Commit:默认是N,在缓冲区满时是否需要commit,如果设为N,需要较大的回滚段。

Ø  Igore: Oracle在恢复数据的过程中，当恢复某个表时，该表已经存在，就要根据ignore参数的设置来决定如何操作。若ignore=y，Oracle不执行CREATE TABLE语句，直接将数据插入到表中，如果插入的记录违背了约束条件，比如主键约束，则出错的记录不会插入，但合法的记录会添加到表中。若ignore=n，Oracle不执行CREATE TABLE语句，同时也不会将数据插入到表中，而是忽略该表的错误，继续恢复下一个表。

代码演示：Imp导入

Ø  数据库对象已经存在

一般情况, 导入数据前应该彻底删除目标数据下的表，序列，函数/过程,触发器等。

数据库对象已经存在, 按缺省的imp参数，则会导入失败。

如果用了参数ignore=y，会把exp文件内的数据内容导入。

如果表有唯一关键字的约束条件，不合条件将不被导入。

如果表没有唯一关键字的约束条件，将引起记录重复。

Ø  数据库对象有主外键约束

不符合主外键约束时，数据会导入失败。

解决办法: 先导入主表，再导入依存表。

disable目标导入对象的主外键约束，导入数据后，再enable它们。

Ø  权限不够

如果要把A用户的数据导入B用户下， A用户需要有imp_full_database权限。

Ø  导入大表( 大于80M ) 时，存储分配失败

默认的EXP时，compress = Y，也就是把所有的数据压缩在一个数据块上。

导入时，如果不存在连续一个大数据块，则会导入失败。

导出80M以上的大表时，记得compress= N，则不会引起这种错误。

Ø  Imp和Exp使用的字符集不同

如果字符集不同，导入会失败，可以改变unix环境变量或者NT注册表里NLS_LANG相关信息。

Ø  Imp和Exp版本不能往上兼容

Imp可以成功导入低版本Exp生成的文件, 不能导入高版本Exp生成的文件根据情况我们可以用。

 [D12]escape关键字经常用于使某些特殊字符，如通配符'%','_'转义为它们原来的字符的意义，被定义的转义字符通常使用'\',但是也可以使用其他的符号。