LOB的更新可以通过DML语句实现,也可以通过存储过程去实现。

我这里是通过一个简单的PL/SQL过程实现的

[oracle@rac1 ~]$ export ORACLE_SID=rac1
[oracle@rac1 ~]$ sqlplus mayp/mayp

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Aug 2 05:24:48 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL>

SQL> alter table lob_test add constraint pk_lob_test primary key (NUM);

Table altered.

SQL> desc lob_test
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(100)
IMAGE BLOB
NOTE CLOB
NUM NOT NULL NUMBER(38)

SQL> select num from lob_test;

NUM
----------
1
2
3
4

SQL>

模拟更新num=1所在的行的image字段

SQL> DECLARE
p_lobloc BLOB;
p_sql varchar2(10000);
p_file BFILE;
P_amt INTEGER;
no_lob_data EXCEPTION;
BEGIN
p_sql := 'SELECT image FROM lob_test WHERE num = :num FOR UPDATE';
EXECUTE IMMEDIATE p_sql
INTO p_lobloc
USING 1;
P_amt := DBMS_LOB.GETLENGTH(p_lobloc);
DBMS_LOB.ERASE(p_lobloc, P_amt);
p_file := BFILENAME('MYDUMP', 'Water.jpg');
DBMS_LOB.FILEOPEN(p_file);
DBMS_LOB.LOADFROMFILE(p_lobloc, p_file, DBMS_LOB.GETLENGTH(p_file));
COMMIT;
DBMS_LOB.FILECLOSE(p_file);
EXCEPTION
WHEN no_lob_data THEN
RAISE_APPLICATION_ERROR('-200001', 'NO BLOB DATA');
END;
/

PL/SQL procedure successfully completed.

更新前的数据

更新后的数据

在应用中可能通过存储过程传入更新的数据,可以直接通过DBMS_LOB.WRITE方法来更新字段。
具体可以参考包 DBMS_LOB。