Clob тип данных oracle

Обновлено: 08.07.2024

На рис. 11.1 приведен список встроенных типов PL/SQL .

Скалярные типы описывают простые значения, не имеющие внутренних составляющих.

Составные типы описывают структуры, в которых имеются внутренние компоненты.

Ссылочные типы содержат значения. LOB типы содержат значения, называемые локаторами, которые определяют расположение больших объектов хранимых данных (например, графические файлы).

В следующей таблице приведено описание некоторых типов данных языка PL/SQL .

Все встроенные типы данных являются базовыми типами.

Любой базовый тип PL/SQL определяется как набор значений и набор операций, выполнимых над этими значениями.

Язык PL/SQL позволяет определять новые подтипы как подмножество значений некоторого базового типа с тем же набором операций. Подтип не вводит никаких дополнительных операций над данными и не определяет никакого нового типа.

Определение подтипа может иметь следующее формальное описание:

В пакете STANDARD базы данных Oracle, автоматически подключаемом для любого блока , определено несколько подтипов .

Пользователь может определить свой тип как некоторый подтип в секции объявлений блока , подпрограммы или пакете PL/SQL .

Типы, используемые как базовые, не могут содержать ограничений длины. Для создания пользовательского типа с ограничением длины предварительно объявляется переменная такого типа и уже на ее основе определяется пользовательский тип.

LOB-типы

LOB-типы используются для хранения больших объектов (Large Object). Стандарт SQL-99 ввел поддержку LOB-типов для расширенного уровня соответствия. Однако в Oracle реализован более полный набор LOB-типов .

В Oracle8 позволяется хранить данные LOB-типа до 4 Гбайт.

Типы LOB от типа LONG отличаются, главным образом, тем, что при выборе значения любого LOB-типа посредством оператора SELECT возвращается указатель, а не само значение; кроме того, типы LOB могут быть и внешними.

Oracle поддерживает следующие четыре типа для больших объектов:

BFILE - для внешнего двоичного файла;
BLOB - для внутреннего двоичного объекта;
CLOB - для внутреннего символьного объекта;
NCLOB - для внутреннего символьного объекта, учитывающего национальный набор символов.

Любой объект LOB состоит из двух частей: данных и указателя на эти данные, называемого локатором.

Типы BLOB , CLOB или NCLOB могут использоваться как для столбца базы данных, так и для переменной PL/SQL .

Для загрузки объекта LOB предусмотрен пакет PL/SQL DBMS_LOB.

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

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

Oracle и PL/SQL поддерживают несколько разновидностей типов данных, предназначенных специально для работы с большими объектами ( LOB, Large OBjects ). Такие объекты позволяют хранить огромные (от 8 до 128 терабайт) объемы двоичных (например, графических) или текстовых данных.

До выхода Oracle9i Release2 в объектах LOB можно было хранить до 4 Гбайт данных. Начиная с Oracle10g, ограничение было повышено до величины от 8 до 128 терабайт (конкретное значение зависит от размера блока вашей базы данных).

В PL/SQL можно объявлять большие объекты четырех типов:

BFILE — двоичный файл. Переменная этого типа содержит локатор файла, указывающий на файл операционной системы вне базы данных. Oracle интерпретирует содержимое файла как двоичные данные.
BLOB — большой двоичный объект. Переменная этого типа содержит локатор LOB , указывающий на большой двоичный объект, хранящийся в базе данных.
CLOB — большой символьный объект. Переменная этого типа содержит локатор LOB , указывающий на хранящийся в базе данных большой блок текстовых данных в наборе символов базы данных.
NCLOB — большой символьный объект с поддержкой символов национальных языков ( NLS ). Переменная этого типа содержит локатор LOB , указывающий на хранящийся в базе данных большой блок текстовых данных с национальным набором символов.

