【规则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;
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-8】对于存储过程、函数等程序块必须要有异常处理部分,以提高程序的自检能力,异常尽可能就近处理。
例如
begin
…
exception
when excep—name1 then
…
when excep—name2 then
…
when others then
…
end;
没有合适异常处理的代码是不允许出现在生产数据库中的。
【规则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;
【规则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%’
那么优化器偏向于使用索引。
高效:
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%’;