Oracle数据库设计规范（V11）.doc

深圳市规划国土房产信息中心版权所有 文档编号文档编号 SUPL-MANU-SDD-004SUPL-MANU-SDD-004 项目名称项目名称 项目来源项目来源 深圳市规划国土房产深圳市规划国土房产 信息中心信息中心 版本号版本号 1.01.0 生效日期生效日期 OracleOracle数据库设计规范数据库设计规范 修订历史修订历史 版本版本说明说明编制编制批准批准批准日期批准日期 1.01.0 初次编写初次编写 SEPGSEPG 1.11.1 针对代码生成器，增加平台相关内容针对代码生成器，增加平台相关内容黄国良黄国良 0c01e8b391b05f446f37a5a91f67173a.pdf 深圳市规划国土房产信息中心版权所有 i 目目 录录 1.1.目的目的 1 1 2.2.数据库设计原则数据库设计原则 1 1 2.1.2.1.一般性原则一般性原则 1 1 2.2.2.2.数据类型的选择数据类型的选择 1 1 2.3.2.3.事务设计事务设计 2 2 2.4.2.4.安全设计安全设计 2 2 3.3.数据库对象命名规则数据库对象命名规则 3 3 3.1.3.1.T TABLEABLE3 3 3.2.3.2.C COLUMNOLUMN3 3 3.3.3.3.C CONSTRAINTONSTRAINT3 3 3.4.3.4.I INDEXNDEX4 4 3.5.3.5.S SEQUENCEEQUENCE4 4 3.6.3.6.V VIEWIEW4 4 3.7.3.7.S SNAPSHOTNAPSHOT4 4 3.8.3.8.D DATABASEATABASET TRIGGERRIGGER5 5 3.9.3.9.D DATABASEATABASEP PROCEDUREROCEDURE5 5 4.4.PL/SQLPL/SQL 程序开发规范程序开发规范5 5 4.1.4.1.程序注释程序注释 5 5 4.2.4.2.程序结构程序结构 5 5 4.3.4.3.游标（游标（C CURSORURSOR）的使用）的使用 6 6 0c01e8b391b05f446f37a5a91f67173a.pdf 深圳市规划国土房产信息中心版权所有 1 1.1. 目的目的 本规范适用于信息中心新开发系统的 Oracle 数据库设计，以提供设计原则及项目 生命周期内的数据库对象命名规范和 PL/SQL 程序开发规范，并供各系统前期设计人员、 程序开发人员和后期维护人员交流之用。本规范不包含数据库物理设计的内容。 2.2. 数据库设计原则数据库设计原则 数据库设计是信息系统设计的重要组成部分，是系统模型的最原始的表示和反映。 数据库设计的好坏直接关系到开发周期和系统性能。 2.1.2.1. 一般性原则一般性原则 1，数据库对象命名合理，符合命名规则，以便交流。 2，数据存储合理，在不涉及系统运行效率和历史数据一致性的情况下应避免冗余， 尽量符合数据库设计的 3 个范式，即：表中每一个属性列都是原子项，即不可再分的 最小数据单位；表中每行都必须可以被唯一地区分；表中不会有多余的已在其他表中 包含的非关键字信息。但是，如果为了降低数据冗余而设计过多的表间关联，则会对 父表和子表的插入、更新、删除操作增加系统开销，同时增加了表间连接查询的操作 时间，因此为了提供合理的响应时间，合理的数据冗余也是必要的，设计人员应均衡 考虑。 3，数据对象的关系清晰明了，符合业务逻辑。有强制关系的数据库表之间应建立 外键约束。 4，保证数据的完整性与一致性。使用约束（Check）来防止系统操作人员误输入 造成数据的错误是设计人员的一种常用手段，但是，不必要的约束也会占用系统的不 必要开销，应根据系统操作的类型、频度加以均衡考虑。 5，创建索引一般有以下两个目的：维护被索引列的唯一性和提供快速访问表中数 据的策略。添加任何一种索引均能提高按索引列查询的速度，但会降低插入、更新、 删除操作的性能。一般情况下，建立主键时系统自动建立索引，外键也应建立索引。 6，建立新表时应告知 DBA 初始数据量及该表的更新频度，建表语句应指明表空间， 即使是使用缺省的表空间，以便 DBA 更好地规划存储空间。 7，应用程序代码中内嵌的 SQL 语句应尽量使用存储过程实现，以提高代码运行效 率。 8，在做数据库设计时，应详细设计数据库触发器的使用，以实现针对数据库表的 增、删、改前后的连带反映。 2.2.2.2. 数据类型的选择数据类型的选择 数据类型的合理选择对于数据库的性能和操作具有很大的影响，这里主要介绍几 点经验作为参考原则： 0c01e8b391b05f446f37a5a91f67173a.pdf 深圳市规划国土房产信息中心版权所有 2 1，字符串尽量选择 VARCHAR2 型，避免使用 CHAR 型，以节约存储空间。 2，ORACLE7 中的 LONG 和 LONG RAW 字段与 ORACLE8 中的 BLOB 字段属指针型数据， 主要用来存放二进制巨型对象。这类数据的操作相比其它数据类型较慢，且在分布式 处理及查询方面非常不灵活，因此要尽量避开使用。 3，日期型字段的优点是有众多的日期函数支持，因此，在日期的大小比较、加减 操作上非常简单。但是，在按照日期作为条件的查询操作也要用函数，相比其它数据 类型速度上就慢许多,因为用函数作为查询的条件时，服务器无法用先进的性能策略来 优化查询而只能进行表扫描遍历每行。 2.3.2.3. 事务设计事务设计 在 ORACLE 中一个事务是一次性完成的一组操作，由多条 SQL 语句构成，但它们作 为一个单独的操作被全部提交或回滚。设计一个应用的事务是非常重要的，因为一个 事务的设计直接影响了数据库的完整性及应用的性能。特别是分布式事务处理，例如 办文系统中的同步转文，对于同一项任务在多个数据库中的操作，要么全部提交，要 么全部回滚，这些都是在系统设计中应该规划好的。 ORACLE 的两阶段提交机制提供了事务控制的有效手段，对于参与该事务的 ORACLE 数据库服务器来说完全是内部的，设计人员所要做的工作是用一条 COMMIT 语句结束分 布式事务，ORACLE 为你完成剩余的工作。应用程序中要捕捉到提交不成功的事务，回 滚后提供出错提示和出错处理。 2.4.2.4. 安全设计安全设计 ORACLE 利用下列机制管理数据库安全性： 数据库用户和模式； 特权； 角色； 存储设置和空间份额； 资源限制； 审计。 在进行数据库安全设计时应遵循以下原则： 1，用户的帐号/密码尽量不要使用连接数据库的帐号/密码，用户只能通过帐号/ 密码从提供的系统入口访问数据库的数据，真实的数据库帐号/密码对用户是保密的。 2，建立对不同数据库对象有不同操作权限的角色，同一用户可拥有多个角色，通 过角色控制用户对数据库对象的操作权限。 3，设置完善的审计管理，根据功能与数据的重要性详细记录用户模块操作、记录 重要数据的读写操作以及操作的时间等信息，记载重要数据或者完整记录数据的变更 轨迹，便于系统管理员在必要的时候打开日志记录进行检查，以防对系统数据的恶意 0c01e8b391b05f446f37a5a91f67173a.pdf 深圳市规划国土房产信息中心版权所有 3 操作。 3.3. 数据库对象命名规则数据库对象命名规则 3.1.3.1. TableTable Table 名采用英文单词，复合单词用“_”连接，长度控制不超过三个单词，可根 据单词长度取缩写形式。并建议以后新建系统的表名以统一的系统名称缩写词开头， 以方便区分由各个系统所创建的表，例如合同管理系统在建表时统一采用 CON_开头的 表名以方便识别。 Tablealias 取表名各单词首字母，如遇重复则在末尾增加区分字母，如表名为单 个单词则取表明缩写。举例： TableNameTableNameTablealiasTablealias 中文描述中文描述 DOCUMENTSDOCUMENTSDOC 文件列表 LAND_REGLAND_REG LR（有重复时可取 LREG）土地登记 3.2.3.2. ColumnColumn 1Column 名采用英文单词，复合单词用“_”连接，并根据单词长度取缩写形式， 长度界限大致为 5 个字母，缩写格式举例： ColumnNameColumnName 英文描述英文描述中文描述中文描述 LAND_TYPELAND_TYPELandType 土地类型 APP_UNITAPP_UNITApplyingUnit 申请单位 APP_NAMEAPP_NAMEApplicantName 申请人 2PrimaryKeyColumn 如果是由 Sequence 产生的流水号则不遵从以上格式，采用 Tablealias+_NO组合的格式，例如：LR_NO（土地登记的流水号） 。 3ForeignKeyColumn 名不遵从以上格式，采用 ReferenceTablealias+_ +ReferencePrimaryKeyColumnName 格式，例如：LR_LR_NO。 3.3.3.3. ConstraintConstraint 1PrimaryKeyConstraint： Tablealias+PK ，如 DOC_PK,LR_PK 2UniqueKey： Tablealias+UK+number，如 LR_UK_1，LR_UK_2 3ForeignKeyConstriant： Tablealias+ReferenceTablealias+FK+number，如 LR_DOC_FK_1 4CheckConstraint Tablealias+CK+number，例如 LR_CK_1，LR_CK_2 0c01e8b391b05f446f37a5a91f67173a.pdf 深圳市规划国土房产信息中心版权所有 4 3.4.3.4. IndexIndex 1PrimaryKeyIndex Tablealias+PK ，如 LR_PK 2ForeignKeyIndex Tablealias+ReferenceTablealias+ID+number，如 LR_DOC_ID_1 3OtherIndexes Tablealias+自定义名+ID+number，如 LR_TREES_ID_1 3.5.3.5. SequenceSequence 1SequenceforPrimaryKeycolumn SEQ_+Tablealias+_NO ，如 SEQ_LR_NO。 2Sequenceforgeneralcolumn SEQ+Tablealias+Columnname，如 SEQ_LR_BAS_NO。 3.6.3.6. ViewView V$打头，分两大类： 完全 VIEW：基于单个表的全部行、列 非完全 VIEW：基于单个表的部分行列或基于多个表的 VIEW 1基于本地的完全 VIEW：V$+表名，如 V$DOC_TASK 2基于远程的完全 VIEW：V$+表名+_XX ，如其他局基于罗湖的视图 V$PARCEL_LH 3基于本地的非完全 VIEW：V$+自定义名(如 Tablealias 组合) 4基于远程的非完全 VIEW：V$+自定义名(如 Tablealias 组合)+_XX 其中 XX=地点： HQ 市局 LH 罗湖分局 FT 福田分局 NS 南山分局 YT 盐田分局 BA 宝安分局 LG 龙岗分局 GY 大工业分局 3.7.3.7. SnapshotSnapshot 以下 XX 代表地点，参见 View 的命名。 1本地简单快照：S$+表名 2远程简单快照：S$+表名+XX 0c01e8b391b05f446f37a5a91f67173a.pdf 深圳市规划国土房产信息中心版权所有 5 3本地复杂快照：S$+自定义名 4远程复杂快照：S$+自定义名+XX 3.8.3.8. DatabaseTriggerDatabaseTrigger TR+Tablealias+FF，其中：FF 为 trigger 类别 BI BeforeInsert BU BeforeUpdate BD BeforeDelete AI AfterInsert AU AfterUpdate AD AfterDelete 例如 TR_LR_BI,为表 LAND_REG 的插入前触发器。 3.9.3.9. DatabaseProcedureDatabaseProcedure PRC+自定义名，其中，自定义名必须用一个或多个能简要描述其功能的英语 单词来表示。 4.4. PL/SQLPL/SQL 程序开发规范程序开发规范 下面简要介绍编写 PL/SQL 块、存储过程、函数与包时应遵守的几点规范。 4.1.4.1. 程序注释程序注释 每个存储过程或程序单元都应编写注释头，说明作者，功能和创立时间（修改时 间）等信息。 例如： 某个存储过程的注释如下： -用途：读取一个企业枚举项下的项目评估统计年报表 -用于效绩评价和保值增值考核 -相关存储过程：NIL -相关表：ApplicationGroup,ApplicationRole -变更历史 -时间人员描述 -2003-3-15yangzy 创建 -2003-4-20yzy 修改 4.2.4.2. 程序结构程序结构 应用程序或 SQL 代码中执行 insert/update/delete 语句应有出错提示及出错处理。 存储过程和函数的基本结构如下： 0c01e8b391b05f446f37a5a91f67173a.pdf 深圳市规划国土房产信息中心版权所有 6 存储过程： CREATE PROCEDURE 过程名 (参数说明 1，参数说明 2，) IS 局部说明 BEGIN 执行语句； EXCEPTION 出错处理； END 过程名； 存储函数： CREATE FUNCTION 函数名 (参数说明 1，参数说明 2，) RETURN 类型 IS 局部说明 BEGIN 执行语句； RETURN 语句； EXCEPTION 出错处理； END 函数名； 4.3.4.3. 游标（游标（CursorCursor）的使用）的使用 游标提供了对特定集合中逐行扫描的手段，一般使用游标逐行遍历数据，根据取 出的数据不同条件进行不同的操作。尤其对多表和大表定义的游标（大的数据集合） 循环很容易使程序进入一个漫长的等特甚至死机，例如在某市住房公积金管理系统 进行日终帐户滚积数计息处理时，对一个 10 万个帐户的游标处理导致程序进入了一个 无限期的等特（后经测算需 48 个小时才能完成） ，后根据不同的条件改成用不同的 UPDATE 语句得以在二十分钟之内完成。示例如下： Declare cursor Mycursor is select count_no from COUNT; Begin Open Mycursor; Loop Fetch Mycursor into vcount_no; Exit when Mycursor%NotFound; If vcount_no=条件 1 then 操作 1; ElsIf vcount_no=条件 2 then 操作 2; 0c01e8b391b05f446f37a5a91f67173a.pdf 深圳市规划国土房产信息中心版权所有