LOB Large Object handling: is primarily a database field for storing large amounts of data, which can be stored 4G non-structured data of bytes . mainly introduces the character type and the storage of the binary file type LOB data, separately describes The storage of the binary type LOB data.
a. Oraclein theLOBData Type Classification
1 , divided by the type of data stored:
①Character type:
CLOB: Store a large numberSingle bytecharacter data.
Nlob: Storage Fixed WidthMulti-bytecharacter data.
②Binary Type:
BLOB: stores large, unstructured binary data.
③binary file type:
BFILE: Store the binaries in the operating system files outside the database. The file path is stored.
2 , divided by storage mode:
①stored in the internal tablespace:
CLOB ,Nloband the BLOB
②point to external operating system files:
BFILE
Second, the large object processing method. Reference book us River offers. Proficient in Oracle.10g.pl.sql programming
1,Dbms_lob. Read (): The process of reading the specified length of data from the LOB data into the buffer.
Dbms_lob. Read (LOB data, specifying length, starting position,
Store returns LOB Type value Variable );
2,Dbms_lob. SUBSTR (): a function that extracts substrings from LOB data.
Dbms_lob. SUBSTR (LOB data, specifying extract length, extracting starting position ):
③ : dbms_lob. INSTR () : from lob The function that finds the position of the substring in the data.
dbms_lob. INSTR (LOB data < Span style= "font-family: ' Times New Roman ';" >, );
: dbms_lob. GetLength () : Returns the specified lob The length of the data function.
data
⑤:Dbms_lob.compare (): Compares two large objects for equality. return value0is equal,-1are not equal.
dbms_lob.compare (LOB data,LOBData);
6.Dbms_lob. Write (): Writes the specified amount of data toLOBthe process.
Dbms_lob. Write ( is writtenLOB,Write Length(refers to writingLOBData), write starting position(refers to being writtenLOB), writeLOBData);
7, Dbms_lob. Append (): Adds the specifiedLOBdata is appended to the specifiedLOBthe process after the data.
Dbms_lob. Append (LOB data,LOBData);
dbms_lob. Erase () : Delete lob
dbms_lob. Erase (LOB
9, Dbms_lob. Trim () : Truncate lob
10, Dbms_lob. Copy (): Starts the source from the specified locationLOBCopy to targetLOB;
Dbms_lob. Copy ( SourceLOB, the targetLOB, copy sourceLOBlength, copy to targetLOBstart location, copy sourceLOBStart Position)
Example: Looping out XML Big data content
Declare
Amount number;
BUF VARCHAR2 (10000): = ";
Temp VARCHAR2 (1000): = ";
Begin
Amount: = 1;
For I in 1..200 loop
Temp: = ';
Select Dbms_lob.substr (krm.doc_content (CLOB type), 400,amount) into temp from km_review_main/* @ekp_link */KRM
where krm.fd_id = ' 138eb83e89178bcce17136b43f8ab176 ';
Amount: = Amount + 400;
Dbms_output.put_line (temp);
End Loop;
End
Reference website:
http://blog.csdn.net/tianlesoftware/article/details/5070981
ORACLE LOB Large Object processing