Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

ORACLE LOB large object processing

2025-01-16 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

LOB large object processing: database fields that are mainly used to store large amounts of data, up to 4G bytes of unstructured data. This paper mainly introduces the storage of character type and binary file type LOB data, and introduces the storage of binary type LOB data separately.

one。 Classification of LOB data types in Oracle

1, according to the type of data stored:

① character type:

CLOB: stores large amounts of single-byte character data.

NLOB: stores fixed-width multi-byte character data.

② binary type:

BLOB: stores large, unstructured binary data.

③ binary file type:

BFILE: stores binaries in operating system files outside the database. The path to the file.

2. According to the storage mode:

① is stored in the internal tablespace:

CLOB,NLOB and BLOB

② points to the external operating system file:

BFILE

Second, the method of dealing with large objects. The reference book is provided by Meihe. Proficient in Oracle.10g.Pl.SQL programming

1. DBMS_LOB.Read (): the process of reading data of a specified length from LOB data to a buffer.

DBMS_LOB.Read (LOB data, specify length, start position

Store returns LOB type value variable)

2. DBMS_LOB.SubStr (): a function that extracts substrings from LOB data.

DBMS_LOB.SubStr (LOB data, specify extraction length, extraction start position):

③: DBMS_LOB.InStr (): a function that looks up the position of a substring from LOB data.

DBMS_LOB.InStr (LOB data, substring)

④: DBMS_LOB.GetLength (): a function that returns the length of the specified LOB data.

DBMS_LOB.GetLength (LOB data)

⑤: DBMS_LOB.Compare (): compare whether two large objects are equal. The returned value 0 is equal and-1 is unequal.

DBMS_LOB.Compare (LOB data, LOB data)

6. DBMS_LOB.Write (): the process of writing a specified amount of data to LOB.

DBMS_LOB.Write (written LOB, write length (refers to write LOB data), write start position (refers to written LOB), write LOB data)

7. DBMS_LOB.Append (): the process of appending specified LOB data to specified LOB data.

DBMS_LOB.Append (LOB data, LOB data)

8. DBMS_LOB.Erase (): the process of deleting part of the LOB data at a specified location

DBMS_LOB.Erase (LOB data, specify deletion length, start deletion location)

9. DBMS_LOB.Trim (): the process of truncating part of LOB data with a specified length starting from the first location

DBMS_LOB.Trim (LOB data, truncated length)

10. DBMS_LOB.Copy (): copy the source LOB to the destination LOB from the specified location

DBMS_LOB.Copy (source LOB, destination LOB, copy source LOB length, copy to destination LOB start location, copy source LOB start location)

For example: looping out the contents of xml big data

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 quant 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report