Oracle中對LOB字段的操作方法

| Comments

在Oracle中插入或更新LOB字段時,可以將字符串以如下方式寫入SQL語句:

1
insert tbl_lob (fld_lob) values (utl_raw.cast_to_raw('hello world'));

這樣做的限制是:

  1. Oracle中單條SQL語句有長度限制
  2. cast_to_raw()對字符串有長度限制

因此如果要插入或更新的字符串過長,會導致執行失敗。此時應該使用dbms_lob處理LOB相關字段。下面是利用dbms_lob更新一個BLOB字段的存儲過程,對CLOB的操作同理:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
create or replace procedure updateblob(
    ctbl in varchar2,
    cfld in varchar2,
    cstr in varchar2,
    ccond in varchar2
)
is
    vqry varchar2(1000);
    vblob blob;
    vbatch varchar2(2000);
    vstrlen number;
    voffset number :=1;
    vamt number :=2000;
begin
    vstrlen := length(cstr);

    vqry := 'update '||ctbl||' set '||cfld||'=empty_blob() where '||ccond;
    execute immediate vqry;

    vqry := 'select '||cfld||' from '||ctbl||' where '||ccond||' for update';
    execute immediate vqry into vblob;

    if vstrlen>vamt then
        while vstrlen>voffset loop
            vbatch := substr(cstr, voffset, vamt);
            voffset := voffset+vamt;
            dbms_lob.writeappend(vblob, length(vbatch), utl_raw.cast_to_raw(vbatch));
        end loop;
    else
        dbms_lob.writeappend(vblob, length(cstr), utl_raw.cast_to_raw(cstr));
    end if;
    commit;
end;

示例:

1
2
3
4
5
6
7
8
9
10
-- 將tbl_lob表的fld_lob字段的值改為“hello lob !”,要求被更改的行滿足條件:
-- 1. fld_code字段的值以“2011”開頭
-- 2. fld_name字段的值等於“Hell”

declare
    vcond varchar2(1000);
begin
    vcond := 'fld_code like ''2011%'' and fld_name=''Hell''';
    updateblob('tbl_lob', 'fld_lob', 'hello lob !', vcond);
end;

Comments