oracle数据库两个表逻数据,Oracle数据库开发轨范

当前位置:我的异常网» 数据库 » Oracle数据库开发轨范

Oracle数据库开发轨范

www.myexceptions.net  网友分享于：2013-03-27  浏览：10次

Oracle数据库开发规范

ORACLE数据库开发规范

目录

目录3

1：字符集、字段类型4

1．1：字符集4

1．2：字段类型4

2:命名规范6

2．1：命名规则表6

2．2：命名规范：7

3:书写规范9

3．1：缩进风格与对齐9

3．2：语句书写规则9

3．3：其他11

4：表13

5：触发器14

6：主键和外键：15

7：索引16

7．1：建立索引16

7．2：使用索引16

8：视图18

9：存储过程和函数19

10：注释规范22

10．1：总则22

10．2：存储过程、函数头注释22

10．3：文件注释23

11：脚本规范25

12：SQL语句性能优化27

13：冗余信息31

14：异常处理32

附录33

数据库三个范式33

1：字符集、字段类型

1．1：字符集

ORACLE的数据库实例有2个字符集设置：数据库字符集和国家字符集。

【规则1-1-1】数据库字符集设置为ZHS16GBK，国家字符集设置为AL16UTF16。

【规则1-1-2】不要试图修改数据库的字符集。如果需要不同字符集的数据库实例，那只能重新建立一个新的数据库实例。

1．2：字段类型

【规则1-2-1】定长文本型字段都要声明char，不定长文本型字段都要声明成varchar2(如果确实需要，也可以使用nvarchar2)类型。

例如：

Create table t_test  (name varchar2(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个字符，。

【规则1-2-1】bool型字段一律定义成char(1)类型，'0 '表示false，'1 '表示true。同时需要在该字段上面加约束，使合法的取值只能是0，1。

【规则1-2-2】所有的整型字段都应该声明为 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))

很显然，这个字段定义得实在太大太大了。不但没有必要，而且对数据库有负面影响。

【规则1-2-3】所有的浮点数定义为：number(p,s),其中的p是表示精度，或者总位数，取1-38中数字。S是小数位数，合法值为：-84--127。

比如:

Create table table1 (salary number(8,2))

这个salary字段表示，精确到小数点后面2位，总共最多有8位数字，这样算来，在小数点前面，最多能有6位数字。这个字段能表示的最大正数是999999.99，最大负数是-999999.99。

【规则1-2-4】如果一个字段保存的是一系列的编码(枚举型)，则该字段必须定义成文本型。字段中保存的是大写字母。

举例如下：

Customer表格有一个字段customer_type，该字段对应的是客户的类型编码，字段类型是char(1)，其中，'N'表示普通客户，'V'表示VIP客户。同时必须增加约束，以保证枚举选择以外的值不能被插入。

【规则1-2-5】如无特殊需求，避免使用大字段(blob, clob, long, text, image 等)。如果需要使用大字段，可以先考虑Blob。

2:命名规范

2．1：命名规则表

对象名前缀范例描叙

数据库实例无sale_test

表空间ts_ts_user

表table业务模块_SYS_Customer表名原则上不超过24个字符

视图viewv_v_ Customer视图名原则上不超过24个字符

序列sequences_seq_ t_Customer_id

约束ckc_

ckt_ckc_ t_Customer_id

ckt_ t_Customer对字段的约束前缀为ckc.

对表的约束前缀为ckt

一般索引idx_idx_sample_mid

唯一索引unidx_ unidx_sample_mid

分区表本地索引ln_ ln1_system_item_

分区表全局索引gn _ gn_system_item

主键pk_

ak_pk_ t_Customer

ak_ t_Customer

存储过程stp _stp_InsAgentAccount

函数fnc _fnc_SelMaxData

包和包体pkg _pkg_MngClt

物化视图mv _mv_ 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_item

2．2：命名规范：

【规则2-2-1】所有用户自定义的数据库对象名统一使用大写字母。

【规则2-2-2】命名尽量采用富有意义的英文词汇，不要采用汉语拼音。

示例：以下书写不符合规范

t_wl_item (物料编码表)

