如果想用文本,就用clob把,blob只能是二进制的数据(数据库中是十六进制) 这里处理CLOB字段的动态PL/SQL 2001-03动态PL/SQL,对CLOB字段操作可传递表名table_name,表的唯一标志字段名field_id,clob字段名field_name,记录号v_id,开始处理字符的位置v_pos,传入的字符串变量v_clob修改CLOB的PL/SQL过程:updateclobcreate or replace procedure updateclob( table_name in varchar2, field_id in varchar2, field_name in varchar2, v_id in number, v_pos in number, v_clob in varchar2) is lobloc clob; c_clob varchar2(32767); amt binary_integer; pos binary_integer; query_str varchar2(1000); begin pos:=v_pos*32766+1; amt := length(v_clob); c_clob:=v_clob; query_str :='select '||field_name||' from '||table_name||' where '||field_id||'= :id for update '; --initialize buffer with data to be inserted or updated EXECUTE IMMEDIATE query_str INTO lobloc USING v_id; --from pos position, write 32766 varchar2 into lobloc dbms_lob.write(lobloc, amt, pos, c_clob); commit; exception when others then rollback; end; / 用法说明: 在插入或修改以前,先把其它字段插入或修改,CLOB字段设置为空empty_clob(), 然后调用以上的过程插入大于2048到32766个字符。 如果需要插入大于32767个字符,编一个循环即可解决问题。查询CLOB的PL/SQL函数:getclob create or replace function getclob( table_name in varchar2, field_id in varchar2, field_name in varchar2, v_id in number, v_pos in number) return varchar2 is lobloc clob; buffer varchar2(32767); amount number := 2000; offset number := 1; query_str varchar2(1000); begin query_str :='select '||field_name||' from '||table_name||' where '||field_id||'= :id '; --initialize buffer with data to be found EXECUTE IMMEDIATE query_str INTO lobloc USING v_id; offset:=offset+(v_pos-1)*2000; --read 2000 varchar2 from the buffer dbms_lob.read(lobloc,amount,offset,buffer); return buffer; exception when no_data_found then return buffer; end; /用法说明:用select getclob(table_name,field_id,field_name,v_id,v_pos) as partstr from dual; 可以从CLOB字段中取2000个字符到partstr中, 编一个循环可以把partstr组合成dbms_lob.getlength(field_name)长度的目标字符串。
这里处理CLOB字段的动态PL/SQL 2001-03动态PL/SQL,对CLOB字段操作可传递表名table_name,表的唯一标志字段名field_id,clob字段名field_name,记录号v_id,开始处理字符的位置v_pos,传入的字符串变量v_clob修改CLOB的PL/SQL过程:updateclobcreate or replace procedure updateclob(
table_name in varchar2,
field_id in varchar2,
field_name in varchar2,
v_id in number,
v_pos in number,
v_clob in varchar2)
is
lobloc clob;
c_clob varchar2(32767);
amt binary_integer;
pos binary_integer;
query_str varchar2(1000);
begin
pos:=v_pos*32766+1;
amt := length(v_clob);
c_clob:=v_clob;
query_str :='select '||field_name||' from '||table_name||' where '||field_id||'= :id for update ';
--initialize buffer with data to be inserted or updated
EXECUTE IMMEDIATE query_str INTO lobloc USING v_id;
--from pos position, write 32766 varchar2 into lobloc
dbms_lob.write(lobloc, amt, pos, c_clob);
commit;
exception
when others then
rollback;
end;
/
用法说明:
在插入或修改以前,先把其它字段插入或修改,CLOB字段设置为空empty_clob(),
然后调用以上的过程插入大于2048到32766个字符。
如果需要插入大于32767个字符,编一个循环即可解决问题。查询CLOB的PL/SQL函数:getclob
create or replace function getclob(
table_name in varchar2,
field_id in varchar2,
field_name in varchar2,
v_id in number,
v_pos in number) return varchar2
is
lobloc clob;
buffer varchar2(32767);
amount number := 2000;
offset number := 1;
query_str varchar2(1000);
begin
query_str :='select '||field_name||' from '||table_name||' where '||field_id||'= :id ';
--initialize buffer with data to be found
EXECUTE IMMEDIATE query_str INTO lobloc USING v_id;
offset:=offset+(v_pos-1)*2000;
--read 2000 varchar2 from the buffer
dbms_lob.read(lobloc,amount,offset,buffer);
return buffer;
exception
when no_data_found then
return buffer;
end;
/用法说明:用select getclob(table_name,field_id,field_name,v_id,v_pos) as partstr from dual;
可以从CLOB字段中取2000个字符到partstr中,
编一个循环可以把partstr组合成dbms_lob.getlength(field_name)长度的目标字符串。