DB2 已声明的全局临时表

引用: http://www-128.ibm.com/developerworks/cn/db2/library/techarticles/dm-0311yip/index.html 内容： 简介 使用临时表 在 DB2 Development Center 中使用临时表 最后注意 参考资料 关于作者 对本文的评价 订阅: developerWorks 时事通讯 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() 删除临时表。 CALL CLOSE_TEMP @ 这个例子中，我们演示了应用程序如何与存储过程共享临时表。 最后注意对已声明的全局临时表的支持在 DB2 UDB v8.1 中得到了显著的加强。比如，可以在上面创建索引和生成统计结果。您可以查阅 DB2 SQL Reference 中的 DECLARE GLOBAL TEMPORARY TABLE，那里有关于创建及使用临时表的全部详细过程。 临时表存储在用户临时表空间中。如果是使用大型数据集，可考虑用专用的缓冲池来代替表空间。可以通过表空间快照来决定是否将数据真正写入磁盘。 致谢作者要感谢 IBM Toronto Lab 的 Gustavo Arocena 帮助审阅了本文。 参考资料 您可以参阅本文在 developerWorks 全球站点上的 英文原文. DB2 UDB Documentation提供了有关本文中所讨论技术的更多信息。 要获得 SQL 过程语言的更多信息，Paul Yip、Drew Bradstock、Hana Curtis、Michael Gao、Zamil Janmohamed、Clara Liu 和 Fraser McArthur 合作编著的 DB2 SQL Procedural Language for Linux, UNIX and Windows （Prentice Hall，2002）一书就是很好的参考资料。 关于作者 Paul Yip 是来自于 IBM 多伦多实验室的顾问，该实验室负责开发 DB2 用于分布式平台的内核。他的首要任务是帮助 IBM 的业务合作伙伴从其他与之竞争的 RDBMS 平台迁移到 DB2，并加快他们进入市场的步伐。他已经为 DB2 开发者园地写了几篇文章，并且还是最近出版的 DB2 SQL Procedural Language for Linux, UNIX, and Windows 一书的作者之一。可通过 ypaul@ca.ibm.com 与他联系。