应如下命名：

t_mtl_item

【规则2-2-3】所有名称采用英文单数名词或动词，避免出现复数。

【规则2-2-4】如果键是主键，用表格名再加前缀pk_的方式命名。如果长度超过30个字符，则对表格名进行缩写。

【规则2-2-5】如果键是备选主键，用表格名再加前缀ak_的方式命名。如果长度超过30个字符，则对表格名进行缩写。

【规则2-2-6】如果是主键字段，统一命名为：表名(去除业务模块_)+Id；举例如下：

用户表SYS_USER；主键则命名为：USERId。

【规则2-2-7】存储过程、函数、触发器、程序块中定义的变量和输入、输出参数在命名上有所区分。

变量名小写，局部变量名使用“l_”开头。用’i_’ 前缀代表输入参数变量，用’o_’ 前缀代表输出参数变量，用’io_’ 前缀代表输入输出参数变量。

【规则2-2-8】当一个单词不能够表达对象的函义时，用词组组合，如果组合太长时，采用简写或缩写，缩写要基本能表达原单词的意义。标识符应当直观且可以拼读，可望文知意。

例如，下面的命名就很合适

l_minValue   number;

l _maxValue   number;

procedure up_setValue(…);

【规则2-2-9】变量和参数用小写字母开头的单词组合而成

示例：

l _itemNumber   number;

【规则2-2-10】变量的名字应当使用“名词”或者“形容词＋名词”

示例

l _itemValue   number;

【规则2-2-11】存储过程名字应当使用“动词”或者“动词＋名词”(动宾词组)

示例

procedure up_setValue (…);

【规则2-2-12】当一个单词不能够表达对象的函义时，用词组组合，如果组合太长时，采用简写或缩写，缩写要基本能表达原单词的意义。原则上首先要考虑的是需要清晰的表达命名所要表达的含义，其次才是考虑长度不要太长。

【规则2-2-13】分区表的表名可以遵循普通表的正常命名规则。

【规则2-2-14】按时间范围分区(每有一个分区)，分区名字为表的主要用途的缩写+下

划线+yymm。示例：库存交易分区表的分区命名可以采用如下方式：

示例

trans_0611 、trans_0612、trans_0701 、trans_0702…

【规则2-2-15】最小分区名字为before_data ,最大分区名字为after_data .

3:书写规范

3．1：缩进风格与对齐

【规则3-1-1】：程序块采用缩进风格书写，保证代码清晰易读，风格一致，缩进格数统一为4个。

【规则3-1-2】同一条语句占用多于一行时，从第二行开始第一个关键字应当空4格。

示例：

select field1,field2,…

from t_tablename

where field1 > 1

【规则3-1-3】对于Insert … 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

)

3．2：语句书写规则

【规则3-2-1】一行代码只做一件事情，如只定义一个变量或几个同类型且密切相关的变量，或只写一条语句。这样的代码容易阅读，并且方便于写注释。

示例：以下书写不符合规范：

l_count  := 1; l_creation_date : =  sysdate;

应写成：

l_count  :=  1;

l_creation_date  :=  sysdate;

【规则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

where duty_id =  :duty_id

and date > ‘20010206’;

【规则3-2-3】：begin、end 、if、else等关键字独立成行

示例：以下不符合规范

begin null; exception when others then null; end;

应写成：

begin

。。。。。;

exception

when others then

。。。。;

end;

【规则3-2-4】参考(3-2-4)if 后的条件要用括号括起来，括号内每行最多两个条件。

示例：

if (v_count  =  1 or v_count  =  2

or v_count  =  5 or  v_count  =  6 )

then

select sysdate

into l_date

from dual;

end if;

【规则3-2-5】不同类型的操作符混合使用时，使用括号进行隔离，以使代码清晰。

示例：以下书写不符合规范：

if ‘abc’||’def’  =  ‘abcdef’ then

。。。。。;

end if;

规范的写法是：

if (‘abc’||’def’)  =  ‘abcdef’ then

。。。。;

end if;

【规则3-2-6】 连接符OR、IN、AND、以及＝、<=、>=等前后加上一个空格。

