oracle 开发规范 -续

                                                                      ORACLE 开发规范

                                                                                                                                 -Dominic

 

前言

目前有这么一个现象，就是：DBA 不太懂写PL/SQL ，而有的开发人员写的是五花八门，执行效率不高。如此以来，造成诸多弊端： 

1. 可读性差。读别人写的一个程序花费的时间，比自己写一个程序的花费时间还要长；非但别人看不懂，时间久了连自己也看不懂了。 

2. 可维护性差。程序越写越长，越改越烂，像懒婆娘的裹脚布，又臭又长。 

3. 可移植性差。今天用oracle 写一套，明天换成SQL Server 的时候再写一套，众多的数据库开发人员在程序的苦海中重复着低级劳动…… 

4. 效率和性能差。一个存储过程或SQL 执行效率简直可以让你感觉到对时间的绝望，你很快就理解什么是相对论了。 

 

例如下面这段简单的代码，您看的明白吗？就算暂时明白，过个一年半载您还明白吗？就算您记忆超群，想必也会忘记，不是么？ 

A:=1;b:=2;SeLeCT username from EmPLOyee where id=a aNd Type=b; 

如果你改为下面这样的写法，相信稍微懂点数据库的人都应该看的明白不是？ 

vID:=1;   --A 为ID 

vType=2;  --B 为类型 

SELECT username FROM employee WHERE id=vID AND type=vType; 

 

 

 

 

 

 

数据约定

1 是指对数据库、数据库对象如表、字段、索引、序列、存储过程等的命名约定；

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

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

4 除数据库名称长度为1－8个字符，其余为1－30个字符，Database link名称也不要超过30个字符；

5 命名只能使用英文字母，数字和下划线；

6 避免使用Oracle的保留字如LEVEL、关键字如TYPE（见Oracle保留字和关键字）；

 

Oracle保留字和关键字
ACCESS DECIMAL INITIAL ON START
ADD NOT INSERT ONLINE SUCCESSFUL
ALL DEFAULT INTEGER OPTION SYNONYM
ALTER DELETE INTERSECT OR SYSDATE
AND DESC INTO ORDER TABLE
ANY DISTINCT IS PCTFREE THEN
AS DROP LEVEL PRIOR TO
ASC ELSE LIKE PRIVILEGES TRIGGER
AUDIT EXCLUSIVE LOCK PUBLIC UID
BETWEEN EXISTS LONG RAW UNION
BY FILE MAXEXTENTS RENAME UNIQUE
FROM FLOAT MINUS RESOURCE UPDATE
CHAR FOR MLSLABEL REVOKE USER
CHECK SHARE MODE ROW VALIDATE
CLUSTER GRANT MODIFY ROWID VALUES
COLUMN GROUP NOAUDIT ROWNUM VARCHAR
COMMENT HAVING NOCOMPRESS ROWS VARCHAR2
COMPRESS IDENTIFIED NOWAIT SELECT VIEW
CONNECT IMMEDIATE NULL SESSION WHENEVER
CREATE IN NUMBER SET WHERE
CURRENT INCREMENT OF SIZE WITH
DATE INDEX OFFLINE SMALLINT

CHAR VARHCAR VARCHAR2 NUMBER DATE LONG
CLOB BLOB BFILE
INTEGER DECIMAL
SUM COUNT GROUPING AVERAGE 

TYPE

 

 

 

 

 

命名规范

除表外，其它各种对象的命名最好用不同的前缀加以区别。采用前缀的方式来命名对象则很容易通过排序对对象进行区别。 

如在命名规范中各组成部分以_ 分割，则前缀建议也以_ 分割；反之则可加可不加

对象名	前缀	范例
表(table)	tbl_/t_	t_user_info/tbl_user_info
视图(view)	v_/v	v_user_info/vuserinfo
序列(sequence)	seq_	seq_user_info
簇(cluster)	c_	c_user_info
触发器(trigger)	trg_	trg_user_info
存储过程(procedure)	sp_/p_	sp_user_info/p_user_info
函数(function)	f_/fn_	fn_user_info/f_user_info
物化视图 (materialized view)	mv_	mv_user_info
包和包体 (package & package body)	pkg_	pkg_user_info
类和类体 (type & type body)	typ_	typ_user_info
主键(primary key)	pk_	pk_user_info
外键(foreign key)	fk_	fk_user_info_fieldname
唯一索引(unique index)	uk_	uk_user_info_fieldname
普通索引(normal index)	idx_	idx_user_info_fieldname
位图索引(bitmap index)	bk_	bk_user_info_fieldname
同义词(synonym)	依据于所 分配的表所属模块/ 模式
数据库链接(database link)	无特殊要求

 

 

 

