《数据库设计规范》word版

1、神州泰岳数据库设计规范北京神州泰岳软件股份有限公司 TIME yyyy年M月d日 2010年11月11日文档属性属性内容客户名称项目名称文档主题文档副标题文档版本文档日期文档状态草稿作者刘建国文档变更版本修订日期修订人描述0.12010-9-15刘建国初稿0.22010-9-20刘建国整理用户权限、密码规范及索引指导文档0.32010-10-9田可风将用户权限、用户密码规范文档等多个文档整理在一起0.42010-10-12田可风添加物理设计规范内容，及将审核检查项整理附于规范末尾，方便检索规范内容0.52010-10-16刘建国审核整理文档送呈姓名目的目 录 TOC o 1-3 h z HYP

2、ERLINK l _Toc275111680 1前言 PAGEREF _Toc275111680 h 6 HYPERLINK l _Toc275111681 2数据库的设计方法及流程 PAGEREF _Toc275111681 h 7 HYPERLINK l _Toc275111682 2.1设计方法 PAGEREF _Toc275111682 h 7 HYPERLINK l _Toc275111683 2.2设计流程 PAGEREF _Toc275111683 h 8 HYPERLINK l _Toc275111684 需求分析阶段 PAGEREF _Toc275111684 h 8 HYP

3、ERLINK l _Toc275111685 概念结构设计阶段 PAGEREF _Toc275111685 h 9 HYPERLINK l _Toc275111686 逻辑设计阶段 PAGEREF _Toc275111686 h 9 HYPERLINK l _Toc275111687 物理设计阶段 PAGEREF _Toc275111687 h 9 HYPERLINK l _Toc275111688 数据库实施阶段 PAGEREF _Toc275111688 h 10 HYPERLINK l _Toc275111689 数据库运行维护阶段 PAGEREF _Toc275111689 h 10

4、HYPERLINK l _Toc275111690 建模工具 PAGEREF _Toc275111690 h 10 HYPERLINK l _Toc275111691 3数据库设计规范 PAGEREF _Toc275111691 h 11 HYPERLINK l _Toc275111692 3.1数据库规范化的五个要求 PAGEREF _Toc275111692 h 11 HYPERLINK l _Toc275111693 要求一：表中应该避免可为空的列 PAGEREF _Toc275111693 h 11 HYPERLINK l _Toc275111694 要求二：表不应该有重复的值或者列

5、PAGEREF _Toc275111694 h 11 HYPERLINK l _Toc275111695 要求三：表中记录应该有一个唯一的标识符 PAGEREF _Toc275111695 h 12 HYPERLINK l _Toc275111696 要求四：数据库对象要有统一的前缀名 PAGEREF _Toc275111696 h 12 HYPERLINK l _Toc275111697 要求五：尽量只存储单一实体类型的数据 PAGEREF _Toc275111697 h 12 HYPERLINK l _Toc275111698 3.2对象命名规范 PAGEREF _Toc275111698

6、 h 13 HYPERLINK l _Toc275111699 规则 PAGEREF _Toc275111699 h 13 HYPERLINK l _Toc275111700 表命名规范 PAGEREF _Toc275111700 h 14 HYPERLINK l _Toc275111701 字段命名规范 PAGEREF _Toc275111701 h 14 HYPERLINK l _Toc275111702 索引命名规范 PAGEREF _Toc275111702 h 15 HYPERLINK l _Toc275111703 分区命名规范 PAGEREF _Toc275111703 h 16

7、 HYPERLINK l _Toc275111704 视图/物化视图命名规范 PAGEREF _Toc275111704 h 16 HYPERLINK l _Toc275111705 触发器/函数/存储过程命名规范 PAGEREF _Toc275111705 h 17 HYPERLINK l _Toc275111706 3.3数据库编程规范 PAGEREF _Toc275111706 h 17 HYPERLINK l _Toc275111707 书写规范 PAGEREF _Toc275111707 h 17 HYPERLINK l _Toc275111708 注释规范 PAGEREF _Toc

8、275111708 h 20 HYPERLINK l _Toc275111709 语法规范 PAGEREF _Toc275111709 h 23 HYPERLINK l _Toc275111710 SQL性能规范 PAGEREF _Toc275111710 h 26 HYPERLINK l _Toc275111711 JOB使用规范 PAGEREF _Toc275111711 h 34 HYPERLINK l _Toc275111712 3.4索引使用规范 PAGEREF _Toc275111712 h 34 HYPERLINK l _Toc275111713 创建索引原则 PAGEREF _

