DB2 9.5 SQL Procedure Developer 认证考试 735 准备，第 5 部分

全局声明式临时表

临时表常常用来存储临时数据和中间结果。因为它们不需要日志记录，也不出现在系统编目中，所以可以提高性能。另外，因为临时表只允许单一连接，所以不需要锁。

只有声明（创建）声明式全局临时表 (DGTT) 的连接才能访问它。当这个数据库连接结束时，临时表被删除。

要想创建 DGTT，需要执行 DECLARE GLOBAL TEMPORARY TABLE 语句。下面是这个语句的语法图：

清单 1. 全局临时表声明的语法

　　　 .-,---------------------.　　　　　　　　　　　　　　　　　　
　　　　V　　　　　　　　　　　 |　　　　　　　　　　　　　　　　　　
>--+-(----| column-definition |-+--)-----------------------------+-->
　 +-LIKE--+-table-name1-+--+------------------+-----------------+　
　 |　　　 '-view-name---'　'-| copy-options |-'　　　　　　　　 |　
　 '-AS--(--fullselect--)--DEFINITION ONLY--+------------------+-'　
　　　　　　　　　　　　　　　　　　　　　　'-| copy-options |-'　　
　　　.-ON COMMIT DELETE ROWS---.　　　
>--?--+-------------------------+--?---------------------------->
　　　'-ON COMMIT PRESERVE ROWS-'　　　
>--+-------------------------------------------+---------------->
　 |　　　　　　 .-ON ROLLBACK DELETE ROWS---. |　
　 '-NOT LOGGED--+---------------------------+-'　
　　　　　　　　 '-ON ROLLBACK PRESERVE ROWS-'　　
>--?--+--------------+--?--+---------------------+-------------->
　　　'-WITH REPLACE-'　　 '-IN--tablespace-name-'　
>--?--+------------------------------------------------------------+--?-><
　　　|　　　　　　　　　　　.-,-----------.　　　　　　　　　　　 |　　　
　　　|　　　　　　　　　　　V　　　　　　 |　　 .-USING HASHING-. |　　　
　　　'-PARTITIONING KEY--(----column-name-+--)--+---------------+-'　　　
column-definition
|--column-name--| data-type |--+--------------------+-----------|
　　　　　　　　　　　　　　　 '-| column-options |-'　

请注意，当指定 WITH REPLACE 子句时，会删除同名的现有 DGTT 并替换为新的表定义。

定义同名的声明式全局临时表的每个会话拥有自己的独特的临时表描述。当会话终止时，表行和临时表描述被删除。

下面解释一些选项：

ON COMMIT DELETE ROWS：在执行 COMMIT 操作时，如果表上没有打开 WITH HOLD 游标，就删除表中的所有行。这是默认设置。

ON COMMIT PRESERVE ROWS：在执行 COMMIT 操作时，保留表中的所有行。

ON ROLLBACK DELETE ROWS：在执行 ROLLBACK（或 ROLLBACK to SAVEPOINT）操作时，如果已经修改了表数据，就删除表中的所有行。这是默认设置。

ON ROLLBACK PRESERVE ROWS：在执行 ROLLBACK（或 ROLLBACK to SAVEPOINT）操作时，保留表中的所有行。

注意，BLOB、CLOB、DBCLOB、LONG VARCHAR、LONG VARGRAPHIC、XML、引用和结构化类型不能用作声明式全局临时表的列的数据类型。

按照以下步骤使用 DB2 GDTT：

步骤 1. 确保有用户临时表空间存在。如果没有用户临时表空间，那么使用以下语法执行 CREATE USER TEMPORARY TABLESPACE 语句：

CREATE USER TEMPORARY TABLESPACE usr_tbsp MANAGED BY SYSTEM USING
　　　　　　　　 ('c:tempusertempspace') ;

步骤 2. 使用前面提供的语法在应用程序中执行 DECLARE GLOBAL TEMPORARY TABLE 语句。例如：

清单 2. DGTT 声明的示例

DECLARE GLOBAL TEMPORARY TABLE temp_proj
　　　　 (projno CHAR(6), projname VARCHAR(24), projsdate DATE, projedate DATE,)
　　WITH REPLACE
　　ON COMMIT PRESERVE ROWS　　
　　NOT LOGGED　　
　　IN usr_tbsp ;

声明式临时表的数据库模式总是 SESSION。

步骤 3. 当在过程中引用临时表时，需要在临时表名前面加上模式名 SESSION。下面的示例演示临时表的使用方法：

清单 3. 临时表的使用示例

CREATE PROCEDURE DB2ADMIN.temp_table (　)
P1: BEGIN
　 DECLARE GLOBAL TEMPORARY TABLE temp1　AS
　　　　 ( SELECT deptnumb as dnum,
　　　　　　　　　deptname as name,
　　　　　　　　　 manager as mgr
　　　　　　 FROM　org )
　 DEFINITION ONLY ON COMMIT PRESERVE ROWS;
　 BEGIN
　　 DECLARE c1 CURSOR WITH RETURN FOR
　　 SELECT dnum, mgr　FROM SESSION.temp1;
　　 INSERT INTO SESSION.temp1 (dnum, name, mgr)
　　　　　(SELECT deptnumb, deptname, manager
　　　　　　FROM org);
　　 OPEN c1;
　　END;
END

层次化查询

在关系数据库中表达层次化关系

当使用关系数据库中的层次化数据时，获取和显示数据都比较困难。递归式 SQL 语句提供了一种使用这些复杂逻辑结构的方法。

在递归式 SQL 语句中，会对结果集重复应用一个 SQL 语句，以便生成进一步的结果。采用一种引用本身的通用表表达式构建这种 SQL 语句（即，它使用自己的定义）。这种查询 “with (…) as tabname” 也称为通用表表达式 (CTE)。

这种数据结构的示例往往包含很多数据。下面的表表示计算机硬件信息，这些信息形成一个层次结构。在此示例中，计算机包含硬盘驱动器、监视器、键盘、鼠标和主板等部件。部件本身可以分解为子部件或组件，比如主板包含处理器和 RAM。

表 1. bill_of_materials 表中的示例数据

下面的语句执行一个递归式查询。WITH 语句定义一个名为 ASSEMBLY 的临时表。UNION ALL 的上半部分只被调用一次。它在配件表中填充五行，这五行的配件 ID 都是 1000。

UNION ALL 的下半部分递归地执行，直到没有匹配为止。也就是说，这个递归式查询逐行循环遍历 bill_of_materials 表，创建最终的结果集，然后输入给递归式查询的下一次迭代。

最后，SELECT 语句返回刚才用 CTE 创建的临时表 ASSEMBLY 中的行。

清单 4. WITH 语句

WITH assembly
　 (sub_assembly_id, assembly_nm, assembly_id) AS
(SELECT sub_assembly_id, assembly_nm, assembly_id
FROM bill_of_materials
WHERE assembly_id=1000
UNION ALL
SELECT child.sub_assembly_id,
　　　 child.assembly_nm,
　　　 child.assembly_id
FROM　bill_of_materials child, assembly p
WHERE child.assembly_id = p.sub_assembly_id)
SELECT assembly_id, sub_assembly_id, assembly_nm from assembly;

WITH 语句返回的最终结果集如下：

清单 5. WITH 语句返回的最终结果集

ASSEMBLY_ID SUB_ASSEMBLY_ID ASSEMBLY_NM
　　 1000　　　　　1100　　　　　　Hard Drive
　　 1000　　　　　1200　　　　　　Monitor
　　 1000　　　　　1300　　　　　　Keyboard
　　 1000　　　　　1400　　　　　　Mouse
　　 1000　　　　　1500　　　　　　Motherboard
　　 1100　　　　　1110　　　　　　Hard drive Cables
　　 1300　　　　　1310　　　　　　Keyboard Cables
　　 1400　　　　　1410　　　　　　Mouse Cables
　　 1500　　　　　1510　　　　　　Processors
　　 1500　　　　　1550　　　　　　RAM
　10 record(s) selected.

图 1 给出这些结果的图形化视图。

图 1. 层次化查询的示例

MERGE 语句

使用 MERGE 语句组合有条件更新、插入或删除操作

MERGE 语句 使用来自源表的数据更新目标表或可更新视图。在一次操作期间，可以对目标表中与源表匹配的行进行更新或删除，同时插入目标表中不存在的行。

例如，假设 EMPLOYEE 表是目标表，其中包含关于一家大公司的职员的最新信息。分支机构办公室通过维护自己的 EMPLOYEE 表版本 MY_EMP 来更新本地职员记录。通过使用 MERGE 语句，可以用 MY_EMP 表