Oracle更新Clob数据类型列中的数据

Question

我对Oracle和Clob数据类型非常陌生。

我们有一个名为ABC的表，它有一列CLOB数据类型。

The Clob Column"；MyClob"；具有以下文本数据：

{ "UD_1":"John", "UD_2":"Doe", "UD_3":"1011=John Doe"}

当我使用以下查询查询表"ABC"时，

Select Id, 
JSON_VALUE(MyClob, '$.UD_1') FirstName
JSON_VALUE(MyClob, '$.UD_2') LastName
JSON_VALUE(MyClob, '$.UD_3') Status
From Abc

我得到下表：

Id    FirstName  LastName    Status
1     John       Doe         1011=John Doe
2     Jack       Ham         2022=Jack Ham

我只想更新其中一行的"UD_3"值。我不知道如何编写更新查询来更新Clob列中的文本。

Answer 1

您可以使用JSON_MERGEPATCH对JSON执行更新。要使用JSON_MERGEPATCH，在第二个参数中，您需要传递一个要更新的键/值对的JSON对象。请参阅下面的示例。

需要注意的另一点是，JSON_MERGEPATCH的返回值将从JSON中删除任何不必要的空格。

SQL> CREATE TABLE sampletable
2  (
3      id,
4      myclob
5  )
6  AS
7      SELECT 1,
8                EMPTY_CLOB ()
9             || '{ "UD_1":"John", "UD_2":"Doe", "UD_3":"1011=John Doe"}'
10        FROM DUAL
11      UNION ALL
12      SELECT 2,
13                EMPTY_CLOB ()
14             || '{ "UD_1":"Jack", "UD_2":"Ham", "UD_3":"2022=Jack Ham"}'
15*       FROM DUAL;
Table SAMPLETABLE created.
SQL> SELECT *
2    FROM sampletable  t
3         CROSS JOIN
4         JSON_TABLE (
5             t.myclob,
6             '$'
7             COLUMNS (firstname VARCHAR2 PATH '$.UD_1',
8                      lastname VARCHAR2 PATH '$.UD_2',
9*                     status VARCHAR2 PATH '$.UD_3'));
ID MYCLOB                                                    FIRSTNAME    LASTNAME    STATUS
_____ _________________________________________________________ ____________ ___________ ________________
1 { "UD_1":"John", "UD_2":"Doe", "UD_3":"1011=John Doe"}    John         Doe         1011=John Doe
2 { "UD_1":"Jack", "UD_2":"Ham", "UD_3":"2022=Jack Ham"}    Jack         Ham         2022=Jack Ham
SQL> UPDATE sampletable
2     SET myclob = json_mergepatch (myclob, '{"UD_3":"3033-Devp"}')
3*  WHERE id = 1;
1 row updated.
SQL> SELECT *
2    FROM sampletable  t
3         CROSS JOIN
4         JSON_TABLE (
5             t.myclob,
6             '$'
7             COLUMNS (firstname VARCHAR2 PATH '$.UD_1',
8                      lastname VARCHAR2 PATH '$.UD_2',
9*                     status VARCHAR2 PATH '$.UD_3'));
ID MYCLOB                                                    FIRSTNAME    LASTNAME    STATUS
_____ _________________________________________________________ ____________ ___________ ________________
1 {"UD_1":"John","UD_2":"Doe","UD_3":"3033-Devp"}           John         Doe         3033-Devp
2 { "UD_1":"Jack", "UD_2":"Ham", "UD_3":"2022=Jack Ham"}    Jack         Ham         2022=Jack Ham