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

核心提示：全局声明式临时表临时表常常用来存储临时数据和中间结果，因为它们不需要日志记录，DB2 9.5 SQL Procedure Developer 认证考试 735 准备，第 5 部分，也不出现在系统编目中，所以可以提高性能，还学习了如何用 DB2 层次化查询和 MERGE 功能帮助实现复杂的业务解决方案，在数据库开发中使用

全局声明式临时表

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

只有声明（创建）声明式全局临时表 (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 表中的示例数据

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. 层次化查询的示例

MERGE 语句

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

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

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

下面的语句把职员编号为 000015 的新职员记录插入 MY_EMP 表中。

清单 6. 插入新职员的记录

INSERT INTO my_emp (empno, firstnme, midinit, lastname, workdept,
phoneno, hiredate, job, edlevel, sex, birthdate, salary)
VALUES ('000015', 'MARIO', 'M', 'MALFA', 'A00',
'6669', '05/05/2000', 'ANALYST', 15, 'M', '04/02/1973', 59000.00)

下面的语句把职员编号为 000010 的现有职员的更新工资数据插入 MY_EMP 表中。

INSERT INTO my_emp (empno, firstnme, midinit, lastname, edlevel, salary)
VALUES ('000010', 'CHRISTINE', 'I', 'HAAS', 18, 66600.00)

现在，插入的数据只存在于 MY_EMP 表中，因为它还没有与 EMPLOYEE 表合并。清单 7 给出的 MERGE 语句获取 MY_EMP 表的内容并把它们合并到 EMPLOYEE 表中。

清单 7. MERGE 语句

MERGE INTO employee AS e
USING (SELECT
empno, firstnme, midinit, lastname, workdept, phoneno,
hiredate, job, edlevel, sex, birthdate, salary
FROM my_emp) AS m
ON e.empno = m.empno
WHEN MATCHED THEN
UPDATE SET (salary) = (m.salary)
WHEN NOT MATCHED THEN
INSERT (empno, firstnme, midinit, lastname, workdept, phoneno,
hiredate, job, edlevel, sex, birthdate, salary)
VALUES (m.empno, m.firstnme, m.midinit, m.lastname,
m.workdept, m.phoneno, m.hiredate, m.job, m.edlevel,
m.sex, m.birthdate, m.salary)

这里给源表和目标表分配了关联名，从而避免搜索条件中的表引用出现歧义。此语句指定 MY_EMP 表中应该考虑的列。语句还指定在 MY_EMP 表中的行与 EMPLOYEE 表匹配和不匹配时分别执行的操作。

现在，对 EMPLOYEE 表执行以下查询会返回职员 000015 的记录：

SELECT * FROM employee WHERE empno = '000015'

以下查询返回职员 000010 的记录，其中的 SALARY 列包含更新后的值：

SELECT * FROM employee WHERE empno = '000010'

系统过程 ADMIN_CMD

DB2 在 SYSPROC 模式中提供 ADMIN_CMD 过程，从而允许使用 CALL 语句从应用程序或另一个过程直接运行管理命令。不能从 UDF 或触发器调用此过程。

ADMIN_CMD 有一个 CLOB(2M) 类型的输入参数，其中包含要执行的一个管理命令。调用此过程的语法如下： CALL ADMIN_CMD(command_string);

当前，此过程支持许多 DB2 v9.5 管理命令。下面是最常用的管理命令：

DESCRIBE

EXPORT

FORCE APPLICATION

IMPORT

LOAD

REORG INDEXES/TABLE

RUNSTATS

UPDATE DATABASE CONFIGURATION

在 IBM DB2 9.5 Information Center 上可以找到支持的命令的完整列表。

下面的示例演示此过程的使用方法：

清单 8. ADMIN_CMD 过程的使用示例

CREATE PROCEDURE test_admin_cmd (　)
P1: BEGIN
　　DECLARE sql_string VARCHAR(200);
　　SET sql_string ='LOAD FROM C:DB9.5_testorg_exp.txt OF DEL
　　　　　　　　　　 METHOD P (1, 2, 3, 4, 5) INSERT INTO DB2ADMIN.ORG1
　　　　　　　　　　 (DEPTNUMB, DEPTNAME, MANAGER, DIVISION, LOCATION)
　　　　　　　　　　　COPY NO INDEXING MODE AUTOSELECT';
　　CALL SYSPROC.ADMIN_CMD(sql_string);
END P1

GET DIAGNOSTIC 语句

SQL PL 提供一个 GET DIAGNOSTICS 语句，用于获取前面执行的 SQL 语句的相关信息。例如，如果需要查明一个 INSERT、DELETE 或 UPDATE 语句影响的行数，就可以使用带 ROW_COUNT 选项的 GET DIAGNOSTICS 语句提供此信息。

清单 9 是 GET DIAGNOSTICS 语句的语法图：

清单 9. GET DIAGNOSTICS 语句的语法

>>-GET DIAGNOSTICS---------------------------------------------->
>--+-SQL-variable-name--=--+-ROW_COUNT---------+-+-------------><
　|　　　　　　　　　　　 '-DB2_RETURN_STATUS-' |　
　　　　　　　　　　　'-| condition-information |
-------------------'　
condition-information
|--EXCEPTION--1------------------------------------------------->
　.-,------------------------------------------.　
　　　　　　　　　　　V　　　　　　|　
>----SQL-variable-name--=--+-MESSAGE_TEXT-----+-+---------------|
　　　　　　　　　　　　　'-DB2_TOKEN_STRING-'　　

可以使用 GET DIAGNOSTICS 语句获取以下信息：

前面执行的 SQL 语句处理的行数

与前一个 CALL 语句相关联的过程返回的状态值

前面执行的 SQL 语句返回的 DB2 错误或警告消息文本

SQL-variable 声明取决于希望获得的信息。如果要存储 ROW_COUNT 或 DB2_RETURN_STATUS，就需??把它声明为 INTEGER；如果要存储错误或警告消息，就应该声明为 varchar(70)。

请注意，GET DIAGNOSTICS 语句不会改变特殊变量 SQLSTATE 和 SQLCODE 的内容。

下面的示例演示 GET DIAGNOSTICS 语句的使用方法：

清单 10. 通过 Get DIAGNOSTICS 语句获取 ROW_COUNT

CREATE PROCEDURE UPDATE_RCOUNT(sales_corr INT, qtr int, OUT row_updated INT)
P1: BEGIN　　
　　UPDATE SALESBYQUARTER
　　SET sales = sales+sales_corr
　　WHERE y < year(current date)
　　　and q = qtr;
　　GET DIAGNOSTICS row_updated = ROW_COUNT; 　　
　　END P1

清单 11. 通过 GET DIAGNOSTICS 语句获取消息文本

CREATE PROCEDURE mess_text1 (num int, new_status varchar(10),
　　　　　　　 OUT p_err_mess varchar(70))　　　　　　　
P1: BEGIN
　　DECLARE SQLCODE INTEGER default 0;
　　DECLARE SQLSTATE CHAR(5) default '';
　　DECLARE v_trunc int default 0;
　　
　　DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
　　　 BEGIN
　　　　　　GET DIAGNOSTICS EXCEPTION 1 p_err_mess = MESSAGE_TEXT;
　　　　　　SET v_trunc = 2;　　　　
　　　 END;　　　　　　　　　　　　　　　　　　　　　　　　　　　　　
　 INSERT INTO tab1 VALUES (num, new_status);　
　 RETURN v_trunc;
END P1

DB2_RETURN_STATUS 返回调用的 SQL 过程的状态值。如果调用的过程成功执行，它会返回零值，否则返回表示失败的正值。

清单 12. 通过 GET DIAGNOSTICS 语句获取状态值

CREATE PROCEDURE myproc1 ()
　 A1:BEGIN
　　DECLARE RETVAL INTEGER DEFAULT 0;
　　…
CALL MYPROC2;
　　GET DIAGNOSTICS RETVAL = DB2_RETURN_STATUS;
　　IF RETVAL <> 0 THEN
　　　 …
　　　 LEAVE A1;
　　ELSE
　　　 …
　　END IF;
　 END A1

保存点

保存点提供一种在工作单元（事务）内实现子事务的方法。实现的办法是在事务内创建多个以后可以引用的引用点（保存点）。例如，在一个应用程序中，可以设置多个保存点；在创建保存点之后的任何时候，都可以把工作回滚到保存点，而不影响在此保存点之前执行的任何工作。

下面是设置保存点的语法：

SAVEPOINT　savepoint-name ON ROLLBACK RETAIN CURSORS

下面给出一个对嵌套的保存点执行回滚操作的示例。首先，创建一个名为 T1 的表。然后，在 T1 表中插入一行。执行插入之后，创建第一个保存点 (SAVEPOINT1)。创建这个保存点之后，在 T1 表中插入另一行并创建另一个保存点 (SAVEPOINT2)。然后，在 T1 表中插入第三行。然后，创建最后一个保存点 (SAVEPOINTS3)。最后，在 SAVEPOINT3 后面插入另一行。现在，在 T1 表中有四行（‘A’、‘B’、‘C’ 和 ‘D’）。

现在，回滚到 savepoint3，这会从 T1 表中删除 ‘D’ 行。接下来，回滚到 savepoint1，这会删除 ‘C’ 和 ‘B’ 行。然后，提交事务，这会导致保存点不再可用。然后，在 T1 表中插入 ‘E’ 行。现在，在 T1 表中有两行（‘A’ 和 ‘E’）。

清单 13 给出实现这些操作的命令。

清单 13. 使用保存点

CREATE PROCEDURE p1()
BEGIN
　CREATE TABLE T1 (C1 CHAR);
　　
　INSERT INTO T1 VALUES ('A');
　SAVEPOINT SAVEPOINT1 ON ROLLBACK RETAIN CURSORS;
　INSERT INTO T1 VALUES ('B');
　SAVEPOINT SAVEPOINT2 ON ROLLBACK RETAIN CURSORS;
　INSERT INTO T1 VALUES ('C');
　SAVEPOINT SAVEPOINT3 ON ROLLBACK RETAIN CURSORS;
　INSERT INTO T1 VALUES ('D');
　ROLLBACK TO SAVEPOINT SAVEPOINT3;
　ROLLBACK TO SAVEPOINT SAVEPOINT1;
COMMIT WORK;
　　
　INSERT INTO T1 VALUES ('E');
　　
END_cnnew1@

PureXML 和存储过程

DB2 9.5 是一种混合型数据库，它支持 XML 数据类型的列以及用于操作 XML 数据的方法和函数。SQL 过程支持 XML 数据类型的参数和变量。这些变量还可以作为参数传递给 XMLEXISTS、XMLQUERY 和 XMLTABLE 表达式中的 XQuery 表达式。

下面的存储过程返回的结果集包含一个关系列和来自 XML 列的属性（客户名）：

清单 14. 存储过程示例

CREATE PROCEDURE proc_wXML (　)
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT cid, xmlquery('declare default element namespace　
　　　　　　　 "http://posample.org";
　　　　　 $cu/customerinfo/name/text()' passing d.info as "cu")
　　 from customer d;
-- Cursor left open for client application
OPEN cursor1;
END P1
The next procedure is accepting XML as input variable along with integer parameter
and inset row into CUSTOMER table that has INFO column declared as XML:
CREATE PROCEDURE PROC_INS_XML1 ( v_cid int, v_info XML)
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
INSERT INTO CUSTOMER (CID, INFO)
　VALUES (v_cid, v_info);
END P1

如果使用 IBM Data Studio 运行此过程，可以把 XML 直接解析为输入值，见图 2。

图 2. XML 字符串用作输入参数

结束语

在本教程中，您学习了如何在存储过程中声明和使用全局临时表。还学习了如何使用保存点和 GET DIAGNOSTICS 语句改进数据库应用程序。另外，还学习了如何用 DB2 层次化查询和 MERGE 功能帮助实现复杂的业务解决方案。在数据库开发中使用 XML 技术可以显著简化复杂的业务逻辑。掌握这些高级特性有助于您通过 735 认证考试。