Paul Yip
DB2 Partner Enablement，IBM Toronto
2003 年 11 月 本文阐述了如何在 DB2 通用数据库中使用已声明的全局临时表。这对于在应用程序与存储过程之间共享数据是一种十分有用的技术。
本文适用于 DB2 UDB Version 8.1 for Linux，UNIX 和 Windows。
简介
自 DB2 v7.2 以来，DB2 UDB 已经支持在驻留内存、非持久性、特定于会话的表中存储数据，这些表的正式名称为已声明的全局临时表（以下简称为临时表）。本文不会详述定义或使用临时表的各种方法（这些主题在 SQL Reference中已涵盖），而是重点介绍一种有趣而鲜为人知的技术，用于在应用程序与存储过程间共享临时表。
| 提示：如果您以前从未使用过临时表，请查阅 DB2 UDB v8 SQL Reference 第 5 章中标题 DECLARE GLOBAL TEMPORARY TABLE 下的内容。 |
使用临时表
我们记得为了要使用临时表，数据库中必须存在一个用户临时表空间（默认情况下没有）。这里先教您如何创建这样一个表空间：
create user temporary tablespace usertemp1
managed by system using ('usertemp1')
|
临时表在进行引用之前必须先声明。下面的 SQL 过程（从命令行创建）演示了这个过程：
清单 1. 在 SQL 过程中使用临时表的例子
db2 –td@
connect to sample @
create procedure temp_table_sample()
specific temp_table_sample
begin
declare global temporary table -- (1)
session.temp (id int, data varchar(10))
not logged on commit preserve rows;
insert into session.temp values (1, 'A'); -- (2)
end
@
|
| 提示：如果您想进一步寻找有关 DB2 SQL PL 的指南，请购买 DB2 SQL Procedural Language for Linux, UNIX, and Windows（ISBN 0131007726），可从任何在线书店订购。 |
只有在(2)处引用之前，先在(1)处声明临时表，该 SQL 过程才能起作用。
虽然临时表总是用于 SQL 过程中，但也能被任何可连接 DB2 的应用程序创建。下面很快就能看到，您甚至可以通过 DB2 命令行处理器（CLP）使用临时表。
当声明临时表的位置与使用它的位置不同时，会引起问题。考虑以下情况：
- 一个应用程序需要声明临时表 T，填充它后并调用过程 P 来处理数据。应用程序和过程 P 如何都能引用 T 而无需过程 P 再重复声明 T 呢？
- 存储过程 P1 声明临时表 T，填充它后并且希望调用过程 P2 和 P3 来处理数据。P2 和 P3 又如何能引用 P1 中声明的临时表而无需再重新声明呢？
这两种情况下，若在相同会话中企图重新声明临时表将导致如下错误：
SQL0601N The name of the object to be created is identical to the
existing name "SESSION.TEMP" of type "DECLARED TEMPORARY TABLE".
SQLSTATE=42710
|
解决这些问题的关键是要明白如果您事先在当前的连接中已经声明了临时表，那么就可以创建存储过程来引用该临时表而无需再声明了。清单 2 举例说明了如何实现该方法：
清单 2. 在 SQL 过程中使用临时表的例子
CONNECT TO SAMPLE@
-- Declare a temporary table from CLP
DECLARE GLOBAL TEMPORARY TABLE temp_employee LIKE employee ON COMMIT PRESERVE ROWS@
-- create a procedure which references the temporary table.
CREATE PROCEDURE p1
RESULT SETS 1
BEGIN
DECLARE cur CURSOR WITH HOLD WITH RETURN TO CLIENT FOR
SELECT empno, firstnme, lastname
FROM SESSION.temp_employee
FETCH FIRST 5 ROWS ONLY;
OPEN cur;
END
@
-- Now, from the CLP (an application)
-- INSERT into the temp table
INSERT INTO SESSION.temp_employee SELECT * FROM EMPLOYEE@
-- CALL the procedure, which returns an open cursor back to the
-- CLP, proving that the temp table data was received.
CALL p1@
|
在上面的例子中，创建 CLP 连接后声明了一个临时表。然后创建了引用临时表的过程 p1。这里要注意的关键是，即使在过程体中没有声明临时表，该过程也被成功创建。为了证实此概念，创建过程后，我们手工填充了临时表，并调用 p1 来显示该表的内容。清单 3 给出了输出结果：
清单 3. 执行过程 p1 的输出
Result set 1
--------------
EMPNO FIRSTNME LASTNAME
------ ------------ ---------------
000010 CHRISTINE HAAS
000020 MICHAEL THOMPSON
000030 SALLY KWAN
000050 JOHN GEYER
000060 IRVING STERN
5 record(s) selected.
Return Status = 0
|
既然我们已经利用有趣的方法创建了使用临时表的过程，接下来就演示临时表及其数据如何可以：
- 在两个或更多存储过程之间共享。
- 在一个应用程序和一个存储过程之间共享。
在 DB2 Development Center 中使用临时表
步骤 1：在 Development Center 中，创建名为 init_temp() 的过程来封装临时表 session.temp 的声明。
清单 4. 封装临时表声明的过程
CREATE PROCEDURE INIT_TEMP()
SPECIFIC INIT_TEMP
BEGIN
DECLARE GLOBAL TEMPORARY TABLE
SESSION.TEMP (id INT, data VARCHAR(10))
ON COMMIT PRESERVE ROWS;
END
|
使用过程来初始化临时表可带来三个主要优点：
- 从 DB2 Development Center 很容易为数据库连接声明临时表。
- 过程开发人员不必为了得到临时表的 DDL 而找遍应用程序代码（可能是由其他人来维护的）。
- 如果在应用程序代码中的多个位置实例化相同的临时表，该表的定义可以集中在一个地方。因此，假如要求改变表结构，就不必搜索所有的声明，而只需在一个地方改变其定义。
步骤 2：在 Development Center 中，创建过程 close_temp() 来删除临时表 session.temp：
清单 5. 封装删除临时表的过程
CREATE PROCEDURE CLOSE_TEMP()
SPECIFIC CLOSE_TEMP
BEGIN
DROP TABLE SESSION.TEMP;
END
|
使用过程来删除临时表有以下两个优点：
- 在 DB2 Development Center 中执行操作时允许删除会话的临时表。
- 完成了通过存储过程声明临时表的封装。
| 注意：删除临时表并非是绝对必要的，因为当应用程序断开连接时它们就会被自动删除。然而，如果您的应用程序使用了连接池，那在返回到连接池的连接前就务必要删除临时表。 |
过程 init_temp() 和 close_temp() 为共享临时表奠定了基础。
例 1：创建引用临时表的过程
步骤 1：在 Development Center 中，运行 init_temp() 过程 来声明 Development Center 的连接环境下的临时表。
图 1. 运行 init_temp
Development Center 在过程运行完毕后还将保持连接状态。因此，在运行 close_temp() 或是重设 Development Center 的连接之前，该临时表依然继续存在。
步骤 2：创建以下过程 insert_to_temp() ，用于执行 session.temp 上的插入操作。请注意 insert_to_temp() 不需要再声明临时表了。
CREATE PROCEDURE INSERT_TO_TEMP()
SPECIFIC INSERT_TO_TEMP
BEGIN
INSERT INTO SESSION.TEMP VALUES (1, 'one');
INSERT INTO SESSION.TEMP VALUES (2, 'two');
INSERT INTO SESSION.TEMP VALUES (3, 'three');
END
|
步骤 3：创建 count_rows()过程， 返回在临时表中找到的行数。再次说明，可以引用 session.temp 是因为在步骤 1 中我们调用了 init_temp()。
CREATE PROCEDURE COUNT_ROWS(OUT p_count INTEGER)
BEGIN
DECLARE cur CURSOR FOR
SELECT count(*) FROM session.temp;
OPEN cur;
FETCH cur INTO p_count;
CLOSE cur;
END
|
步骤 4：运行 insert_to_temp() 。向临时表填充三行记录。
步骤 5：运行 count_rows() 。输出参数 p_count 将显示找到了 3 行记录。
图 2. 在两个过程间共享临时表
这个例子中，我们演示了如何在两个过程中共享一个临时表。当然，该技术能很容易地扩展为在任意多个过程间共享任意多个临时表。
步骤 6：删除。运行 close_temp() 来删除临时表。
例 2：在应用程序和存储过程之间共享临时表
对于这些例子，我们将使用 CLP 作为应用程序。
步骤 1：连接数据库并运行 init_temp() 来声明 CLP 连接环境中的临时表。
CONNECT TO SAMPLE
CALL INIT_TEMP()
|
步骤 2：向 session.temp 表插入几行记录。
INSERT INTO SESSION.TEMP VALUES (1, 'two')
INSERT INTO SESSION.TEMP VALUES (2, 'two')
|
步骤 3：运行 count_rows() 过程，并确认该过程能够查看到刚才插入的行。
CALL count_rows(?)
Value of output parameters
--------------------------
Parameter Name : P_COUNT
Parameter Value : 2
Return Status = 0
|
步骤 4：删除。运行 close_temp() 删除临时表。
这个例子中，我们演示了应用程序如何与存储过程共享临时表。
最后注意
对已声明的全局临时表的支持在 DB2 UDB v8.1 中得到了显著的加强。比如，可以在上面创建索引和生成统计结果。您可以查阅 DB2 SQL Reference 中的 DECLARE GLOBAL TEMPORARY TABLE，那里有关于创建及使用临时表的全部详细过程。
临时表存储在用户临时表空间中。如果是使用大型数据集，可考虑用专用的缓冲池来代替表空间。可以通过表空间快照来决定是否将数据真正写入磁盘。 |
