Oracle数据库开发规范.docx

Oracle数据库开发规范.docx

《Oracle数据库开发规范.docx》由会员分享，可在线阅读，更多相关《Oracle数据库开发规范.docx（36页珍藏版）》请在冰豆网上搜索。

Oracle数据库开发规范

项目编号：

×××

xxx

Oracle数据库开发规范

OracleDBDevelopmentStandardization

Version>

<部门名称>

**年**月**日

文档信息：

文档名称：

文档编号：

文档版本日期：

起草人：

起草日期：

复审人：

复审日期：

版本历史：

版本

日期

作者

更改参考

说明

审批信息：

签字/日期

审核

审批

目录

1概述4

1.1编写目的4

1.2文档约定4

1.3预期的读者和阅读建议4

1.4参考文献5

2数据库对象命名6

2.1命名总体原则6

2.2表名6

2.3视图6

2.4同义词6

2.5序列7

2.6索引7

2.7存储过程7

2.8存储函数8

2.9存储程序包8

2.10触发器8

2.11字段8

2.12其他9

3设计规范9

3.1范围9

3.2表空间9

3.3字符集10

3.4主外键约束10

3.5分区表10

3.6RAC下的序列设计10

3.7字段10

3.8表结构设计11

3.9索引设计11

3.10临时表11

4SQL编写规范12

4.1书写规范12

4.2SQL语句的索引使用13

4.3SQL语句降低系统负荷15

5PL/SQL编程规范18

5.1书写规范18

5.2常用数据库操作语句编码规范19

5.3常用过程控制结构20

5.4Condition21

5.5Cursor22

5.6变量定义与赋值22

5.7过程与函数调用23

5.8例外处理（Exception）23

5.9例外处理的错误消息24

5.10注释（Comment）25

5.11应用调试控制27

5.12并发控制27

5.13代码测试、维护29

1概述

1.1编写目的

为规范软件开发人员的Oracle数据库开发提供参考依据和统一标准。

1.2文档约定

说明本文档中所用到的专用术语定义或解释，缩略词定义。

1.3预期的读者和阅读建议

本文档适用于所有开发员。

1.4参考文献

列出有关的参考文件，如：

a．属于本项目的其他已发表文件；

b．本文件中各处引用的文档资料。

列出这些文件的标题、作者，说明能够得到这些文件资料的来源。

2数据库对象命名

2.1命名总体原则

本规范所涉及数据库对象主要是指表、视图、同义词、索引、序列、存储过程、函数、触发器等；

命名应使用富有意义的英文词汇，尽量避免使用缩写，多个单词组成的，中间以下划线分割；

避免使用Oracle的保留字或关键字，如LEVEL和TYPE；

各表之间相关列名尽量同名；

除数据库模式对象名称长度为1－8个字符，其余对象名称均要求不超过30个字符；

命名只能使用大写英文字母，数字和下划线，且以英文字母开头。

2.2表名

规则：

XXX_MMM_DDDD

说明：

XXX代表子系统或模块名称（2-3个字母构成）；

MMM代表子模块名称（2-3个字母构成，根据实际情况可以没有）；

DDDD为表的简称含义，使用英文单词或词组构成，可包括下划线，但不得使用汉语拼音。

示例：

PO_HEADERS_ALL

2.3视图

规则：

XXX_MMM_DDDD_V

说明：

XXX代表子系统或模块名称（2-3个字母构成）；

MMM代表子模块名称（2-3个字母构成，根据实际情况可以没有）；

DDDD为视图简称含义，使用英文单词或词组构成，可包括下划线，但不得使用汉语拼音；

V表示视图，如有多个功能类似的视图，名称以V,V1,V2...Vn区分。

示例：

PO_HEADER_RELEASE_V

2.4同义词

规则：

与引用的原对象同名

示例：

PO_HEADERS_ALL

2.5序列

规则：

XXX_MMM_DDDD_S

说明：

XXX代表子系统或模块名称（2-3个字母构成）；

MMM代表子模块名称（2-3个字母构成，根据实际情况可以没有）；

DDDD为序列简称含义，使用英文单词或词组构成，可包括下划线，但不得使用汉语拼音；

S表示序列。

示例：

PO_HEADERS_S

2.6索引

规则：

XXX_MMM_DDDD_Tn