【规则3-2-7】不要将空的变量值直接与比较运算符(符号)比较。如果变量可能为空，应使用is null 或is not null 或nvl 函数进行比较。

示例：以下代码不符合规范，会得到错误结果

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;

【规则3-2-8】避免使用select * 语句。

说明：不要用*来代替所有字段，应给出字段列表，注：不包含select coun(*).

示例：以下不符合规范：

select * from t_duty

应如下书写：

select duty_id,duty_name,creation_date,created_by

from t_duty

【规则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;

end

【规则3-2-10】常量定义在包头部，并使用大写命名。

比如，圆周率：

PI number(8,7) := 3.1415926;

3．3：其他

【规则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;

【规则3-3-2】:对于非常复杂的sql(特别是多层嵌套，带子句或相关的查询)，应该先考虑是否设计不当引起的，对于复杂的一些sql 可以考虑使用程序实现，原则上遵循一句话只做一件事情。不要在数据层包含业务逻辑。

4：表

【规则4-1】表的命名请参考第2节。

【规则4-2】每个表在创建时候，必须显式指定所在的表空间，不要采用默认表空间。对于事务比较繁忙的数据表，必须存放在在该表专用空间中。

5：触发器

【规则5-1】在数据库的开发中尽量不要使用触发器。

6：主键和外键：

6．1：主键

【规则6-1-1】主键命名规则请参考第2节。

【规则6-1-2】所有的数据表都要有主键。

【规则6-1-3】尽量不让主键具有可更新性。在确定采用什么字段作为表的键的时候，小心用户将要编辑的字段。通常的情况下不要选择用户可编辑的字段作为键。

【规则6-1-5】主键的选择,可以有下面3种：

1：数据库的某个唯一性字段，如果是这个情况，必须非常小心。

2：数据库序列。

3：组合键。

6．2：外键

【规则6-2-1】外键命名规则请参考第2节。

【规则6-2-2】数据模型上面需要有外键。

【规则6-2-3】运行的生产数据库不要使用外键。数据的一致性可以由程序来保证。

7：索引

7．1：建立索引

【规则7-1-1】索引的命名请参考第2节。

【规则7-1-2】建议：每个索引在创建时，必须指定索引专用表空间，不要采用默认表空间，以分离索引和数据。

【规则7-1-3】只在需要的地方建立合适的索引，尽可能少建索引。

在很多情况下，建立的索引是不会被优化器使用的。而无用的索引会严重影响数据库的性能。所以应该合适的选择建立索引的列。

下面的情况一般认为比较合适建立索引：

经常出现在where子句中的列上可以建立索引。

选择性比较高的列上可以建立索引。

【规则7-1-4】合适的建立复合索引，复合索引的第一个字段应该是在where子句中出现最频繁、选择性较好的列。

【规则7-1-5】如果复合索引所包含的字段超过3 个，那么仔细考虑其必要性，考虑减少复合的字段。

【规则7-1-5】一般情况下不要使用函数索引。

7．2：使用索引

【规则7-2-1】任何对列的操作都可能导致全表扫描。这里所谓的操作包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等式的右边，甚至去掉函数。

如果在table1(col1)上面有一个索引，那么下面的查询语句

select * from table1 where col1 + 2  =  10;

就不会考虑使用索引，如果改写成下面的完全等价的写法：

select * from table1 where col1  =  8;

那么优化器会偏向与使用索引。

【规则7-2-2】ORACLE提供了基于函数的索引。

create index table1_idx1 on table1 (upper(col1));

这样，下面的查询就会倾向于使用索引。

select col3 from table1 where upper(col1)  =  'ABC';

【规则7-2-3】搜索条件中有“ is null ”、“！=”“not in”这样的条件，将肯定不会使用索引。所以如果可能的话应该尽量使用其他的等效写法来代替“ is null ”、“！=”“not in”这样的条件。

【规则7-2-4】 使用复合索引且第一个索引字段没有出现在where 中时，建议使用hint 强制。

比如：

如果在表emp中有一个索引id1 on emp (ename,deptno),针对deptno字段进行搜索一般是不会使用索引的。这个时候，可以使用下面的语法强制优化器使用索引。

