分享
 
 
 

SQL编写规范

王朝mssql·作者佚名  2006-11-24
窄屏简体版  字體: |||超大  

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在解析的过程中,会将'*'依次转换成所有的列名

这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间

4.减少访问数据库的次数

当执行每条SQL语句时,ORACLE在内部执行了许多工作:解析SQL语句,估算索引的利用率,绑定变量,读数据块等等

由此可见,减少访问数据库的次数,就能实际上减少ORACLE的工作量

例如:

以下有三种方法可以检索出雇员号等于0342或0291的职员

方法1 (最低效)

SELECT EMP_NAME, SALARY, GRADE

FROM EMP

WHERE EMP_NO = 342;

SELECT EMP_NAME, SALARY, GRADE

FROM EMP

WHERE EMP_NO = 291;

方法2 (次低效)

DECLARE

CURSOR C1 (E_NO NUMBER) IS

SELECT EMP_NAME,SALARY,GRADE

FROM EMP

WHERE EMP_NO = E_NO;

BEGIN

OPEN 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.GRADE

FROM EMP A, EMP B

WHERE A.EMP_NO = 342

AND B.EMP_NO = 291;

5.使用DECODE函数来减少处理时间

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表

例如:

SELECT COUNT(*), SUM(SAL)

FROM EMP

WHERE DEPT_NO = '0020'

AND ENAME LIKE 'SMITH%';

SELECT COUNT(*), SUM(SAL)

FROM EMP

WHERE DEPT_NO = '0030'

AND ENAME LIKE 'SMITH%';

你可以用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, NULL)) D0030_SAL

FROM EMP

WHERE ENAME LIKE 'SMITH%';

'X'表示任何一个字段

类似的,DECODE函数也可以运用于GROUP BY和ORDER BY子句中

6.用Where子句替换HAVING子句

避免使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤,这个处理需要排序、统计等操作

如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销

例如:

低效

SELECT REGION, AVG(LOG_SIZE)

FROM LOCATION

GROUP BY REGION

HAVING REGION REGION != 'SYDNEY'

AND REGION != 'PERTH'

高效

SELECT REGION, AVG(LOG_SIZE)

FROM LOCATION

WHERE REGION REGION != 'SYDNEY'

AND REGION != 'PERTH'

GROUP BY REGI

[1] [2] 下一页

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有