说明：

XXX代表子系统或模块名称（2-3个字母构成）；

MMM代表子模块名称（2-3个字母构成，根据实际情况可以没有）；

DDDD为索引简称含义，要求同表名，如总长度超过限制，可适当缩写；

T有三种值：

U表示唯一性索引，N表示非唯一性索引，B表示位图索引；

n表示序号，用数字构成。

示例：

PO_HEADERS_ALL_U1

2.7存储过程

规则：

XXX_MMM_DDDD_P

说明：

XXX代表子系统或模块名称（2-3个字母构成）；

MMM代表子模块名称（2-3个字母构成，根据实际情况可以没有）；

DDDD为存储过程简称含义，使用英文单词或词组构成，可包括下划线，不得使用汉语拼音；

P表示存储过程。

示例：

PO_HEADERS_P

2.8存储函数

规则：

XXX_MMM_DDDD_F

说明：

XXX代表子系统或模块名称（2-3个字母构成）；

MMM代表子模块名称（2-3个字母构成，根据实际情况可以没有）；

DDDD为存储函数简称含义，使用英文单词或词组构成，可包括下划线，不得使用汉语拼音；

F表示存储函数。

示例：

PO_HEADERS_F

2.9存储程序包

规则：

XXX_MMM_DDDD_PKG

说明：

XXX代表子系统或模块名称（2-3个字母构成）；

MMM代表子模块名称（2-3个字母构成，根据实际情况可以没有）；

DDDD为存储程序包简称，使用英文单词或词组构成，可包括下划线，不得使用汉语拼音；

PKG表示存储程序包。

示例：

PO_HEADERS_PKG

2.10触发器

规则：

XXX_MMM_DDDD_Tn

说明：

XXX代表子系统或模块名称（2-3个字母构成）；

MMM代表子模块名称（2-3个字母构成，根据实际情况可以没有）；

DDDD为触发器简称含义，要求同表名，如总长度超过限制，可适当缩写；

T表示触发器；

n表示序号，用数字构成。

示例：

PO_HEADERS_T1

2.11字段

一般性字段：

使用英文单词、词组或其有意义的缩写表示，可包括下划线，不得使用汉语拼音，多个类似含义的字段，可在最后加数字序号；

特殊字段：

（1）代表主键的字段：

表名或缩写+"_ID"，如PO_HEADER_ID

（2）代表外键的字段：

要求与相关表对应字段相同

（3）记录跟踪字段（代表记录创建人、创建时间、最后修改人、最后修改时间）：

字段名

类型

NULL?

说明

CREATED_BY

NUMBER

NOTNULL

这些字段仅用于跟踪数据的变化，而不能用于其他用途。

CREATION_DATE

DATE

NOTNULL

LAST_UPDATED_BY

NUMBER

NOTNULL

LAST_UPDATE_DATE

DATE

NOTNULL

（4）扩展字段（预留未来使用）：

ATTRIBUTE+n，n代表序号，如ATTRIBUTE1，

类型统一为VARCHAR2（150）；

对应扩展属性分类字段，命名为ATTRIBUTE_CATEGORY,类型为VARCHAR2（30）。

2.12其他

其他对象参照上述原则和对象命名。

3设计规范

3.1范围

设计规范主要是对与开发关系密切的数据库整体设计部分进行说明，以便于DBA更有效的管理和维护数据库。

主要包括表空间、字符集、主外键约束、分区表、字段类型等内容。

3.2表空间

　　一致性：

无论是基于商品化软件的开发，还是独立的客制化开发系统，对于客制化对象，每个子系统或模块都使用统一的一组表空间（数据表空间和索引表空间各一个，且数据表空间为默认表空间）；

　　独立性：

数据和索引独立存储，表空间由DBA统一管理和维护，任何新建表均应使用默认的数据表空间，新建索引均需使用指定的索引表空间；新建表和索引时，原则上不允许指定除表空间以外的任何参数，特殊情况必须指定有别于表空间的默认参数时，需经过单独评审。

　　示例：

CREATETABLETABLE_NAME（FIELD1...）TABLESPACEXXX;

　　例外情况：

当使用分区表时，可根据情况建立独立的表空间。

3.3字符集

应用系统数据库应使用UTF8字符集，应用程序开发本身也需要支持UTF8字符集录入和查询。

