db2 调用存储过程sql_在DB2 SQL存储过程中使用XML

最新推荐文章于 2024-10-08 14:09:43 发布

cuyi7076

最新推荐文章于 2024-10-08 14:09:43 发布

阅读量1.1k

点赞数

文章标签：数据库编程语言 python mysql java

原文链接：https://www.ibm.com/developerworks/data/library/techarticle/dm-0701oliva/index.html

版权

本文详细介绍了如何在IBM DB2 9的SQL存储过程中使用XML，包括XML数据类型、存储过程中的XML功能、代码示例和解决方法。文章通过多个代码清单展示了XML查询、变量赋值、IF/THEN/ELSE语句和XMLEXISTS的使用，帮助读者理解在存储过程中如何处理XML数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

db2 调用存储过程sql

用于Linux，UNIX®和Windows®的IBM®DB2®9在先前的DB2发行版之上添加了主要的新XML功能。在DB2 9之前，您可以将XML文档存储为LOB，也可以将传入的XML数据切入关系表中，该过程需要一个名为XML Extender的附加工具。但是，DB2 9使用户能够以其本机分层格式存储和查询XML数据-一种称为pureXML™支持的技术。现在，您可以在XML数据上建立索引，并使用对存储过程的调用来执行粉碎。

本文讨论了XML在SQL存储过程中的使用。我提供了许多代码示例来帮助说明特定的技术要点。提供的示例旨在帮助您理解，因此尽可能地简化。为了实现此目标，请注意，这些示例有些人为设计。

注意：本文假定您是应用程序开发人员或DBA，他们已经熟悉编写SQL存储过程。

XML简介

XML代表“可扩展标记语言”，这是一种以非常类似于HTML的语法表示信息的分层方法。 XML文档是由“元素”或分支组成的树形结构。每个元素都有一个名称，可以包含一个文本值，可以具有一个属性列表（格式为“ name = value”），并且可以具有子级。允许重复的元素名称，尽管属性名称在元素内必须唯一。

XQuery是基于W3C标准的XML查询语言。它通常在任何关系数据库之外用于查询XML数据，该数据可以存储在文本文件中。 XQuery允许您访问XML数据（包括联接），循环，声明的变量，IF / THEN / ELSE语句和其他构造。

在DB2 9中有四种使用XML数据的不同方法：

普通SQL
- 普通SQL由常规SQL语句和函数组成，这些语句和函数没有对XPath或XQuery的引用。功能仅限于将XML文档作为完整实体使用。可以将它们插入数据库或从数据库中检索它们，以及将其转换为文本或不转换为文本。但是，无法解析出XML文档的一部分，也无法应用任何谓词。
带有嵌入式XQuerySQL / XML
- SQL / XML包括三个新SQL函数，这些函数可处理XML数据并将嵌入式XQuery命令作为参数-XMLQUERY，XMLEXISTS和XMLTABLE。
  - XMLQUERY用于从XML数据提取。结合功能XMLCAST，关系数据可以从XML派生。
  - XMLEXISTS将谓词应用于XML数据，通常在WHERE子句中使用。
  - XMLTABLE以关系表的形式提取XML数据。
XQuery
- 现在可以在DB2数据库引擎中本地理解XQuery。提供了两个函数来访问DB2数据。第一个db2-fn：xmlcolumn将XML列的值返回到XQuery。包括所有行；没有谓词。
带有嵌入式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语句
- 取得INTO陈述式
- 执行INTO语句
- SET语句

局限性和解决方法

SQL存储过程中的XML功能有一些限制：

XQuery查询只能作为动态游标的一部分运行。
XML变量在COMMIT或ROLLBACK之后不可用。

动态游标中的XQuery

XQuery命令可以出现在两个不同的上下文中：

在单引号中作为SQL / XML函数XMLQUERY，XMLEXISTS和XMLTABLE的参数
静态SQL语句中允许在SQL / XML函数中嵌入XQuery。在下面的示例中，变量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变量

在COMMIT或ROLLBACK之后，参数和声明的XML变量均不可用。尝试使用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语句
选择进入
重视价值

所有这三种技术都可以使用SQL / XML函数，特别是XMLQUERY。本节中的后续示例说明了所提到的每种技术。

以下所有示例均使用相同的输入/输出参数列表，相同的输入数据，相同SQL / XML函数，并产生相同的结果。三个参数传递给存储过程。参数1是输入XML文档。参数2是输出VARCHAR。参数3是输出XML文档。

这些例子：

演示XMLCAST的使用，当您将XML数据提取到标准关系数据类型（例如CHAR，INT）的变量中时，该方法经常使用
显示可以将XMLQUERY的输出分配给XML类型的变量
在函数XMLQUERY中，子句“通过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

变量分配/选择进入

此示例与我们之前的示例相似。但是，此版本使用完整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子句填充变量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

通过子句

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

清单23.提取位置节点和属性-输出



Output

Value of output parameters
--------------------------
Parameter Name  : ATTRNAME
Parameter Value : status

Parameter Name  : ATTRVALUE
Parameter Value : open

Parameter Name  : NODENAME
Parameter Value : city

Parameter Name  : NODEVALUE
Parameter Value : Hollywood

Return Status = 0

重组XML文档

本示例演示了一种重组XML文档的方法。原始文档作为输入参数传递，而新文档作为输出参数传递。

清单24.输入和所需的输出

<customer>                       <customer @id="xxx">
  <id> ... </id>                   <name>
  <name>                             <first> ... </first>
    <first> ... </first>             <last> ... </last>
    <last> ... </last>             </name>
  </name>                          <city> ... </city>
  <city> ... </city>             </customer>
</customer>

在清单24中，一个节点被转换为一个属性。这是很小的变化，但是您可以将相同的技术应用于更复杂的情况。基本思想是将输入文档切成单独的组件。如果某个组件不会发生变化，例如本例中的<name>元素，则将其保留为XML类型，并且不做任何进一步的分析。如果重组涉及在属性和节点之间移动事物，则始终需要将数据转换为关系数据类型。

清单25.重组XML文档



CREATE PROCEDURE ex_proc
(
  IN  inpdoc  XML,
  OUT outdoc  XML
)
LANGUAGE SQL
NO EXTERNAL ACTION
BEGIN
  DECLARE v_id    INT;
  DECLARE v_name  XML;
  DECLARE v_city  XML;

  VALUES
  (
    XMLCAST
    (
      XMLQUERY('$d/customer/id' PASSING inpdoc AS "d")
      AS INT
    ),
    XMLQUERY('$d/customer/name' PASSING inpdoc AS "d"),
    XMLQUERY('$d/customer/city' PASSING inpdoc AS "d")
  )
  INTO
       v_id,
       v_name,
       v_city
  ;

  SET outdoc =
        XMLQUERY
        (
          '<customer id="{$id}">
             {$name}
             {$city}
           </customer>
          '
          PASSING v_id AS "id",
                  v_name AS "name",
                  v_city AS "city"
        )
  ;

END

清单26.输入和输出值

Sample data for input parm "inpdoc"

<customer>
  <id>3005302</id>
  <name>
    <first>Miguel</first>
    <last>Rodriguez</last>
  </name>
  <city>Wichita</city>
</customer>

Output:

<customer id="3005302">
  <name>
    <first>Miguel</first>
    <last>Rodriguez</last>
  </name>
  <city>Wichita</city>
</customer>

摘要

在本文中，我们探讨了如何在SQL存储过程中利用DB2 9的新XML功能。此版本中有一个新的XML数据类型，您可以通过SQL / XML函数和XQuery进行访问。本文中的示例说明了如何使用XML变量声明，分配和测试值。您已经了解了如何生成测试数据和重组现有XML文档，并希望这证明与您的实际场景有关。我的希望是，本文为SQL开发人员提供了将XML集成到应用程序中所需的信息。

专业术语

CURSOR ：一种编程技术，允许应用程序逐行检索查询结果。通常，有一个循环继续读取每一行，直到到达数据末尾为止。

动态SQL ：所有查询都分为动态查询或静态查询。 “动态”是指查询以字符串形式发送到数据库引擎中；必须对其进行分析并生成查询执行计划。从命令行处理器（CLP）运行的所有内容始终是动态的。

PARAMETER MARKER ：应用程序可能希望多次调用相同的查询，每次仅更改特定的值（例如，发送到存储过程的参数或WHERE子句中的值）。参数标记是一种解决方法。查询使用“？” 任何不想硬编码值的地方。查询已准备就绪，这将锁定执行计划。每次执行查询时，都会提供变量，这些变量将映射到“？”中。查询的一部分。

STATIC SQL ：所有查询都分类为动态或静态。 “静态”仅存在于应用程序（例如SQL存储过程）中，并且存在于应用程序（例如JavaSQLJ）中的嵌入式SQL。静态查询已经被解析，并生成了查询计划。该计划作为“包”的一部分存储在数据库中。执行静态查询时，将使用先前计算的查询计划。

UID ：UPDATE，INSERT或DELETE类型SQL语句的首字母缩写。由于所有这三个语句都修改了数据，因此使用缩写词来引用它们都是很有用的。

VALUES INTO ：能够只对一行运行查询非常有用。在DB2中，可以从表SYSIBM.SYSDUMMY1中进行选择，该表恰好包含1行。您也可以使用语法“ VALUES”。对于不从任何表或视图读取的查询而言，它很有用。 VALUES INTO将值提取到变量中。

XML属性 ：在此XML示例-<cust id =“ 725”> </ cust>中，存在一个名为“ id”的XML属性，其值为“ 725”。属性属于节点-在这种情况下，称为“ cust”的节点。与节点不同，属性必须具有唯一的名称。

XML NODE ：XML节点是这种形式的两个标签之间的所有内容：<cust> blah blah </ cust>。一个节点可以包含文本，属性，其他节点（因此，XML的层次结构）以及其他一些特殊的东西（例如处理指令和注释）。

XMLCAST ：一个新SQL函数，它将XML节点的值转换为关系数据类型，例如VARCHAR，INTEGER，DATE等。

XMLEXISTS ：一个新SQL函数，用于测试XML文档的某些条件。条件用XQuery的语法表示。它们可以包括诸如特定节点的存在，具有特定值的节点，特定属性的存在，具有特定值的属性等内容。

XMLQUERY ：一种新SQL函数，可从XML文档中提取内容。那个“东西”可以是一个节点，一系列节点或XQuery函数，例如count（）。

XMLTABLE ：一种新SQL函数，旨在将XML文档的某些部分视为关系表。

XQUERY ：用于查询XML文档的本机语言。 XQuery的最简单部分称为“ XPath”。它看起来像一个完全限定的UNIX文件名（例如/ abc / xxx / 305xk），带有表示谓词的语法。