Неявное преобразование типов в oracle

Обновлено: 07.07.2024

Строчные функции конвертации типа данных разработаны для изменения типа данных столбца, выражения или литерала. Наиболее часто используемые функции конвертации это TO_CHAR, TO_NUMBER и TO_DATE. TO_CHAR преобразует числа и даты в символьные данные, когда TO_NUMBER и TO_DATE преобразует символьные данные соответственно в число и дату.

Функции конвертации

Oracle позволяет определять столбцы с типами данных ANSI, DB2 и SQL/DS. Эти типы преобразуется к типам данных Oracle. У каждого столбца определяется тип данных который ограничивает природу данных которые могут храниться в этом столбце. Столбец NUMBER не может хранить символьную информацию. Столбец DATE не может хранить случайные символы или числа. VARCHAR2 может хранить символьные эквиваленты чисел и дат.

Если функция у которой входной параметр является строкой получает на вход число, Oracle автоматически конвертирует его в символьный эквивалент. Если функция с входными параметрами типа данных число или дата получает на вход строку, то должны соблюдаться определённые условия для автоматической конвертации. Несмотря на то что автоматическая неявная конвертация доступна, гораздо более понятно и надёжно явно сконвертировать один тип данных в другой перед вызовом строчной функции.

Неявная конвертация типов

Значения, которые не соответствуют типам данных параметров функции неявно конвертируется перед выполнением если это возможно. Оба типа данных VARCHAR2 и CHAR используются как символьные типы данных. Символьные типы данных достаточно гибкие для хранения практически любой информации. Таким образом, ДАТА и ЧИСЛО можно легко преобразовать в их символьный эквивалент. Такая конвертация известна как преобразования число в строку и дата в строку. Рассмотрим следующие запросы

Query 1: select length(1234567890) from dual

Query 2: select length(SYSDATE) from dual

Оба запроса используют функцию LENGTH у которой входной параметр определён как строка. Число 1234567890 в запросе один неявно конвертируется в строку ‘1234567890’ перед вычисление функции LENGTH и результат функции будет 10. Запрос номер два вычисляет функцию SYSDATE предположим 7 апреля 2008 года. Результат преобразуется в строку ’07-APR-08’ и результат выполнения функции LENGTH будет число 9.

Обычно не принято допускать неявную конвертацию строк в числа, так как единственная ситуация, когда это возможно, это если строка представляет собой валидное число. Строка ‘11’ будет неявно преобразована в число 11, но строка ’11.123.345’ не будет, как показано в следующих примерах

Запросы 3 и 4 неявно преобразовали строки ‘11’ и ’11.123’ в числа 11 и 11.123 соответственно, перед вызовом функции MOD которая в свою очередь вернула результат 1 и 1.123. Запрос 5 вернул ошибку ‘ORA-1722: invalid number’, когда Oracle попытался неявно преобразовать строку в число, так как ‘11.123.456’ не является корректным числом. Запрос 6 также вернул ошибку так как символ доллара не может бять неявно преобразован в число.

Неявная конвертация строки в дату возможна, когда строка удовлетворяет следующим шаблонам: [D|DD] separator1 [MON|MONTH] separator2][R|RR|YY|YYYY], где D и DD это день MON первые три буквы месяца, MONTH – полное название месяца. R и RR YY и YYYY отображают одну, две и четыре цифры года соответственно. Параметром separator1 и separator2 может быть практически любой спец символ, включая сюда пробел, табуляцию, знаки пунктуации и т.д. Таблица 10-2 показывает неявную конвертацию строки в дату, включая вызов функций работы с датами и результаты. Эти результаты предполагают, что система использует американскую локаль.

TIP Несмотря на то что неявная конвертация типов доступна, лучше использовать явный вызов функции конвертации перед использование данным в других функциях. Конвертация строки в число и дату использует маску форматирования.

Явная конвертация типов данных

Функции, которые конвертирует значение из одного типа данных в другой известны как явное преобразование типов. Они возвращают значение, которое будет гарантировано нужного типа данных и являются надёжным методом конвертации данных.

Число и дату можно явно конвертировать в строку используя функцию TO_CHAR. Строку можно явно конвертировать в число используя функцию TO_NUMBER. Функция TO_DATE используется для конвертации строку в DATE. Маски форматирования Oracle позволяют гибко контролировать процесс конвертации строки в число или дату.