注意事项：

UTF8字符集默认排序规则非中文拼音，如有需要应在代码级或数据库会话级做特殊处理。

3.4主外键约束

数据库表对象原则上禁止使用主外键约束，主外键所承担的主要作用应通过其他方式实现，如主子表数据完整性由应用层实现，索引功能通过建立单独索引实现。

3.5分区表

为了降低数据库的维护复杂度，非必要情况下，不使用分区表；或经常需要跨分区查询数据，此时也不应使用分区表；特殊应用场景确有必要时，可选择范围分区表、列表分区表、哈希分区表、组合分区表几种方式，并根据分区类型和分区依据，调整索引，使相关数据查询尽量在一个分区中完成。

3.6RAC下的序列设计

当数据库部署架构为RAC时，原则上禁止使用序列生成依赖其大小排序的单据号码，如确有必要，则序列必须设计为禁用缓存模式。

3.7字段

数值类型使用NUMBER，需要存储整型数值时，可指定小数位精度为0；

枚举类型优先使用数值型替代字符型，且需要备注说明枚举类型的各个不同取值的含义；

ID结尾的字段为应为数值型，CODE结尾的字段可为字符型或数值型；

用CHAR

（1）表示布尔值的取大写：

"Y"，"N"，或改为数值型，用0和1表示；

原则上使用VARCHAR2代替CHAR类型，以节省存储空间，但对性能要求太别高的应用场景例外；

日期时间型应使用DATE，禁止使用字符型；

使用CLOB代替LONG，BLOB代替LONGRAW；

一般性字段应有含义注释，对于可以有缺省值的字段，应设为非空类型，并给出缺省值。

3.8表结构设计

原则上表结构设计应符合第三范式，减少冗余字段，但在不影响数据一致性的情况下，为了提高性能或开发实现的方便性，可以适当增加冗余字段。

创建表时，应添加相关注释，简要说明主要用途。

表与表之间的关系应提供E-R图。

3.9索引设计

对代表唯一记录的字段或字段组合应建立唯一索引，对于查询中需要作为查询条件的字段，可以考虑建立非唯一索引，对于作为查询条件的枚举型字段，可考虑建立位图索引。

建立复合索引时，应根据条件字段的查询频率决定索引字段顺序。

开发过程中新编写的SQL，应对照查询条件检查有无相关索引，除需要特别指定全表扫描的情况外，原则上应建立相关索引。

3.10临时表

临时表指创建表时指定临时表参数的数据表，并非指表名中含有TEMP字符的表。

尽量避免在临时表上使用DISTINCT、ORDERBY、GROUPBY、HAVING、JOIN，以减轻临时表负担，同时应避免频繁创建和删除临时表，减少系统表资源的消耗。

在新建临时表时，如果一次性插入数据量很大，应使用SELECTINTO代替CREATETABLE，避免数据库LOG，提高速度；如果数据量不大，为了缓和系统表的资源，可先CREATETABLE，然后INSERT。

如果临时表的数据量较大，需要建立索引，那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中，以保证系统能够很好的使用到该临时表的索引。

如果在程序中动态创建了临时表，最后务必将其显式删除，并优先TRUNCATETABLE，然后DROPTABLE，以避免系统表的较长时间锁定。

4SQL编写规范

　　为了提高系统性能，避免未优化的SQL语句降低系统性能，要求SQL语句写法必须考虑以下两个方面：

a、使用恰当的索引b、减少系统负荷。

同时为保障规范的落地执行，SQL开发工具统一使用PL/SQLDeveloper，并使用统一的Beautifier设置和模板。

4.1书写规范

4.1.1大小写

　　保留字统一使用大写字符，其他字符除常量值外，一律使用小写字符。

4.1.2对齐

　　SELECT、UPDATE、DELETE、INSERT、FROM、WHERE等保留字原则上要独立引领一行，保持左对齐并与其后字符保留空格；WHERE条件中"="两边各保留一个空格，条件Column保持对齐。

　　示例：

语句名

格式

SELECT

SELECTcolumn1

column2

column3

FROMtable_name

WHEREcolumn1=v_condition1

ANDcolumn2=v_condition2

GROUPBYcolumn_name

ORDERBYcolumn_name;

4.1.3别名

