SQL书写规范

SQL书写规范

 

    为了保证编写SQL语句质量，改善软SQL可读性，故需要对SQL书写要制定相关规范。本标准是为规范SQL书写而制定。

1.1 过程/函数头定义

包括过程/函数名称定义、参数定义、注释说明、变量定义四部分。

1.1.1 参数定义

所有的参数必须显示指出变量类型，对于返回参数，必须指出其方向；

参数变量定义格式为：“p_” +变量名称，参考变量命名规范。

如：p_UserName

1.1.2 注释说明

每一个存储过程/函数必须包括以下的注释内容：

(1).过程函数的代码/名称；

(2).功能描述，对于实现过程/函数的功能的描述，如果是复杂过程，尽量将过程的处理步骤在此处进行说明（有利于维护）；

(3).参数说明，包括输入、输出参数的含意及方向、可能出现的参数值范围及处理说明；

对于返回参数，必须严格定义和说明返回代码及其含意。

(4).创建人/日期、修改人/日期的列表说明；

1.1.3 变量定义

(1).变量的命名

所有的变量命名采用此方式：变量名 = “v_” + 变量名称。

其中变量名称由代表变量确切含意的单词代码组合而成，每一个单词第一个字母必须大写。

建议参数、变量命名与字段命名保持一致，单词之间采用下划线“_”分割。

对游标变量，建议采用不同的前缀，如 “c_”

(2).变量类型

必须显示定义变量的所属类型，尽量将变量的定义分开来，并进行格式化，以便程序代码的阅读；

一般的变量是与表的字段相关联，建议变量类型尽量不要采用显式定义，而采用与表字段关联的定义方式，

如 v_Variable_Name table_name.column_name%TYPE;

另外，对应尽量采用记录型（表记录，游标记录）变量定义方式，

如：v_Variable_Name c_Cursor_Name%ROWTYPE;

       v_Variable_Name table_name%ROWTYPE;

(3).变量注释

原则上要求对于一般变量定义时必须加上注释，如变量有特定的值范围，则必须显示描述各确切值及其含意说明；

1.1.4 字符类型数据

SQL中的字符类型数据应该统一使用单引号。特别对纯数字的字串，必须用单引号，否则会导致内部转换而引起性能问题或索引失效

问题。利用trim()，lower()等函数格式化匹配条件。

1.1.5 过程/函数体定义

在过程/函数的定义中，最重要的是过程代码的严谨性和可读性，主要包括以下的注意事项：

(1).所有的标准SQL保留字必须全部大字，所有的应用级的数据库对象小写。如表：sm_user；

(2).原则上要求所有的SQL语句必须在其前面加上注释，对于IF/CASE等流程控制语句，必须在语句前/后说明控制处理和可能的流程方向；

(3).所有的赋值语句要求变量与运算符之间要有空格。如：

   v_Count  := v_Count + 1；

(4).为了保证可读性，不允许一个操作语句按字段分行写。但提倡对于复杂的语句根据关键字以缩进对齐方式分行写。如下示：

SELECT ……

  FROM sm_user a, cd_branch b, cd_person c, …

WHERE ……

UPDATE sm_user SET ……

        WHERE ……

(5).对于复杂的语句块之间，要求中间加入空行；

(6).所有可能的返回结果，必须过程体中显示定义和说明，并在注释中说明。在其它的过程、代码中调用本过程/函数时，必须在其

代码中处理所有的各种可能的返回结果；

(7).在过程/函数中尽量少引用视图、游标等可能导致性能问题的数据库对象，需要对数据表、数据行加锁时，需要考虑由此导致的

并发操作失败的处理；

(8).所有的过程/函数代码在编写之前，必须要有设计原型及其说明；

1.2 过程错误处理

(1).所有的DML语句必须考虑死锁、并发、主关键字不唯一等的出错异常处理，应该对所有可能出现的异常进行捕获、编码处理，并

注明异常的内容（注：异常捕获及处理见2.2）；

(2).异常处理有两种需要考虑的问题，一是事务的完整性；二是错误的出错日志及返回处理。所有异常的根据其类型和等级进行以

下的处理：

类型	内容	处理
系统级	数据库级发出的底层的异常，如主关键字不唯一冲突等；	返回定义错误代码为其它错误，完全回退事务，登录异常日志；
应用级	应用系统定义并发出的异常，如指定记录不存在；	定义应用级错误代码，根据实际情况处理事务；

1.2.1 过程使用原则

(1).存储过程功能尽量保持独立、复用，但尽量避免嵌套调用，充分考虑其性能体现；

(2).使用前台代码调用存储过程时，必须考虑返回传入参数的有效性、代码的处理及展示、事务完整性处理；

(3).在B/S结构应用中，为保证事务完整性和提高程序性能，以及集中业务逻辑的处理，规定较为复杂的业务逻辑由设计人员通过存

储过程实现；

1.2.2 异常处理规范

在通过存储过程对数据库的处理进行业务处理时，必须定义相应的事务，必分别处理出现异常的情况，保证事务的完整性，

返回不同的结果代码（注：代码是预先定义的）。调用者必须要处理所有的返回代码，并根据需要登录事务处理日志。

ORACLE8i以上支持自主事务( AUTONOMOUS_TRANSACTION)，类似对立事务，可以考虑使用，特别对在脚本中进行日志记录操作有用。

对所有的存储过程、函数必须要根据处理的内容，以及可能出现的结果（含异常）定义所有的返回代码。

