1、第1章Oracle11g数据库系统第1章 Oracle 11g数据库系统Oracle数据库系统是世界领先的数据库管理系统，Oracle数据库以其功能强大和配置灵活而著称，同时也因有一定的操作难度，让很多初学者望而却步。本书将由浅入深，以简单易懂的示例带领读者拨开Oracle的神秘面纱。目前在Oracle世界主要有两类人员：一类是Oracle数据库管理人员，简称DBA；一类是Oracle开发人员。Oracle DBA主要的工作是负责日常的数据库维护和性能优化管理。由于Oracle系统较庞大、复杂，要成为一名合格的DBA，需要掌握的知识较多，比如要掌握Oracle体系结构和性能优化等方面的知识，相
2、对其他数据库而言入门门槛较高，但是薪酬一般也较丰厚。Oracle开发人员的主要工作是使用Oracle提供的SQL语言和PL/SQL结构化程序设计语言操作数据库，主要职责是操纵Oracle数据库对象，不需要对Oracle系统结构有深入了解，入门较容易。当Oracle开发人员积累了一定的开发知识后，可以通过学习Oracle DBA方面的知识向数据库管理员转型。1.1 关系型数据库系统介绍1970年6月，IBM公司的研究员E.F.Codd博士（中文名：埃德加弗兰克科德），发表了名为“大型共享数据库的关系模型”的论文，受到了学术界和产业界的高度重视和广泛响应，使得关系型数据库系统很快成为数据库市场的主
3、流。E.F.Codd博士被誉为“关系数据库之父”，其照片如图1.1所示。图1.1 关系数据库之父埃德加弗兰克科德1.1.1 什么是关系型数据模型关系型数据库简而言之就是使用关系或二维表存储信息。以公司的人员信息管理为例，为了存储公司内部的员工信息，人事部门一般会建一份列表，在公司未引入信息化管理系统时，大多数人事职员会使用Excel来保存员工信息，例如图1.2是一份简单的人员信息列表的Excel文件。这种Excel存储数据的方式，将人员的所有信息都包含在一张表中，随着Excel中的栏位和记录数越来越多，这份人员信息表会变得越来越繁杂，这种存储数据的方式称为平面文件数据模型。为了简化修改与维护的
4、复杂性，关系型数据库设计人员通过使用实体关系模型进行数据库建模，例如人员信息表可以分为员工表和部门表，通过部门编号进行关联，ER模型如图1.3所示。 图1.2 Excel人员信息列表 图1.3 人员信息表ER关系模型由图1.3中可以看到，通过将员工和部门分别存储在不同的二维表格中，使用主键（PK）和外键（FK）进行关联，使得获取和维护数据变得更容易，这就是关系型数据模型。上述ER图的3个关键组件分别如下所示。 实体：需要了解的信息，比如部门和员工信息。 属性：一般也称为列或字段，描述实体必须或可选的信息，比如员工表中的工号和姓名等。 关系：实体之间指定的关联，比如员工的部门编号关联到了部门表的
5、编号属性。关系型数据模型还涉及一些较复杂的组成元素，涉及较多的数学知识，有兴趣的读者可以参考一些理论性的读物。1.1.2 数据库系统范式为了规范化关系型数据模型，关系型数据库系统在设计时必须遵循一定的规则，这种规则称为关系型数据库系统范式。了解范式是每个数据库设计或开发人员必须具备的基本功，范式的主要目的是降低数据冗余，设计结构合理的数据库。目前较常用的范式有如下3种。1第一范式（1NF）：字段必须具有单一属性特性，不可再拆分如果字段中的值已经是无法再分割的值，则符合第一范式，即1NF。例如，在员工表中，姓名字段一般仅包含员工的正式姓名，这是符合第一范式的，但是如果要在姓名字段中包含中文名、英
6、文名、昵称、别名等信息，就意味着姓名字段是可再拆分的。因此员工名的设计可以如图1.4所示。图1.4 修改后的员工表以匹配1NF范式2第二范式（2NF）：表要具有唯一性的主键列第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分，为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。第二范式是在第一范式的基础上的进一步增强，在数据库设计时一般使用唯一性主键来唯一地标识行。比如在员工表中定义了以工号作为主键，因为公司员工的工号通常用来识别某个员工个体，不能进行重复；在部门表中通过部门编号作为主键，来唯一地区分一个部门。3第三范式（3NF）：表中的字段不能包含在其他表中已出现的非
7、主键字段第三范式（3NF）是在前两个范式的基础上的进一步增强，主要用来降低数据的冗余。比如，员工表中包含了部门编号，它引用到部门表中的部门编号这个主键，符合第三范式。如果在员工表中又包含一个部门名称，那么表中的字段就包含了其他表中已出现的非主键字段，造成了数据的冗余，不符合第三范式。范式主要用来规范数据库的设计，使得设计出来的数据库结构清晰，简洁易懂，避免了数据冗余和操作的异常。在设计数据库模型时，灵活地应用范式是创建一个优秀的数据库系统的基石。1.1.3 关系型数据库管理系统关系型数据库管理系统，简称DBMS，是基于关系型数据库理论而开发的软件系统。目前比较热门的关系型数据管理系统有：Ora
8、cle、Microsoft SQL Server、Access、MySQL及PostgreSQL等。数据库管理系统是用于建立、使用和维护数据库，对数据库进行统一的管理和控制，保证数据库的安全性和完整性的一套大型的电脑程序。数据库管理系统功能结构示意图如图1.5所示。图1.5 数据库管理系统功能结构示意图如图1.5所示，一个数据库管理系统通常要提供如下所示的几项功能。 定义数据库结构：DBMS提供数据定义语言来定义（DDL）数据库结构，用来搭建数据库框架，并被保存在数据字典中。 存取数据库内容：DBMS提供数据操纵语言（DML），实现对数据库数据的基本存取操作，即检索、插入、修改和删除等。 数据
9、库的运行管理：DBMS提供数据控制功能，即数据的安全性、完整性和并发控制等，对数据库运行进行有效的控制和管理，以确保数据正确有效。 数据库的建立和维护：包括数据库初始数据的装入，数据库的转储、恢复、重组织，系统性能监视、分析等功能。 数据库的传输：DBMS提供处理数据的传输，实现用户程序与DBMS之间的通信，通常与操作系统协调完成。有了关系型数据库管理系统，开发人员就可以在数据库中创建数据库、创建表、存取数据库内容、对数据库进行备份和管理，只需要理解常用的系统相关的操作，而不用去研究关系型数据库系统内部深奥难懂的数据方面的理论知识。 1.1.4 使用SQL语句与数据库管理系统通信关系型数据库管
10、理系统提供了SQL语言，允许用户操纵数据库。SQL语言的全称是结构化查询语言（Structured Query Language），它是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解其具体的数据存放方式，其操作示意图如图1.6所示。尽管SQL语言已经被ISO组织定义了具有国际标准的SQL规范，但是各种数据库系统厂商在其数据库管理系统中都对SQL规范做了某些编改和扩充。所以，实际上不同数据库系统之间的SQL不能完全相互通用，目前比较流行的两大类SQL语言分别是微软的T-SQL和Oracle的PL/SQL，这两类SQL既有相似之处又有不同之
11、处。图1.6 使用SQL操作数据库管理系统SQL语言主要又分为如下两大类。 DML数据操纵语言，主要是完成数据的增、删、改和查询的操作。 DDL数据定义语言，主要用来创建或修改表、视图、存储过程及用户等。除此之外，还包含称为DCL的数据控制语言。数据库管理员，即DBA，通常使用DDL来管理数据库的对象，而数据操纵语言DML则主要由数据库开发人员使用来操纵数据。举个例子，如果想要在数据库中创建如图1.4所示的关系模型，可以使用代码1.1的DDL语句来实现。代码1.1 使用DDL语句创建列和键-创建员工表CREATE TABLE 员工表( -定义员工表列 工号 INT NOT NULL, 中文姓名
12、 NVARCHAR2(20) NOT NULL, 英文姓名 VARCHAR2(20) NULL, 别名 VARCHAR2(20) NULL, 年龄 INT DEFAULT 18, 入职日期 DATE NULL, 部门编号 INT NULL, -定义员工表主键 CONSTRAINT PK_员工表 PRIMARY KEY(工号);-创建部门表CREATE TABLE 部门表( -定义部门表列 部门编号 INT NOT NULL, 部门名称 NVARCHAR2(50) NULL, 部门经理 INT NOT NULL, 部门描述 NVARCHAR2(200) NULL, 工号 INT NOT NULL
13、, -定义部门表主键 CONSTRAINT PK_部门表 PRIMARY KEY(部门编号) );-为员工表添加外键引用ALTER TABLE 员工表 ADD ( CONSTRAINT FK_部门编号 FOREIGN KEY (部门编号) REFERENCES 部门表 (部门编号); -为部门表添加外键引用ALTER TABLE 部门表 ADD ( CONSTRAINT FK_部门经理 FOREIGN KEY (部门经理) REFERENCES 员工表 (工号);代码使用DDL语句CREATE TABLE，创建了员工表和部门表，并指定了表列及列的数据类型，同时为每个表都创建了主键。在创建了表之
14、后，使用ALTER TABLE语句为表指定了表间的主外键关系。说明：-表示SQL中的注释语句。一旦创建好了表结构，数据库开发人员就可以使用DML语句来操纵这些表数据，比如下面的代码分别向部门表和员工表插入了一些数据：-张三是理财部的经理，他不属于任何部门 INSERT INTO 员工表 VALUES(100,张三,San Zhang,老三,20,date2011-01-01,null);-李四是财务部职员INSERT INTO 员工表 VALUES(101,李四,Li Si,老四,20,date2011-01-01,100);-部门表INSERT INTO 部门表 VALUES(100,财务部
15、,100,理财部,0); -让张三属于财务部UPDATE 员工表 SET 部门编号=100 WHERE 工号=100;上面的代码使用DML语句INSERT向表中插入了3条记录，最后使用了UPDATE语句对员工表中的记录进行了更新。如果要查询部门为财务部的所有的员工信息，可以使用如下所示的SELECT语句来进行查询：SELECT * FROM 员工表 WHERE 部门编号=100;数据库开发人员通过灵活使用这些DML语句，就可以非常方便地对数据库数据进行操作，以满足自己的应用程序逻辑需求。1.2 初识Oracle 11gOracle公司是目前全球第一大数据库厂商，该公司成立于1977年，最初专门
16、开发数据库。Oracle以其灵活的架构、强大的可配置性使得该数据库在数据库规模、稳定性与工作效率方面位居数据库管理系统首位。目前我国大多数的大中型企业都不约而同地选择使用Oracle作为后台管理系统，世界上很多知名的企业也使用Oracle来处理海量的数据存储。1.2.1 Oracle 11g简介Oracle 11g是目前Oracle公司数据库管理系统的最新版本，11g后面的g表示的是网格Grid，因为Oracle数据库系统是一个网格的数据库管理系统。网格是指通过众多独立的、可以模块化的软硬件进行连接及重组，提供网状的企业信息系统，它是一种具有弹性的体系结构，可以满足复杂的、多元化的计算需求。O
17、racle数据库系统有两种主要的使用形式：客户端/服务器端体系结构和多层结构。目前国内使用得较为广泛的通常是客户端/服务器端结构，整个数据库系统分为两个部分：客户端和服务器端。 客户端：一个数据库应用程序，比如使用Oracle数据库的ERP系统，或者是用来开发PL/SQL的开发工具。客户端负责请求、处理、展现由数据库服务器管理的数据。运行客户端的计算机可以针对它自身的工作进行优化。例如，客户端计算机不需要大容量的磁盘，但应该适当提高显示性能。 服务器端：服务器运行Oracle数据库管理软件，处理并发、共享的数据访问。数据库服务器接收、处理由客户端应用程序提交的SQL或PL/SQL语句。运行数据
18、库的计算机也可以根据它的职责进行优化，应具备大容量存储和较快的处理能力。目前大多数公司会在UNIX或Linux上安装部署Oracle服务器端，然后在客户端的Windows电脑上安装Oracle客户端进行数据库的开发和管理，因此在安装Oracle数据库系统时会发现Oracle客户端和Oracle服务器端的安装选项。在Oracle公司的网站上提供了Oracle各种版本的数据库的安装程序下载，可以看到在网站上即可下载整套的Oracle数据库系统，还可以单独下载客户端，如图1.7所示。图1.7 Oracle 11g数据库安装程序下载网页1.2.2 Oracle 11g体系结构一个Oracle数据库服务
19、器包括如下两个方面。 存储Oracle数据的物理数据库，即保存Oracle数据库数据的一系列物理文件，包含控制文件、数据文件、日志文件和其他文件。 Oracle实例：这是物理数据库和用户之间的一个中间层，用来分配内存，运行各种后台进程，这些分配的内存区和后台进程统称为Oracle实例。当用户在客户端连接并使用数据库时，实际上是连接到该数据库的实例，由实例来连接、使用数据库，示意图如图1.8所示。图1.8 Oracle数据库访问示意图注意：实例不是数据库，数据库主要是指用于存储数据的物理结构，总是实际存在的。而实例是由操作系统的内存结构和一系列进程组成的，可以对实例进行启动和 关闭。当然一台计算
20、机上总是可以创建多个Oracle数据库，要同时使用这些数据库，就需要创建多个实例，因此Oracle系统要求每个实例要使用SID进行划分，即在创建数据库时要指定数据库的SID。整个Oracle数据库系统的体系结构如图1.9所示。图1.9 Oracle数据库体系结构Oracle的体系结构提供了较多的配置项允许DBA通过灵活的配置来提升整个系统的性能，这也是相对较复杂的一部分，有志于从事DBA的读者可以通过相关资料详细地理解Oracle的体系结构。1.2.3 如何创建数据库由于Oracle系统体系结构的复杂性，数据库的创建工作要求用户熟悉较多的配置参数，不像在SQL Server中创建数据库那样简单
21、。幸好Oracle提供了数据库配置助手DBCA这个工具，允许用户以图形化的方式创建和配置数据库。如果读者还没有安装Oracle数据库系统，请从Oracle网站下载Oracle 11g for Windows 32位安装包，约2GB大小，然后按照安装向导的提示进行一步一步的安装。安装完成后，可以在程序面板上看到Oracle系统菜单栏。下面通过示例演示如何创建一个示例数据库，本书后面的内容都会对这个数据库进行操作。创建步骤如下所示。（1）单击“开始所有程序Oracle配置和移植工具Database Configuration Assistant”菜单项，系统将弹出数据库配置助手向导，单击“下一步”
22、按钮，选择“创建数据库”单选按钮，如图1.10所示。图1.10 数据库配置助手向导（2）在选择“创建数据库”选项后，单击“下一步”按钮，向导将进入数据库模板选择窗口，模板窗口提供了几种预装模板，这些模板配置了合适的数据库参数，允许用户快速创建一个数据库。在示例中选择“一般用途或事务处理”数据库模板，如图1.11所示。图1.11 选择数据库模板（3）在选择了数据库模板之后，接下来进入指定数据库标识窗口，如图1.12所示。图1.12 数据库标识窗口全局数据库名是将数据库与任何其他数据库唯一标识出来的数据库全称，通常的名称格式为：数据库名称.数据库域名。比如Demos.M，数据库名称为Demos，数
23、据库域名通常用来指定数据库所在的域。SID是Oracle数据库实例的名称，因为在Oracle中每一个数据库都有一个对象的数据库实例。在示例中将实例名称与SID名分别命名为BookDemo和BookDemoSID。（4）在配置了数据库名称后，单击“下一步”按钮，将进入管理选项窗口，保留该窗口的默认值。“配置Enterprise Manager”选项是指可以通过Oracle 11g的数据库配置管理工具OEM来配置该数据库。（5）接下来向导要求为系统重要的数据库管理账户设置密码来保证数据的安全，在示例中为所有的账户使用同一口令，口令统一为password，如图1.13所示。图1.13 设置系统管理员
24、口令注意：用户必须妥善保管好管理员密码，以备在将来对数据库进行维护时输入正确的 密码。（6）在设置了口令并单击“下一步”按钮后，向导进入数据库存储机制窗口，普通应用只需要选择文件系统即可，如图1.14所示。图1.14 设置系统管理员口令（7）在指定存储位置后，接下来进入恢复配置窗口，启用默认值即可。恢复配置之后的窗口是示例方案窗口，选中“示例方案”复选框，在本书后面的内容中将使用示例方案中的样例数据来介绍PL/SQL编程，如图1.15所示。图1.15 选择示例方案在Oracle系统中，方案是一个非常重要的概念，方案的名称与用户的名称相同，但是方案与用户是完全不同的两个概念，默认情况下，所有用户
25、所创建的对象都位于自己的方案中，也就是说，Oracle使用方案的方法将数据库按用户进行了划分。（8）在配置了示例方案后，接下来进入初始化设置窗口，这个窗口涉及较多的数据库初始参数设置的内容，直接使用系统的默认值即可，如图1.16所示。（9）使用了默认的初始化参数配置后，单击“下一步”按钮将进入数据库存储配置窗口，该窗口允许用户查看和更改数据库的控制文件、数据文件和重做日志组对象，样例数据库使用默认设置即可，如图1.17所示。（10）在确定了存储配置窗口后，单击“下一步”按钮，将进入数据库创建选项窗口，此处可以选择直接创建数据库，也可以将前面几步的创建设置保存为一个新的数据库模板，如图1.18所
26、示。图1.16 初始参数配置窗口图1.17 存储配置窗口在此直接使用默认选择创建一个新的数据库，单击“完成”按钮后，系统会弹出一个确认窗口，显示了所要创建的数据库的概要信息，如图1.19所示，确定了数据库的创建后，系统将开始分3步创建数据库，如图1.20所示。 图1.18 创建选项窗口 图1.19 数据库概要窗口经过一段时间的创建工作后，向导将弹出一个提示窗口告之用户，数据库已经创建完成，单击“确定”按钮后，整个数据库的创建工作就结束了，如图1.21所示。此时可以通过Windows的服务窗口看到DBCA已经启动了BookDemo相关的服务，如图1.22所示。 图1.20 创建数据库 图1.21
27、 数据库创建完成确认窗口图1.22 BookDemo数据库实例服务可以看到服务已经启动，接下来客户端就可以使用Oracle客户端连接BookDemo来使用这个新创建的数据库了，比如图1.23所示是使用Oracle SQL Developer客户端工具连接到BookDemo并查询emp数据表的效果。图1.23 使用Oracle SQL Developer客户端工具连接BookDemo数据库1.2.4 比较Oracle数据库与SQL Server数据库如果读者曾经有过在SQL Server数据库上开发应用程序的经验，在转入到Oracle数据库系统后，需要转变观念，因为Oracle的体系结构与SQL
28、 Server有着本质上的不同，而不只是不同厂商类似的数据库产品。与SQL Server的一个明显的区别是：Oracle数据库系统是一个跨平台的数据库管理系统，可以运行在Windows、UNIX、Linux等操作系统上，而SQL Server只能运行于微软的操作系统平台。在使用SQL Server时，当用户使用企业管理器连接到某个SQL Server实例后，可以同时管理多个数据库，这是因为SQL Server中，实例就是SQL Server服务器引擎，每个引擎都有一套不为其他实例共享的系统及数据库，因此一个实例可以建多个数据库。在Oracle中，实例是由一系列的进程和服务组成的，与数据库可以是
29、一对一的关系，也就是说一个实例可以管理一个数据库；也可以是多对一的关系，也就是说多个实例可以管理一个数据库，其中多个实例组成一个数据库的架构称为集群，简称为RAC，英文全称是Oracle Real Application Clusters。注意：在Oracle中一个实例不能管理多个数据库，这是与SQL Server的一个明显的 区别。大多数情况下，Oracle的实例与数据库都是一对一的关系，比如在笔者的公司，Dev数据库对应了一个Dev的实例，Prod数据库对应了Prod实例。不同的实例对不同的数据库进行管理，实例与数据库的一对一关系如图1.24所示。图1.24 实例与数据库的关系最后在文件存
30、储、日志管理、方案管理、事务处理、安全性管理等方面，两者都存在非常大的区别，本小节并不是要详细对两个数据库进行比较，那将需要较大的篇幅，本小节主要的目的是建议由SQL Server转入Oracle开发的用户转变观念，从头开始认真学习Oracle系统，不要用SQL Server的思维去审识Oracle系统，以免在学习的过程中走弯路。1.3 什么是PL/SQL标准的SQL语言提供了定义和操纵数据库对象的能力，但是并没有提供程序设计语言所具有的诸多特性，比如不支持过程和函数的定义，不能处理运行错误。Oracle公司的PL/SQL是在标准SQL语言的基础上进行过程性扩展后形成的一门程序设计语言，具有第
31、三代编程语言的特性，可以定义变量、常量，具有过程控制、子程序、错误处理等功能。1.3.1 PL/SQL是一种语言PL/SQL，简言之就是为标准SQL语言添加了过程化功能的一门程序设计语言，PL/SQL是Procedural Language/SQL的缩写，如其名所示，该语言通过增加了过程性语言中的结构对SQL进行了扩展。在PL/SQL程序语言中，最基本的单元是语句块，所有的PL/SQL程序都是由语句块构成的，块与块之间可嵌套，在块中可以定义变量、常量，可以使用IF-THEN-ELSE或循环结构，可以定义函数、过程。举个例子，要向在1.1.4小节中创建的员工表中添加一个新的员工，首先判断要添加的员工工号是否存在，如果存在，则更新该工号对应的员工的信息，否则添加一个新的员工。PL/SQL语句如代码1.2所示。代码1.2 PL/SQL代码块示例DECLARE -在PL/SQL匿名块中定义变量 v_EmpNo INT:=102; v_ChsName NVARCHAR2(20):=王五; v_EngName VARCHAR2(20):=Wang wu; v_AlsName VARCHAR2(20):=老五; v_Age INT:=28