select /*+ index (emp id1) */* from emp where deptno = 2

请注意：这样的语法要比较慎重。刚开始的时候不要这样使用，一般只在数据库后期优化的时候可以这样使用。因为优化器一般来说是比较“聪明”的，它选择的一般情况下都是COST最小的路径，如果强制使用索引不当，有可能还会使性能下降。

8：视图

【规则8-1】视图的命名规则请参考第2节

建立视图的目的

1：为了达到权限控制。

2：实现简单的外连接。

3：简单常用的UNION。

4：简化频繁使用的SQL查询语句。

【规则8-4】禁止将业务逻辑固定在视图中并联结过多的表。这样做实际上是一次将过多的业务层逻辑交给数据库去做，在开发阶段省了事，但是会给数据库性能以及后期的维护带来很大的麻烦。

9：存储过程和函数

在说到存储过程和函数的时候，先简单介绍一下包。

Oracle中存储过程和函数的集合叫作包(Packages), 一个包分为 包头和包体, 包头定义了存储过程的名称和参数 ，包体除了名称和参数，还包存储过程的所有语句；几个存储过程可以组成一个包。

示例：

包头的定义

create or replace package myTest_pkg

is

type cur_out is ref cursor;

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_count

from code where 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;

open 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;

【规则9-1】存储过程和函数的命名、书写规则请参考第一、二节

【规则9-2】所有的函数和存储过程都必包含在某一个特定包中，不要产生游历于所有包以外的函数或者存储过程。

【规则9-3】定义参数的类型尽量使用锚定的声明。

%type

例如，定义一个变量l_date，它的类型与表job_history的start_date字段是相同类型(varchar2(8))，请不要这样定义：l_date  varchar2(8),

需要用如下的方式定义：

l_date  job_history.start_date%type，

【规则9-4】存储过程或者函数的接口参数必须显示标明是传入参数还是传出参数，同时，传入参数加前缀i_，传出参数加前缀o_，传入传出参数加io_。

【规则9-5】存储过程中变量的声明应集中在as 和begin 关键字之间，不允许在代码中随意定义变量，定义变量时，完成相同功能模块的变量应放在一起，与不同模块的变形量应空行隔开，增加代码的可读性。

【规则9-6】事务必须显式提交或者取消，不要采用隐式提交或者自动提交。

【规则9-7】当存储过程和函数有多个分支返回时，若有事务，需确保各个分支都结束事务。

【规则9-8】对于存储过程、函数等程序块必须要有异常处理部分，以提高程序的自检能力，异常尽可能就近处理。

例如

begin

…

exception

when excep—name1 then

…

when excep—name2 then

…

when others then

…

end;

没有合适异常处理的代码是不允许出现在生产数据库中的。

【规则9-9】存储过程和函数代码中不要使用goto 语句。

说明：很显然，使用goto语句是一种不极其好的编码风格。

【规则9-10】存储过程和函数中要确保所的变量和参数都使用到。

说明：申明变量等也要一定的系统开销，如果申明无用的变量，也显得代码不够严谨，而且它还会影响到任何后来阅读代码者的思路。

【规则9-11】存储过程和函数不宜过大，一般程序行不要超过150行。过大的存储过程或者函数可以考虑拆分。

【规则9-12】如果在函数或者存储过程中使用了动态SQL，则一定要使用绑定变量。详细请看demo中\ 8createPack\ pkg_some_business.sql的存储过程up_do_some_business。

【规则9-13】供别的文件或函数调用的函数，不应使用全局变量交换数据。

10：注释规范

10．1：总则

【规则10-1-1】注释内容要清晰、明了、含义准确，防止注释二义性

【规则10-1-2】禁止在注释中使用缩写，特别是非常用的缩写。

说明：注释就是要写清楚给别人看的，如果使用缩写则不太容易表达清楚，而且有时候会有歧义。在使用缩写时或之前，应进行必要的说明。

【规则10-1-3】一般情况下，源程序有效注释量须在30%以上。