Большие объекты можно разделить на две категории: внутренние и внешние. Внутренние большие объекты (типы BLOB, CLOB и NCLOB ) хранятся в базе данных и могут участвовать в транзакциях на сервере базы данных. Внешние большие объекты (тип BFILE ) представляют двоичные данные, хранящиеся в файлах операционной системы вне таблиц базы данных. Они не могут участвовать в транзакциях, то есть вносимые в них изменения нельзя сохранить или отменить в зависимости от результата транзакции. Целостность данных обеспечивается только на уровне файловой системы. Кроме того, повторное чтение из BFILE может приводить к разным результатам — в отличие от внутренних больших объектов, соответствующих модели логической целостности чтения.

LONG И LONG RAW

Вероятно, спецы, знакомые с Oracle, заметили, что мы до сих пор не упоминали о типах данных LONG и LONG RAW . И это не случайно. Конечно, в столбцах типа LONG и LONG RAW базы данных можно хранить большие объемы (до 2 Гбайт) соответственно символьных и двоичных данных. Однако максимальная длина соответствующих им переменных PL/SQL значительно меньше: всего лишь 32 760 байт, что даже меньше длины переменных VARCHAR2 и RAW (32 767 байт). С учетом столь странного ограничения в программах PL/SQL лучше использовать переменные типа VARCHAR2 и RAW , а не типа LONG и LONG RAW.

Значения типов LONG и LONG RAW , извлекаемые из базы данных и содержащие более 32 767 байт данных, не могут присваиваться переменным типа VARCHAR2 и RAW . Это крайне неудобное ограничение, из-за которого типы LONG и LONG RAW лучше вообще не применять.

Эти типы официально считаются устаревшими и поддерживаются только для сохранения обратной совместимости кода. Компания Oracle не рекомендует ими пользоваться, и я с ней полностью согласен. В новых приложениях вместо них лучше использовать типы CLOB и BLOB . А для существующих приложений в документации Oracle SecureFiles and Large Objects Developer’s Guide приводятся рекомендации по преобразованию данных типа LONG в данные типа LOB .

До OpenEdge 10 работа с данными ограничивалась максимальным объемом 32K. В OpenEdge 10 были введены новые типы данных, которые позволили работать с объемами данных превышающие данное ограничение.

BLOB (Binary Large Object) - тип данных предназначен для хранение больших бинарных данных, таких как картинки, аудио - видео записи и т.д.

CLOB (Character Large Object) - тип данных предназначен для хранения больших текстовых данных заданной кодировки.

Третьим типом данных для работы с большими объемами данных, появившемся с OpenEdge 10 является тип LONGCHAR, но о нем мы уже говорили.

Стоит отметить, что в отличие от типа данных LONGCHAR, типы данных BLOB и CLOB могут быть определены только для полей таблиц и не могут быть определены для переменных.

РАБОТА С ТИПАМИ БОЛЬШИХ ДАННЫХ

COPY-LOB осуществляет копирование больших типов данных. При этом невозможно напрямую осуществлять копирование данных между типами BLOB и CLOB. Для этого необходимо скопировать данные из CLOB или BLOB источников в MEMPTR или LONGCHAR с преобразованием данных, а впоследствии уже осуществить копирование из MEMPTR или LONGCHAR в CLOB или BLOB .

COPY-LOB [FROM]source-lob | FILE source-filename>
[STARTING AT n] [FOR length]
TO target-lob> [OVERLAY AT n [TRIM]] |
FILE target-filename [APPEND]>
[NO-COVERT | CONVERT <[SOURCE CODEPAGE codepage]
[TARGET CODEPAGE codepage]>]
[NO-ERROR].

FROM - определяет источник данных для копирования:

[OBJECT] source-lob - определяет источник данных для копирования в качестве которого может выступать переменные MEMPTR или LONGCHAR типа, либо поля таблиц типов BLOB и CLOB.

FILE source-filename - определяет имя файла, содержимое которого необходимо скопировать.

STARTING AT n - определяет целочисленное значение позиции, с которой начинается чтение данных из источника. Если позиция не определена, то по умолчанию она принимается равное 1. Для байтовых типов данных таких как BLOB или MEMPTR стартовая позиция определяет позицию байта, а для текстовых типов, таких как CLOB или LONGCHAR - позицию символа с которого начинается чтение данных.

