В том случае, когда при преобразовании символьной строки в число слева или справа от десятичной запятой получается больше цифр, чем допускает маска форматирования, в PL/SQL происходит ошибка. Но при преобразовании числа в символьную строку ошибка возникает только при наличии лишних цифр слева от запятой. Если маска форматирования содержит меньше цифр после десятичной запятой, чем требуется для представления числа, число округляется до указанного количества цифр.
Если попытка преобразования завершается неудачей из-за того, что слева от запятой было слишком много цифр, функция TO_CHAR возвращает строку из символов «#». Например, следующее преобразование не будет выполнено, потому что число 123 не помещается в маску:
SQL> DECLARE 2 b VARCHAR2(30);BEGIN 3 b := TO_CHAR(123.4567,'99.99'); 4 DBMS_OUTPUT.PUT_LINE(b);END; ######
Если же дробная часть числа не помещается в маску, происходит округление:
SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE(TO_CHAR(123.4567,'999.99')); 3 DBMS_OUTPUT.PUT_LINE(TO_CHAR(123.4567,'999'));END; 123.46 123
Цифры 5 и больше округляются в большую сторону, так что число 123,4567 округляется до 123,46, а цифры меньше 5 — в меньшую, поэтому 123,4xxx округляется до 123.
Пробелы при преобразовании чисел в символьные строки
В ходе преобразования числа в символьную строку функция TO_CHAR всегда оставляет место для знака «-», даже если число положительное.
DECLARE
b VARCHAR2(30);
c VARCHAR2(30);
BEGIN
b := TO_CHAR(-123.4,'999.99');
c := TO_CHAR(123.4,'999.99');
DBMS_OUTPUT.PUT_LINE(':' || b || ' ' || TO_CHAR(LENGTH(b)));
DBMS_OUTPUT.PUT_LINE(':' || c || ' ' || TO_CHAR(LENGTH(c)));
END; Результат преобразования:
:-123.40 7 : 123.40 7
Обратите внимание: каждая строка имеет длину семь символов, хотя для положительного числа достаточно и шести. Начальный пробел применяется для выравнивания чисел в столбцах. Но если по какой-то причине вам требуется вывести компактные числа без пробелов, это создаст проблемы.
Если для представления отрицательных чисел используются угловые скобки (в маске задан элемент PR), положительные числа дополняются одним начальным и одним завершающим пробелом.
Если числа, преобразованные в символьные данные, не должны содержать ни начальных, ни завершающих пробелов, существует несколько решений. Одно из них основано на использовании элемента форматирования TM, определяющего «минимальное» представление числа:
DECLARE
b VARCHAR2(30);
c VARCHAR2(30);
BEGIN
b := TO_CHAR(-123.4,'TM9');
c := TO_CHAR(123.4,'TM9');
DBMS_OUTPUT.PUT_LINE(':' || b || ' ' || TO_CHAR(LENGTH(b)));
DBMS_OUTPUT.PUT_LINE(':' || c || ' ' || TO_CHAR(LENGTH(c)));
END;
Результат:
:-123.4 6 :123.4 5
Этот метод удобен, но не позволяет задавать другие элементы форматирования. Так, нельзя задать формат TM999.99, чтобы число выводилось с двумя цифрами в дробной части. Если вам нужны другие элементы форматирования или если элемент TM не поддерживается в вашей версии PL/SQL, можно воспользоваться усечением результата:
DECLARE
b VARCHAR2(30);
c VARCHAR2(30);
BEGIN
b := LTRIM(TO_CHAR(-123.4,'999.99'));
c := LTRIM(TO_CHAR(123.4,'999.99'));
DBMS_OUTPUT.PUT_LINE(':' || b || ' ' || TO_CHAR(LENGTH(b)));
DBMS_OUTPUT.PUT_LINE(':' || c || ' ' || TO_CHAR(LENGTH(c)));
END;
Результат преобразования:
:-123.40 7 :123.40 6
Функция LTRIM была использована для удаления начальных пробелов и сохранения двух фиксированных цифр справа от десятичной запятой. Если же знак выводится справа от числа (например, с использованием элемента форматирования MI), можно воспользоваться функцией RTRIM. Если же при выводе используются элементы, влияющие на вывод с обеих сторон числа (например, PR), используется функция TRIM.
Передача параметров NLS функции TO_CHAR
По аналогии с функцией TO_NUMBER, функция TO_CHAR может получать в третьем параметре строку настроек NLS. Пример:
BEGIN DBMS_OUTPUT.PUT_LINE( TO_CHAR(123456.78,'999G999D99','NLS_NUMERIC_CHARACTERS='',.''') ); END;
Результат:
123.456,78
Таким способом можно задавать три параметра NLS: NLS_NUMERIC_CHARACTERS, NLS_CURRENCY и NLS_ISO_CURRENCY. Пример одновременного использования всех трех параметров приводился ранее, в разделе «Передача функции TO_NUMBER параметров NLS».
Функция CAST
Функция CAST применяется для преобразования чисел в строки, и наоборот. Синтаксис функции выглядит так:
CAST (выражение AS тип_данных)
В следующем примере функция CAST сначала используется для преобразования числа типа NUMBER в строку VARCHAR2, а затем символы этой строки преобразуются в соответствующее числовое значение:
DECLARE a NUMBER := -123.45; a1 VARCHAR2(30); b VARCHAR2(30) := '-123.45'; b1 NUMBER; b2 BINARY_FLOAT; b3 BINARY_DOUBLE; BEGIN a1 := CAST (a AS VARCHAR2); b1 := CAST (b AS NUMBER); b2 := CAST (b AS BINARY_FLOAT); b3 := CAST (b AS BINARY_DOUBLE); DBMS_OUTPUT.PUT_LINE(a1); DBMS_OUTPUT.PUT_LINE(b1); DBMS_OUTPUT.PUT_LINE(b2); DBMS_OUTPUT.PUT_LINE(b3); END;
Результат выполнения:
-123.45 -123.45 -1.23449997E+002 -1.2345E+002
У функции CAST есть один недостаток: она не поддерживает маски форматирования чисел. С другой стороны, эта функция является частью стандарта ISO SQL — в отличие от функций TO_CHAR и TO_NUMBER. Если разработчику важно, чтобы программный код был полностью совместим со стандартом ANSI, используйте для преобразования чисел в строковые значения функцию CAST. В других случаях мы рекомендуем применять функции TO_CHAR и TO_NUMBER.
Поскольку PL/SQL не соответствует стандарту ISO, написать на этом языке полностью совместимый с указанным стандартом код невозможно. Таким образом, функция CAST в программах на PL/SQL становится лишней. Она востребована только в SQL-командах (SELECT, INSERT и т. д.), если они должны быть совместимы со стандартом ANSI.
Неявные преобразования
Преобразования между числами и строками можно выполнить еще одним способом: просто поручите эту работу PL/SQL. Такое преобразование называется неявным, так как оно не определяется явно в программном коде. Пример неявных преобразований:
DECLARE a NUMBER; b VARCHAR2(30); BEGIN a := '-123.45'; b := -123.45; ...
Как упоминалось в главе 7, неявные преобразования имеют ряд недостатков. Прежде всего, я считаю, что разработчик должен в полной мере контролировать свой код, а при использовании этого метода этот контроль отчасти утрачивается. Всегда лучше знать, когда и какое именно выполняется преобразование, поэтому желательно выполнять его явно. Если полагаться на неявное преобразование, то вы не сможете отследить, где и когда оно выполняется, и код станет менее эффективным. Кроме того, наличие явных преобразований делает код более понятным другим программистам.
Другой недостаток неявных преобразований заключается в том, что они могут нормально работать (по крайней мере на первый взгляд) в простых случаях, но их результат не всегда очевиден. Рассмотрим пример:
DECLARE a NUMBER; BEGIN a := '123.400' || 999;
Остерегайтесь неявных преобразований!
В разделе «Типы BINARY_FLOAT и BINARY_DOUBLE» этой главы я привел код (binary_performance.sql), использовавшийся для сравнения производительности BINARY_DOUBLE и NUMBER. В первой версии этого теста циклы для вычисления площади были запрограммированы следующим образом:
DECLARE bd BINARY_DOUBLE; ... BEGIN ... FOR bd IN 1..1000000 LOOP bd_area := bd**2 * pi_bd; END LOOP; ...
Я был потрясен, когда результаты вдруг показали, что вычисления с NUMBER выполняются намного быстрее вычислений с BINARY_DOUBLE. Это было совершенно непостижимо, потому что я «знал», что операции с BINARY_DOUBLE выполняются на аппаратном уровне, а следовательно, просто обязаны работать быстрее операций с NUMBER.
Потом кто-то из сотрудников Oracle Corporation указал мне на мою ошибку: цикл FOR (в приведенном виде) неявно объявляет переменную цикла PLS_INTEGER с именем bd. Область действия нового объявления bd перекрывает блок цикла и замещает мое объявление bd в формате BINARY_DOUBLE. Кроме того, я записал константу в формате 2 (вместо 2d), из-за чего она интерпретировалась как NUMBER. Таким образом, значение bd сначала неявно преобразовывалось в NUMBER, затем возводилось в квадрат, и полученное значение NUMBER неявно снова преобразовывалось в BINARY_DOUBLE для умножения на pi_bd. Неудивительно, что результаты были настолько плохи! Подобные ловушки характерны для неявных преобразований.
Какое значение будет содержать переменная a после выполнения этого кода? Это зависит от того, как PL/SQL вычисляет выражение в правой части оператора присваивания. Если он сначала преобразует строку в число, мы получим следующий результат:
a := '123.400' || 999; a := 123.4 || 999; a := '123.4' || '999'; a := '123.4999'; a := 123.4999;
С другой стороны, если PL/SQL сначала преобразует число в строку, то результат будет таким:
a := '123.400' || 999; a := '123.400' || '999'; a := '123.400999'; a := 123.400999;
И какой из двух результатов будет получен? Вы знаете? Даже если знаете, вряд ли другие программисты сразу догадаются об этом, читая ваш код. В данном случае лучше записать преобразование в явном виде:
a := TO_NUMBER('123.400' || TO_CHAR(999));
Кстати говоря, это выражение соответствует порядку обработки исходного выражения базой данных. Согласитесь, с явно выраженными преобразованиями его намного проще понять с первого взгляда.