说明：注释的原则是有助于对程序阅读理解，在该加的地方都加了，注释不宜太多也不能太少，注释语言须准确、易懂、简洁。一般来说，30%的注释才能把程序大概说清楚。所以30%应该是一个下限。

10．2：存储过程、函数头注释

【规则10-1-1】 存储过程、函数头的注释。

示例：使用以下书写规范：

(注意：开发人员可以直接COPY这段注释到自己的代码中，然后进行相应内容条目的修改既可。)

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

name: f_insert_user

purpose:向sm_users 表中写入一条记录

revsions:

ver date author description

1.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.增加用户名校验过程

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_date 赋值.

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

说明：

name:函数或过程的名称

purpse:函数或过程的用途

revisions 下面是版本信息

ver:当前版本

date:创建或修改日期

author:创建人或修改人

description:在修改时，一定要在这里写出改动的内容，有1、2、3 清晰列出来

parameters:对传入和传出参数进行说明

return:函数返回结果

notes:使用该函数或过程时需要特别注意的事情，如果没有可以不写。

10．3：文件注释

【规则10-3-1】所有变量定义需要加注释，说明该变量的用途和含义。

【规则10-3-2】对存储过程、函数的任何修改，都需要在注释最后添加修改人、修改日期及修改原因等信息。

【规则10-3-3】对程序分支必须书写注释。

说明：这些语句往往是程序实现某一特定功能的关键，对于维护人员来说，良好的注释帮助更好的理解程序，有时甚至优于看设计文档。

【规则10-3-5】注释应与其描述的代码相似，对代码注释应放在其上方或右方(对单条

语句的注释)相近位置，不可放在下面。

下面的书写就不规范。

select user_name,

disabled_date

into v_user,

v_disabled_date

from sm_users su where

su.user_id  =  p_user_id;

--取得用户的失效时间

应该如下书写：

--取得用户的失效时间

select user_name,

disabled_date

into

v_user_name,

v_disabled_date

from sm_users su

where su.user_id  =  p_user_id;

【规则10-3-6】注释与所描述的内容进行同样的缩排。

【建议10-3-8】函数应对返回的代码进行详细描述。

【建议10-3-9】凡是涉及到类型的参数，建议在注释中把类型说明全部逻列出来。

【建议10-3-10】尽量使用”--” 进行行注释。

【建议10-3-11】行尾注释须使用”--” 。

【建议10-3-12】避免在一行代码或表达式的中间插入注释。

【规则10-3-14】注释用中文书写(考虑到开发人员英语水平的不同，为了提高代码以及注释的可读性，使用中文可以做到沟通代价最小)。

【规则10-3-15】重复代码需要注释说明 。

11：脚本规范

【规则11-1】所有脚本按内容分开存放，并按以下命名和存储：

1：创建用户脚本，创建表空间脚本

创建表空间脚本 1createTablespace.sql

创建用户脚本 2createUser.sql

2：创建业务表脚本，创建索引脚本

创建业务表脚本命名 3createTable.sql

创建索引脚本：4createIndex.sql

3：创建临时表脚本

创建临时表脚本 5createTempTable.sql

4：创建数据库联结脚本

创建数据库联结脚本 6createDBLink.sql

5：创建视图脚本

创建视图脚7createView.sql

6：创建包脚本

创建一个目录：8createPack，在这个目录下面包含所有的创建包的脚本.

包文件的文件名应该等于：本包的名字+.sql。每个包(包括存储过程和函数)创建脚本单独保存。

7：初始化数据脚本。

初始化数据脚本：9initData.sql

9：创建作业脚本

创建作业脚本：10createJob.sql

每一个脚本(或者目录)前面的数字说明了执行顺序。

【规则112】 在规则【规则11-1】中的6：创建包脚本

包脚本包含在8createPack目录下。对于这个目录下的脚本，如果脚本数量不多，就不用建立子目录。如果脚本数量很多，可以按照功能分模块建立子目录，把相应的脚本放到对应的子目录。在附件的demo中，8createPack下面就有2个模块，一个是buy_future，一个是sale_future，下面分别存放了一些脚本。

【规则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语句性能优化

【规则12-1】避免在WHERE字句中对列施以函数:

