依据SQL编写的规范标准说明

1、!-SQL编写规范1书写格式示例代码：存储过程SQL文书写格式例selectc.dealerCode,roun d(sum(c.submitSubletAmou ntDLR + c.submitPartsAm oun tDLRc.submitLaborA moun tDLR) / coun t(*), 2) as avg,decode( null, x, xx, CNY)from (selecta.dealerCode,a.submitSubletAmou ntDLR,a.submitPartsAm oun tDLR,a. submitLaborA moun tDLRfrom SRV_TWC_

2、F awhere (to_char(a.ORIGSUBMITTIME, yyyy/mm/dd) = Date Ra nge(start) and to_char(a.ORIGSUBMITTIME, yyyy/mm/dd) = Date Ran ge(e nd) and n vl(a.deleteflag, 0) 1) union allselectb. dealerCode,b.submitSubletAmou ntDLR,b.submitPartsAmou ntDLR,b.submitLaborA moun tDLRfrom SRV_TWCHistory_F bwhere (to_char(

3、b.ORIGSUBMITTIME, yyyy/mm/dd) = Date Ra nge(start) and to_char(b.ORIGSUBMITTIME,yyyy/mm/dd) = Date Ra nge(e nd) and n vl(b.deleteflag,O) 1)cgroup by c.dealerCodeorder by avg desc;Java source里的SQL字符串书写格式例strSQL = insert into Sn d_Fi nan ceHistory_Tb + (DEALERCODE,+ REQUESTSEQUECE,+ HANDLETIME,+ JOBFL

4、AG,+ FRAMENO,+ INMONEY ,+ REMAINMONEY ,+ DELETEFLAG ,+ UPDATECOUNT,+ CREUSER,+ CREDATE,+ HONORCHECKNO,+ SEQ)+ values ( + draftl nputDetail.dealerCode + ,+ ” + draftI nputDetail.requestsequece + ,+ sysdate,+ 07,+ + frameNO + ,+ requestM oney + ,+ rema inMoney + ,+ 0,II+ 0,+ ” + draftStruct.employeeCo

5、de + ,+ sysdate,+ ” + draftI nputDetail.h on orCheckNo + ,+ in dex + );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子句内容如果只有一

6、项，与 From同行写5.From子句内容如果多于一项，每一项单独占一行，在对应From的基础上向右缩进8个空格（Java source无缩进）6.Where子句的条件如果有多项，每一个条件占一行，以AND开头，且无缩进7.（Update）Set子句内容每一项单独占一行，无缩进8.I nsert子句内容每个表字段单独占一行，无缩进；values每一项单独占一行，无缩进9.SQL文中间不允许出现空行10.Java sourceM单引号必须跟所属的SQL子句处在同一行，连接符（+）必须在行首3) .空格1.SQL内算数运算符、逻辑运算符连接的两个元素之间必须用空格分隔2.逗号之后必须接一个空格3.

7、关键字、保留字和左括号之间必须有一个空格2. 不等于统一使用Oracle认为!=和是等价的，都代表不等于的意义。为了统一，不等于 一律使用表示3. 使用表的别名数据库查询，必须使用表的别名4.SQL文对表字段扩展的兼容性在Java source里使用Select *时，严禁通过getString的形式得到查询结果， 必须使用getString(字段名)的形式使用Insert时，必须指定插入的字段名，严禁不指定字段名直接插入values5. 减少子查询的使用子查询除了可读性差之外，还在一定程度上影响了SQL运行效率请尽量减少使用子查询的使用，用其他效率更高、可读性更好的方式替代6. 适当添加索引

8、以提高查询效率适当添加索引可以大幅度的提高检索速度请参看ORACLE SQL性能优化系列7. 对数据库表操作的特殊要求本项目对数据库表的操作还有以下特殊要求：1) .以逻辑删除替代物理删除注意：现在数据库表中数据没有物理删除，只有逻辑删除以deleteflag字段作为删除标志，deleteflag=1代表此记录被逻辑删除，因此 在查询数据时必须考虑deleteflag的因素deleteflag 的标准查询条件：NVL(deleteflag, 0) 12) .增加记录状态字段数据库中的每张表基本都有以下字段：DELETEFLAG、UPDATECOUNT、CREDATE、CREUSER、UPDAT

