??
字號:
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>處理CLOB字段的動態PL/SQL</title>
</head>
<body background="images/wsand.gif">
<p align="center"><strong><font color="#FF0000">處理CLOB字段的動態PL/SQL</font></strong></p>
<pre>
2001-03 余楓
</pre>
<p> 動態PL/SQL,對CLOB字段操作可傳遞表名<font face="Times New Roman">table_name</font>，表的唯一標志字段名<font
face="Times New Roman">field_id</font>，clob字段名<font face="Times New Roman">field_name</font>，記錄號<font
face="Times New Roman">v_id</font>，開始處理字符的位置<font
face="Times New Roman">v_pos</font>，傳入的字符串變量<font face="Times New Roman">v_clob</font></p>
<p>修改<font face="Times New Roman">CLOB</font>的<font face="Times New Roman">PL/SQL</font>過程：<font
face="Times New Roman">updateclob</font></p>
<pre>create 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;
/
</pre>
<p>用法說明：<br>
在插入或修改以前，先把其它字段插入或修改，CLOB字段設置為空<font
face="Times New Roman">empty_clob()</font>，<br>
然后調用以上的過程插入大于2048到32766個字符。<br>
如果需要插入大于32767個字符，編一個循環即可解決問題。</p>
<p>查詢<font face="Times New Roman">CLOB</font>的<font face="Times New Roman">PL/SQL</font>函數：<font
face="Times New Roman">getclob</font><br>
</p>
<pre>
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;
/
</pre>
<p>用法說明：</p>
<p>用select getclob(table_name,field_id,field_name,v_id,v_pos) as partstr from dual;<br>
可以從CLOB字段中取2000個字符到partstr中，<br>
編一個循環可以把partstr組合成dbms_lob.getlength(field_name)長度的目標字符串。</p>
<pre>
調用PL/SQL過程的方法:
SQL*PLUS SQL> EXEC 過程名[(參數)];
Procedure Builder PL/SQL>過程名[(參數)];
JAVA CALL { 過程名[(參數)] };
PHP BEGIN { 過程名[(參數)] } END;
</pre>
</body>
</html>
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -