Oracle数据库命名编码规范xym.docx

《Oracle数据库命名编码规范xym.docx》由会员分享，可在线阅读，更多相关《Oracle数据库命名编码规范xym.docx（27页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、文件标识：QD-30-08密级：内部版本号：Ver1.0大庆金桥软件开发作业体系Oracle数据库命名编码规范oracle编码规范变更历史操作责任人日期版本变更内容创建李富华2010-04-20无创建文档编辑周强2010-04-21无修改文档大庆金桥信息技术工程有限公司 - 26 -目录1.文档介绍41.1文档目的41.2文档范围41.3定义41.4参考资料42.命名规范42.1一般规则42.2对象命名汇总表53.编码规范63.1一般性规定63.2CREATE语句73.3SELECT语句73.4INSERT语句83.5UPDATE语句83.6DELETE语句93.7游标语句93.8IF语句93

2、.9简单循环语句113.10FOR循环语句113.11WHILE循环语句113.12程序块规范123.13语法规范124.设计规范144.1一般表设计144.2特殊表设计原则154.3索引设计原则154.4完整性设计原则154.5触发器154.6视图设计154.7性能优化165.书写规范205.1缩进风格205.2空格及换行206.注释说明216.1一般性注释216.2函数文本注释217.异常规范227.1pl/sql异常规范227.2后台验证异常信息规范238.附录238.1附一 开发工具238.2附二 预定义异常248.3附三 范式258.3.1第一范式258.3.2第二范式258.3.3

3、第三范式258.3.4Boyce-Codd范式268.3.5第四范式268.3.6第五范式268.3.7反规范化261. 文档介绍1.1 文档目的本文档用于指导开发设计人员对Oracle数据库进行设计和编码。使用统一的命名和编码规范，使数据库对象命名及编码风格标准化，可增加程序的可读性，增强系统的可维护性，提高软件的质量。本文档仅仅针对Oracle数据库做的规范，对其他数据库不具有指导意义。1.2 文档范围本规范适用于公司范围内所有以Oracle作为后台数据库的应用系统和项目开发工作。对公司2010年以前用Oracle数据库开发的项目不做限制。1.3 定义无1.4 参考资料2. 命名规范2.1

4、 一般规则Oracle中的各种数据对象，包括实例、数据库、表空间、表、视图、存储过程、函数、解发器等的命名都要遵循Oracle的标准命名规则：1） 以字符打头，30个字符以内，名称超过长度的情况下适当采用缩写。2） 只能包含A-Z,a-z,0-9,_。3） 不能和同一个表空间下的其他对象重名。4） 不能是Oracle服务器的保留字。5） 数据对象尽量不要使用缩写；如要缩写最好使用容易看懂的缩写。2.2 对象命名汇总表对象对象名前缀范例描述表(table)t_t_user表名长度原则上不超过25个字符；表、视图、字段名中不出现复数，创建表必须要注释，comments必须要填写。视图(view)

5、v_v_user如果表名或字段名过长，则用表名或字段名的缩写。序列(sequence) s_s_user一般索引(normal index) i_i_user_usernamefield:字段名，遇上长字段名可采用缩写唯一索引(unique index) i_u_i_u_user_birthday主键(primary key) pk_pk_user_userid外键(foreign key) fk_fk_student_user_userid簇(cluster) cl_cl_user_student触发器(triger) tr_i _ tr_u _ tr_d _tr_iud_tr_i_ add

6、_user_birthdayi:insertu:updated:delete存储过程(procedure) p_i_p_u_p_d_p_s_p_i_user_birthday函数 (function) f_f_get_username_by_userid包及包体(package & package body) pkg_pkg_user_info类及类体 (type &type body) type_type_user_class同义词(synonym)inv_inv_user保存点(savepoint)save_save_表的保存点事务的保存点事务(transaction)trans_tran

7、s_insert_userinfo字段(field)create_date单词小写，中间用下划线隔开，字段必须要注释，comments必须要填写。游标(cursor)cur_ cur_user_info数据库链接（database link）link_若远程服务器名和数据库名一致，采用 link_用户及角色3. 编码规范3.1 一般性规定1、 sql语句中的所有表名、字段名全部小写，系统保留字、内置函数名、sql保留字大写。【推荐】 2、 连接符OR、IN、AND、以及、=等前后各加上一个空格。当语句中出现括号时，括号的两边不留空格。3、 “不等于”统一使用。虽然 !=和是等价的，为了统一，不

8、等于一律使用表示。4、 对较为复杂的sql语句加上注释，说明算法、功能。5、 使用空行将逻辑相关的代码段之间分隔开。6、 程序块采用缩进风格书写，保证代码清晰易读，风格一致，缩进格数统一为 2个。必须使用空格，不允许使用TAB键，以免用不同的编辑器阅读程序时，因 TAB键所设置的空格数目不同而造成程序布局不整齐。7、 一行有多列，超过80个字符时，基于列对齐原则，采用下行缩进。8、 where子句书写时，每个条件占一行，保留字或者连接符放到行的最后面，含有键的条件放到其他条件的前面。3.2 CREATE语句CREATE TABLE t_dksz(YHBS VARCHAR2(20) NOT NU

9、LL,ZHGX DATE,DKKHD VARCHAR2(24),CONSTRAINT pk_dksz_yhbs PRIMARY KEY (YHBS)3.3 SELECT语句查询语句采用以下原则编写（可最大化重用共享池中的SQL语句，提高应用程序性能）：（1） 由SELECT开头，后跟一个显示查询结果的列表；【推荐】（2） 语句中嵌入逗号时，在逗号后面加一空格，当逗号是最后一个字符时，把它放在本行；（3） 由FROM开头，后跟一个或多个获取数据所涉及的表，如果后面跟多个表，关键字右对齐；【推荐】（4） 由WHERE开头，后跟一个或多个确定所需值的条件，如果后面有多个条件，关键字右对齐；【推荐】（

10、5） 由GROUP BY开头，后跟一个或多个表列名，通过这些列以对查询结果进行汇总，关键字右对齐；【推荐】（6） 由ORDER BY开头，后跟一个或多个表列名，通过这些列以对查询结果进行排序，关键字右对齐；【推荐】（7） 当语句中出现括号时，括号的两边不留空格；（8） 在SQL语句使用运算符时，操作两边应各留一个空格；（9） 每个部分分行编写，将每一行的第一个关键字与第一行的SELECT尾部对齐；例如：SELECT col1, col2, col3FROM table1WHERE col1 col2GROUP BY col1, col2ORDER BY col1;SELECT col1, co

11、l2, col3, col4, col5, col6,col7, col8, col9, col10FROM sb_sbqkxx,sb_bb011101WHEREsb_sbqkxx.czwdbh = sb_bb.czwdbhANDsb_sbqkxx.swdjbh = avc_swdjbhANDsb_sbqkxx.sbsssq = avc_sbsssq;3.4 INSERT语句 关键字用大写，列名和表名采用小写；【推荐】 语句中嵌入逗号时，在逗号后面加一空格，当逗号是最后一个字符时，把它放在本行； 当语句的同一部分要延续到下一行时，按下列格式排列： 当语句中出现括号时，括号的两边不留空格。格式如

12、下：【推荐】INSERT INTO (, , . , ,)VALUES(, , .,)例如：insert into sm_user(user_id, user_name, login_name)values( p_user_id, p_user_name, p_login_name)insert into sm_duty_bak(duty_id, duty_name, created_by, creation_date, last_updated_by,last_update_date, disable_date ) selectduty_id, duty_name, created_by,

13、creation_date, last_updated_by,last_update_date, disable_date from sm_dutywhere duty_id =: duty_id3.5 UPDATE语句 关键字右对齐；格式如下：【推荐】UPDATE SET = , = , = 3.6 DELETE语句格式如下：【推荐】DELETE FROM table1WHERE col1 = ?3.7 游标语句格式如下：【推荐】程序中使用显示游标。格式如下OPEN cur_name;LOOPFETCH cur_ into ;EXIT WHEN cur_name%notfound; END

14、LOOP;CLOSE cur_name;3.8 IF语句条件执行语句IFELSE按以下格式编写：【推荐】IF THEN ELSE (或ELSIF) THEN END IF; 在IFTHEN和ELSE(或ELSIF)及ELSETHEN和ENDIF间可包含一条或多条PL/SQL语句，而不需要加BEGIN和END IFELSEENDIF语句可以嵌套； 注意ELSIF的写法； if后的条件要用括号括起来，括号内每行最多两个条件。例如： if (v_count = 1 or v_count = 2 or v_count = 5 or v_count = 6 ) then select sysdate i

15、nto v_date from dual; end if; 减少控制语句的检查次数，如在 else(if.else)控制语句中，对最常用符合条件，尽量往前被检查到。例如：以下例如不符合规范（假设 v_count = 1条件大数情况会被满足） if (v_count = 0) then null; elsif (v_count = 1) then null; end if;应如下书写： if (v_count = 1) then null; elsif (v_count = 0) then null; end if; 尽量避免使用嵌套的 if语句，在这种情况应使用多个 if语句来判断其可能。例如

16、：以下例如不符合规范 if v_count =0 then if v_flag = 0 then null; else null; end if; else v_count =1 then if v_flag = 0 then null; else null; end if; end if;应如下书写： if (v_count = 0) and (v_flag = 0) then null; elsif (v_count = 0 ) and (v_flag = 1) then null; elsif (v_count = 1) and (v_flag = 0) then null; elsif

17、 (v_count = 1) and (v_flag = 1) then null; end if;3.9 简单循环语句LOOP EXIT WHEN END LOOP;3.10 FOR循环语句FOR 变量 IN 变量取值范围LOOP END LOOP;3.11 WHILE循环语句WHILE LOOP END LOOP;3.12 程序块规范在sql代码块中尽量使用begin.end 语句块，提高代码可读性。对于触发器、存储过程、函数等带名的程序块，要使用块结束标识。如CREATE OR REPLACE PROCEDURE p_get_userinfoBEGINEND p_get_userinfo

18、;/* 此处的过程名p_get_userinfo是可选的，规范要求写上，与块开始的CREATE相对应 */3.13 语法规范 避免隐式的数据类型转换。说明：在书写代码时，必须确定表的结构和表中各个字段的数据类型，特别是书写查询条件时的字段就更要注意了。例如：以下代码不符合规范， status_type是 number型数据. select wdj.wip_entity_id from wip.wip_discrete_jobs wdj where wdj.status = 3;应如下书写： select wdj.wip_entity_id from wip.wip_discrete_jobs

19、wdj where wdj.status = 3; 不要将空的变量值直接与比较运算符(符号)比较。如果变量可能为空，应使用 is null 或 is not null 或 nvl函数进行比较。例如：以下代码不符合规范 if v_user_name = null then dbms_output.put_line(user name is null); end if;应该如下书写: if v_user_name is null then dbms_output.put_line(user name is null); end if; 对于非常复杂的 sql(特别是多层嵌套，带子句或相关的查询 )

20、，应该先考虑是否设计不当引起的，对于复杂的一些 sql可以考虑使用程序实现，原则上遵循一句话只做一件事情。 尽可能地使用相关表字段的类型定义，形如 %type、%rowtype。 存储过程中变量的声明应集中在 as和 begin关键字之间，不允许在代码中随意定义变量，定义变量时，完成相同功能模块的变量应放在一起，与不同模块的变形量应空行隔开，增加代码的可读性。 order by 后面字段不唯一时分页会出现问题，分页时如果 order by 后面的字段不唯一，一定要让 order by 唯一，最佳方案是增加一 pk，如实在没办法则可以追加 rowid，order by后尽量避免使用 rowid。

21、 使用 varchar2代替 varchar类型。 当存储过程有多个分支返回时，若有事务，需确保各个分支都结束了事务。 in、out参数应按类别分开书写，不要交叉，对于 out参数，特别是 nest table、record,尽量都带上 nocopy，提高程序的运行效率。 聚集函数 max、min、sum在没有记录得符合查询条件的情况下返回 null，不会产生 no_data_found异常。 原则上不要使用动态 sql，如果非得使用运态 sql，须绑定变量。【推荐】 尽量不要使用子函数方式实现存储过程，应分别定义。【推荐】 代码中不建议使用 goto语句。【推荐】 确保所的变量和参数都使用到

22、。【推荐】 确保变量和参数在类型和长度与表数据列类型和长度相匹配。如果与表数据列宽度不匹配，则当较宽或较大的数据传进来时会产生运行异常。例如：如 fnd_users表 user_name字符宽为 50，当用户名大于 10时会报错。 declare v_user_name varchar2(10); begin select fu.user_name into v_user_name from fnd_user fu where fu.user_id = p_user_id; end; 当一个 PL/SQL或 SQL语句中涉及到多个表时，始终使用别名来限定字段名，这使其它人阅读起来更方便，避免了

23、含议模糊的引用，其中能够别名中清晰地判断出表名。别名命名时，尽量避逸使用无意义的代号 a、 b、c，而应该有意义 (如表 system_items_b对应别名为 msi，po_headers_all别名对应为 pha)。例如：以下编码不符合规范： selectwip_entity_name,a.wip_entity_id,a.date_released from wip.wip_entities b, wip.wip_discrete_jobs awhere b.wip_entity_id = a.wip_entity_id and a.status_type = 3 and a.date_r

24、eleased trunc(sysdate)应如下书写： select we.wip_entity_name, wdj.wip_entity_id, wdj.date_released from wip.wip_entities we, wip.wip_discrete_jobs wdj where we.wip_entity_id = wdj.wip_entity_id and wdj.status_type = 3 and wdj.date_released trunc(sysdate)4. 设计规范4.1 一般表设计 表空间设计，原则上表空间名与 schema名一致，其索引所在空间为 s

25、chema name + index。如： schema为 INV，则默认的表空间应该为 INV，所对应的索引空间为 INVINDEX tablespace每个表在创建时候，必须指定所在的表空间，不要采用默认表空间，以防止表建立在 system空间上，导致性能问题。对于事务比较繁忙的数据表，必须存放在在该表专用空间中。 根据性能需要，可以适当可曾加冗余；4.2 特殊表设计原则 分区表对于数据量比较大的表，根据表数据的属性进行分区，以得到较好的性能。如果表按某些字段进行增长，则采用按字段值范围攻进行分区；如果表按某个字段的几个关键值进行分布，则采用列表分区；对于静态表，则采用 hash分区或列表

26、分区；在范围分区中，如果数据按某关键字段均衡分由，则采用子分区的复合分区法。 在分区表中不建议使用全局索引，因为 trunc分区时会导致全局索引失效，造成难以维护。 4.3 索引设计原则 每个索引在创建时，必须指定表空间，不要采用默认表空间，以防止索引建立在 system空间和非索引专用空间，以减少 IO冲突，提高性能。 4.4 完整性设计原则 主键约束原则上所有的数据表都要有主键。对于数据量比较大的表，要求指定索引字段。 外键关联对于关联两个表字段，一般应该分别建立主键、外键。实际是否建立外键，根据对数据完整性的要求决定。为了提高性能，对于数据量较大的表要求对外键建立索引。对于有要求级联删除

27、属性的外键，必须指定 on delete cascade. Null值对于字段能否为 null，应该在 sql建表脚本中明确指定，不应该使用缺省。由于 null值在参加任何计算时，结果均为 null，所以在程序中必须用 nvl()函数把可能为 null值的字段或变量转换非 null的默认值。 Check条件对于字段有检查性约束，需指定 check原则。 4.5 触发器触发器是一种特殊的存储过程，通过数据表的 DML操作而触发执行，其作用为确保数据的完整性和一致性不被破坏而创建，实现数据的完整性约束。说明：触发器的 before或 after事务属性的选择时候，对表操作的事务属性必须与应用程序保

28、持一致，以避免死锁发生，在大型导入表中，尽量避免使用触发器。 在系统中不要使用过多的触发器。 4.6 视图设计 尽量使用简单的视图，避免使用复杂的视图。简单视图：数据来自单个表，且无分组 (distinct/group by)、无函数。复杂视图：数据来自多个表，或有分组、有函数。4.7 性能优化 避名频繁 commit，尤其是把 commit写在循环体中每次循环都进行commit。 使用绑定变量，避免常量的直接引用。例如：以下书写不符合本规范 . insert into sm_users(user_id,user_name,created_by,creation_date) values (1

29、,Tang,-1,sysdate);建议用如下方式操作： declare v_user_id sm_users.user_id%type; v_user_name sm_users_user_name%type; v_created_by sm_users.created_by%type; v_creation_date sm_users.creation_date%type; begin insert into sm_users(user_id, user_name, created_by, creation_date) values(v_user_id, v_user_name, v_cr

30、eated_by,v_creation_date); in、exists的使用规范例如：当有 A、B两个结果集，当结果集 B很大时， A较小时，适用 exists，如：select * from a where exists(select 1 from b where a.column = b.column);当结果集 A很大时， B很小时，适用 in,如： select * from a where a.column in (select b.column from b ) 避免不必要的排序说明：对查询结果进行排序会大大的降低系统的性能。 对于数字型的唯一键值，用序列 sequence产生。

31、 索引的规则：建立索引常用的原则如下：1).表的主键、外键必须有索引 2).1000行的表应该有索引 3).经常与其它表进行连接的表，在边接字段上应建立索引 4).经常出现在 where子句中的字段且过滤性极强的，特别是大表的字段，应该建立索引 5).索引字段，尽量避免值为 null 6).复合索引的建立需要仔细分析；尽量考虑用单字段索引代替； A.正确选择复合索引中的第一个字段，一般是选择性较好的且在 where子句中常的字段上； B.复合索引的几个字段是否经常同时以 and方式出现在 where子句中？单字段查询是否极少其至没有？如果是，则可以建立复合索引；否则考虑单字段索引； C.如果复

