《SQL编码规范教材.ppt》由会员分享，可在线阅读，更多相关《SQL编码规范教材.ppt（64页珍藏版）》请在三一办公上搜索。
1、SQL编码规范教材,SITECH,提纲,概述排版规则注释规则命名规范SQL程序编码规则SQL性能规则与建议程序质量保证安全保障,1,1.1 概述1.2 范围,概述,制定编码规范的最主要的目的是为了对产出代码的长期维护。通常负责维护代码的人大多都不是开发者本人，如果有一个统一的代码格式以及说明就可以减少混淆提高理解速度。因此，下列的编码规范是基于良好的编码习惯和可读性的原则来制定的。,范围,本规范定义了所有代码编写者在编写SQL程序时应遵守的一些规则和习惯。本规范采用以下术语描述：规则：编程时强制必须遵守的原则。建议：编程时必须加以考虑的原则。说明【】：对此规则或建议进行必要的解释。示例：对此规
2、则或建议从正、反两个方面给出例子。,提纲,概述排版规则注释规则命名规范SQL程序编码规则SQL性能规则与建议程序质量保证安全保障,2,2.1一般规则2.2 E-SQL排版规则,一般规则,规则2.1 独立单元书写规则：整个文件，以过程、函数或包为相对独立的单元。在每个单位中，采用以下统一格式。示例：CREATE or REPLACE ASBEGINEXCEPTIONEND;即：CREATE、AS、BEGIN、EXCEPTION和END，在第1列。后面的内容，间隔1个TAB书写。除非内部处理，否则一律带上EXCEPTION。,一般规则,规则2.2 块关键字对齐原则：任何块的关键字，首字母在列上对齐
3、。即：一个程序块内部，标识程序块的关键字开始语句(IF、DO、FOR和BEGIN)、中间语句(ELSIF、EXCEPTION、ELSE)和结束语句(END IF、END LOOP、END)，在列位置对齐。规则2.3 块内缩进原则即：一个块下属的内容，从下一行开始，从这个块的标识首字符右移一个tab键，从右移一个tab键开始。规则2.4 TAB/空格一致原则即：一个源文件中，在内容缩进上，要么采用tab方式，要么采用4个空格方式。不能二者并存。强烈建议要求使用TAB对齐。,一般规则,规则2.5 程序块间隔行规则：SQL程序块之间，采用1行空行分隔。适当时候，可以存在2行空行，不要超过2行(注释行
4、不在此约定范围内)。规则2.6 IF语句：IF语句是条件判断语句，只有一个条件的时候，可以不用圆括号。两个以上条件的时候，要求用圆括号()，使得逻辑处理明晰。IF/ELSIF/ELSE/END IF关键字，每个都独立占一行。且每行的开始列位置都相同。特别注意，结束语句END IF后面，有分号。,一般规则,规则2.7 循环语句：循环开始语句(FOR、WHILE)跟LOOP语句分行。END LOOP语句，跟LOOP在同一列位置。示例：FOR v_Index IN 1.20LOOPEND LOOP;,一般规则,规则2.7 循环语句：一行最长不能超过80字符。当超过时，以语意为单位，进行断行，且断行后
5、的内容，缩进书写。示例：-创建汇总基础表 Create_BaseDat-p_BaseSQL in varchar-SQL 语句-p_sResultTBL in varchar-结果输出数据表-p_sInfoStr in varchar-提示信息-返回值：0记录数Function CReate_BaseData(p_BaseSQL in varchar,p_sResultTBL in varchar,p_sInfoStr in varchar2)RETURN INT;此外，SELECT/FROM/WHERE/ORDER BY/GROUP BY等子句必须另起一行写。,一般规则,规则2.9 空格：S
6、QL内算数运算符、逻辑运算符连接的两个元素之间必须用空格分隔。逗号之后必须接一个空格。关键字、保留字和左括号之间必须有一个空格。规则2.10 大小写规则SQL 语句中出现的系统保留字、内置函数名、SQL保留字、绑定变量等都应大写。,提纲,概述排版规则注释规则命名规范SQL程序编码规则SQL性能规则与建议程序质量保证安全保障,2,2.1一般规则2.2 E-SQL排版规则,E-SQL排版规则,规则2.11在EXEC SQL语句后加一个TAB键，然后再写SQL语句。规则2.12SQL语句的关键字SELECT、FROM、WHERE、AND等，必须尾部对齐。规则2.13语句的结束符“;”，放在语句的结束
7、位置，不单独起一行。规则2.14SQL语句中不能有注释，如“/*/”、“-”等，注释写在SQL语句的外面。,E-SQL排版规则,规则2.15SQL语句中的对齐方式用空格对齐，SQL语句到“EXEC SQL”之间用TAB对齐。示例：EXEC SQLSELECT COL1,COL2 FROM tab1 WHERE col1=?AND col2=?;规则2.16每个嵌入的EXEC SQL语句，必须单独一行开始。,提纲,概述排版规则注释规则命名规范SQL程序编码规则SQL性能规则与建议程序质量保证安全保障,3,3.1注释规则,注释规则,规则3.1 对象注释要求创建每一数据库对象时都要加上COMMENT
8、 ON注释，以说明该对象的功能和用途；建表时，对某些数据列也要加上COMMENT ON注释，以说明该列和/或列取值的含义。示例：comment on table RasInfo.TD_UserInfo is 普通地区用户信息表，用于记录除成都外的其他地区的用户信息,按地区分区;comment on column is 用户编码,主键;,注释规则,规则3.2 文件注释在每个SQL脚本文件的开头，要用块注释，进行文件功能说明，文件编写人、联系方式和文件创建时间说明等。由于PL/SQL语句的特殊性，每行注释前以-为标志。示例：-收入保障系统 后台数据库脚本-特殊号码汇总计算程序 dsras_pkgS
9、pecal_CalcSum.SQL-文本责任人：)-2007.02.28 开始创建-,注释规则,规则3.3 过程与函数的注释在每个过程或函数的开头，使用块注释，进行函数功能说明，入口出口参数和返回值说明。示例：-得到集团用户外部编码。-author xxxx-version%I%,%G%-since 1.00-inparam regionCode 地市代码-inparam smCode 业务代码-outparam grpUserNo 集团用户编码-return 返回0表示正确，否则出错！-,注释规则,规则3.4 变量注释对SQL代码中的局部变量，重要的，要进行注释。尤其对Package和OBJ
10、ECT中的内部全局变量，特别要进行注解。关于变量的注释，采用行尾注释。示例：TYPE RefCursor IS REF CURSOR;-定义参考游标类型-最大欠费用户指标所在地区描述f_AreaName VarChar(20);对常量及变量注释时，应注释被保存值的含义，包括合法取值的范围。,注释规则,规则3.5 参数注释对函数或过程的每个参数进行说明。如果取值范围确定，也一并说明。取值有特定含义的变量（如boolean类型变量），给出每个值的含义。规则3.6 功能性注释对较为复杂的 SQL 语句应注释，并说明算法和功能。应对不易理解的分支条件表达式加注释。对重要的计算应说明其功能。过长的函数实
11、现，应将其语句按实现的功能分段加以概括性说明。规则3.7 单行注释SQL程序中，尽量采用单行注释：-。这样，有利于程序调试时，通过块注释方式，实现大段代码注释。,提纲,概述排版规则注释规则命名规范SQL程序编码规则SQL性能规则与建议程序质量保证安全保障,4,4.1 数据库对象4.2 PL/SQL程序块内变量4.3 E-SQL源文件4.4常用英文缩写字典,数据库对象,建议4.1 命名长度和结构对象名不超过30个字符，不低于6个字符，建议在825个左右。由35部分构成，每部分为有一定含义的英文缩写或者数字。每部分建议为34个字符。英文缩写参见“常见缩写字典”。规则4.2 不得出现省市、项目简称不
12、允许出现某省某地市或某项目的简称，例如sc_CustInfo中的sc是不许可的。规则4.3 年月日等数字年月日数字符号，一般应在数据表的尾部。不允许将年月日及其它数字放在对象名的中间。例如，Cust200703Info不许可，CustInfo200703则许可。,数据库对象,规则4.4 区分非正式表存在于生产环境的非正式表，如备份表，必须以原表名加前缀bakyymmdd_，以便于识别和及时清理。因此，正式生产表中，最好不要出现BAK、BACK之类的字符串。,数据库对象,规则4.5 命名特例,提纲,概述排版规则注释规则命名规范SQL程序编码规则SQL性能规则与建议程序质量保证安全保障,4,4.1
13、 数据库对象4.2 PL/SQL程序块内变量4.3 E-SQL源文件4.4常用英文缩写字典,PL/SQL程序块内变量,规则4.6 程序块内变量命名全局变量，以g_开头进行命名；包内公用变量，以f_开头命名；过程局部变量，以v_开头命名；参数变量，以p_开头命名；游标变量，以cur_开头命名。,提纲,概述排版规则注释规则命名规范SQL程序编码规则SQL性能规则与建议程序质量保证安全保障,4,4.1 数据库对象4.2 PL/SQL程序块内变量4.3 E-SQL源文件4.4常用英文缩写字典,E-SQL源文件,规则4.7：E-SQL源文件命名源文件命名规则，一般如下：RXX_XX.pc或者RXX_XX
14、.cp我们以前的E-SQL命令一般都用*.cp，而国际标准一般都用*.pc。特此约定，以前的程序目录沿用*.cp，新的程序目录使用*.pc命名。文件名以大写字母R开头，后面跟操作代码。操作代码的前两位放在最前面，后面的两位按照顺序放在“_”的后面，如R11_04060820。,提纲,概述排版规则注释规则命名规范SQL程序编码规则SQL性能规则与建议程序质量保证安全保障,4,4.1 数据库对象4.2 PL/SQL程序块内变量4.3 E-SQL源文件4.4常用英文缩写字典,常用英文缩写字典,规则4.8：业务操作类（一）,常用英文缩写字典,规则4.8：业务操作类（二）,常用英文缩写字典,规则4.8：
15、业务操作类（三）,常用英文缩写字典,规则4.9：业务名词类（一）,常用英文缩写字典,规则4.9：业务名词类（二）,常用英文缩写字典,规则4.9：业务名词类（三）,提纲,概述排版规则注释规则命名规范SQL程序编码规则SQL性能规则与建议程序质量保证安全保障,5,5.1 PL/SQL程序块5.2 E-SQL程序5.3可读性要求5.4常用英文缩写字典,PL/SQL程序块,规则5.1 简单函数或过程：单一功能函数或过程(以含注释在内，不超过100行就能解决问题为标准)，约定采用独立过程或函数来实现。示例：-是否是一个有效的数值-参数：-p_NumStr in varchar2-字符型数字串-返回值：-
16、如果是数值，返回true；否则返回false-CREATE OR REPLACE Function RasInfo.IsNumber(p_NumStr in varchar2)return booleanASv_Num Number;BEGINIF p_NumStr IS NULL THENreturn False;END IF;v_Num:=To_Number(p_NumStr);Return True;EXCEPTIONWHEN OTHERS THENRETURN False;END;,PL/SQL程序块,规则5.2 不允许使用内嵌函数或过程：尽管ORCALE支持内嵌，但是不允许使用内嵌函
17、数或过程。规则5.3 复杂函数或过程：功能比较复杂的函数，不建议使用独立函数或过程的实现。必须使用Package或OBJECT技术来实现。按照功能相近或相似原则，进行package或object的组织。复杂的业务逻辑，采用OBJECT技术来实现。公用的基础功能，采用package技术实现。,提纲,概述排版规则注释规则命名规范SQL程序编码规则SQL性能规则与建议程序质量保证安全保障,5,5.1 PL/SQL程序块5.2 E-SQL程序5.3可读性要求5.4常用英文缩写字典,E-SQL程序,规则5.4：SQL语句出错判断，使用NOTFOUND、SQLOK等宏判断SQL语句是否正确执行。规则5.5
18、：E-SQL使用自己的出错控制，不使用E-SQL提供的统一出错控制，E-SQL的出错一般设置为无效。EXEC SQL WHENEVER SQLERROR CONTINUE;EXEC SQL WHENEVER SQLWARNING CONTINUE;EXEC SQL WHENEVER NOT FOUND CONTINUE;规则5.6属主变量都要放在SQL定义区统一定义：EXEC SQL BEGIN DECLARE SECTION;char sSql1024;EXEC SQL END DECLARE SECTION;,提纲,概述排版规则注释规则命名规范SQL程序编码规则SQL性能规则与建议程序质量
19、保证安全保障,5,5.1 PL/SQL程序块5.2 E-SQL程序5.3可读性要求5.4常用英文缩写字典,可读性要求,规则5.7 不允许使用SELECT*：使用SELECT语句时，不允许使用SELECT*，要使用明确的列名。目的是防止数据字段增加后的影响。规则5.8 INSERT必须指定插入的字段名：使用INSERT时，必须指定插入的字段名，主要是考虑以后表结构的变动。规则5.9 函数及表达式放在等号左边：应将SQL语句中的数据库函数、计算表达式等放置在等号右边。不要在等号的左边。规则5.10 不等于统一使用：Oracle认为!=和是等价的，都代表不等于的意义。为了统一，不等于一律使用表示。规
20、则5.11 使用表的别名：多表关联查询，必须使用表的别名。,提纲,概述排版规则注释规则命名规范SQL程序编码规则SQL性能规则与建议程序质量保证安全保障,6,6.1性能相关的规则6.2性能相关的建议,性能相关的规则,规则6.1 添加索引提高效率：对大表的操作，业务规范后，必须建立相应的索引，以提高查询效率。规则6.2 WHERE子句中不应对索引列使用函数：不要在WHERE子句中，不应该对索引列施以函数，否则该索引将无法使用。规则6.3 使用显式的类型转换：不要使用数据库的类型自动转换功能（如数字自动转换为字符串），应使用显式的类型转换。自动转换时往往导致该字段的索引无法使用。,性能相关的规则,
21、规则6.4 为共享SQL语句，要使用绑定变量：执行相同操作的SQL语句必须使用相同名字的绑定变量例如：第一组的两个SQL语句，绑定变量是相同的，而第二组中的两个语句绑定变量不同，即使赋于不同的绑定变量相同的值也不能使这两个SQL语句相同，达不到共享SQL语句目的。a)第一组 select pin,name from people where pin=:blk1.pin;select pin,name from people where pin=:blk1.pin;b)第二组 select pin,name from people where pin=:blk1.ot_ind;select pi
22、n,name from people where pin=:blk1.ov_ind;,提纲,概述排版规则注释规则命名规范SQL程序编码规则SQL性能规则与建议程序质量保证安全保障,6,6.1性能相关的规则6.2性能相关的建议,性能相关的建议,建议6.5 减少子查询的使用：子查询除了可读性差之外，还在一定程度上影响了SQL运行效率.应尽量减少子查询的使用，采用其他效率更高、可读性更好的方式替代。,性能相关的建议,建议6.6 使用DECODE函数来减少处理时间：使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.示例：SELECT COUNT(*)，SUM(SAL)FROMEMP WH
23、ERE DEPT_NO=0020 AND ENAME LIKESMITH%;SELECT COUNT(*)，SUM(SAL)FROMEMP WHERE DEPT_NO=0030 AND ENAME LIKESMITH%;可以用DECODE函数高效地得到相同结果 SELECT COUNT(DECODE(DEPT_NO,0020,X,NULL)D0020_COUNT,COUNT(DECODE(DEPT_NO,0030,X,NULL)D0030_COUNT,SUM(DECODE(DEPT_NO,0020,SAL,NULL)D0020_SAL,SUM(DECODE(DEPT_NO,0030,SAL,N
24、ULL)D0030_SAL FROM EMP WHERE ENAME LIKE SMITH%;类似的,DECODE函数也可以运用于GROUP BY 和ORDER BY子句中,性能相关的建议,建议6.7 用UNION替换OR(适用于索引列)：通常情况下,用UNION替换WHERE子句中的OR将会起到较好的效果.对索引列使用OR将造成全表扫描.注意,以上规则只针对多个索引列有效.如果有column没有被索引,查询效率可能会因为你没有选择OR而降低.在下面的例子中,LOC_ID 和REGION上都建有索引.高效：SELECT LOC_ID,LOC_DESC,REGION FROM LOCATION
25、WHERE LOC_ID=10 UNION SELECT LOC_ID,LOC_DESC,REGION FROM LOCATION WHERE REGION=“MELBOURNE”低效：SELECT LOC_ID,LOC_DESC,REGION FROM LOCATION WHERE LOC_ID=10 OR REGION=“MELBOURNE”注意:WHERE KEY1=10(返回最少记录)OR KEY2=20(返回最多记录)ORACLE 内部将以上转换为 WHERE KEY1=10 AND(NOT KEY1=10)AND KEY2=20),性能相关的建议,建议6.8 如何删除重复记录：高效
26、的删除重复记录方法(因为使用了ROWID)DELETE FROM EMP E WHERE E.ROWID(SELECT MIN(X.ROWID)FROM EMP X WHERE X.EMP_NO=E.EMP_NO);建议6.9 用TRUNCATE替代DELETE删除全表：当删除表中的所有记录时,如果不需要恢复，建议使用TRUNCATE而不是DELETE ALL，既不占用回滚段，也能加快速度。建议6.10 特大事务多使用COMMIT：在程序中尽量避免特大事务，多使用COMMIT,这样程序的性能得到提高,也会因为COMMIT所释放的资源而减少。当然要注意，COMMIT次数也不能太频繁，频繁同样会增
27、加数据库负担。,性能相关的建议,建议6.11 用Where子句替换HAVING子句：避免使用HAVING子句,HAVING 只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.示例：低效：SELECT REGION，AVG(LOG_SIZE)FROM LOCATION GROUP BY REGION HAVING REGION!=SYDNEY AND REGION!=PERTH 高效：SELECT REGION，AVG(LOG_SIZE)FROM LOCATION WHERE REGION!=SYDNEY A
28、ND REGION!=PERTH GROUP BY REGION,性能相关的建议,建议6.12 用EXISTS替代IN：在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下,使用EXISTS(或NOT EXISTS)通常将提高查询的效率.示例：低效：SELECT*FROM EMP(基础表)WHERE EMPNO 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC=MELB)高效:SELECT*FROM EMP(基础表)WHERE EMPNO 0 AND EXISTS(SELECT X FROM DEPT WHERE
29、 DEPT.DEPTNO=EMP.DEPTNO AND LOC=MELB),性能相关的建议,建议6.13 用NOT EXISTS替代NOT IN：无论在哪种情况下,NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历).可以把它改写成外连接(Outer Joins)或NOT EXISTS.示例：SELECT FROM EMP WHERE DEPT_NO NOT IN(SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT=A);(方法一:高效)SELECT.FROM EMP A,DEPT B WHERE A.DEPT_NO=B.DEPT(+)AND B.DE
30、PT_NO IS NULL AND B.DEPT_CAT(+)=A(方法二:最高效)SELECT.FROM EMP E WHERE NOT EXISTS(SELECT X FROM DEPT D WHERE D.DEPT_NO=E.DEPT_NO AND DEPT_CAT=A);,性能相关的建议,建议6.14 用表连接替换EXISTS：通常来说,采用表连接的方式比EXISTS更有效率。SELECT ENAME FROM EMP E WHERE EXISTS(SELECT X FROM DEPT WHERE DEPT_NO=E.DEPT_NO AND DEPT_CAT=A);(更高效)SELEC
31、T ENAME FROM DEPT D,EMP E WHERE E.DEPT_NO=D.DEPT_NO AND DEPT_CAT=A;,性能相关的建议,建议6.15 用EXISTS替换DISTINCT：当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT。一般可以考虑用EXIST替换。示例：低效：SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO=E.DEPT_NO 高效：SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EX
32、ISTS(SELECT X FROM EMP E WHERE E.DEPT_NO=D.DEPT_NO);,性能相关的建议,建议6.17 避免在索引列上使用计算：WHERE子句中，如果索引列参与计算，优化器将不使用索引而使用全表扫描。示例：低效：SELECT FROM DEPT WHERE SAL*12 25000;高效:SELECT FROM DEPT WHERE SAL 25000/12;,性能相关的建议,建议6.18 避免在索引列上使用NOT：避免在索引列上使用NOT,NOT会产生在和在索引列上使用函数相同的影响.当ORACLE”遇到”NOT,会停止使用索引转而执行全表扫描。示例：低效:(
33、不使用索引)SELECT FROM DEPT WHERE NOT DEPT_CODE=0;高效:(使用了索引)SELECT FROM DEPT WHERE DEPT_CODE 0;需要注意的是,在某些时候,ORACLE优化器会自动将NOT转化成相对应的关系操作符。NOT to=to=NOT,性能相关的建议,建议6.19 用=替代：如果DEPTNO上有一个索引,高效：SELECT*FROM EMP WHERE DEPTNO=4 低效：SELECT*FROM EMP WHERE DEPTNO 3 两者的区别在于,前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记
34、录并且向前扫描到第一个DEPT大于3的记录。,提纲,概述排版规则注释规则命名规范SQL程序编码规则SQL性能规则与建议程序质量保证安全保障,7,7.1 程序质量保证,程序质量保证,规则7.1 运行日志：是指SQL运行点的输出日志。要求程序一定要使用日志语句，在运行过程输出。日志的输出，可以是数据表，或者通过Java函数，或者UT_FILE包，书写到操作系统文件。运行日志必须设置调试开关，在生产环境有效屏蔽其在开发测试环境输出的日志。规则7.2 错误日志：系统容错处理的日志，一定要与运行日志分开。使用数据表时，错误日志的数据表与运行日志表不同；使用操作系统文件时，错误日志文件名与运行日志文件名不
35、同。,程序质量保证,规则7.3 工作日志：系统关键性的SQL执行情况，应包括语句、记录数等的日志，便于程序上线后维护。规则7.2 错误日志：SQL程序，一定使用BEGIN/EXCEPTION/END代码块，进行错误捕获，并输出错误日志。,提纲,概述排版规则注释规则命名规范SQL程序编码规则SQL性能规则与建议程序质量保证安全保障,8,8.1 安全保障,PL/SQL安全,规则8.1 DML/DDL语句需说明功能和要求：Insert/delete/update语句以及脚本中的CREATE/DROP/ALTER命令，应添加注释说明其功能和要求。规则8.2 游标使用结束后必须关闭。规则8.3 PROC程序中禁止出现用户名和密码规则8.4 DML/DDL操作应显式写明对象属主数据库的表应用，独立的存储过程，独立的函数，或者处理包的开头，带上明确的数据对象属主，以避免误操作。例如：dbcustadm.dcustmsg,CREATE OR REPLACE PACKAGE dbRevass.pkgOweSum_BASEASBEGINEND;,携手共进 再创辉煌,
