对其分组,每组进行不同的转换。case 表
达式(Oracle 早已在decode()(注1)中提供了功能等效的cāo作符)可以容易地模拟ELSE逻辑:
根据每条记录值的不同,返回具有不同值的结果集。下面用伪代码(pseudocode)表达case 结
构的使用(注2):
CASE
WHEN condition THEN <return scomthing to the result set>
WHEN condition THEN <return scomthing else>
...
WHEN condition THEN <return still scomthing else>
ELSE <fall back on this value>
数值或日期的比较则简单明了。cāo作字符串可以用Oracle 的greatest()或least(),或者MySQL
的strcmp()。有时,可以为insert语句增加过程逻辑,具体办法是多重insert及条件insert(注3),
并借comrge 语句。如果DBMS 提供了这样语句,毫不犹豫地使用它。也就是说,有许多
逻辑可以放入SQL 语句中;虽然仅执行多条语句中的一条这种逻辑价值不大,但如果设法利
用casecomrge 或类似功能将多条语句合并成一条,价值可就大了。
总结:只要有可能,应尽量把条件逻辑放到SQL语句中,而不是SQL的宿主语言中。
一次完成多个更新
Multiple
Updates
at
Once
我的基本主张是:如果每次更新的是彼此无关的记录,对一张表连续进行多次updatecāo作还可
以接受;否则,就应该把它们合并成一个updatecāo作。例如,下面是来自实际应用的一些代码
(注4):
两个连续的更新是对同一个表进行的。但它们是否将访问相同的记录呢?不得而知。问题是,
搜索条件的效率有多高?任何名为type或status的字段,其值的分布通常是杂乱无章的,所以上
面两个update语句极可能对同一个表连续进行两次完整扫描:一个update有效地利用了索引,而
第二个update不可避免地进行全表扫描;或者,幸运
的话,两次update都有效地利用了索引。无论如何,把这两个update合并到一起,几乎不会有损
失,只会有好处:
END
update tbo_invoice_extractor
set pga_status = 0
where pga_status in (1,3)
and inv_type = 0;
update tbo_invoice_extractor
set rd_status = 0
where rd_status in (1,3)
and inv_type = 0;
update tbo_invoice_extractor
set pga_status = (case pga_status
when 1 then 0
when 3 then 0
else pga_status
上例中,可能出现重复更新相同字段为相同内容的情况,这的确增加了一小点儿开销。但在多
数情况下,一个update会比多个update快得多。注意上例中的“逻辑(logic)”,我们通过case 语
句实现了隐式的条件逻辑(implicit conditional logic),来处理那些符合更新条件的数据记录,并
且更新条件可以有多条。
总结:有可能的话,用一个语句处理多个更新;尽量减少对同一个表的重复访问。
慎用自定义函数
Careful Use of User-Written Functions
将自定义函数(User-Written Function)嵌到SQL语句后,它可能被调用相当多次。如果在select
语句的选出项列表中使用自定义函数,则每返回一行数据就会调用一次该函数。如果自定义函
数出现在where 子句中,则每一行数据要成功通过过滤条件都会调用一次该函数;如果此时
其他过滤条件的筛选能力不够强,自定义函数被调用的次数就非常可观了。
如果自定义函数内部还要执行一个查询,会发生什么情况呢?每次函数调用都将执行此内部查
询。实际上,这和关联子查询(correlated subquery)效果相同,只不过自定义函数的方式阻
碍了基于开销的优化器(cost-based optimizer,CBO)对整个查询的优化效果,因为“子查询”
隐藏在函数中,数据库优化器鞭长莫及。
下面举例说明将SQL语句隐藏在自定义函数中的危险xìng。表flights描述商务航班,有航班号、起
飞时间、到达时间及机场IATA 代码(注5)等字段。IATA代码均为三个字母,有9 000多个,
它们的解释保存在参照表中,包含城市名称(若一个城市有多个机场则应为机场名称)、国家名
称等。显然,显示航班信息时,应该包含目的城市的机场名称,而不是简单的IATA 代码。
在此就遇到了现代软件工程中的矛盾之一。被认为是“优良传统”的模块化编程一般情况下非常
适用,但对数据库编程而言,代码是开发者和数据库引擎的共享活动(shared activity),模块
化要求并不明确。例如,我们可以遵循模块化原则编写一个小函数来查找IATA 代码,并返回
完整的机场名称:
end),
rd_status = (case rd_status
when 1 then 0
when 3 then 0
else rd_status
end)
where (pga_status in (1,3)
or rd_status in (1, 3))
and inv_type = 0;
create or replace function airport_city(iata_code in char)
return varchar2
is
对于不熟悉Oracle 语法的读者,在此做个说明,以下查询中trunc(sysdate)的返回值为“今天的
00:00 a.m.”,日期计算以天为单位;所以起飞时间的条件是指今天8:30 a.m. 至4:00 p.m. 之
间。调用airport_city函数的查询可以非常简单,例如:
这个查询的执行速度令人满意;在我机器上的随机样本中,返回77行数据只用了0.18 秒(多次
执行的平均值),用户对这样的速度肯定满意(统计数据表明,此处理访问了
303个数据块,53个是从磁盘读出的——而且每行数据有个递归调用)。
我们还可以用join来重写这段代码,作为查找函数的替代方案,当然它看起来会稍微复杂些:
city_ncom varchar2(50);
begin
select city
into city_ncom
from iata_airport_codes
where code = iata_code;
return(city_ncom);
end;
/
select flight_number,
to_char(departure_tcom, 'HH24:MI') DEPARTURE,
airport_city(arrival) "TO"
from flights
where departure_tcom between trunc(sysdate) + 17/48
and trunc(sysdate) + 16/24
order by departure_tcom
/
select f.flight_number,
to_char(f.departure_tcom, 'HH24:MI') DEPARTURE,
a.city "TO"
from flights f,
iata_airport_codes a
where a.code = f.arrival
and departure_tcom between trunc(sysdate) + 17/48
and trunc(sysdate) + 16/24