32、合索引中包含的字段经常单独出现在 where子句中，则分解为多个单字段索引； D.如果复合索引所包含的字段超过 3个，那么仔细考虑其必要性，考虑减少复合的字段； E.如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引； 7).频繁 DDL的表，不要建立太多的索引； 8).删除无用的索引，避免对执行计划造成负面影响； 让 SQL语句用上合理的索引。原则如下：首先，看是否用上了索引，对于该使用索引而没有用上索引的 SQL语句，应该想办法用上索引。其次，看是否用上了索引，特别复杂的 SQL语句，当其中 where子句包含多个带有索引的字段时，更应该注意索引的选择是否合理。错误的索引

33、不仅不会带来性能的提高，相反往往导致性能的降低。针对如何用上合理的索引，以 Oracle数据中的例子进行说明：l 任何对列的操作都可能导致全表扫描，这里所谓的操作包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等式的右边，甚至去掉函数。 l 避免不必要的类型转换，要了解“隐藏”的类型转换。 l 增加查询的范围，限制全范围的搜索。 l 索引选择性低，但数据分布差异很大时，仍然可以利用索引提高效率。 l Oracle优化器无法用上合理索引的情况下，利用 hint强制指定索引。 l 使用复合索引且第一个索引字段没有出现在 where中时，建议使用 hint强制。 pl/sql使用短路径法，当

