程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> DB2數據庫 >> DB2教程 >> 在 DB2 SQL 存儲過程中使用 XML

在 DB2 SQL 存儲過程中使用 XML

編輯:DB2教程

簡介

與以前的 DB2 版本相比,IBM® DB2® 9 for Linux, UNIX®, and Windows® 的主要改進是添加了新的 XML 功能。在 DB2 9 之前,可以將 XML 文檔存儲為 LOB,或者將傳入的 XML 數據分解到關系表中,這個過程需要使用 XML Extender。但是,DB2 9 使用戶能夠按照 XML 固有的層次化格式存儲和查詢 XML 數據 —— 這種技術稱為 pureXML™ 支持。現在,可以在 XML 數據上建立索引,並通過調用存儲過程執行分解。

本文討論如何在 SQL 存儲過程中使用 XML。我提供了許多代碼示例來幫助演示特定的技術要點。提供示例的目的在於幫助您理解,因此示例要盡可能簡單。為此,示例可能有點兒不夠真實。

注意:本文假設您是應用程序開發人員或 DBA,而且已經熟悉如何編寫 SQL 存儲過程。

XML 簡介

XML 表示 “可擴展標記語言(extensible markup language)”,這是一種表示信息的層次化方法,它的語法與 Html 非常相似。XML 文檔是一個由 “元素(element)” 或分支組成的樹結構。每個元素有名稱,可以包含文本值,可以具有一系列屬性(格式為 “name=value”),可以有子元素。允許重復的元素名稱,但是一個元素中的屬性名稱必須是惟一的。

XQuery 是一種用於 XML 的查詢語言,它基於 W3C 標准。XQuery 常用於在關系數據庫外查詢 XML 數據,這些數據可能存儲在文本文件中。XQuery 可以訪問 XML 數據(包括聯結)、循環、聲明的變量、IF/THEN/ELSE 語句和其他結構。

在 DB2 9 中有四種處理 XML 數據的方式:

一般 SQL

一般 SQL 由常規 SQL 語句和函數組成,它們沒有引用 XPath 或 XQuery。一般 SQL 只能將 XML 文檔作為完整的實體進行操作。可以在數據庫中插入和檢索 XML 文檔,以及在實體和文本之間進行轉換。但是,無法解析 XML 文檔的一部分,也無法應用任何謂詞。

帶嵌入 XQuery 的 SQL/XML

SQL/XML 包含三個操作 XML 數據的新函數,這些函數以嵌入的 XQuery 命令作為參數 —— XMLQUERY、XMLEXISTS 和 XMLTABLE。

XMLQUERY 用來從 XML 數據中提取信息。通過結合使用 XMLCAST 函數,可以從 XML 中派生出關系數據。

XMLEXISTS 對 XML 數據應用謂詞,常常用在 WHERE 子句中。

XMLTABLE 以關系表的形式提取 XML 數據。

XQuery

XQuery 現在是 DB2 數據庫引擎中的本機特性。有兩個用來訪問 DB2 數據的函數。第一個函數 db2-fn:xmlcolumn 向 XQuery 返回 XML 列的值。包含所有行;沒有謂詞。

帶嵌入 SQL 的 XQuery

還提供 db2-fn:sqlquery 函數來訪問 DB2 數據,它以一個 SELECT 語句作為參數。這個 SELECT 可以執行所需的任何操作,但是必須在 SELECT 列表中返回一個 XML 類型的列。

存儲過程中支持的 XML 功能

對於 DB2 9,SQL 存儲過程有一些用來處理 XML 數據的新功能。有一種新的數據類型 “XML”。不但可以將它用於表中的列,還可以用於參數和聲明的變量。DB2 9 還增強了 SELECT 和 UID 語句,增加了幾個新的 XML 函數。

這在三個方面增強了 SQL 存儲過程:

可以在以下位置使用新的 XML 函數(例如,XMLQUERY、XMLEXISTS、XMLTABLE):

在游標中

允許 SELECT 語法的任何地方,比如 SELECT INTO 和 VALUES

變量賦值,比如 SET 語句

XML 列可以:

在游標結果集中返回

綁定(?表示法)到 SELECT 和 UID 語句中

IF/THEN/ELSE 語句可以測試 XMLEXISTS 謂詞

可以從以下語句裝載 XML 類型的變量:

SELECT INTO 語句

VALUES INTO 語句

FETCH INTO 語句

EXECUTE INTO 語句

SET 語句

限制和解決方法

SQL 存儲過程中的 XML 功能有一些限制:

XQuery 查詢只能作為動態游標的一部分運行。

XML 變量在 COMMIT 或 ROLLBACK 之後不可用。

動態游標中的 XQuery

XQuery 命令可以出現在兩種不同的上下文中:

