1.2 嵌套的逐行处理

在PL/SQL语言中游标可以嵌套使用。首先将数据从一个游标中取出,然后将这些值传给另一个游标,即二级游标,再从二级游标取值传给三级游标,以此类推,这是很常见的编程方法。但是如果游标嵌套很深,这种基于循环的代码导致的性能问题就会加剧。由于游标嵌套,SQL执行的次数大量增加,导致程序运行时间更长。

在代码清单1-3中,c1、c2和c3是嵌套游标。游标c1是顶级游标,从表t1取得数据,c2是开放游标,传递从游标c1取得的值,c3也是开放游标,传递游标c2取得的值。有一个UPDATE语句对游标c3返回的每一行执行一次。尽管UPDATE语句已经优化为执行一次只要0.01秒,但程序的性能还是会由于深度嵌套游标而难以忍受的。假设游标c1、c2和c3分别返回20、50和100行,那么上述代码需要循环100 000行,程序的总执行时间超过了1000秒。对这类程序的调优通常需要完全重写它。

代码清单1-3 用嵌套游标逐行处理

代码清单1-3中代码的另一个问题在于先执行一个UPDATE语句。如果UPDATE 语句产生了no_data_found异常1,那么再执行一个INSERT语句。这种类型的问题可以利用MERGE语句从PL/SQL转到SQL引擎处理。

1这个说法是错的,select 语句才能触发no_data_found异常,update语句只能用if sql%notfound 去判断是否更新成功,update语句不会触发该异常。

从概念上讲,代码清单1-3中的三重循环表示表t1、t2和t3之间的等值连接。代码清单1-4展示了根据上述逻辑改写的使用表别名t的SQL语句。UPDATE和INSERT逻辑的结合用MERGE语句代替,MERGE语法提供了更新存在的行和插入不存在的行的功能。

代码清单1-4 用MERGE语句重写逐行处理

不要在PL/SQL语言中编写深度嵌套游标的代码。审查这类代码的逻辑,看是否能用SQL语句来代替。

目录