34、计算逻辑表达式，即：一旦确定后， pl/sql停止计算表达式。【推荐】 not in的替换写法【推荐】例如：select deptno from dept where deptno not in (select deptno from emp)建议写成： select deptno from dept, emp where dept.deptno = emp.deptno(+) and emp.deptno is null like 子句尽量前端匹配【推荐】 like参数使用得非常频繁，因此如果能够对于 like子句使用索引，将很好地提高查询的效率。例如：查询城市代码select * from

35、 city where city_name like %ZHEN%修改为select * from city where city_name like SHNEZHEN% 避免使用 select * 语句。说明：不要用 *来代替所有字段，应给出字段列表，注：不包含 select coun(*).例如：以下不符合规范： insert语句必须给出字段列表，使用 insert语句一定要给出要插入的字段列表，这样即使更改了表结构加了字段也不会使用引用了本表的存储过程失效。 从表中同一笔记录中获取记录的字段值，须使用同一 SQL语句得到，不允许分多条 SQL语句。例如：以下不符合此规范 select d

36、uty_idinto v_duty_id from sm_duty where rownum=1; select duty_nameinto v_duty_name from sm_duty where rownum=2;应如下书写：select duty_id,duty_nameinto v_duty_id,v_duty_name from sm_duty where rownum=15. 书写规范5.1 缩进风格 程序块采用缩进风格书写，保证代码清晰易读，风格一致，缩进格数统一为 2个。 必须使用空格，不允许使用 TAB键。 具体语句参考“编码规范”小节5.2 空格及换行 不允许把多个语句