放在單引號中,作為 SQL/XML 函數 XMLQUERY、XMLEXISTS 和 XMLTABLE 的參數

SQL/XML 函數中嵌入的 XQuery 允許靜態 SQL 語句。在下面的示例中,變量 adrs 是一個 XML 列或 XML 類型的變量。


清單 1. SQL/XML 函數中嵌入的 XQuery

XMLQUERY('$d/cust/name' PASSING adrs AS "d") 
注意: 這個示例只包含一個非常簡單的 XPath 表達式,但是 SQL/XML 函數(XMLQUERY、XMLEXISTS、XMLTABLE)可以包含任何有效的 XQuery,包括復雜的 FLWOR 表達式。

作為查詢

XQuery 查詢只能在動態游標中定義。以下代碼在 SQL 存儲過程中是不允許的:

清單 2. 在 SQL 存儲過程中不允許靜態 XQUERY

XQUERY 
for $dept in db2-fn:XMLcolumn("DEPT.DEPTDOC")/dept 
where $dept/@deptID="PR27" 
return $dept/employee/name 

在 COMMIT 或 ROLLBACK 之後的 XML 變量

XML 變量(包括參數和聲明的變量)在 COMMIT 或 ROLLBACK 之後不可用。如果試圖使用 XML 變量,而沒有首先給它們賦新值,就會產生下面這樣的錯誤:
清單 3. 在 COMMIT/ROLLBACK 之後收到的錯誤消息

SQL1354N An SQL variable in routine "XXX" is not available for reference due 
 to a recent commit or rollback Operation. SQLSTATE=560CE 


如果需要在存儲過程中顯式地控制提交點,那麼考慮以下選項:

將變量的 XML 文檔傳遞進一個持久性的表中。使用 “持久性的” 表是因為聲明的全局臨時表當前不允許 XML 列。

將 XML 變量串行化成 VARCHAR,在 COMMIT 或 ROLLBACK 之後再解析它。

代碼示例

我們來研究一下如何在 DB2 存儲過程中處理 pureXML 數據。為此,我們將考慮幾個與常見編程場景相關的示例。

生成測試數據

盡管在開發期間可以從命令行測試存儲過程,但是可能需要創建虛構的 XML 文檔,它們將作為輸入參數發送。清單 4 提供一種生成測試數據的簡單技術:
清單 4. 生成測試數據(1)

CALL ex_proc 
( 
 XMLPARSE( 
  DOCUMENT 
  ' 
   <customer> 
    <custid>HX25</custid> 
   </customer> 
  ' 
 ) 
) 
; 


為了清晰,前一個示例保留了許多空白,使 XML 標記處於單獨的行上。但是,也可以讓代碼更緊湊:
清單 5. 生成測試數據(2)

CALL ex_proc(XMLPARSE(DOCUMENT '<customer><custid>HX25</custid></customer>')); 

聲明參數

新的 XML 數據類型可以作為參數傳遞進存儲過程中,可以采用任何模式(輸入、輸出或者同時作為輸入/輸出)。在聲明數據類型為 XML 時,不指定長度。與將 XML 數據作為 CLOB 或 VARCHAR 處理(這需要指定最大長度)相比,這是一項不錯的改進。
清單 6. 聲明參數

CREATE PROCEDURE process_cust 
( 
 IN   inpdoc  XML, 
 OUT  outdoc  XML, 
 INOUT doc3   XML 
) 
LANGUAGE SQL 
NO EXTERNAL ACTION 
BEGIN 
 ... 
END 

聲明變量

變量可以聲明為 XML 類型。與參數的情況一樣,不指定長度。
清單 7. 聲明變量

CREATE PROCEDURE ex_proc 
LANGUAGE SQL 
NO EXTERNAL ACTION 
BEGIN 
 DECLARE v_xml  XML; 
 ... 
END 

變量賦值

除了從游標獲得數據之外,可以使用三種不同的技術將來自單行查詢的值裝載進變量中:

SET 語句

SELECT INTO

VALUES INTO

這三種技術都可以使用 SQL/XML 函數,尤其是 XMLQUERY。本節中後面的示例分別演示這些技術。

下面的所有示例都使用同樣的輸入/輸出參數列表、同樣的輸入數據和同樣的 SQL/XML 函數,並產生同樣的結果。將三個參數傳遞給存儲過程。第一個參數是一個輸入 XML 文檔。第二個參數是一個輸出 VARCHAR。第三個參數是一個輸出 XML 文檔。

這些示例:

演示如何使用 XMLCAST,在將 XML 數據提取為標准關系數據類型(例如,CHAR 或 INT)時常常使用這種方法。

演示將 XMLQUERY 的輸出賦值給 XML 類型的變量。

