使用DB2中已聲明的全局臨時表(1)

本文適用於 DB2 UDB Version 8.1 for Linux，UNIX 和 Windows。

簡介

自 DB2 v7.2 以來，DB2 UDB 已經支持在駐留內存、非持久性、特定於會話的表中存儲數據，這些表的正式名稱為已聲明的全局臨時表（以下簡稱為臨時表）。本文不會詳述定義或使用臨時表的各種方法，而是重點介紹一種有趣而鮮為人知的技術，用於在應用程序與存儲過程間共享臨時表。

提示：如果您以前從未使用過臨時表，請查閱 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_samplebegin    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 1BEGIN    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 tableINSERT 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    HAAS000020 MICHAEL      THOMPSON000030 SALLY        KWAN000050 JOHN         GEYER000060 IRVING       STERN5 record(s) selected.Return Status = 0

既然我們已經利用有趣的方法創建了使用臨時表的過程，接下來就演示臨時表及其數據如何可以：

• 在兩個或更多存儲過程之間共享。
• 在一個應用程序和一個存儲過程之間共享。

在 DB2 Development Center 中使用臨時表

步驟 1：在 Development Center 中，創建名為 init_temp() 的過程來封裝臨時表 session.temp 的聲明。

清單 4. 封裝臨時表聲明的過程

CREATE PROCEDURE INIT_TEMP()SPECIFIC INIT_TEMPBEGIN    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_TEMPBEGIN    DROP TABLE SESSION.TEMP;END

使用過程來刪除臨時表有以下兩個優點：

• 在 DB2 Development Center 中執行操作時允許刪除會話的臨時表。
• 完成了通過存儲過程聲明臨時表的封裝。

注意：刪除臨時表並非是絕對必要的，因為當應用程序斷開連接時它們就會被自動刪除。然而，如果您的應用程序使用了連接池，那在返回到連接池的連接前就務必要刪除臨時表。

過程 init_temp() 和 close_temp() 為共享臨時表奠定了基礎。