SQL编写规范.doc

*公司ERP组SQL编写规范Author by DB小组1. 写SQL注意事项1.1 书写规范：1) SQL语句大小写: 所有数据库关键字和保留字均使用大写2) 不允许把多个语句写在一行中，一行只写一条语句3) 避免将复杂的SQL 语句写到同一行，建议要在关键字和谓词处换行4) 相对独立的程序块之间必须加空行。5) INSERT语句中必须指定字段列表，防止表结构变化导致的错误1.2 命名规范:1) 不要使用数据库关键字和保留字创建对象名(如select 等)2) 命名中不要使用中文和特殊字符,下划线可以使用.3) 严禁使用空格给表和字段来命名4) 创建对象时，对象名注意不要加双引号5) 对象名称不要超过30个字符6) 变量命名规定如下:i. 函数或过程中所接收变量名称以P_开头，如create proc aaa(P_branchid in varchar2)ii. 过程中所定义变量以V_开头，如declare v_branchid varchar2(3)7) ERP数据库对象命名必须加前缀以标识对象类型，规范如下:表（table）tb_视图（view） vw_ 序列（sequence） seq_ 簇（cluster） c_ 触发器（trigger） trg_ 存储过程（procedure） sp_ 函数（function） fun_ 物化视图（materialized view） mv_ 包和包体（package & package body） pkg_ 类和类体（type & type body） typ_ 主键（primary key） pk_ 外键（foreign key） fk_ 唯一索引（unique index） uk_ 普通索引（normal index） ix_ 位图索引（bitmap index） bi_ 同义词（synonym） 依据所分配的表所属模块 数据库链接（database link） 根据所连接服务器特点指定名称1.3 注释规范:1) 在一般情况下，源程序有效注释量必须在30%左右，注释不宜太多也不能太少，注释语言需准确、易懂、简洁、精炼。2) 尽量使用中文进行注释，对内容描述应该准确完整3) 避免在一行代码中间或表达式中间加入注释4) 不要在建表语句中间添加注释 5) 统一文件头的注释,主要是对相关过程、函数进行功能性描述、修订记录，以及入参出参说明,对存储过程、函数的任何修改，都需要在注释后添加修改人、修改日期及修改原因等修订说明。例子：/*名称: sp_sync_checkfd9功能描述：对比集中数据库与万州公司的销售数据差异修订记录：版本号 编辑时间 编辑人 修改描述1.0.0 2010-01-01 tzt. 创建此存储过程1.0.1 2010-02-01 wy 增加传入参数入参出参描述：iparameter1 IN VARCHAR2(20) 传入参数1iparameter2 IN VARCHAR2(20) 传入参数2iparameter1 OUT VARCHAR2(20) 传出参数1iparameter2 OUT VARCHAR2(20) 传出参数2返回值描述：(主要针对函数)0 - Success1 - normal fail*/1.4 性能方面注意点:1.1.1 即使相关字段上有索引，在如下4种情况，也不会走索引：(1)使用不等于操作符（、!=）(2)使用ISNULL或ISNOTNULL(3)使用函数(4)比较不匹配的数据类型所以在SQL 编写过程中，尽量避免以上4种情况。1.1.2 少用NOT，用NOT，若字段上有索引的话，则不会走索引。1.1.3 SELECT子句中避免使用*，减少不需要的数据，降低I/O。1.1.4 多表连接时，表应设置别名，应始终使用别名来限定表名和字段名。1.1.5 避免数据类型的隐式转换，列数据类型为varchar类型时，所对应的条件内容也应该为字符串。1.1.6 避免把表中的列放在表达式中去作比较，尽量将计算放在表达式的右边进行。示例：以下语句中过滤列上有索引，此写法将不会用到基于列的索引：（WHERE SAL*12 25000），应使用（WHERE SAL 25000/12）1.1.7 如果通配符（%）在搜寻词首出现， Oracle系统不使用此列做索引；然而当通配符出现在字符串其他位置时，优化器就能利用索引。示例：在下面的查询中索引得到了使用select * from employee where last_name like c%; 1.1.8 Union 和union all是将两个SQL查询结果集进行合并，但是union将两查询结果合并后的重复值进行过滤掉了，相当于union all多了一个排序操作，条件允许情况下，尽量使用union all。1.1.9 使用having的时候，注意是在用了聚合函数之后的结果集上进行过滤，若having后面的过滤列是单列的过滤，能放在where后面进行筛选的数据尽量不要在having后筛选。1.1.10 避免进行不必要的排序操作，order ,distinct ,group by , union 都会引起排序操作，任何在Order by语句中非索引项或者有计算表达式都将降低查询速度。适时用EXISTS替换DISTINCT，可提高效率，避免不必要的排序操作 1.1.11 避免相关子查询，in和exists都能引起相关子查询；若子查询后面的数据集比较少用IN较好，子查询的数据集较大用EXISTS较好。一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。in和exists区别：in是子查询驱动父查询，exists是父查询驱动子查询。exists和in的效率跟行源（row source）大小有关，exists一般是在大数据集中过滤高效，in一般在小数据集中过滤高效。通常来说，采用表连接的方式比exists更高效，所以 表连接exists in1.1.12 根据实际应用，适当语句分离。下面的查询将强迫对orders表执行顺序操作：select e.employee_id,e.first_name,salary from employees e,departments dwhere e.department_id=90 or e.employee_id=105虽然在department_id和employee_id上建有索引，但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合，所以应该改为如下语句：select e.employee_id,e.first_name,salary from employees ewhere department_id=90 unionselect e.employee_id,e.first_name,salary from employees ewhere employee_id=105这样就能利用索引路径处理查询。1.1.13 不等于统一使用“”。1.5 程序代码中应注意的:1.2.1 使用数据库提供的约束来判断某些错误，如唯一性有种情况，应用程序中经常出现，举个例子：有张全国居民身份表resident，其中有个字段身份证号码ID_card，是主键，在登记一个居民时，为了避免发生主键冲突，应用程序往往保存之前用一个 SqL语句去查此ID_card号是否已存在，存在则提示ID_card冲突。上述情况完全可以避免使用写SQL语句的方法去查，因为oracle的每个错误都有一个唯一的错误号，只要在程序中去捕捉违例(exception)，然后进行对比是否是主键冲突即可；有些时候非主键的字段也要保持唯一性，则可以使用唯一索引。当发生唯一性冲突时，也有一个错误号；1.2.2 由多个SQL语句完成的操作尽可能写成一个SQL完成，避免分解。示例：需要得到2004年中，每个季度(1-3月，4-6月，7-9月，10-12月)的日报个数；第一种方法：比较容易想到的，用4个SQL语句分别去得到各个季度的统计：Select count(*) from report where day=to_date(2004-01-01,yyyy-mm-dd) and day=to_date(2004-04-01,yyyy-mm-dd) and day=to_date(2004-07-01,yyyy-mm-dd) and day=to_date(2004-10-01,yyyy-mm-dd) and day=to_date(2004-01-01,yyyy-mm-dd) and day=to_date(2004-04-01,yyyy-mm-dd) and day=to_date(2004-07-01,yyyy-mm-dd) and day=to_date(2004-10-01,yyyy-mm-dd) and day select * from t1 ;EMPLOYEE_ID FIRST_NAME MANAGER_ID- - - 198 Douglas 124 199 Donald 124 180 Winston 103 200 Lilei 115 100 Steven不使用NVL： SQL select * from t1 where ( t1.manager_id=&mid or &mid is null) ;输入 mid 的值:输入 mid 的值:原值 1: select * from t1 where ( t1.manager_id=&mid or &mid is null)新值 1: select * from t1 where ( t1.manager_id= or is null)select * from t1 where ( t1.manager_id= or is null) *第 1 行出现错误:ORA-00936: 缺失表达式SQL select * from t1 where ( t1.manager_id=&mid or &mid is null) ;输入 mid 的值:输入 mid 的值:原值 1: select * from t1 where ( t1.manager_id=&mid or &mid is null)新值 1: select * from t1 where ( t1.manager_id= or is null)EMPLOYEE_ID FIRST_NAME MANAGER_ID- - - 198 Douglas 124 199 Donald 124 180 Winston 103 200 Lilei 115 100 Steven 等价于 select * from t1 ，此处manager_id列上用到了隐式转换。改进用NVL，改写如下：SQL select * from t1 where t1.manager_id= nvl(&mid , t1.manager_id);输入 mid 的值:原值 1: select * from t1 where t1.manager_id= nvl(&mid , t1.manager_id)新值 1: select * from t1 where t1.manager_id= nvl( , t1.manager_id)EMPLOYEE_ID FIRST_NAME MANAGER_ID- - - 198 Douglas 124 199 Donald 124 180 Winston 103 200 Lilei 115等价于select * from t1 where t1.manager_id= t1.manager_id,查询结果将NULL值排除了。2. 常见操作或函数：2.1 is null当比较一个字段是否为null或非null时，应该使用 where f1 is null或where f1 is not null，而不能用=或比较符；任何值与Null做=,，比较时，都返回false；任何值与null进行+-*/数字运算操作时，得到的还是null；字符串与null值使用|连接还是那个字符串值；2.2 trim功能：默认删除字符串首部和尾部的空格, oracle trim函数其实是具有删除“任意指定”字符的功能语法：TRIM( LEADING | TRAILING | BOTH trim_character | trim_character FROM trim_source )注意：这里的“trim_character”参数只允许包含一个字符，不支持多字符示例：select trim (both x from xxxxDWEYExxxx) TRIM e.g. from dual;2.3 LTRIM和RTRIMLTRIM和RTRIM可删多字符2.4 decode语法：decode (expression, search_1, result_1, search_2, result_2, , default_value)相当于if else if . Else2.5 nvl语法：nvl(expr1,expr2)功能：替换null值，当expr1为null时返回expr2值，否则返回expr1。2.6 nvl2语法：nvl2(expr1,expr2,expr3)功能：若expr1不为Null，则返回expr2