程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> DB2數據庫 >> DB2教程 >> DB2 9.5 SQL Procedure Developer 認證考試 735 准備,第5部分

DB2 9.5 SQL Procedure Developer 認證考試 735 准備,第5部分

編輯:DB2教程

全局聲明式臨時表

臨時表常常用來存儲臨時數據和中間結果。因為它們不需要日志記錄,也不出現在系統編目中,所以可以提高性能。另外,因為臨時表只允許單一連接,所以不需要鎖。

只有聲明(創建)聲明式全局臨時表 (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 字符串用作輸入參數

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved