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

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


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



注意　



你是否注意到，上述第一段代码中使用了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　Use　of　Exceptions　



勇敢与鲁莽的界线很模糊，我建议进攻式编程，但并不是要你模仿轻步兵旅在Balaclava的自杀　

性冲锋（注7）。针对异常编程，最终可能落得虚张声势的愚蠢结果，但自负的开发者还是对它“推　

崇备至（go　for　it）”，并坚信检查和处理异常能使他们完成任务。　



正如其名字所暗示的，异常应该是那些例外情况。对数据库编程的具体情况而言，不是所有异　

常都要求同样的处理方式——这是理解异常的使用是否明智的关键点。有些是“好”异常，应预　

先抛出；有些是“坏”异常，仅当真正的灾害发生时才抛出。　



例如，以主键为条件进行查询时，如果没有结果返回则开销极少，因为只需检查索引即可判断。　

然而，如果查询无法使用索引，就必须搜索整个表——当此表数据量很大，所在机器又正在接　

近满负荷工作时，可能造成灾难。　



有些异常的处理代价高昂，即使是在最佳情况下也不例外，例如重复键（duplicate　key）的探　

测。“唯一性（uniqueness）”如何保证呢？我们几乎总是建立一个唯一性索引，每次向该索引增　

加一个键时，都要检查是否违反了该唯一性索引的约束。然而，建立索引项需要记录物理地址，　

于是就要求先将记录插入表，后将索引项插入索引。如果违反此约束，数据库会取消不完全的　

插入，并返回违反约束的错误信息。上述这些操作开销巨大。但最大的问题是，整个处理必须　

围绕个别异常展开，于是我们必须“从个别记录的角度进行思考”，而不是“从数据集出发进行思　

考”，这与关系数据库理论完全背道而驰。多次违反此约束会导致性能严重下降。　



来看一个　Oracle　的例子。假设在两家公司合并后，电子邮件地址定为的标准　

格式，最多　12　个字符，所有空格或引号以下划线代替。　



如果新的employee表已经建好，并包含3　000　条从employee_old表中提取并进行标准化处理的　

电子邮件地址。我们希望每个员工的电子邮件地址具有唯一性，于是Fernando　Lopez的地址为　

flopez，而Francisco　Lopez的地址为flopez2。实际上，我们实际测试的数据中有33　个潜在的　

重复项，所以我们需要做如下测试：　

　　SQL》　insert　into　employees（emp_num；　emp_name；　

　　emp_firstname；　emp_email）　

　　2　select　emp_num；　


…………………………………………………………Page　29……………………………………………………………

　　3　　　　　　emp_name；　

　　4　　　　　　emp_firstname；　

　　5　　　　　　substr（substr（EMP_FIRSTNAME；　1；　1）　

　　6　　　　　　　　　｜｜translate（EMP_NAME；　'　'''；　'_　_'）；　1；　12）　

　　7　from　employees_old；　



　　insert　into　employees（emp_num；　emp_name；　emp_firstname；　emp_email）　



　　*　

　　ERROR　at　line　1：　

　　ORA…00001：　unique　constraint　（EMP_EMAIL_UQ）　violated　



　　Elapsed：　00：00：00。85　



3　000　条数据中重复　33　条，比率大约是　1％，所以，或许可以心安理得地处理符合标准的　

99％，并用异常来处理其余部分。毕竟，1％　的不符标准数据带来的异常处理开销应该不大。　



但这个异常处理的开销到底在哪里呢？让我们先从测试数据中剔除“问题记录”，然后再执行相　

同的测试，比较发现：这次测试的总运行时间，与上次几乎相同，都是18　秒。然而，从测试数　

据中剔除“问题记录”之后再执行前面第一段　insert。。。select　语句时，速度明显比循环快：最终发　

现采用“一次处理一行”的方式导致耗时增加了近　50％。那么，在此例中可以不用“一次处理一　

行”的方式吗？可以，但要首先避免使用异常。正是这个通过异常处理解决“问题记录”问题决定，　

迫使我们采用循序方式的。　



另外，由于发生冲突的电子邮件地址可能不止一个，可以为它们指定某个数字获得唯一性。　



很容易判断有多少个数据记录发生了冲突，增加　一个groupby子句就可以了。但在分配数字时，　

如果不使用主数据库系统提供的分析功能，恐怕比较困难。（Oracle　称为分析功能（analytical　

function），　DB2　则称在线分析处理（online　analyticalprocessing，OLAP），SQLServer　称之为排　

名功能（ranking　function）。）纯粹从SQL角度来看，探索此问题的解决方案很有意义。　



重复的电子邮件地址都可以被赋予一个具唯一性的数字：1赋给年纪最大的员工，2　赋给年纪次　

之的的员工……依次类推。为此，可以编写一个子查询，如果是group中的第一个电子邮件地址　

就不作操作，而该group中的后续电子邮件地址则加上序号。代码如下：　



　　　SQL》insert　into　employees（emp_num；　emp_firstname；　

　　　2　　　　　　　　　　　　emp_name；　emp_email）　

　　　3　select　emp_num；　

　　　4　　　　emp_firstname；　

　　　5　　　　emp_name；　

　　　6　　　　decode（rn；　1；emp_email；　

　　　7　　　　　　　　　　　substr（emp_email；　


…………………………………………………………Page　30……………………………………………………………

　　　8　　　　　　　　　　　　　1；12…length（ltrim（to_char（rn））））　

　　　9　　　　　　　　　　　　　　｜｜ltrim（to_char（rn）））　

　　　10　from　（select　emp_num；　

　　　11　　　　　　　　　emp_firstname；　

　　　12　　　　　　　　　emp_name；　

　　　13　　　　　　　　　substr（substr（emp_firstname；　1；1）　

　　　14　　　　　　　　　　｜｜translate（emp_name；　''''；'_　_'）；　1；12）　

　　　15　　　　　　　　　　　　　　　emp_email；　

　　　16　　　　　　　　　row_number（）　

　　　17　　　　　　　　　　over　（partitionby　

　　　18　　　　　　　　　　　　　　substr（substr（emp_firstname；　1；1）　

　　　19　　　　　　　　　　　　　　｜｜translate（emp_name；'　'''；'_　_'）；1；12）　

　　　20　　　　　　　　　　　　　　order　byemp_num）　rn　

　　　21　　　　　from　employees_old）　

　　　22　/　



　　　3000rows　created。　



　　　Elapsed：　00：00：11。68　



上面的代码避免了一次一行的处理，而且该解决方案的执行时间仅是先前方案的　60％。　



总结：异常处理会迫使我们采用过程式逻辑。应始终使用声明式SQL，尽量预测可能的异常情况。　



SQL　

SSQQLL的本质　



本章我们将深入讨论SQL查询，并研究如何根据不同情况的具体要求，来编写SQL语句。我们　

会分析复杂的SQL查询语句，将它们拆解成小的语句片断，并讲解这些语句片断如何共同促成　

了最终查询结果的产生。　



SQL　

SSQQLL的本质　



The　Nature　of　SQL　



在深入讨论如何编写SQL查询之前