不知道你text的具体格式是什么。先给你一些修改lob字段的dbms_lob包中的函数和用途。
PL/SQL Functions/Procedures To Modify BLOB, CLOB, and NCLOB Values
Table 3-3 PL/SQL: DBMS_LOB Procedures To Modify BLOB, CLOB, and NCLOB Values
Function/Procedure Description
APPEND()
Appends the LOB value to another LOB
COPY()
Copies all or part of a LOB to another LOB
ERASE()
Erases part of a LOB, starting at a specified offset
LOADFROMFILE()
Load BFILE data into an internal LOB
TRIM()
Trims the LOB value to the specified shorter length
WRITE()
Writes data to the LOB at a specified offset
WRITEAPPEND()
Writes data to the end of the LOB
PL/SQL Functions/Procedures To Modify BLOB, CLOB, and NCLOB Values
Table 3-3 PL/SQL: DBMS_LOB Procedures To Modify BLOB, CLOB, and NCLOB Values
Function/Procedure Description
APPEND()
Appends the LOB value to another LOB
COPY()
Copies all or part of a LOB to another LOB
ERASE()
Erases part of a LOB, starting at a specified offset
LOADFROMFILE()
Load BFILE data into an internal LOB
TRIM()
Trims the LOB value to the specified shorter length
WRITE()
Writes data to the LOB at a specified offset
WRITEAPPEND()
Writes data to the end of the LOB
text
-------------
abc2001-10-10
abc2001-10-10fasf
2001-10-10fasre
afa2001-10-10dfa2001-10-10还有除了2001-10-10是不是还有2005-01-10等等其它日期也要改?不同的情况程序都不一样的。总之,基本上就是用到上面列出的几种函数。楼主可以根据自己的情况考虑如何使用。实在困难再问吧
declare
source_lob CLOB;
v_pos BINARY_INTEGER;
buffer CHAR(10);
BEGIN
select text into source_lob FROM employee WHERE ID = ....;
v_pos := DBMS_LOB.INSTR(source_lob, '2001-10-10', 1,1);
DBMS_LOB.WRITE(source_lob, 10, v_pos, buffer);
END;
SQL> create table liq (c clob);Table created.SQL> insert into liq values ('2001-10-10');1 row created.SQL> insert into liq values ('2001-10-10');1 row created.SQL> insert into liq values ('2001-10-10');1 row created.SQL> insert into liq values ('2001-10-10');1 row created.SQL> /1 row created.SQL> commit;Commit complete.SQL> select replace(c,'2001-10-10','2001.10.10') from liq;REPLACE(C,'2001-10-10','2001.10.10')
-------------------------------------------------------------------------2001.10.10
2001.10.10
2001.10.10
2001.10.10
2001.10.10SQL> update liq set c=replace(c,'2001-10-10','2001.10.10') ;5 rows updated.SQL> commit;Commit complete.SQL> select c from liq;C
-------------------------------------------------------------------------2001.10.10
2001.10.10
2001.10.10
2001.10.10
2001.10.10SQL>
source_lob CLOB;
v_pos BINARY_INTEGER;
buffer CHAR(10);
BEGIN
buffer := '2001.10.10';
select CLOB_LOCATOR into source_lob FROM MYLOBS WHERE LOB_INDEX = 1 for update;
v_pos := DBMS_LOB.INSTR(source_lob, '2001-10-10', 1,1);
DBMS_LOB.WRITE(source_lob, 10, v_pos, buffer);
END;
------------------------------------------------------------------------------2001.10.10 ddddddddd
2001.10.10 ddddddddd
2001.10.10 ddddddddd
2001.10.10 dddddddddSQL>
source_lob CLOB;
v_pos BINARY_INTEGER;
buffers CHAR(4);
BEGIN
select text into source_lob from employee where id='20050119074938_106';
v_pos := DBMS_LOB.INSTR(source_lob,'.173',1,1);
DBMS_LOB.WRITE(source_lob,4,v_pos,buffers);
END;怎么会出现以下错误?declare
*
ERROR 位于第 1 行:
ORA-06502: PL/SQL: 数字或值错误
ORA-06512: 在"SYS.DBMS_LOB", line 708
ORA-06512: 在line 8
declare
source_lob CLOB;
v_pos BINARY_INTEGER;
buffer CHAR(6);
CURSOR mylob IS select text FROM employee;
BEGIN
buffer := '2001.10.10';
FOR mycur IN mylob LOOP
select text into source_lob FROM employee WHERE id = mycur.id for update;
v_pos := DBMS_LOB.INSTR(source_lob, '2001-10-10', 1,1);
IF v_pos > 0 Then
DBMS_LOB.WRITE(source_lob, 10, v_pos, buffer);
END IF;
END LOOP;END;
/
COMMIT;
改成
v_pos integer;
v_pos 的值为NULL或为0,buffers没有指定值,应指定成替换后的值。
怎样在sql中象函数一样去调用这个过程呀? 你可以将13楼的语句写成个存储过程。
IS
source_lob CLOB;
v_pos BINARY_INTEGER;
CURSOR mylob IS select text FROM employee;
BEGIN
FOR mycur IN mylob LOOP
select text into source_lob FROM employee WHERE id = mycur.id for update;
v_pos := DBMS_LOB.INSTR(source_lob, source_txt, 1,1);
IF v_pos > 0 Then
DBMS_LOB.WRITE(source_lob, 10, v_pos, desc_txt);
END IF;
END LOOP;
COMMIT;
END;
/
CREATE OR REPLACE PROCEDURE P_REPL_CLOB(source_txt char, desc_txt char)
IS
source_lob CLOB;
v_pos BINARY_INTEGER;
str_len integer;
CURSOR mylob IS select text FROM employee;
BEGIN
str_len := LENGTH(desc_txt);
FOR mycur IN mylob LOOP
select text into source_lob FROM employee WHERE id = mycur.id for update;
v_pos := DBMS_LOB.INSTR(source_lob, source_txt, 1,1);
IF v_pos > 0 Then
DBMS_LOB.WRITE(source_lob, str_len, v_pos, desc_txt);
END IF;
END LOOP;
COMMIT;
END;
...
END LOOP;
没错,Oracle有这种语法,以上过程在我这里试了,没问题,把你的报错信息贴出来。
CURSOR mylob IS select text FROM employee;
应改为
CURSOR mylob IS select ID FROM employee;