在 XMLQUERY 函數中,“PASSING inpdoc” 引用變量。如果在 SELECT 語句中使用 XMLQUERY,那麼 PASSING 子句可以引用表列或者存儲過程變量。

在調用存儲過程的輸出中,可以看到提取 XML 元素與提取 XML 元素值之間的差異。

盡管這個示例只將值賦給輸出參數,但是使用聲明的變量也很容易。



清單 8. 三個變量賦值示例的輸入參數 “inpdoc” 的示例數據

<customer> 
 <name>Jed Clampett</name> 
 <city>Beverly Hills</city> 
</customer> 




清單 9. 三個變量賦值示例的輸出

Value of output parameters 
-------------------------- 
 
Parameter Name : P2 
Parameter Value : Jed Clampett 
 
Parameter Name : P3 
Parameter Value : <name>Jed Clampett</name> 
 
Return Status = 0 

變量賦值 / SET 語句

我們首先考慮如何將 XMLQuery 函數的輸出返回給存儲過程中聲明的變量。在這裡,p2 將輸出轉換為 VARCHAR 值,而 p3 以 XML 形式返回輸出。
清單 10. 使用 SET 語句進行變量賦值

CREATE PROCEDURE ex_proc 
( 
 IN inpdoc XML, 
 OUT p2   VARCHAR(30), 
 OUT p3   XML 
) 
LANGUAGE SQL 
NO EXTERNAL ACTION 
BEGIN 
 
 SET p2 = 
    XMLCAST 
    ( 
     XMLQUERY('$d/customer/name' PASSING inpdoc AS "d") 
     AS VARCHAR(30) 
    ); 
 
 SET p3 = XMLQUERY('$d/customer/name'PASSING inpdoc AS "d"); 
 
END 

變量賦值 / SELECT INTO

這個示例與前一個示例很相似。但是,這個版本使用完整的 SQL/XML SELECT 語句填充 p2 和 p3 變量。
清單 11. 使用 SELECT INTO 進行變量賦值

CREATE PROCEDURE ex_proc 
( 
 IN inpdoc XML, 
 OUT p2   VARCHAR(30), 
 OUT p3   XML 
) 
LANGUAGE SQL 
NO EXTERNAL ACTION 
BEGIN 
 
 SELECT XMLCAST 
     ( 
      XMLQUERY('$d/customer/name' PASSING inpdoc AS "d") 
      AS VARCHAR(30) 
     ) 
 INTO 
     p2 
 FROM 
     sysibm.sysdummy1 
 ; 
 
 SELECT XMLQUERY('$d/customer/name' PASSING inpdoc AS "d") 
 INTO 
     p3 
 FROM 
     sysibm.sysdummy1 
 ; 
 
END 

變量賦值 / VALUES INTO

還可以使用 VALUES 子句填充變量 p2 和 p3,如下例所示。
清單 12. 使用 VALUES INTO 進行變量賦值

CREATE PROCEDURE ex_proc 
( 
 IN inpdoc XML, 
 OUT p2   VARCHAR(30), 
 OUT p3   XML 
) 
LANGUAGE SQL 
NO EXTERNAL ACTION 
BEGIN 
 
 VALUES XMLCAST 
     ( 
      XMLQUERY('$d/customer/name' PASSING inpdoc AS "d") 
      AS VARCHAR(30) 
     ) 
 INTO 
     p2 
 ; 
 
 VALUES XMLQUERY 
     ( 
      '$d/customer/name' PASSING inpdoc AS "d" 
     ) 
 INTO 
     p3 
 ; 
 
END 

類型轉換錯誤

如果目標數據類型與實際數據不兼容,XMLCAST 函數會引發錯誤。例如,不能將字符串 “HX25” 轉換為整數。存儲過程可以捕獲類型轉換錯誤,如清單 13 到清單 15 所示:
清單 13. 類型轉換錯誤 —— 輸入參數 “inpdoc” 的示例數據

 <customer> 
  <custid>HX25</custid> 
 </customer> 




清單 14. 類型轉換錯誤

CREATE PROCEDURE ex_proc ( IN inpdoc XML ) 
LANGUAGE SQL 
NO EXTERNAL ACTION 
BEGIN 
 DECLARE v_int   INTEGER; 
 DECLARE v_badnum  CHAR(1)  DEFAULT 'N'; 
 
 DECLARE CONTINUE HANDLER FOR SQLSTATE '10608' 
  SET v_badnum = 'Y'; 
 
 SET v_int = 
    XMLCAST 
    ( 
     XMLQUERY('$d/customer/custid' PASSING inpdoc AS "d") 
     AS INT 
    ); 
 
 IF (v_badnum = 'Y') THEN 
  SIGNAL SQLSTATE '75002' SET MESSAGE_TEXT = 'Bad number'; 
 END IF; 
 
END 




清單 15. 類型轉換錯誤 —— 輸出

