ORACLE LOB Large Object processing

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.