9、Toc275111713 h 34 HYPERLINK l _Toc275111714 索引使用建议 PAGEREF _Toc275111714 h 35 HYPERLINK l _Toc275111715 总结 PAGEREF _Toc275111715 h 40 HYPERLINK l _Toc275111716 3.5分区表使用规范 PAGEREF _Toc275111716 h 40 HYPERLINK l _Toc275111717 3.6物理设计规范 PAGEREF _Toc275111717 h 41 HYPERLINK l _Toc275111718 环境配置 PAGEREF

10、_Toc275111718 h 41 HYPERLINK l _Toc275111719 数据库配置 PAGEREF _Toc275111719 h 41 HYPERLINK l _Toc275111720 其他参数配置 PAGEREF _Toc275111720 h 42 HYPERLINK l _Toc275111721 控制文件 PAGEREF _Toc275111721 h 42 HYPERLINK l _Toc275111722 日志文件 PAGEREF _Toc275111722 h 43 HYPERLINK l _Toc275111723 表空间及数据文件设计原则 PAGEREF

11、 _Toc275111723 h 43 HYPERLINK l _Toc275111724 4数据库安全规范 PAGEREF _Toc275111724 h 45 HYPERLINK l _Toc275111725 4.1用户密码规范 PAGEREF _Toc275111725 h 45 HYPERLINK l _Toc275111726 4.2用户权限规范 PAGEREF _Toc275111726 h 48 HYPERLINK l _Toc275111727 不同应用分配不同帐号 PAGEREF _Toc275111727 h 48 HYPERLINK l _Toc275111728 删除

12、或锁定无关帐号 PAGEREF _Toc275111728 h 48 HYPERLINK l _Toc275111729 限制SYSDBA远程登录 PAGEREF _Toc275111729 h 48 HYPERLINK l _Toc275111730 限制业务用户权限 PAGEREF _Toc275111730 h 48 HYPERLINK l _Toc275111731 对用户的属性进行控制， PAGEREF _Toc275111731 h 48 HYPERLINK l _Toc275111732 启用数据字典保护 PAGEREF _Toc275111732 h 48 HYPERLINK

13、l _Toc275111733 4.3数据库监听规范 PAGEREF _Toc275111733 h 49 HYPERLINK l _Toc275111734 需要时为监听设置密码 PAGEREF _Toc275111734 h 49 HYPERLINK l _Toc275111735 需要时设置信任IP集 PAGEREF _Toc275111735 h 49 HYPERLINK l _Toc275111736 5数据库评审 PAGEREF _Toc275111736 h 50 HYPERLINK l _Toc275111737 5.1评审范围 PAGEREF _Toc275111737 h

14、50 HYPERLINK l _Toc275111738 5.2评审标准 PAGEREF _Toc275111738 h 50 HYPERLINK l _Toc275111739 6附录 PAGEREF _Toc275111739 h 53 HYPERLINK l _Toc275111740 6.1用户权限分配规范 PAGEREF _Toc275111740 h 53 HYPERLINK l _Toc275111741 6.2数据库安装文档 PAGEREF _Toc275111741 h 53 HYPERLINK l _Toc275111742 6.3分区实践文档 PAGEREF _Toc27

15、5111742 h 53前言为明确公司项目中数据库逻辑设计及物理设计的内容和流程，特制定本规范，供数据库设计、开发及维护人员参考。本规范属于参考性规范，由DBA组制定。数据库的设计方法及流程设计方法数据库设计方法目前可分为四类：直观设计法、规范设计法、计算机辅助设计法和自动化设计法。其中，新奥尔良法是目前公认的比较完整和权威的一种规范设计法。新奥尔良法将数据库设计分成需求分析（分析用户需求）、概念设计（信息分析和定义）、逻辑设计（设计实现）和物理设计（物理数据库设计）。目前，常用的规范设计方法大多起源于新奥尔良法，并在设计的每一阶段采用一些辅助方法来具体实现。以下是两种常用的规范设计方法：基于

16、E-R模型的数据库设计方法。该方法是由P.P.S.chen于1976年提出的数据库设计方法，其基本思想是在需求分析的基础上，用E-R（实体联系）图构造一个反映现实世界实体之间联系的企业模式，然后再将此企业模式转换成基于某一特定的DBMS的概念模式。基于3NF的数据库设计方法。该方法是由SAtre提出的结构化设计方法，其基本思想是在需求分析的基础上，确定数据库模式中的全部属性和属性间的依赖关系，将它们组织在一个单一的关系模式中，然后再分析模式中不符合3NF的约束条件，将其进行投影分解，规范成若干个3NF关系模式的集合。 其具体设计步骤分为五个阶段： 设计企业模式，利用规范化得到的3NF关系模式画

