大话数据库编程规范
节选自《剑破冰山——oracle开发艺术》一书，书籍销售地址： http://product.china-pub.com/197199 http://product.dangdang.com/product.aspx?product_id=20981228&ref=book-02-L http://www.amazon.cn/mn/detailApp?uid=477-1785695-2022958&ref=RK&asin=B004CMIVM6 ITPUB个人空间W?6Y L*m 1.1 前言 目前在软件圈内有这么一个现象，就是：DBA不太懂写PL/SQL，而开发人员写的又是五花八门，而且效率不高。如此以来，造成诸多弊端： 1.可读性差。读别人写的一个程序花费的时间，比自己写一个程序的花费时间还要长；非但别人看不懂，时间久了连自己也看不懂了。 2.可维护性差。程序越写越长，越改越烂，像懒婆娘的裹脚布，又臭又长。 3.可移植性差。今天用oracle写一套，明天换成SQL Server的时候再写一套，众多的数据库开发人员在程序的苦海中重复着低级劳动…… 4.效率和性能差。一个存储过程或SQL执行效率简直可以让你感觉到对时间的绝望，你很快就理解什么是相对论了。 1.2 编程规范概述 事实上为了统一软件开发过程中关于数据库设计时的命名规范和编程规范，正规一些的IT公司都会制定一些关于数据库对象的命名和编程规范。 否则的话，你写你的我写我的，各自为战不兼容，彼此看不懂，甚至到最后连自己都弄不明白了，这样的经历相信每个人都曾经遇到过。 例如下面这段简单的代码，您看的明白吗？就算暂时明白，过个一年半载您还明白吗？就算您记忆超群，想必也会忘记，不是么？ A:=1;b:=2;SeLeCT username from EmPLOyee where id=a aNd Type=b; 如果你改为下面这样的写法，相信稍微懂点数据库的人都应该看的明白不是？ vID:=1; --A为ID vType=2; --B为类型 SELECT username FROM employee WHERE id=vID AND type=vType; 1.3 书写规范 丑陋的书写规范不仅可读性较差，而且给人以敬而远之的感觉，就是您是大侠也不行啊；而良好的书写规范则给人以享受和艺术的体验。 1.3.1大小写风格 规则1.3.1.1 所有数据库关键字和保留字均使用大写；关于字段、变量的大小写风格在1.4详细介绍。 1.3.2缩进风格 规则1.3.2.1 程序块严格采用缩进风格书写，保证代码清晰易读，风格一致，缩进格数统一为2 /4个。 必须使用空格，不允许使用TAB键。以免用不同的编辑器阅读程序时，因TAB键所设置的空格数目不同而造成程序布局不整齐。 规则1.3.2.2 同一条语句需要占用多于一行时，每行的其它关键字与第一行的关键字进行右对齐。 IF flag=1 THEN SELECT username --同上一行相比缩进4个空格 INTO vuserinfo --INTO与SELECT进行右对齐 FROM userinfo --FROM与SELECT进行右对齐 WHERE userid=:iuserid; --WHERE与SELECT进行右对齐 END IF; 1.3.3空格及换行 规则1.3.3.1 不允许把多个语句写在一行中，即一行只写一条语句。 规则1.3.3.2 避免将复杂的SQL语句写到同一行，建议要在关键字和谓词处换行。 规则1.3.3.3 相对独立的程序块之间必须加空行。 BEGIN、END独立成行 规则1.3.3.4 太长的表达式应在低优先级操作符处换行，操作符或关键字放在新行之首。划分出新行应当适当地缩进，使排版整齐，语句可读。 不同类型的操作符混合使用时，建议使用括号进行隔离，以使代码清晰。 规则1.3.3.5 减少控制语句的检查次数，如在IF…ELSE控制语句中，对最常用符合条件，尽量前置以被检查到。 DECLARE --定义局部变量 vFlag VARCHAR2(10); --判断标志 … BEGIN IF ((a=b AND a=c AND a=d) OR --在OR处断行，可使得逻辑更为清晰 (a=e AND e=f)) THEN --Process something IF vFlag=1 THEN --vFlag=1为经常出现之条件，可有效减少判断检查次数 --Process something ELSIF vFlag=2 THEN --vFlag=2为次之出现的条件 --Process something ELSE --Process something END IF; 1.3.4其它 规则1.3.4.1 避免使用SELECT *语句；不要用*来代替所有字段，应给出字段列表，以避免表结构发生变化时应用程序出现无法识别的情况。 规则1.3.4.2 INSERT语句必须给出字段列表，以避免表结构发生变化时发生编译错误。 规则1.3.4.3 当一个PL/SQL或SQL语句中涉及到多个表时，始终使用别名来限定表名和字段名，这使其它人阅读起来更方便，避免了含义模糊的引用，并能够别名中清晰地判断出表名和相关字段名。 规则1.3.4.4 确保变量和参数在类型和长度与表数据列类型和长度相匹配。说明：如果与表数据列宽度不匹配，则当较宽或较大的数据传进来时会产生运行异常。 DECLARE --定义相关表字段变量 vDeptNo salary.Deptno%type; --not VARCHAR2(10)，以适应变化 vEmployeeNo salary.EmployeeNo%type; --not VARCHAR2(10)，以适应变化 vSalary salary.Salary%type; --not NUMBER，以适应变化 BEGIN --Process something END; 1.4 命名规范 一千个读者就有一千个哈姆雷特，对于命名规范来说，想做到完全统一的确是不可能的任务。命名规范更多的是个人层面的爱好，就算有命名规范，也不过是体现制订规范的相关人的爱好而已。 因此即使无法完全做到一致，但是我们仍然要尽量去遵守，必要的时候需要通过代码检查和专家评审来进行约束，因为一个不成熟的规范总会胜过没有规范。 1.4.1表和字段命名规范 在此仅提供几种常见的命名方法(表和字段的命名方式雷同)。 以用户权限字段/表为例： UserPrivilege 适合那些英文比较好，并且喜欢抑扬顿挫和有艺术美感的人。 userprivilege 适合那些英文好，且比较严谨的人，毕竟全部小写很容易与数据库关键字相区别。 tbl_user_privilege 适合那些做开发的人，开发的人会习惯性的给变量加前缀。 (这里指表的命名，字段一般很少加前缀) yhqx 热爱中文的人，前提是恐怕您得对这些缩写先做好相关备注，等大家习惯了才行。 实际上这几种命名规范各有千秋，很难去指责或否定哪种更好，完全取决于整个公司多数人的习惯，记住没有十全十美的命名规范，只有绝大多数人心甘情愿的去遵从了，那就是好的命名规范。 就我个人而言，我更偏向于第一种命名习惯。 规则1.4.1.1 不建议使用数据库关键字和保留字（不建议并不意味着不能使用），只是为了避免不必要的冲突和麻烦； 例如name,id,level,remark,description等等。 有兴趣的话，大家可以参考下SELECT * FROM v$reserved_words WHERE reserved='Y' 实际上oracle不建议大家使用v$reserved_words表中所有的关键字，无奈这些关键字太多了；reserved='Y'的关键字则是被完全禁止的。 规则1.4.1.2 严禁使用带空格的名称来对字段和表命名；在产生数据库脚本并重新加载的时候可能会出现意想不到的错误而被迫终止。 1.4.2其它对象命名 用户自定义的数据库对象名包括表、视图、主外键、索引、触发器、函数、存储过程、序列、同义词、数据库链接、包和包体等等。 规则1.4.2.1 其它对象的命名也与表和字段的命名规则类似，风格保持一致即可 规则1.4.2.2 除数据库名称长度为1－8个字符，其余为1－30个字符，database link名称也不要超过30个字符； 命名只能使用英文字母，数字和下划线 规则1.4.2.3 除表外，其它各种对象的命名最好用不同的前缀加以区别。采用前缀的方式来命名对象则很容易通过排序对对象进行区别。 如在命名规范中各组成部分以_分割，则前缀建议也以_分割；反之则可加可不加 对象名 前缀 范例 表(table) tbl_/t_ (或不加前缀） userinfo/t_user_info/ tbl_user_info 视图(view) v_/v v_user_info/vuserinfo 序列(sequence) seq_ seq_user_info 簇(cluster) c_ c_user_info 触发器(trigger) trg_ trg_user_info 存储过程(procedure) sp_/p_ sp_user_info/p_user_info 函数(function) f_/fn_ fn_user_info/f_user_info 物化视图(materialized view) mv_ mv_user_info 包和包体(package & package body) pkg_ pkg_user_info 类和类体(type & type body) typ_ typ_user_info 主键(primary key) pk_ pk_user_info 外键(foreign key) fk_ fk_user_info_fieldname 唯一索引(unique index) uk_ uk_user_info_fieldname 普通索引(normal index) idx_ idx_user_info_fieldname 位图索引(bitmap index) bk_ bk_user_info_fieldname 同义词(synonym) 依据于所分配的表所属模块/模式 数据库链接(database link) 无特殊要求 1.5 变量命名 规则1.5.1 所有PL/SQL中的变量与对象命名规则相似 变量类型 前缀 范例 输入变量 i_/i i_user_id/iuserid 输出变量 o_/o o_user_name/ousername 输出输入变量 io_/io io_user_name/iousername 普通变量 v_/v v_user_id/vuserid 全局变量 gv_/gv gv_user_id/gvuserid 常量 大写 PI 游标 cur_ cur_userinfo 用户自定义类型 type_ type_user_info 保存点(save point) spt_ spt_user_info 规则1.5.2 命名不允许使用中文或者特殊字符。 命名中若使用特殊约定或缩写，则要注释说明。 规则1.5.3 使用有意义、易于记忆、描述性强、简短及唯一的英文单词/拼音缩写。自己特有的命名风格，要自始自终保持一致，不可来回变化。 说明：个人命名风格，在符合所在项目组的命名规则的前提下，才可以使用。 规则1.5.4 对于变量命名，禁止取单个字符(如i、j…)，建议除了要有具体含义外，还能表明变量类型等。 说明：变量，尤其是局部变量，如果用单个字符表示，很容易敲错(如i写成j)，而编译时又检查不出来，有可能为了这个小小的错误而花费大量的时间。 1.6 注释规范 注释规范是判断一个开发人员优劣和成熟度的重要指标。一个优秀的研发人员必然是经过深思熟虑然后才洋洋洒洒妙笔生花的，注释的书写体现了一个人思考问题的全过程和步骤；话又说回来，就算代码写的烂，只要注释写的好，至少也会给人以良好的感觉；同时也能造福后人，不是么？呵呵。 规则1.6.1 一般情况下，源程序有效注释量必须在30%左右。 说明：注释的原则是有助于对程序阅读理解，在该加的地方都加了，注释不宜太多也不能太少，注释语言须准确、易懂、简洁、精炼。 规则1.6.2 统一文件头的注释. 主要是对相关过程、函数进行功能性描述、修订记录、以及入参出参说明 对存储过程、函数的任何修改，都需要在注释后添加修改人、修改日期及修改原因等修订说明。 /*********************************************************** 名称: sp_xxx 功能描述： 修订记录： 版本号 编辑时间 编辑人 修改描述 1.0.0 2010-01-01 John 1、创建此存储过程 1.0.1 2010-02-01 Sandy 2、增加传入参数 入参出参描述： iparameter1 IN VARCHAR2(20) 传入参数1 iparameter2 IN VARCHAR2(20) 传入参数2 iparameter1 OUT VARCHAR2(20) 传入参数1 iparameter2 OUT VARCHAR2(20) 传入参数2 返回值描述：(主要针对函数) 0 - Success 1 - normal fail ***********************************************************/ 规则1.6.3 所有变量定义需要加注释，说明该变量的用途和含义。 规则1.6.4 注释内容要清晰、明了、含义准确，防止注释二义性 在代码的功能、意图层次上进行注释，提供有用、额外的信息。 避免在一行代码或表达式的中间插入注释。 尽量使用”--”进行注释；行尾注释须使用”--”。 规则1.6.5 对程序分支必须书写注释。 说明：这些语句往往是程序实现某一特定功能的关键，对于维护人员来说，良好的注释帮助更好的理解程序，有时甚至优于看设计文档。 在程序块的结束行右方加注释，以表明程序块结束。 规则1.6.6 注释应与其描述的代码相似，对代码注释应放在其上方或右方(对单条语句的注释)相近位置，不可放在下面。 注释与所描述的内容进行同样的缩排。 注释上面的代码应空行隔开。 建议1.6.7 注释用中文书写 有一次，同事写了一个900行的存储过程，里面定义了十几个游标以进行遍历，这个存储过程缺乏注释，执行一次居然要一天一夜，已经达到了无法容忍的地步。 因为缺乏注释，我花了整整一天的时间来对该存储过程进行分析，然后用了半天时间来进行改写和调试。 其实很简单定义，我定义了一些对应的临时表，把游标遍历替换成SQL的集合操作，把整个的一个大事务分割成若干小事务，只是修改了部分代码，结果执行时间就变成了短短的3分钟。 当然游标也并非不可触及的，既然存在就有他存在的理由。 1.7 语法规范 良好的语法规范有助于书写出高效、完备的PL/SQL程序，同时有助于提高系统的容错性、健壮性、可追溯性。 规则1.7.1 避免隐式的数据类型转换。 说明：在书写代码时，必须确定表的结构和表中各个字段的数据类型，特别是书写查询条件时的字段就更要注意了。这个是导致SQL性能不佳常犯的错误之一。 规则1.7.2 为了方便不同的数据库平台的移植，尽量使用SQL99标准，而不要使用Oracle的方言。 例如：DECODE函数完全可以用CASE WHEN语句代替，而且可编程性更强。 (+)=右关联用RIGHT OUTER JOIN语句代替。 =(+)左关联用LEFT OUTER JOIN语句代替。 规则1.7.3 对于非常复杂的SQL(特别是多层嵌套，带子句或相关的查询)，应该先考虑是否设计不当引起的，对于复杂的一些SQL可以考虑使用程序实现，原则上遵循一句话只做一件事情。 关于处理的优先级 1、 静态SQL>动态SQL 2、 绑定变量的SQL>动态SQL（在OLTP系统中建议这么做） 3、 SQL>PL/SQL的过程，极端复杂的SQL除外 4、 SQL>游标遍历 5、 Oracle函数>自定义函数 6、尽量使用Oracle分析函数代替同一个表多次的关联。 规则1.7.4 原则上不要使用动态SQL，如果非得使用动态SQL，建议使用绑定变量。 规则1.7.5 一定要及时关闭和释放游标 规则1.7.6 建议在异常处理中，把收集到错误信息记入错误日志表，以备查询和分析。 CREATE OR REPLACE PROCEDURE sp_increament_xxx /*********************************************************** 名称: sp_increament_xxx 功能描述：xxx表/模块数据增量更新，错误原因分析通过tbl_task_table日志表 修订记录： 版本号 编辑时间 编辑人 修改描述 1.0.0 2010-05-01 John 1、创建此存储过程 1.0.1 2010-06-01 Sandy 2、更新xxx字段在xxx处 入参出参描述： N/A 返回值描述：(主要针对函数) N/A ***********************************************************/ AS v_err_num NUMBER; v_err_msg VARCHAR2(100); v_begin_date DATE; v_end_date DATE; BEGIN v_err_num:=0; v_err_msg:=''; --某表增量更新步骤 BEGIN SAVEPOINT spt_xxx; --从任务表中获取更新初始时间 SELECT lasttime INTO v_begin_date FROM tbl_task_table T WHERE id='sp_increament_xxx'; --从源数据表中获取更新最后时间 SELECT MAX(oper_date) INTO v_end_date FROM tbl_table_source; --为提高执行效率，将增量数据写入临时表中 INSERT INTO tmp_tbl_table_source (fieldname1,fieldname2,fieldname3, fieldname4,fieldname5,fieldname6) SELECT fieldname1,fieldname2,fieldname3, fieldname4,fieldname5,fieldname6 FROM tbl_table_source sourcetable WHERE sourcetable.create_date > v_begin_date AND sourcetable.create_date <= v_end_date; --再讲增量数据从临时表更新到最终目标表 MERGE INTO tbl_table_original_dest desttable USING tmp_tbl_table_source tmptable ON (desttable.primarykey = tmptable.primarykey) --匹配判断标准，根据主键判断 WHEN MATCHED THEN --如果已存在，更新原纪录 UPDATE SET desttable.fieldname1 = tmptable.fieldname1, desttable.fieldname1 = tmptable.fieldname1, desttable.fieldnamem = tmptable.fieldnamem, desttable.fieldnamen = tmptable.fieldnamen WHEN NOT MATCHED THEN --如果不存在，插入新纪录 INSERT (fieldname1,fieldname2,fieldname3, fieldname4,fieldname5,fieldname6) VALUES (tmptable.fieldname1,tmptable.fieldname2,tmptable.fieldname3, tmptable.fieldname4,tmptable.fieldname5,tmptable.fieldname6); --更新任务表相应的状态、时间 UPDATE tbl_task_table SET lasttime_=v_end_date,status='SUCCESS' WHERE id='sp_increament_xxx'; COMMIT; --异常处理，把错误记入相关日志表，可以及时找到错误原因并进行分析。 EXCEPTION WHEN OTHERS THEN ROLLBACK TO SAVEPOINT spt_xxx; v_err_num := SQLCODE; v_err_msg := SUBSTR(SQLERRM, 1, 100); UPDATE tbl_task_table SET lasttime_=v_begin_date,status='FAIL' WHERE id='sp_increament_xxx'; COMMIT; END; --某表增量更新步骤 BEGIN ... END; END; 本例为数据库定时调用存储过程，同时也只是为了演示存储过程的全部过程；对于常用的由客户端调用的存储过程，建议不要捕获异常，而由客户程序进行直接处理。 规则1.7.7 不要将空的变量值直接与比较运算符(符号)比较。如果变量可能为空，应使用IS NULL或IS NOT NULL或NVL函数进行比较。 规则1.7.8 尽可能地使用相关表字段的类型定义，形如%TYPE、%ROWTYPE。这样做当表结构发生变动的时候，能够最大程度的做到容错性和健壮性。 规则1.7.9 存储过程中变量的声明应集中在AS和BEGIN关键字之间，不允许在代码中随意定义变量，定义变量时，完成相同功能模块的变量应放在一起，与不同模块的变量应空行隔开，增加代码的可读性。 1.8 脚本规范 脚本规范有助于进行版本基线的管理、版本控制，也有助于系统的自动部署、定位和解决部署过程中出现的问题。 规则1.8.1 所有脚本按分类或内容分开存放，并按以下顺序存储： 1. 创建数据库角色、用户脚本 2. 创建数据库表空间、数据文件脚本 3. 创建数据类型脚本，自定义的数据类型 4. 创建业务表脚本，表是其他依赖关系的基础 5. 创建临时表脚本，可能会在过程脚本中用到 6. 创建视图脚本 7. 创建主外键脚本 8. 创建索引脚本 9. 创建触发器脚本 10. 创建函数、存储过程脚本 11. 初始化数据脚本 12. 创建作业脚本 规则1.8.2 创建每个对象代码的首部应该有对象注释 规则1.8.3 每个函数、存储过程应单独创建脚本，在配置库上按照功能模块存放到不同的目录下。 并在相应的目录下，创建一个运行所有脚本的总脚本。