37、写在一行中，即一行只写一条语句。例如：以下书写不符合规范： v_count :=1; v_creation_date := sysdate;应写成： v_count := 1; v_creation_date := sysdate; 相对独立的程序块之间必须加空行。两个程序块在逻辑上相对独立，应用空行加以分隔，同时增加注释。 超过 80列的语句要分行书写，长表达式应在低先级操作符处换行，操任符或关键字放在新行之首。划分出新行应当适当地缩进，使排版整齐，语句可读。 begin、end独立成行例如：以下不符合规范 begin null; exception when others then nul

38、l; end;应写成： begin null; exception when others then null; end;6. 注释说明6.1 一般性注释 注释尽可能简洁、详细而全面。 创建每一数据库对象时都要加上COMMENT ON注释，以说明该对象的功能和用途；建表时，对某些数据列也要加上COMMENT ON注释，以说明该列和/或列取值的含义。如：XX表中有CZZT列属性为NUMBER(10,0)可加COMMENTON注释如下COMMENT ON COLUMN XX.CZZTIS0=正常,1=等待,2=超时,3=登出 在pl/sql中，为每个变量声明添加注释，说明该变量用途。 在块的每个主

39、要部分之前增加注释，说明该段语句及算法的目的以及要得到的结果。 注释语法包含两种情况：单行注释、多行注释n 单行注释：注释前有两个连字符（-），一般对变量、条件子句可以采用该类注释。n 多行注释：符号/*和*/之间的内容为注释内容。对某项完整的操作建议使用该类注释。6.2 函数文本注释 在每一个块和过程（存储过程、函数、包、触发器、视图等）的开头放置注释。/*函数名称：功能描述： 输入参数：输出参数：无返 回 值：无创建者：创建时间：2010-04-10-修改记录-修改人员：修改时间：修改内容：*/CREATE ORREPLACE PROCEDURE dfsp_xxx 传入参数的含义应该有所说