17、出企业模式； 设计数据库的概念模式，把企业模式转换成DBMS所能接受的概念模式，并根据概念模式导出各个应用的外模式； 设计数据库的物理模式（存储模式）； 对物理模式进行评价； 实现数据库。设计流程以规范性设计为例，把数据库设计流程分为以下几个阶段：需求分析阶段：综合各个用户的应用需求(现实世界的需求)。概念设计阶段：形成独立于机器和各DBMS产品的概念模式(信息世界模型)，用E-R图来描述。逻辑设计阶段：将E-R图转换成具体的数据库产品支持的数据模型，如关系模型，形成数据库逻辑模式。然后根据用户处理的要求，安全性的考虑，在基本表的基础上再建立必要的视图(VIEW)形成数据的外模式。物理设计阶段

18、：根据DBMS特点和处理的需要，进行物理存储安排，设计索引，形成数据库内模式。数据库实施阶段数据库运行与维护阶段需求分析阶段需求收集和分析，得到数据字典描述的数据需求和数据流图描述的处理需求。需求分析的重点：调查、收集与分析用户在数据管理中的信息要求、处理要求、安全性与完整性要求。需求分析的方法：调查组织机构情况、各部门的业务活动情况、协助用户明确对新系统的各种要求、确定新系统的边界。常用的调查方法有： 跟班作业、开调查会、请专人介绍、询问、设计调查表请用户填写、查阅记录。分析和表达用户需求的方法：主要包括自顶向下和自底向上两类方法。采用逐层分解的方式分析系统，并把每一层用数据流图和数据字典描

19、述。数据流图（Data Flow Diagram，DFD）表达了数据和处理过程的关系。系统中的数据则借助数据字典(Data Dictionary，简称DD)来描述。概念结构设计阶段概念模型用于信息世界的建模。概念模型不依赖于某一个DBMS支持的数据模型。通过对用户需求进行综合、归纳与抽象，形成一个独立于具体DBMS的概念模型，可以用E-R图表示。概念模型可以转换为计算机上某一DBMS支持的特定数据模型。概念模型特点：具有较强的语义表达能力，能够方便、直接地表达应用中的各种语义知识。应该简单、清晰、易于用户理解，是用户与数据库设计人员之间进行交流的语言。逻辑设计阶段将概念结构转换为某个DBMS所

20、支持的数据模型(例如关系模型)，并对其进行优化。将E-R图转换为关系模型实际上就是要将实体、实体的属性和实体之间的联系转化为关系模式,这种转换一般遵循如下原则：一个实体型转换为一个关系模式，实体的属性就是关系的属性，实体的码就是关系的码。一个多对多的联系转换为一个关系模式，两个实体的码和联系本身的属性就是关系的属性，两个实体的码就是关系的码。数据模型的优化，确定数据依赖，消除冗余的联系，确定各关系模式分别属于第几范式。确定是否要对它们进行合并或分解。一般来说将关系分解为3NF的标准。物理设计阶段为逻辑数据模型选取一个最适合应用环境的物理结构(包括存储结构和存取方法)。根据DBMS特点和处理的需

21、要，进行物理存储安排，设计索引，形成数据库内模式。数据库实施阶段运用DBMS提供的数据语言(例如SQL)及其宿主语言(例如C)，根据逻辑设计和物理设计的结果建立数据库，编制与调试应用程序，组织数据入库，并进行试运行。 数据库实施主要包括以下工作：用DDL定义数据库结构、组织数据入库 、编制与调试应用程序、数据库试运行。数据库运行维护阶段在数据库系统运行过程中必须不断地对其进行评价、调整与修改。内容包括：数据库的转储和恢复、数据库的安全性、完整性控制、数据库性能的监督、分析和改进、数据库的重组织和重构造。建模工具为加快数据库设计速度，目前有很多数据库辅助工具(CASE工具)，如Rational公

22、司的Rational Rose，CA公司的Erwin和Bpwin，Sybase公司的PowerDesigner以及Oracle公司的oracle Designer等。数据库设计规范数据库规范化的五个要求要求一：表中应该避免可为空的列 虽然表中允许空列，但是，空字段是一种比较特殊的数据类型。数据库在处理的时候，需要进行特殊的处理。如此的话，就会增加数据库处理记录的复杂性。当表中有比较多的空字段时，在同等条件下，数据库处理的性能会降低许多。所以，虽然在数据库表设计的时候，允许表中具有空字段，但是，我们应该尽量避免。若确实需要的话，我们可以通过一些折中的方式，来处理这些空字段，让其对数据库性能的影响