Использование функций TO_CHAR, TO_NUMBER и TO_DATE

Использование функций конвертации

Во многих ситуациях возникает потребность в использовании функций конвертации, начиная от форматирования даты в отчётах и до проверки численных значений в символьных столбцах перед стартом арифметических вычислений.

Таблица 10-3 показывает синтаксис строчных функций конвертации данных

Необязательный параметр поддержки национального формата (nls_parameters) полезен для указания языка и форматирования, в котором названия дней, месяцев и разделители разрядов, целой и дробной части заранее предопределены. На рисунке 10-2 отображено представление NLS_SESSION_PARAMETERS которое содержит значения параментов NLS для текущей сессии. По умолчанию значение NLS_CURRENCY – знак доллара, но это можно изменить на уровне сессии. Например, для изменения символа валюты на строку ‘GBP’ можно выполнить запрос

ALTER SESSION SET NLS_CURRENCY=’GBP’

Рисунок 10-2 – Представление NLS_SESSION_PARAMETERS

Конвертация числа в строку используя функцию TO_CHAR

Функция TO_CHAR возвращает значение типа VARCHAR2. Когда входных параметром является число то доступны некоторые параметры форматирования. Синтаксис команды TO_CHAR(num, [format], [nls_parameter]). Параметр num обязательный и должен быть числом. Необязательный параметр format можно использовать для указания информации о форматировании, такой как длина, символ валюты, позиция разделителя дробной и целой части и разделитель разрядов (три разряда) и должен быть заключен в одинарные кавычки. Доступны различные опции форматирования и часть из них представлена в таблице 10-4. Рассмотрим два запроса

Tip Конвертация чисел в строки надёжный способ убедиться что функция и SQL запрос в целом, который ожидает символьного значения, не вернёт ошибку когда встретится число. Конвертация чисел в строки часто используется для форматирования значений для отчетов. Маска форматирования поддерживает символ валюты, разделитель порядков и разделитель целой и дробной части, что часто используется при отображении финансовой информации.

Конвертация даты в строку используя функцию TO_CHAR

Вы можете использовать преимущества модели масок форматирования при конвертации ДАТЫ в практически любой вариант отображения даты как символьного значения используя функцию TO_CHAR. Синтаксис функции TO_CHAR(date1, [format], [nls_parameter]).

Только параметр date1 обязательный; тогда он должен быть значением, которое может неявно преобразоваться в строку. Необязательный параметр format регистрозависимый и должен быть обрамлён одинарными кавычками. Маска форматирования указывает какие лементы даты должы быть выбраны и как отображать названия элементов даты: полные названия или аббревиатуры. Названия дней и месяцев автоматически разделяются пробелом. Такое поведение можно изменить, используя параметр маски fill mode (fm). Указав в начале маски параметр fm, вы укажете Oracle о необходимости убрать все пробелы. Доступно много опций для маски форматирования, часть из которых отображена в таблице 10-5. Рассмотрим три запроса

Если текущая системная дата 3 января 2009 года и по умолчанию формат отображения DD/MON/RR тогда запрос один вернёт строку ‘03/JAN/09 is todays date’. Во втором запросе обратите внимание на две детали: во-первых, только месяц выбирается из даты, и во-вторых так как маска форматирования регистрозавсимая и в запросе используется ‘Month’, то запрос вернёт ‘January is a special time’. Нет нужды добавлять пробел в начале литерала, так как функция TO_CHAR автоматически добавит пробел к названию месяца. Если бы маска во втором запросе была ‘MONTH’ то запрос вернул бы ‘JANUARY is a special time’. Параметр fm в третьем запросе препятствует добавлению пробелов и результатом будет ‘Januaryis a special time’. В таблице 10-5 предполагается что обрабатывается дата 2 июня 1975 года и текущий год 2009.

Параметры форматирования, связанные с неделей, кварталом, веком и другими более редко использующимися элементами даты показаны на рисунке 10-7. Столбец результата предполагает, что функция использовалась для работы с датой 24 сентября 1000 года, с маской форматирования указанной в столбец format element.

Компонент время в типе данных дата выбирается, используя модели форматирования в таблице 10-7. Результат рассчитывается функцией TO_CHAR используя дату 27 июня 2010 года время 21:35:13 с маской форматирования указанной в столбце format element.

Некоторые различные элементы, которые можно использовать в форматировании даты и времени перечислены в таблице 10-8. Знаки пунктуации используются для разделения элементов форматирования. Три типа суффиксов существуют для форматирования элементов. Более того, символьные литералы могут быть включены в модель форматирования если они заключены в двойные кавычки. Результаты в таблице 10-8 получены используя функцию TO_CHAR для даты 12 сентября 2008 года 14:31 с маской форматирования указанной в соответствующем столбце.

Рисунок 10-3 – Запрос в таблицу JOB_HISTORY

ORDER BY END_DATE;

Несмотря на то что компонент «век» не отображается по умолчанию, он хранится в базе данных и доступен для запроса. Маска форматирования DD-MON-RR используется по умолчанию для ввода значений и отображения. Когда значение добавляется или изменяется если явно неуказан век, то используется век из функции SYSDATE. Формат RR отличается от формата YY и так как RR также использует значение столетия. Влияние значения столетия на формат RR легче понять если рассмотреть следующие принципы

Если две последние цифры текущего года между 0 и 49, а в указанном значении даты две последние цифры года между 50 и 99 то используется предыдущий век. Предположим, что текущая дата 2 июня 2007 года. Значение века для даты 24-JUNE-94 года будет 20
Если две цифры текущего года между 50-99 и указанной даты также между 55 и 99, то возвращается текущий век. Преположим что текущая дата 2 июня 1975 года. Тогда значение века для 24-JUL-94 будет 20.
Если две цифры текущей даты между 50 и 99, а в укащанной дате год между 0 и 49 – то считается следующий век. Предположим, что текущая дата 2 июня 1975 года, тогда для значения 24-JUL-07 значение века будет 21.

Конвертация строки в дату используя функцию TO_DATE

Функция TO_DATE возвращает значение типа данных DATE. Строка, конвертируемая в дату может содержать все или часть компонентов, составляющих тип DATE. Когда строка содержащая только часть компонентов даты преобразуется в дату, Oracle использует значение по умолчанию для составления валидного значения типа DATE. Части строки сопоставляются с элементами даты используя маску (или модель) форматирования. Синтаксис функции TO_DATE(string1, [format], [nls_parameter]).

Только параметра string1 обязателен, и eсли маска форматирования не указана, string1 должна быть в формат неявно конвертируемом в дату. Необязательный параметр format используется практически всегда и должен быть заключён в одинарные кавычки. Маска форматирования идентича перечисленным в таблицах 10-5, 10-6, и 10-7. У функции TO_DATE есть модификатор fx, которые используется подобно параметру fm функции TO_CHAR. Параметр fx требует обязательного совпадения строки и маски форматирования. Если строка не совпадает с маской – возвращается ошибка. Рассмотрим несколько примеров

Конвертация строки в число используя функцию TO_NUMBER

Функция TO_NUMBER возвращает значение типа данных NUMBER. Исходная строка должна быть составлена таким образом, чтобы все несовместимые символы отсутствовали или были указаны в соответствующей маске форматирования. Синтаксис функции TO_NUMBER(string1, [format], [nls_parameter]). Только string1 является обязательным параметром, и если не указан параметр format то значение должно быть таким, чтобы была возможность неявно сконвертировать его в число. Маски форматирования идентичны перечисленным в таблице 10-4. Рассмотрим запросы

Запрос один не может неявно преобразовать строку, так как она содержит знак валюты и разделители, которые явно неуказаны в маске, поэтому возвращается ошибка ORA-1722: invalid number. Запрос два находит символ валюты, запятой и точки в маске форматирования и несмотря на то что длина маски больше чем чем исходное значение в строке, возвращается число 1000.55

Функция TO_NUMBER конвертирует значение строки в число. Если вы используете число длиннее чем маска, возвращается ошибка. Если вы конвертируете число используя более длинную маску – возвращается значение исходной длины. Не путайте TO_NUMBER с TO_CHAR. Например TO_NUMBER(‘123.45’,’999.9’) вернёт ошибку, когда TO_CHAR(123.45,’999.9’) вернёт 123.6

Начну с того, что я полагаю, что неявное преобразование - это плохо. Еще хуже. Зло. Нет-нет, серьезно! Теперь позвольте мне дать определения преобразований обоих типов и объяснять, почему я так считаю.

Преобразование

Под преобразованием я понимаю приведение хранимых данных из одного типа к другому типу данных. Например, символьное поле "1" может быть преобразовано к числовому 1. Символьное поле может быть преобразовано к полю даты, если оно правильно отформатировано. Форматирование преобразования может управляться командой и маской формата (явное преобразование), или Вы можете положиться на Oracle и/или инструмент третьих фирм, который сделал бы это за Вас (неявное преобразование).

Неявное преобразование

Примером неявного преобразования может быть:

Если Вы используете схему scott и оригинальную таблицу emp, то должны получить две строки. Возможно. Если Вы используете sql*plus или другой инструмент, который не устанавливает маску формата даты по умолчанию, и окажется, что маска формата базы данных по умолчанию совпадает с той, что я ввел, или Вы используете инструмент, который установил маску формата в соответствии с той, что я ввел.

Явное преобразование

Если Вы выполните этот оператор, то должны получить две строки, независимо от того, какой инструмент или какие маски формата по умолчанию установлены.

Отметьте, что различие состоит в том, что я ЯВНО выполнил преобразование из символьного представления к дате с помощью функции TO_DATE, в которой я указал правильную маску формата.

Явное преобразование требует дополнительного набора. Это верно.

Так что использовать?

Всегда, всегда, всегда используйте явное преобразование. Помимо того, что это намного проще читать и поддерживать, явное преобразование гарантирует, что, если формат по умолчанию когда-нибудь будет изменен, ваш код продолжит работать.

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

Всегда, всегда, всегда используйте явное преобразование.

Что говорит Oracle?

Это выдержка непосредственно из справочного руководства к Oracle 10g:

Преобразование данных

Как правило, выражение не может содержать значения данных разных типов. Например, выражение не может перемножить 5 и 10, а затем добавить 'JAMES'. Однако Oracle поддерживает как неявное, так и явное преобразование значений из одного типа данных в другой.

Неявное и явное преобразование данных

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

? Операторы SQL легче понять, когда Вы используете функции явного преобразования типа данных.

? Неявное преобразование типа данных может оказать отрицательное влияние на производительность, особенно если тип данных столбца преобразуется к типу данных константы, а не наоборот.

? Неявное преобразование зависит от контекста, в котором оно происходит и, возможно, не будет работать одинаково в каждом случае. Например, неявное преобразование значения типа данных VARCHAR2 может возвратить неожиданный год в зависимости от значения параметра NLS_DATE_FORMAT.

? Алгоритмы для неявного преобразования подвержены изменениям при обновлении версий продуктов Oracle. Поведение явных преобразований более предсказуемо.

Я полагаю, что эти аргументы применимы не только к Oracle. Использование явных преобразований следует отнести к лучшим методам для любой базы данных и любого языка программирования.

Если Вы обнаружите случай, когда неявное преобразование окажется лучше, чем явное, пожалуйста, напишите комментарий к этому посту и объясните. Мне ничего подобное не приходит в голову.

Примерно полтора года назад я сдал экзамены на OCP Advanced PL/SQL Developer, далее специфика работы несколько изменилась, и после стандартного производственного использования Oracle я занимался разработкой архитектуры двухуровневой клиент-серверной системы на основе Oracle для нужд компьютерной лингвистики. Далее был этап развития системы и решения наукоемких задач на ее основе, пришлось заниматься использованием иерархических запросов в решении нестандартных задач и другими специфическими вещами. Результатом углубления в специфику стало некоторое «проседание» базы, а значит, наступило время снова просмотреть материалы, использовавшиеся для подготовки к экзаменам.

Ниже будет приведено несколько нестандартных примеров использования sql-запросов. Такие примеры обычно приходят на ум во время просмотра конспекта, проверяются, обсуждаются с другими специалистами и забываются. В этот раз мне захотелось сохранить некоторые из них в sql-файле, позже стала понятна необходимость в комментариях к каждому запросу. Так и появилась эта заметка.

Несколько слов о специфике оператора order by

Как вы думаете, будет ли ошибка в результате выполнения данного запроса?

Таким образом, мы отсортировали таблицу dual по выражению «3X», что бессмысленно, однако в качестве выражения для сортировки можно, например, использовать выражение с функцией substr. Важна сама возможность использования выражений.

Как мы знаем, таблица dual содержит один столбец, вернет ли ошибку такой запрос:

В первом случае мы имеем дело с позиционным указанием колонок – т.е. ссылкой на колонку №2, которой не существует, соответственно, получим ошибку.

Во втором случае мы имеем дело с выражением, т.е. это уже не номер колонки, а сортировка по числу 2, аналогичная сортировке по строке «3X» в запросе №1. Поэтому ошибки не будет.

Посмотрите на следующий пример. Какой порядок строк мы получим в результате выполнения этого запроса? Будет ли ошибка?

Union all не сортирует строки объединяемых множеств (в отличии от union), т.е. без order by мы получим строки в указанном в запросе порядке (union all гарантирует сохранение исходного порядка строк). Теперь настала очередь order by, главный вопрос, что такое «3» в этом случае? Поскольку у нас использованы двойные кавычки ", а не одинарные ', то «3» – это алиас колонки. Как известно, операции с множествами требуют использования сходных типов данных, а имена колонок берутся из первого запроса, поскольку мы явно не указали имя первой колонки, то, по умолчанию, она получила имя выражения, т.е. «3». Работа с такими алиасами показана, например, в запросе №5 (главное не забывать про аппер-кейс).

Сортировка по умолчанию – всегда asc, т.е. результаты запроса №4 сортируются по первой колонке по возрастанию. Результат: строка «2, Х», потом «3, Х».

Повторим эксперимент из запроса №3 на множествах. Каким будет результат запроса?

Поэтому запрос №6 вернет ошибку.

Использование not in

Наверняка, большинство людей, прошедших различные курсы, помнят, что следует избегать оператора not in, а сходную функциональность можно получить, используя операторы in либо exists. Причина такого негативного отношения к not in кроется в специфике его работы с null-значениями.

Определите результат выполнения следующего запроса:

Для начала рассмотрим такой запрос:

Ничего необычного в запросе №2 нет: подзапрос возвращает множество из двух строк со значениями «2» и «null», условие where принимает значение true, весь запрос №2 возвращает 1 строку – стандартное поведение.

null AND false = false
false AND null = false
null AND true = null
true AND null = null
null OR true = true
true OR null = true
null OR false = null
false OR null = null

Таким образом, условие where в запросе №1 преобразуется в null, если хотя бы один операнд null, поэтому весь запрос №1 приблизительно эквивалентен следующему запросу:

Очевидно, что запрос №3 не вернет ни одной строки, соответственно, запрос №1 также не вернет ни одной строки.

Неявное преобразование типов

Тема явного и неявного преобразования типов очень обширна, поэтому, не пытаясь охватить ее в целом, я хотел бы рассмотреть лишь один пример. Пускай сегодня 10.09.11 10:00:00 и Оракл сконфигурирован так, что формат DD.MM.RR распознается по умолчанию, какой из запросов вернет одну строку?

Ответ – оба. Почему так и как это, вообще, возможно? Ответ лежит в механизме неявного преобразования типов.

Рассмотрим запрос №1: в where мы сравниваем дату со строкой, в этом случае Оракл пытается преобразовать строку в дату, если формат строки соответствует одному из форматов даты по умолчанию (формат даты по умолчанию для сессии можно посмотреть в параметре NLS_DATE_FORMAT, выполнив запрос
select * from nls_session_parameters). Если формат строки не отвечает формату даты по умолчанию, то получим ошибку. В нашем случае форматы соответствуют и строка '10.09.11' преобразуется в дату 10.09.11 00:00:00, поскольку sysdate = 10.09.11 10:00:00, то запрос №1 вернет 1 строку.

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

Неявно — поиск «оптимального варианта» поручается исполнительному ядру PL/SQL;
Явно — преобразование выполняется вызовом функции или соответствующим оператором PL/SQL.

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

Неявное преобразование типов

Обнаружив необходимость преобразования, PL/SQL пытается привести значение к нужному типу. Вероятно, вас удивит, насколько часто это делается. На рис. 1 показано, какие виды неявного преобразования типов выполняются PL/SQL.

Рис. 1. Неявные преобразования типов, выполняемые PL/SQL

Неявное преобразование типов осуществляется при задании в операторе или выражении литерального значения в правильном внутреннем формате, которое PL/SQL преобразует по мере необходимости. В следующем примере PL/SQL преобразует литеральную строку «125» в числовое значение 125 и присваивает его числовой переменной:

Неявное преобразование типов выполняется также при передаче программе параметров не того формата, который в ней используется. В следующей процедуре таким параметром является дата. Вызывая эту процедуру, вы передаете ей строку в формате ДД-МММ-ГГ, которая автоматически преобразуется в дату:

Неявное преобразование строки в дату выполняется в соответствии со спецификацией NLS_DATE_FORMAT . Проблема заключается в том, что в случае изменения NLS_DATE_FORMAT работа программы будет нарушена.

Ограничения неявного преобразования

Как видно из рис. 1, преобразование может выполняться только между определенными типами данных; PL/SQL не может преобразовать произвольный тип данных в любой другой. Более того, при некоторых неявных преобразованиях типов генерируются исключения. Возьмем следующую операцию присваивания:

В PL/SQL нельзя преобразовать строку «abc» в число, поэтому при выполнении приведенного кода инициируется исключение VALUE_ERROR . Вы сами должны позаботиться о том, чтобы значение, для которого PL/SQL выполняет преобразование типов, могло быть конвертировано без ошибок.

Недостатки неявного преобразования

Неявное преобразование типов имеет ряд недостатков.

PL/SQL относится к языкам со статической типизацией. Неявные преобразования означают потерю некоторых преимуществ статической типизации — таких, как ясность и надежность кода.
Каждое неявное преобразование означает частичную потерю контроля над программой. Программист не выполняет его самостоятельно и никак им не управляет, а лишь предполагает, что оно будет выполнено и даст желаемый эффект. В этом есть элемент неопределенности — если компания Oracle изменит способ или условие выполнения преобразований, это может отразиться на программе.
Неявное преобразование типов в PL/SQL зависит от контекста. Оно может работать в одной программе и не работать в другой, хотя на первый взгляд код кажется одинаковым. Кроме того, результат преобразования типов не всегда соответствует ожиданиями программиста.
Программу легче читать и понять, если данные в ней преобразуются явно, поскольку при этом фиксируются различия между типами данных в разных таблицах или в таблице и коде. Исключая из программы скрытые действия, вы устраняете и потенциальную возможность ошибок.

Таким образом, в SQL и PL/SQL рекомендуется избегать неявного преобразования типов. Лучше пользоваться функциями, которые выполняют явное преобразование — это гарантирует, что результат преобразования будет точно соответствовать вашим ожиданиям.

Явное преобразование типов

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	Строку произвольного набора символов в Юникод

Функция CHARTOROWID

Преобразует строку типа CHAR или VARCHAR2 в значение типа ROWID . Синтаксис функции:

Для успешного преобразования функцией CHARTOROWID строка должна состоять из 18 символов в формате:

где ОООООО — номер объекта данных, ФФФ — относительный номер файла базы данных, ББББББ — номер блока в файле, а ССС — номер строки в блоке. Все четыре компонента задаются в формате Base64. Если исходная строка не соответствует этому формату, инициируется исключение VALUE_ERROR .

Функция 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, что вложенная таблица должна интерпретироваться как реляционная. На экран выводятся следующие результаты:

Функция CONVERT

Преобразует строку из одного набора символов в другой. Синтаксис функции:

Третий аргумент старый_набор_символов не является обязательным. Если он не задан, применяется набор символов, используемый в базе данных по умолчанию.

Функция CONVERT не переводит слова или фразы с одного языка на другой, а заменяет буквы или символы одного набора символов буквами или символами другого.

Функция HEXTORAW

Преобразует шестнадцатеричную строку типа CHAR или VARCHAR2 в значение типа RAW . Синтаксис функции HEXTORAW :

Функция RAWTOHEX

Преобразует значение типа RAW в шестнадцатеричную строку типа VARCHAR2 . Синтаксис функции RAWTOHEX :

Функция RAWTOHEX всегда возвращает строку переменной длины, хотя обратная ей перегруженная функция HEXTORAW поддерживает оба типа строк.

Функция ROWIDTOCHAR

Преобразует двоичное значение типа ROWID в строку типа VARCHAR2 . Синтаксис функции ROWIDTOCHAR :

Возвращаемая функцией строка имеет следующий формат:

где ОООООО — номер объекта данных, ФФФ — относительный номер файла базы данных, ББББББ — номер блока в файле, а ССС — номер строки в блоке PL/SQL. Все четыре компонента задаются в формате Base64. Пример:

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