返回代码必须在调用者或被调用者的设计文档中体现，调用者必须处理所有的被调用者的返回代码。

/********************************************************/

以下为前台代码中处理异常的编码机制

/********************************************************/

/*

每一个错误代码由以下方式组成：

应用代码 + “-ERR:” + 异常性质 + 子系统段 + 模块段 + 序号

其中：

(1).应用代码：为应用本身的代码，要求2~4位字母；

(2).异常性质：根据异常的类型定义，A-应用级；S-系统级；

(3).子系统段：即将系统划分成的子系统段起始数。每个子系统使用两位子系统代码（参考数据库子系统划分）；

例如：系统管理异常范围为： SM010001~SMFF9999；

(4).模块段：将子系统段划分多个模块段，每个模块段有相应起始数。

例如：系统管理——用户维护：SM010001~SM019999

*/

所有过程和函数处理必须考虑可能出现的错误，一是数据库或操作系统底层错误；二是由于业务逻辑造成的错误；三是无反应或超

时；四是输入参数不在指定范围内。

设计所有的过程时，必须考虑出错后的恢复。

1.3 SQL健壮性规范

1.3.1

使用Insert语句一定要给出要插入值的字段列表，这样即使更改了表结构加了字段也不会影响现有系统的运行。

1.3.2

sequence 因回滚，系统崩溃（使用cache 内的值将认为已用），多表引用都将使其跳号，所以不能用于为连续序号

1.4 SQL安全性规范

1.4.1

无论在使用Select，还是使用破坏力极大的Update和Delete语句时，一定要检查Where条件判断的完整性，不要在运行时出现数据的

重大丢失。如果不确定，最好先用Select语句带上相同条件来果一下结果集，来检验条件是否正确。

1.4.2

对所有基础数据，在系统中都不应使用物理删除，而通过在基础数据表中增加“是否有效”的字段来控制记录是否进行删除。

对于业务数据，则根据业务需要或者用户需求的要求，来决定是否对数据执行物理删除操作。

1.5 SQL完整性规范

1.5.1

有依赖关系的表，例如主外键关系表，在删除父表时必须级联删除其子表相应数据，或者按照某种业务规则转移该数据。9I中表中

字段缩小及变类型，字段为空或表空，varchar和char长度不变可任意改，字段名和表名可字段可用 ALTER TABLE table SET

UNUSED (column) 设定为不可用，注意无命令再设为可用

1.5.2

对于非常复杂的sql（特别是有多层嵌套，带子句或相关查询的），应该先考虑是否设计不当引起的。对于一些复杂SQL可以考虑使

用程序实现。

1.6 ORACLE、SQL SERVER、DB2 UDB的数据类型差异

 

请参照下表。

数据类型	Oracle 9	SQL Server 2000	DB2 UDB 8
日期	DATE	DATETIME	DATE
时间	DATE	DATETIME	TIME
日期时间	DATE	DATETIME	TIMESTAMP
时间戳	TIMESTAMP	DATETIME	TIMESTAMP
定长字符串	NCHAR CHAR 最大2000字节	NCHAR最大4000字符， CHAR最大8000字节	CHAR 最大254字节 GRAPHIC 最大127双字节
变长字符串	VARCHAR2 最大4000字节 NVARCHAR2 最大4000字节 LONG 最大2G字节 CLOB 最大4G字节 NCLOB 最大4G字节	VARCHAR 最大8000字节 TEXT 最大2G字节 NVARCHAR 最大4000字符 NTEXT 最大1G字符	VARCHAR 最大32672字节 LONG VARCHAR 最大32700字节 CLOB 最大2G字节 VARGRAPHIC 最大16336双字节字符 LONG VARGRAPHIC 最大16350双字节字符 DBCLOB 最大1G双字节字符
BLOB	BLOB 最大4G字节	IMAGE 最大2G字节	BLOB 最大2G字节

 

1.7 性能范例

1.7.1

任何对列的操作都将导致表扫描而使得索引失效，它包括数据库函数、计算表达式等；查询时要尽可能将操作移至运算符右边。

举例

select * from record where substring(card_no,1,4)='5378'

用时：13秒

select * from record where amount/30< 1000

用时：11秒

select * from record where convert(char(10),date,112)='19991201'

用时：10秒

将SQL修改成下面语句：

select * from record where card_no like '5378%'

用时：< 1秒

select * from record where amount < 1000*30

用时：< 1秒

select * from record where date= '1999/12/01'

用时：< 1秒

分析： where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索

引；如果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表搜索。

1.7.2

IN、OR子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把子句拆开，拆开的子句应包含索引；

举例：

表stuff有200000行，id_no上有非群集索引，查询语句：

select count(*) from stuff where id_no in ('0','1')

用时：200000行23秒；620000行220秒；

将查询语句修改为：

select count(*) from stuff where id_no='0'

select count(*) from stuff where id_no='1'

用时：200000行3秒；620000行4秒；

分析：where条件中的'in'在逻辑上相当于'or'，所以语法分析器会将in ('0','1')转化为id_no ='0' or id_no='1'来执行。我们

期望它会根据每个or子句分别查找，再将结果相加，这样可以利用id_no上的索引；但实际上（根据showplan）,它却采用了"OR策

略"，即先取出满足每个or子句的行，存入临时数据库的工作表中，再建立唯一索引以去掉重复行，最后从这个临时表中计算结果。

因此，实际过程没有利用id_no上索引，并且完成时间还要受tempdb数据库性能的影响；实践证明，表的行数越多，工作表的性能就

越差。