23、降低到最少。一是通过设置默认值的形式，来避免空字段的产生。二是若一张表中，允许为空的列比较多，接近表全部列数的三分之一。而且，这些列在大部分情况下，都是可有可无的。若数据库管理员遇到这种情况，建议另外建立一张副表，以保存这些列。然后通过关键字把主表跟这张副表关联起来。将数据存储在两个独立的表中使得主表的设计更为简单，同时也能够满足存储空值信息的需要。要求二：表不应该有重复的值或者列如进销存管理中，还需要对客户的联系人进行管理。而一个客户的联系人可能有多个，为了解决这个问题，有多种实现方式。若设计不合理的话在，则会导致重复的值或者列。我们可以这么设计，把客户信息、联系人都放入同一张表中。为了解决

24、多个联系人的问题，可以设置第一联系人、第一联系人电话、第二联系人、第二联系人电话等等。若还有第三联系人、第四联系人等等，则往往还需要加入更多的字段。可是这么设计的话，会产生一系列的问题。如客户的采购员流动性比较大，在一年内换了六个采购员。直接修改又不利于追踪。所以，在数据库设计的时候要尽量避免这种重复的值或者列的产生。建议，若数据库管理员遇到这种情况，可以改变一下策略。如把客户联系人另外设置一张表。然后通过客户ID把供应商信息表跟客户联系人信息表连接起来。也就是说，尽量将重复的值放置到一张独立的表中进行管理。然后通过视图或者其他手段把这些独立的表联系起来。要求三：表中记录应该有一个唯一的标识符

25、在数据库表设计的时候，数据库管理员应该养成一个好习惯，用一个ID号来唯一的标识行记录，而不要通过名字、编号等字段来对纪录进行区分。每个表都应该有一个ID列，任何两个记录都不可以共享同一个ID值。另外，这个ID值最好有数据库来进行自动管理，而不要把这个任务给前台应用程序。否则的话，很容易产生ID值不统一的情况。另外，在数据库设计的时候，最好还能够加入行号。ID号是用户不能够维护的。但是，行号用户就可以维护。这是在实际应用程序设计中对ID列的一个有效补充。要求四：数据库对象要有统一的前缀名一个比较复杂的应用系统，其对应的数据库表往往以千计。若让数据库管理员看到对象名就了解这个数据库对象所起的作用，

26、恐怕会比较困难。而且在数据库对象引用的时候，数据库管理员也会为不能迅速找到所需要的数据库对象而头疼。建议：在开发数据库之前，最好能够花一定的时间，去制定一个数据库对象的前缀命名规范。最常用的是根据前台应用程序的模块来定义后台数据库对象前缀名。需要注意的是，这个命名规范应该在数据库管理员与前台应用程序开发者之间达成共识，并且严格按照这个命名规范来定义对象名。其次，表、视图、函数等最好也有统一的前缀。如视图可以用V为前缀，而函数则可以利用F为前缀。如此数据库管理员无论是在日常管理还是对象引用的时候，都能够在最短的时间内找到自己所需要的对象。要求五：尽量只存储单一实体类型的数据实体类型跟数据类型不是

27、一回事，要注意区分。如现在有一个图书馆里系统，有图书基本信息、作者信息两个实体对象。若用户要把这两个实体对象信息放在同一张表中也是可以的。如可以把表设计成图书名字、图书作者等等。可是如此设计的话，会给后续的维护带来不少的麻烦。如当后续有图书出版时，则需要为每次出版的图书增加作者信息，这无疑会增加额外的存储空间，也会增加记录的长度。而且若作者的情况有所改变，如住址改变了以后，则还需要去更改每本书的记录。同时，若这个作者的图书从数据库中全部删除之后，这个作者的信息也就荡然无存了。很明显，这不符合数据库设计规范化的需求。遇到这种情况时，建议可以把上面这张表分解成三种独立的表，分别为图书基本信息表、作

28、者基本信息表、图书与作者对应表等等。如此设计以后，以上遇到的所有问题就都引刃而解了。对象命名规范规则【规则-1】：Oracle数据库对象的名字由字母、数字和一些连接符组成。1、多个单词之间用连接符分开，连接符用“_”字符，不建议使用其他的字符；2、例如：存储过程：sp_create_accbak_table【规则-2】：Oracle数据库对象的名字长度不超过30个字符。1、如果超过30个字符，可以缩写，但一定要采用标准的缩写，常用标准缩写见附件。如果表或者是字段的名称仅有一个单词，那么建议不使用缩写，而是用完整的单词；2、例如：表:ATP_PARA_CFG【规则-3】：在创建数据库对象时，不要

