数据库管理开发规范.docx

数据库管理开发规范.docx

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

数据库管理开发规范

数据库管理开发规范

1目的

规范软件开发过程中的数据库管理，作为数据库设计开发的指导性文件。

统一数据库对象的命名格式，提高数据库代码的可读性。

2数据库管理

2.1数据库文件管理

测试部统一管理各个项目的数据库文件，开发部在开发过程中按照测试部发布的项目数据库建立开发环境，并对数据库环境进行维护，维护变更过程以《数据库修改记录报告》形式进行记录，程序开发完毕同程序一并提交测试部，由测试部向各个项目发布最新的数据库文件。

2.2数据库命名及事务对象

整个系统共有7个数据库构成，分别为：

库名

中文含义

事务对象名

提交方式

备注

CODE

基础代码

SQLCode

TRUE

存放ERP系统涉及到的基础代码表，如部门代码（bmdm）、物资代码（wzdm）等

CONFIG

配置

SQLConfig

TRUE

对ERP系统的配置信息表，如企业基本信息、数据配置信息、权限配置信息表等

JYXS

经营

SQLJy

TRUE

存放对应子系统各功能模块用到的数据表

SCXT

生产

SQLSc

TRUE

WZXT

物资

SQLWz

TRUE

RS

人事

SQLRs

TRUE

CWXT…

财务

SQLCw

TRUE

CWXT+年度（+账套）

JS

技术

SQLJs

TRUE

ZL

质量

SQLZl

TRUE

系统默认使用自动提交，如果涉及到多表操作需要手动提交的则自己建立事务对象。

2.3多服务器跨库操作

如果一个数据窗中涉及到两个数据库中的表，不管这两个库是否在一个服务器上，为了提高程序的通用性，数据窗采用视图或者存储过程实现，如果使用存储过程实现需要在每个数据库名之前加入IP地址作参数。

取一个数据库所在的IP地址的函数为of_Getserverip（），判断两个数据库是否在一个服务器上用函数of_Getserverip_two（）。

『注意』：

跨库指的是同时操作两个不同库中的表，即原则上SQL语句（不论是数据窗口还是存储过程），不出现Code.dbo.gcdm字样，而是建成Uv_OuterCode_Gcdm视图。

建立跨库视图时注意只引用需要使用的字段，尽量不使用SELECT*，将视图中必要体现的字段列出，避免数据源表字段内容发生变化时需要重新生成视图的情况。

3对象命名规则

3.1注意事项

1、对象命名均不得超过30个字符的系统限制。

2、数据对象、变量、字段的命名都采用英文名、缩写或者拼音缩写，禁止使用中文命名。

3、除变量命名外，其他对象的命名均遵循Pascal大小写规范，即其中每个单词的第一个字母都是大写，其余小写；变量命名遵循camel大小写规范，其中除了第一个单词（表示数据类型）外每个单词的第一个字母都是大写；

3.2表

表名命名采用英文名、缩写或者拼音缩写，用单数形式表示名称；使用Pascal大小写处理；表名中不允许出现下划线。

3.3字段

字段命名采用英文名、缩写或者拼音缩写；如果采用英文名或英文缩写表示，使用Pascal大小写处理；若使用拼音缩写，用小写表示。

表中不允许以id作字段名，id字段命名以表名缩写加“_id”，id的长度非大数据量表字段类型及长度为IindentifyDecimal（10,0），大数据量表为IndentifyDecimal（18,0）；其中主键一律用表名_id或者表名简称_id，其他通用字段的命名以及类型规范详见《数据库常用字段标准表》

所有编号字段都设为“大小写区分”的排序规则。

在不同系统、不同表中，表示相同属性的字段名称保持一致，不要出现不同的字段名称对应代码的相同属性。

如避免在不同的表中分别用“wzph”或“clph”表示物资代码的物资牌号属性。

『注意』：

