临时表

在 DB2 存储过程开发中经常使用临时表。合理的使用临时表可以简化程序的编写，提供执行效率，然而滥用临时表同样也会使得程序运行效率降低。

临时表一般在如下情况下使用：

临时表用于存储程序运行中的临时数据。例如，如果在一个程序中第一条查询语句执行的结果会被后续的查询语句用到，那么我们可以把第一次查询的结果存储在一个临时表中供后续查询语句使用，而不是在后续查询语句中重新查询一次。如果第一条查询语句非常复杂和耗时，那么上面的策略是非常有效的。

临时表可以用于存储在一个程序中需要返回多次的结果集。例如，程序中有一个很耗资源的多表查询，同时，该查询在程序中需要执行多次，那么就可以把第一次查询的结果集存储在临时保中，后续的查询只需要查临时表就可以了。

临时表也可以用于让 SQL 访问非关系型数据库。例如，可以编写程序把非关系型数据库中的数据插入到一个全局临时表中，那么我们就可以对其数据进行查询。

我们可使用 DECLARE GLOBAL TEMPORARY TABLE 语句来定义临时表。 DB2 的临时表是基于会话的，且在会话之间是隔离的。当会话结束时，临时表的数据被删除，临时表被隐式卸下。对临时表的定义不会在 SYSCAT.TABLES 中出现 下面是定义临时表的一个示例：

创建有两个字段的临时表

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

 

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

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

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

create procedure Test(

)

DYNAMIC RESULT SETS 1

LANGUAGE SQL

BEGIN

-----------------------------------------------------

TEMPORARY TABLE & CURSOR declaration

-----------------------------------------------------

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP

(

ID         INTEGER ,

NAME      CHAR (30)        

)

--WITH REPLACE

NOT LOGGED;

 

 

INSERT INTO SESSION.TEMP VALUES (1, 'zhangsan' );

BEGIN

DECLARE R_CRSR CURSOR WITH RETURN TO CLIENT FOR

SELECT * FROM SESSION.TEMP

FOR READ ONLY;

OPEN R_CRSR;

END ;

 

END @

运行后得到的结果 :

call Test()

completed successfully.

 

结果集 1

--------------

 

ID          NAME                         

----------- ------------------------------

1 zhangsan                     

 

1 条记录已选择。

返回状态 = 0

Statement processed successfully in 0.29 secs.

可以看到是我们想要的结果，但是如果再次运行这个存储过程会怎样呢 .

再次 call Test() 后报错 ,

可以看到报的是已经有 SESSION.TEMP 的错误。

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

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

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

create procedure Test(

)

DYNAMIC RESULT SETS 1

LANGUAGE SQL

BEGIN

-----------------------------------------------------

TEMPORARY TABLE & CURSOR declaration

-----------------------------------------------------

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP

(

ID         INTEGER ,

NAME      CHAR (30)        

)

WITH REPLACE

NOT LOGGED;

 

 

INSERT INTO SESSION.TEMP VALUES (1, 'zhangsan' );

BEGIN

DECLARE R_CRSR CURSOR WITH RETURN TO CLIENT FOR

SELECT * FROM SESSION.TEMP

FOR READ ONLY;

OPEN R_CRSR;

END ;

 

END @

运行结果为 :

call test()

completed successfully.

结果集 1

--------------

 

ID          NAME                         

----------- ------------------------------

1 zhangsan                     

1 条记录已选择。

返回状态 = 0

再次运行 call test() ，这次并没有报错，反正和第一次一样的结果。可以看出使用 WITH REPLACE 之后在一个连接里面，多次调用存储过程的临时表，也不会出现问题。

 

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