《SQL语言艺术(PDF格式)》第7章

andinv_type　=0；　
上例中，可能出现重复更新相同字段为相同内容的情况，这的确增加了一小点儿开销。但在多　
数情况下，一个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语句隐藏在自定义函数中的危险性。表flights描述商务航班，有航班号、起　
飞时间、到达时间及机场　IATA　代码（注5）等字段。IATA代码均为三个字母，有9　000多个，　
它们的解释保存在参照表中，包含城市名称（若一个城市有多个机场则应为机场名称）、国家名　
称等。显然，显示航班信息时，应该包含目的城市的机场名称，而不是简单的　IATA　代码。　
在此就遇到了现代软件工程中的矛盾之一。被认为是“优良传统”的模块化编程一般情况下非常　
适用，但对数据库编程而言，代码是开发者和数据库引擎的共享活动（shared　activity），模块　
化要求并不明确。例如，我们可以遵循模块化原则编写一个小函数来查找　IATA　代码，并返回　
完整的机场名称：　
create　or　replace　function　airport_city（iata_code　in　char）　
return　varchar2　
is　
…………………………………………………………Page　21……………………………………………………………
city_name　varchar2（50）；　
begin　
select　city　
into　city_name　
from　iata_airport_codes　
where　code　=　iata_code；　
return（city_name）；　
end；　
/　
对于不熟悉　Oracle　语法的读者，在此做个说明，以下查询中trunc（sysdate）的返回值为“今天的　
00：00　a。m。”，日期计算以天为单位；所以起飞时间的条件是指今天　8：30　a。m。　至　4：00　p。m。　之　
间。调用airport_city函数的查询可以非常简单，例如：　
select　flight_number；　
to_char（departure_time；　"HH24：MI"）　DEPARTURE；　
airport_city（arrival）　〃TO〃　
from　flights　
where　departure_time　between　trunc（sysdate）　＋　17/48　
and　trunc（sysdate）　＋　16/24　
order　by　departure_time　
/　
这个查询的执行速度令人满意；在我机器上的随机样本中，返回77行数据只用了0。18　秒（多次　
执行的平均值），用户对这样的速度肯定满意（统计数据表明，此处理访问了　
303个数据块，53个是从磁盘读出的——而且每行数据有个递归调用）。　
我们还可以用join来重写这段代码，作为查找函数的替代方案，当然它看起来会稍微复杂些：　
select　f。flight_number；　
to_char（f。departure_time；　"HH24：MI"）　DEPARTURE；　
a。city　〃TO〃　
from　flights　f；　
iata_airport_codes　a　
where　a。code　=　f。arrival　
and　departure_time　between　trunc（sysdate）　＋　17/48　
and　trunc（sysdate）　＋　16/24　
order　by　departure_time　
/　
…………………………………………………………Page　22……………………………………………………………
这个查询只用了　0。05　秒（统计数据同前，但没有递归调用）。对于执行时间不到　0。2　秒的查　
询来说，速度快了3倍似乎无关紧要，但在大型系统中，这些查询每天经常执行数十万次——假　
设以上查询每天只执行五万次，于是查询的总耗时为　2。5　小时。若不使用上述查找函数（lookup　
function）则只需要不到　42　分钟，速度提高超过300％，这对大数据量的系统意义重大，最终　
带来经济上的节约。通常，使用查找函数会使批处理程序的性能极差。而且查询时间的增加，　
会使同一台机器支持的并发用户数减少，我们将在第9章对此展开讨论。　
总结：优化器对自定义函数的代码无能为力。　
SQL　
简洁的SSQQLL　
Succinct　SQL　
熟练的开发者使用尽可能少的　SQL语句完成尽可能多的事情。相反，拙劣的开发者则倾向于严　
格遵循已制订好的各功能步骤，下面是个真实的例子：　
…Get　the　start　of　the　accounting　period　
select　closure_date　
into　dtPerSta　
from　tperrslt　
where　fiscal_year=to_char（Param_dtAcc；"YYYY"）　
and　rslt_period="1"　｜｜　to_char（Param_dtAcc；"MM"）；　
…Get　the　end　of　the　period　out　of　closure　
select　closure_date　
into　dtPerClosure　
from　tperrslt　
where　fiscal_year=to_char（Param_dtAcc；"YYYY"）　
and　rslt_period="9"　｜｜　to_char（Param_dtAcc；"MM"）；　
就算速度可以接受，这也是段极糟的代码。很不幸，性能专家经常遇到这种糟糕的代码。既然　
两个值来自于同一表，为什么要分别用两个不同的语句呢？下面用Oracle的bulk　collect子句，　
一次性将两个值放到数组中，这很容易实现，关键在于对rslt_period进行order　by操作，如下所　
示：　
select　closure_date　
bulk　collect　into　dtPerStaArray　
from　tperrslt　
where　fiscal_year=to_char（Param_dtAcc；"YYYY"）　
and　rslt_period　in　（"1"　｜｜　to_char（Param_dtAcc；"MM"）；　
…………………………………………………………Page　23……………………………………………………………
"9"　｜｜　to_char（Param_dtAcc；"MM"））　
order　by　rslt_period；　
于是，这两个日期被分别保存在数组的第一个和第二个位置。其中，bulkcollect　是　PL/SQL　语　
言特有的，但任何支持显式或隐式数组提取的语言都可如法炮制。　
其实甚至数组都是不必要的，用以下的小技巧（注6），这两个值就可以被提取到两个变量中：　
select　max（decode（substr（rslt_period；　1；　1）；　…Check　the　first　character　
"1"；　closure_date；　
…If　it"s　"1"　return　the　date　we　want　
to_date（"14/10/1066"；　"DD/MM/YYYY"）））；　
…Otherwise　something　old　
max（decode（substr（rslt_period；　1；　1）；　
"9"；　closure_date；　…The　date　wewant　
to_date（"14/10/1066"；　"DD/MM/YYYY"）））；　
into　dtPerSta；　dtPerClosure　
from　tperrslt　
where　fiscal_year=to_char（Param_dtAcc；"YYYY"）　
and　rslt_period　in　（"1"　｜｜　to_char（Param_dtAcc；"MM"）；　
"9"　｜｜　to_char（Param_dtAcc；"MM"））；　
在这个例子中，预期返回值为两行数据，所以问题是：如何把原本属于一个字段的两行数据，　
以一行数据两个字段的方式检索出来（正如数组提取的例子一样）。为此，我们　
检查rslt_period字段，两行数据的rslt_period字段有不同值；如果找到需要的记录，就返回要找　
的日期；否则，就返回一个在任何情况下都远比我们所需日期要早的日期（此处选了哈斯?