DB2 存储过程开发最佳实践

核心提示： 清单7：定义临时表DECLARE GLOBAL TEMPORARY TABLE gbl_temp LIKE person ON COMMIT DELETE ROWS NOT LOGGED IN usr_tbsp此语句创建一个名为 gbl_temp 的用户临时表，定义此用户临时表 所使用的列的名称和说明与 person

清单7：定义临时表

DECLARE GLOBAL TEMPORARY TABLE gbl_temp LIKE person ON COMMIT DELETE ROWS NOT LOGGED IN usr_tbsp

此语句创建一个名为 gbl_temp 的用户临时表。定义此用户临时表 所使用的列的名称和说明与 person 的列的名称和说明完全相同。

清单8：创建有两个字段的临时表

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP2 ( ID INTEGER default 3, NAME CHAR(30) ) --WITH REPLACE NOT LOGGED; --IN USER_TEMP_01;

此语句创建了一个有两个字段的临时表。

理论上临时表是不需要显示DROP的，因为它是基于会话的，当临时表基于的连接关闭的时候，临时表也就不存在了。但是在实际开发中会有一些情况需要我们对临时表加以注意。

一种情况就是被调用的存储过程的返回值是一个基于临时表的结果集。当存储过程执行完毕的时候，临时表并不会消失，因为返回的结果集相当于一个指针，指向临时表所在的内存地址，此时临时表是不会被DROP掉的。这种情况下，既不能在存储过程中删除这个临时表，也不应该由客户应用显示的删除临时表，这就容易出现一些问题。下面我们通过一个例子来说明这个问题。

下面示例代码是返回临时表的存储过程(get_temp_table)：

清单9：返回临时表的存储过程

----------------------------------------------------- -- TEMPORARY TABLE & CURSOR declaration ----------------------------------------------------- DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP ( ID INTEGER, NAME CHAR(30) ) --WITH REPLACE NOT LOGGED; P2: BEGIN DECLARE R_CRSR CURSOR WITH RETURN TO CLIENT FOR SELECT * FROM SESSION.TEMP FOR READ ONLY; INSERT INTO SESSION.TEMP VALUES(1,piName); OPEN R_CRSR; END P2;

存储过程中声明了有两个字段的临时表TEMP,声明了一个游标R_CRSR返回临时表中所有记录，最后在临时表中插入两条记录。

图2：程序第一次执行的结果

　

可以从图中看出，运行结果是我们期望的。那么如果我们再运行一次，会有什么结果呢?下图是其运行结果：

图3：程序再次执行的结果

第二次执行的时候程序却出错了，这是因为在同一个连接中，临时表并没有被DROP掉，所以在第二次调用存储过程的时候就会出现临时表已经存在的错误。

另外一种情况，就是很多时候例如在websphere中通过JDBC连接数据库时使用了连接池的技术，这带来了一些效率的提升，同时在某些情况下也容易让人误解。客户应用程序中关闭了数据库连接，但是并不一定真正关闭了数据库连接，如果客户应用程序使用了临时表而数据库连接并没有关闭，那么临时表就不会被DROP。当连接池把这个连接分给另一个客户程序的时候，新的客户程序仍然可以使用旧的临时表，这不是我们希望的。如果想避免上述问题，可以在创建临时表时，加上WITH REPLACE;或者根据业务逻辑在合适的地方显示的DROP临时表。

下面是使用WITH REPLACE创建临时表的执行情况。

图4：使用WITH REPLACE创建临时表

可以看出在一个连接里面，多次调用存储过程get_temp_table，也不会出现问题。临时表在某些情况下也是需要避免使用的。大家知道临时表是存放在内存中的，如果一个临时表有上万或者十几万条记录，同时程序的并发数很大，那么在内存中建立的临时表耗费的资源就很庞大，此时数据库的性能会急剧下降，甚至会导致数据库崩溃。因此，大家在使用临时表的时候，需要考虑它对资源的耗费，避免盲目使用临时表。