9、ETIME、UPDATEUSER要注意在对标进行操作时必须考虑以下字段插入一条记录时要置 DELETEFLAG=O, UPDATECOUNT=0, CREDATE=sysdate, CREUSER=登录 User查询一条记录时要考虑DELETEFLAG，如果有可能对此记录作更新时还要 取得UPDATECOUNT作同步检查修改一条记录时要置UPDATETIME=sysdate, UPDATEUSER=登录 User,UPDATECOUNT=(UPDATECOUNT+1) mod 1000,删除一条记录时要置 DELETEFLAG=13) .历史表数据库里部分表还存在相应的历史表，比如 srv_t

10、wc_f和srv_twchistory_f在查询数据时除了检索所在表之外，还必须检索相应的历史表，对二者的结 果做 Un io n (或 Un io n All)8. 用执行计划分析SQL性能EXPLAIN PLAN是一个很好的分析SQL语句的工具，它可以在不执行SQL 的情况下分析语句通过分析，我们就可以知道 ORACLE是怎样连接表，使用什么方式扫描表 (索引扫描或全表扫描)，以及使用到的索引名称按照从里到外，从上到下的次序解读分析的结果EXPLAIN PLAN的分析结果是用缩进的格式排列的，最内部的操作将最先 被解读，如果两个操作处于同一层中，带有最小操作号的将首先被执行目前许多第三方的

11、工具如 PLSQL Developer和TOAD等都提供了极其方便 的 EXPLAIN PLAN 工具PG需要将自己添加的查询SQL文记入log，然后在EXPLAIN PLAN中进行 分析，尽量减少全表扫描ORACLE SQL性能优化系列1选择最有效率的表名顺序（只在基于规则的优化器中有效）ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表（基础表 driving table）将被最先处理在FROM子句中包含多个表的情况下，必须选择记录条数最少的表作为基 础表当ORACLE处理多个表时，会运用排序及合并的方式连接它们首先，扫描第一个表（FROM子句中

12、最后的那个表）并对记录进行排序；然后扫描第二个表（FROM子句中最后第二个表）；最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并例如：表TAB1 16,384条记录表TAB2 5条记录选择TAB2作为基础表（最好的方法）select count（*） from tab1,tab2 执行时间 0.96 秒选择TAB2作为基础表（不佳的方法）select count（*） from tab2,tab1 执行时间 26.09秒如果有3个以上的表连接查询，那就需要选择交叉表（in tersection table）作 为基础表，交叉表是指那个被其他表所引用的表例如：EMP表描述了 LOC

13、ATION表和CATEGORY表的交集SELECT *FROM LOCATION L,CATEGORY C,EMP EWHERE E.EMP_NO BETWEEN 1000 AND 2000AND E.CAT_NO = C.CAT_NOAND E丄 OCN =匚 LOCN将比下列SQL更有效率SELECT *FROM EMP E ,LOCATION L ,CATEGORYCWHERE E.CAT_NO = C.CAT_NOAND E丄 OCN =匚 LOCNAND E.EMP_NO BETWEEN 1000 AND 20002.WHERE子句中的连接顺序ORACLE采用自下而上的顺序解析 WH

14、ERE子句根据这个原理，表之间的连接必须写在其他 WHERE条件之前，那些可以过 滤掉最大数量记录的条件必须写在 WHERE子句的末尾例如：(低效，执行时间156.3秒)SELECT *FROM EMP EWHERE SAL 50000AND JOB = MANAGERAND 25 (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);(高效，执行时间10.6秒)SELECT *FROM EMP EWHERE 25 50000AND JOB = MANAGER;3.SELECT子句中避免使用*当你想在SELECT子句中列出所有的COLUMN时，使用动态SQL

15、列引用* 是一个方便的方法，不幸的是，这是一个非常低效的方法实际上，ORACLE在解析的过程中，会将*依次转换成所有的列名这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间4. 减少访问数据库的次数当执行每条SQL语句时ORACLE在内部执行了许多工作：解析SQL语句, 估算索引的利用率，绑定变量，读数据块等等由此可见，减少访问数据库的次数，就能实际上减少ORACLE的工作量例如：以下有三种方法可以检索出雇员号等于 0342或0291的职员方法1 (最低效)SELECT EMP_NAME, SALARY , GRADEFROM EMPWHERE EMP_NO = 342;SELECT

