SQL编程规范.doc

NC SQL编程规范一、概述本手册侧重于代码编写过程中SQL语句的编写规范问题，内容涉及书写风格、性能优化、多数据适配等方面。文档中用标示的内容为必须遵守的条例，其余的可视为建议。二、书写风格1. SQL语句全部使用小写。2. 引用字符时用单引号。如：update testable set idcol=abcd。 3. 连接符或运算符or、in、and、=， +，- 等前后加上一个空格。4. 严禁使用select * .形式的语句，必须指出select的具体字段，即select col1, col2, from tablea where .5. 严禁使用 insert into table values(?,?,?)，必须指出具体要赋值的字段，即 insert into tablea (col1, col2,) values(?,?,)6. SQL语句包含多表连接时，建议对每个表命名别名，对每个字段的使用都要带上表别名，即 select a.col1, a.col2, b.col3 from tablea a, tableb b where a.col4=b.col57. 避免隐含的类型转换。例如在where子句中numeric 型和int型的列的比较或相加。8. 读取是指通过JDBC读到的数据格式，保存是指保存在VO中的数据格式，插入或者更新是指insert或者update语句中的数据格式。a) 整型字段：读取时根据字段设置保存为Integer或者Long。b) 数字型字段：读取为BigDecimal，并保存为UFDouble，插入或者更新时为BigDecimal。c) 字符型字段：读取为String，并保存为String，插入或者更新为String。d) 布尔型字段：读取为String(Y OR N)，并保存为UFBoolean，插入或者更新时为String(Y OR N)。e) 时间字段：读取为String，并保存为UFDateTime，插入或者更新时的时间格式由中间件统一处理，有单独需求的要申请后才能决定。 9. 在子查询中前后必须加上括号, select col1, col2 from tablea where col3 in ( select col4 from tableb where col40)10. 避免在where使用1=1,1=2这种表达式作为部分条件，如 select col1, col2 from tablea where 1=1 and col1 0。11. 禁止使用视图。三、性能优化1. 尽量使用prepareStatement，利用预处理功能。2. 在进行多条记录的增加、修改、删除时，建议使用批处理功能，批处理的次数以整个SQL语句不超过相应数据库的SQL语句大小的限制为准。3. 建议每条SQL 语句中in中的元素个数在500以下，如果个数超过时，应拆分为多条SQL语句。禁止使用xx in(,.) or xx in(,)。4. 禁止使用or 超过 500，如 xx =123 or xx=456。 5. 尽量不使用外连接。6. 禁止使用not in 语句，建议用not exist。7. 禁止使用Union, 如果有业务需要，请拆分为两个查询。8. 禁止在一条SQL语句中使用3层以上的嵌套查询，如果有，请考虑使用临时表或中间结果集。9. 尽量避免在一条SQL语句中从= 4个表中同时取数，对于仅是作为过滤条件关联，但不涉及取数的表，不参与表个数计算；如果必须关联4个或4个以上表，尽量采用子查询的方式。10. 尽量避免使用order by和group by排序操作，因为大量的排序操作影响系统性能。如必须使用排序操作，尽量建立在有索引的列上。11. 对索引列的比较，尽量避免使用NOT 或 !=，可以考虑拆分为几个条件。如col1 是索引列，条件col1 !=0 可以拆分为col1 0 or col2 =、= 以及is null运算符，不能出现 、!=、以及is not null运算符。 否则在Oracle的decode函数无法表达。当必须使用 , != ，is not null时，建议采用如下变通方法：1）使用 !=时：例如 case when A!=B then e,可改为 ： case A=b then e1 else e (间接实现 A!=B)2）使用 时：例如 case when AB then e,可改为 ： case A=b then case A=B then e1 else e (间接实现 A=b then e1 else e (间接实现 A 时：例如 case when AB then e, 可改为 ： case A=b then case A=B then e1 else e (间接实现 AB)或 case AB)4）使用 is not null 时：例如 case when A is not null then e,可改为： case A is null then e1 else e (间接实现 A is not null)特别说明：当执行大数据量的操作时，sql Server 对 case when 的执行效率极低，甚至可能会死机，因此希望大家尽量不要使用case when。21. 左连接的写法必须带“outer”关键字。例如： select f1 from t1 left outer t2 on t1.f1 = t2.f1；而不是： select f1 from t1 left t2 on t1.f1 = t2.f1。22. 只能使用以下函数，如要使用新的函数必须申报，审批后才能使用。函数：coalesce, cast, len, left, replace, right, substring, lower, upper, ltrim, rtrim, abs, acos, asin, atan, cos, ceiling, exp, floor, log, power, round, sign,sin, square, sqrt, tan, count, max, min, sum, avg。23. substring函数中起始位置为1，表示从头开始。24. 对于一些char/varchar的字段的值，即使是0,1，2等值，也必须表达为0,1,2 25. 在cast和convert语句中只支持到字符型、日期型和数字型的转换，且日期型只支持UFDate的格式，即YYYY-MM-DD 26. 不能通过来percent n 限制查询结果集的记录数，也不能使用 select top n 的语句27. join 与on 必须严格匹配，不允许出现没有on的join。28. joinon 后面慎用 or,如果用到，请把or的范围用( )括起来29. 不能使用select into 的格式30. 给某个字符型的列赋值时，如果其值含有单引号，例如：set col=fsdffdsfdsfsdf, 有两种解决方案：1) 使用转意字符，将单引号写成,即 set col=fsdffdsfdsfsdf2) 通过set的方式赋值，即：set col= ？ 然后：setString(8,” fsdffdsfdsfsdf”)