DB2 命名开发规范.docx

1、DB2 命名开发规范For DB2DB Development-NamingSpecification2012/01/01Author(作者)：Jet LeeDate：Checked by(审核)：Date：Change History (变更历史)DateVersionAuthorDescription of change2012-6-121.0Jet Lee新起草2012-6-151.1Jet Lee文档拆分成两个Content1 Introduction (引言) 61.1 Purpose(规范的目的) 61.2 Readers (阅读的人员) 61.3 Glossary (术语) 61

2、.4 Reference (参考资料) 62 Unified criterion for Naming (统一的命名规范) 62.1 Overall Principle (整体性原则) 62.2 Database alias (数据别名) 72.3 Object Schema (对象概述) 72.4 Table(表) 72.4.1 Table Name(表名称) 72.4.2 Table Index(表索引) 72.4.3 Table Field(表字段) 72.5 View(视图) 82.6 Stored procedure(存储过程) 82.7 Function(函数) 82.8 Vari

3、ant or Parameter(变量 & 参数) 83 Unified criterion for Development (统一的开发规范) 93.1 Statement (语句) 93.1.1 Overall Principle (整体性原则) 93.1.2 Nesting (语句嵌套) 93.2 Comments(备注) 93.2.1 Field(字段) 93.2.2 Parameter (参数) 93.2.3 Function(函数) 103.2.4 Procedure(过程) 103.2.5 View(视图) 103.2.6 Key Arithmetic (关键算法) 113.3

4、Create Table(表创建) 113.3.1 Table Index(索引) 113.3.2 删除Paradigm Requirement (范式要求，原则上属于设计要求) 113.4 View(视图) 113.5 Function (函数) 113.6 Procedure (过程) 123.7 Trigger (触发器) 133.8 Isolation Level (隔离集使用) 134 Attachment (附件) 134.1 删除，系统设计的范畴Table Normal Form 134.2 Isolation Level 131 Introduction (引言)1.1 Pur

5、pose(规范的目的)Guide the developer how to name the DB objects (Tables, views, stored procedure, etc)(指引开发人员如何去规范对DB中对象进行命名)；1.2 Readers (阅读的人员) Developer, the members of the review board, Development Manager；(开发人员；Review board成员；开发经理)；1.3 Glossary (术语) Table Normal form: Refer the attachment;表的范式：参考附件；I

6、solation Level: Refer the attachment;隔离级：参考附件；1.4 Reference (参考资料)None2 Unified criterion for Naming (统一的命名规范)2.1 Overall Principle (整体性原则) The characters range for all names: A-Z, a-z, 0-9, _ (Underscore). Not allowed to use other characters as the name. (所有名称的字符范围为：A-Z, a-z, 0-9 和_ (下划线) 。不允许使用其他字

7、符作为名称). Please use English words or English phrases (including abbreviations) as name, but cannot be used as the name of meaningless characters or Chinese Pinyin. (采用英文单词或英文短语（包括缩写）作为名称，不能使用无意义的字符或汉语拼音。) Name should be clarity, the ability to accurately express the meaning of things, and readable. P

8、lease follow the principle of see to know the meaning. (名称应该清晰明了，能够准确表达事物的含义，最好可读，遵循“见名知意”的原则。) Database objects need to add the necessary comments, such as Tables, procedures, functions, etc. Express the contents of the comments in your mother language. (数据库对象需要添加必要的注释，如表、存储过程、函数等，注释使用母语；) Accordin