在命名字段名时，不要重复表的名称；例如，在名为Employee的表中避免使用名为EmployeeLastName的字段；不要在列的名称中包含数据类型。

3.4视图

是数据库中在表对象的基础上建立的虚拟表，具有表的特性，可以查询，也可以数据更新，在做数据更新时视图中必须包含源表的唯一索引字段。

视图命名以Uv_作为前缀；命名应尽量体现各视图的功能，采用动宾结构。

采用英文名、缩写或者拼音缩写；使用Pascal大小写处理；

命名方式为Uv_[主模块名]_<视图主体名称>，在Code，Config等通用库中的视图需要增加所属主模块前缀加以区别，例如“Uv_Js_”，表示技术模块使用；其他库中本系统的视图不需要加主模块前缀，例如物资系统中的视图“Uv_”即可；

对于表的映射视图，即对本地表的映射某些字段在建立了视图（这种情况不多见），请使用“Uv_Local_表名”表示本地表直接映射的视图；如果是为了跨库实现，对应非本地数据表映射建立了视图，请使用“Uv_OuterCwxt_表名”，即将所跨的数据库也体现；

3.5触发器

是数据库中用来保存程序代码的对象，是数据库中发生特定事件而触发执行的。

当一个触发器被触发时，数据可以根据其他被访问或者修改的数据而被相应修改，对减少数据冗余有用。

优点：

1、无论什么原因修改，自动触发

2、比外键、CHECK约束和规则对象等更为复杂的检查和操作。

可以对同一修改建立多个触发器。

触发器命名以Tri作为前缀；其后为相应的表名加上触发器类型后缀，Insert触发器加'_I'，Delete触发器加'_D'，Update触发器加'_U'，如：

Tri_Wzdm_I，Tri_Wzdm_D，Tri_Wzdm_U。

如果为Instead触发器，在最后加上_Inst后缀；这种情况比较少；

3.6存储过程

存储过程是保存的数据库中的程序，通常用来管理数据和进行批处理操作。

可以接受参数、返回状态和参数值。

存储过程命名以Up_作为前缀；命名应尽量体现各存储过程的功能，采用动宾结构。

采用英文名、缩写或者拼音缩写；使用Pascal大小写处理；

命名方式为Up_[主模块名]_<存储过程主体名称>，在Code，Config等通用库中的存储过程需要增加所属主模块前缀加以区别，例如“Up_Js_”，表示技术模块使用；其他库中本系统的存储过程不需要加主模块前缀，例如物资系统中的存储过程“Up_”即可；

3.7用户定义函数

用户定义函数采用零个或更多的输入参数并返回标量值或表。

函数最多可以有1024个输入参数。

当函数的参数有默认值时，调用该函数时必须指定默认DEFAULT关键字才能获取默认值。

用户函数命名以Uf_作为前缀；命名应尽量体现各函数的功能，采用动宾结构。

采用英文名、缩写或者拼音缩写；使用Pascal大小写处理；

命名方式为Uf_[主模块名]_<函数主体名称>，在Code，Config等通用库中的函数如果非通用，需要增加所属主模块名前缀加以区别，例如“Uf_Js_”，表示技术模块使用；其他库中本系统的函数不需要加主模块前缀，例如物资系统中的函数，“Uf_”即可；

如果函数返回的是表，则主命名部分以Tab开头，形如“Uf_Cx_TabStatTjsj”；如果为返回值的函数应包括返回值的说明，例如Uf_GetCurrentKey。

3.8索引/键

在数据库表中，可以使用索引加快访问数据的速度；

主索引以Pk_作为前缀，命名方式为Pk_<表名>_<主键标识>，主键标识一般为字段名；

非主索引以Ix_为前缀，命名方式为Ix_<表名>_<索引键标识>，索引键标识一般为字段名，如果为复合索引，使用能表示复合索引含义的单词或者词组；

外键命名以Fk_为前缀，命名方式为Fk_<表名>_<主表名>_<外键标识>；

