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

核心提示：步骤 3. 当在过程中引用临时表时，需要在临时表名前面加上模式名 SESSION，DB2 9.5 SQL Procedure Developer 认证考试 735 准备，第5部分(2)，下面的示例演示临时表的使用方法：清单 3. 临时表的使用示例CREATE PROCEDURE DB2ADMIN.temp_table

步骤 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 表中的示例数据

ASSEMBLY_ID	SUB_ASSEMBLY_ID	ASSEMBLY_NM
1000		Computer
1000	1100	Hard Drive
1000	1200	Monitor
1000	1300	Keyboard
1000	1400	Mouse
1100	1110	Hard drive Cables
1300	1310	Keyboard Cables
1400	1410	Mouse
1000	1500	Motherboard
1500	1510	Processors
1500	1550	RAM

下面的语句执行一个递归式查询。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. 层次化查询的示例