16、EMP_NAME, SALARY , GRADEFROM EMPWHERE EMP_NO = 291;方法2 (次低效)DECLARECURSOR C1 (E_NO NUMBER) ISSELECT EMP_NAME,SALARY ,GRADEFROM EMPWHERE EMP_NO = E_NO;BEGINOPEN C1(342);FETCH C1 INTO,，；OPEN C1(291);FETCH C1 INTO,，；CLOSE C1;END;方法2 (高效)SELECT A.EMP_NAME, A.SALARY , A.GRADE,B.EMP_NAME, B.SALARY , B.GRA

17、DEFROM EMP A, EMP BWHERE A.EMP_NO = 342AND B.EMP_NO = 291;5. 使用DECODE函数来减少处理时间使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表例如：SELECT COUNT(*), SUM(SAL)FROM EMPWHERE DEPT_NO = 0020AND ENAME LIKE SMITH%;SELECT COUNT(*), SUM(SAL)FROM EMPWHERE DEPT_NO = 0030AND ENAME LIKE SMITH%;你可以用DECODE函数高效地得到相同结果SELECT COUNT(DECO

18、DE(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, NULL) D0030_SALFROM EMPWHERE ENAME LIKE SMITH%;X表示任何一个字段类似的，DECODE函数也可以运用于 GROUP BY和ORDER BY子句中6. 用Where子句替换HAVING子句避免使用HAVING子句，HAVIN

19、G只会在检索出所有记录之后才对结果集 进行过滤，这个处理需要排序、统计等操作如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销例如：低效SELECT REGION, AVG(LOG_SIZE)FROM LOCATIONGROUP BY REGIONHAVING REGION REGION != SYDNEYAND REGION != PERTH高效SELECT REGION, AVG(LOG_SIZE)FROM LOCATIONWHERE REGION REGION != SYDNEYAND REGION != PERTHGROUP BY REGION7. 减少对表的查询在含有子查

20、询的SQL语句中，要特别注意减少对表的查询例如：低效SELECT TAB_NAMEFROM TABLESWHERE TAB_NAME = (SELECT TAB_NAMEFROM TAB_COLUMNSWHERE VERSION = 604)AND DB_VER = (SELECT DB_VERFROM TAB_COLUMNSWHERE VERSION = 604)高效SELECT TAB_NAMEFROM TABLESWHERE (TAB_NAME, DB_VER) = (SELECT TAB_NAME, DB_VERFROM TAB_COLUMNSWHERE VERSION = 604)U

21、pdate多个 Column 例子：低效UPDATE EMPSET EMP_CAT = (SELECT MAX(CATEGORY)FROM EMP_CATEGORIES),SAL_RANGE = (SELECT MAX(SAL_RANGE)FROM EMP_CATEGORIES)WHERE EMP_DEPT = 0020;高效UPDATE EMPSET (EMP_CAT, SAL_RANGE) = (SELECT MAX(CATEGORY), MAX(SAL_RANGE)FROM EMP_CATEGORIES)WHERE EMP_DEPT = 0020;8. 使用表的别名(Alias)当在SQ

22、L语句中连接多个表时，请使用表的别名并把别名前缀于每个Column 上这样可以减少解析的时间并减少那些由 Column歧义引起的语法错误9. 用 EXISTS 替代 IN在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行 联接在这种情况下，使用EXISTS(或NOT EXISTS)通常将提高查询的效率低效SELECT *FROM EMP (基础表)WHERE EMPNO 0AND DEPTNO IN (SELECT DEPTNOFROM DEPTWHERE LOC = MELB)高效SELECT *FROM EMP (基础表)WHERE EMPNO 0AND EXISTS (S

23、ELECT XFROM DEPTWHERE DEPT.DEPTNO = EMP.DEPTNOAND LOC = MELB)10. 用 NOT EXISTS 替代 NOT IN在子查询中，NOT IN子句将执行一个内部的排序和合并无论在哪种情况下，NOT IN都是最低效的，因为它对子查询中的表执行了 一个全表遍历为了避免使用 NOT IN，我们可以把它改写成外连接(Outer Joi ns)或NOT EXISTS例如：SELECT FROM EMPWHERE DEPT_NO NOT IN (SELECT DEPT_NOFROM DEPTWHERE DEPT_CAT = A);为了提高效率改写为高

24、效SELECT FROM EMP A, DEPT BWHERE A.DEPT_NO = B.DEPT(+)AND B.DEPT_NO IS NULLAND B.DEPT_CAT(+) = A最高效SELECT FROM EMP EWHERE NOT EXISTS (SELECT XFROM DEPT DWHERE D.DEPT_NO = E.DEPT_NOAND DEPT_CAT = A);11用表连接替换EXISTS通常来说，采用表连接的方式比 EXISTS更有效率例如：SELECT ENAMEFROM EMP EWHERE EXISTS (SELECT XFROM DEPTWHERE DE

25、PT_NO = E.DEPT_NOAND DEPT_CAT = A);更高效SELECT ENAMEFROM DEPT D, EMP EWHERE E.DEPT_NO = D.DEPT_NOAND DEPT_CAT = A;12. 用 EXISTS 替换 DISTINCT当提交一个包含多表信息(比如部门表和雇员表)的查询时，避免在SELECT 子句中使用DISTINCT，一般可以考虑用EXIST替换例如：低效SELECT DISTINCT DEPT_NO, DEPT_NAME FROM DEPT D, EMP EWHERE D.DEPT_NO = E.DEPT_NO高效SELECT DEPT_

26、NO, DEPT_NAMEFROM DEPT DWHERE EXISTS (SELECT XFROM EMP EWHERE E.DEPT_NO = D.DEPT_NO);EXISTS使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满 足后，立刻返回结果13. 用索引提高效率索引是表的一个概念部分，用来提高检索数据的效率。实际上，ORACLE使用了一个复杂的自平衡B-tree结构通常，通过索引查询数据比全表扫描要快。当ORACLE找出执行查询和Update语句的最佳路径时，ORACLE优化器将使用索引同样，在联结多个表时使用索引也可以提高效率。另一个使用索引的好处是， 它提供了主键(p

27、rimary key)的唯一性验证除了那些LONG或LONG RAW数据类型，你可以索引几乎所有的列通常在大型表中使用索引特别有效，当然，在扫描小表时，使用索引同样能 提高效率虽然使用索引能得到查询效率的提高，但是我们也必须注意到它的代价索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被 修改时，索引本身也会被修改这意味着每条记录的INSERT、DELETE、UPDATE将为此多付出4、5次的 磁盘I/O因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应 时间变慢ORACLE对索引有两种访问模式：1).索弓I唯一扫描(INDEX UNIQUE SCAN)大多数情