每个表建立一个簇索引，经常插入和修改的表应使用较小的填充因子；

3.9默认值

默认值命名以Df_作为前缀；

3.10约束规则

是放置数据规则的对象，可以用来控制列中的数据，约束通常定义在列一级。

可以用来加强引用完整性。

规则命名以Ru_作为前缀；

3.11变量名

1、变量名的长度限制为29（不包括标识字符@）。

2、变量名使用camel大小写处理（documentFormatType），其中除了第一个单词（表示数据类型）外每个单词的第一个字母都是大写的。

数据类型前缀的规则见下表。

前缀

说明

Var

Sql_variant（最高）

Nstr

nvarchar

Str

varchar

Ch

Char

Ft

Float

Rl

Real

Dec

decimal

Int

Int

Dtm

Datetime

B

Bit

3、布尔变量名应该包含Is，这意味着Yes/No或True/False值，如bFileIsFound。

在命名状态变量时，避免使用诸如Flag的术语。

状态变量不同于布尔变量的地方是它可以具有两个以上的可能值。

不是使用documentFlag，而是使用更具描述性的名称，如documentFormatType。

4、即使对于可能仅出现在几个代码行中的生存期很短的变量，仍然使用有意义的名称。

仅对于短循环索引使用单字母变量名，如i或j。

5、不要使用原义数字或原义字符串，如Fori=1To7。

而是使用命名常量，如Fori=1ToNUM_DAYS_IN_WEEK，常量采用大写，要有明确的含义。

6、只要合适，在变量名的末尾追加计算限定符（Avg、Sum、Min、Max、Index）。

7、在变量名中使用互补对，如Begin/End和Open/Close。

4对象编程结构和描述规则

4.1代码缩格书写格式

为方便开发和维护，提高代码的易读性和可维护性，规范建议按照分级缩进格式编写该文本。

1、让每个主要的Transact-SQL子句单独占一行，以使语句更易于阅读。

2、相邻两级的缩进量为10个空格。

一个TAB键也相当于10个空格。

3、程序执行的各命令位于同一级；条件语句块（statenemtblock，以BEGIN…END为边界）位于下一级，类推。

4、SQL语句是该文本的主体。

规范建议按照SQL语句中系统保留字的关键程度再划分为三级，每一级相对缩进。

具体分级请参照下表。

主关键字

次关键字

一般关键字

SELECT

FROM

AND

INSERT（INTO）

WHERE

OR

UPDATE

VALUES

BETWEEN

DELETE

INSERT…SELECT…FROM语句中的SELECT和FROM

IN

LIKE

『注』：

按照功能，四类SQL语句（SELECT、INSERT、UPDATE、DELETE）的关键字可以划分为三类：

主关键字、次关键字、一般关键字。

5、非系统保留字（如字段名、数据表名、标点符号）相对本级保留字再缩进一级。

多个连续的非保留字可以分行书写，也可以写在同一行。

6、WHERE包含的条件子句较复杂时，应该每行只写一个条件分句，并为重要的条件字句填写单行注释。

7、在保证基本缩进格式的前提下，通过对齐某些重要关键字（如条件关键字AND、OR，符号=、<>等）来进一步提高文本的易读性和可维护性。

8、拆行：

最大的长度应在70～80个字符之间。

对于特别长的行，拆分缩进，把运算符（如条件关键字AND、OR，符号=、<>等）放在前边。

4.2字体

1、系统保留字应大写，包括系统公共变量等。

2、其他字符（如用户自定义变量、用户自定义数据对象名）小写。

3、需要特殊强调的部分可以大写。

4、常量采用大写。

4.3注释规范

注释可以包含在包含T-SQL语句的对象中。

在视图、用户自定义函数、存储过程、触发器中包含描述性注释将大大增加文本的可读性和可维护性。

本规范建议：

1、注释说明部分以中文为主。

2、注释尽可能详细、全面。

创建每一数据对象前，应具体描述该对象的功能和用途。