29、使用引号。1、说明：Oracle数据库自动转换为大写字符；避免使用系统保留字作为数据库对象的名称。2、例：建存储过程SP_SET_PROCESS_STATUS时写法正确写法：CREATE OR REPLACE PROCEDURE sp_set_process_status end sp_set_process_status;错误写法：CREATE OR REPLACE PROCEDURE “sp_set_process_status” end sp_set_process_status;表命名规范表的命名遵循数据库对象命名的一般规则：【规则-1】：要求表的命名尽量采用标准的翻译，能准确地表达该

30、表的中文含义，能根据英文猜测到表的用途。例：gsm_user 用户资料表、user_imei 用户IMEI信息。【规则-2】对于一些特定作用的表，除了遵循普通表命名规范外，增加特定后缀表示。例：配置历史表表示为XXX_cfg_his。字段命名规范字段命名遵循数据库对象命名的一般规则： 【规则-1】：字段的命名由英文单词(或缩写)和下划线“_”组成，多个单词之间用下划线分开。例：user_id、package_code等。【规则-2】：要求字段名能表达字段的含义。例如：Fail（Fail）：失败记录表History（ His）：历史Identity（ID）：ID号【规则-3】：字段长度，根据业务

31、需要定义字典长度，对字符型字段，尽量用varchar2类型，不要用char、varchar类型。【规则-4】：如果相同字段在不同的表中出现，要使用相同的命名，且必须保证他们的类型和长度是相同的。例：gsm_user的字段gsm_user_id，和表user_package的字段gsm_user_id，长度和类型一致。索引命名规范【规则-1】：IDX_表名缩写_字段名缩写。例：CLERK_OPERATING_DETAIL表CHECKOUT_SRL列上的索引命名：IDX_COD_CHECKOUT_SRL。【规则-2】：如果是复合索引，字段名缩写至少要包含复合索引的头两个字段名的缩写。例如：CLER

32、K_OPERATING_DETAIL表（OPERATOR_ID,CHECKOUT_SRL）列上的复合索引命名为：IDX_COD_OPID_CHKSRL；【规则-3】：如果是主键索引，以“PKX”为索引前缀。例：CLERK_OPERATING_DETAIL表(OPERATING_SRL,CODE_COUNTY)上的主键索引命名为：PKX_COD_OPSRL_CODECTY。【规则-4】：给表增加主键时要显式指定约束的名字，并且要指定using index tablespace 参数。例：alter table CLERK_OPERATING_DETAILadd constraint PKX_CO

33、D_OPSRL_CODECTY -主键约束名，也是索引名primary key (OPERATING_SRL, CODE_COUNTY) using index tablespace ;不能写成alter table CLERK_OPERATING_DETAIL add primary key (OPERATING_SRL, CODE_COUNTY);如果不显式指定约束名系统将随机分配一个约束名字(同时也是主键索引的名字) 如果不指定using index tablespace 参数，索引会建在该用户默认的表空间上，不利于数据库性能和系统维护。分区命名规范表名各单词第一个字母的缩写_P例1：B

34、ILL_ADJUST_SEQ 表的分区名为：BAS_P01BAS_P13例2：按月份分区的分区分区命名：表名各单词第一个字母的缩写_P（年月），如CLERK_OPERATING_DETAIL表分区名为：COD_P0507COD_P0512；注：COD是表的简称，0507是指2005年7月的分区。视图/物化视图命名规范【规则-1】：视图的命名规范遵循普通表的命名规范，并在视图前加上“v_”前缀，表示是视图。例：v_cardinfo。【规则-2】：物化视图命名规则：MV_源表名_源数据库。例：mv_gsm_user_11， mv_gsm_user_21, mv_gsm_user_41，分别表示从z

35、wdb1，zwdb2和zwdb4复制过来的gsm_user表的数据。触发器/函数/存储过程命名规范【规则-1】：触发器的命名规则：trg_表名_说明：其中表名为触发源表名，ins/upd/del表示insert/update/delete触发器。例如：trg_msn_member_del，触发器的含义为：create or replace trigger ti_msn_member_delbefore delete on msn_member for each rowdeclare beginend【规则-2】：存储过程的命名以“sp_”打头说明：其他规范遵循普通表的命名规范，要求名字能表达存