SQL0438N Application raised error with diagnostic text: "Bad number".  
SQLSTATE=75002 

IF/THEN/ELSE 語句和 XMLEXISTS

可以使用 XMLEXISTS 在 IF/THEN/ELSE 語句中測試 XML 類型的變量。
清單 16. 涉及 IF/THEN/ELSE 的代碼示例

CREATE PROCEDURE ex_proc(IN inpdoc XML) 
LANGUAGE SQL 
NO EXTERNAL ACTION 
BEGIN 
 
 IF XMLEXISTS('$d/request[@action="add"]' PASSING inpdoc AS "d") THEN 
  SIGNAL SQLSTATE '75002' SET MESSAGE_TEXT = 'F(x) add'; 
 ELSEIF XMLEXISTS('$d/request[@action="update"]' PASSING inpdoc AS "d") THEN 
  SIGNAL SQLSTATE '75002' SET MESSAGE_TEXT = 'F(x) update'; 
 ELSE 
  SIGNAL SQLSTATE '75002' SET MESSAGE_TEXT = 'F(x) invalid'; 
 END IF; 
 
END 


清單 17. IF/THEN/ELSE,輸入值(1)

<request action="add"> 
 <name>Jean Luc Picard</name> 
</request> 
 
Output: 
 SQL0438N Application raised error with diagnostic text: "F(x) add".  
 SQLSTATE=75002 




清單 18. IF/THEN/ELSE,輸入值(2)

<request action="update"> 
 <name>Jean Luc Picard</name> 
</request> 
 
Output: 
 SQL0438N Application raised error with diagnostic text: "F(x) update".  
 SQLSTATE=75002 




清單 19. IF/THEN/ELSE,輸入值(3)

<request action="delete"> 
 <name>Jean Luc Picard</name> 
</request> 
 
Output: 
 SQL0438N Application raised error with diagnostic text: "F(x) invalid".  
 SQLSTATE=75002 

PASSING 子句

XMLQUERY、XMLEXISTS 和 XMLTABLE 函數的 PASSING 子句可以傳遞多個變量。變量不必限於 XML 數據類型。下兩個示例演示兩種有意義的操作。
清單 20. 測試屬性值

CREATE PROCEDURE ex_proc 
( 
 IN inpdoc  XML, 
 IN search  VARCHAR(20), 
 OUT id    VARCHAR(30) 
) 
LANGUAGE SQL 
NO EXTERNAL ACTION 
BEGIN 
 
 SET id = 
    XMLCAST( 
     XMLQUERY('$d/customers/customer[@id=$a]/name' PASSING inpdoc AS "d", 
                                search AS "a" 
     ) 
     AS VARCHAR(30) 
    ) 
 ; 
 
END 

另一個示例演示如何使用 XMLQUERY(和其他 SQL/XML 函數)的 PASSING 子句。如果需要編寫泛型代碼以便訪問以各種方式進行格式化的 XML 數據,那麼可以根據位置(第一個、第二個、第三個等等)訪問節點和屬性。

輸入參數 “inpdoc” 的示例數據
清單 21. 根據位置提取節點和屬性 —— 輸入數據

<order id="333" status="open"> 
 <customer> 
  <custid>11029></custid> 
  <name>Johnny Depp</name> 
  <city>Hollywood</city> 
 </customer> 
</order> 



清單 22. 根據位置提取節點和屬性

CREATE PROCEDURE ex_proc 
( 
 IN inpdoc  XML, 
 IN attrpos  INT, 
 OUT attrname VARCHAR(30), 
 OUT attrvalue VARCHAR(30), 
 IN nodepos  INT, 
 OUT nodename VARCHAR(30), 
 OUT nodevalue VARCHAR(30) 
) 
LANGUAGE SQL 
NO EXTERNAL ACTION 
BEGIN 
 
 VALUES 
 ( 
  XMLCAST( 
   XMLQUERY('$d/order/@*[$a]/name()' PASSING inpdoc AS "d", attrpos AS "a") 
   AS VARCHAR(30) 
  ), 
  XMLCAST( 
   XMLQUERY('$d/order/@*[$a]' PASSING inpdoc AS "d", attrpos AS "a") 
   AS VARCHAR(30) 
  ), 
  XMLCAST( 
   XMLQUERY('$d/order/customer/*[$n]/name()' PASSING inpdoc AS "d", nodepos AS "n") 
   AS VARCHAR(30) 
  ), 
  XMLCAST( 
   XMLQUERY('$d/order/customer/*[$n]' PASSING inpdoc AS "d", nodepos AS "n") 
   AS VARCHAR(30) 
  ) 
 ) 
 INTO 
     attrname, 
     attrvalue, 
     nodename, 
     nodevalue 
 ; 
 
END 

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