SQL编写规范

亦凉 2022-09-20 08:45 8阅读 0赞

**1.书写格式**  
　　  
　　示例代码：  
　　  
　　存储过程SQL文书写格式例  
　　select  
　　c.dealerCode,  
　　round(sum(c.submitSubletAmountDLR + c.submitPartsAmountDLR + c.submitLaborAmountDLR) / count(\*), 2) as avg,  
　　decode(null, 'x', 'xx', 'CNY')  
　　from (  
　　select  
　　a.dealerCode,  
　　a.submitSubletAmountDLR,  
　　a.submitPartsAmountDLR,  
　　a.submitLaborAmountDLR  
　　from SRV\_TWC\_F a  
　　where (to\_char(a.ORIGSUBMITTIME, 'yyyy/mm/dd') >= 'Date Range(start)'  
　　and to\_char(a.ORIGSUBMITTIME, 'yyyy/mm/dd') <= 'Date Range(end)'  
　　and nvl(a.deleteflag, '0') <> '1')  
　　union all  
　　select  
　　b.dealerCode,  
　　b.submitSubletAmountDLR,  
　　b.submitPartsAmountDLR,  
　　b.submitLaborAmountDLR  
　　from SRV\_TWCHistory\_F b  
　　where (to\_char(b.ORIGSUBMITTIME, 'yyyy/mm/dd') >= 'Date Range(start)'  
　　and to\_char(b.ORIGSUBMITTIME,'yyyy/mm/dd') <= 'Date Range(end)'  
　　and nvl(b.deleteflag,'0') <> '1')  
　　) c  
　　group by c.dealerCode  
　　order by avg desc;  
　　  
　　Java source里的SQL字符串书写格式例  
　　strSQL = "insert into Snd\_FinanceHistory\_Tb "  
　　+ "(DEALERCODE, "  
　　+ "REQUESTSEQUECE, "  
　　+ "HANDLETIME, "  
　　+ "JOBFLAG, "  
　　+ "FRAMENO, "  
　　+ "INMONEY, "  
　　+ "REMAINMONEY, "  
　　+ "DELETEFLAG, "  
　　+ "UPDATECOUNT, "  
　　+ "CREUSER, "  
　　+ "CREDATE, "  
　　+ "HONORCHECKNO, "  
　　+ "SEQ) "  
　　+ "values ('" + draftInputDetail.dealerCode + "', "  
　　+ "'" + draftInputDetail.requestsequece + "', "  
　　+ "sysdate, "  
　　+ "'07', "  
　　+ "'" + frameNO + "', "  
　　+ requestMoney + ", "  
　　+ remainMoney + ", "  
　　+ "'0', "  
　　+ "0, "  
　　+ "'" + draftStruct.employeeCode + "', "  
　　+ "sysdate, "  
　　+ "'" + draftInputDetail.honorCheckNo + "', "  
　　+ index + ")";  
　　  
　　1).缩进  
　　对于存储过程文件，缩进为8个空格  
　　对于Java source里的SQL字符串，不可有缩进，即每一行字符串不可以空格开头  
　　  
　　2).换行  
　　1>.Select/From/Where/Order by/Group by等子句必须另其一行写  
　　2>.Select子句内容如果只有一项，与Select同行写  
　　3>.Select子句内容如果多于一项，每一项单独占一行，在对应Select的基础上向右缩进8个空格（Java source无缩进）  
　　4>.From子句内容如果只有一项，与From同行写  
　　5>.From子句内容如果多于一项，每一项单独占一行，在对应From的基础上向右缩进8个空格（Java source无缩进）  
　　6>.Where子句的条件如果有多项，每一个条件占一行，以AND开头，且无缩进  
　　7>.(Update)Set子句内容每一项单独占一行，无缩进  
　　8>.Insert子句内容每个表字段单独占一行，无缩进；values每一项单独占一行，无缩进  
　　9>.SQL文中间不允许出现空行  
　　10>.Java source里单引号必须跟所属的SQL子句处在同一行，连接符（"+"）必须在行首  
　　  
　　3).空格  
　　1>.SQL内算数运算符、逻辑运算符连接的两个元素之间必须用空格分隔  
　　2>.逗号之后必须接一个空格  
　　3>.关键字、保留字和左括号之间必须有一个空格  
　　  
　　 **2.不等于统一使用"<>"**  
　　  
　　Oracle认为"!="和"<>"是等价的，都代表不等于的意义。为了统一，不等于一律使用"<>"表示  
　　  
　　 **3.使用表的别名**  
　　  
　　数据库查询，必须使用表的别名  
　　  
　　 **4.SQL文对表字段扩展的兼容性**  
　　  
　　在Java source里使用Select \*时，严禁通过getString(1)的形式得到查询结果，必须使用getString("字段名")的形式  
　　使用Insert时，必须指定插入的字段名，严禁不指定字段名直接插入values  
　　  
　　 **5.减少子查询的使用**  
　　  
　　子查询除了可读性差之外，还在一定程度上影响了SQL运行效率  
　　请尽量减少使用子查询的使用，用其他效率更高、可读性更好的方式替代  
　　  
　　 **6.适当添加索引以提高查询效率**  
　　  
　　适当添加索引可以大幅度的提高检索速度  
　　请参看ORACLE SQL性能优化系列  
　　  
　　 **7.对数据库表操作的特殊要求**  
　　  
　　本项目对数据库表的操作还有以下特殊要求：  
　　  
　　1).以逻辑删除替代物理删除  
　　  
　　注意：现在数据库表中数据没有物理删除，只有逻辑删除  
　　  
　　以deleteflag字段作为删除标志，deleteflag='1'代表此记录被逻辑删除，因此在查询数据时必须考虑deleteflag的因素  
　　  
　　deleteflag的标准查询条件：NVL(deleteflag, '0') <> '1'  
　　  
　　2).增加记录状态字段  
　　  
　　数据库中的每张表基本都有以下字段：DELETEFLAG、UPDATECOUNT、CREDATE、CREUSER、UPDATETIME、UPDATEUSER  
　　  
　　要注意在对标进行操作时必须考虑以下字段  
　　  
　　插入一条记录时要置DELETEFLAG='0', UPDATECOUNT=0, CREDATE=sysdate, CREUSER=登录User  
　　  
　　查询一条记录时要考虑DELETEFLAG，如果有可能对此记录作更新时还要取得UPDATECOUNT作同步检查  
　　  
　　修改一条记录时要置UPDATETIME=sysdate, UPDATEUSER=登录User, UPDATECOUNT=(UPDATECOUNT+1) mod 1000,  
　　  
　　删除一条记录时要置DELETEFLAG='1'  
　　  
　　3).历史表  
　　  
　　数据库里部分表还存在相应的历史表，比如srv\_twc\_f和srv\_twchistory\_f  
　　  
　　在查询数据时除了检索所在表之外，还必须检索相应的历史表，对二者的结果做Union（或Union All）  
　　  
　　 **8.用执行计划分析SQL性能**  
　　  
　　EXPLAIN PLAN是一个很好的分析SQL语句的工具，它可以在不执行SQL的情况下分析语句  
　　  
　　通过分析，我们就可以知道ORACLE是怎样连接表，使用什么方式扫描表（索引扫描或全表扫描），以及使用到的索引名称  
　　  
　　按照从里到外，从上到下的次序解读分析的结果  
　　  
　　EXPLAIN PLAN的分析结果是用缩进的格式排列的，最内部的操作将最先被解读，如果两个操作处于同一层中，带有最小操作号的将首先被执行  
　　  
　　目前许多第三方的工具如PLSQL Developer和TOAD等都提供了极其方便的EXPLAIN PLAN工具  
　　  
　　PG需要将自己添加的查询SQL文记入log，然后在EXPLAIN PLAN中进行分析，尽量减少全表扫描  
　　  
　　 **ORACLE SQL性能优化系列**  
　　  
　　 **1.选择最有效率的表名顺序(只在基于规则的优化器中有效)**  
　　  
　　ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表（基础表driving table）将被最先处理  
　　  
　　在FROM子句中包含多个表的情况下，必须选择记录条数最少的表作为基础表  
　　  
　　当ORACLE处理多个表时，会运用排序及合并的方式连接它们  
　　  
　　首先，扫描第一个表（FROM子句中最后的那个表)并对记录进行排序；  
　　  
　　然后扫描第二个表（FROM子句中最后第二个表)；  
　　  
　　最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并  
　　  
　　例如:  
　　  
　　表 TAB1 16,384 条记录  
　　  
　　表 TAB2 5 条记录  
　　  
　　选择TAB2作为基础表 (最好的方法)  
　　  
　　select count(\*) from tab1,tab2 执行时间0.96秒  
　　  
　　选择TAB2作为基础表 (不佳的方法)  
　　  
　　select count(\*) from tab2,tab1 执行时间26.09秒  
　　  
　　如果有3个以上的表连接查询，那就需要选择交叉表（intersection table）作为基础表，交叉表是指那个被其他表所引用的表  
　　  
　　例如:  
　　EMP表描述了LOCATION表和CATEGORY表的交集  
　　SELECT \*  
　　FROM LOCATION L,  
　　CATEGORY C,  
　　EMP E  
　　WHERE E.EMP\_NO BETWEEN 1000 AND 2000  
　　AND E.CAT\_NO = C.CAT\_NO  
　　AND E.LOCN = L.LOCN  
　　  
　　将比下列SQL更有效率  
　　SELECT \*  
　　FROM EMP E ,  
　　LOCATION L ,  
　　CATEGORY C  
　　WHERE E.CAT\_NO = C.CAT\_NO  
　　AND E.LOCN = L.LOCN  
　　AND E.EMP\_NO BETWEEN 1000 AND 2000  
　　  
　　 **2.WHERE子句中的连接顺序**  
　　  
　　ORACLE采用自下而上的顺序解析WHERE子句  
　　  
　　根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾  
　　  
　　例如：  
　　(低效,执行时间156.3秒)  
　　SELECT \*  
　　FROM EMP E  
　　WHERE SAL > 50000  
　　AND JOB = 'MANAGER'  
　　AND 25 < (SELECT COUNT(\*) FROM EMP WHERE MGR=E.EMPNO);  
　　  
　　(高效,执行时间10.6秒)  
　　SELECT \*  
　　FROM EMP E  
　　WHERE 25 < (SELECT COUNT(\*) FROM EMP WHERE MGR=E.EMPNO)  
　　AND SAL > 50000  
　　AND JOB = 'MANAGER';  
　　  
　　 **3.SELECT子句中避免使用'\*'**  
　　  
　　当你想在SELECT子句中列出所有的COLUMN时，使用动态SQL列引用'\*'是一个方便的方法，不幸的是，这是一个非常低效的方法  
　　  
　　实际上，ORACLE在解析的过程中，会将'\*'依次转换成所有的列名  
　　  
　　这个工作是通过查询数据字典完