Что такое lob oracle

Обновлено: 01.07.2024

Для работы с данными большого объема СУБД Oracle предоставляет типы данных BLOB, CLOB, NCLOB и BFILE. Здесь LOB означает large object, или большой объект, и далее по тексту термины LOB и "большой объект" взаимозаменяемы. По сути, большой объект - это абстрактный тип для манипуляции данными большого объема внутри БД, а типы BLOB, CLOB, NCLOB и BFILE - его конкретные реализации.

Указанные типы данных можно использовать в СУБД Oracle для определения столбцов таблиц, атрибутов объектных типов и переменных PL/SQL.

Вот краткая характеристика этих типов:

BFILE (от binary file) - данные хранятся во внешнем по отношению к БД файле, а значение типа BFILE содержит указатель на файл; данные считаются двоичными.
BLOB (от binary large object) - данные хранятся в базе данных в отдельном сегменте * , а значение типа BLOB содержит указатель на них (LOB locator); данные считаются двоичными.
CLOB (от character large object) - данные хранятся в базе данных в отдельном сегменте * , а значение типа CLOB содержит указатель на них (LOB locator); данные интерпретируются как текст в кодировке базы данных (database character set).
NCLOB (от national character large object) - данные хранятся в базе данных в отдельном сегменте * , а значение типа CLOB содержит указатель на них (LOB locator); данные интерпретируются как текст в национальной кодировке (national character set)

* По умолчанию LOB'ы размером до 4000 байт хранятся непосредственно в строках таблицы (в табличном сегменте), а LOB'ы большего размера - в отдельном сегменте (возможно, в отдельном табличном пространстве). Это поведение регулируется опцией ENABLE|DISABLE STORAGE IN ROW команд CREATE TABLE и ALTER TABLE .

Итак, по месту хранения LOB'ы делятся на

внутренние (BLOB, CLOB, NCLOB), данные которых хранятся в БД, и
внешние (BFILE), данные которых хранятся в файлах операционной системы,

а по содержанию на

двоичные (BFILE и BLOB), для хранения данных в двоичных форматах, например, MP3, JPG, объектный код программ, и
текстовые (CLOB и NCLOB), для хранения данных в текстовых форматах, таких как XML, HTML, JSON, обычный текст.

Oracle 11g, согласно документации, работает с внутренними LOB'ами размером до 2 32 -1 байт и с BFILE файлами размером до 2 64 -1 байт.

Для работы с LOB'ами cоздам таблицу со столбцами соответствующих типов:

Вместе с таблицей были созданы сегменты для хранения больших объектов:

Для столбца типа BFILE отдельный сегмент не создан - ведь данные этого типа хранятся во внешних файлах.

Значение типа LOB может быть

NULL - неинициализировано, не содержит указателя на LOB,
пустым (empty) - указатель на LOB указывает в никуда,
непустым - указатель на LOB указывает на данные LOB'а.

Пустые LOB значения создаются функциями EMPTY_CLOB и EMPTY_BLOB :

Последний запрос демонстрирует два способа проверить, является ли LOB пустым. Запрос использует пакет DBMS_LOB , содержащий процедуры и функции для работы с LOB'ами.

Начиная с версии Oracle 9i в SQL и PL/SQL поддерживается неявная конвертация между (N)CLOB и VARCHAR2, что позволяет манипулировать значениями в (N)CLOB столбцах и переменных так, как будто это значения типа VARCHAR2:

Как видим, функции и операторы, работающие с VARCHAR2, перегружены для типа (N)CLOB! При этом преодолеваются ограничения в 4000 символов, свойственные SQL типу VARCHAR2:

А вот операторы сравнения для (N)CLOB работают только в PL/SQL и не работают в SQL:

Выше я воспользовался функциями TO_NCLOB и TO_CLOB для явной конвертации значений VARCHAR2 в значения (N)CLOB. В следующей таблице представлены все функции для конвертации в LOB типы и обратно:

Функция	Где работает
TO_CLOB(character_data)	SQL и PL/SQL
TO_BLOB(raw_data)	SQL и PL/SQL
TO_LOB(long_data)	SQL and PL/SQL
TO_NCLOB(character_data)	SQL и PL/SQL
TO_RAW(blob_data)	только PL/SQL

