更新 Oracle 中的 CLOB 字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13568932/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Updating CLOB field in Oracle
提问by Shai
I have a table in Oracle database with field with data type CLOB. The name of field is XMLString. I am storing XML string which is 10,000 characters long for each record. I have more than 100, 000 of records in this table.
我在 Oracle 数据库中有一个表，其中包含数据类型为 的字段CLOB。字段的名称是XMLString。我正在存储每条记录长度为 10,000 个字符的 XML 字符串。我在这个表中有超过 100, 000 条记录。
I need to update segment of the XML string on each record at specific place. For example I need to update each record at 14th position with some string like "My New text". This replacement text is 11 characters long. So this simply means it will replace 11 characers starting from 14th character.
我需要在特定位置更新每条记录上的 XML 字符串段。例如，我需要使用诸如“我的新文本”之类的字符串更新第 14 位的每条记录。此替换文本的长度为 11 个字符。所以这只是意味着它将替换从第 14 个字符开始的 11 个字符。
I tried to use DBMS_LOB.FRAGMENT_REPLACE, but it is not exactly what I wanted.
我尝试使用DBMS_LOB.FRAGMENT_REPLACE，但这不是我想要的。
Is there any simple command like
有没有像这样的简单命令
Replace(XMLString, 14, ‘My New text')
so that I can do something like below?
这样我就可以做类似下面的事情？
UPDATE MYTABLE
SET MyClobField = Replace(MyClobField, 14, 'My New text')
WHERE MyTableID>5000
Any help would be appreciated.
任何帮助，将不胜感激。
回答by DazzaL
SQL using
SQL 使用
UPDATE MYTABLE
SET MyClobField = substr(MyClobField, 1, 10) || to_clob('MyNewtext')||substr(MyClobField, 10+length('MyNewtext')+1)
where..
just change the 2 occurances of "10" to the offset.
只需将“10”的 2 次更改为偏移量。
or in PL/SQL like this using the DBMS_LOB.WRITE API (this is faster than the above)
或者在像这样的 PL/SQL 中使用 DBMS_LOB.WRITE API（这比上面的要快）
SQL> create table foo(c clob);
Table created.
SQL> insert into foo values ( 'this is a test string ' || rpad('x', 20, 'x'));
1 row created.
SQL> commit;
Commit complete.
SQL> select * from foo;
C
--------------------------------------------------------------------------------
this is a test string xxxxxxxxxxxxxxxxxxxx
SQL> declare
2 v_lob clob;
3 begin
4
5 for r_lob in (select c
6 from foo
7 for update)
8 loop
9 dbms_lob.write(r_lob.c, 6, 16, 'phrase'); -- ie write at offset 16, 6 bytes
10 end loop;
11 end;
12 /
PL/SQL procedure successfully completed.
SQL> select * from foo;
C
--------------------------------------------------------------------------------
this is a test phrase xxxxxxxxxxxxxxxxxxxx
回答by Opster Elasticsearch Pro-Vijay
Try this :-
尝试这个 ：-
update table set column = replace(column,'hello','abcded')
更新表集列 = 替换（列，'你好'，'abcded'）