36、储过程的用途。例：sp_pre_score_modify、sp_create_scp_operation。【规则-3】：函数的命名以“fn_”打头说明：其他规范遵循普通表的命名规范，要求名字能表达函数的用途。例：fn_get_packname、fn_check_brand_package。数据库编程规范书写规范【规则-1】：所有代码统一使用小写字母书写。例：create or replace procedure sp_check_package( i_package_code number, i_user_id number, o_flag out number ) is begin o_fl

37、ag := 0; end sp_check_package;/【规则-2】：确保变量和参数在类型和长度上与表数据列类型和长度相匹配。说明：如果与表数据列宽度不匹配，则当较宽或较大的数据传进来时会产生运行异常。【规则-3】：参数和变量的命名符合如下规范：1、传入参数以“i_”为前缀；2、传出参数以“o_”为前缀；3、变量以“v_”为前缀4、例如：create or replace procedure SP_GET_GLOBAL(i_operator_id in number, i_session_id in number, o_flag out number) is v_cityID varch

38、ar(2); v_region_level number(1);begin.end;【规则-4】：程序块中的begin、end独立成行。【规则-5】：程序块采用缩进风格书写，保证代码清晰易读，风格一致，缩进格数统一。例： beginupdate CFG_CDR_REFRESH set CHANGE_TIME=sysdate where CFG_TYPE=5;if sql%rowcount = 0 theninsert into CFG_CDR_REFRESH(CFG_TYPE,CHANGE_TIME,STATE)values(5,sysdate,0);end if; end trg_CFG_C

