书写格式
示例代码
存储过程SQL文书写格式例
select
cdealerCode
round(sum(csubmitSubletAmountDLR + csubmitPartsAmountDLR + csubmitLaborAmountDLR) / count(*) ) as avg
decode(null x xx CNY)
from (
select
adealerCode
asubmitSubletAmountDLR
asubmitPartsAmountDLR
asubmitLaborAmountDLR
from SRV_TWC_F a
where (to_char(aORIGSUBMITTIME yyyy/mm/dd) >= Date Range(start)
and to_char(aORIGSUBMITTIME yyyy/mm/dd) <= Date Range(end)
and nvl(adeleteflag ) <> )
union all
select
bdealerCode
bsubmitSubletAmountDLR
bsubmitPartsAmountDLR
bsubmitLaborAmountDLR
from SRV_TWCHistory_F b
where (to_char(bORIGSUBMITTIME yyyy/mm/dd) >= Date Range(start)
and to_char(bORIGSUBMITTIMEyyyy/mm/dd) <= Date Range(end)
and nvl(bdeleteflag) <> )
) c
group by cdealerCode
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 ( + draftInputDetaildealerCode +
+ + draftInputDetailrequestsequece +
+ sysdate
+
+ + frameNO +
+ requestMoney +
+ remainMoney +
+
+
+ + draftStructemployeeCode +
+ sysdate
+ + draftInputDetailhonorCheckNo +
+ index + );
)缩进
对于存储过程文件缩进为个空格
对于Java source里的SQL字符串不可有缩进即每一行字符串不可以空格开头
)换行
>Select/From/Where/Order by/Group by等子句必须另其一行写
>Select子句内容如果只有一项与Select同行写
>Select子句内容如果多于一项每一项单独占一行在对应Select的基础上向右缩进个空格（Java source无缩进）
>From子句内容如果只有一项与From同行写
>From子句内容如果多于一项每一项单独占一行在对应From的基础上向右缩进个空格（Java source无缩进）
>Where子句的条件如果有多项每一个条件占一行以AND开头且无缩进
>(Update)Set子句内容每一项单独占一行无缩进
>Insert子句内容每个表字段单独占一行无缩进values每一项单独占一行无缩进
>SQL文中间不允许出现空行
>Java source里单引号必须跟所属的SQL子句处在同一行连接符（+）必须在行首
)空格
>SQL内算数运算符逻辑运算符连接的两个元素之间必须用空格分隔
>逗号之后必须接一个空格
>关键字保留字和左括号之间必须有一个空格
不等于统一使用<>
Oracle认为!=和<>是等价的都代表不等于的意义为了统一不等于一律使用<>表示
使用表的别名
数据库查询必须使用表的别名
SQL文对表字段扩展的兼容性
在Java source里使用Select *时严禁通过getString()的形式得到查询结果必须使用getString(字段名)的形式
使用Insert时必须指定插入的字段名严禁不指定字段名直接插入values
减少子查询的使用
子查询除了可读性差之外还在一定程度上影响了SQL运行效率
请尽量减少使用子查询的使用用其他效率更高可读性更好的方式替代
适当添加索引以提高查询效率
适当添加索引可以大幅度的提高检索速度
请参看ORACLE SQL性能优化系列
对数据库表操作的特殊要求
本项目对数据库表的操作还有以下特殊要求
)以逻辑删除替代物理删除
注意现在数据库表中数据没有物理删除只有逻辑删除
以deleteflag字段作为删除标志deleteflag=代表此记录被逻辑删除因此在查询数据时必须考虑deleteflag的因素
deleteflag的标准查询条件NVL(deleteflag ) <>
)增加记录状态字段
数据库中的每张表基本都有以下字段DELETEFLAGUPDATECOUNTCREDATECREUSERUPDATETIMEUPDATEUSER
要注意在对标进行操作时必须考虑以下字段
插入一条记录时要置DELETEFLAG= UPDATECOUNT= CREDATE=sysdate CREUSER=登录User
查询一条记录时要考虑DELETEFLAG如果有可能对此记录作更新时还要取得UPDATECOUNT作同步检查
修改一条记录时要置UPDATETIME=sysdate UPDATEUSER=登录User UPDATECOUNT=(UPDATECOUNT+) mod
删除一条记录时要置DELETEFLAG=
)历史表
数据库里部分表还存在相应的历史表比如srv_twc_f和srv_twchistory_f
在查询数据时除了检索所在表之外还必须检索相应的历史表对二者的结果做Union（或Union All）
用执行计划分析SQL性能
EXPLAIN PLAN是一个很好的分析SQL语句的工具它可以在不执行SQL的情况下分析语句
通过分析我们就可以知道ORACLE是怎样连接表使用什么方式扫描表（索引扫描或全表扫描）以及使用到的索引名称
按照从里到外从上到下的次序解读分析的结果
EXPLAIN PLAN的分析结果是用缩进的格式排列的最内部的操作将最先被解读如果两个操作处于同一层中带有最小操作号的将首先被执行
目前许多第三方的工具如PLSQL Developer和TOAD等都提供了极其方便的EXPLAIN PLAN工具
PG需要将自己添加的查询SQL文记入log然后在EXPLAIN PLAN中进行分析尽量减少全表扫描
ORACLE SQL性能优化系列
选择最有效率的表名顺序(只在基于规则的优化器中有效)
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名因此FROM子句中写在最后的表（基础表driving table）将被最先处理
在FROM子句中包含多个表的情况下必须选择记录条数最少的表作为基础表
当ORACLE处理多个表时会运用排序及合并的方式连接它们
首先扫描第一个表（FROM子句中最后的那个表)并对记录进行排序
然后扫描第二个表（FROM子句中最后第二个表)
最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并
例如:
表 TAB 条记录
表 TAB 条记录
选择TAB作为基础表 (最好的方法)
select count(*) from tabtab 执行时间秒
选择TAB作为基础表 (不佳的方法)
select count(*) from tabtab 执行时间秒
如果有个以上的表连接查询那就需要选择交叉表（intersection table）作为基础表交叉表是指那个被其他表所引用的表
例如:
EMP表描述了LOCATION表和CATEGORY表的交集
SELECT *
FROM LOCATION L
CATEGORY C
EMP E
WHERE EEMP_NO BETWEEN AND
AND ECAT_NO = CCAT_NO
AND ELOCN = LLOCN
将比下列SQL更有效率
SELECT *
FROM EMP E
LOCATION L
CATEGORY C
WHERE ECAT_NO = CCAT_NO
AND ELOCN = LLOCN
AND EEMP_NO BETWEEN AND
WHERE子句中的连接顺序
ORACLE采用自下而上的顺序解析WHERE子句
根据这个原理表之间的连接必须写在其他WHERE条件之前那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾
例如
(低效执行时间秒)
SELECT *
FROM EMP E
WHERE SAL >
AND JOB = MANAGER
AND < (SELECT COUNT(*) FROM EMP WHERE MGR=EEMPNO);
(高效执行时间秒)
SELECT *
FROM EMP E
WHERE < (SELECT COUNT(*) FROM EMP WHERE MGR=EEMPNO)
AND SAL >
AND JOB = MANAGER;
SELECT子句中避免使用*
当你想在SELECT子句中列出所有的COLUMN时使用动态SQL列引用*是一个方便的方法不幸的是这是一个非常低效的方法
实际上ORACLE在解析的过程中会将*依次转换成所有的列名
这个工作是通过查询数据字典完
