LOB数据管理--更新LOB列

最新推荐文章于 2021-04-04 03:58:25 发布

conghan9137

最新推荐文章于 2021-04-04 03:58:25 发布

阅读量128

点赞数

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.

[@more@]

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.