所有PL/SQL 中的变量与对象命名规则相似

变量类型	前缀	范例
输入变量	i_/i	i_user_id/iuserid
输出变量	o_/o	o_user_name/ousername
输出输入变量	io_/io	io_user_name/iousername
普通变量	v_/v	v_user_id/vuserid
全局变量	gv_/gv	gv_user_id/gvuserid
常量	大写	PI
游标	cur_	cur_userinfo
用户自定义类型	type_	type_user_info
保存点(save point)	spt_	spt_user_info

 

命名不允许使用中文或者特殊字符。 

命名中若使用特殊约定或缩写，则要注释说明。 

 

 

 

 

设计规范

规范约定

1. 遵守数据的设计规范3NF 规定

· 表内的每一个值都只能被表达一次。

· 表内的每一行都应该被唯一的标识（有唯一键）。

· 表内不应该存储依赖于其他键的非键信息。

2. 实体表都必须包含四个字段：编号（ID）、代码（Code）、创建人（Created By）、创建时间（Creation Time）、修改人（Modified By）、修改时间（Modification Time）、删除标志（Delete Status）。其含义如下：

ü 编号是系统内的唯一标识

ü 代码是客户为这个实体的编码，这个字段看需要确定是否保留

ü 创建人是是指数据库记录创建人

ü 创建时间是指数据库记录创建时间，默认为SYSDATE

ü 修改人是是指数据库记录修改人

ü 修改时间是指数据库记录修改时间，默认为SYSDATE

ü 删除标志是本条记录是否已删除的标志（0：没有删除；1：已经删除），数据中的数据不做真正删除，只设置这个标志

这五个字段的命名规则如下

ü 实体名＋字段名称，如user_id、user_code、created_by、creation_time、modified_by 、modification_time 、delete_status。

ü 这五个字段的类型和长度规则如下

n 编号 INTEGER

n 代码 VARCHAR2（30）

n 创建人 INTEGER

n 创建时间DATE

n 修改人 INTEGER

n 修改时间DATE

n 删除标志 CHAR(1)

3 代码中需要空位时，统一采用空格键输入，不允许用TAB键产生空位； 说明：不同的编辑器对TAB的空位格数设置不一致，会导致使用TAB键产生空位的代码格式混乱；

4 数据库代码中，关键字大写，其他内容小写（在PL/SQL中可设置关键字自动转换为大写，以降低编码时的大小写切换）；

5 同一条语句占用多行时，每一行的开始应是关键字，且关键字应和第一行左对齐,如确实不能从关键字分行，则分行处应对其上一行被分行的同类代码的最左边；

6 (建议)对于INSERT… VALUES和UPDATE语句，一行写一个字段，每个字段相对于INSERT语句空两格，这段后面紧跟注释（注释语句左对齐），VALUES和INSERT左对齐，左括号和右括号与INSERT、VALUES左对齐；

7 (建议)INSERT…SELECT 语句时，应使每行的字段顺序对应，以每行最多不超过4 个字段，以方便代码阅读，括号的内容另起一行缩进2 格开始书写，关键字单词左对齐，左括号、右括号另起一行与左对齐；

8 不允许将多行语句书写在同一行;

9 不允许将SQL语句写成一行，再短的SQL也应该在谓词处分行 ;
10 相对独立的程序块之间应加空行；

11 不同类型的操作符混合使用时，应使用括号明确的表达运算的先后关系;

12 BEGIN和END应独立成行；

13 SQL语句中的逗号后面应增加一个空格，以使得代码清晰；

14 存储过程、函数、触发器、程序块中定义的变量和输入、输出参数在命名上有所区分;

15 原则上每个表应设计一个主键；

16 一般建议在查询数据量10%以下使用索引；

17 如果字段查询频率相同，则把表中的数据的排列顺序所依据的字段放在前面；
18 使用EXISTS/NOT EXISTS替代IN/NOT IN;

19  注释：

 /***********************************************************

  名称: Sp_Tbl_CHN_AF_xxxx

  功能描述：根据配置表TBL_CONFIG_TBSTATUS从录入库表Tbl_Chn_I_AF_ANACD提取更新数据到中心库表Tbl_CHN_AF_ANACD

  修订记录：

  版本号    编辑时间    编辑人  修改描述

  1.0.0    2011.12.29   xxx  1.创建此存储过程

  入参出参描述：

  N/A

  ***********************************************************/

 

注：请按ORACLE语法编写脚本并加上中文注释，注意表名的命名，参考脚本:

CREATE TABLE TBL_CHN_x_AF_xxx

  (ANACD NVARCHAR2(80) NOT NULL,

  ANANM NVARCHAR2(50) NOT NULL,

  ANANM_EN NVARCHAR2(200) NOT NULL);

COMMENT ON COLUMN TBL_CHN_I_AF_xxx. ANACD IS '分析师代码';

COMMENT ON COLUMN TBL_CHN_I_AF_xxx. ANANM IS '分析师名称';

COMMENT ON COLUMN TBL_CHN_I_AF_xxx. ANANM IS '分析师英文名称'; 

 

 

 

 

 

 

 

 

 

 

字段规范

1 一行记录必须表内唯一，表必须有主键。

2 枚举类型使用 NUMBER，且需要说明枚举类型的各个不同取值的含义

3 ID结尾的字段为仅由数字组成，Code结尾的字段为仅由字母或数字组成

4 用CHAR（1）表示布尔值的取大写：“Y”，“N”。

5 应尽量使用VARCHAR2代替CHAR类型；

6 VARCHAR(2)最多4000字符；

7 DATE精确到秒，而非天；

8 使用CLOB代替LONG，BLOB代替LONG RAW；

9 使用NUMBER数据类型时请给定长度，例如:NUMBER(5,2)　表示整数部分最大3位，小数部分为2位；

 

 

 

 

 

 

 

 

 

使用规范

综合

1 如果开发过程中需要建立索引，需要提交书面的更改请求，说明所需索引的定义(名称、字段列表、顺序、索引类型)以及建立的理由。数据库管理员统一维护索引并将提交的请求更改。

2 数据库各表的初始数据(包含各代码表、配置表)需要提交给数据库管理员。

3 不得使用触发器。

4 涉及到数据库多表数据的更改(Insert/Delete/Update)必须使用数据库事务进行控制，并且必须有完整事务开始和提交/回滚机制。不能使用范围事务

5 尽量避免Union操作的使用，需要使用时，请向数据库管理员咨询使用Union操作的影响。

6 SQL语句和存储过程，尽量使用PL/SQL Develper的代码美化器美化。

7 属于ORACLE的关键字大小，表名、列名等小写。

查询

8 在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须显式写明

9 在表查询中，必须有Where条件，除非此表为非增长表

10 在表查询中，一次最多返回的记录条数不要超过1000条或记录内容不要大于1MB的数据。

11 在表查询中，作Order By排序时，优先使用主键列，索引列

12 多表关联查询时，优先使用Where条件，再作表关联，并且需要保证被关联的字段需要有索引。

13 避免在WHERE字句中对列施以函数:

错误：
SELECT　service_id,service_name　
FROM　service_promotion
WHERE TO_CHAR(gmt_modified,’yyyy-mm-dd’)
= ‘20001-09-01’;
正确：
SELECT service_id,service_name　
FROM service_promotion
WHERE gmt_modified
> = TO_DATE(‘2001-9-01’,’yyyy-mm-dd’)
AND gmt_modified
< TO_DATE(‘2001-9-02’,’yyyy-mm-dd’);

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

错误：
SELECT category_id, category_name FROM category
WHERE category_id = ‘123’; -- id’s type is number

正确：
SELECT category_id, category_name FROM category
WHERE category_id = 123; -- id’s type is number

删除

15 删除记录时，必须有Where唯一条件

16 当有主从表时，要先删除从表记录，在删除主表记录

修改

17 修改记录时，必须有Where唯一条件