时间戳

首页 > 解决方案 > 替换一行clob

sql - 替换一行clob

问题描述

我在 Oracle SQL 中有 clob 变量，其中包含类似的内容。

<catalog>
   <book id="bk101">
      <author>Joe</author>
       <genre>Fantasy</genre>
      <title>XML Developer  Guide</title>
      <price>44.95</price>
   </book>
   <book id="bk102">
      <author>Gregor</author>
      <genre>Fantasy</genre>
      <title>Midnight Rain</title>
      <price>5.95</price>
   </book>
</catalog>

我想把 Fantasy 改成别的东西，只有当作者的名字是 Gregor。我认为 REGEXP_REPLACE 函数对此有好处，但不知道在这种情况下如何正确使用它你能帮我吗？

标签： sqlregexplsqlreplace

解决方案

完成所有这些工作后，我发现了一个堆栈溢出页面，其中有一个更简洁的答案。查看Oracle-XMLTYPE：如何更新值。

我包括两种方法，一种使用直接 SQL 的蛮力替换方法，然后将 CLOB 从 XML 转换为行/列，进行替换，然后将其转换回 XML。

蛮力 SQL

DECLARE
-- This probably isn't what you want as it converts all "Fantasy" to "Something Else"
-- if author Gregory appears anywhere in the XML
-- Define clob for test
l_clob                   CLOB;
-- Define text including string to replace
c_text          CONSTANT VARCHAR2( 512 ) := '<catalog>
<book id="bk101">
  <author>Joe</author>
   <genre>Fantasy</genre>
  <title>XML Developer  Guide</title>
  <price>44.95</price>
</book>
<book id="bk102">
  <author>Gregor</author>
  <genre>Fantasy</genre>
  <title>Midnight Rain</title>
  <price>5.95</price>
</book>
</catalog>';
-- Define text to replace
c_replace       CONSTANT VARCHAR2( 100 ) := '<genre>Fantasy</genre>';
--Define text to replace string
c_replacewith   CONSTANT VARCHAR2( 100 ) := '<genre>Something Else</genre>';
-- Define search string
c_search        CONSTANT VARCHAR2( 100 ) := '<author>Gregor</author>';
BEGIN
    -- Initialize l_clob
    DBMS_LOB.createtemporary( l_clob, TRUE );
    l_clob   := c_text;

    -- replace the text
    -- replace function works on strings and clobs
    IF DBMS_LOB.INSTR( l_clob, c_search ) > 0
    THEN
        l_clob   :=
            REPLACE( l_clob
                   , c_replace
                   , c_replacewith );
    END IF;

    -- Write the replace string out.
    -- put_line only outputs first 20,000 characters
    DBMS_OUTPUT.put_line( l_clob );
    DBMS_LOB.freetemporary( l_clob );
END;

XML 到表到 XML

-- Create table with clob
CREATE TABLE deleteme_table
(
    xml_data    CLOB
);

-- Insert XML into table
INSERT INTO deleteme_table
     VALUES ( '<catalog>
   <book id="bk101">
      <author>Joe</author>
       <genre>Fantasy</genre>
      <title>XML Developer  Guide</title>
      <price>44.95</price>
   </book>
   <book id="bk102">
      <author>Gregor</author>
      <genre>Fantasy</genre>
      <title>Midnight Rain</title>
      <price>5.95</price>
   </book>
</catalog>' );


-- Do the conversion
WITH
    aset AS
        (-- convert xml value to row/column
         SELECT xt.*
           FROM deleteme_table  x
              , XMLTABLE( '/catalog/book'
                          PASSING xmltype( x.xml_data )
                          COLUMNS id VARCHAR2( 10 ) PATH '@id'
                                , author VARCHAR2( 30 ) PATH 'author'
                                , genre VARCHAR2( 30 ) PATH 'genre'
                                , title VARCHAR2( 30 ) PATH 'title'
                                , price NUMBER PATH 'price' ) xt),
    bset AS
        ( -- replace the value
         SELECT id
              , author
              , CASE WHEN author = 'Gregor' THEN 'Something Else' ELSE genre END     AS genre
              , title
              , price
           FROM aset)
-- convert results back to XML
SELECT XMLELEMENT( "catalog"
                 , XMLELEMENT( "book"
                             , xmlattributes( bset.id AS "id" )
                             , XMLELEMENT( "author", bset.author )
                             , XMLELEMENT( "genre", bset.genre )
                             , XMLELEMENT( "title", bset.title )
                             , XMLELEMENT( "price", bset.price ) ) )
  FROM bset;

推荐阅读