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

按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 Enter 键可回到本书目录页，按键盘上方向键 ↑ 可回到本页顶部！
————未阅读完？加入书签已便下次继续阅读！





　…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，则可　

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



注意　



你是否注意到，上述第一段代码中使用了count（*）呢？这是个count（*）被误用于存在性检测的绝　

佳例子。　

“进攻式编程”的本质特征是：以合理的可能性（reasonableprobabilities）为基础。例如，检查　


…………………………………………………………Page　25……………………………………………………………

客户是否存在是毫无意义的——因为既然该客户不存在，那么他的记录根本就不在数据库中！　

所以，应该先假设没有事情会出错；但如果出错了，就在出错的地方（而且只在那个地方）采　

取相应措施。有趣的是，这种方法很像一些数据库系统中采用的“乐观并发控制（optimistic　

concurrency　control）”，后者会假设update冲突不会发生，只在冲突真的发生时才进行控制处理。　

结果，乐观方法比悲观方法的吞吐量高得多。　



总结：以概论为基础进行编程。假设最可能的结果；不是的确必要，不要采用异常捕捉的处理　

方式。　



　　　　　　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'）；　

　　　'9'　｜｜　to_char（Param_dtAcc；'MM'））　

　　　order　by　rslt_period；　


…………………………………………………………Page　26……………………………………………………………

于是，这两个日期被分别保存在数组的第一个和第二个位置。其中，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　27……………………………………………………………

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

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

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



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



　　　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，则可　

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



注意　



你是否注意到，上述第一段代码中使用了count（*）呢？这是个count（*）被误用于存在性检测的绝　


…………………………………………………………Page　28……………………………………………………………

佳例子。　

“进攻式编程”的本质特征是：以合理的可能性（reasonableprobabilities）为基础。例如，检查　

客户是否存在是毫无意义的——因为既然该客户不存在，那么他的记录根本就不在数据库中！　

所以，应该先假设没有事情会出错；但如果出错了，就在出错的地方（而且只在那个地方）采　

取相应措施。有趣的是，这种方法很像一些数据库系统中采用的“乐观并发控制（optimistic　

concurrency　control）”，后者会假设update冲突不会发生，只在冲突真的发生时才进行控制处理。　

结果，乐观方法比悲观方法的吞吐量高得多。　



总结：以概论为基础进行编程。假设最可能的结果；不是的确必要，不要采用异常捕捉的处理　

方式。　



　　　　　　　　　　　　Exceptions　

精明地使用异常（EExxcceeppttiioonnss）　



Discerning