40、明。如果取值范围确定，也应该一并说明。取值有特定含义的变量（如boolean类型变量），应给出每个值的含义。 在每一个变量声明的旁边添加注释。说明该变量要用作什么，通常，简单使用单行注释就行了，例如l_sfhCHAR(11)-身份证号码。 在块的每个主要部分之前添加注释在块的每个主要部分之前增加注释，解释下组语句目的，最好是说明该段语句及算法的目的以及要得到的结果，但不要对其细节进行过多的描述 在块和过程的开头注释中还可以增加要访问的数据库等信息7. 异常规范7.1 pl/sql异常规范对于存储过程、函数等程序块都要有异常处理部分，以提高程序的自检能力。异常节格式如下：-自定义异常my_err

41、 EXCEPTION;-抛出自定义异常RAISE my_err;-异常节EXCEPTION WHEN my_err THENWHEN no_data_found THEN -系统预定义异常WHEN others THENEND;在异常节的最后都要设置others异常处理，它处理所有没有明确列出的异常。项目开发阶段，为了调试方便、快速定位错误，可以创建xtycxx（系统异常信息）表，记录产生系统异常的存储过程或函数名、异常名，如果过程或函数中有多个程序块，记录块的描述信息。例如EXCEPTIONWHEN no_data_found THENINSERT INTO xtycxx(ccmc,ycmc,ms)VALUES (ap_name，no_data_found，获取操作文档编号);WHEN others THENINSERT INTO xtycxx(ccmc,ycmc,ms)VALUES (ap_name，others，获取操作文档编号);RETURN；END;7.2 后台验证异常信息规范使用数据库过程、函数进行后台数据验证时，发现异常情况