LOB的基本操作和工作原理

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

在本文中，我們將探討 LOB 資料型別在 DBMS 和 SQL 中的基本操作和工作原理。我們還將提供如何在 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 列不包含任何資料，長度為零。

  • Populated − LOB 列包含資料，長度大於零。

可以使用 IS NULL 和 IS EMPTY 謂詞確定 LOB 列的狀態。

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

結論

在本文中，我們探討了 LOB 資料型別在 DBMS 和 SQL 中的基本操作和工作原理。LOB 資料型別對於儲存和處理大量非結構化資料（例如文字、影像和影片）非常有用。我們還提供瞭如何在 SQL 中使用 LOB 資料型別儲存、更新、選擇和刪除大量非結構化資料的示例，以及如何執行高階操作（如搜尋、比較、截斷、複製和連線 LOB 資料）。

更新時間： 2023年1月10日

324 次瀏覽

啟動您的 職業生涯

透過完成課程獲得認證

開始
廣告