LOB的基本操作和工作

LOB（即大型物件）是資料庫管理系統 (DBMS) 中的一種資料類型，用於儲存大量非結構化數據，例如文字、影像和影片。 LOB 資料類型對於儲存和操作不適合傳統行列結構的資料非常有用，例如文件、圖形或音訊檔案。

在本文中，我們將探討DBMS和SQL中LOB資料類型的基本操作和工作原理。我們還將提供如何在SQL中使用LOB資料類型來儲存和操作大量非結構化資料的範例。

LOB資料型別

有幾種類型的LOB數據，包括−

• BLOB（二進位大物件）− BLOB是一組二進位數據，例如影像、音訊或視訊檔案。 BLOB資料以位元組序列存儲，沒有特定的字元集。

• CLOB（字元大物件） - CLOB 是字元資料的集合，例如文本 文件或 HTML 文件。 CLOB 資料儲存為字元序列並具有 特定字元集，例如 UTF-8 或 ASCII

• NCLOB（國家字元大物件） − NCLOB與CLOB類似，但用於儲存使用國家字元集（如中文、日文或韓文）的字元資料。

LOB資料類型的工作原理

LOB資料類型儲存在資料庫的特殊區域中，稱為LOB儲存區域。這樣可以將LOB資料與資料庫的其他部分分開儲存和訪問，從而在處理大量非結構化資料時提高效能和效率。

LOB資料是使用指標存取的，指標是LOB儲存區域中LOB資料位置的引用。指標與其餘資料一起儲存在資料庫中，但實際的 LOB 資料儲存在 LOB 儲存區域。這使得資料庫能夠快速有效地存取 LOB 數據，而無需將整個 LOB 儲存在資料庫本身中。

LOB列狀態

LOB 欄位可以處於三種狀態之一 -

• NULL − LOB欄位不包含任何資料。

• EMPTY - LOB 欄位不包含資料且長度為零。

• 已填入 - LOB 欄位包含資料且長度大於零。

#LOB 欄位的狀態可以使用 IS NULL 和 IS EMPTY 謂詞來決定。

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

要注意的是，即使 LOB 欄位的長度不為零，它也可以處於 EMPTY 狀態。如果 LOB 列僅包含空格或控製字符，則可能會發生這種情況。要檢查這一點，您可以使用 LENGTH 函數來確定 LOB 資料的實際長度。

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

LOB資料的基本操作

在SQL中，可以對LOB資料執行幾個基本操作，包括 -

插入 LOB 資料 - 可以使用 INSERT 語句將 LOB 資料插入資料庫。 LOB 資料可以指定為字串、檔案或程式變數。

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

更新LOB資料 - 可以使用UPDATE語句更新LOB資料。 LOB資料可以指定為字串字面值、檔案或程式變數。

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

選擇 LOB 資料 - 可以使用「SELECT」語句從資料庫中擷取 LOB 資料。 LOB 資料可以作為字串傳回或寫入檔案。

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

刪除LOB資料 − 可以使用DELETE語句從資料庫中刪除LOB資料。

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

DELETE FROM documents
WHERE doc_id = 1;

DELETE FROM documents
WHERE doc_id = 2;

LOB資料的進階操作

除了上述描述的基本操作之外，還可以在SQL中對LOB資料執行幾個進階操作。

搜尋 LOB 資料

LIKE 運算子可用於搜尋 LOB 資料中的特定模式。 DBMS_LOB 套件還提供了幾個用於搜尋和操作 LOB 資料的函數。

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

比較 LOB 資料

= 運算子可用來比較 LOB 資料是否相等。 DBMS_LOB套件也提供了COMPARE函數來比較LOB資料。

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

截斷 LOB 資料

DBMS_LOB軟體包提供了TRUNCATE函數，用於將LOB資料截斷到指定長度。

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

複製 LOB 資料

DBMS_LOB套件提供了一個COPY函數，用於將LOB資料從一個LOB複製到另一個LOB。

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

連接 LOB 資料

DBMS_LOB套件提供了CONCATENATE函數，用於將兩個LOB連接在一起。

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

結論

在本文中，我們探討了DBMS和SQL中LOB資料類型的基本操作和工作原理。 LOB數據類型適用於儲存和操作大量的非結構化數據，如文字、圖像和影片。我們也提供如何在SQL中使用LOB資料類型來儲存、更新、選擇和刪除大量的非結構化資料的範例，以及執行進階操作，例如搜尋、比較、截斷、複製和連接LOB資料。

以上是LOB的基本操作和工作的詳細內容。更多資訊請關注PHP中文網其他相關文章！