oracle数据库编程规范.doc

Oracle数据库编程规范日期修订版本修改描述作者备注2008-12-241.0初始化杨云亮目 录第一部分 概述31 概述31.1 背景31.2 术语约定31.3 适用范围3第二部分编程规范32 书写规范32.1大小写风格32.2 缩进风格42.3 空格及换行52.4 其它93、命名规范：113.1 对象命名汇总表113.2 对象命名123.3 变量命名133.4 表分区命名144、注释规范155、语法规范186、脚本规范197、性能优化208、设计规范228.1 一般表设计228.2 特殊表设计原则238.3 索引设计原则238.4 完整性设计原则238.5 触发器238.6 视图设计249 文档规范249.1 数据库设计及文档维护249.2 数据库接口文档2410 开发工具2411、附录1：范式2511.1 第一范式2511.2 第二范式2511.3 第三范式2511.4 Boyce-Codd 范式2511.5 第四范式2511.6 第五范式2511.7 反规范化25第一部分 概述1 概述1.1 背景为统一对Oracle数据库系统的认识及操作，特制定此规范。1.2 术语约定本规范采用以下术语描述： 规则：编程是强制必须遵守的原则 建议：编程时必须加以考虑的原则 说明：对此规则或建议进行必要的解释 示例：对此规则或建议从正、反两个方面给出1.3 适用范围本规范适用于Oracle 8i 及以上版本第二部分编程规范2 书写规范2.1大小写风格规则2.1.1：所有数据代码统一使用小写字母书写，也方便不同数据的移值。说明：本人在看大写程序时，感觉比较痛苦。示例：以下编码不符合规范应如下编写：2.2 缩进风格规则2.2.1： 程序块采用缩进风格书写，保证代码清晰易读，风格一致，缩进格数统一为2 个。规则2.2.2：必须使用空格，不允许使用TAB 键。说明：以免用不同的编辑器阅读程序时，因TAB 键所设置的空格数目不同而造成程序布局不整齐。规则2.2.3：同一条语句占用多于一行时，每行的第一个关键字应当左对齐。示例：建议2.2.4：对于Insert values 和update 语句，一行写一个字段，这段后面紧跟注释（ 注释语句左对齐），values 和insert 左对齐，左括号和右括号与insert 、values 左对齐。示例：建议2.2.5：insertselect 语句时，应使每行的字段顺序对应，以每行最多不超过4 个字段，以方便代码阅读，括号的内容另起一行缩进2 格开始书写，关键字单词左对齐，左括号、右括号另起一行与左对齐。示例,以下代码不符合此规范：应如下编写：说明：1.select 语句中每行的字段应与insert 语句对应。2.insert 语中中折行的字段名应缩进并与上一行的第一个字段名对齐。3.select 语句中折行的字段名应缩进并与上一行的第一个字段名对齐。2.3 空格及换行规则2.3.1：不允许把多个语句写在一行中，即一行只写一条语句。示例：以下书写不符合规范：v_count :=1; v_creation_date := sysdate; 应写成：规则2.3.2：避免将SQL 语句写到同一行，再短的语句也要在关键字和谓词处换行。示例：以下书写不符合规范：select duty_id,duty_name from sm_duty where duty_id = :duty_id 应写成: 规则2.3.3：相对独立的程序块之间必须加空行。说明：两个程序块在逻辑上相对独立，应用空行加以分隔，同时增加注释。示例：应写成：规则2.3.4：超过110 列的语句要分行书写，长表达式应在低先级操作符处换行，操任符或关键字放在新行之首。划分出新行应当适当地缩进，使排版整齐，语句可读。示例：以下不符合规范应写成：说明：A . 加法的优先级低于乘法，因此应在加号处折行。B. 两组乘法虽然在逻辑上会先于加法，但显示加上括号使用可读性更强。规则2.3.5：begin、end 独立成行示例：以下不符合规范begin null; exception when others then null; end; 应写成：规则2.3.6：if 后的条件要用括号括起来，括号内每行最多两个条件。如：示例：建议2.3.7：不同类型的操作符混合使用时，建议使用括号进行隔离，以使代码清晰。示例：以下书写不符合规范：应写成：建议 2.3.8：减少控制语句的检查次数，如在 else( if.else)控制语句中，对最常用符合条件，尽量往前被检查到。示例：以下示例不符合规范（ 假设v_count = 1 条件大数情况会被满足）应如下书写：建议2.3.9：尽量避免使用嵌套的if 语句，在这种情况应使用多个if 语句来判断其可能。示例：以下示例不符保规范应如下书写：规则2.3.10：存储过程、函数、触发器、程序块中定义的变量和输入、输出参数在命名上有所区分。说明：一般用v代表程序块中定义的变量一般用p代表输入参数变量一般用x代表输入输出或输出参数变量2.4 其它规则2.4.1：避免使用select * 语句。说明：不要用*来代替所有字段，应给出字段列表，注：不包含select coun(*). 示例：以下不符合规范：select * from sm_duty 应如下书写：规则2.4.2：insert 语句必须给出字段列表。说明：使用insert 语句一定要给出要插入的字段列表，这样即使更改了表结构加了字段也不会使用引用了本表的存储过程失效。示例：以下不符合本规范应如下: 规则2.4.3：从表中同一笔记录中获取记录的字段值，须使用同一SQL 语句得到，不允许分多条SQL 语句。示例：以下不符合此规范应如下书写：规则2.4.4：当一个PL/SQL 或SQL 语句中涉及到多个表时，始终使用别名来限定字段名，这使其它人阅读起来更方便，避免了含义模糊的引用，其中能够别名中清晰地判断出表名。说明：别名命名时，尽量避逸使用无意义的代号a、b 、c ，而应该有意义( 如表mtl_system_items_b 对应别名为msi，po_headers_all 别名对应为pha)。示例：以下编码不符合规范：应如下书写：select we.wip_entity_name, wdj.wip_entity_id, wdj.date_released 规则2.4.5：确保变量和参数在类型和长度与表数据列类型和长度相匹配。说明：如果与表数据列宽度不匹配，则当较宽或较大的数据传进来时会产生运行异常。示例：如fnd_users 表user_name 字符宽为50，当用户名大于10 时会报错。3、命名规范：3.1 对象命名汇总表对象对象名后缀范例描述表(table)无mtl_system_items 表名长度原则上不超过25 个字符视图(view)_v mtl_system_items_v 如果表名或字段名过长，则用表名或字段名的缩写序列(sequence) _s mtl_system_items_s 一般索引(normal index) _n + 序号mtl_system_items_n1 位图索引(bitmap index) _b + 序号mtl_system_items_b1 唯一索引(unique index) _u + 序号mtl_system_items_u1 主键(primary key) _pk mtl_system_items_pk 外键(foreign key) _fk + 序号mtl_system_items_fk1 簇(cluster) _c cust_orders_c 触发器(triger) _tr mtl_system_items_tr m过程(procedure) _p mtl_system_items_p 函数(function) _f mtl_system_items_f 包及包体(package & package body)_pkg mtl_system_items_pkg 类及类体(type&type body) _typ mtl_system_items_typ 物化视图(materialized view)_mv mtl_system_items_mv 同义词(synonym) 无inv_mtl_system_titems 数据库联接(data base link) 无from_erp 保存点(avepoint) _spt wip_spt 变量变量后缀示范说明自定义记录类型(record)_rec type item_rec is record(item varchar2(50), inventory_item_id number);自定义记录类型变量前缀v_后缀为_recv_item_rec自定义嵌套表类型(nest table)_tbl type items_tbl is table of item_rec index by binary_integer;自定义嵌套表变量前缀为v_后缀为_tblv_items_tbl 游标(cursor) 前缀为cur_ cursor cur_wip is 其它变量前缀统一为v_，无后缀declare v_count pls_integer; v_duty_name varchar2(20); v_date date; v_rowid rowid;3.2 对象命名所有用户自定义的数据库对象名统一使用小写字母，形如混合拼写的格式+下划线+后缀名(对象类型). 规则3.2.1：命名尽量采用富有意义的英文词汇，不准采用汉语拼音。示例：以下书写不符合规范wl_system_items (物料编码表) 应如下命名比较好：mtl_system_items 规则3.2.2：存储过程和函数命名。名称应与其实际功能保持一致。导致发生某动作应以动词前缀为命令，命名里面不能出现复数。示例：以下书写不符合规范add_users_p (增加用户) 应如下书写：规则3.2.3：数据表、视图名。各表中相关列尽量同名并保持相同的宽度。列名不要命名为name,id,level,remark,description 等关键字。说明：如果不同名时，当修改表结构时，容易漏改。规则3.2.4：序列命名。Mtl_system_items_s 规则3.2.5：索引命名。Mtl_system_items_n1 规则3.2.5：主键命名。Mtl_system_items_pk 规则3.2.6：外建命名。Mtl_system_items_fk 规则3.2.6：触发器命名。Mtl_system_items_tr 3.3 变量命名所有用户定义的存储过程或函数中使用的参数、变量、常量、异常等统一采用v_+混合拼写(如v_count)的格式(目前也流行采用变量类型首字母+混合拼写的格式，如n_count，我个人喜欢使用前者，当然也会带来另外一些问题，智者见智，仁者见仁，请发表意见)，其中小写首字母代表变量的类型，命名尽量采用富有意议的英文词汇，如果要缩写尽量采用约定俗成的缩写或去元音缩写，如info(信息)和ctrl(控制)等。规则3.3.1:所有名称采用英文单数名词或动词，避免出现复数。规则3.3.2:固定长度的字符串类型采用char，长度不固定的字符串采用varchar2。避免长度不固定的情况下采用char。规则3.3.3: 如无特殊需求，避免使用大字段(blob, clob, long, text, image 等)。规则3.3.4:命名使用英文单词，避免使用拼音，特别不应该使用拼音简写。命名不允许使用中文或者特殊字符。说明：英文单词使用对象本身意义相对或相近的单词。选择简单或最普通的单词，不要使用毫不相干的单词来命名。当一个单词不能够表达对象的含义时，用词组组合，如果组合太长时，采用简写或缩写，缩写要基本能表达原单词的意义。当出现对象名重名时，是不同类型对象时，加类型前缀或后缀以示区别。标识符命名要清晰、明了，有明确含义，同时使用完整的单词或大家基本可以理解的缩写，避免使人产生误解。较短的单词可以去掉元音形成缩写； 较长的单词可取单词的前几个字母形成缩写； 一些单词有大家公认的缩写。规则3.3.5：命名中若使用特殊约定或缩写，则要注释说明。说明：应该在源文件的开始之处，对程序中所使用的缩写或约定，特别是特殊的缩写，进行必要的注释说明。规则3.3.6：使用有意义、易于记忆、描述性强、简短及唯一的英文单词。自已特有的命名风格，要自始自终保持一至，不可来回变化。说明：个人命名风格，在符合所在项目组的命名规则的前提下，才可以使用。规则3.3.7：对于变量命名，禁止取单个字符(如i、j)，建议除了要有具体含义外，还能表明变量类型等。说明：变量，尤其是局部变量，如果用单个字符表示，很容易敲错(如i 写成j)，而编译时又检查不出来，有可能为了这个小小的错误而花费大量的时间。规则3.3.8：除非必要，不允许使用数据字或较奇怪的字符来定义标识符。示例：如下命名，使用人产生疑惑。declare temp_o_test varchar2(10); 规则3.3.9：用正确的反义词组命名具有互斥意义的变量或相反的动作函数等。示例：规则3.3.11：如果不涉及复杂运算，一律使用number 定义计数应用； 如果是简单的判断一条记录是否存在等应用，则使用pls_integer。示例：3.4 表分区命名建议3.4.1：分区表的表名可以遵循普通表的正常命名规则。建议3.4.2:按时间范围分区(每有一个分区)，分区名字为表的主要用途的缩写+下划线+yymm。示例：库存交易分区表的分区命名可以采用如下方式：trans_0611 、trans_0612、trans_0701 、trans_0702 建议3.4.3：按地域分布的子公司库存表（ 每个区域一个分区），分区名这为表的主要用途的缩写+区域的缩写。示例：库存(inventoy)的分区表的分区命名可以采用如下方式：inv_ap(亚太)，inv_lm(拉美)，inv_eu(欧洲). 规则3.4.4：最小分区名字为before_data 规则3.4.5:最大分区名字为after_data 规则3.4.5:子分区的名字为：父分区名+下划线+sub+ 下划线+no( 区域缩写)，根据实际情况进行组合。示例：如交易记录表在2006 年11 月有5 个分区，则子分区可以采用如下命名方式：如库存表在亚太有3 个国家和地区，则子分区可以采用如下命名方式：inv_ap_sub_hk(亚太香港)、inv_ap_sub_th(亚太泰国)、inv_ap_sub_kr( 亚太韩国) 规则3.4.7：分区表本地索引命名在正常索引名的最后一个下划线前加L。示例：如库存交易的一个普通索引为mtl_material_transactions_n1，则对应的分区表本地索引为mtl_material_transactions_ln1。规则3.4.8：分区表全局索引命名在正常索引名的最后一个下划线前加G。示例：如库存交易的一个普通索引为mtl_material_transactions_n1，则对应的分区表本地索引为mtl_material_transactions_gn1 。4、注释规范函数和过程代码要求按照下列格式书写注释：规则4.1：一般情况下，源程序有效注释量要求在30%以上。说明：注释的原则是有助于对程序阅读理解，在该加的地方都加了，注释不宜太多也不能太少，注释语言须准确、易懂、简洁。规则4.2：统一文件头的注释. 说明：name:函数或过程的名称purpse:函数或过程的用途revisions 下面是版本信息ver:当前版本date:创建或修改日期author:创建人或修改人description:在修改时，一定要在这里写出改动的内容，有1、2、3 清晰列出来parameters:对传入和传出参数进行说明return:函数返回结果notes:使用该函数或过程时需要特别注意的事情，如果没有可以不写。规则4.3：所有变量定义需要加注释，说明该变量的用途和含义。规则4.4：注释内容要清晰、明了、含义准确，防止注释二义性规则4.5：禁止在注释中使用缩写，特别是非常用的缩写。说明：在使用缩写时或之前，应进行必要的说明规则4.6：对存储过程的任何修改，都需要在注释最后添加修改人、修改日期及修改原因等信息。规则4.7：对程序分支必须书写注释。说明：这些语句往往是程序实现某一特定功能的关键，对于维护人员来说，良好的注释帮助更好的理解程序，有时甚至优于看设计文档。规则4.8：在代码的功能、意图层次上进行注释，提供有用、额外的信息。说明：注释代码的目的是解释代码的目的、功能和采用的方法，提供代码以外的信息，帮助维护人员理解代码，防止没有必要的重复注释信息。示例：以下代码注释意义不大。而如下的注释则给出了额外有用的信息。-调用其它过程执行成功规则4.9：注释应与其描述的代码相似，对代码注释应放在其上方或右方(对单条语句的注释)相今位置，不可放在下面。示例：以下编码不符合本规范：-取得用户的失效时间应该如下书：-取得用户的失效时间规则4.10：注释与所描述的内容进行同样的缩排。规则4.11：注释上面的代码应空行隔开。建议4.12：函数应对返回的代码进行详细描述。建议4.13：凡是涉及到类型的参数，建议在注释中将类型说明全部逻列出来。建议4.14：尽量使用”-” 进行注注释。建议4.15：行尾注释须使用”-” 。建议4.16：避免在一行代码或表达式的中间插入注释。建议4.17：在程序块的结束行右方加注释，以表明程序块结束。建议4.18：注释用中文书写(不凭别的，咱都是中国人，都要用母语呀，程序代码是英文，原因很简单，写程序是外国人发明的，要是哪天咱们用汉语写程序才快乐了，决对让老外找不到东西南北)。建议4.19：重要代码需要说明5、语法规范规则5.1：避免隐式的数据类型转换。说明：在书写代码时，必须确定表的结构和表中各个字段的数据类型，特别是书写查询条件时的字段就更要注意了。示例：以下代码不符合规范，status_type 是number 型数据.应如下书写：规则5.2：不要将空的变量值直接与比较运算符(符号)比较。如果变量可能为空，应使用is null 或is not null 或nvl 函数进行比较。示例：以下代码不符合规范应该如下书写:规则5.3：对于非常复杂的sql(特别是多层嵌套，带子句或相关的查询)，应该先考虑是否设计不当引起的，对于复杂的一些sql 可以考虑使用程序实现，原则上遵循一句话只做一件事情。规则5.4：尽可能地使用相关表字段的类型定义，形如%type 、%rowtype 。规则5.5：存储过程中变量的声明应集中在as 和begin 关键字之间，不允许在代码中随意定义变量，定义变量时，完成相同功能模块的变量应放在一起，与不同模块的变形量应空行隔开，增加代码的可读性。规则5.6：order by 后面字段不唯一时分页会出现问题，分页时如果order by 后面的字段不唯一，一定要让order by 唯一，最佳方案是增加一pk，如实在没办法则可以追加rowid，order by 后尽量避免使用rowid。规则5.7：使用varchar2 代替varchar 类型。规则5.8：当存储过程有多个分支返回时，若有事务，需确保各个分支都结束了事务。规则5.9：in、out 参数应按类别分开书写，不要交叉，对于out 参数，特别是nest table、record, 尽量都带上nocopy，提高程序的运行效率。规则5.10：聚集函数max、min、sum 在没有记录得符合查询条件的情况下返回null，不会产生no_data_found 异常。建议5.11：要采用成熟的技术来编码，对于先进的技术或没有成熟的技术，需在team 内进行评审。建议5.12：原则上不要使用动态sql，如果非得使用动态sql，须绑定变量。建议5.14：尽量不要使用子函数方式实现存储过程，应分别定义。建议5.15：代码中不建议使用goto 语句。建议5.16：确保所的变量和参数都使用到。说明：申明变量等也要一定的系统开销，也显得代码不够严谨。6、脚本规范规则6.1:所有脚本按内容分开存放，并按以下顺序存储：1)、创建数据类型脚本2)、创建业务表脚本3)、创建临时表脚本4)、创建视图脚本5)、创建主外键脚本6)、创建索引脚本7)、创建触发器脚本8)、创建存储过程脚本9)、初始化数据脚本10)、创建作业脚本规则6.2：创建每个对象代码的首部应该有对象注释规则6.3：每个存储过程（ 函数） 创建脚本单独存和，在配置库上按照功能模块存放到不同的目录下。7、性能优化规则7.1：避免频繁commit，尤其是把commit 写在循环体中每次循环都进行commit。规则7.2：使用绑定变量，避免常量的直接引用。示例：以下书写不符合本规范. 建议用如下方式操作：规则7.3：in、exists 的使用规范示例：当有A、B 两个结果集，当结果集B 很大时，A 较小时，适用exists，如：当结果集A 很大时，B 很小时，适用in,如：规则7.4：避免不必要的排序说明：对查询结果进行排序会大大的降低系统的性能。规则7.5：对于数字型的唯一键值，用序列sequence 产生。规则7.6：索引的规则：建立索引常用的原则如下：1)、表的主键、外键必须有索引2)、数据量超过1000 行的表应该有索引3)、经常与其它表进行连接的表，在边接字段上应建立索引4)、经常出现在where 子句中的字段且过滤性极强的，特别是大表的字段，应该建立索引5)、索引字段，尽量避免值为null 6)、复合索引的建立需要仔细分析； 尽量考虑用单字段索引代替； A.正确选择复合索引中的第一个字段，一般是选择性较好的且在where 子句中常的字段上； B.复合索引的几个字段是否经常同时以and 方式出现在where 子句中？ 单字段查询是否极少其至没有？ 如果是，则可以建立复合索引； 否则考虑单字段索引； C.如果复合索引中包含的字段经常单独出现在where 子句中，则分解为多个单字段索引； D.如果复合索引所包含的字段超过3 个，那么仔细考虑其必要性，考虑减少复合的字段； E.如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引； 7). 频繁DDL 的表，不要建立太多的索引；8). 删除无用的索引，避免对执行计划造成负面影响；让SQL 语句用上合理的索引。合理让SQL 语句使用索引的原则如下：首先，看是否用上了索引，对于该使用索引而没有用上索引的SQL 语句，应该想办法用上索引。其次，看是否用上了索引，特别复杂的SQL 语句，当其中where 子句包含多个带有索引的字段时，更应该注意索引的选择是否合理。错误的索引不仅不会带来性能的提高，相反往往导致性能的降低。针对如何用上合理的索引，以Oracle 数据中的例子进行说明：1、任何对列的操作都可能导致全表扫描，这里所谓的操作包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等式的右边，甚至去掉函数。2、避免不必要的类型转换，要了解“ 隐藏” 的类型转换。3、增加查询的范围，限制全范围的搜索。4、索引选择性低，但数据分布差异很大时，仍然可以利用索引提高效率。5、Oracle 优化器无法用上合理索引的情况下，利用hint 强制指定索引。6、使用复合索引且第一个索引字段没有出现在where 中时，建议使用hint 强制。建议7.7：pl/sql 使用短路径法，当计算逻辑表达式，即：一旦确定后，pl/sql 停止计算表达式。建议7.8：not in 的替换写法例如：建议写成：建议7.12：like 子句尽量前端匹配 like 参数使用得非常频繁，因此如果能够对于like 子句使用索引，将很好地提高查询的效率。例如：查询城市代码 select * from city where city_name like %ZHEN% 修改为 select * from city where city_name like SHNEZHEN% 8、设计规范8.1 一般表设计规则8.1.1：表空间设计，原则上表空间名与schema 名一致，其索引所在空间为schema name + index。如：schema 为INV，则默认的表空间应该为INV，所对应的索引空间为INVINDEX 规则8.1.2：tablespace 每个表在创建时候，必须指定所在的表空间，不要采用默认表空间，以防止表建立在system 空间上，导致性能问题。对于事务比较繁忙的数据表，必须存放在在该表专用空间中。8.2 特殊表设计原则规则8.2.1：分区表 对于数据量比较大的表，根据表数据的属性进行分区，以得到较好的性能。如果表按某些字段进行增长，则采用按字段值范围攻进行分区； 如果表按某个字段的几个关键值进行分布，则采用列表分区； 对于静态表，则采用hash 分区或列表分区； 在范围分区中，如果数据按某关键字段均衡分由，则采用子分区的复合分区法。规则8.2.2：在分区表中不建议使用全局索引，因为trunc 分区时会导致全局索引失效，造成难以维护。8.3 索引设计原则规则8.3.1：每个索引在创建时，必须指定表空间，不要采用默认表空间，以防止索引建立在system 空间和非索引专用空间，以减少IO 冲突，提高性能。8.4 完整性设计原则规则8.4.1：主键约束 原则上所有的数据表都要有主键。对于数据量比较大的表，要求指定索引段。规则8.4.2：外键关联 对于关联两个表字段，一般应该分别建立主键、外键。实际是否建立外键，根据对数据完整性的要求决定。为了提高性能，对于数据量较大的表要求对外键建立索引。对于有要求级联删除属性的外键，必须指定on delete cascade.规则8.4.3：Null 值 对于字段能否为null，应该在sql 建表脚本中明确指定，不应该使用缺省。由于null 值在参加任何计算时，结果均为null，所以在程序中必须用nvl()函数把可能为null 值的字段或变量转换非null 的默认值。规则8.4.4：Check 条件 对于字段有检查性约束，需指定check 原则。8.5 触发器触发器是一种特殊的存储过程，通过数据表的DML 操作而触发执行，其作用为确保数据的完整性和一致性不被破坏而创建，实现数据的完整性约束。说明：触发器的before 或after 事务属性的选择时候，对表操作的事务属性必须与应用程序保持一致，以避免死锁发生，在大型导入表中，尽量避免使用触发器。规则8.5.1：在系统中不要使用过多的触发器。8.6 视图设计规则8.6.1：尽量使用简单的视图，避免使用复杂的视图。简单视图：数据来自单个表，且无分组(distinct/group by)、无函数。复杂视图：数据来自多个表，或有分组、有函数. 9 文档规范每一个版本必须要有数据库表设计文档、数据库接口文档，重要的流程必须要有流程图。9.1 数据库设计及文档维护在数据库设计同应用程序设计同时进行，数据库人员需要参与到系统的设计中，维护数据库设计文档。9.2 数据库接口文档在设计阶段应考虑多种接入方式(来自外面的不同接口)连接到数据； 此时数据库设计人员应该主动同有关人员进行交流，熟悉系统流程，共同制定出数据库接口，并形成数据库接口文档，对于较复杂的接口，应提供流程图，以后存在接口变更，都应该同相关人员进行沟通，并及时列更新文档。规则9.2.1：提供输入输出参数类型和含义描述规则9.2.2：避免在已经复杂的处理里再叠加相应的处理流程。规则9.2.3：绘制主要流程图。10 开发