SQL语言艺术(PDF格式)-第4部分

快捷操作: 按键盘上方向键 ← 或 → 可快速上下翻页 按键盘上的 Enter 键可回到本书目录页 按键盘上方向键 ↑ 可回到本页顶部! 如果本书没有阅读完，想下次继续接着阅读，可使用上方 "收藏到我的浏览器" 功能 和 "加入书签" 功能！




把逻辑放到查询中　



Program　Logic　into　Queries　



在数据库应用程序中实现过程逻辑（procedural　logic）的方法有几种。SQL语句内部可实现某　

种程度上的过程逻辑（尽管SQL语句应该说明做什么，而不是怎么做）。即便内嵌式SQL的宿主　

语言（host　language）非常完善，依然推荐尽量将上述过程逻辑放在SQL语句当中，而不是宿　

主语言当中，因为前一种做法效率更高。过程性语言（Procedural　language）的特点在于拥有　

执行迭代（循环）和条件（if　。。。　then　。。。　else　结构）逻辑的能力。SQL不需要循环能力，因为它　

本质上是在操作集合，SQL只需要执行条件逻辑的能力。　



条件逻辑包含两部分——IF和ELSE。要实现IF的效果相当容易——where子句可以胜任，困难　

的是实现　ELSE　逻辑。例如，要取出一些记录，然后对其分组，每组进行不同的转换。case　表　

达式（Oracle　早已在decode（）（注1）中提供了功能等效的操作符）可以容易地模拟ELSE逻辑：　

根据每条记录值的不同，返回具有不同值的结果集。下面用伪代码（pseudocode）表达case　结　

构的使用（注2）：　



　CASE　

　WHEN　condition　THEN　　

　WHEN　condition　THEN　　

　。。。　

　WHEN　condition　THEN　　

　ELSE　　


…………………………………………………………Page　19……………………………………………………………

　　END　



数值或日期的比较则简单明了。操作字符串可以用Oracle　的　greatest（）或least（），或者MySQL　

的strcmp（）。有时，可以为insert语句增加过程逻辑，具体办法是多重insert及条件insert（注3），　

并借助　merge　语句。如果　DBMS　提供了这样语句，毫不犹豫地使用它。也就是说，有许多　

逻辑可以放入　SQL　语句中；虽然仅执行多条语句中的一条这种逻辑价值不大，但如果设法利　

用　case、merge　或类似功能将多条语句合并成一条，价值可就大了。　



总结：只要有可能，应尽量把条件逻辑放到　SQL语句中，而不是SQL的宿主语言中。　



一次完成多个更新　



Multiple　Updates　atOnce　

MMuullttiippllee　UUppddaatteess　aattOOnnccee　



我的基本主张是：如果每次更新的是彼此无关的记录，对一张表连续进行多次update操作还可　

以接受；否则，就应该把它们合并成一个update操作。例如，下面是来自实际应用的一些代码　

（注4）：　



　　　update　tbo_invoice_extractor　

　　　set　pga_status　=0　

　　　where　pga_status　in（1；3）　

　　　andinv_type　=0；　

　　　update　tbo_invoice_extractor　

　　　set　rd_status=0　

　　　where　rd_status　in（1；3）　

　　　andinv_type　=0；　



两个连续的更新是对同一个表进行的。但它们是否将访问相同的记录呢？不得而知。问题是，　

搜索条件的效率有多高？任何名为type或status的字段，其值的分布通常是杂乱无章的，所以上　

面两个update语句极可能对同一个表连续进行两次完整扫描：一个update有效地利用了索引，而　

第二个update不可避免地进行全表扫描；或者，幸运　



的话，两次update都有效地利用了索引。无论如何，把这两个update合并到一起，几乎不会有损　

失，只会有好处：　

　　update　tbo_invoice_extractor　

　　setpga_status　=（casepga_status　

　　when　1then　0　

　　when　3then　0　

　　else　pga_status　


…………………………………………………………Page　20……………………………………………………………

　　end）；　

　　rd_status　=（caserd_status　

　　when　1then　0　

　　when　3then　0　

　　else　rd_status　

　　end）　

　　where　（pga_status　in（1；3）　

　　orrd_status　in（1；　3））　

　　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字段有不同值；如果找到需要的记录，就返回要找　

的日期；否则，就返回一个在任何情况下都远比我们所需日期要早的日期（此处选了哈斯丁之　

役（battle　of　Hastings）的日期）。只要每次取出最大值，就可以确保获得需要的日期。这是个　

非常实用的技巧，也可以应用在字符或数值数据，第11章会有更详细的说明。　



总结：SQL是声明性语言（declarative　language），所以设法使你的代码超越业务过程的规格　

说明。　



SQL　

SSQQLL的进攻式编程　



Offensive　Coding　with　SQL　



一般的建议是进行防御式编程（code　defensively），在开始处理之前先检查所有参数的合法性。　

但实际上，对数据库编程而言，尽量同时做几件事情的进攻式编程有切实的优势。　



有个很好的例子：进行一连串检查，每当其中一个检查所要求的条件不符时就产生异常。信用　

卡付款的处理中就涉及类似步骤。例如，检查所提交的客户身份和卡号是否有效，以及两者是　

否匹配；检查信用卡是否过期；最后，检查当前的支付额是否超过了信用额度。如果通过了所　


…………………………………………………………Page　24……………………………………………………………

有检查，支付操作才继续进行。　



为了完成上述功能，不熟练的开发者会写出下列语句，并检查其返回结果：　



　select　count（*）　

　from　customers　

　where　customer_id　=　provided_id　



接下来，他会做类似的工作，并再一次检查错误代码：　



　select　card_num；　expiry_date；　credit_limit　

　from　accounts　

　where　customer_id　=　provided_id　



之后，他才会处理金融交易。　



相反，熟练的开发者更喜欢像下面这样编写代码（假设today（）返当前日期）：　

　　update　accounts　

　　set　balance　=　balance　purchased_amount　

　　where　balance　》=　purchased_amount　

　　and　credit_limit　》=　purchased_amount　

　　and　expiry_date　》　today（）　

　　and　customer_id　=　provided_id　

　　and　card_num　=　provided_cardnum　

接着，检查被更新的行数。如果结果为　0，只需执行下面的一个操作即可判断出错原因：　

　　select　c。customer_id；　a。card_num；　a。expiry_date；　

　　a。credit_limit；　a。balance　

　　from　customers　c　

　　leftouter　join　accounts　a　

　　on　a。customer_id　=　c。customer_id　

　　and　a。card_num　=　provided_cardnum　

　　where　c。customer_id　=　provided_id　



如果此查询没有返回数据，则可断定customer_id　的值是错的；如果　card_num　是　null，则可　

断定卡号是错的；等等。其实，多数情况下此查询无需被执行。