В ходе выполнения программы PL/SQL часто возникает необходимость преобразования данных из одного типа в другой. Преобразование может выполняться двумя способами:
- Неявно — поиск «оптимального варианта» поручается исполнительному ядру PL/SQL;
- Явно — преобразование выполняется вызовом функции или соответствующим оператором PL/SQL.
В этом разделе мы сначала разберемся, как в PL/SQL выполняются неявные преобразования, а затем перейдем к изучению функций и операторов явного преобразования.
Неявное преобразование типов
Обнаружив необходимость преобразования, PL/SQL пытается привести значение к нужному типу. Вероятно, вас удивит, насколько часто это делается. На рис. 1 показано, какие виды неявного преобразования типов выполняются PL/SQL.
Рис. 1. Неявные преобразования типов, выполняемые PL/SQL
Неявное преобразование типов осуществляется при задании в операторе или выражении литерального значения в правильном внутреннем формате, которое PL/SQL преобразует по мере необходимости. В следующем примере PL/SQL преобразует литеральную строку «125» в числовое значение 125 и присваивает его числовой переменной:
DECLARE a_number NUMBER; BEGIN a_number := '125'; END;
Неявное преобразование типов выполняется также при передаче программе параметров не того формата, который в ней используется. В следующей процедуре таким параметром является дата. Вызывая эту процедуру, вы передаете ей строку в формате ДД-МММ-ГГ, которая автоматически преобразуется в дату:
PROCEDURE change_hiredate (emp_id_in IN INTEGER, hiredate_in IN DATE) change_hiredate (1004, '12-DEC-94');
Неявное преобразование строки в дату выполняется в соответствии со спецификацией NLS_DATE_FORMAT
. Проблема заключается в том, что в случае изменения NLS_DATE_FORMAT
работа программы будет нарушена.
Ограничения неявного преобразования
Как видно из рис. 1, преобразование может выполняться только между определенными типами данных; PL/SQL не может преобразовать произвольный тип данных в любой другой. Более того, при некоторых неявных преобразованиях типов генерируются исключения. Возьмем следующую операцию присваивания:
DECLARE a_number NUMBER; BEGIN a_number := 'abc'; END;
В 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
. Синтаксис функции:
FUNCTION CHARTOROWID (исходная_строка IN CHAR) RETURN ROWID FUNCTION CHARTOROWID (исходная_строка IN VARCHAR2) RETURN 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-команду:
SELECT employee_id, cast (hire_date AS VARCHAR2 (30)) FROM employee;
Также возможен вызов в синтаксисе PL/SQL:
DECLARE hd_display VARCHAR2 (30); BEGIN hd_display := CAST (SYSDATE AS VARCHAR2); END;
Намного более интересное применение CAST встречается при работе с коллекциями PL/SQL (вложенными таблицами и VARRAY), поскольку эта функция позволяет преобразовывать коллекцию из одного типа в другой. Кроме того, CAST может использоваться для работы (из инструкций SQL) с коллекцией, объявленной как переменная PL/SQL.
Обе темы подробно рассматриваются в главе 12, а следующий пример дает некоторое представление о синтаксисе и возможностях преобразования. Сначала мы создаем два типа вложенных таблиц и одну реляционную таблицу:
CREATE TYPE names_t AS TABLE OF VARCHAR2 (100); CREATE TYPE authors_t AS TABLE OF VARCHAR2 (100); CREATE TABLE favorite_authors (name VARCHAR2(200))
Далее пишется программа, которая связывает данные из таблицы favorite_authors с содержимым вложенной таблицы, объявленной и заполненной в другой программе. Рассмотрим следующий блок:
/* Файл в Сети: cast.sql */ 1 DECLARE 2 scifi_favorites authors_t 3 := authors_t ('Sheri S. Tepper', 'Orson Scott Card', 'Gene Wolfe'); 4 BEGIN 5 DBMS_OUTPUT.put_line ('I recommend that you read books by:'); 6 7 FOR rec IN (SELECT column_value favs 8 FROM TABLE (CAST (scifi_favorites AS names_t)) 9 UNION 10 SELECT NAME 11 FROM favorite_authors) 12 LOOP 13 DBMS_OUTPUT.put_line (rec.favs); 14 END LOOP; 15 END;
В строках 2 и 3 объявляется локальная вложенная таблица, заполняемая именами нескольких популярных авторов. В строках 7–11 с помощью оператора UNION объединяются строки таблиц favorite_authors и scifi_favorites. Для этого вложенная таблица scifi_favorites (локальная и не видимая для ядра SQL) преобразуется с использованием функции CAST в коллекцию типа names_t. Такое преобразование возможно благодаря совместимости их типов данных. После преобразования вызов команды TABLE сообщает ядру SQL, что вложенная таблица должна интерпретироваться как реляционная. На экран выводятся следующие результаты:
I recommend that you read books by: Gene Wolfe Orson Scott Card Robert Harris Sheri S. Tepper Tom Segev Toni Morrison
Функция CONVERT
Преобразует строку из одного набора символов в другой. Синтаксис функции:
FUNCTION CONVERT (исходная_строка IN VARCHAR2, новый_набор_символов VARCHAR2 [, старый_набор_символов VARCHAR2]) RETURN VARCHAR2
Третий аргумент старый_набор_символов не является обязательным. Если он не задан, применяется набор символов, используемый в базе данных по умолчанию.
Функция CONVERT
не переводит слова или фразы с одного языка на другой, а заменяет буквы или символы одного набора символов буквами или символами другого.
Два самых распространенных набора символов — WE8MSWIN1252 (8-разрядный набор символов Microsoft Windows, кодовая страница 1252) и AL16UTF16 (16-разрядный набор символов Юникод).
Функция HEXTORAW
Преобразует шестнадцатеричную строку типа CHAR
или VARCHAR2
в значение типа RAW
. Синтаксис функции HEXTORAW
:
FUNCTION HEXTORAW (исходная_строка IN CHAR) RETURN RAW FUNCTION HEXTORAW (исходная_строка IN VARCHAR2) RETURN RAW
Функция RAWTOHEX
Преобразует значение типа RAW
в шестнадцатеричную строку типа VARCHAR2
. Синтаксис функции RAWTOHEX
:
FUNCTION RAWTOHEX (двоичное_значение IN RAW) RETURN VARCHAR2
Функция RAWTOHEX
всегда возвращает строку переменной длины, хотя обратная ей перегруженная функция HEXTORAW
поддерживает оба типа строк.
Функция ROWIDTOCHAR
Преобразует двоичное значение типа ROWID
в строку типа VARCHAR2
. Синтаксис функции ROWIDTOCHAR
:
FUNCTION ROWIDTOCHAR (исходная_строка IN ROWID ) RETURN VARCHAR2
Возвращаемая функцией строка имеет следующий формат:
ООООООФФФББББББССС
где ОООООО — номер объекта данных, ФФФ — относительный номер файла базы данных, ББББББ — номер блока в файле, а ССС — номер строки в блоке PL/SQL. Все четыре компонента задаются в формате Base64. Пример:
AAARYiAAEAAAEG8AAB