select  item

from service_promotion

where   to_char (gmt_modified,’yyyy-mm-dd’)

=   ‘20001-09-01’;

而应使用：

select  item

from  service_promotion

where   gmt_modified

>=    to_date(‘2001-9-01’,’yyyy-mm-dd’)

and   gmt_modified

【规则12-2】一定要使用绑定变量

以JAVA为例，所有提交给数据库执行的SQL语句应该如下面这样绑定变量。

{

Connection con  =  DriverManager.getConnection

("jdbc:oracle:oci:@database","scott","tiger");

conn.setAutoCommit(false);//把自动提交修改为非自动提交。

String v_sql  =  'select name from table_a where id  =  ? '; //嵌入绑定变量

ReparedStatement stmt  =  conn.prepareStatement( v_sql );

stmt.setString(1, 'xxxx' ); //为绑定变量赋值

stmt.executeQuery();

conn.commit();

}

【规则12-3】不要频繁提交，不要使用自动提交

以JAVA为例，任何与数据库事务相关的代码都应该是这样的：

{

Connection con  =  DriverManager.getConnection

("jdbc:oracle:oci:@database","scott","tiger");

conn.setAutoCommit(false);//把自动提交修改为非自动提交。

//业务代码

//业务代码

//业务代码

conn.commit();//根据业务需求最后再一并提交。

}

注意，尤其是不要将commit()语句放在循环当中。

笔者曾经不止一次的在网上看到所谓“ORACLE优化”的文章，里面说：“commit越频繁越好，越能加快释放资源，增加性能”。这样的文章实际上是不对的。在别的主流数据库中，这个说法大多正确，但是在ORACLE不是这样的！

在ORACLE数据库中，频繁的提交对数据库的性能正好是有负面效果的。

对这点的详细说明，请参照《ORACLE数据库开发优化指南.doc》

【规则12-4】:查询返回尽量少的字段，select 后面尽量不连接*。

比如 select * from table1

这样会把表table1中的所有字段全部返回，而实际上应该只把需要的字段放在select后面。

比如

select col1,col2 from table1.这样就会查询需要的2个字段并返回，而不会把所有的字段都查询出来。

【规则12-5】尽量消除排序操作(如果可以的前提下)。

排序操作是非常昂贵的。可以不使用order by, group by的地方，尽量不要使用。同时如果可以使用UNION ALL，那么就应该尽量不使用UNION操作。因为UNION操作会把连接的2张表分别进行排序以后将重复的记录剔除然后输出，而UNION ALL就不会排序，也不会把重复记录消除。

【规则12-6】尽量使用not exists来代替not in。

说明：使用not exists，ORACLE将会更好的使用索引。

select empno

from emp

where deptno not in ( select deptno from test2);

上面的语句不会使用索引。

与上面语句完全等效的下面的语句优化器会偏向于使用索引

Select  empno

From emp d

where not exists

(select null

from test2 e

where  e.deptno = d.deptno);

【规则12-7】使用ORACLE提供的真正的临时表，而不是开发人员自己构造的临时表

说明：ORACLE提供了真正的临时表，可以基于会话，也可以基于事务。

基于会话的临时表(临时表中的数据可以跨提交存在，即提交后仍然存在，但是断开连接以后在连接时数据就没有了)。

create global temporary table temp_table_session

on commit preserve rows

as select * from scott.emp

基于事务的临时表(提交以后数据自动消失)。

create global temporary table temp_table_session

on commit delete rows

as select * from scott.emp

在任何情况下，一个会话中的临时表数据都不会被其他任何会话看到，因为ORACLE已经对不同会话做了数据隔离。

【规则12-8】表连接查询中，相同的表只出现一次。

select t1.col1,t2.col2 from

(select a.col1,b.col2 from table1 a,table2 b where ......) t1,

(select a.col1,c.col2 from table1 a,table3 c where ......) t2  where ....

上面的查询中，table1出现了2次。

可以把它改写为下面等效的语句：

select a.col1,b.col2 from table1 a,table2 b,table3 c where .......

还有一个例子，低效

select tab_name

from tables

where tab_name  =  (select tab_name

from tab_columns

where version = 604)

and db_ver  =  (select db_ver

from tab_columns

where version  =  604)

高效

select tab_name

from tables

where (tab_name, db_ver)

=  (select tab_name, db_ver from tab_columns

where version  =  604)

【规则12-9】like 子句尽量前端匹配

like 参数使用得非常频繁，因此如果能够对于like 子句使用索引，将很好地提高查询的效率。

例如：查询城市代码(city表上有基于city_name列的索引)

select city_name_chinese from city where city_name like ‘%ZHEN%’

优化器偏向于不使用索引。

如果对上面的语句进行修改，成下面的表达形式

select city_name_chinese from city where city_name like ‘SHNEZHEN%’

那么优化器偏向于使用索引。

【规则12-10】 用UNION替换OR (适用于索引列)

通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低.

在下面的例子中, LOC_ID 和REGION上都建有索引.

高效:

select loc_id , loc_desc , region

from location

where loc_id  =  10

union

select loc_id , loc_desc , region

from location

where region  =  “Melbourne”

低效:

select loc_id , loc_desc , region

from location

where loc_id  =  10 or region  =  “melbourne”

【规则12-11】EXISTS替换DISTINCT

下面2个SQL是逻辑等效，但是性能相差很大。因为distinct会让数据集多做一次昂贵的SORT操作

低效

select distinct dept_no, dept_name

from dept d, emp e

where d.dept_no  =  e.dept_no

高效

select dept_no, dept_name

from dept d

where exists (select null

from emp e

where e.dept_no  =  d.dept_no);

【规则12-12】使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.

可以用DECODE函数高效地得到结果

select count(decode(dept_no,0020,'x',null)) d0020_count,

count(decode(dept_no,0030,'x',null)) d0030_count,

sum(decode(dept_no,0020,sal,null)) d0020_sal,

sum(decode(dept_no,0030,sal,null)) d0030_sal

from emp where ename like ‘smith%’;

【规则12-13】当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上，这样可以减少解析的时间，并减少那些由Column歧义引起的语法错误并增加可读性。

【规则12-13】使用statspack来查看优化器的行为，从而筛选出合适高效的SQL语句。

【规则12-14】建立合适的索引来提高查询速度。如何建立索引以及如何利用索引优化查询请参考索引一节。

13：冗余信息

【规则13-1】根据数据库设计第3范式，数据库中应该尽量消除冗余信息。因为冗余信息的存在给数据库数据的一致性、完整性带来潜在的破坏风险。

【规则13-2】如果在引入冗余信息能给数据库带来很大的性能提升或者其他方面的收益的情况下可以考虑引入。

【规则13-3】如果引入冗余信息，那么必须要有一定的手段保证冗余信息的一致性、完整性。一般情况下应该使用逻辑代码来完成。

14：异常处理

【规则14-1】在存储过程和函数内部的异常处理可以参考存储过程和函数一节。

【规则14-2】任何与数据库相关的外部代码都必须对数据库异常作出处理。

在外部的代码，以JAVA为例

try

{

}

catch(DBException ex)

{

//这里，请写上你的异常处理代码！

}

finally

{

//

}

在catch语句下面，应该有自己的异常处理代码。如果没有异常处理代码，相当于把异常隐藏起来了，会给程序带来灾难性后果。

【规则14-3】没有合适异常处理的代码应该是不能出现在生产环境中的。

【规则14-4】异常传播机制如下：

1、没有处理的异常将沿检测异常调用程序传播到外面，当异常被处理并解决或到达程序最外层传播停止。

2、在声明部分抛出的异在异常部分抛出的异常将控制转到上一层的异常部分。

3、执行部分抛出的异常将首先传递到同一块的异常部分，如果在同一块的异常部分没有处理这个异常的处理器，那么异常将会传播到上一层的异常部分中，一直到最外层。

4、在异常部分抛出的异常将控制转到上一层的异常部分。

5、在异常部分抛出的异常将控制转到上一层的异常部分。

我的异常网推荐解决方案：oracle存储过程,http://www.myexceptions.net/oracle-develop/177537.html

文章评论