东华软件SQL代码开发规范文档.docx
《东华软件SQL代码开发规范文档.docx》由会员分享，可在线阅读，更多相关《东华软件SQL代码开发规范文档.docx（60页珍藏版）》请在冰点文库上搜索。
东华软件SQL代码开发规范文档
SQL编码规范
（V1.00）
文档信息
文档名称：
SQL编码规范
电子文档：
版本号：
1.00
密级：
保密
文档编号：
编写人：
黄业明
日期：
2007-03-27
校对人：
日期：
审核人：
日期：
批准人：
日期：
更改记录
更改
序号
更改原因
更改
页码
更改前版本号
更改后版本号
更改人
生效日期
备注
1
注释规范
1.1.一般性注释
1.1.1.创建每一数据库对象时都要加上COMMENTON注释，以说明该对象的功能和用途；建表时，对某些数据列也要加上COMMENTON注释，以说明该列和/或列取值的含义。
1.1.2.注释语法包含两种情况：
单行注释、多行注释
单行注释：
注释前有两个连字符（--）。
多行注释：
符号/*和*/之间的内容为注释内容。
1.2.函数文本注释
1.2.1.在每一个块和过程（存储过程、函数、包、触发器、视图等）的开头放置注释
/*****************************************************************
*name:
--函数名
*function:
--函数功能
*input:
--输入参数
*output:
--输出参数
*author:
--作者
*CreateDate:
--创建时间
*UpdateDate:
--函数更改信息（包括作者、时间、更改内容等）
******************************************************************/
CREATE[ORREPLACE]PROCEDUREdfsp_xxx
…
1.2.2.对传入参数的含义进行说明。
如果取值范围确定，也一并说明。
取值有特定含义的变量（如boolean类型变量），给出每个值的含义。
1.2.3.在每一个变量声明的旁边添加注释。
说明该变量要用作什么。
通常使用单行注释即可，例如
login_idVARCHAR2（32）NOTNULL,--会员标识
1.2.4.对存储过程的任何修改，都需要在注释最后添加修改人、修改日期及修改原因等信息
1.2.5.避免在一行代码或表达式的中间插入注释
1.2.6.在程序块的结束行右方加注释，以表明程序块结束
1.2.7.在块的每个主要部分之前添加注释
在块的每个主要部分之前增加注释，解释下—组语句目的，说明该段语句及算法的目的以及要得到的结果，但不要对其细节进行过多的描述。
1.2.8.对程序分支必须书写注释
2.排版格式
2.1.缩进
2.1.1.存储过程中的SQL语句
●低级别语句在高级别语句后的，缩进4个空格。
●同一语句不同部分的缩进，如果为子句，则为4个空格，如果与上一行某部分有密切联系的，则缩至与其对齐。
2.1.2.对于Pro*C,Java等代码里的SQL字符串,每一行字符串不可以空格开头。
2.2.换行
2.2.1.一行最长不能超过80字符
2.2.2.SELECT/FROM/WHERE/ORDERBY/GROUPBY等子句应独占一行。
2.2.3.SQL语句中间不允许出现空行
2.2.4.SELECT子句内容如果只有一项，应与SELECT同占一行。
2.2.5.SELECT子句内容如果多于一项，每一项都应独占一行，并在对应SELECT的基础上向右缩进8个空格。
2.2.6.FROM子句内容如果只有一项，应与FROM同占一行。
2.2.7.FROM子句内容如果多于一项，每一项都应独占一行，并在对应FROM的基础上向右缩进4个空格。
2.2.8.WHERE子句内容如果只有一项，应与WHERE同占一行。
2.2.9.WHERE子句的条件如果有多项，每一个条件应独占一行，并以AND开头，并在对应WHERE的基础上向右缩进4个空格。
2.2.9.1.进1个Tab或者4个字符。
示例：
//SELECT语句书写的正确示例
SELECTbill_no,
FROMmft_list
WHEREmanifest_no=‘000000000000000007’;
SELECT
list.manifest_no,
list.list_no,
stat.list_stat
FROM
mft_listlist,
list_statstat
WHERE
list.manifest_no=stat.manifest_no
ANDstat.stat!
=2;
2.2.10.（UPDATE）SET子句内容如果有一项，应与SET同占一行。
2.2.11.（UPDATE）SET子句内容如果有多项，每一项应独占一行，并在对应SET的基础上向右缩进4个空格。
示例：
//UPDATE语句书写的正确示例
UPDATElist_stat
SET
list_stat='2',
parent='0'
WHERElist_no='bill010';
2.2.12.INSERT子句左/右括号以及每个表字段应独占一行，其中括号无缩进，表字段在对应括号的基础上向右缩进4个空格。
2.2.13.VALUES子句左/右括号以及每一项的值应独占一行，其中括号无缩进，每一项的值在对应括号的基础上向右缩进4个字符。
示例：
//INSERT语句书写的正确示例
INSERTINTOlist_stat
（
list_no,
list_stat,
parent,
manifest_no,
div_flag
）
VALUES
（
'bill020',
'1',
'0',
'000000000000007807',
'0'
）;
2.3.空格
2.3.1.SQL内算数运算符、逻辑运算符连接的两个元素之间必须用空格分隔
2.3.2.逗号之后必须接一个空格
2.3.3.关键字、保留字和左括号之间必须有一个空格
2.4.大小写
2.4.1.SQL语句中出现的所有表名、表别名、字段名、序列等数据库对象都应小写
2.4.2.SQL语句中出现的系统保留字、内置函数名、SQL保留字、绑定变量等都应大写。
2.5.对齐
2.5.1.变量初始化赋值时，各变量列对齐，赋值号列对齐，被赋值列对齐；
3.变量命名规则
3.1.输入变量
in+变量含义的英文单词，单词的首字母大写，单词之间用”_”分割。
3.2.输出变量
out+变量含义的英文单词，单词的首字母大写，单词之间用”_”分割。
3.3.内部变量
v+变量含义的英文单词，单词的首字母大写，单词之间用”_”分割。
3.4.游标命名
CURSOR_表名
4.BC命名规范
4.1.查询类BC
命名规则：
cQ[表名缩写]By[查询条件]
例如：
cQCustInfoByCustId
cQUserInfoByCustId
对于查询条件是多个的，可选择一个具有代表性的字段作为查询条件，或者新定义一个新的单词
例如根据客户类型、证件类型、证件号码查询客户信息的BC，命名为cQCustInfoByIccid
4.2.删除类BC
根据主键删除的，命名规则：
cD[表名缩写]
不根据主键删除的，命名规则：
cD[表名缩写]By[删除条件]
例如：
根据用户标识删除用户扩展属性，用户标识是非主键，BC命名为cDUserAddInfoByIdNo
根据主键（用户标识+属性代码）删除的，BC命名为cDUserAddInfo
4.3.修改类BC
根据主键修改的，命名规则：
cU[字段信息]Of[表名缩写]
不根据主键修改的，命名规则：
cU[字段信息]Of[表名缩写]By[修改条件]
例如：
根据群成员标识修改群成员的状态，BC命名为cUStateOfUserGroupMbrInfo
根据群标识修改群成员表中其所有成员的状态，BC命名为cUStateOfUserGroupMbrInfoByGrpId
4.4.插入类BC
插入本表，命名规则：
cI[表名缩写]
插入历史表：
1、插入本表时，插入历史表，命名规则：
cI[历史表表名缩写]
2、删除、修改本表时，将数据备份到历史
i、根据主键删除修改时备份入历史，命名规则为：
cI[历史表表名缩写]Bak
ii、不根据主键删除修改时备份入历史，命名规则为：
cI[历史表表名缩写]BakBy[条件]
例如：
插入用户扩展信息表BC命名为cIUserAddInfo
插入用户扩展信息表时插用户扩展信息历史表BC命名为cIUserAddInfoHis
根据（用户标识+属性代码）删除用户扩展信息之前，将预删除记录备份到历史BC命名为cIUserAddInfoHisBak
根据用户标识删除用户扩展信息之前，将预删除记录备份到历史BC命名为cIUserAddInfoHisBakByIdNo
4.5.多表联合查询类BC
命名规则：
cGet[获取信息描述]By[条件]
例如：
根据用户标识联合查询用户信息表和客户信息表，获取客户基本信息BC命名为cGetCustInfoByIdNo
5.编码规范
5.1.不等于统一使用"<>"
Oracle认为"!
="和"<>"是等价的，都代表不等于的意义。
为了统一，不等于一律使用"<>"表示。
5.2.使用表的别名
多表连接时，应为每个表使用别名，别名要简短最好一个字母，且能代表一定意义，所有被引用列要加上表的别名。
5.3.使用SELECT语句时，必须指出列名
不要使用列的序号或者用“*”替代所有列名。
5.4.使用INSERT语句时，必须指定插入的字段名。
5.5.减少子查询的使用
子查询除了可读性差之外，还在一定程度上影响了SQL运行效率.请尽量减少子查询的使用，采用其他效率更高、可读性更好的方式替代
5.6.适当添加索引以提高查询效率
适当添加索引可以大幅度的提高检索速度（具体的优化方法见附录A性能优化部分）
5.7.不要在WHERE字句中对索引列施以函数
5.8.不要使用数据库的类型自动转换功能，使用显式的类型转换
5.9.应使用变量绑定实现SQL语句共享，避免使用硬编码
5.9.1.不允许直接拼写SQL语句，而要使用绑定变量。
例如：
例1：
此种写法不允许：
init（dynStmt）;
sprintf（dynStmt,"INSERTINTOwChg%s（id_no,total_date,login_accept,sm_code,belong_code,phone_no,org_code,login_no,op_code,op_time,machine_code,cash_pay,check_pay,sim_fee,machine_fee,innet_fee,choice_fee,other_fee,hand_fee,deposit,back_flag,encrypt_fee,system_note,op_note）VALUES（%ld,TO_NUMBER（%s）,%ld,'%s','%s','%s','%s','%s','%s',TO_DATE（'%s','yyyymmddhh24:
mi:
ss'）,'zz',0,0,0,0,0,0,0,0,0,'0',0,'%s','%s'）",YearMonth,idNo,totalDate,LoginAccept,smCode,belongCode,phoneNo,orgCode,loginNo,opCode,opTime,systemNote,systemNote）;
EXECSQLPREPAREins_stmtFROM:
dynStmt;
EXECSQLEXECUTEins_stmt;
例2：
可以使用：
init（dynStmt）;
sprintf（dynStmt,"INSERTINTOwLoginOpr%s"
"（"
"total_date,login_accept,op_code,pay_type,pay_money,"
"sm_code,id_no,phone_no,org_code,loin_no,op_time,op_note,ip_addr"
"）"
"VALUES"
"（"
"TO_NUMBER（:
v1）,:
v2,:
v3,:
v4,:
v5,"
":
v6,:
v7,:
v8,:
v9,:
v10,TO_DATE（:
v11,'yyyymmddhh24:
mi:
ss'）,:
v12,:
v13"
"）",
YearMonth）;
EXECSQLPREPAREprepare1FROM:
dynStmt;
EXECSQLEXECUTEprepare1USING:
totalDate,:
LoginAccept,:
opCode,:
payType,:
handFee,
:
smCode,:
idNo,:
phoneNo,:
orgCode,:
loginNo,:
opTime,:
opNote,:
ipAddress;
例3：
可以使用：
EXECSQLINSERTINTOdCustMsgAdd
（
id_no,busi_type,user_type,field_code,field_order,field_value,
other_value
）
VALUES
（
:
idNo,:
busiType,:
userType,:
fieldCode,:
fieldOrder,:
fieldValue,
:
otherValue
）;
5.9.2.执行相同操作的SQL语句必须使用相同名字的绑定变量。
例如：
第一组的两个SQL语句，绑定变量是相同的，而第二组中的两个语句绑定变量不同，即使赋于不同的绑定变量相同的值也不能使这两个SQL语句相同，达不到共享SQL语句目的。
a）第一组
selectpin,namefrompeoplewherepin=:
blk1.pin;
selectpin,namefrompeoplewherepin=:
blk1.pin;
b）第二组
selectpin,namefrompeoplewherepin=:
blk1.ot_ind;
selectpin,namefrompeoplewherepin=:
blk1.ov_ind;
5.10.用执行计划分析SQL性能
EXPLAINPLAN是一个很好的分析SQL语句的工具，它可以在不执行SQL的情况下分析语句.通过分析，我们就可以知道ORACLE是怎样连接表，使用什么方式扫描表（索引扫描或全表扫描），以及使用到的索引名称,按照从里到外，从上到下的次序解读分析的结果。
EXPLAINPLAN的分析结果是用缩进的格式排列的，最内部的操作将最先被解读，如果两个操作处于同一层中，带有最小操作号的将首先被执行。
目前许多第三方的工具如PLSQLDeveloper和TOAD等都提供了极其方便的EXPLAINPLAN工具。
附录A：
OracleSQL性能优化
A.1选用适合的ORACLE优化器
●ORACLE的优化器共有3种:
✧RULE（基于规则）
✧COST（基于成本）
✧CHOOSE（选择性）
设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS.你当然也可以在SQL句级或是会话（session）级对其进行覆盖.
●为了使用基于成本的优化器（CBO,Cost-BasedOptimizer）,你必须经常运行analyze命令,以增加数据库中的对象统计信息（objectstatistics）的准确性.
如果数据库的优化器模式设置为选择性（CHOOSE）,那么实际的优化器模式将和是否运行过analyze命令有关.如果table已经被analyze过,优化器模式将自动成为CBO,反之,数据库将采用RULE形式的优化器.
●在缺省情况下,ORACLE采用CHOOSE优化器,为了避免那些不必要的全表扫描（fulltablescan）,你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器
A.2访问TABLE的方式
ORACLE采用两种访问表中记录的方式:
●全表扫描
全表扫描就是顺序地访问表中每条记录.ORACLE采用一次读入多个数据块（databaseblock）的方式优化全表扫描.
●通过ROWID访问表
你可以采用基于ROWID的访问方式情况,提高访问表的效率,ROWID包含了表中记录的物理位置信息.ORACLE采用索引（INDEX）实现了数据和存放数据的物理位置（ROWID）之间的联系.通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.
A.3共享SQL语句
为了不重复解析相同的SQL语句,在第一次解析之后,ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA（systemglobalarea）的共享池（sharedbufferpool）中的内存可以被所有的数据库用户共享.因此,当你执行一个SQL语句（有时被称为一个游标）时,如果它
和之前的执行过的语句完全相同,ORACLE就能很快获得已经被解析的语句以及最好的
执行路径.
ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用.
可惜的是ORACLE只对简单的表提供高速缓冲（cachebuffering）,这个功能并不适用于多表连接查询.
数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了.
当你向ORACLE提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句.
这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须
完全相同（包括空格,换行等）.
共享的语句必须满足三个条件:
●字符级的比较:
当前被执行的语句和共享池中的语句必须完全相同
例如:
SELECT*FROMEMP;
和下列每一个都不同
SELECT*fromEMP;
Select*FromEmp;
SELECT*FROMEMP;
●两个语句所指的对象必须完全相同
例如:
用户对象名如何访问
Jacksal_limitprivatesynonym
JackWork_citypublicsynonym
JackPlant_detailpublicsynonym
Jillsal_limitprivatesynonym
JillWork_citypublicsynonym
JillPlant_detailtableowner
考虑一下下列SQL语句能否在这两个用户（Jack,Jill）之间共享?
✧SQL:
SELECTMAX（sal_cap）FROMsal_limit;
✧能否共享:
不能
✧原因:
每个用户都有一个privatesynonym-sal_limit,它们是不同的对象
✧SQL:
SELECTCOUNT（0）FROMwork_cityWHEREsdescLIKE'NEW%';
✧能否共享:
不能
✧原因:
两个用户访问相同的对象publicsynonym-work_city
✧SQL:
SELECTa.sdesc,b.locationFROMwork_citya,plant_detailb
WHEREa.city_id=b.city_id
✧能否共享:
不能
✧原因:
用户jack通过publicsynonym访问plant_detail而jill是表的所有者,对象不同.
●两个SQL语句中必须使用相同的名字的绑定变量（bindvariables）
例如：
第一组的两个SQL语句是相同的（可以共享）,而第二组中的两个语句是不同的（即使在运行时,赋于不同的绑定变量相同的值）
a）第一组
selectpin,namefrompeoplewherepin=:
blk1.pin;
selectpin,namefrompeoplewherepin=:
blk1.pin;
b）第二组
selectpin,namefrompeoplewherepin=:
blk1.ot_ind;
selectpin,namefrompeoplewherepin=:
blk1.ov_ind;
A.4选择最有效率的表名顺序（只在基于规则的优化器中有效）
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名.
因此FROM子句中写在最后的表（基础表drivingtable）将被最先处理.在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.
当ORACLE处理多个表时,会运用排序及合并的方式连接它们.首先,扫描第一个表（FROM子句中最后的那个表）并对记录进行派序,然后扫描第二个表（FROM子句中最后第二个表）,最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.
例如:
表TAB116,384条记录
表TAB21条记录
●选择TAB2作为基础表（最好的方法）
selectcount（*）fromtab1,tab2执行时间0.96秒
●选择TAB2作为基础表（不佳的方法）
selectcount（*）fromtab2,tab1执行时间26.09秒
●如果有3个以上的表连接查询,那就需要选择交叉表（intersectiontable）作为基础表,交叉表是指那个被其他表所引用的表.
例如:
EMP表描述了LOCATION表和CATEGORY表的交集.
SELECT*
FROMLOCATIONL,
CATEG
升级会员 