　　当一个SQL语句中涉及到多个表时，应始终使用别名来限定字段名，避免含义模糊的引用；别名命名时，避免使用无意义的代号a、b、c...，而应该有意义（如表mtl_system_items_b对应别名为msi，po_headers_all别名对应为pha）。

4.2SQL语句的索引使用

　　除特殊情况外，原则上SQL语句应使用索引，且一般要遵守以下规则。

4.2.1避免索引列上的函数

如果函数中使用到索引列字段，则索引是不被利用的，可以通过改变SQL写法来回避。

SUBSTR

不使用

SELECTe.ename

FROMempe

WHERESUBSTR（e.ename,1,1）='S';

使用

SELECTe.ename

FROMempe

WHEREe.enameLIKE'S%';

以上假设emp表的ename建立了索引。

TO_CHAR、TO_DATE

不使用

SELECTe.ename

FROMempe

WHERETO_CHAR（e.hiredate,'YYYY/MM/DD'）='1981/11/17';

使用

SELECTe.ename

FROMempe

WHEREe.hiredate=TO_DATE（'1981/11/17','YYYY/MM/DD'）;

以上假设emp表的hiredate建立了索引。

NVL

不使用

SELECTe.ename

FROMempe

WHEREnvl（m,0）<1000;

使用

SELECTe.ename

FROMempe

WHEREmISNULLORm<1000;

以上假设emp表的comm建立了索引。

TRUNC

不使用

SELECTe.ename

FROMempe

WHERETRUNC（e.hiredate）BETWEENto_date（'2005/01/01','YYYY/MM/DD'）ANDto_date（'2005/01/31','YYYY/MM/DD'）;

使用

SELECTe.ename

FROMempe

WHEREe.hiredate>=to_date（'2005/01/01','YYYY/MM/DD'）

ANDe.hiredate

以上假设emp表的hiredate建立了索引。

4.2.2避免索引列上的计算公式

如果索引列上使用了计算公式，则索引不能使用，可以通过更改计算公式来避免。

不使用

SELECTe.ename

FROMempe

WHEREe.sal*1.1>900;

使用

SELECTe.ename

FROMempe

WHEREe.sal>900/1.1;

以上假设emp表的sal建立了索引。

4.2.3避免使用notin，使用notexists代替

　　使用notin时，可能导致从表索引无法使用，可以使用notexists来避免。

不使用

SELECTe.ename

FROMempe

WHEREe.deptnoNOTIN（SELECTd.deptnoFROMdeptd）;

使用

SELECTe.ename

FROMempe

WHEREe.deptnoNOTEXISTS（SELECT'x'FROMdeptdWHEREd.deptno=e.deptno）;

4.2.4LIKE的使用

LIKE用于模糊检索，LIKE检索的样式有三种：

前匹配（XX%）、中间匹配（X%X）、后匹配（%XX）。

对于前匹配可以使用索引，而使用中间匹配和后匹配，都不能使用索引。

因此除非必要，否则应尽量避免使用中间匹配和后匹配。

不使用

SELECTe.ename

FROMempe

WHEREe.enameLIKE'%B';

使用

SELECTe.ename

FROMempe

WHEREe.enameLIKE'B%';

4.2.5复合索引的使用

　　要使用复合索引，where语句中必须包括复合索引中的所有列或前几个列。

如果复合索引的第一个列不在where语句中则不能使用该复合索引。

不使用

SELECTe.ename

FROMempe

WHEREe.job='MANAGER';

使用

SELECTe.ename

FROMempe

WHEREe.deptno=20ANDe.job='MANAGER';

使用

SELECTe.ename

FROMempe

WHEREe.deptno=20;

以上假设emp表的deptno,job建立了索引。

4.2.6索引提示使用

对于特别的应用场景可利用hint固化执行计划，以使用正确索引。

4.3SQL语句降低系统负荷

4.3.1编写可再利用性的SQL语句

　　对已经运行过的SQL语句，Oracle会将它放在SQL缓冲池中，当有SQL需要运行时，Oracle会先从SQL缓冲池中查询是否该语句已经存在，如存在的话则直接执行，否则需要进行编译、解析的操作。

因此编写可再利用的SQL语句可以提高系统性能。

为了提高SQL语句的可再利用，必须注意以下两点：

