oracle clob 中包含,在Oracle clob列中搜索特定字符串

使用dbms_lob.instr和dbms_lob.substr，就像常規的InStr和SubstStr函數一樣。

看簡單的例子：

SQL> create table t_clob(

2 id number,

3 cl clob

4 );

Tabela zosta│a utworzona.

SQL> insert into t_clob values (1, ' xxxx abcd xyz qwerty 354657 [] ');

1 wiersz zosta│ utworzony.

SQL> declare

2 i number;

3 begin

4 for i in 1..400 loop

5 update t_clob set cl = cl || ' xxxx abcd xyz qwerty 354657 [] ';

6 end loop;

7 update t_clob set cl = cl || ' CALCULATION=[N]NEW.PRODUCT_NO=[T9856] OLD.PRODUCT_NO=[T9852].... -- with other text ';

8 for i in 1..400 loop

9 update t_clob set cl = cl || ' xxxx abcd xyz qwerty 354657 [] ';

10 end loop;

11 end;

12/

Procedura PL/SQL zosta│a zako˝czona pomyťlnie.

SQL> commit;

Zatwierdzanie zosta│o uko˝czone.

SQL> select length(cl) from t_clob;

LENGTH(CL)

----------

25717

SQL> select dbms_lob.instr(cl, 'NEW.PRODUCT_NO=[') from t_clob;

DBMS_LOB.INSTR(CL,'NEW.PRODUCT_NO=[')

-------------------------------------

12849

SQL> select dbms_lob.substr(cl, 5,dbms_lob.instr(cl, 'NEW.PRODUCT_NO=[') + length('NEW.PRODUCT_NO=[')) new_product

2 from t_clob;

NEW_PRODUCT

--------------------------------------------------------------------------------

T9856