Basic operations and working of LOB

LOB, or Large Object, is a data type in a database management system (DBMS) used to store large amounts of unstructured data such as text, images, and videos. The LOB data type is useful for storing and manipulating data that does not fit into a traditional row-column structure, such as documents, graphics, or audio files.

In this article, we will explore the basic operations and working principles of LOB data types in DBMS and SQL. We will also provide examples of how to use LOB data types in SQL to store and manipulate large amounts of unstructured data.

LOB data type

There are several types of LOB data, including −

• BLOB (Binary Large Object)− A BLOB is a set of binary data, such as an image, audio, or video file. BLOB data is stored in a sequence of bytes and has no specific character set.

• CLOB (Character Large Object) - CLOB is a collection of character data, such as text Document or HTML file. CLOB data is stored as a sequence of characters and has A specific character set, such as UTF-8 or ASCII

• NCLOB (National Character Large Object) − NCLOB is similar to CLOB, but is used to store character data using national character sets (such as Chinese, Japanese, or Korean).

How the LOB data type works

LOB data types are stored in a special area of ​​the database, called the LOB storage area. This allows LOB data to be stored and accessed separately from the rest of the database, improving performance and efficiency when processing large amounts of unstructured data.

LOB data is accessed using pointers, which are references to the location of the LOB data in the LOB storage area. The pointers are stored in the database along with the rest of the data, but the actual LOB data is stored in the LOB storage area. This allows the database to access LOB data quickly and efficiently without having to store the entire LOB in the database itself.

LOB column status

LOB columns can be in one of three states -

• NULL − LOB column does not contain any data.

• EMPTY - LOB column contains no data and has zero length.

• Polled - The LOB column contains data and has a length greater than zero.

The status of a LOB column can be determined using the IS NULL and IS EMPTY predicates.

-- Check if a LOB column is NULL
SELECT doc_id
FROM documents
WHERE doc_text IS NULL;

-- Check if a LOB column is EMPTY
SELECT doc_id
FROM documents
WHERE doc_text IS EMPTY;

-- Check if a LOB column is populated
SELECT doc_id
FROM documents
WHERE doc_text IS NOT NULL AND doc_text IS NOT EMPTY;

It should be noted that even if the length of a LOB column is not zero, it can be in the EMPTY state. This may occur if the LOB column contains only spaces or control characters. To check this, you can use the LENGTH function to determine the actual length of the LOB data.

-- Check if a LOB column is EMPTY but has a non-zero length
SELECT doc_id
FROM documents
WHERE doc_text IS NOT NULL AND doc_text IS EMPTY AND LENGTH(doc_text) > 0;

Basic operations of LOB data

In SQL, you can perform several basic operations on LOB data, including -

Insert LOB data - You can use the INSERT statement to insert LOB data into the database. LOB data can be specified as strings, files, or program variables.

-- Insert a BLOB from a file
INSERT INTO images (image_id, image)
VALUES (1, BFILENAME('IMAGE_DIR', 'image1.jpg'));

-- Insert a CLOB from a string literal
INSERT INTO documents (doc_id, doc_text)
VALUES (1, 'This is the text of the document.');

-- Insert a NCLOB from a program variable
DECLARE
   doc_text CLOB;
BEGIN
   doc_text := 'WELCOME';
   INSERT INTO documents (doc_id, doc_text)
   VALUES (2, doc_text);
END;

Update LOB data - You can use the UPDATE statement to update LOB data. LOB data can be specified as string literals, files, or program variables.

-- Update a BLOB with a file
UPDATE images
SET image = BFILENAME('IMAGE_DIR', 'image2.jpg')
WHERE image_id = 1;

-- Update a CLOB with a string literal
UPDATE documents
SET doc_text = 'This is the updated text of the document.'
WHERE doc_id = 1;

-- Update a NCLOB with a program variable
DECLARE
doc_text CLOB;
BEGIN
doc_text := 'WELCOME';
UPDATE documents
SET doc_text = doc_text
WHERE doc_id = 2;
END;