Как видим, функция TO_RAW недоступна в SQL и, отсюда, возможности конвертации между BLOB и RAW в SQL ограничены. Например:

Зато в PL/SQL работают явная и неявная конвертации между BLOB и RAW:

Рассмотренные возможности по работе со значениями LOB как с VARCHAR2 получили название SQL семантика для LOB'ов (SQL semаntics for LOBs). С их использованием связаны некоторые ограничения, как мы увидим ниже.

С точки зрения PL/SQL большие объекты делятся на:

временные (temporary), время жизни которых не превышает сеанса работы с СУБД,
постоянные (persistent), которые хранятся в базе данных или во внешнем файле.

создаются либо с помощью DBMS_LOB.CREATETEMPORARY , либо простым присваиванием значения LOB переменной в PL/SQL коде,
располагаются на диске во временном табличном пространстве (temporary tablespace),
могут быть проверены с помощью DBMS_LOB.ISTEMPORARY ,
освобождаются с помощью DBMS_LOB.FREETEMPORARY , что приводит к инвалидированию указателя на LOB,
в отличие от постоянных, изменяются без создания записей в журнале БД (logging) и не контролируются транзакциями,
могут быть скопированы в постоянные LOB'ы c помощью DBMS_LOB.COPY .

В вышеприведенных примерах с PL/SQL мы имели дело с временными LOB'ами.

Для работы с постоянными LOB'ами в PL/SQL нужно сначала получить указатель на LOB, а затем с его помощью извлекать или изменять данные, используя пакет DBMS_LOB . Следующий пример демонстрирует получение постоянного LOB'а и его потерю(!) при попытке изменить его значение простым присваиванием:

Дело в том, что SQL семантика для LOB'ов всегда порождает временные LOB'ы - это и есть то ограничение, о котором я упоминал выше. Неявное приведение VARCHAR2 к LOB (строка 7) или функция, перегруженная для (N)CLOB (строка 14), дают нам временные LOB'ы. Как только переменной PL/SQL, указывающей на постоянный LOB, присваивается временный LOB, переменная начинает указывать на временный LOB. А связь переменной с постоянным LOB'ом утрачивается.

Значение временного LOB'а можно сохранить в БД - и тем самым сделать постоянным - либо с помощью SQL либо, как уже упоминалось, с помощью DBMS_LOB.COPY . Продемонстрирую обе возможности:

Обратите внимание, что процедура DBMS_LOB.COPY заменила в постоянном NCLOB c3 только фрагмент, равный по размеру значению исходного NCLOB'а c2 . Как вариант, можно было перед копированием очистить LOB назначения с помощью DBMS_LOB.ERASE .

Изменения внутренних постоянных LOB'ов (в отличие от внешних или временных) в СУБД Oracle подчиняются транзакциям. Убедимся в этом, отменив только что сделанные изменения:

Типичный алгоритм для чтения или изменения постоянного LOB'а с помощью PL/SQL таков:

Извлечь указатель на LOB из столбца таблицы с помощью SELECT .
Открыть большой объект с помощью DBMS_LOB.OPEN .
Получить оптимальный размер фрагмента для чтения (записи) LOB с помощью DBMS_LOB.GETCHUNKSIZE
Получить размер LOB'а в байтах (для BLOB и BFILE) или символах (для CLOB и NCLOB) с помощью DBMS_LOB.GETLENGTH .
Многократно вызывать DBMS_LOB.READ для чтения последовательных фрагментов LOB'а, пока не будут извлечены все данные
ИЛИ
многократно вызывать DBMS_LOB.WRITE , со смещением, или DBMS_LOB.WRITEAPPEND или иные процедуры DBMS_LOB для записи фрагментов данных.
Закрыть LOB с помощью DBMS_LOB.CLOSE .

В предыдущем примере с DBMS_LOB.COPY я не открывал и не закрывал постоянный LOB при помощи DBMS_LOB.OPEN и DBMS_LOB.CLOSE , однако, это стоит делать для улучшения производительности при изменениях больших объектов.

Приведу пример выгрузки данных из постоянного CLOB'а во внешний файл. Для доступа к внешним файлам потребуется создать директорию, например:

Следующий код выгружает содержимое столбца lobs_tab.clob_col в файл clob_col1.txt , используя пакет DBMS_LOB для чтения CLOB и пакет UTL_FILE для записи во внешний файл:

Альтернативно, можно выгрузить CLOB во внешний файл, пользуясь SQL семантикой для LOB и не прибегая к DBMS_LOB :

Для обратной операции - загрузки содержимого файла в LOB - также можно воспользоваться пакетами UTL_FILE и DBMS_LOB , циклически читая данные из файла и записывая в LOB. Но интереснее сделать это с помощью типа данных BFILE.

Тип данных BFILE содержит указатель на внешний файл, который

состоит из двух частей: имя директории и имя файла,
создается с помощью функции BFILENAME , например, BFILENMAE('FILES_DIR', 'novel.txt') ,
может указывать на несуществующий файл.

Пакет DBMS_LOB позволяет читать содержимое BFILE, но не изменять его. Чтение из BFILE возвращает двоичные данные как тип данных RAW. Для преобразования в VARCHAR2, при необходимости, используется функция UTL_RAW.CAST_TO_VARCHAR2 .

Пример чтения BFILE и записи во временный BLOB:

В примере BFILE открывается и закрывается с помощью OPEN и CLOSE , аналогично внутренним LOB'ам. Также, пакет DBMS_LOB содержит несколько процедур и функций специально для работы с объектами BFILE:

Процедура / Функция	Что делает
FILEGETNAME	возвращает имя директории и файла BFILE
FILEEXISTS	проверяет, что файл BFILE существует
FILEOPEN	открывает файл BFILE
FILEISOPEN	проверяет, что файл BFILE открыт
FILECLOSE	закрывает файл BFILE
FILECLOSEALL	закрывает все открытые в сеансе файлы BFILE

Вместо чтения BFILE по частям пакет DBMS_LOB позволяет

с помощью LOADCLOBFROMFILE загрузить содержимое BFILE в CLOB, указав, какую кодировку (набор символов) имеет содержимое,
с помощью LOADBLOBFROMFILE загрузить содержимое BFILE в BLOB.

Пример загрузки текстового файла во временный CLOB (аналогично можно загрузить и в постоянный CLOB):

Значения src_offset и dest_offset отличаются, поскольку первое, для BFILE, выражено в байтах, а второе, для CLOB, выражено в символах. В файле и CLOB'е имеются девять двухбайтовых русских букв - напомню, их содержимое начинается с " привет, мир ".

Приведу неполный список процедур и функций DBMS_LOB для чтения, анализа и изменения значений BLOB, CLOB и NCLOB:

Процедура / Функция	Что делает
APPEND	добавляет один LOB в конец другого
COPY	копирует все или часть содержимого LOB'а в другой LOB
ERASE	удаляет все или часть содержимого LOB'а
GETLENGTH	возвращает размер LOB'а
INSTR	ищет "подстроку" в LOB'е
ISOPEN	проверяет, открыт ли LOB
ISTEMPORARY	проверяет, временный ли LOB
READ	читает данные LOB'а
SUBSTR	получает "подстроку" из LOB'а
TRIM	сокращает размер LOB'а до указанного
WRITE	записывает данные в LOB
WRITEAPPEND	записывает данные в конец LOB'а

Следующий эксперимент покажет разницу между внутренними и внешними постоянными LOB'ами. Помещу в поле bfile_col таблицы lobs_tab объект BFILE и скопирую единственную строку таблицы во вторую строку:

Команда INSERT привела к тому, что значения bfile_col в обеих строках связаны с одним и тем же внешним файлом, и его изменение отразится на обоих значениях.

А вот значения столбцов clob_col , nclob_col и blob_col для строк 1 и 2 стали независимы - не только указатели на LOB, но и данные внутренних LOB'ов в LOB-сегментах были скопированы. Продемонстрирую их независимость, изменив значения clob_col и nclob_col для строки 2:

Аналогично, при присваивании BLOB и (N)CLOB переменных в PL/SQL мы получаем независимые копии LOB'ов:

Итак, мы на примерах рассмотрели работу с большими объектами в SQL и PL/SQL. Работа с большими объектами имеет и другой аспект - это технология SecureFiles, позволяющая, в частности, сжимать хранимые в LOB-сегментах данные, свести к минимуму их дублирование, шифровать эти данные. Но эта тема выходит за рамки данного очерка.

Читайте также: