ORACLE LOB Large Object processing

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