第 1 章 大话数据库编程规范 目前在软件圈内有这么一个现象，就是 DBA 不太懂得写 PL/SQL，而开发人员写得又是五花八门，而且效率不高。如此一来，造成诸多弊端： 可读性差。读别人写的一个程序花费的时间，比自己写一个程序花费的时间还要长；不但别人看不懂，时间久了连自己也看不懂了。 可维护性差。程序越写越长，越改越烂。 可移植性差。今天用 Oracle 写一套，明天换成 SQL Server 的时候再写一套，众多的数据库开发人员在程序的苦海中重复着低级劳动。 效率和性能差。 一个存储过程或 SQL 执行效率简直可以让你感觉到对时间的绝望， 你很快就理解什么是相对论了。 1.1.1 1 编程规范概述 事实上为了统一在软件开发过程中关于数据库设计时的命名规范和编程规范，正规的 IT 公司都会制定一些关于数据库对象的命名和编程规范。 否则，你写你的我写我的，各自为战不兼容，彼此看不懂，甚至到最后连自己都弄不明白了，这样的经历相信每个人都曾经遇到过。 第 章 1 大话数据库编程规范 剑破冰山 Oracle 开发艺术 2 剑破冰山Oracle 开发艺术 如下面这段简单的代码，你看得明白吗？就算暂时明白，过个一年半载你还明白吗？就算你记忆超群，想必也会忘记，不是吗？ 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.1.2 2 书写规范 丑陋的书写规范不仅可读性较差，而且给人以敬而远之的感觉，就算你是大侠也不行；而良好的书写规范则给人以享受和艺术的体验。 1.2.1 大小写风格 规则 1.2.1.1 所有数据库关键字和保留字均使用大写；关于字段、变量的大小写风格在 1.4 节详细介绍。 1.2.2 缩进风格 规则 1.2.2.1 程序块严格采用缩进风格书写，保证代码清晰易读，风格一致，缩进格数统一为 2/4 个。 必须使用空格，不允许使用【Tab】键。以免在用不同的编辑器阅读程序时，因【Tab】键所设置的空格数目不同而造成程序布局不整齐。 规则 1.2.2.2 当同一条语句需要占用多于一行时，每行的其他关键字与第一行的关键字进行右对齐。 IF flag=1 THEN SELECT username -同上一行相比缩进 2 个空格 INTO vuserinfo -INTO 与 SELECT 进行右对齐 FROM userinfo -FROM 与 SELECT 进行右对齐 WHERE userid=:iuserid; -WHERE 与 SELECT 进行右对齐 END IF; 第 1 章 大话数据库编程规范 3 1.2.3 空格及换行 规则 1.2.3.1 不允许把多个语句写在一行中，即一行只写一条语句。 规则 1.2.3.2 避免将复杂的 SQL 语句写到同一行，建议要在关键字和谓词处换行。 规则 1.2.3.3 相对独立的程序块之间必须加空行。 BEGIN、END 独立成行。 规则 1.2.3.4 太长的表达式应在低优先级操作符处换行，操作符或关键字放在新行之首。划分出新行应当适当地缩进，使排列整齐，语句可读。 当不同类型的操作符混合使用时，建议使用括号进行隔离，以使代码清晰。 规则 1.2.3.5 减少控制语句的检查次数，例如，在 IFELSE 控制语句中，应将最常用的符合条件前置以被检查到。 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; 4 剑破冰山Oracle 开发艺术 1.2.4 其他 规则 1.2.4.1 避免使用 SELECT * 语句；不要用*来代替所有字段，应给出字段列表，以避免在表结构发生变化时应用程序出现无法识别的情况。 规则 1.2.4.2 INSERT 语句必须给出字段列表，以避免在表结构发生变化时发生编译错误。 规则 1.2.4.3 当一个 PL/SQL 或 SQL 语句中涉及多个表时，始终使用别名来限定表名和字段名，这使其他人阅读起来更方便，避免了含义模糊的引用，并能够在别名中清晰地判断出表名和相关字段名。 规则 1.2.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.1.3 3 命名规范 一千个读者就有一千个哈姆雷特，对于命名规范来说，想做到完全统一的确是不可能的任务。命名规范更多的是个人层面的爱好，就算有命名规范，也不过是体现制定规范的相关人的爱好而已。 即使无法完全做到一致，但是我们仍然要尽量去遵守，必要的时候需要通过代码检查和专家评审来进行约束，因为一个不成熟的规范总会胜过没有规范。 1.3.1 表和字段命名规范 在此仅提供几种常见的命名方法（表和字段的命名方式雷同） 。 第 1 章 大话数据库编程规范 5 以用户权限字段/表为例，如表 1-1 所示。 表 1-1 命名规范表 UserPrivilege 适合那些英文比较好，并且喜欢抑扬顿挫和有艺术美感的人 userprivilege 适合那些英文好，且比较严谨的人，毕竟全部小写很容易与数据库关键字区别 tbl_user_privilege 适合那些做开发的人，开发的人会习惯性地给变量加前缀 （这里指表的命名，字段一般很少加前缀） yhqx 热爱中文的人，前提是恐怕你得对这些缩写先做好相关备注，等大家习惯了才行 实际上这几种命名规范各有千秋，很难去指责或否定哪种不好，完全取决于整个公司多数人的习惯，记住没有十全十美的命名规范，只有绝大多数人心甘情愿地去遵从了，那就是好的命名规范。 就笔者个人而言，我更偏向于第一种命名习惯。 规则 1.3.1.1 不建议使用数据库关键字和保留字（不建议并不意味着不能使用） ，只是为了避免不必要的冲突和麻烦。 例如，name,id,level,remark,description 等。 如果有兴趣，则大家可以参考 SELECT * FROM v$reserved_words WHERE reserved=Y。 实际上 Oracle 不建议大家使用 v$reserved_words 表中所有的关键字，因为这些关键字太多了；reserved=Y的关键字则是被完全禁止的。 规则 1.3.1.2 严禁使用带空格的名称来给字段和表命名；在产生数据库脚本并重新加载的时候可能会出现错误而被迫终止。 1.3.2 其他对象命名 用户自定义的数据库对象名包括表、视图、主外键、索引、触发器、函数、存储过程、序列、同义词、数据库链接、包和包体等。 规则 1.3.2.1 其他对象的命名也与表和字段的命名规则类似，风格保持一致即可。 规则 1.3.2.2 除数据库名称长度为 18 个字符外，其余为 130 个字符，database link 名称也不要超过6 剑破冰山Oracle 开发艺术 30 个字符。 命名只能使用英文字母、数字和下画线。 规则 1.3.2.3 除表外，其他各种对象的命名最好用不同的前缀加以区别。采用前缀的方式来命名对象则很容易通过排序对对象进行区别。 如在命名规范中各组成部分以“_”分割，则前缀建议也以“_”分割；反之则可加可不加，如表 1-2 所示。 表 1-2 其他对象命名规范表 对象名 前缀 范例 表（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 章 大话数据库编程规范 7 1.1.4 4 变量命名 规则 1.4.1 所有 PL/SQL 中的变量与对象命名规则相似，如表 1-3 所示。 表 1-3 变量命名规范表 变量类型 前缀 范例 输入变量 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.4.2 命名不允许使用中文或者特殊字符。 命名中若使用特殊约定或缩写，则要注释说明。 规则 1.4.3 使用有意义、易于记忆、描述性强、简短及唯一的英文单词/拼音缩写。保持自己特有的命名风格，不可来回变化。 说明：个人命名风格，在符合所在项目组的命名规则的前提下，才可以使用。 规则 1.4.4 对于变量命名，禁止取单个字符（如 i、j） ，建议除了要有具体含义外，还要能表明变量类型等。 说明：变量，尤其是局部变量，如果用单个字符表示，很容易输错（如 i 写成 j） ，而编译时又检查不出来，有可能因为这个小小的错误而花费大量的时间。 8 剑破冰山Oracle 开发艺术 1.1.5 5 注释规范 注释规范是判断一个开发人员优劣和成熟度的重要标准。一个优秀的研发人员必然是经过深思熟虑后才洋洋洒洒妙笔生花的，注释的书写体现了一个人思考问题的全过程和步骤；话又说回来，就算代码写得很差，只要注释写得好，至少也会给人以良好的感觉。 规则 1.5.1 在一般情况下，源程序有效注释量必须在 30%左右。 说明：注释的原则是有助于对程序阅读理解，在该加的地方都加了，注释不宜太多也不能太少，注释语言需准确、易懂、简洁、精炼。 规则 1.5.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.5.3 所有变量定义需要加注释，说明该变量的用途和含义。 第 1 章 大话数据库编程规范 9 规则 1.5.4 注释内容要清晰、明了、含义准确，防止注释二义性。 在代码的功能、意图层次上进行注释，提供有用、额外的信息。 避免在一行代码或表达式的中间插入注释。 尽量使用“-”进行注释；行尾注释需使用“-” 。 规则 1.5.5 对程序分支必须书写注释。 说明：这些语句往往是程序实现某一特定功能的关键，对于维护人员来说，良好的注释有助于更好地理解程序，有时甚至优于看设计文档。 在程序块的结束行右方加注释，以表明程序块结束。 规则 1.5.6 注释应与其描述的代码相似，对代码的注释应放在其上方或右方（对单条语句的注释）相近位置，不可放在下面。 注释要与所描述的内容进行同样的缩排。 注释上面的代码应空行隔开。 规则 1.5.7 注释用中文书写。 有一次，同事写了一个 900 行的存储过程，里面定义了十几个游标以进行遍历，这个存储过程缺乏注释，执行一次居然要一天一夜，已经达到了无法容忍的地步。 因为缺乏注释，我花了整整一天的时间来对该存储过程进行分析，然后用了半天时间来进行改写和调试。 其实很简单，把游标遍历替换成 SQL 的集合操作，把整个大事务分割成若干个小事务，然后修改了部分代码，结果执行时间就变成了短短的 3 分钟。 当然游标也并非不可触及，既然存在就有它存在的理由。 1.1.6 6 语法规范 良好的语法规范有助于书写出高效、完备的 PL/SQL 程序，同时有助于提高系统的容错性、10 剑破冰山Oracle 开发艺术 健壮性、可追溯性。 规则 1.6.1 避免隐式的数据类型转换。 说明：在书写代码时，必须确定表的结构和表中各个字段的数据类型，特别是在书写查询条件时的字段就更要注意了。这个是导致 SQL 性能不佳的原因之一。 规则 1.6.2 为了方便不同数据库平台的移植，尽量使用 SQL99 标准，而不要使用 Oracle 的“方言” 。 例如，DECODE 函数完全可以用 CASE WHEN 语句代替，而且可编程性更强。 (+)=右关联用 RIGHT OUTER JOIN 语句代替。 =(+)左关联用 LEFT OUTER JOIN 语句代替。 规则 1.6.3 对于非常复杂的 SQL（特别是多层嵌套、带子句或相关的查询） ，应该先考虑是否是由设计不当引起的，对于复杂的一些 SQL 可以考虑使用程序实现，原则上遵循一句话只做一件事情。 关于处理的优先级。 静态 SQL动态 SQL。 绑定变量的 SQL动态 SQL（在 OLTP 系统中建议这么做） 。 SQLPL/SQL 的过程，极端复杂的 SQL 除外。 SQL游标遍历。 Oracle 函数自定义函数。 尽量使用 Oracle 分析函数代替同一个表多次的关联。 规则 1.6.4 原则上不要使用动态 SQL，如果非得使用动态 SQL，建议使用绑定变量。 规则 1.6.5 一定要及时关闭和释放游标。 规则 1.6.6 建议在异常处理中，把收集到的错误信息记入错误日志表，以备查询和分析。 CREATE OR REPLACE PROCEDURE sp_increament_xxx /* 名称: sp_increament_xxx 功能描述：xxx 表/模块数据增量更新，错误原因分析通过 tbl_task_table 日志表 第 1 章 大话数据库编程规范 11 修订记录： 版本号 编辑时间 编辑人 修改描述 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, 12 剑破冰山Oracle 开发艺术 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.6.7 不要将空的变量值直接与比较运算符 （符号） 比较。 如果变量可能为空， 应使用 IS NULL 或IS NOT NULL 或 NVL 函数进行比较。 规则 1.6.8 尽可能地使用相关表字段的类型定义，如%TYPE、%ROWTYPE。这样做当表结构发生变动的时候，能够最大程度地做到容错性和健壮性。 规则 1.6.9 在存储过程中变量的声明应集中在 AS 和 BEGIN 关键字之间，不允许在代码中随意定义变量。在定义变量时，完成相同功能模块的变量应放在一起，与不同模块的变量应空行隔开，增加代码的可读性。 第 1 章 大话数据库编程规范 13 1.1.7 7 脚本规范 脚本规范有助于进行版本基线的管理、版本控制，也有助于系统的自动部署、定位和解决部署过程中出现的问题。 规则 1.7.1 所有脚本按分类或内容分开存放，并按以下顺序进行存储： （1）创建数据库角色、用户脚本。 （2）创建数据库表空间、数据文件脚本。 （3）创建数据类型脚本，自定义的数据类型。 （4）创建业务表脚本，表是其他依赖关系的基础。 （5）创建临时表脚本，可能会在过程脚本中用到。 （6）创建视图脚本。 （7）创建主外键脚本。 （8）创建索引脚本。 （9）创建触发器脚本。 （10）创建函数、存储过程脚本。 （11）初始化数据脚本。 （12）创建作业脚本。 规则 1.7.2 创建每个对象代码的首部应该有对象注释。 规则 1.7.3 每个函数、存储过程应单独创建脚本，在配置库上按照功能模块存放到不同的目录下。并在相应的目录下，创建一个运行所有脚本的总脚本。