搜索
第1页共22页SQL开发规范文件编号：文件版次：文档编号版本号0.9分册名称sqlServer开发规范第1册/共1册总页数正文附录编制孙启琳审批生效日期东软集团股份有限公司第2页共22页修改记录版本号变更控制报告编号更改条款及内容更改人审批人更改日期0.92010001文档创建孙启琳2010-2-2第3页共22页1.1目的规范sql的编写格式，提高SQL语句的可读性，共享性和执行效率。1.2使用范围所有需要与数据库交互的应用系统。1.3概述大部分业务系统需要与数据库进行交互，与数据库交互的主要方式就是SQL语句，编写规范的SQL语句不但利于阅读，而且被数据库重复使用的几率也较大，执行效率相对较高，编写的好的SQL与编写的差的SQL在执行性能上可能会差几倍甚至几千几万倍，因此养成好的SQL编写规范对于提高项目质量及提高开发人员自身素质有着潜在的极大的影响。1.4书写SQL书写遵守如下规范：在同一个项目中，为了最大限度实现SQL的共享，要求书写sql语句时大小写要一致，为了阅读方便和统一起见，所有SQL语句全部小写（如SQL谓词，字段名，表名等）,常量除外，常量可以按需要书写。举例：下面两个相同的语句除常量外都要统一起来。1)selectnamefromemp;2)select‘NAME’fromempwhereemp_no=’QD001’SQL语句尽可能放在一行，若SQL太长放在一行中影响阅读时可分多行，但要保持缩进一致，缩进可用TAB或者空格，但TAB数和空格数最好一致。SQL语句中，各谓词之间以空格分割的，尽量保持空格数量一致，即若用一个空格分割，则全部都用一个空格分割，便于数据库能够共享。能使用绑定变量的，尽量使用绑定变量，尤其是在前台程序中.对下面列出的情况，慎重使用绑定变量：1）列值倾斜严重，如：某一状态列大部分值是‘1’，只有极少数值为’2’，这种情况不宜用绑定变量，而应该用常量，便于数据库使第4页共22页用柱状图统计信息。2）日期时间列。总之：书写SQL的目标是若sql的用途是一样的，则sql应该完全一致，包括空格，大小写。下面的语句由于写法不完全相同，数据库会理解为4条不同的语句从而导致重复编译，降低了性能。1）selectnamefromempwhereemp_no=’111’2）SelectnameFromempWhereemp_no=’111’3）selectNamefromEMPwhereemp_no=’111’4）selectnamefromempwhereEMP_NO=’111’下面的语句，由于语句规范，可以只编译一次。1）selectnamefromempwhereemp_no=’111’2）selectnamefromempwhereemp_no=’222’3）selectnamefromempwhereemp_no=’333’4）selectnamefromempwhereemp_no=:b11.5注释1.5.1开头注释所有的过程、函数、触发器、包都应该在开头有注释，注释中要列出对象名称；完成功能简述；调用模块，调用时机；创建日期；作者信息；历次修改日期；历次修改人；历次修改原因和其它作者认为重要的内容，在每次修改的注释之间建一空行。举例如下：/*************************************************************名称：PRO_WO_MULTI_REPAIR_JOB功能：多次维修判定程序调用：自动统计月结前，后台调用作者：xxx时间：2009-02-20第5页共22页修改人：xxx修改时间:2009-03-08修改内容：重写部分SQL优化性能修改人：xxx修改日期：2008-03-23修改内容：增加判断条件*************************************************************/1.5.2块注释对于复杂的语句块，必须提供块注释，清晰描述该语句块的功能逻辑、数据结构以及算法；块注释应该和它们所描述的代码具有一样的缩进格式；块注释之首应该有一个空行，用于把块注释和代码分割开来；块注释结构如下所示：…/*计算配送能力系数：派工系数＝a*b+c*d+e*fa:考评成绩，取xx的考评成绩c:承担度度，根据当前遗留量和总服务能力计算所得，c=当前遗留配送量/总配送能力...*/1.5.3行注释对于复杂的SQL语句，必须提供行注释，清晰描述该SQL语句功能以及目的；行注释结构如下所示：--计算动态能力得分,当负荷率超高时，可能出现负值，注意处理。第6页共22页添加注释时要注意：注释中包含GO命令时会生成一个错误消息。1.5.4过程和函数1.5.5命名规范存储过程/函数的命名（代码）使用以下方式：存储过程名=SP_+模块代码+存储过程代码函数名=F_+模块代码+函数代码存储过程以表现其功能的简洁语言进行命名，定义好后，放置于每一个Diagram的特定区域（一般置于右边部分的上边）。1.5.6过程/函数头定义规范包括过程/函数名称定义、参数定义、注释说明、变量定义四部分。参数定义所有的参数必须显示指出变量类型，对于返回参数，必须指出其方向；参数变量定义格式为：“a“+参数类型简写＋“_”＋参数名称，其中a是单词argument(参数)的第一个字母，参数类型如下：文本型：s日期时间型：d数字型：n整形：i布尔型：b举例：as_name标识文本型ad_birthdate表示日期时间型an_age表示数字型注释说明见前面描述。1.5.7变量定义规范(1).变量的命名除游标变量以外，所有的变量命名采用此方式：第7页共22页变量名=“v_”+变量名称游标变量采用：变量名=“cur”+变量名(第一个字母大写)。其中变量名称由代表变量确切含意的单词代码组合而成，每一个单词的首字母根据阅读的方便性决定是否需要大写。举例：变量名：v_EmpName游标名：curNetlist(2).变量类型定义变量的类型时，尽量采用显示定义的方式。对于仅用于Oracle系列的数据库应用，也可以引用表、游标的引用定义方式。尽量将变量的定义分开来，并进行格式化，以便程序代码的阅读。例如：v_UserNameCHAR(10)v_SEXCHAR(1)1.5.8变量注释原则上要求对于一般变量定义时必须加上注释，如变量有特定的值范围，则必须显示描述各确切值及其含意说明；1.5.9过程/函数体定义规范在过程/函数的定义中，最重要的是过程代码的严谨性和可读性，主要包括以下的注意事项：(1).如无特殊需要，SQL中涉及的所有内容都要小写。(2).原则上要求所有的SQL语句必须在其前面加上注释，对于IF/CASE等流程控制语句，必须在语句前/后说明控制处理和可能的流程方向；(3).所有的赋值语句要求变量与运算符之间要有空格。如：v_Count:=v_Count+1，并保持适当的对齐；(4).尽量避免复杂SQL，尤其是关联多个大表的SQL，对于需要关联多个大表的SQL最好分解成小的SQL分步处理，避免出现性能问题，对于复杂的语句块之间，要求中间加入空行；(5).所有可能的返回结果，必须在过程体中显示定义和说明，并在注释中说第8页共22页明。在其它的过程、代码中调用本过程/函数时，必须在其代码中处理所有的各种可能的返回结果；(6).锁定数据时，尽可能只锁定要操作的数据行，避免锁定整个表，限制使用localtable等DDL语句，若要对数据表、数据行加锁时，需要考虑由此导致的并发操作失败的处理；(7).所有的过程/函数代码在编写之前，必须要有设计原型及其说明；存储过程/函数错误定义(1).所有的DML语句必须考虑死锁、并发、主关键字不唯一等的出错异常处理，应该对所有可能出现的异常进行捕获、编码处理，并注明异常的内容（注：异常捕获及处理请参见1.2）；(2).异常处理有两种需要考虑的问题，一是事务的完整性；二是错误的出错日志及返回处理。所有异常的根据其类型和等级进行以下的处理：类型内容处理系统级数据库级发出的底层的异常，如主关键字不唯一冲突等；返回定义错误代码为其它错误，完全回退事务，登录异常日志；应用级应用系统定义并发出的异常，如指定记录不存在；定义应用级错误代码，根据实际情况处理事务；使用原则(1).存储过程功能尽量保持独立、复用，但尽量避免嵌套调用，充分考虑其性能体现；(2).使用前台代码调用存储过程时，必须考虑返回传入参数的有效性、代码的处理及展示、事务完整性处理；(3).直接使用参数作为变量处理，不要在存储过程中定义参数同样含义的变量使用，避免由于值的不同导致性能问题。1.6触发器1.6.1命名规范：触发器不同类型的命名结构如下操作时机命名结构InsertAfterai_表名第9页共22页UpdateAfterau_表名DeleteAfterad_表名1.6.2触发器编写规范请参见存储过程/函数编写规范。1.7异常所有过程和函数处理必须考虑可能出现的错误，一是数据库或操作系统底层错误；二是由于业务逻辑造成的错误；三是无反应或超时；四是输入参数不在指定范围内。设计所有的过程时，必须考虑出错后的恢复。1.8优化1.8.1SQL语句原则Where子句尽量避免使用函数；避免在ORDERBY子句中使用表达式；限制在GROUPBY子句中使用表达式；慎用游标；大小写规范统一，变量绑定统一，避免重复编译；尽可能少的返回结果集行的数量避免使用select*语句；减少结果集中的列的数量；视图嵌套使用不能超过3层；不要使用没有意义的列作为聚集索引列，例如，加1自增列；避免隐式类型转换，例如字符型一定要用’’，数字型一定不要使用’’；查询语句一定要有范围的限定，避免全表扫描操作；合理对大表进行分区；慎用DISTINCT关键字；慎用UNION关键字，可以用OR替代；使用top1替count(*)来判断是否存在记录；第10页共22页1.8.2索引创建原则同一索引中的组成列最好不要超过3列。把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面。根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。若某列中有大量的值是空值，可以建立索引。要对值分布较宽的列建立索引。若表主要用来查询，则可按需要建立索引，若对表操作主要是UPDATE，则尽可能少建索引。不要对值较窄的列建立索引，如性别不要索引较小的表（如表不足1000行）若某列的值大部分是a，少数是别的值（如b,c,d…），且经常以该列的其它值(如b,c,d…)为查询条件，则可以将值a设为空值，并在此列上创建索引。1.8.3充分利用索引1.8.3.1函数、表达式使用在where语句中，尽量避免在运算符左边对列进行函数或者表达式操作，容易引起全表扫描，要尽可能将操作移至运算符右边。1.8.3.2IN/OR子句使用IN、OR、NOTINSqlServer2005数据库可以分析出应该根据索引查找。属于2005版本的新特性。1.8.3.3!=或操作符子句使用!=或操作符可以用INDEXSEEK查找的，可以正常使用。第11页共22页1.8.3.4不要对索引字段进行运算例如：SELECTIDFROMTWHERENUM/2=100应改为:SELECTIDFROMTWHERENUM=100*2SELECTIDFROMTWHERENUM/2=NUM1如果NUM有索引应改为:SELECTIDFROMTWHERENUM=NUM1*2如果NUM1有索引则不应该改。1.8.3.5不要对索引字段进行格式转换日期字段的例子：WHERECONVERT(VARCHAR(10),日期字段,120)=‘2008-08-15’应该改为WHERE日期字段〉=‘2008-08-15’AND日期字段‘2008