FOR length - определяет целочисленное значение байт для байтовых источников данных, или количество символов для текстовых типов, которое необходимо скопировать. По умолчанию, читаются все данные до конца.

ТО - определяет приемник копируемых данных:

[OBJECT] target-lob - определяет приемник копируемых данных, в качестве которого могут выступать переменные MEMPTR или LONGCHAR типа, либо поля таблиц типов BLOB и CLOB.

OVERLAY AT n [TRIM] - определяет целочисленное значение позиции, с которой начинается вставка копируемых данных в источник. Если позиция не определена, то по умолчанию она принимается равное 1. Для байтовых типов данных, таких как BLOB или MEMPTR, стартовая позиция определяет позицию байта, а для текстовых типов, таких как CLOB или LONGCHAR - позицию символа с которого начинается вставка данных.

FILE target-filename [APPEND] - определяет имя файла, в который осуществляется копирование данных.

NO-CONVERT - не осуществлять конвертацию копируемых данных.

CONVERT <[SOURCE CODEPAGE codepage] [TARGET CODEPAGE codepage]> - осуществляет конвертацию копируемых данных.

Значение и различие типов данных больших объектов blob, clob, nclob в Oracle

Недавно при вставке и извлечении данных из таблицы я столкнулся с NCLOB Тип поля, когда результат поиска повторяется несколько раз, значение этого поля всегда отображается как NULL , Или только первая часть значения [dbms_lob.substr(TX)] Или кеша не хватает [TO_CHAR(TX)] В ожидании вопроса, так началась Baidu. Казалось, что я столкнулся с этим типом поля год назад, но я не осознавал себя достаточно, чтобы привлечь внимание. Ниже приводится информация, которую я нашел в Интернете, и мое собственное решение этой проблемы. [TO_NCLOB(TX)] Я надеюсь помочь всем.

Один, blob 、 clob 、 nclob Что означает?

1 , Они оба oracle Типы данных больших объектов в базе данных, oracle Предоставляем специальные пакеты для его эксплуатации.

2 ． blob 、 clob 、 nclob Есть три больших объекта (LOB) , Используется для сохранения больших графических файлов или форматированных текстовых файлов, таких как Miceosoft Word Документы и нетекстовые файлы, такие как аудио и видео, максимальная длина составляет 4GB 。

3 ． LOB Есть несколько типов, в зависимости от типа байтов, которые вы используете, Oracle 8i Действительно сохранить эти данные в базе данных для сохранения. Специальные операции, такие как чтение, хранение и запись могут быть выполнены.

4 ． CLOB(Character Large Object) Используется для хранения символьных данных, соответствующих набору символов, определенному базой данных. (Похожий на long Типы)

5 ． BLOB(Binary Large Object) Может использоваться для хранения неструктурированных двоичных данных. (Похожий на row с long row ）

два, LOB из BLOB 、 CLOB 、 BFILE Каковы различия и для чего они используются? Как это сделать?

BLOB ---- Внутренний бинарный большой объект.

CLOB ---- Внутренний характер крупного объекта.

NCLOB ---- Внутренний многобайтовый символ большой длины фиксированной длины.

BFILE ---- Внешний бинарный файл.

LOB Состоит из двух частей: данные (значение) и указатель на данные (локатор). Хотя значение хранится в самой таблице, но один LOB Столбец не содержит значения, только его позиционный указатель. Кроме того, чтобы использовать большие объекты, программа должна объявить локальные переменные типа локатора. когда LOB (Помимо BFILE ) Когда он создается, локатор сохраняется в столбце, значение сохраняется в LOB В этом абзаце LOB Сегменты являются частью таблиц в базе данных. когда BFILE При создании локатор сохраняется в столбце как обычно.

три, blob с clob Какой максимум? Или нет максимального ограничения?

Их максимальный предел 4G ， Clob Может хранить однобайтовые символьные данные, Blob Может хранить неструктурированные двоичные данные.

Ниже приведен случай кода, когда я решаю вставку и извлечение поля ,по assertEquals() Суждение, вставленное значение и извлеченное значение согласованы, Junit Тест пройден ：

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