Что такое lob oracle
Для работы с данными большого объема СУБД 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-сегментах данные, свести к минимуму их дублирование, шифровать эти данные. Но эта тема выходит за рамки данного очерка.
Читайте также: