Oracle преобразовать long в char
The Oracle CAST function is quite powerful, but it can be hard to use. Learn how to use it and what it can do in this article.
2 Answers 2
Converting from long to varchar2 right away using a single statement is not possible, as long has certain restrictions.
You can either Create a temporary table or use PL/SQL code to solve your problem:
CREATE TABLE TABLE2 AS SELECT TO_LOB(COLUMN1) COLUMN FROM TABLE1;
It looks like Oracle internally converts LONG to something else (probably CLOB) when you select LONG in FOR loop. I did not find any explanations in Oracle documentation, but this works
This is an example how to copy all views from another schema to your schema
For some reason it only works for FOR loop and does not work if you use WITH or select from another query
Both inserts raise the same error
ORA-00932: inconsistent datatypes: expected CHAR got LONG
Im trying to convert Long data type to Char but getting this error
ORA-00932: inconsistent datatypes: expected CHAR got LONG
select TO_CHAR(SUBSTR(to_char(terms),0,3999)) FROM
Im trying this query in BI Publisher report and getting this error ORA-00932: inconsistent datatypes: expected CHAR got LONG
Please help me in converting Long data type column to Vrchar or char. or help me in any other solution
Please describe term_table.
If terms is a LONG data type, then here’s some techniques and here’s another
- Мне нравится Показать отметки "Мне нравится" (0) (0)
- Действия
Thanks for the reply.
The data type is LONG. The suggestion for creating a new view/table may work i guess. Is there any other easy way to convert?
- Мне нравится Показать отметки "Мне нравится" (0) (0)
- Действия
Columns defined as LONG can store variable-length character data containing up to 2 gigabytes of information. LONG data is text data that is to be appropriately converted when moving among different systems.
Can you check below query ? And I guess if this works, you dont need to_char function.
select SUBSTR(to_char(terms),0,3999) FROM
- Мне нравится Показать отметки "Мне нравится" (0) (0)
- Действия
Did you look at the second link I posted?
- Мне нравится Показать отметки "Мне нравится" (0) (0)
- Действия
Im getting below error if i try the same
ORA-00932: inconsistent datatypes: expected CHAR got LONG
- Мне нравится Показать отметки "Мне нравится" (0) (0)
- Действия
create or replace function get_long_varchar2(p_emp_no number) return varchar2 is
select long_col into a from emp where empno = p_emp_no;
exception when others then
return ‘Error occurred, unable to cast long to varchar !’;
select substr(get_long_varchar2(empno)1,3999) from emp;
Which will return the expected results only If long column is having less than 32767. If the Content of LONG_COL exceed 32767 characters, then none of the data from that row return. and will return an error message.
- Мне нравится Показать отметки "Мне нравится" (0) (0)
- Действия
Im trying to convert Long data type to Char but getting this error
ORA-00932: inconsistent datatypes: expected CHAR got LONG
select TO_CHAR(SUBSTR(to_char(terms),0,3999)) FROM
Im trying this query in BI Publisher report and getting this error ORA-00932: inconsistent datatypes: expected CHAR got LONG
Please help me in converting Long data type column to Vrchar or char. or help me in any other solution
I strongly suggest you dont use a LONG in the first place, you are experiencing one of their limitations — they are absolutely horribly to work with and don’t solve any problem that a lob doesn’t solve better.
If you have any say in this tables structure then you should really push for this to change.
I need to search field TRIGGER_BODY for text values in Oracle View all_triggers (OWNER VARCHAR2(30) ,
TRIGGER_NAME VARCHAR2(30) ,
TRIGGER_TYPE VARCHAR2(16) ,
TRIGGERING_EVENT VARCHAR2(227) ,
TABLE_OWNER VARCHAR2(30) ,
BASE_OBJECT_TYPE VARCHAR2(16) ,
TABLE_NAME VARCHAR2(30) ,
COLUMN_NAME VARCHAR2(4000) ,
REFERENCING_NAMES VARCHAR2(128) ,
WHEN_CLAUSE VARCHAR2(4000) ,
STATUS VARCHAR2(8) ,
DESCRIPTION VARCHAR2(4000) ,
ACTION_TYPE VARCHAR2(11) ,
TRIGGER_BODY LONG) ;
As a developer, I do not have access to the tables used in this view. The ability to search TRIGGER_BODY for text values is needed for a finite number of tables, ie those with ID column. One option would be tol create a local table with column trigger_body VARCHAR2(2000). To do this, I need to convert LONG to VARCHAR or CLOB. Can you help?
I had a similar requirement for searching view text, so I created
several functions to break the long column into varchar2(4000) chunks as
such:
FUNCTION "GET_VIEW_TEXT" (f_owner varchar2, f_view varchar2)
return varchar2 is
fetch_text varchar2(32000);
return_text varchar2(4000);
begin
select text
into fetch_text
from all_views
where owner = f_owner
and view_name = f_view;
—
return_text := substr(fetch_text,1,4000);
return return_text;
end get_view_text;
function get_view_text2
(f_owner varchar2, f_view varchar2) return varchar2 is
fetch_text varchar2(32000);
return_text varchar2(4000);
begin
select text
into fetch_text
from all_views
where owner = f_owner
and view_name = f_view;
—
return_text := substr(fetch_text,4001,4000);
return return_text;
end get_view_text2;
I then check each chunk for the search text:
accept string prompt ‘Text to search for: ‘
select owner,view_name
from all_views
where instr(upper(get_view_text(owner,view_name)),upper(‘&&string’))>0
or instr(upper(get_view_text2(owner,view_name)),upper(‘&&string’))>0
or instr(upper(get_view_text3(owner,view_name)),upper(‘&&string’))>0
or instr(upper(get_view_text4(owner,view_name)),upper(‘&&string’))>0
or instr(upper(get_view_text5(owner,view_name)),upper(‘&&string’))>0
Функция TO_NUMBER
Функция TO_NUMBER преобразует строки фиксированной и переменной длины, а также вещественные типы IEEE-754 к типу NUMBER с использованием необязательной маски форматирования. Используйте эту функцию, когда потребуется преобразовать строковое представление числа в соответствующее числовое значение. Синтаксис вызова TO_NUMBER :
Здесь строка — строка или выражение типа BINARY_DOUBLE , содержащее представление числа; формат — необязательная маска, которая определяет, как функция TO_NUMBER должна интерпретировать символьное представление числа, содержащегося в первом параметре; параметры_nls — необязательная строка со значениями параметров NLS . Ее можно применять для замены текущих установок параметров NLS уровня сеанса.
При работе с функциями TO_BINARY_FLOAT и TO_BINARY_DOUBLE можно использовать строки INF и –INF для представления положительной и отрицательной бесконечности, а также строку NaN для представления «не числа». Эти специальные строки не чувствительны к регистру символов.
Функция ROWIDTOCHAR
Преобразует двоичное значение типа ROWID в строку типа VARCHAR2 . Синтаксис функции ROWIDTOCHAR :
Возвращаемая функцией строка имеет следующий формат:
где ОООООО — номер объекта данных, ФФФ — относительный номер файла базы данных, ББББББ — номер блока в файле, а ССС — номер строки в блоке PL/SQL. Все четыре компонента задаются в формате Base64. Пример:
Строчные функции конвертации типа данных разработаны для изменения типа данных столбца, выражения или литерала. Наиболее часто используемые функции конвертации это 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’ не будет, как показано в следующих примерах
Query 3: select mod(’11’, 2) from dual
Query 4: select mod(‘11.123’, 2) from dual
Query 5: select mod(‘11.123.456’, 2) from dual
Query 6: select mod(‘$11’, 2) from dual
Запросы 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_DATE(’01-JAN-00′,’DD-MON-RR’),’Day’).
Использование функций 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. Рассмотрим два запроса
Query 1: select to_char(00001)||’ is a special number’ from dual;
Query 2: select to_char(00001, ‘0999999’)||’ is a special number’ from dual;
В запросе номер один у числа 00001 убираются ведущие нули, значение преобразуется в строку ‘1’ и возвращается результат ‘1 is a special number’. Зпрос номер два использует маску форматирования ‘0999999’ для числа 00001 преобразуя число в строку ‘0000001’ и возвращая результат ‘0000001 is a special number’. 0 и 6 девяток в маске форматирования указывают функции TO_CHAR что необходимо использовать ведущие нули, и длина строки должна быть 7. Таким образом строка, возвращаемая функцией TO_CHAR содержит семь символов.
Tip Конвертация чисел в строки надёжный способ убедиться что функция и SQL запрос в целом, который ожидает символьного значения, не вернёт ошибку когда встретится число. Конвертация чисел в строки часто используется для форматирования значений для отчетов. Маска форматирования поддерживает символ валюты, разделитель порядков и разделитель целой и дробной части, что часто используется при отображении финансовой информации.
Конвертация даты в строку используя функцию TO_CHAR
Вы можете использовать преимущества модели масок форматирования при конвертации ДАТЫ в практически любой вариант отображения даты как символьного значения используя функцию TO_CHAR. Синтаксис функции TO_CHAR(date1, [format], [nls_parameter]).
Только параметр date1 обязательный; тогда он должен быть значением, которое может неявно преобразоваться в строку. Необязательный параметр format регистрозависимый и должен быть обрамлён одинарными кавычками. Маска форматирования указывает какие лементы даты должы быть выбраны и как отображать названия элементов даты: полные названия или аббревиатуры. Названия дней и месяцев автоматически разделяются пробелом. Такое поведение можно изменить, используя параметр маски fill mode (fm). Указав в начале маски параметр fm, вы укажете Oracle о необходимости убрать все пробелы. Доступно много опций для маски форматирования, часть из которых отображена в таблице 10-5. Рассмотрим три запроса
Query 1: select to_char(sysdate)||’ is today»s date’ from dual;
Query 2: select to_char(sysdate,’Month’)||’is a special time’ from dual;
Query 3: select to_char(sysdate,’fmMonth’)||’is a special time’ from dual;
Если текущая системная дата 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 с маской форматирования указанной в соответствующем столбце.
Таблица JOB_HISTORY хранит информацию о должностях сотрудника, которые он занимал в компании. Запрос на рисунке 10-3 выбирает информацию из этой таблицы о конце срока действия должности сотрудника для каждого сотрудинка на основании столбцов END_DATE, EMPLOYEE_ID и JOB_ID и форматирует результат в красивое предложение. Символьное выражение объединяется с функцией TO_CHAR в которой используется маска ‘fmDay «the «ddth «of» Month YYYY’. Параметр fm используется чтобы убрать пробелы в имени дня недели и месяца. Два литерата ограниченые двойными кавычками используются для добавления читабельности результату. Литерал “th” применяется к элементу даты чтобы сделать литературно правильным отображение даты как 17 th или 31th. ‘Month’ в маске означает использовать полное имя месяца и наконец YYYY форматирует год как все четыре символа года.
Рисунок 10-3 – Запрос в таблицу JOB_HISTORY
select ‘Employee ‘||EMPLOYEE_ID||’ quit as ‘||JOB_ID||’ on ‘||TO_CHAR(END_DATE,’fmDay «the «ddth «of» Month YYYY’) «Quitting Date»
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 требует обязательного совпадения строки и маски форматирования. Если строка не совпадает с маской – возвращается ошибка. Рассмотрим несколько примеров
Query 1: select to_date(’25-DEC-2010′) from dual;
Query 2: select to_date(’25-DEC’) from dual;
Query 3: select to_date(’25-DEC’, ‘DD-MON’) from dual;
Query 4: select to_date(’25-DEC-2010 18:03:45′, ‘DD-MON-YYYY HH24:MI:SS’) from dual;
Query 5: select to_date(’25-DEC-10′, ‘fxDD-MON-YYYY’) from dual;
Запрос 1 преобразует строку ’25-DEC-2010’ и строка может быть преобразована в дату используя маску DD-MON-YYYY. Дефис можно заменить на другой разделитель. Так как не было указано никакой информации о времени то используется значение по-умолчанию 00:00:00. Запрос 2 не может неявно преобразовать строку в дату, так как недостаточно информации и возвращается ошибка ORA-01840: input value is not long enough for date format. Указав маску DD-MON для строки ’25-DEC’ в запросе номер три, Oracle может преобразовать значение в дату, но так как год и время не указаны, в значении года будет использоваться значение года функции SYSDATE, а время установлено в полночь. Если текущий год будет 2009, то запрос 3 вернёт дату 25/DEC/09 00:00:00. Запрос 4 преобразует строку с явно указанными всеми компнонентами даты и времени. Запрос 5 использует параметра fx. Так как год в строке указан двумя символами, а требуемый формат предполагает четыре цифры года, то запрос возвращает ошибку ORA-01862: the numeric value does not match the length of the format item.
Конвертация строки в число используя функцию TO_NUMBER
Функция TO_NUMBER возвращает значение типа данных NUMBER. Исходная строка должна быть составлена таким образом, чтобы все несовместимые символы отсутствовали или были указаны в соответствующей маске форматирования. Синтаксис функции TO_NUMBER(string1, [format], [nls_parameter]). Только string1 является обязательным параметром, и если не указан параметр format то значение должно быть таким, чтобы была возможность неявно сконвертировать его в число. Маски форматирования идентичны перечисленным в таблице 10-4. Рассмотрим запросы
Query 1: select to_number(‘$1,000.55’) from dual;
Query 2: select to_number(‘$1,000.55′,’$999,999.99’) from dual;
Запрос один не может неявно преобразовать строку, так как она содержит знак валюты и разделители, которые явно неуказаны в маске, поэтому возвращается ошибка 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
I am trying to insert into varchar2 column from a long column. here is the below example, TEXT.TEXT_COL = VARCHAR2(4000) and NOTE.TEXT_NOTE = LONG .
When i run the above sql i get error
SQL Error: ORA-00997: illegal use of LONG datatype
I used TO_LOB() too, but still the same error.
Is there any function which simply coverts long and put it in varchar2 . Let me know your thoughts.
Функция CHARTOROWID
Преобразует строку типа CHAR или VARCHAR2 в значение типа ROWID . Синтаксис функции:
Для успешного преобразования функцией CHARTOROWID строка должна состоять из 18 символов в формате:
где ОООООО — номер объекта данных, ФФФ — относительный номер файла базы данных, ББББББ — номер блока в файле, а ССС — номер строки в блоке. Все четыре компонента задаются в формате Base64. Если исходная строка не соответствует этому формату, инициируется исключение VALUE_ERROR .
Недостатки неявного преобразования
Неявное преобразование типов имеет ряд недостатков.
- PL/SQL относится к языкам со статической типизацией. Неявные преобразования означают потерю некоторых преимуществ статической типизации — таких, как ясность и надежность кода.
- Каждое неявное преобразование означает частичную потерю контроля над программой. Программист не выполняет его самостоятельно и никак им не управляет, а лишь предполагает, что оно будет выполнено и даст желаемый эффект. В этом есть элемент неопределенности — если компания Oracle изменит способ или условие выполнения преобразований, это может отразиться на программе.
- Неявное преобразование типов в PL/SQL зависит от контекста. Оно может работать в одной программе и не работать в другой, хотя на первый взгляд код кажется одинаковым. Кроме того, результат преобразования типов не всегда соответствует ожиданиями программиста.
- Программу легче читать и понять, если данные в ней преобразуются явно, поскольку при этом фиксируются различия между типами данных в разных таблицах или в таблице и коде. Исключая из программы скрытые действия, вы устраняете и потенциальную возможность ошибок.
Таким образом, в SQL и PL/SQL рекомендуется избегать неявного преобразования типов. Лучше пользоваться функциями, которые выполняют явное преобразование — это гарантирует, что результат преобразования будет точно соответствовать вашим ожиданиям.
Purpose of the Oracle CAST Function
The purpose of the Oracle CAST function is to convert one data type to another.
It allows for more functionality than the TO_NUMBER, TO_CHAR and TO_DATE functions, as it allows you to specify the actual data types you want to convert to, rather than just use the defaults of those functions.
Example 3 – Oracle CAST VARCHAR to Number
This example shows how to convert a VARCHAR to a NUMBER.
‘2093’ | OUTPUT_VALUE |
2093 | 2093 |
I’ve put spaces around the first number value, which have been removed when it has converted to a NUMBER data type.
Неявное преобразование типов
Обнаружив необходимость преобразования, PL/SQL пытается привести значение к нужному типу. Вероятно, вас удивит, насколько часто это делается. На рис. 1 показано, какие виды неявного преобразования типов выполняются PL/SQL.
Рис. 1. Неявные преобразования типов, выполняемые PL/SQL
Неявное преобразование типов осуществляется при задании в операторе или выражении литерального значения в правильном внутреннем формате, которое PL/SQL преобразует по мере необходимости. В следующем примере PL/SQL преобразует литеральную строку «125» в числовое значение 125 и присваивает его числовой переменной:
Неявное преобразование типов выполняется также при передаче программе параметров не того формата, который в ней используется. В следующей процедуре таким параметром является дата. Вызывая эту процедуру, вы передаете ей строку в формате ДД-МММ-ГГ, которая автоматически преобразуется в дату:
Неявное преобразование строки в дату выполняется в соответствии со спецификацией NLS_DATE_FORMAT . Проблема заключается в том, что в случае изменения NLS_DATE_FORMAT работа программы будет нарушена.
Использование TO_NUMBER с маской форматирования
Применение функции TO_NUMBER с маской форматирования позволяет получить более разнообразные представления чисел в PL/SQL. Например, местоположение разделителей групп и символ денежной единицы могут задаваться следующим образом:
Указывать в форматной строке точное количество цифр не обязательно. Функция TO_NUMBER позволяет задать в строке форматирования больше цифр, чем содержится в преобразуемом значении. Следующая строка кода также выполняется без ошибок:
Но если справа или слева от десятичной запятой значение содержит больше цифр, чем допускает маска форматирования, произойдет ошибка. Первое из следующих двух преобразований завершится ошибкой, поскольку строка содержит десять цифр слева от десятичной запятой, тогда как маска разрешает только девять. Второе преобразование завершается ошибкой из-за того, что строка содержит слишком много цифр справа от десятичной запятой:
Элемент форматирования 0 обеспечивает вывод начальных нулей:
Элемент PR распознает угловые скобки как обозначение отрицательного числа:
Однако не все элементы форматирования предназначены для преобразования строк в числа. Например, элемент RN , предназначенный для вывода числа римскими цифрами, предназначен только для форматирования выводимой информации. Следующая попытка преобразования вызовет ошибку:
Элемент EEEE тоже используется только для форматирования вывода. Для обратного преобразования он не нужен, поскольку функция TO_NUMBER правильно распознает числа в научной записи без дополнительных указаний:
Функция TO_CHAR
Функция TO_CHAR выполняет задачу, обратную функции TO_NUMBER : она преобразует число в его символьное представление. Используя необязательную маску форматирования, можно подробно указать, каким должно быть представление. Функция TO_CHAR вызывается следующим образом:
Здесь число — это число, которое требуется представить в символьной форме. Оно может относиться к любому из числовых типов PL/SQL: NUMBER, PLS_INTEGER, BINARY_INTEGER, BINARY_FLOAT, BINARY_DOUBLE, SIMPLE_INTEGER, SIMPLE_FLOAT или SIMPLE_DOUBLE . Параметр формат содержит необязательную маску форматирования, определяющую способ представления числа в символьной форме; необязательная строка параметры_nls содержит значения параметров NLS . Ее можно применить для замещения текущих установок параметров NLS уровня сеанса.
Ограничения неявного преобразования
Как видно из рис. 1, преобразование может выполняться только между определенными типами данных; PL/SQL не может преобразовать произвольный тип данных в любой другой. Более того, при некоторых неявных преобразованиях типов генерируются исключения. Возьмем следующую операцию присваивания:
В PL/SQL нельзя преобразовать строку «abc» в число, поэтому при выполнении приведенного кода инициируется исключение VALUE_ERROR . Вы сами должны позаботиться о том, чтобы значение, для которого PL/SQL выполняет преобразование типов, могло быть конвертировано без ошибок.
Parameters
- expr (mandatory choice): This is the value or expression that is to be converted to another data type. See below for the valid data types.
- MULTISET (optional): This keyword indicates that the subquery results will return multiple rows, and are then CAST into a collection value. If this is omitted, and the subquery returns multiple rows, it will only return a single row.
- subquery (mandatory choice): This is the subquery that can be run to be converted into a collection data type.
- type_name (mandatory): This is the Oracle data type that the expr or subquery will be converted to. See below for the valid data types.
Использование TO_CHAR без маски форматирования
Функция TO_CHAR , как и TO_NUMBER , может вызываться без маски форматирования:
Результат выглядит так:
В отличие от TO_NUMBER форма TO_CHAR особой пользы не приносит. Чтобы число лучше читалось, нужно задать как минимум разделитель групп разрядов.
Явное преобразование типов
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 | Строку произвольного набора символов в Юникод |
Example 5 – Oracle Cast NUMBER to VARCHAR2
This example shows how to convert a NUMBER value to a VARCHAR2 data type.
9834 | OUTPUT_VALUE |
9834 | 9834 |
This is similar to Example 2. It can be hard to see in this example but the alignment is different in the SQL Developer tool, which is done for different data types.
Example 6 – CAST with Multiset Subquery
This example is a simple example of how to use a multiset subquery with cast.
First, we look at the customers table to find employees in the USA.
FIRST_NAME |
John |
Sally |
Adam |
Then, we create a new TYPE to store VARCHAR2 values.
Then, we write our SQL to return a value of that type.
As you can see, it returns one value. This value is of type “usa_employee_first_names” and includes several values. It can then be used for other purposes, such as inserting into a table.
Компьютеры лучше работают с числами в двоичном представлении, тогда как людям удобнее видеть числовые данные в виде строк, состоящих из цифр, запятых и пробелов. PL/SQL позволяет преобразовывать числа в строки, и наоборот. Обычно такие преобразования выполняются функциями TO_CHAR и TO_NUMBER .
При работе с двоичными вещественными типами IEEE-754 используйте функции TO_BINARY_FLOAT и TO_BINARY_DOUBLE . Чтобы упростить последующее изложение, в тексте будет упоминаться только функция TO_NUMBER . Помните, что все неуточненные ссылки на TO_NUMBER в равной степени относятся к функциям TO_BINARY_FLOAT и TO_BINARY_DOUBLE .
Syntax
The syntax for the Oracle CAST function is:
This structure is a little different as it does not separate the parameters by a comma, as many other functions do. Instead, it has the word “AS”.
There are two main ways you can run this query. You can run it using a single result data type, which would be:
Or, you can run it using a subquery, which would be:
Функция HEXTORAW
Преобразует шестнадцатеричную строку типа CHAR или VARCHAR2 в значение типа RAW . Синтаксис функции HEXTORAW :
Передача функции TO_NUMBER параметров NLS
Действие многих элементов форматирования определяется текущими установками параметров NLS . Например, элемент G представляет разделитель групп разрядов, но какой именно символ используется в качестве разделителя, зависит от текущего значения параметра NLS_NUMERIC_CHARACTERS в момент выполнения преобразования. Для просмотра текущих значений параметров можно запросить представление NLS_SESSION_PARAMETERS :
Некоторые установки NLS по умолчанию зависят от других. Если присвоить параметру NLS_TERRITORY значение AMERICA , Oracle по умолчанию установит параметр NLS_NUMERIC_CHARACTERS равным '.,'. Это не помешает явно присвоить параметру NLS_NUMERIC_CHARACTERS другое значение (например, с использованием команды ALTER SESSION ).
Иногда отдельные параметры NLS требуется переопределить только на время вызова TO_NUMBER . В этом случае нужные установки задаются в этом вызове и действуют они исключительно для него. Например, в следующем примере при вызове TO_NUMBER задаются установки NLS , соответствующие NLS_TERRITORY = FRANCE :
Строка параметров NLS получается слишком длинной, поэтому мы разбиваем ее на три строки, объединяемых оператором конкатенации, чтобы пример лучше смотрелся на странице. Обратите внимание на дублирование кавычек. Параметру NLS_NUMERIC_CHARACTERS требуется присвоить следующее значение:
Поскольку это значение вместе с кавычками включается в строку параметров NLS , каждую кавычку необходимо продублировать. В результате получается следующая строка:
Функция TO_NUMBER позволяет задавать только три приведенных в данном примере параметра NLS . Было бы удобнее, если бы при вызове можно было использовать следующую запись:
К сожалению, изменение параметра NLS_TERRITORY при вызове TO_NUMBER не поддерживается. Функция поддерживает только NLS_NUMERIC_CHARACTERS , NLS_CURRENCY и NLS_ISO_CURRENCY .
За подробной информацией о настройке параметров NLS обращайтесь к руководству «Oracle’s Globalization Support Guide», которое является частью документации Oracle11g.
Передавать TO_NUMBER третий аргумент не рекомендуется — лучше полагаться на настройки сеанса, определяющие, как PL/SQL интерпретирует элементы маски форматирования (такие, как L, G и D). Вместо того чтобы жестко кодировать информацию в программах, нужно дать пользователю возможность задавать их на уровне сеанса.
Использование функции TO_CHAR с маской форматирования
При преобразовании числа в символьное представление функция TO_CHAR используется чаще всего с маской форматирования. Например, с ее помощью можно вывести денежную сумму:
В локальном контексте США результат будет выглядеть так:
Элементы форматирования позволяют очень гибко определять формат символьного представления числа. Чтобы лучше понять, как они работают, стоит немного с ними поэкспериментировать. В следующем примере указано, что старшие разряды должны быть заполнены нулями, но при этом элемент форматирования B требует замены нулей пробелами. Данный элемент предшествует цифровым элементам (нулям), но следует за индикатором вывода знака денежной единицы L :
Результат будет иметь следующий вид:
В примере выводится только одна строка, полученная после первого преобразования. В результате второго преобразования получается нуль, и элемент форматирования B заставляет TO_CHAR вернуть пустую строку, хотя в маске форматирования указано, что нулевые старшие разряды числа следует оставить. В качестве эксперимента попробуйте выполнить этот пример без элемента B.
Не все комбинации элементов форматирования являются допустимыми. Например, нельзя использовать сочетание LRN , которое выводит перед числом, записанным римскими цифрами, знак денежной единицы. Oracle не документирует такие нюансы, поэтому о некоторых из них можно узнать только на практике.
Valid Data Types for Oracle Cast
The Oracle CAST function can only convert to and from certain data types. This table from Oracle shows which types are compatible.
from BINARY_FLOAT, BINARY_DOUBLE | from CHAR, VARCHAR2 | from NUMBER | from DATETIME / INTERVAL | from RAW | from ROWID, UROWID | from NCHAR, NVARCHAR2 |
to BINARY_FLOAT, BINARY_DOUBLE | X | X | X | X | ||
to CHAR, VARCHAR2 | X | X | X | X | X | X |
to NUMBER | X | X | X | X | ||
to DATE, TIMESTAMP, INTERVAL | X | X | ||||
to RAW | X | X | ||||
to ROWID, UROWID | X | X | ||||
to NCHAR, NVARCHAR2 | X | X | X | X | X | X |
A few things to note from this table:
- The Datetime/Interval column includes DATE, TIMESTAMP, TIMESTAMP WITH TIMEZONE, INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH.
- You cannot cast a UROWID to a ROWID if the UROWID contains the value of a ROWID of an index-organized table.
- CAST does not support any of the LOB data types. If you try to CAST a CLOB into a character data type, for example, the LOB value is converted to a character value and then converted to the target data type. If the resulting value is larger than the target type, then you’ll get an error.
Want to see articles like this on all of the other Oracle functions? Check out this page which lists all of the functions and guides for each of them.
Функция RAWTOHEX
Преобразует значение типа RAW в шестнадцатеричную строку типа VARCHAR2 . Синтаксис функции RAWTOHEX :
Функция RAWTOHEX всегда возвращает строку переменной длины, хотя обратная ей перегруженная функция HEXTORAW поддерживает оба типа строк.
Использование TO_NUMBER без параметров форматирования
В простейших случаях функция TO_NUMBER вызывается без строки форматирования. Все следующие преобразования успешно выполняются без дополнительных параметров:
В общем случае функция TO_NUMBER может использоваться без параметров форматирования в следующих случаях:
- когда число представлено только цифрами с единственной десятичной запятой;
- при использовании научной записи — например, 1.25E2;
- перед числом стоит необязательный знак, плюс или минус; при отсутствии знака число считается положительным.
Если символьная строка не соответствует этим критериям или значения должны округляться до заданного количества десятичных знаков, вызывайте функцию TO_NUMBER с маской форматирования.
Example 4 – Oracle CAST DATE to TIMESTAMP
This example shows how to cast a DATE value to a TIMESTAMP value. For this example, I’ve used the CUSTOMERS table shown above.
START_DATE | OUTPUT_VALUE |
12/APR/10 | 12/APR/10 12:00:00.000000000 AM |
04/JUL/11 | 04/JUL/11 12:00:00.000000000 AM |
21/MAR/09 | 21/MAR/09 12:00:00.000000000 AM |
01/FEB/01 | 01/FEB/01 12:00:00.000000000 AM |
(null) | (null) |
10/FEB/12 | 10/FEB/12 12:00:00.000000000 AM |
16/OCT/12 | 16/OCT/12 12:00:00.000000000 AM |
16/OCT/12 | 16/OCT/12 12:00:00.000000000 AM |
Each of the DATE value has been converted to a TIMESTAMP which includes hours, minutes, seconds, and fractional seconds.
Example 2 – Oracle CAST NUMBER to String
This example shows how to cast a number to a CHAR data type.
41.522 | OUTPUT_VALUE |
41.522 | 41.522 |
It can be hard to see in this example but the alignment is different in the SQL Developer tool, which is done for different data types.
Examples of the Oracle CAST Function
Here are some examples of the Oracle CAST function. I find that examples are the best way for me to learn about code, even with the explanation above.
First of all, let’s set up the CUSTOMERS table.
FIRST_NAME | LAST_NAME | COUNTRY | FULL_Address | EMPLOYEES | START_DATE |
John | Smith | USA | 10 Long Road | 4 | 12/APR/10 |
Sally | Jones | USA | 50 Market Street | 10 | 4/JUL/11 |
Steve | Brown | Canada | 201 Flinders Lane | 15 | 21/MAR/09 |
Mark | Allan | UK | 8 Smith Street | 23 | 1/FEB/01 |
Adam | Cooper | USA | 14 Wellington Road | 55 | (null) |
Josh | Thompson | (null) | 80 Victoria Street | 1 | 10/FEN/12 |
Peter | Manson | France | 5 Johnson St | (null) | 16/OCT/12 |
(null) | (null) | (null) | 155 Long Road | (null) | 16/OCT/12 |
We can see the data in the customers table that will be used for a few examples below.
Функция CONVERT
Преобразует строку из одного набора символов в другой. Синтаксис функции:
Третий аргумент старый_набор_символов не является обязательным. Если он не задан, применяется набор символов, используемый в базе данных по умолчанию.
Функция CONVERT не переводит слова или фразы с одного языка на другой, а заменяет буквы или символы одного набора символов буквами или символами другого.
Example 1 – Oracle CAST String to DATE
This example shows how to CAST from a string value that contains a date, to a date data type.
’30-Apr-15′ | OUTPUT_VALUE |
30-Apr-15 | 30/APR/15 |
Notice how the date in the output value is displayed in the date format for Oracle.
Элемент форматирования V
Элемент форматирования V достаточно необычен, чтобы его стоило упомянуть особо. Он позволяет масштабировать значение, а его действие лучше показать на примере (рис. 1).
Рис. 1. Элемент форматирования V
Зачем может понадобиться масштабирование? Рассмотрим простой пример. Стандартная единица сделки на бирже составляет 100 акций, и сообщая о реализованных на бирже акциях обычно говорят о количестве проданных пакетов по 100 акций. Поэтому 123 продажи означает 123 пакета по 100 акций, то есть 12 300 акций.
Следующий пример показывает, как использовать элемент V для масштабирования значения 123, с учетом того, что на самом деле оно представляет количество сотен:
Заметьте, что в этом примере маска форматирования включает элемент G , определяющий местоположение разделителя групп (запятой), который может быть задан только слева от элемента V , что не всегда удобно. Следующая маска форматирования на первый взгляд выглядит вполне разумно:
Вы ожидаете, что результат будет отформатирован в виде 1,234,500, но элемент G не может располагаться справа от V. Можно использовать маску 9G99V9999 для получения результата 1,234500 или маску 999V9999 для получения 1234500, но оба эти результата выглядят не так, как нам хотелось бы.
В ходе выполнения программы 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, что вложенная таблица должна интерпретироваться как реляционная. На экран выводятся следующие результаты:
Читайте также: