處理Clob資料關於oracle中大對象處理的一些方法和執行個體

來自CSDN
=======================================
在oracle中，有4個大對象（lobs）類型可用，分別是blob,clob,bfile,nclob。  
下面是對lob資料類型的簡單介紹。  
l blob:二進位lob，為位元據，最長可達4GB，存貯在資料庫中。  
l clob:字元lob,字元資料,最長可以達到4GB,存貯在資料庫中。  
l bfile:二進位檔案;存貯在資料庫之外的唯讀型位元據，最大長度由作業系統限制。  
l nclob：支援對位元組字元集合(nultibyte characterset)的一個clob列。  
對於如何檢索和操作這些lob資料一直是oracle資料庫開發人員經常碰到的問題。下面我將在oracle對lob資料處理的一些方法和技巧，介紹給讀者，希望能夠對讀者以後的開發有所協助。  
oracle中可以用多種方法來檢索或操作lob資料。通常的處理方法是通過dbms_lob包。  
其他的方法包括使用api(application programming interfaces)應用程式介面和oci（oracle call interface）oracle調用介面程式。  
一、在oracle開發環境中我們可以用dbms_lob包來處理！dbms_lob包功能強大，簡單應用。既可以用來讀取內部的lob對象，也可以用來處理bfile對象。但處理兩者之間，還有一點差別。處理內部lob對象（blob,clob）時，可以進行讀和寫，但處理外部lob對象bfile時，只能進行讀操作，寫的操作可以用pl/sql處理。另外用sql也可以處理lob，但要注意sql僅可以處理整個lob，不能操作lob的資料片。  
在dbms_lob包中內建了read(),append,write()，erase()，copy(),getlength()，substr()等函數，可以很方便地操作lob對象。這裡不做深入討論，讀者可以參看相關的書籍。  
對於pl/sql，下面介紹一種技巧，用動態pl/sql語句處理clob對象來傳替表名！  
example 1.  
動態PL/SQL,對CLOB欄位操作可傳遞表名table_name，表的唯一標誌欄位名field_id，clob欄位名field_name記錄號v_id，開始處理字元的位置v_pos，傳入的字串變數v_clob   
修改CLOB的PL/SQL過程：updateclob   
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;  
l /用法說明：   
在插入或修改以前，先把其它欄位插入或修改，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;  
l 用法說明：   
用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)長度的目標字串。   
二、對於在其他不同的開發環境，例如vc,vb,pb,java等環境下對lob的處理，處理方法不盡相同，在這裡將簡要舉幾個例子來說明不在oracle開發環境下對lob的處理。  
（一） 在pb中的處理  
exampler 2.  
string ls_path,ls_filename,ls_jhdh   
long ll_num,ll_count,rtn   
blob ole_blob   
ll_num=dw_lb.getrow()   
if ll_num>0 then ls_jhdh=dw_lb.object.ct_njhdh[ll_num]   
select count(*) into :ll_count from sj_jh_jhfjb where ct_jhdlxbh='1' and ct_jhdh=:ls_jhdh and ct_jdlxbh=:is_jdlx;   
if ll_count>0 then   
rtn=messagebox("提示","是否要修改此附件",question!,yesno!,1)   
if rtn=1 then   
SELECTBLOB ct_jhfjnr INTO le_blob from sj_jh_jhfjb where ct_jhdlxbh='1' and ct_jhdh=:ls_jhdh and ct_jdlxbh=:is_jdlx;   
ole_1.objectdata =ole_blob   
If ole_1.activate(offsite!) <> 0 Then   
Messagebox("OLE Activate","不能啟用")   
Return -1   
end If   
end if   
else   
messagebox("提示","沒有附件")   
end if   
end if  
（二）在vb中的處理  
在vb中處理大對象，一般可以用OO4O(oracle objects for ole)來處理大對象。這裡介紹一種不用0040處理大對象blob的方法。  
下面這段程式可以將一個檔案（文字檔，doc檔案，圖象檔案等）儲存到資料庫中，並可以將其從資料庫讀出   
需要兩個commandbutton   
cmd1 名稱 cmdsave caption 儲存   
cmd2 名稱 cmdread caption 讀取   
一個cmddialog控制項   
同時需要建立一張表t_demo(欄位id 類型 number,;欄位text 類型 blob;)  
exmple 3.  
Option Explicit  
Dim rn As ADODB.Connection  
Public Function CreateDataSource(DataSource As String, UserID As String, Password As String) As Boolean  
On Error GoTo DbConErr:  
Set rn = New ADODB.Connection  
With rn  
.ConnectionString = "Provider=OraOledb.Oracle.1;" & _  
"password=" [$ Password & "]" & _  
"User ID =" [$ UserID & "]" & _  
"Data Source=" [$ DataSource & "]" & _  
"Locale Identifier=2052"  
.Open  
End With  
CreateDataSource = True  
Exit Function  
DbConErr:  
CreateDataSource = False  
End Function  
  
Private Sub cmdRead_Click()  
Dim rs As New ADODB.Recordset  
rs.ActiveConnection = rn  
rs.LockType = adLockOptimistic  
rs.CursorLocation = adUseClient  
rs.Source = "select * from t_demo"  
rs.Open  
ComDlgDir.DialogTitle = "儲存檔案"  
ComDlgDir.Filter = "*.*"  
ComDlgDir.ShowSave  
Call BlobToFile(rs.Fields("text"), ComDlgDir.filename)  
Set rs = Nothing  
Exit Sub  
Set rs = Nothing  
End Sub  
  
Private Sub cmdsave_Click()  
Dim rs As New ADODB.Recordset  
rs.ActiveConnection = rn  
rs.LockType = adLockOptimistic  
rs.CursorLocation = adUseClient  
rs.Source = "select * from t_demo"  
rs.Open  
rs.AddNew   
ComDlgDir.DialogTitle = "選取檔案"  
ComDlgDir.ShowOpen   
rs.Fields("id").Value = 1  
If ComDlgDir.filename <> "" Then  
Call FileToBlob(rs.Fields("text"), ComDlgDir.filename)  
rs.Update  
End If   
Set rs = Nothing  
Exit Sub  
Set rs = Nothing   
End Sub  
  
Private Sub Form_Load()  
If Not CreateDataSource("sid", "systemp", "manager") Then  
MsgBox "Connection failure!"  
End If  
End Sub  
  
fld As ADODB.Field, filename As String, Optional ChunkSize As Long = 8192)  
Dim fnum As Integer, bytesleft As Long, bytes As Long  
Dim tmp() As Byte  
If (fld.Attributes And adFldLong) = 0 Then  
Err.Raise 1001, , "field doesn't support the GetChunk method."  
End If  
If Dir$(filename) = "" Then Err.Raise 53, , "File not found"  
fnum = FreeFile   
Open filename For Binary As fnum  
bytesleft = LOF(fnum)  
Do While bytesleft  
bytes = bytesleft  
If bytes > ChunkSize Then bytes = ChunkSize  
ReDim tmp(1 To bytes) As Byte  
Get fnum, , tmp  
fld.AppendChunk tmp  
bytesleft = bytesleft - bytes  
Loop  
Close #fnum  
End Sub  
  
Sub BlobToFile(fld As ADODB.Field, filename As String, Optional ChunkSize As Long = 8192)  
Dim fnum As Integer, bytesleft As Long, bytes As Long  
Dim tmp() As Byte  
If (fld.Attributes And adFldLong) = 0 Then  
Err.Raise 1001, , "field doesn't support the GetChunk method."  
End If  
If Dir$(filename) <> "" Then Kill filename  
fnum = FreeFile  
Open filename For Binary As fnum  
bytesleft = fld.ActualSize  
Do While bytesleft  
bytes = bytesleft  
If bytes > ChunkSize Then bytes = ChunkSize  
tmp = fld.GetChunk(bytes)  
Put #fnum, , tmp  
bytesleft = bytesleft - bytes  
Loop  
Close #fnum  
End Sub

(三)用jdbc處理lob  
exmple 4.  
首先是Getting BLOB and CLOB Locators from a Result Set  
// Select LOB locator into standard result set.  
ResultSet rs =stmt.executeQuery ("SELECT blob_col, clob_col FROM lob_table");  
while (rs.next())  
{// Get LOB locators into Java wrapper classes.  
oracle.jdbc2.Blob blob = (oracle.jdbc2.Blob)rs.getObject(1);  
oracle.jdbc2.Clob clob = (oracle.jdbc2.Clob)rs.getObject(2);  
[...process...]  
}  
然後是Read BLOB data from BLOB locator.  
InputStream byte_stream = my_blob.getBinaryStream();  
byte [] byte_array = new byte [10];  
int bytes_read = byte_stream.read(byte_array);  
和Writing BLOB Data   
java.io.OutputStream outstream;  
// read data into a byte array   
byte[] data = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9};  
// write the array of binary data to a BLOB  
outstream = ((BLOB)my_blob).getBinaryOutputStream();  
outstream.write(data);  
還有Passing a BLOB Locator to a Prepared Statement  
OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement  
"INSERT INTO blob_table VALUES(?)");   
ops.setBLOB(1, my_blob);  
ops.execute();  
最後應該注意：  
insert的時候一定要用empty_blob()初始化  
stmt.execute ("insert into my_blob_table values ('row1', empty_blob()");  
  
（四）在pro*c中的處理  
PRO*C可以用三種方式對LOB欄位處理。   
1、The DBMS_LOB package inside PL/SQL blocks.   
2、OCI (Oracle Call Interface) function calls.   
3、Embedded SQL statements.   
Embedded SQL statements.的方式簡單而且比較靈活。OTN上提供一個例子：   
In this example we will be reading data from a BLOB with an unknown arbitrary length into a buffer and then writing the data from the buffer into an external file.   
Our buffer is small, so depending on the size of the BLOB we are reading, we may   
be able to read the BLOB value into the buffer in a single READ statement or we   
may be required to utilize a standard polling method instead.   
First we start off with oci.h and some simple local variable declarations   
example 5.  
#include <oci.h>   
OCIBlobLocator *blob ;   
FILE *fp ;   
unsigned int amt, offset = 1 ;   
Now we need a buffer to store the BLOB value and then write to the file from:   
#define MAXBUFLEN 5000   
unsigned char buffer[MAXBUFLEN] ;   
EXEC SQL VAR buffer IS RAW(MAXBUFLEN) ;   
Allocate the BLOB host variable and select a BLOB which we will READ:   
EXEC SQL ALLOCATE :blob ;   
EXEC SQL SELECT a_blob INTO :blob FROM lob_table WHERE ... ;   
We can then open the external file to which we will write the BLOB value:   
fp = fopen((const char *)"image.gif", (const char *)"w") ;   
If the buffer can hold the entire LOB value in a single READ we need to catch the   
NOT FOUND condition to signal LOB READ termination:   
EXEC SQL WHENEVER NOT FOUND GOTO end_of_lob ;   
Now do our first READ.We set the amount to the maximum value of 4 Gigabytes. It   
is larger than our buffer so if the LOB doesn't fit we will READ using a polling   
mode:   
amt = 4294967295 ;   
EXEC SQL LOB READ :amt FROM :blob AT ffset INTO :buffer ;   
If we get here then it means that the buffer was not large enough to hold the entire   
LOB value, so we must write what we have using binary I/O and continue reading:   
(void) fwrite((void *)buffer, (size_t)MAXBUFLEN, (size_t)1, fp) ;   
We use a standard polling method to continue reading with the LOB READ inside   
of an infinite loop. We can set up the NOT FOUND condition to terminate the loop:   
EXEC SQL WHENEVER NOT FOUND DO break ;   
while (TRUE)   
{   
During polling, the offset is not used so we can omit it in subsequent LOB READs.   
We need the amount, however, because it will tell us how much was READ in the   
last READ invocation   
EXEC SQL LOB READ :amt FROM :blob INTO :buffer ;   
(void) fwrite((void *)buffer, (size_t)MAXBUFLEN, (size_t)1, fp) ;   
}   
Here, we have reached the end of the LOB value. The amount holds the amount of   
the last piece that was READ. During polling, the amount for each interim piece   
was set to MAXBUFLEN, or the maximum size of our buffer:   
end_of_lob:   
(void) fwrite((void *)buffer, (size_t)amt, (size_t)1, fp) ;   
  
（五） 在delphi中的處理  
對於lob欄位而言，個人認為其使用比long類型有很大的靈活性，而且lob欄位可以儲存各類的資料，可以儲存圖片，大量的文字，現就clob跟blob兩種類型加以說明，其中blob儲存圖片資訊，clob儲存大量文字。  
exmple 6.  
Create table test_table  
(c_no number(1) not null,  
c_blob blob,  
c_clob clob,  
constraint pk_test_table primary key (c_no));  
  
unit Unit1;  
  
interface  
  
uses  
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,  
Dialogs, StdCtrls, DBCtrls, Grids, DBGrids, DB, DBTables, ExtDlgs;  
  
type  
TForm1 = class(TForm)  
Database1: TDatabase; //用於串連資料庫  
Table1: TTable; //擷取表資訊  
DataSource1: TDataSource;   
DBGrid1: TDBGrid;  
DBMemo1: TDBMemo; //顯示c_clob欄位內容  
DBImage1: TDBImage; //顯示c_blob欄位內容  
Button1: TButton; //插入按鈕  
Button2: TButton; //儲存按鈕  
Table1C_NO: TFloatField; //Tfiled  
Table1C_BLOB: TBlobField;  
Table1C_CLOB: TMemoField;  
OpenPictureDialog1: TOpenPictureDialog; //從檔案擷取圖片  
OpenDialog1: TOpenDialog; //從檔案擷取文字  
procedure Button1Click(Sender: TObject);  
procedure Button2Click(Sender: TObject);  
private  
{ Private declarations }  
public  
{ Public declarations }  
end;  
  
var  
Form1: TForm1;  
  
implementation  
  
{$R *.dfm}  
  
procedure TForm1.Button1Click(Sender: TObject);  
begin //插入操作  
with Table1 do  
begin  
Insert; //將表狀態置為插入狀態  
if OpenPictureDialog1.Execute then //獲得圖片資訊  
Table1C_BLOB.LoadFromFile(OpenPictureDialog1.FileName);  
if OpenDialog1.Execute then //獲得文字資訊  
Table1C_CLOB.LoadFromFile(OpenDialog1.FileName);  
end;  
end;  
  
procedure TForm1.Button2Click(Sender: TObject);  
begin //提交插入內容  
try  
Table1.Post;  
except  
Application.MessageBox('錯誤發生','警告',0);  
end;  
end;  
  
end.  
  
注意：  
openpiceturedilog只能開啟dmp,ico,wmf等檔案，事先需要將圖片檔案格式儲存成這幾類；  
在文字欄位不從檔案獲得時，可以手動輸入  
本例只是對lob欄位的一個小小的探索，用法不當及需改正之處，還請多多指教。  
  
注:本篇文章大部分例子均取自論壇，如有侵犯您的著作權，請來信告知，我會做相應處理。  
  
      
   注意：  
openpiceturedilog只能開啟dmp,ico,wmf等檔案，事先需要將圖片檔案格式儲存成這幾類；  
在文字欄位不從檔案獲得時，可以手動輸入