Oracle преобразовать blob в clob
Для работы с данными большого объема СУБД 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-сегментах данные, свести к минимуму их дублирование, шифровать эти данные. Но эта тема выходит за рамки данного очерка.
Can I convert an oracle BLOB to Base64 CLOB in One go?
I know I can add functions/Stored proc to do the work. Performance aspect is very important,so I am asking if there is a way to overcome the 32K limitation by directly pushing the data into a CLOB.
4 Answers 4
This function got from here should do the job.
Then the update can look like
Note that maybe the function should be optimized with the function DBMS_LOB.APPEND instead of that concatenation operator. Try that if you have performance problems.
Provided that stored procs would despite be a viable alternative for you, here's one possible solution to your problem .
First, let's make that nice base64encode() function of Tim Hall's into a procedure .
The "trick" here is to directly use the persistent LOB locators in calls to procedures/functions. Why "persistent"? Because if you create a function that returns a LOB, then there's a temporary LOB created in background and this means some TEMP disk/memory usage and LOB content copying involved. For large LOBs this may imply a performance hit. In order to satisfy your requirement of making this the most performing possible, you should avoid this TEMP space usage. Hence, for this approach, a stored procedure instead of a function must be used.
Then, of course, the procedure must be fed with persistent LOB locators. You have to do that, again, with a stored procedure, where you e.g. insert an empty LOB (effectively creating a new LOB locator) to a table first, and then supplying that newly created LOB locator to the base64 encoding routine .
Note: Of course, if you base64-encode only small files (the actual size depends on your PGA settings, I guess; a question for a DBA, this is), then the function-based approach may be equally performing than this procedure-based one. Base64-encoding a 200MB file on my laptop took 55 seconds with the function+update approach, 14 seconds with the procedure approach. Not exactly a speed demon, so choose what suits your needs.
Note: I believe this procedure-based approach may be further speeded up by reading the file to inmemory chunks in loop, base64-encoding the chunks to another inmemory chunks and appending them both to the target persistent LOBs. That way you should make the workload even easier by avoiding re-reading the full test.image LOB contents by the base64encode() procedure.
I would split the bounty - but there is no way to do it, so i accepted your detailed answer, Thank you!
You're welcome. No problem about the bounty, writing and then benchmarking this piece of code was a nice mental exercise for me. :-)
Hi! Why pls_integer := 22500? Currently I have a such problem. ORA-06502: PL/SQL: numeric or value error And. How those two procedures are connected?
@Almas, after 4 years I don't remember anymore. It has to do something with UTF8 being potentially padded to 4 bytes. Experiment with different "step" sizes yourself. More specifically, use lower and lower values until you get the stuff working. ORA-6502 can mean anything, I can't help you easily without knowing the whole context.
I solved this same problem at work by using a Java stored procedure. There is no chunking/contatenation of VARCHAR2s involved in such an approach, since the ability to encode/decode base64 is natively built into Java, simply writing an Oracle function that thinly wraps the Java method works well and is high-performance since as soon as you've executed it a few times, the HotSpot JVM compiles the Java proc into low-level code (high performance just like a C stored function). I'll edit this answer later and add the details about that Java code.
But to step back just one step, question why are you storing this data as both a BLOB and base64 encoded (CLOB)? Is it because you have clients that want to consume the data in the latter format? I'd really prefer to only store the BLOB format. One reason why is that the base64 encoded version can be double the size of the original binary BLOB, so storing them both means possibly 3x the storage.
One solution, the one I implemented at work, to create your own Java stored function base64_encode() that encodes binary --> base64 and then use that function to encode base64 on the fly at query time (it's not expensive). From the application/client side, you would query something like SELECT base64_encode(image) FROM test WHERE .
If the application code can't be touched (ie COTS application) or if your developers aren't thrilled about using a function, you could abstract this for them (since you are using 11g+) by using a VIRTUAL (computed) column on the table which contains the computed base64_encode(image) . It would function like a view, in that it wouldn't physically store the encoded CLOBs, but would generate them at query time. To any client, they would not be able to tell they are not reading a physical column. The other benefit is that if you ever update the jpg (BLOB), the virtual CLOB is immediately and automatically updated. If you ever have to insert/update/delete a huge batch of BLOBs, you'd save 66% of the redo/archivelog volume from not having to process all the CLOBs.
Lastly, for performance, make very sure you are using SecureFile LOBs (both for BLOBs and CLOBs). They really are much faster and better in just about every way.
UPDATE - I found my code, at least the version that uses a Java Stored Procedure to do the opposite (converting a base64 encoded CLOB to its binary BLOB version). It would not be that difficult to write the inverse.
Step 1: Create plsql function blob_to_clob. It should return the CLOB .
CREATE OR REPLACE
FUNCTION blob_to_clob(
blob_in IN BLOB)
RETURN CLOB
AS
v_clob CLOB;
v_varchar VARCHAR2(32767);
v_start pls_integer := 1;
v_buffer pls_integer := 32767;
BEGIN
dbms_lob.createtemporary(v_clob, TRUE);
FOR i IN 1..ceil(dbms_lob.getlength(blob_in) / v_buffer)
loop
v_varchar := utl_raw.cast_to_varchar2(dbms_lob.substr(blob_in, v_buffer, v_start));
dbms_lob.writeappend(v_clob, LENGTH(v_varchar), v_varchar);
v_start := v_start + v_buffer;
END loop;
RETURN v_clob;
END blob_to_clob;
Step 2: Call the function and test it .
Many ways to check this funtionality,
- Generate XML data from relational content
- Convert CLOB (XML data) to BLOB
- Convert BLOB to CLOB
- Print the CLOB
DECLARE
l_xml CLOB;
l_clob_to_blob BLOB;
l_blob_to_clob CLOB;
BEGIN
-- Generate XML
SELECT dbms_xmlgen.getxml('select * from emp') xml
INTO l_xml
FROM dual;
-- Convert CLOB to BLOB
l_clob_to_blob := clob_to_blob (l_xml);
-- Convert BLOB to CLOB
l_blob_to_clob := blob_to_clob(l_clob_to_blob);
-- Print the CLOB
print_clob_to_output (l_blob_to_clob);
END;
- Store BLOB in database
- Convert BLOB to CLOB
- Print the CLOB
CREATE TABLE store_blob_clob_files
(
ID NUMBER,
blob_content BLOB,
clob_content CLOB
);
DECLARE
l_blob BLOB;
l_clob_to_blob BLOB;
l_blob_to_clob CLOB;
BEGIN
-- Get BLOB file
SELECT blob_content
INTO l_blob
FROM store_blob_clob_files
WHERE >
-- Convert BLOB to CLOB
l_blob_to_clob := blob_to_clob(l_blob);
UPDATE store_blob_clob_files
SET clob_content = l_blob_to_clob
WHERE >
COMMIT;
-- Print the CLOB
print_clob_to_output (l_blob_to_clob);
END;
могу ли я преобразовать Oracle BLOB в Base64 CLOB за один раз?
Я знаю, что могу добавить функции / сохраненный proc для выполнения работы. Аспект производительности очень важен, поэтому я спрашиваю, есть ли способ преодолеть ограничение 32K, напрямую вставляя данные в CLOB.
при условии, что сохраненные процессы будут, несмотря на жизнеспособную альтернативу для вас, вот одно возможное решение вашей проблемы .
во-первых, давайте сделаем это красиво
эта функция есть отсюда должен делать свою работу.
тогда обновление может выглядеть как
обратите внимание, что, возможно, функция должна быть оптимизирована с помощью функции DBMS_LOB.Добавьте вместо этого оператор конкатенации. Попробуйте это, если у вас есть проблемы с производительностью.
Я решил эту же проблему на работе, используя хранимую процедуру Java. В таком подходе нет чанкинга/контатенации VARCHAR2s, поскольку возможность кодирования / декодирования base64 изначально встроена в Java, просто написание функции Oracle, которая тонко обертывает метод Java, работает хорошо и является высокопроизводительной, так как как только вы выполнили его несколько раз, HotSpot JVM компилирует Java proc в низкоуровневый код (высокая производительность так же, как и хранимая функция C). Я буду редактировать этот ответ позже и добавьте сведения об этом коде Java.
но чтобы отступить всего на один шаг, вопрос, почему вы храните эти данные как BLOB и base64 закодированы (CLOB)? это потому что у вас есть клиенты, которые хотят использовать данные в последнем формате? Я бы предпочел хранить только формат BLOB. Одна из причин заключается в том, что кодированная версия base64 может быть вдвое больше размера исходного двоичного BLOB, поэтому сохранение их обоих означает, возможно, 3x место хранения.
одно решение, которое я реализовал на работе, чтобы создать свою собственную сохраненную функцию Java base64_encode() который кодирует binary --> base64, а затем использует эту функцию для кодирования base64 на лету во время запроса (это не дорого). Со стороны приложения / клиента вы запросите что-то вроде SELECT base64_encode(image) FROM test WHERE .
если код приложения нельзя коснуться (т. е. приложение COTS) или если ваши разработчики не в восторге от использования функции, вы можете абстрагировать это для них (так как вы используете 11g+), используя виртуальный (вычисляемый) столбец в таблице, который содержит вычисляемый base64_encode(image) . Он будет функционировать как представление, поскольку физически не будет хранить закодированные Клобы, а будет генерировать их во время запроса. Для любого клиента они не смогут сказать, что не читают физическую колонку. Другое преимущество заключается в том, что если вы когда-либо обновляете jpg (BLOB), виртуальный CLOB немедленно и автоматически обновляется. Если вам когда-либо нужно вставить/обновить / удалить огромный пакет из BLOBs вы сэкономите 66% объема redo / archivelog от необходимости обрабатывать все CLOBs.
наконец, для производительности убедитесь, что вы используете Securefile LOBs (как для BLOBs, так и для CLOBs). Они действительно намного быстрее и лучше почти во всех отношениях.
обновление - я нашел свой код, по крайней мере, версию, которая использует хранимую процедуру Java, чтобы сделать обратное (преобразование кодированного CLOB base64 в его двоичную версию BLOB). Оно не будет трудно написать обратное.
самый простой способ, который я нашел, который работает со специальными символами (в вашем случае у вас нет этой проблемы), использует dbms_lob.конвертоклоб.
В ходе выполнения программы PL/SQL часто возникает необходимость преобразования данных из одного типа в другой. Преобразование может выполняться двумя способами:
- Неявно — поиск «оптимального варианта» поручается исполнительному ядру PL/SQL;
- Явно — преобразование выполняется вызовом функции или соответствующим оператором PL/SQL.
В этом разделе мы сначала разберемся, как в PL/SQL выполняются неявные преобразования, а затем перейдем к изучению функций и операторов явного преобразования.
Функция CAST
Функция CAST является очень удобным и гибким механизмом преобразования данных. Она преобразует значение любого (или почти любого) встроенного типа данных или коллекции в другой встроенный тип данных или коллекцию, и скорее всего, будет знакома всем программистам с опытом работы на объектно-ориентированных языках.
С помощью функции CAST можно преобразовать неименованное выражение (число, дату, NULL и даже результат подзапроса) или именованную коллекцию (например, вложенную таблицу) в тип данных или именованную коллекцию совместимого типа. Допустимые преобразования между встроенными типами данных показаны на рис. 2. Необходимо соблюдать следующие правила:
- не допускается преобразование типов данных LONG , LONG RAW , любых типов данных LOB и типов, специфических для Oracle;
- обозначению « DATE » на рисунке соответствуют типы данных DATE , TIMESTAMP , TIMESTAMP WITH TIMEZONE , INTERVAL DAY TO SECOND и INTERVAL YEAR TO MONTH ;
- для преобразования именованной коллекции определенного типа в именованную коллекцию другого типа нужно, чтобы элементы обеих коллекций имели одинаковый тип;
Рис. 2. Преобразование встроенных типов данных PL/SQL
- тип UROWID не может быть преобразован в ROWID , если UROWID содержит значение ROWID индекс-таблицы.
Ниже приведен пример использования функции CAST для преобразования скалярных типов данных. Ее вызов может быть включен в SQL-команду:
Также возможен вызов в синтаксисе PL/SQL:
Намного более интересное применение CAST встречается при работе с коллекциями PL/SQL (вложенными таблицами и VARRAY), поскольку эта функция позволяет преобразовывать коллекцию из одного типа в другой. Кроме того, CAST может использоваться для работы (из инструкций SQL) с коллекцией, объявленной как переменная PL/SQL.
Обе темы подробно рассматриваются в главе 12, а следующий пример дает некоторое представление о синтаксисе и возможностях преобразования. Сначала мы создаем два типа вложенных таблиц и одну реляционную таблицу:
Далее пишется программа, которая связывает данные из таблицы favorite_authors с содержимым вложенной таблицы, объявленной и заполненной в другой программе. Рассмотрим следующий блок:
В строках 2 и 3 объявляется локальная вложенная таблица, заполняемая именами нескольких популярных авторов. В строках 7–11 с помощью оператора UNION объединяются строки таблиц favorite_authors и scifi_favorites. Для этого вложенная таблица scifi_favorites (локальная и не видимая для ядра SQL) преобразуется с использованием функции CAST в коллекцию типа names_t. Такое преобразование возможно благодаря совместимости их типов данных. После преобразования вызов команды TABLE сообщает ядру SQL, что вложенная таблица должна интерпретироваться как реляционная. На экран выводятся следующие результаты:
Ограничения неявного преобразования
Как видно из рис. 1, преобразование может выполняться только между определенными типами данных; PL/SQL не может преобразовать произвольный тип данных в любой другой. Более того, при некоторых неявных преобразованиях типов генерируются исключения. Возьмем следующую операцию присваивания:
В PL/SQL нельзя преобразовать строку «abc» в число, поэтому при выполнении приведенного кода инициируется исключение VALUE_ERROR . Вы сами должны позаботиться о том, чтобы значение, для которого PL/SQL выполняет преобразование типов, могло быть конвертировано без ошибок.
Функция ROWIDTOCHAR
Преобразует двоичное значение типа ROWID в строку типа VARCHAR2 . Синтаксис функции ROWIDTOCHAR :
Возвращаемая функцией строка имеет следующий формат:
где ОООООО — номер объекта данных, ФФФ — относительный номер файла базы данных, ББББББ — номер блока в файле, а ССС — номер строки в блоке PL/SQL. Все четыре компонента задаются в формате Base64. Пример:
Функция RAWTOHEX
Преобразует значение типа RAW в шестнадцатеричную строку типа VARCHAR2 . Синтаксис функции RAWTOHEX :
Функция RAWTOHEX всегда возвращает строку переменной длины, хотя обратная ей перегруженная функция HEXTORAW поддерживает оба типа строк.
Функция HEXTORAW
Преобразует шестнадцатеричную строку типа CHAR или VARCHAR2 в значение типа RAW . Синтаксис функции HEXTORAW :
Явное преобразование типов
Oracle предоставляет обширный набор функций и операторов, с помощью которых можно выполнить преобразование типов данных в SQL и PL/SQL. Их полный список приведен в табл. 1. Большая часть функций описывается в других главах книги (для них в последнем столбце указан номер главы).
Таблица 1. Функции преобразования типов в PL/SQL
Функция | Выполняемое преобразование |
ASCIISTR | Строку из любого набора символов в строку ASCII из набора символов базы данных |
CAST | Одно значение встроенного типа данных или коллекции в другой встроенный тип данных или коллекцию. Этот способ может использоваться вместо традиционных функций (таких, как TO_DATE) |
CHARTOROWID | Строку в значение типа ROWID |
CONVERT | Строку из одного набора символов в другой |
FROM_TZ | В значение типа TIMESTAMP добавляет информацию о часовом поясе, преобразуя его тем самым в значение типа TIMESTAMP WITH TIME ZONE |
HEXTORAW | Значение из шестнадцатеричной системы в значение типа RAW |
MULTISET | Таблицу базы данных в коллекцию |
NUMTODSINTERVAL | Число (или числовое выражение) в литерал INTERVAL DAY TO SECOND |
NUMTOYMINTERVAL | Число (или числовое выражение) в литерал INTERVAL YEAR TO MONTH |
RAWTOHEX, RAWTONHEX | Значение типа RAW в шестнадцатеричный формат |
REFTOHEX | Значение типа REF в символьную строку, содержащую его шестнадцатеричное представление |
ROWIDTOCHAR, ROWIDTONCHAR | Двоичное значение типа ROWID в символьную строку |
TABLE | Коллекцию в таблицу базы данных; по своему действию обратна функции MULTISET |
THE | Значение столбца в строку виртуальной таблицы базы данных |
TO_BINARY_FLOAT | Число или строку в BINARY_FLOAT |
TO_BINARY_DOUBLE | Число или строку в BINARY_DOUBLE |
TO_CHAR, TO_NCHAR (числовая версия) | Число в строку (VARCHAR2 или NVARCHAR2 соответственно) |
TO_CHAR, TO_NCHAR (версия для дат) | Дату в строку |
TO_CHAR, TO_NCHAR (символьная версия) | Данные из набора символов базы данных в набор символов национального языка |
TO_BLOB | Значение типа RAW в BLOB |
TO_CLOB, TO_NCLOB | Значение типа VARCHAR2, NVARCHAR2 или NCLOB в CLOB (либо NCLOB) |
TO_DATE | Строку в дату |
TO_DSINTERVAL | Символьную строку типа CHAR, VARCHAR2, NCHAR или NVARCHAR2 в тип INTERVAL DAY TO SECOND |
TO_LOB | Значение типа LONG в LOB |
TO_MULTI_BYTE | Однобайтовые символы исходной строки в их многобайтовые эквиваленты (если это возможно) |
TO_NUMBER | Строку или число (например, BINARY_FLOAT) в NUMBER |
TO_RAW | Значение типа BLOB в RAW |
TO_SINGLE_BYTE | Многобайтовые символы исходной строки в соответствующие однобайтовые символы |
TO_TIMESTAMP | Символьную строку в значение типа TIMESTAMP |
TO_TIMESTAMP_TZ | Символьную строку в значение типа TO_TIMESTAMP_TZ |
TO_YMINTERVAL | Символьную строку типа CHAR, VARCHAR2, NCHAR или NVARCHAR2 в значение типа INTERVAL YEAR TO MONTH |
TRANSLATE . USING | Текст в набор символов, заданный для преобразования набора символов базы данных в национальный набор символов |
UNISTR | Строку произвольного набора символов в Юникод |
Функция CONVERT
Преобразует строку из одного набора символов в другой. Синтаксис функции:
Третий аргумент старый_набор_символов не является обязательным. Если он не задан, применяется набор символов, используемый в базе данных по умолчанию.
Функция CONVERT не переводит слова или фразы с одного языка на другой, а заменяет буквы или символы одного набора символов буквами или символами другого.
Недостатки неявного преобразования
Неявное преобразование типов имеет ряд недостатков.
- PL/SQL относится к языкам со статической типизацией. Неявные преобразования означают потерю некоторых преимуществ статической типизации — таких, как ясность и надежность кода.
- Каждое неявное преобразование означает частичную потерю контроля над программой. Программист не выполняет его самостоятельно и никак им не управляет, а лишь предполагает, что оно будет выполнено и даст желаемый эффект. В этом есть элемент неопределенности — если компания Oracle изменит способ или условие выполнения преобразований, это может отразиться на программе.
- Неявное преобразование типов в PL/SQL зависит от контекста. Оно может работать в одной программе и не работать в другой, хотя на первый взгляд код кажется одинаковым. Кроме того, результат преобразования типов не всегда соответствует ожиданиями программиста.
- Программу легче читать и понять, если данные в ней преобразуются явно, поскольку при этом фиксируются различия между типами данных в разных таблицах или в таблице и коде. Исключая из программы скрытые действия, вы устраняете и потенциальную возможность ошибок.
Таким образом, в SQL и PL/SQL рекомендуется избегать неявного преобразования типов. Лучше пользоваться функциями, которые выполняют явное преобразование — это гарантирует, что результат преобразования будет точно соответствовать вашим ожиданиям.
Функция CHARTOROWID
Преобразует строку типа CHAR или VARCHAR2 в значение типа ROWID . Синтаксис функции:
Для успешного преобразования функцией CHARTOROWID строка должна состоять из 18 символов в формате:
где ОООООО — номер объекта данных, ФФФ — относительный номер файла базы данных, ББББББ — номер блока в файле, а ССС — номер строки в блоке. Все четыре компонента задаются в формате Base64. Если исходная строка не соответствует этому формату, инициируется исключение VALUE_ERROR .
Неявное преобразование типов
Обнаружив необходимость преобразования, PL/SQL пытается привести значение к нужному типу. Вероятно, вас удивит, насколько часто это делается. На рис. 1 показано, какие виды неявного преобразования типов выполняются PL/SQL.
Рис. 1. Неявные преобразования типов, выполняемые PL/SQL
Неявное преобразование типов осуществляется при задании в операторе или выражении литерального значения в правильном внутреннем формате, которое PL/SQL преобразует по мере необходимости. В следующем примере PL/SQL преобразует литеральную строку «125» в числовое значение 125 и присваивает его числовой переменной:
Неявное преобразование типов выполняется также при передаче программе параметров не того формата, который в ней используется. В следующей процедуре таким параметром является дата. Вызывая эту процедуру, вы передаете ей строку в формате ДД-МММ-ГГ, которая автоматически преобразуется в дату:
Неявное преобразование строки в дату выполняется в соответствии со спецификацией NLS_DATE_FORMAT . Проблема заключается в том, что в случае изменения NLS_DATE_FORMAT работа программы будет нарушена.
Читайте также: