Oracle编程风格.docx
- 文档编号:10845933
- 上传时间:2023-02-23
- 格式:DOCX
- 页数:43
- 大小:40.76KB
Oracle编程风格.docx
《Oracle编程风格.docx》由会员分享,可在线阅读,更多相关《Oracle编程风格.docx(43页珍藏版)》请在冰豆网上搜索。
Oracle编程风格
Oracle编程规范
文档编号:
20050201_01
版本号:
V1.0
报告人:
吴绵彪
报告日期:
修改情况
版本号
修改人
修改日期
审阅人
审阅日期
V1.0
吴绵彪
一.基础环境
1.基础:
HOST=192.168.0.101
PORT=1521
SERVICE_NAME=ora9201
2.类型:
数字型:
number(n,m)
定长字符型:
char(n)
变长字符型:
varchar2(20)
日期型:
char()–‘YYYYMMDDHH24MiSe’
布尔值:
用CHAR
(1)代替-‘0’成功,非0不成功
二、命名约定
1.命名只能使用英文字母,数字和下划线
2.命名富有意义英文词汇,除个别通用的(见列表),要避免使用缩写),多个单词组成的,中间以下划线分割;
3.避免使用Oracle的保留字(保留字见付表);
4.名表之间相关列名尽量同名;
5.详细命名如下:
字段:
f_nId
f_chDesc
f_vchDesc
表:
Tbase_SysMyTableTcity_SysMyTable
Tbase_SvcMyTableTcity_SvcMyTable
存储过程:
Pbase_SysMyProcPcity_SysMyProc
Pbase_SvcMyPorcPbase_SvcMyProc
索引:
Idx_SvcMyTableIdDesc
视图:
Vbase_SysMyViewVbase_SvcMyView
Vcity_SysMyViewVcity_svcMyView
触发器:
tger_SysMyTable_InsertUpdateDelete
表空间:
函数:
约束:
序列:
包:
数据库SID:
Ora9201(92是oracle的版本,01是我们的第一个数据库)
三、注释说明
对较为复杂的sql语句加上注释,说明算法、功能。
注释风格:
注释单独成行、放在语句前面。
(1) 应对不易理解的分支条件表达式加注释;
(2) 对重要的计算应说明其功能;
(3) 过长的函数实现,应将其语句按实现的功能分段加以概括性说明;
(4) 每条SQL语句均应有注释说明(表名、字段名)。
(5) 常量及变量注释时,应注释被保存值的含义(必须),合法取值的范围(可选)
(6) 可采用单行/多行注释。
(-- 或/**/方式)
三、SQL语句的缩进风格
(1) 一行有多列,超过80个字符时,基于列对齐原则,采用下行缩进
(2) where子句书写时,每个条件占一行,语句令起一行时,以保留字或者连接符开始,连接符右对齐。
(3) tab=4space
四、断行
•一行最长不能超过80字符
•同一语句不同字句之间
•逗号以后空格
•其他分割符前空格
SELECToffer_name,
offer_countasoffer_category,
id
FROMcategory
WHEREsuper_category_id_1=0;
五、大小写与其他
sql语句的所有表名、字段名全部小写
连接符or、in、and、以及=、<=、>=等前后加上一个空格。
六、列类型的选择
七、主键选择
八、列长度的选择
应当根据实际需要选择列长度。
有对应web页面的,与页面上对应列长度一致。
对数据的验证除数据库端实现外还要尽可能在表现层控制。
九、使用SQL语句的约定
1.尽量避免在循环中使用SQL语句。
2.避免在WHERE字句中对列施以函数:
SELECT *
FROM service_promotion
WHERETO_CHAR(gmt_modified,’yyyy-mm-dd’)
=‘20001-09-01’;
而应使用:
SELECT*
FROMservice_promotion
WHEREgmt_modified>=TO_DATE(‘20050221’,’yyyymmdd’)AND
gmt_modified 3.避免使用数据库的类型自动转换功能: SELECT* FROMcategory WHEREid=‘123’;--id’stypeisnumber 4.避免无效的连接: SELECTcount(*) FROMoffera,count_by_emailb WHEREa.email(+)=b.email; 5.连接(join)时要使用别名: SELECTa.*,b.offer_count(*) FROMoffera,count_by_emailb WHEREa.email(+)=b.email; 6.取TABLE的META信息: SELECT* FROMtable_name WHERErowidisnull(orrownum=1orpk=impossible_value); 可考虑使用: SELECT* FROMtable_name WHERE1=0; 7.操作符优化 1)IN操作符 用IN写出来的SQL的优点是比较容易写及清晰易懂,这比较适合现代软件开发的风格。 但是用IN的SQL性能总是比较低的,从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别: ORACLE试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则直接采用多个表的连接方式查询。 由此可见用IN的SQL至少多了一个转换的过程。 一般的SQL都可以转换成功,但对于含有分组统计等方面的SQL就不能转换了。 推荐方案: 在业务密集的SQL当中尽量不采用IN操作符。 2)NOTIN操作符 此操作是强列推荐不使用的,因为它不能应用表的索引。 推荐方案: 用NOTEXISTS或(外连接+判断为空)方案代替 3)<>操作符(不等于) 不等于操作符是永远不会用到索引的,因此对它的处理只会产生全表扫描。 推荐方案: 用其它相同功能的操作运算代替,如 a<>0改为a>0ora<0 a<>’’改为a>’’ 4)ISNULL或ISNOTNULL操作(判断字段是否为空) 判断字段是否为空一般是不会应用索引的,因为B树索引是不索引空值的。 推荐方案: 用其它相同功能的操作运算代替,如 aisnotnull改为a>0或a>’’等。 不允许字段为空,而用一个缺省值代替空值,如业扩申请中状态字段不允许为空,缺省为申请。 建立位图索引(有分区的表不能建,位图索引比较难控制,如字段值太多索引会使性能下降,多人更新操作会增加数据块锁的现象) 5)>及<操作符(大于或小于操作符) 大于或小于操作符一般情况下是不用调整的,因为它有索引就会采用索引查找,但有的情况下可以对它进行优化,如一个表有100万记录,一个数值型字段A,30万记录的A=0,30万记录的A=1,39万记录的A=2,1万记录的A=3。 那么执行A>2与A>=3的效果就有很大的区别了,因为A>2时ORACLE会先找出为2的记录索引再进行比较,而A>=3时ORACLE则直接找到=3的记录索引。 6)LIKE操作符 LIKE操作符可以应用通配符查询,里面的通配符组合可能达到几乎是任意的查询,但是如果用得不好则会产生性能上的问题,如LIKE‘%5400%’这种查询不会引用索引,而LIKE‘X5400%’则会引用范围索引。 一个实际例子: 用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号YY_BHLIKE‘%5400%’这个条件会产生全表扫描,如果改成YY_BHLIKE’X5400%’ORYY_BHLIKE’B5400%’则会利用YY_BH的索引进行两个范围的查询,性能肯定大大提高。 7)UNION操作符 UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。 实际大部分应用中是不会产生重复的记录,最常见的是过程表与历史表UNION。 如: select*fromgc_dfys union select*fromls_jg_dfys 这个SQL在运行时先取出两个表的结果,再用排序空间进行排序删除重复的记录,最后返回结果集,如果表数据量大的话可能会导致用磁盘进行排序。 推荐方案: 采用UNIONALL操作符替代UNION,因为UNIONALL操作只是简单的将两个结果合并后就返回。 select*fromgc_dfys unionall select*fromls_jg_dfys 8)SQL书写的影响 同一功能同一性能不同写法SQL的影响 如一个SQL在A程序员写的为 Select*fromzl_yhjbqk B程序员写的为 Select*fromdlyx.zl_yhjbqk(带表所有者的前缀) C程序员写的为 Select*fromDLYX.ZLYHJBQK(大写表名) D程序员写的为 Select*fromDLYX.ZLYHJBQK(中间多了空格) 以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的,但是从ORACLE共享内存SGA的原理,可以得出ORACLE对每个SQL都会对其进行一次分析,并且占用共享内存,如果将SQL的字符串及格式写得完全相同则ORACLE只会分析一次,共享内存也只会留下一次的分析结果,这不仅可以减少分析SQL的时间,而且可以减少共享内存重复的信息,ORACLE也可以准确统计SQL的执行频率。 9)WHERE后面的条件顺序影响 WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响,如 Select*fromzl_yhjbqkwheredy_dj='1KV以下'andxh_bz=1 Select*fromzl_yhjbqkwherexh_bz=1anddy_dj='1KV以下' 以上两个SQL中dy_dj及xh_bz两个字段都没进行索引,所以执行的时候都是全表扫描,第一条SQL的dy_dj='1KV以下'条件在记录集内比率为99%,而xh_bz=1的比率只为0.5%,在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较,而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较,以此可以得出第二条SQL的CPU占用率明显比第一条低。 10)查询表顺序的影响 在FROM后面的表中的列表顺序会对SQL执行性能影响,在没有索引及ORACLE没有对表进行统计分析的情况下ORACLE会按表出现的顺序进行链接,由此因为表的顺序不对会产生十分耗服务器资源的数据交叉。 (注: 如果对表进行了统计分析,ORACLE会自动先进小表的链接,再进行大表的链接) SQL语句索引的利用 对操作符的优化(见上节) 对条件字段的一些优化 采用函数处理的字段不能利用索引,如: substr(hbs_bh,1,4)=’5400’,优化处理: hbs_bhlike‘5400%’ trunc(sk_rq)=trunc(sysdate),优化处理: sk_rq>=trunc(sysdate)andsk_rq 进行了显式或隐式的运算的字段不能进行索引,如: ss_df+20>50,优化处理: ss_df>30 ‘X’||hbs_bh>’X5400021452’,优化处理: hbs_bh>’5400021542’ sk_rq+5=sysdate,优化处理: sk_rq=sysdate-5 hbs_bh=5401002554,优化处理: hbs_bh=’5401002554’,注: 此条件对hbs_bh进行隐式的to_number转换,因为hbs_bh字段是字符型。 条件内包括了多个本表的字段运算时不能进行索引,如: ys_df>cx_df,无法进行优化 qc_bh||kh_bh=’5400250000’,优化处理: qc_bh=’5400’andkh_bh=’250000’ 应用ORACLE的HINT(提示)处理 提示处理是在ORACLE产生的SQL分析执行路径不满意的情况下要用到的。 它可以对SQL进行以下方面的提示 目标方面的提示: COST(按成本优化) RULE(按规则优化) CHOOSE(缺省)(ORACLE自动选择成本或规则进行优化) ALL_ROWS(所有的行尽快返回) FIRST_ROWS(第一行数据尽快返回) 执行方法的提示: USE_NL(使用NESTEDLOOPS方式联合) USE_MERGE(使用MERGEJOIN方式联合) USE_HASH(使用HASHJOIN方式联合) 索引提示: INDEX(TABLEINDEX)(使用提示的表索引进行查询) 其它高级提示(如并行处理等等) ORACLE的提示功能是比较强的功能,也是比较复杂的应用,并且提示只是给ORACLE执行的一个建议,有时如果出于成本方面的考虑ORACLE也可能不会按提示进行。 根据实践应用,一般不建议开发人员应用ORACLE提示,因为各个数据库及服务器性能情况不一样,很可能一个地方性能提升了,但另一个地方却下降了,ORACLE在SQL执行分析方面已经比较成熟,如果分析执行的路径不对首先应在数据库结构(主要是索引)、服务器当前性能(共享内存、磁盘文件碎片)、数据库对象(表、索引)统计信息是否正确这几方面分析。 十、Sql常用脚本 十一、关于表重复属性的定义 不能有多择一的重复属性列; 对于能多选的重复属性列,如果不做查询列并且重复次数较多,应使用ID_VARRAY代替,如要用作查询列,重复次数多的,应另建一表,次数少的且列值为二择一时,应使用NUMBER类型,加位操作。 十二、数据修改约定 发现Production环境中数据有误,需要改正的,应在RA TERMINAL上提交数据更新表单,由数据库操作员在当日17时前修改完毕。 数据库更程序更新的结构变动及数据更新,类似处理。 提交SQTT测试需要准备数据或更新结构的,暂时先写入测试请求中,由SQTT同志MAIL(注明测试请求表单链接即可)通过数据库操作员修改数据库。 待内部网TEAM增加新的表单后,测试请求分开提交。 十三、数据库设计流程 在新项目设计阶段会议,应有DBA参加。 在SCHEMA确定以后方可开始编码。 十四、书写优化性能建议 1、避免嵌套连接。 例如: A=BandB=CandC=D 2、where条件中尽量减少使用常量比较,改用主机变量 3、系统可能选择基于规则的优化器,所以将结果集返回数据量小的表作为驱动表(from后边最后一个表)。 4、大量的排序操作影响系统性能,所以尽量减少orderby和groupby排序操作。 如必须使用排序操作,请遵循如下规则: (1) 排序尽量建立在有索引的列上。 (2) 如结果集不需唯一,使用unionall代替union。 5、索引的使用。 (1) 尽量避免对索引列进行计算。 如对索引列计算较多,请提请系统管理员建立函数索引。 (2) 尽量注意比较值与索引列数据类型的一致性。 (3) 对于复合索引,SQL语句必须使用主索引列 (4) 索引中,尽量避免使用NULL。 (5) 对于索引的比较,尽量避免使用NOT=(! =) (6) 查询列和排序列与索引列次序保持一致 6、尽量避免相同语句由于书写格式的不同,而导致多次语法分析。 7、尽量使用共享的SQL语句。 8、查询的WHERE过滤原则,应使过滤记录数最多的条件放在最前面。 9、任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边。 10、in、or子句常会使用工作表,使索引失效;如果不产生大量重复值,可以考虑把子句拆开;拆开的子句中应该包含索引。 三、其他经验性规则 1、尽量少用嵌套查询。 如必须,请用notexist代替notin子句。 如例 (2) 2、用多表连接代替EXISTS子句。 如例(3) 3、少用DISTINCT,用EXISTS代替如例(4) 4、使用UNIONALL、MINUS、INTERSECT提高性能 5、使用ROWID提高检索速度。 对SELECT得到的单行记录,需进行DELETE、UPDATE操作时,使用ROWID将会使效率大大提高。 6、使用优化线索机制进行访问路径控制。 7、使用cursor时,显示光标优于隐式光标 本规范示例: 例一: SELECTaka042 --单位缴费划入个人帐户比例 INTOprm_aaa043 FROMka01 --医疗保险单位缴费划入个人帐户比例分段信息 WHEREakc021 =rec_kc01.akc021 --医疗人员类别 ANDaka041>=rec_kc01.akc023 --年龄上限 ANDaka040<=rec_kc01.akc023 --年龄下限 ANDaae030<=prm_date --开始时间 AND(aae031>=prm_dateORaae031ISNULL); --终止时间 例二: X SELECT...... FROMemp WHEREdept_noNOTIN(SELECTdept_no FROMdept WHEREdept_cat='A'); O SELECT...... FROMempe WHERENOTEXISTS(SELECT'X' FROMdept WHEREdept_no=e.dept_no ANDdept_cat='A'); 例三: X SELECT...... FROMemp WHEREEXISTS(SELECT'X' FROMdept WHEREdept_no=e.dept_no ANDdept_cat='A'); O SELECT...... FROMempe,deptd WHEREe.dept_no=d.dept_no ANDdept_cat='A'; 例四: X SELECTDISTINCTd.dept_code,d.dept_name FROMdeptd,empe WHEREe.dept_code=d.dept_code; O SELECTdept_code,dept_name FROMdeptd WHEREEXISTS(SELECT'X' FROMempe WHEREe.dept_code=d.dept_code); 关键词列表: ACCESSDECIMALINITIALONSTART ADDNOTINSERTONLINESUCCESSFUL ALLDEFAULTINTEGEROPTIONSYNONYM ALTERDELETEINTERSECTORSYSDATE ANDDESCINTOORDERTABLE ANYDISTINCTISPCTFREETHEN ASDROPLEVELPRIORTO ASCELSELIKEPRIVILEGESTRIGGER AUDITEXCLUSIVELOCKPUBLICUID BETWEENEXISTSLONGRAWUNION BYFILEMAXEXTENTSRENAMEUNIQUE FROMFLOATMINUSRESOURCEUPDATE CHARFORMLSLABELREVOKEUSER CHECKSHAREMODEROWVALIDATE CLUSTERGRANTMODIFYROWIDVALUES COLUMNGROUPNOAUDITROWNUMVARCHAR COMMENTHAVINGNOCOMPRESSROWSVARCHAR2 COMPRESSIDENTIFIEDNOWAITSELECTVIEW CONNECTIMMEDIATENULLSESSIONWHENEVER CREATEINNUMBERSETWHERE CURRENTINCREMENTOFSIZEWITH DATEI
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 编程 风格