9、g to the character of the DB2 naming management, we all use the uppercase of the letter in create the following objects: Procedure, table, fields, view or functions. (根据DB2系统本身对对象名称管理特点，我们在创建表、视图，函数，过程，字段过程中全部使用大写。)Sample:CREATE FUNCTION SFC.F_SHOW_FIELD_NAME () RETURNS INT ASBEGIN RETURN (DBO.F_SHO

10、W_FILED_QUANTITY() END2.2 Database alias (数据别名) Just follow the principle of see to know the meaning. (只要遵循“见名知意”的原则。)Sample:PROM, MASII;2.3 Object Schema (对象概述) Keep the schema short as you can as possible. 3 to 6 letters are available. All the letters should be capitalized. (请尽量简短 (3至6个字母) ，建议使用包括

11、意义的缩写，需要全大写。)Sample:SFC (该schema下的所有表或对象是与SFC相关的)LPC2.4 Table(表)2.4.1 Table Name(表名称) Use not more than three words to construct the table name. When the length of a single word is more than eight, not more than 4 letters can be chosen to represent the whole words meaning. In the combination name, a

12、ll the letters of every word should be capitalized. Use the “_” to space every word in the whole name. Except the single word for the table name, all the words cannot be plural in the combination name. (请使用不多于三个单词的组合构建一个表的名称。当某个单词字母较多（超过8个字母时），请选择其中四个关键字作为它的代表含义；每个单词之间使用“-”分隔开；组合单词不使用复数，但单个单词则可以。)Sa

13、mple:DELIVER_CSTM_ORDER (递交客户订单)ORDERS (订单表)2.4.2 Table Index(表索引) Use the system default naming rule for the table index. Dont change it. (在定义表的索引时（Primary Key, Unique, etc），请使用系统默认的命名，无须去改动它。)2.4.3 Table Field(表字段) Use not more than three words to construct the table name. When the length of a sin

14、gle word is more than eight, not more than 4 letters can be chosen to represent the whole words meaning. Use the “_” to space every word in the whole name. (请使用不多于三个单词的组合构建一个字段的名称。当某个单词字母较多时（超过8个字母），请选择其中不多于四个关键字作为它的代表含义；组合的单词中，每个单词之间以下划线间隔开)；Sample:SCAN_DATE: 扫描日期CSTM_ORDER_ID: 客户订单ID2.5 View(视图) R

15、efer the naming rule of the table. We should add the capitalized “V_” to differentiate the table name. (命名规则请参考“Table Name”，为了与之相区别，在此前面加大写V_进行区分)Sample: V_ORDERS (订单)V_SHOP_SCAN (车间扫描)2.6 Stored procedure(存储过程) Refer the naming rule of the table. We should add the capitalized “P_” to differentiate

16、the table name. (命名规则请参考“Table Name”，为了与之相区别，在此前面加大写P_进行区分。)Sample: P_GET_SHOP_SCAN (获取车间扫描数据)P_SEND_MAIL () (发送邮件通知)2.7 Function(函数) Refer the naming rule of the table. We should add the capitalized “F_” to differentiate the table name. (命名规则请参考“Table Name”，为了与之相区别，在此前面加大写F_进行区分。)Sample: F_CHECK_CO

17、A (COA检查)F_VIEW_FIELD_NAME (显示字段名)2.8 Variant or Parameter(变量 & 参数) Follow the field naming rule: (遵循字段的命名方式)Sample: ORDER_NUMBER 订单编号;2.9 Job(作业任务) Meet the principle of see to know the meaning. Not more than 8 words in the combination. The shorten name is not available. All the letters should be c

18、apitalized. The job executing frequency should be added to the name. (符合见名知意的原则，不多于8个单词组合的名称，不使用缩写；所有字母需要大写；在名称后面加入该JOB的执行频率);Sample:IMPORT_CUSTOMER_ORDER5 Min2.10 User defined data type(用户自定义数据类型) Follow the table naming rule. Add the prefix “UDT_” to the name. (Follow 表的命名规则，前面加“UDT_”)；Sample:UDT_

19、PHONE_NUMBER (自定义一个电话号码的数据类型);3 Unified criterion for Development (统一的开发规范)3.1 Statement (语句)3.1.1 Overall Principle (整体性原则) In the programming, for reducing the input workload reason, all statement which involves the following objects: Table, view, function, procedure, schema, etc, should be upperc

20、ase. (在编程过程中，为减少录入切换工作量起见，对于语句中的手工录入的关键字、语句以及涉及到的数据库对象 (Table, View, Procedure, Function, Schema) 等等，全部使用大写。)Sample:SELECT FIELD_NAME, FIELD_TYPE FROM TABLE_LIST WHERE UPDATE TABLE_LIST SET FIELD_NAME=XXX WHERE EXEC P_LOAD_MACHINE_LIST ()3.1.2 Nesting (语句嵌套) In DB2, we encourage use the nesting stat

21、ement. But for the ease to understand, the depth of the nesting should not be more than two tiers. All the alias used in the statement should be varies to each other. (在DB2中，鼓励使用查询的嵌套语句，但是为了可读性起见，嵌套的深度不要过两层以上。同时嵌套语句中使用的别名都应该各不相同。)SELECT * FROM (SELECT FILED_NAME, FIELD_TYPE FROM (SELECT FROM TABLE_L

22、IST) T ) A, ALL_OBJECTS B WHERE 上面绿字部分为第一层；桔黄色部分为第二层； But in the permitted tiers, we dont care how many times you use the nesting. (但在允许的层级上，使用多少次嵌套，不进行限制。)3.2 Comments(备注)3.2.1 Field(字段) Make comments for the field as possible as you can. As to the simplified words included in the field name, you m

23、ust the give clear explanation for it.（请尽量对表中所有字段进行备注；对于使用了简化单词组合成的字段，请必须说明。)Sample:FILE_NAME : 文件名 （这个简单明了，不一定要备注上去）.CSTM_NAME_CONVERT: 客户名称转换 (这个必须要备注，其中有CSTM是使用了简化单词的，请解释明白。3.2.2 Parameter (参数) To the function or procedure, we should add the comments for the entry parameters. (对于函数或存储过程，在它们的过程体前面

24、添加对各个入口参数的解释.)Sample:P_LOADMACHINESCAN (IN ORDER_NUMBER VARCHAR (20), IN INPUT_DATETIME TIMESTEMP)/* FUNCTION NAME: P_ LOADMACHINESCAN. 此过程用于载入某个批次所有主机的扫描记录； PARAMETER LIST: ORDER_NUMBER 用户输入的工单号; (输入) INPUT_DATETIME 用户选择的扫描时间范围; (输入)*/3.2.3 Function(函数) Associate with the parameters; add the commen

25、ts in the head of the function. (请连同参数一起，在过程体前面添加对该函数的作用进行解释).Sample:F_SHOW_FIELD_NAME (TABLENAME VARCHAR (50)/* FUNCTION NAME: F_ SHOW_FIELD_NAME. 此函数用于显示指定表的所有字段名称； PARAMETER LIST: TABLENAME 用户输入的表名字; （输入）*/3.2.4 Procedure(过程) Refer the function. (请参考”函数”)；Sample:P_LOAD_MACHINE_SCAN (IN ORDER_NUMB

26、ER VARCHAR (20), IN INPUT_DATETIME TIMESTEMPT)/* PROCEDURE NAME: P_LOAD_MACHINE_SCAN. 此过程用于载入某个批次所有主机的扫描记录； PARAMETER LIST: ORDER_NUMBER 用户输入的工单号; (输入) INPUT_DATETIME 用户选择的扫描时间范围; (输入)*/3.2.5 View(视图) Add the necessary explanation to the view: The related the table and other view name, and function

27、name. (请在语句体前面加入对该视图的必要说明：该视图所涉及的表，或其它视图的名称，所引用的函数名称等等)； Sample: CREATE VIEW SFC.V_ORDERS AS/*VIEW NAME: V_ORDERS此视图主要用于计算内部客户订购公司产品的联合价格； 该视图引用： ORDERS (订单表) INNER_CSTM (内部客户表) F_COMBINE_OUTPUT (计算联合价格函数)*/ SELECT A.ORDER_NUMBER, B.CSTM_ID, DBO.F_COMBINE_OUTPUT (A.PRICE, B.QUANTITY) AS COMBINE_OUTP

28、UT FROM ORDERS A, INNER_CSTM B WHERE A.ORDER_NUMBER=B.ORDER_NUMER3.2.6 Key Arithmetic (关键算法) (No matter the procedure or function, add the comments to the position where you want think it is difficult to understand. (不管是存储过程，还是函数，在某些语句的表达上，如果认为较复杂，或作必要说明的，请在该语句上面添加备注。) /*根据订单表与内部客户表联合，通过函数F_ COMBINE

29、_OUTPUT计算得到联合价格更新到订单表的价格*/ UPDATE A SET A.PRICE=DBO.F_COMBINE_OUTPUT (A.PRICE, B.QUANTITY) FROM ORDERS A, INNER_CSTM B WHERE A.ORDER_NUMBER=B.ORDER_NUMER.3.3 Create Table(表创建)3.3.1 Table Index(索引) Not more than 3 fields can be combined to the primary key. As to the field whose width is longer than 2

30、0 characters, it cannot be set as the primary key. (Primary Key的索引组合不要超过三个字段；字段长度超过20字符的，不能应用于Primary Key的组合)； The index should be created for the filed that is used frequently. (对于频繁使用某个字段进行检索该表的，应当在该字段上创建索引)； Not more than 3 indexes exist in a single table (In addition to the primary key). (一个表（除了

31、Primary key）索引数量不应当超过三个)；3.3.2 Paradigm Requirement (范式要求，原则上属于设计要求) (Except for the data warehouse building, all the table creating should meet the DB 1NF requirement.) 除非进行数据仓库的建设，否则所有任何表至少需要符合第一范式；3.4 View(视图) For saving the workload, nesting views is available. But, for the ease to understand, the nesting statement should not be more than three tiers. (为了节省代码的编写，允许使用嵌套视图，但不应该超过三层。)Sample:CREATE VIEW V_ORDERS _DELIVER AS /*第一层*/ SELECT * FROM V_ORDER