28、况下,优化器通过 WHERE子句访问INDEX!-例如：表LODGING 有两个索引：建立在LODGING 列上 的唯一性索引 LODGING_PK 和建立在 MANAGER 列上的非唯一性索引 LODGING$MANAGERSELECT *FROM LODGINGWHERE LODGING = ROSE HILL;在内部，上述SQL将被分成两步执行：首先，LODGING_PK索引将通过索引唯一扫描的方式被访问，获得相对应 的ROWID ;然后通过ROWID访问表的方式执行下一步检索如果被检索返回的列包括在INDEX列中，ORACLE将不执行第二步的处理 （通过ROWID访问表）因为检索数据保

29、存在索引中，单单访问索引就可以完全满足查询结果2）.索弓I范围查询（INDEX RANGE SCAN）适用于两种情况：1.基于唯一性索引的一个范围的检索2.基于非唯一性索引的检索例1SELECT LODGINGFROM LODGINGWHERE LODGING LIKE M%;WHERE子句条件包括一系列值，ORACLE将通过索引范围查询的方式查询LODGING_PK由于索引范围查询将返回一组值，它的效率就要比索引唯一扫描低一些例2SELECT LODGINGFROM LODGINGWHERE MANAGER = BILL GATES;这个SQL的执行分两步，LODGING$MANAGER的索

30、引范围查询（得到所 有符合条件记录的ROWID ），通过ROWID访问表得到LODGING列的值由于LODGING$MANAGER是一个非唯一性的索引，数据库不能对它执行 索引唯一扫描WHERE子句中，如果索引列所对应的值的第一个字符由通配符 (WILDCARD )开始，索引将不被采用SELECT LODGINGFROM LODGINGWHERE MANAGER LIKE % HANMAN;在这种情况下，ORACLE将使用全表扫描14. 避免在索引列上使用计算WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用 全表扫描例如：低效SELECT FROM DEPTWHERE SAL * 12 25000;高效SELECT FROM DEPTWHERE SAL 25000/12;请务必注意，检索中不要对索引列进行处理，如：TRIM，TO_DATE，类型转换等操作，破坏索引，使用