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 表中的示例數據

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