Oracle编程风格.docx

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语句的约定

１．尽量避免在循环中使用SQL语句。

２．避免在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

３．避免使用数据库的类型自动转换功能：

SELECT*

FROMcategory

WHEREid=‘123’;--id’stypeisnumber

４．避免无效的连接:

SELECTcount（*）

FROMoffera,count_by_emailb

WHEREa.email（+）=b.email;

５．连接（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