传入参数的含义应该有所说明。

如果取值范围确定，也应该一并说明。

取值有特定含义的变量（如boolean类型变量），应给出每个值的含义。

3、注释应准确，易懂，不要写缩写。

4、代码清晰的时候，不加注释。

5、注释语法包含两种情况：

单行注释、多行注释

●单行注释：

注释前有两个连字符（--），最后以行尾序列（CR-LF）结束。

对变量、条件子句采用该类注释。

●多行注释：

符号/*和*/之间的内容为注释内容。

对某项完整的操作使用该类注释。

6、多重（欠套）循环，在段落的开始加注释。

4.4对象注释

编写对象文本时，必须为每个对象增加适当注释。

该注释以多行注释为主，主要结构如下：

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

*name:

--对象名称

*describe:

--功能描述

*input:

--输入参数

*output:

--输出参数

*author:

--作者

*CreateDate:

--创建时间

*UpdateDate:

--更改信息（包括作者、时间、更改内容等）

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

CREATEPROCEDUREUp_GetMaxPzbh

…

【注意】

1、修改代码时，总是使代码周围的注释保持最新。

即写代码，写注释；改代码，改注释。

2、避免在代码行的末尾添加注释；除非在批注变量声明时，在这种情况下，将所有行尾注释在公共制表位处对齐。

3、为了防止问题反复出现，对错误修复和解决方法代码总是使用注释。

4、用空白将注释同注释分隔符分开。

在没有颜色提示的情况下查看注释时，这样做会使注释很明显且容易被找到。

4.5其他编程规范

1、空行：

在每个独立代码体之间使用空行。

2、分割标识符

符合所有标识符格式规则的标识符可以使用分隔符，也可以不使用分隔符。

不符合标识符格式规则的标识符必须使用方括号（[]）或双引号（""）对其进行分隔。

3、所有字符、字符串、二进制数据和Unicode常量都使用单引号。

4、使用圆括号表示复杂数学计算的执行顺序。

5、变量赋值

若要为变量赋值，请使用SET语句。

也可以通过SELECT语句的选择列表中当前所引用值为变量赋值。

6、AS的使用：

指定别名的时候用AS代替所有的空格。

7、在存储过程和用户自定义函数中，如果用到其他库的Table或View，务必在当前库中建立View来实现跨库操作，最好不要直接使用“databse.dbo.table_name”，因为sp_depends系统存储过程不能显示出该SP所使用的跨库table或view，不方便校验。

8、在提交存储过程之前，除了前期的优化检验之外，建议使用setshowplanon分析过查询计划，做自身的查询优化检查。

9、SQL优化注意事项：

1）尽量避免大事务操作，慎用HOLDLOCK子句，提高系统并发能力。

2）尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接。

3）尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。

4）注意WHERE字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小。

5）不要在WHERE子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

6）尽量使用EXISTS代替SELECTCOUNT

（1）来判断是否存在记录，count函数只有在统计表中所有行数时使用，而且COUNT

（1）比COUNT（*）更有效率。

7）注意一些OR子句和UNION子句之间的替换

8）注意表之间连接的数据类型，避免不同类型数据之间的连接。

10、tempdb创建临时表的使用规范：

1）尽量避免使用DISTINCT、ORDERBY、GROUPBY、HAVING、JOIN、COMPUTE，因为这些语句会加重tempdb的负担。

2）避免频繁创建和删除临时表，减少系统表资源的消耗。

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

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

5）如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先TRUNCATETABLE，然后DROPTABLE，这样可以避免系统表的较长时间锁定。

6）慎用大的临时表与其他大表的连接查询和修改，减低系统表负担，因为这种操作会在一条语句中多次使用tempdb的系统表。

11、建议存储过程以RAISERROR和RETURN返回。

在每个SQL执行语句（UPDATE，DELETE，INSERT）之后要检查@@ERROR。

【实例】

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

*name:

--Up_Get_Key

*describe:

--获取键值

*input:

--@name,@inc

*output:

--@code

*author:

--XX

*CreateDate:

--2002-12-12

*UpdateDate:

--更改信息（包括作者、时间、更改内容等）

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

CREATEPROCEDUREUp_Get_Key

@nameVARCHAR（50）,--名称

@incINTEGER--？

AS

/*获取键值表中对应名称的键值*/

SELECT@code=CODE_

FROMkey_table

WHERENAME_=@name

/*键值加1，修改相应键值表*/

IF@inc=1

BEGIN

SET@code=@code+1

UPDATEkey_table

SETCODE_=@code

IF@@ERROR<>0

SET@code=-1

END

/*返回键值，如果执行失败返回-1*/

RETURN@code

5错误处理

5.1@@ERROR

如果最后的Transact-SQL语句执行成功，则@@ERROR系统函数返回0；如果此语句产生错误，则@@ERROR返回错误号。

每一个Transact-SQL语句完成时，@@ERROR的值都会改变。

@@ERROR可用以下方法处理：

在Transact-SQL语句后，马上检测或使用@@ERROR。

以下为SQLServer系统常用的返回状态码：

值

含义

0

Procedurewasexecutedsuccessfully

-1

Objectmissing

-2

Datatypeerroroccurred

-3

Processwaschosenasdeadlockvictim

-4

Permissionerroroccurred

-5

Syntaxerroroccurred

–6

Miscellaneoususererroroccurred

-7

Resourceerror,suchasoutofspace,occurred

-8

Non-fatalinternalproblemencountered

-9

Systemlimitwasreached

-10

Fatalinternalinconsistencyoccurred

-11

Fatalinternalinconsistencyoccurred

-12

Tableorindexiscorrupt

-13

Databaseiscorrupt

-14

Hardwareerroroccurred

在程序中，用return语句可指定大于0或小于-99的返回值，调用程序可以设置局部变量接收和检查返回状态。

5.2关于RAISERROR

SQLSERVER系统的RAISERROR命令能够把某个出错情况返回给调用过程，这对说明调用过程的执行情况很有必要；同时可以部分避免客户端的冗余操作。

另外，结合系统存储过程sp_addmessage和sp_dropmessage可以方便实现数据对象在SQLSERVER端的国际化。

SQLSERVER的MASTER数据库中有错误信息数据表sysmessages，专门用于存储系统和用户的错误提示及相关信息（如错误ID号、错误等级、状态）。

用户可以调用sp_addmessage和sp_dropmessage预先将各类错误信息记入该数据表。

其中，不同的错误信息用错误ID号区分。

在编写存储过程代码时，调用RAISERROR函数从错误信息表sysmessages中引用相关错误ID号的错误信息。

由于0~50000的值是保留为SQLSERVER使用的，所以用户自定义错误信息的错误ID号必须大于50000。

RAISERROR的语法如下：

RAISERROR（{msg_id|msg_str},severity,state）

本规范建议存储过程以RAISERROR和RETURN返回。

举例如下：

第一步：

生成该错误信息

/*insertaerrormessageintothemaster..sysmessages*/

sp_addMessage50001,16,'Can"tupdatetheprimarykeyfromtable%s'

第二步：

执行存储过程up_xxx，异常返回时引用上述错误信息

CREATEPROCEDUREup_xxx

BEGIN

…

RAISERROR（50001,16,1,'r_a'）--Can"tupdatetheprimarykeyfromtabler_a

ROLLBACKTRAN

RETURN

（1）

END

【注】：

[RAISERROR（'Can"tupdatetheprimarykeyfromtable%s',16,1,'r_a'）--Can"tupdatetheprimarykeyfromtabler_a]——不使用sp_addMessage的用法

第三步：

在PB调用中，

DECLAREcheckPROCEDUREFORup_xxxUSINGSQLCA

EXECUTEcheck;

MessageBox（"错误",SQLxmxt.SQLErrT