39、ITY_HOST_QRY_ALL;【规则-6】：不允许把多个语句写在一行中，即一行只写一条语句。【规则-7】：同一条语句占用多于一行时，每行的第一个关键字应当左对齐，例：select * into v_gsm_user from gsm_userwhere msisdn = to_number(trim(i_phone_number)and user_city = i_city_id;【规则-8】：对于 Insert values 和 update 语句，一行写一个字段，字段后面紧跟注释（注释语句左对齐），values 和 insert 左对齐，左括号和右括号与 insert、values 左

40、对齐例：insert into table_name(user_id,-用户 ID，主键user_name,-用户名login_name -登录名)values( v_user_id,v_user_name,v_login_name）【规则-9】：相对独立的程序块之间需加空行。例：-判断套餐是否可增加成员并获得专网标志 begin select use_date, decode(package_type, 1000, 1, 0) exceptionend;-如果为手机号码if i_number_type = 0 thenbegin end;【规则-10】：超过110列的语句要分行书写，长表达式

41、应在低优先级操作符处换行，操作符或关键字放在新行之首。例：v_exchangeable_score := (v_score_phone_score + v_score_bounty_score + v_score_gift_score - v_score_alr_excg_score)+ (v_pre_score_phone_score + v_pre_score_bounty_score - v_pre_score_alr_excg_score)注释规范【规则-1】：所有变量定义都要加上注释，说明变量的用途及含义。例：create or replace procedure sp_sms_zo

42、neas v_city number(2); -监控信息变量 v_db number(2); -数据库代码 v_Total number(10) :=0; -发送总数 v_Succ number(10) :=0; -成功数 v_Fail number(10) :=0; -没有发送数 v_Srl number(10) ; -记录流水o_flag number; -调用sp_send_query_sms返回的标识 end sp_sms_zone;/【规则-2】：注释内容要清晰、明了，含义准确，防止注释二义性。例：正确：ret_code out integer,-返回代码(0 成功 ,其它：错误,参

43、见：错误代码定义)；错误：ret_code out integer,-返回代码【规则-3】：对存储过程的任何修改，都需要在注释最后添加修改人、修改日期及修改原因等信息。例：/*修改原因：修改积分兑换和积分转赠修改人：* 修改时间：2006-01-05*/【规则-4】：对程序分支必须书写注释。例：-如果为手机号码if i_number_type = 0 thenbegin end;【规则-5】：在代码的功能、意图层次上进行注释，帮助维护人员理解代码。例：case rdm_type1 when 1 then -积分奖励 . . when 2 then -扣减(停用) . . when 3 then

44、 -积分兑换 . .end case;【规则-6】：代码注释应放在描述的代码上方或右方相近位置，不可放在下面。例：-查询转出用户的积分信息SP_SCORE_QUERY( s_user_id, -用户id rdm_city_id, -地市id )【规则-7】：注释与所描述的内容进行同样的缩排。例：-未达到可兑换标准if v_score_change_flag=0 then -对于兑换类型为961的，取消1000分门限的限制。 if rdm_type2 = 961 or rdm_type2 = 962 then end if;end if;【规则-8】：函数应对返回的代码进行详细描述。例：crea

45、te or replace function fn_check_brand_package( i_package number, i_user_id number, i_brand_id number := 0, i_pay_mode number := 0 ) return number isResult number(1):=0; -0表示没有限制 ,其他表示有品牌限制 end fn_check_brand_package;【规则-9】：在程序块的结束行右方加注释，以表示程序块结束。例：-未达到可兑换标准if v_score_change_flag=0 then end if;-未达到可兑

46、换标准处理结束【规则-10】：统一文件头的注释。例：create or replace procedure sysmgmt.sp_encrypt3(i_pwdin varchar2,o_new_pwdout varchar2,o_flag out number) is /*功能描述：密码加密脚本，本存储过程用于把指定的明文密码转化为密文参数描述：pwd：输入明文密码，new_pwd：输出的密文，o_flag：输出结果的状态(0：right，1：error)修改历史： 2005-12-28 *，修改加密方式为密钥方式； 2005-05-16 *，修改加密算法；2005-01-01 *，创建脚本；

47、*/bl1varchar(1);bl2varchar(1);BEGIN end;语法规范【规则-1】：存储过程的In、out参数应按类别分开书写，不要交叉。例：create or replace procedure sp_create_scp( i_gsm_user_id number, -用户号码 i_order_code number, -命令代码 i_operation_type number, -工单类型 i_operating_srl number, -操作流水 o_operation_srl out number, -工单流水（返回参数） o_flag out number, -成

48、功 0 ，失败 1 o_msg out varchar2 )begin end;【规则-2】：存储过程中变量的声明应集中在is和begin关键字之间申明例:create or replace procedure sp_create_scp( )is v_user gsm_user%rowtype; -用户信息 v_value varchar2(30); nCnt number(5):=0; nPriority number(2):=0;begin end;【规则-3】：尽可能使用相关表字段类型来定义相关的变量，如%type，%rowtype。说明：一般变量，都可能会用于表的查询条件，或增加记录

49、的值；当字段数据类型发生变更时，对应的变量无需修改。例：v_user gsm_user%rowtype; -用户信息select * from gsm_user where gsm_user_id = v_user;当gsm_usre_id的类型变成字符型时，该段代码无需修改。【规则-4】：确保所有的变量和参数都用到，没有用到的变量和参数要删除。【规则-5】：存储过程有多个分支返回时，若有事务控制，需确保各个分支都结束事务。异常时，应该在Exception中捕捉异常，并进行事务处理。【规则-6】：存储过程：不要在异常部分，进行正常的业务处理。说明：从程序的易读性、易维护性考虑，不要在异常处理部

50、分，包含正常的业务处理部分。例：（不好的写法）exceptionWhen no_data_found then-没有找到记录，则重新查找其他数据Select * from .End;【规则-7】：原则上不要使用动态 sql，如果必须使用，需绑定变量，绑定变量写法：QUERY_STRING :=select pr_template from bssp.bssp_process_template where process_id=:ora_process_id for update; EXECUTE IMMEDIATE QUERY_STRING INTO LOB USING 38880003;【规

51、则-8】：代码中的异常捕获返回结果，要加上Oracle的异常sqlerrm（Oracle的一个session级的全局变量）。例：exception when others thenrollback; oResult :=1; oMsg := others|substr(sqlerrm,200);end;【规则-10】：捕捉到异常后需要rollback回滚事务。例：exception when others thenrollback; oResult :=1; oMsg := others|sqlerrm;end;SQL性能规范批量操作，避免频繁使用commit频繁的COMMIT会导致物理I/O

52、增大，同时会产生log file sync等待，但长时间不提交将带来更多的性能问题。建议小于3秒的事务可以一次提交，大于3秒的操作尽可能3秒左右提交一次。实际应用中使用COMMIT时必须保证事务的完整性。避免动态SQL，要使用绑定变量方式为了不重复解析相同的SQL语句，在第一次解析之后，ORACLE将SQL语句缓存在内存中，当你执行一个SQL语句时，如果它和之前的执行过的语句完全相同，ORACLE就能很快获得内存中相同语句的执行计划，然后就直接执行该语句，而不需要进行解析。如果没有使用绑定变量，虽然语句结构相同，但因传入的变量不一样导致语句不相同，ORACLE还是需要对每条结构相同的语句重新解

53、析一遍，同时在内存中缓存该语句，导致共享池中存放了N条结构相同的语句，达不到共享的目的，又浪费了大量的内存空间，如果语句执行的太频繁，会导致数据库无法申请共享内存而出错。Java代码示例：未使用绑定变量：.try String sSql = select * from my_table where field=1; pStmt = connection.prepareStatement(sSql); pstmt.executeUpdate(); /执行动态SQL语句 .使用绑定变量：.tryString sSql = select * from my_table where field=?;p

54、Stmt = connection.prepareStatement(sSql);pstmt.setString(1, sCode); /sCode为传入变量 pstmt.executeUpdate(); /执行动态SQL语句 .C/C+代码示例未使用绑定变量：/处理传入的参数，构造动态SQL语句 sprintf(sztmp1, SELECT TO_CHAR(apply_date, yyyymmddhh24miss), FROM user_smscall WHERE (gsm_user_id = %s ) , ora_gsm_user_id); EXEC SQL PREPARE sql FRO

55、M :sqlstmt; /Prepare /定义游标EXEC SQL declare user_smscall_cu_1 cursor for sql;EXEC SQL open user_smscall_cu_1;使用绑定变量：/处理传入的参数，构造动态SQL语句 sprintf(sztmp1, SELECT TO_CHAR(apply_date, yyyymmddhh24miss), FROM user_smscall WHERE (gsm_user_id = :v1 ) ); EXEC SQL PREPARE sql FROM :sqlstmt; /Prepare /定义游标 EXEC

56、SQL declare user_smscall_cu_1 cursor for sql; EXEC SQL OPEN user_smscall_cu_1 USING :ora_gsm_user_id;避免不必要的排序对查询结果进行排序会大大的降低系统的性能用WHERE子句替换HAVING子句例如：SELECT NAME，SUM(AGE) FROM EMPLOYEE GROUP BY NAME HAVING NAME！=ABC 修改为以下语句效果更好 SELECT NAME，SUM(AGE) FROM EMPLOYEE WHERE NAME！=ABC GROUP BY NAME 用“=”替代“

57、”如：在ID列上建有索引，则语句SELECT * FROM EMPLOYEE WHERE ID = 9 要比语句SELECT * FROM EMPLOYEE WHERE ID 8高效。这是由于前者DBMS将直接跳到第一个ID等于9的记录而后者将首先定位到8的记录并且向前扫描到第一个DEPT大于9的记录。删除表中所有记录时用TRUNCATE替代DELETE当删除表中的记录时，在通常情况下，回滚段用来存放可以被恢复的信息，如果你没有COMMIT事务，ORACLE可以将数据恢复到删除之前的状态；而当运用TRUNCATE时，回滚段不存放任何可被用于恢复的信息，当命令运行后，数据不能被恢复,因此很少的资

58、源被调用，执行时间也会很短,空间立即释放，detele操作后的空间可以被重新利用，但不会释放。用UNION-ALL 代替UNION说明：UNION-ALL不过虑重复记录，UNION 过滤重复记录，所以需要先排序。如果不需要过滤重复的记录，UNION-ALL 比UNION 性能更好。用(NOT) EXISTS替代(NOT) IN。在许多基于驱动表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS(或NOT EXISTS)通常将提高查询的效率。例如：SELECT * FROM EMP WHERE EMPNO 0 AND DEPTNO IN (SELECT DEP

59、TNO FROM DEPT WHERE LOC = MELB)没有以下语句高效SELECT * FROM EMP WHERE EMPNO 0 AND EXISTS (SELECT X FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = MELB)使用DECODE函数来减少处理时间。使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。例：SELECT NAME|UNDER AGEFROM EMPLOYEE WHERE AGE=18可以改成：SELECT DECODE(SIGN(AGE-18),-1,NAME|UNDERAGE，1，NA

60、ME|OVERAGE)FROM EMPLOYEE使用表的别名(Alias)。当在SQL语句中连接多个表时，应使用表的别名并把别名前缀于每个列上，好处是：使用表的别名可以减少解析的时间使用表的别名可以减少那些由Column歧义引起的语法错误例：正确：SELECT A.USER_ID, A.BILL_MONTH, A.DETAIL_BILL_ITEM_DEF_ID FROM ACCOUNT.SPEC_DETAIL_BILL_ITEM A, ACCOUNT.DETAIL_BILL_ITEM_DEF B WHERE A.BILL_MONTH_NUMBER = :B0 AND TO_NUMBER(:B1