[计算机软件及应用]Oracle数据库开发规范.doc

《[计算机软件及应用]Oracle数据库开发规范.doc》由会员分享，可在线阅读，更多相关《[计算机软件及应用]Oracle数据库开发规范.doc（31页珍藏版）》请在三一办公上搜索。

1、ORACLE数据库开发规范 ORACLE数据库开发规范注：本规范基于ORACLE10G。对于其他数据库以及ORACLE数据库其他的版本，本规范并不一定合适。目录目录31：字符集、字段类型411：字符集412：字段类型42:命名规范621：命名规则表622：命名规范：73:书写规范931：缩进风格与对齐932：语句书写规则933：其他114：表135：触发器146：主键和外键：157：索引1671：建立索引1672：使用索引168：视图189：存储过程和函数1910：注释规范22101：总则22102：存储过程、函数头注释22103：文件注释2311：脚本规范2512：SQL语句性能优化2713

2、：冗余信息3114：异常处理32附录33数据库三个范式331：字符集、字段类型11：字符集ORACLE的数据库实例有2个字符集设置：数据库字符集和国家字符集。l 【规则1-1-1】数据库字符集设置为ZHS16GBK，国家字符集设置为AL16UTF16。l 【规则1-1-2】不要试图修改数据库的字符集。如果需要不同字符集的数据库实例，那只能重新建立一个新的数据库实例。12：字段类型l 【规则1-2-1】定长文本型字段都要声明char，不定长文本型字段都要声明成varchar2（如果确实需要，也可以使用nvarchar2）类型。 例如：Create table t_test (name varch

3、ar2(10), department nvarchar2(20);请注意，在ZHS16GBK字符集下，每一个汉字是占用2个字节，而每一个英文字母和符号占用一个字节。上面的name字段可以最多存储5个汉字，或者是10个字母。而department字段可以最多保存20个字符，不论是汉字或者字母。 也可以如下声明：Create table t_test (name varchar2(10 char), department varchar2(20 char);这样的话，不论是汉字或者是字母，name字段就可以保存最多10个字符，。l 【规则1-2-2】所有的日期型都要声明成文本型，形同：yyyyM

4、Mdd。其中，yyyy是4位数的年份，MM是两位数的月份，dd是两位数的日期。举例如下：20050112表示2005年1月份12日l 【规则1-2-3】所有的时间型都要声明成文本型，形同：hh24:mi:ss。其中，hh24表示是24进制的小时，mi是分钟，ss是秒数。 举例如下：11:12:13表示11点12分13秒。l 【规则2-2-3】如果一个字段需要同时包含日期和时间，需要分成日期型和时间型两种类型的字段进行设计。l 【规则1-2-4】bool型字段一律定义成char(1)类型，0 表示false，1 表示true。同时需要在该字段上面加约束，使合法的取值只能是0，1。l 【规则1-2

5、-5】所有的整型字段都应该声明为 number(n)，n为整型字段有效长度。 比如 Create table t_test (age number(3)字段age能存储从-999 到 999的数字。当然，可以在这个字段上加约束，以约束它只能保存正数。不要如以下的方式定义整型：Create table t_test (age number)。因为这样定义等价于：Create table t_test (age number(38)很显然，这个字段定义得实在太大太大了。不但没有必要，而且对数据库有负面影响。l 【规则1-2-6】所有的浮点数定义为：number(p,s),其中的p是表示精度，或者总

6、位数，取1-38中数字。S是小数位数，合法值为：-84-127。 比如:Create table table1 (salary number(8,2)这个salary字段表示，精确到小数点后面2位，总共最多有8位数字，这样算来，在小数点前面，最多能有6位数字。这个字段能表示的最大正数是999999.99，最大负数是-999999.99。l 【规则1-2-7】如果一个字段保存的是一系列的编码（枚举型），则该字段必须定义成文本型。字段中保存的是大写字母。 举例如下：Customer表格有一个字段customer_type，该字段对应的是客户的类型编码，字段类型是char(1)，其中，N表示普通客户

7、，V表示VIP客户。同时必须增加约束，以保证枚举选择以外的值不能被插入。l 【规则1-2-8】如无特殊需求，避免使用大字段(blob, clob, long, text, image 等)。如果需要使用大字段，可以先考虑Blob。2:命名规范21：命名规则表对象名前缀范例描叙数据库实例无sale_test表空间ts_ts_user表tablet_t_Customer表名原则上不超过24个字符分区表t_+分区描叙t_system_items_0612视图viewv_v_ Customer视图名原则上不超过24个字符序列sequences_seq_ t_Customer_id约束ckc_ckt_c

8、kc_ t_Customer_idckt_ t_Customer对字段的约束前缀为ckc.对表的约束前缀为ckt一般索引idx_idx_sample_mid位图索引mapidx _ mapidx_sample_mid唯一索引unidx_ unidx_sample_mid分区表本地索引ln_ ln1_system_item_分区表全局索引gn _ gn_system_item主键pk_ak_pk_ t_Customerak_ t_Customer存储过程up _up_InsAgentAccount函数uf _uf_SelMaxData包和包体pkg _pkg_MngClt物化视图mv _mv_

9、Customer数据库联接from_from_ sale_test保存点spt_spt_wrp变量自定义记录类型rec_Type rec_item is ( item_id varchar2(10);游标cur_Cursor cur_items is局部变量l_l_item输入变量i_i_itme输出变量o_o_itme输入输出变量io_io_item22：命名规范：l 【规则2-2-1】所有用户自定义的数据库对象名统一使用小写字母。l 【规则2-2-2】命名尽量采用富有意义的英文词汇，不要采用汉语拼音。 示例：以下书写不符合规范t_wl_item (物料编码表) 应如下命名：t_mtl_it

10、em l 【规则2-2-3】所有名称采用英文单数名词或动词，避免出现复数。l 【规则2-2-4】如果键是主键，用表格名再加前缀pk_的方式命名。如果长度超过30个字符，则对表格名进行缩写。l 【规则2-2-5】如果键是备选主键，用表格名再加前缀ak_的方式命名。如果长度超过30个字符，则对表格名进行缩写。l 【规则2-2-6】如果是主键字段，统一命名为：Id；如果是保存其他表上的主键字段则用缩写加“Id”的方法命名。举例如下：l 销售订单的编号字段命名：SalOrdId；主键则命名为：Id。l 【规则2-2-7】存储过程、函数、触发器、程序块中定义的变量和输入、输出参数在命名上有所区分。 变量

11、名小写，局部变量名使用“l_”开头。用i_ 前缀代表输入参数变量，用o_ 前缀代表输出参数变量，用io_ 前缀代表输入输出参数变量。l 【规则2-2-8】当一个单词不能够表达对象的函义时，用词组组合，如果组合太长时，采用简写或缩写，缩写要基本能表达原单词的意义。标识符应当直观且可以拼读，可望文知意。 例如，下面的命名就很合适l_minValue number;l _maxValue number;procedure up_setValue();l 【规则2-2-9】变量和参数用小写字母开头的单词组合而成 示例：l _itemNumber number;l 【规则2-2-10】变量的名字应当使用

12、“名词”或者“形容词名词” 示例l _itemValue number;l 【规则2-2-11】存储过程名字应当使用“动词”或者“动词名词”（动宾词组） 示例procedure up_setValue ();l 【规则2-2-12】当一个单词不能够表达对象的函义时，用词组组合，如果组合太长时，采用简写或缩写，缩写要基本能表达原单词的意义。原则上首先要考虑的是需要清晰的表达命名所要表达的含义，其次才是考虑长度不要太长。l 【规则2-2-13】分区表的表名可以遵循普通表的正常命名规则。l 【规则2-2-14】按时间范围分区(每有一个分区)，分区名字为表的主要用途的缩写+下划线+yymm。示例：库存

13、交易分区表的分区命名可以采用如下方式： 示例trans_0611 、trans_0612、trans_0701 、trans_0702 l 【规则2-2-15】最小分区名字为before_data ,最大分区名字为after_data .3:书写规范31：缩进风格与对齐l 【规则3-1-1】：程序块采用缩进风格书写，保证代码清晰易读，风格一致，缩进格数统一为4个。l 【规则3-1-2】同一条语句占用多于一行时，从第二行开始第一个关键字应当空4格。 示例：select field1,field2, from t_tablename where field1 1l 【规则3-1-3】对于Inser

14、t values 和update 语句，一行写一个字段，这段后面紧跟注释（注释语句左对齐），values 和insert 左对齐，左括号和右括号与insert、values 左对齐。 示例：insert into t_user ( user_id, -用户ID， 主键 user_name, -用户名 login_name -登录名) values ( i_user_id, i _user_name, i _login_name )32：语句书写规则l 【规则3-2-1】一行代码只做一件事情，如只定义一个变量或几个同类型且密切相关的变量，或只写一条语句。这样的代码容易阅读，并且方便于写注释。 示

15、例：以下书写不符合规范：l_count := 1; l_creation_date : = sysdate; 应写成：l_count := 1; l_creation_date := sysdate;l 【规则3-2-2】建议：如果一行能将SQL语句全部容纳下，可以把语句放在一行，否则句子要在关键字和谓词处适当换行，以增加可读性。 示例：稍微短点的SQL语句可以这样写：select duty_id,duty_name from sm_duty where duty_id = :duty_id 长的应该写成: select duty_id, date, duty_name from t_duty

16、 where duty_id = :duty_id and date 20010206;l 【规则3-2-3】：begin、end 、if、else等关键字独立成行 示例：以下不符合规范begin null; exception when others then null; end; 应写成：begin 。; exception when others then 。; end;l 【规则3-2-4】参考（3-2-4）if 后的条件要用括号括起来，括号内每行最多两个条件。 示例：if (v_count = 1 or v_count = 2or v_count = 5 or v_count = 6

17、 ) thenselect sysdate into l_date from dual; end if;l 【规则3-2-5】不同类型的操作符混合使用时，使用括号进行隔离，以使代码清晰。 示例：以下书写不符合规范：if abc|def = abcdef then。; end if; 规范的写法是：if (abc|def) = abcdef then 。; end if;l 【规则3-2-6】 连接符OR、IN、AND、以及、=等前后加上一个空格。l 【规则3-2-7】不要将空的变量值直接与比较运算符(符号)比较。如果变量可能为空，应使用is null 或is not null 或nvl 函数进

18、行比较。 示例：以下代码不符合规范，会得到错误结果 if v_user_name = null then dbms_output.put_line(user name is null); end if; 应该如下书写: if v_user_name is null then dbms_output.put_line(user name is null);end if;l 【规则3-2-8】避免使用select * 语句。说明：不要用*来代替所有字段，应给出字段列表，注：不包含select coun(*). 示例：以下不符合规范：select * from t_duty 应如下书写：select

19、 duty_id,duty_name,creation_date,created_by from t_duty l 【规则3-2-9】确保变量和参数在类型和长度与表数据列类型和长度相一致。为了保证一致，定义参数的类型尽量使用锚定的声明。 示例 declare v_user_name t_user.fu.user_name %type；; begin select user_name into l_user_name from t_user where user_id = i_user_id; endl 【规则3-2-10】常量定义在包头部，并使用大写命名。 比如，圆周率： PI number(

20、8,7) := 3.1415926;33：其他l 【规则3-3-1】避免隐式的数据类型转换。说明：在书写代码时，必须确定表的结构和表中各个字段的数据类型，特别是书写查询条件时的字段就更要注意了。 示例：以下代码不符合规范，status_type 是number 型数据. select wip_entity_id from t_discrete_jobs where status = 3; 应如下书写：select wip_entity_id from t_discrete_jobs where status = 3; l 【规则3-3-2】:对于非常复杂的sql(特别是多层嵌套，带子句或相关的

21、查询)，应该先考虑是否设计不当引起的，对于复杂的一些sql 可以考虑使用程序实现，原则上遵循一句话只做一件事情。不要在数据层包含业务逻辑。 4：表【规则4-1】表的命名请参考第2节。【规则4-2】其索引所在空间为 表空间名+ index。 如：默认的表空间如果为ts_user，则所对应的索引空间为ts_user_index【规则4-3】每个表在创建时候，必须显式指定所在的表空间，不要采用默认表空间。对于事务比较繁忙的数据表，必须存放在在该表专用空间中。5：触发器【规则5-1】在数据库的开发中不要使用触发器。6：主键和外键：61：主键l 【规则6-1-1】主键命名规则请参考第2节。l 【规则6-

22、1-2】所有的数据表都要有主键。l 【规则6-1-3】尽量不让主键具有可更新性。在确定采用什么字段作为表的键的时候，小心用户将要编辑的字段。通常的情况下不要选择用户可编辑的字段作为键。l 【规则6-1-5】主键的选择,可以有下面3种：1：数据库的某个唯一性字段，如果是这个情况，必须非常小心。2：数据库序列。3：组合键。62：外键l 【规则6-2-1】外键命名规则请参考第2节。l 【规则6-2-2】数据模型上面需要有外键。l 【规则6-2-3】运行的生产数据库不要使用外键。数据的一致性可以由程序来保证。7：索引71：建立索引l 【规则7-1-1】索引的命名请参考第2节。l 【规则7-1-2】建议

23、：每个索引在创建时，必须指定索引专用表空间，不要采用默认表空间，以分离索引和数据。l 【规则7-1-3】只在需要的地方建立合适的索引，尽可能少建索引。在很多情况下，建立的索引是不会被优化器使用的。而无用的索引会严重影响数据库的性能。所以应该合适的选择建立索引的列。下面的情况一般认为比较合适建立索引：经常出现在where子句中的列上可以建立索引。选择性比较高的列上可以建立索引。l 【规则7-1-4】合适的建立复合索引，复合索引的第一个字段应该是在where子句中出现最频繁、选择性较好的列。l 【规则7-1-5】如果复合索引所包含的字段超过3 个，那么仔细考虑其必要性，考虑减少复合的字段。l 【规

24、则7-1-5】一般情况下不要使用函数索引。72：使用索引l 【规则7-2-1】任何对列的操作都可能导致全表扫描。这里所谓的操作包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等式的右边，甚至去掉函数。 如果在table1(col1)上面有一个索引，那么下面的查询语句select * from table1 where col1 + 2 = 10;就不会考虑使用索引，如果改写成下面的完全等价的写法： select * from table1 where col1 = 8;那么优化器会偏向与使用索引。l 【规则7-2-2】ORACLE提供了基于函数的索引。 create index tab

25、le1_idx1 on table1 (upper(col1); 这样，下面的查询就会倾向于使用索引。 select col3 from table1 where upper(col1) = ABC;l 【规则7-2-3】搜索条件中有“ is null ”、“！=”“not in”这样的条件，将肯定不会使用索引。所以如果可能的话应该尽量使用其他的等效写法来代替“ is null ”、“！=”“not in”这样的条件。l 【规则7-2-4】 使用复合索引且第一个索引字段没有出现在where 中时，建议使用hint 强制。 比如：如果在表emp中有一个索引id1 on emp (ename,de

26、ptno),针对deptno字段进行搜索一般是不会使用索引的。这个时候，可以使用下面的语法强制优化器使用索引。select /*+ index (emp id1) */* from emp where deptno = 2 请注意：这样的语法要比较慎重。刚开始的时候不要这样使用，一般只在数据库后期优化的时候可以这样使用。因为优化器一般来说是比较“聪明”的，它选择的一般情况下都是COST最小的路径，如果强制使用索引不当，有可能还会使性能下降。8：视图l 【规则8-1】视图的命名规则请参考第2节建立视图的目的1：为了达到权限控制。2：实现简单的外连接。3：简单常用的UNION。4：简化频繁使用的S

27、QL查询语句。l 【规则8-4】禁止将业务逻辑固定在视图中并联结过多的表。这样做实际上是一次将过多的业务层逻辑交给数据库去做，在开发阶段省了事，但是会给数据库性能以及后期的维护带来很大的麻烦。9：存储过程和函数在说到存储过程和函数的时候，先简单介绍一下包。Oracle中存储过程和函数的集合叫作包(Packages), 一个包分为 包头和包体, 包头定义了存储过程的名称和参数，包体除了名称和参数，还包存储过程的所有语句；几个存储过程可以组成一个包。示例：包头的定义create or replace package myTest_pkg is type cur_out is ref cursor;

28、 procedure writeCount(i_codeid in t.a type); procedure testSandyInSert(i_codeid in nvarchar2,o_counts out number); end myTest;包体的定义create or replace package body myTest_pkg is procedure writeCount(i_codeid in nvarchar2) is v_count number; begin m_count: = 0; select count(1) into v_countfrom code whe

29、re code_id = codeid_p; dbms_output.put_line(输入参数是|codeid_p); dbms_output.put_line(查询结果是|v_count); end; end writeCount;procedure testSandyInSert(i_codeid in nvarchar2,o_counts out number) is l _cur cur_out; l _code_sn nvarchar2(50); l code_id nvarchar2(50); l _for number: = 0; begin counts_p: = 0; op

30、en cur for select code_sn,i_code_id,code_name from code where code_id = codeid_p; loop fetch m_cur into l_code_sn, l _code_id,l _code_name; exit when m_cur %notfound; end loop/。close v_cur; end testSandyInSert;end myTest_pkg;l 【规则9-1】存储过程和函数的命名、书写规则请参考第一、二节l 【规则9-2】所有的函数和存储过程都必包含在某一个特定包中，不要产生游历于所有包以

31、外的函数或者存储过程。l 【规则9-3】定义参数的类型尽量使用锚定的声明。 %type 例如，定义一个变量l_date，它的类型与表job_history的start_date字段是相同类型（varchar2(8)），请不要这样定义：l_date varchar2(8),需要用如下的方式定义：l_date job_history.start_date%type，l 【规则9-4】存储过程或者函数的接口参数必须显示标明是传入参数还是传出参数，同时，传入参数加前缀i_，传出参数加前缀o_，传入传出参数加io_。 l 【规则9-5】存储过程中变量的声明应集中在as 和begin 关键字之间，不允许在

32、代码中随意定义变量，定义变量时，完成相同功能模块的变量应放在一起，与不同模块的变形量应空行隔开，增加代码的可读性。l 【规则9-6】事务必须显式提交或者取消，不要采用隐式提交或者自动提交。l 【规则9-7】当存储过程和函数有多个分支返回时，若有事务，需确保各个分支都结束事务。l 【规则9-8】对于存储过程、函数等程序块必须要有异常处理部分，以提高程序的自检能力，异常尽可能就近处理。 例如beginexceptionwhen excepname1 thenwhen excepname2 thenwhen others thenend;没有合适异常处理的代码是不允许出现在生产数据库中的。l 【规则

33、9-9】存储过程和函数代码中不要使用goto 语句。说明：很显然，使用goto语句是一种不极其好的编码风格。l 【规则9-10】存储过程和函数中要确保所的变量和参数都使用到。说明：申明变量等也要一定的系统开销，如果申明无用的变量，也显得代码不够严谨，而且它还会影响到任何后来阅读代码者的思路。l 【规则9-11】存储过程和函数不宜过大，一般程序行不要超过150行。过大的存储过程或者函数可以考虑拆分。l 【规则9-12】如果在函数或者存储过程中使用了动态SQL，则一定要使用绑定变量。详细请看demo中 8createPack pkg_some_business.sql的存储过程up_do_some

34、_business。l 【规则9-13】供别的文件或函数调用的函数，不应使用全局变量交换数据。10：注释规范101：总则l 【规则10-1-1】注释内容要清晰、明了、含义准确，防止注释二义性l 【规则10-1-2】禁止在注释中使用缩写，特别是非常用的缩写。说明：注释就是要写清楚给别人看的，如果使用缩写则不太容易表达清楚，而且有时候会有歧义。在使用缩写时或之前，应进行必要的说明。l 【规则10-1-3】一般情况下，源程序有效注释量须在30%以上。说明：注释的原则是有助于对程序阅读理解，在该加的地方都加了，注释不宜太多也不能太少，注释语言须准确、易懂、简洁。一般来说，30%的注释才能把程序大概说清

35、楚。所以30%应该是一个下限。102：存储过程、函数头注释l 【规则10-1-1】 存储过程、函数头的注释。 示例：使用以下书写规范：（注意：开发人员可以直接COPY这段注释到自己的代码中，然后进行相应内容条目的修改既可。）/* name: f_insert_user purpose:向sm_users 表中写入一条记录 revsions: ver date author description1.0.0 2006.10.01 Tang 1.创建此存储过程 1.0.1 2006.12.01 Tang 1.增加hint 1.2.0 2007.01.01 Tang 1.增加传入参数 2.增加用户名

36、校验过程 parameters: p_user_name in varchar2 新增用户名 p_password in varchar2 新增用户的加密密码 p_diabled_date in date 新增用户的过期时间 p_created_by in number 新增用户的操作人 p_password_cyle in number 密码更改周期 x_message out varchar2 执行异常时返回的错误信息 return: 0 成功-1 失败 notes: 1.本函在前台调用,系统后台会自动地为last_updated_by、 last_update_date、creation

37、_date 赋值. */ 说明：name:函数或过程的名称purpse:函数或过程的用途revisions 下面是版本信息ver:当前版本date:创建或修改日期author:创建人或修改人description:在修改时，一定要在这里写出改动的内容，有1、2、3 清晰列出来parameters:对传入和传出参数进行说明return:函数返回结果notes:使用该函数或过程时需要特别注意的事情，如果没有可以不写。 103：文件注释l 【规则10-3-1】所有变量定义需要加注释，说明该变量的用途和含义。l 【规则10-3-2】对存储过程、函数的任何修改，都需要在注释最后添加修改人、修改日期及修改

38、原因等信息。l 【规则10-3-3】对程序分支必须书写注释。说明：这些语句往往是程序实现某一特定功能的关键，对于维护人员来说，良好的注释帮助更好的理解程序，有时甚至优于看设计文档。l 【规则10-3-5】注释应与其描述的代码相似，对代码注释应放在其上方或右方(对单条语句的注释)相近位置，不可放在下面。 下面的书写就不规范。select user_name, disabled_dateinto v_user, v_disabled_datefrom sm_users su where su.user_id = p_user_id; -取得用户的失效时间 应该如下书写：-取得用户的失效时间sele

39、ct user_name, disabled_date into v_user_name, v_disabled_date from sm_users su where su.user_id = p_user_id; l 【规则10-3-6】注释与所描述的内容进行同样的缩排。l 【建议10-3-8】函数应对返回的代码进行详细描述。l 【建议10-3-9】凡是涉及到类型的参数，建议在注释中把类型说明全部逻列出来。l 【建议10-3-10】尽量使用”-” 进行行注释。l 【建议10-3-11】行尾注释须使用”-” 。l 【建议10-3-12】避免在一行代码或表达式的中间插入注释。l 【规则10-3

40、-14】注释用中文书写(考虑到开发人员英语水平的不同，为了提高代码以及注释的可读性，使用中文可以做到沟通代价最小)。l 【规则210-3-15】重复代码需要注释说明。11：脚本规范l 【规则11-1】所有脚本按内容分开存放，并按以下命名和存储：1：创建用户脚本，创建表空间脚本 创建表空间脚本 1createTablespace.sql 创建用户脚本 2createUser.sql2：创建业务表脚本，创建索引脚本 创建业务表脚本命名 3createTable.sql 创建索引脚本：4createIndex.sql3：创建临时表脚本 创建临时表脚本 5createTempTable.sql4：创建

41、数据库联结脚本 创建数据库联结脚本 6createDBLink.sql5：创建视图脚本 创建视图脚7createView.sql6：创建包脚本 创建一个目录：8createPack，在这个目录下面包含所有的创建包的脚本.包文件的文件名应该等于：本包的名字+.sql。每个包（包括存储过程和函数）创建脚本单独保存。 7：初始化数据脚本。 初始化数据脚本：9initData.sql9：创建作业脚本 创建作业脚本：10createJob.sql每一个脚本（或者目录）前面的数字说明了执行顺序。l 【规则112】 在规则【规则11-1】中的6：创建包脚本包脚本包含在8createPack目录下。对于这个目

42、录下的脚本，如果脚本数量不多，就不用建立子目录。如果脚本数量很多，可以按照功能分模块建立子目录，把相应的脚本放到对应的子目录。在附件的demo中，8createPack下面就有2个模块，一个是buy_future，一个是sale_future，下面分别存放了一些脚本。l 【规则11-2】创建每个对象代码的首部应该有对象注释。对于视图、临时表等对象，还应该注释清楚创建这个对象的目的，也就是这个对象实现了什么功能。 示例：-标准仓单表Create table table1 . 又如：-这个视图实际上是一个简单的表连接查询。-它组合了t_job_history，t_department表的相关信息。create view v_forquery as select t1.start_date,t1.end_date,t2.department_name from t_job_history t1,t_department t2 where t1.department_id = t2.id;12：SQL语句性能优化l 【规则12-1】避