遵守SQL编写规范：

原则上，对于不是完全一样的SQL，oracle不会去再利用。

即使是大小写不同，多了1个空格，都被认为是不同的SQL语句。

所以，要彻底的遵循SQL/PLSQL编程规范，来保证SQL语句的再利用性。

　　利用变量绑定：

当需要动态组合条件时，应避免直接将变量值组合到条件中去，而应该使用变量绑定，从而提高SQL语句的再利用性。

4.3.2使用表别名

　　通过对表附加别名，SQL编译时可以明确列的来源表，从而使得SQL的编译时间缩短，另外还可以增加SQL语句的可读性。

4.3.3ROWNUM的使用

　　ROWNUM可以限制检索数据的数量，如果为了判断对象是否存在，使用ROWNUM=1是非常有效的。

检索是否存在部门20的员工

SELECT'Y'

FROMempe

WHEREe.deptno=20

ANDROWNUM=1;

　　注意：

由于ROWNUM是获得数据的顺序号，在有ORDERBY子句的情况下，ROWNUM列无法按分类有序递增。

4.3.4UNION和UNIONALL

　　为了排除有UNION重复的data，系统自动进行分类处理。

如果确定联合中不会出现重复数据的话，必须设定UNIONALL来取消自动分类以提高检索速度。

4.3.5替代DISTINCT

　　使用DISTINCT将引起内部排序处理，如有可能，应尽量使用EXISTS、NOTEXISTS或子查询来避免。

使用DISTINCT

SELECTDISTINCTd.deptno,

d.dname

FROMdeptd,

empe

WHEREd.deptno=e.deptno;

使用EXISTS

SELECTd.deptno,

d.dname

FROMdeptd

WHEREEXISTS（SELECT'x'FROMempeWHEREd.deptno=e.deptno）;

4.3.6避免视图滥用

　　如果查询只检索基表的几个字段，应避免直接使用视图，造成不必要的数据块检索。

视图

CREATEVIEWCRM_emp_v

SELECTe.empno,

e.ename,

e.sal,

d.deptno,

d.dname

FROMempe,

deptd

WHEREe.deptno=d.deptno;

使用视图

SELECTe.empno,e.enameFROMCRM_emp_v;

使用基表

SELECTe.empno,e.enameFROMemp;

4.3.7组合查询语句

　　尽量将几个简单的SQL语句组合成一个SQL进行查询，可以减少数据检索次数，提高检索效率，但会造成可读性较差。

组合前

SELECTe.sal

FROMempe

WHEREe.ename='Jimmy';

SELECTAVG（e.sal）

FROMempe

WHEREe.deptno=20;

组合后

SELECTe.sal,d.sal_avg

FROM（SELECTe1.sal

FROMempe1

WHEREe1.ename='Jimmy'）e

（SELECTAVG（e2.sal）sal_avg

FROMempe2

WHEREe2.deptno=20）d;

4.3.8WHERE条件中不调用自定义函数或过程

　　在WHERE条件中调用自定义函数或过程极易造成SQL性能严重降低，原则上应禁止使用，除非十分有必要且能百分百保证此条件不会成为查询驱动条件。

4.3.9避免强制类型转换

　　SQL中的字符类型数据应该统一使用单引号，特别对纯数字的字符串，必须用单引号，以免导致内部转换而引起性能问题或索引失效。

　　SQL中的变量、常量、表关联字段等与对应条件字段应保持相同类型或使用显式转换，避免数据库内部的强制转换。

5PL/SQL编程规范

5.1书写规范

5.1.1SQL书写

　　SQL书写应符合SQL编写规范。

5.1.2大小写

　　程序代码中要求保留字统一使用大写字符，其他字符除常量值外，一律使用小写字符；程序注释中不做大小写要求。

5.1.3变量和参数

　　程序内部变量名称要求以"v_"开头；

　　输入参数名称要求以"p_"开头；

　　输出参数或变量名称要求以"x_"开头；

　　游标变量要求以"cur_"开头；

　　游标记录变量要求以"rec_"开头；

　　与表记录或字段关联的变量，变量类型统一使用%ROWTYPE或%TYPE，以便保持与相关表或字段一致。

5.2常用数据库操作语句编码规范

语句名

格式

SELECT

SELECTcolumn1

column2