Select LOB data - LOB data can be retrieved from the database using the "SELECT" statement. LOB data can be returned as a string or written to a file.

-- Select a BLOB and write it to a file
SELECT image
INTO BFILENAME('IMAGE_DIR', 'image3.jpg')
FROM images
WHERE image_id = 1;

-- Select a CLOB and return it as a string
SELECT doc_text
FROM documents
WHERE doc_id = 1;

-- Select a NCLOB and return it as a string
SELECT doc_text
FROM documents
WHERE doc_id = 2;

Delete LOB data − You can use the DELETE statement to delete LOB data from the database.

-- Delete LOB data
DELETE FROM images
WHERE image_id = 1;

DELETE FROM documents
WHERE doc_id = 1;

DELETE FROM documents
WHERE doc_id = 2;

Advanced operations on LOB data

In addition to the basic operations described above, several advanced operations can be performed on LOB data in SQL.

Search LOB data

The LIKE operator can be used to search for specific patterns in LOB data. The DBMS_LOB package also provides several functions for searching and manipulating LOB data.

-- Search a CLOB for a specific pattern
SELECT doc_id
FROM documents
WHERE doc_text LIKE '%specific pattern%';

-- Use the INSTR function to search a CLOB
SELECT doc_id
FROM documents
WHERE INSTR(doc_text, 'specific pattern') > 0;

-- Use the SUBSTR function to extract a portion of a CLOB
SELECT SUBSTR(doc_text, 1, 50)
FROM documents
WHERE doc_id = 1;

Compare LOB data

The

= operator can be used to compare LOB data for equality. The DBMS_LOB package also provides the COMPARE function to compare LOB data.

-- Compare LOB data for equality
SELECT doc_id
FROM documents
WHERE doc_text = 'This is the text of the document.';

-- Use the COMPARE function to compare LOB data
SELECT doc_id
FROM documents
WHERE COMPARE(doc_text, 'This is the text of the document.') = 0;

Truncate LOB data

The DBMS_LOB software package provides the TRUNCATE function to truncate LOB data to a specified length.

-- Truncate a CLOB to 50 characters
UPDATE documents
SET doc_text = TRUNCATE(doc_text, 50)
WHERE doc_id = 1;

Copy LOB data

The DBMS_LOB package provides the COPY function for copying LOB data from one LOB to another.

-- Copy CLOB data from one row to another
DECLARE
   source_doc CLOB;
   target_doc CLOB;
BEGIN
   SELECT doc_text INTO source_doc FROM documents WHERE doc_id = 1;
   SELECT doc_text INTO target_doc FROM documents WHERE doc_id = 2;
   COPY(source_doc, target_doc);
   UPDATE documents SET doc_text = target_doc WHERE doc_id = 2;
END;

Connect LOB data

The DBMS_LOB package provides the CONCATENATE function for connecting two LOBs together.

-- Concatenate two CLOBs together
DECLARE
   doc1 CLOB;
   doc2 CLOB;
   doc3 CLOB;
BEGIN
   SELECT doc_text INTO doc1 FROM documents WHERE doc_id = 1;
   SELECT doc_text INTO doc2 FROM documents WHERE doc_id = 2;
   doc3 := CONCATENATE(doc1, doc2);
   INSERT INTO documents (doc_id, doc_text) VALUES (3, doc3);
END;

in conclusion

In this article, we explored the basic operations and working principles of LOB data types in DBMS and SQL. The LOB data type is suitable for storing and manipulating large amounts of unstructured data, such as text, images, and videos. We also provide examples of how to use the LOB data type in SQL to store, update, select, and delete large amounts of unstructured data, as well as perform advanced operations such as searching, comparing, truncation, copying, and joining LOB data.

The above is the detailed content of Basic operations and working of LOB. For more information, please follow other related articles on the PHP Chinese website!