Раздел 3. Использование встроенных функций и преобразование типов

Строковые функции корректно работают со строковыми значениями национальных алфавитов.

Ниже приведены запросы, иллюстрирующие использование строковых функций. Запрос 3.2. иллюстрирует добавление символов «*» к строке слева или справа, чтобы общая длина получившейся строки стала 25 символов; эта функция удобна для выравнивания колонок текста в текстовых отчетах.

SELECT customer_name, LPAD(customer_name, 25, '*') "LPAD",

RPAD(customer_name, 25, '*') "RPAD"

FROM acceptedorders; (3.2)

В запросе 3.3 используется обратное действие – отсечение незначащих символов от строки слева или справа (сначала к исходным значениям полей искусственно добавляются символы '!!!!!!').

SELECT customer_name,

LTRIM('!!!!!!' || customer_name, '!') "LTRIM",

RTRIM(customer_name || '!!!!', '!') "RTRIM"

FROM acceptedorders; (3.3)

В запросе 3.4. производится преобразование строковых значений в верхний и нижний регистры и замена первых букв слов на заглавные.

SELECT customer_name, UPPER(customer_name) "UPPER",

LOWER(customer_name) "LOWER",

INITCAP(LOWER(customer_name)) "INITCAP"

FROM acceptedorders; (3.4)

В запросе 3.5. выполняется замена подстроки 'Ms' на строку 'Miss', выделение подстроки из строки, начиная с четвертого символа от начала, а также вычисление длин строк.

SELECT customer_name,

REPLACE(customer_name, 'Ms', 'Miss') "REPLACE",

SUBSTR(customer_name, 4) "SUBSTR"",

LENGTH(RTRIM(customer_name)) "LENGTH"

FROM acceptedorders; (3.5)

Большое значение имеет работа со значениями-датами и с функциями работы с датами. Значения-даты записываются в литеральном представлении, как и строковые значения, при помощи ограничивающих одиночных кавычек. С этими значениями можно выполнять некоторые дополнительные действия, в частности, добавлять и вычитать числа, что интерпретируется как переход к следующей или предыдущей дате. Поскольку значения-даты могут записываться в разных форматах, обычно функции, получающие такие значения как параметр, используют специальные строки, определяющие использованный формат записи даты. Для полного описания всех функций работы с датами следует обратиться к документации; в данном издании приводятся примеры использования наиболее типичных способов использования значений-дат.

Системная функция SYSDATE, употребляемая без параметров и без скобок, возвращает текущую дату, т.е. дату вызова этой функции. Следущий запрос иллюстрирует инкрементирование значения-даты; значение инкремента интерпретируется как количество дней.

SELECT SYSDATE, SYSDATE + 1, SYSDATE + 0.1, SYSDATE - 1, SYSDATE + 30, SYSDATE + 300 FROM DUAL; (3.6)

Функции TRUNC и ROUND выполняют округление арифметических значений; применительно к значениям-датам эти функции используются для получения даты первого или последнего дня месяца или года. В запросе 3.7 определяется первые дни текущего и будущего года, первые дни текущего и следующего месяца и округление текущей даты, с арифметической точки зрения, в нижнюю и верхнюю сторону – т.е. к текущей и следующей дате с точностью до дня. Второй параметр функций – это строка-формат преобразования даты.

SELECT SYSDATE "TODAY",

TRUNC(SYSDATE, 'YEAR') "YEAR",

ROUND(SYSDATE, 'YEAR') "YEAR",

TRUNC(SYSDATE, 'MONTH') "MONTH",

ROUND(SYSDATE, 'MONTH') "MONTH",

TRUNC(SYSDATE, 'DDD') "DDD",

ROUND(SYSDATE, 'DDD') "DDD" FROM DUAL; (3.7)

Функция LAST_DAY удобна для определения последнего дня месяца:

SELECT LAST_DAY(SYSDATE),

LAST_DAY(SYSDATE) - SYSDATE "осталось"

FROM DUAL; (3.8)

Теперь рассмотрим вопрос преобразование типов при вычислении выражений. В качестве примера приведем три запроса:

SELECT item + price FROM acceptedorders; (3.9)

SELECT item || price FROM acceptedorders; (3.10)

SELECT item || TO_CHAR(price) FROM acceptedorders; (3.11)

Из них первый запрос 3.9 является некорректным и не будет вычислен, поскольку тип поля item – строка, тип поля price – число, а оператор «+» применяется только к числовым значениям. Второй запрос, однако, будет выполнен, несмотря на то, что оператор «||» применяется только к строковым значениям и сформирует следующую выборку:

Разница состоит в том, что перед вычислением выражений производится попытка преобразования операндов к типу, который требуется при вычислении данной операции. В первом случае, поскольку оператор «+» - арифметический, произойдет попытка преобразовать значения поля item в значения целого типа, причем преобразованы должны быть значения этого поля во всех строках, которые используются в запросе. Однако конкретные значения ‘Watch’, ‘Book’ не могут быть преобразованы в числа, поэтому запрос 3.9 является ошибочным с точки зрения исполнения, хотя синтаксически он правильный. Например, следующий запрос будет выполнен, поскольку значение ‘134’ допускает преобразование в число:

SELECT '123' + price FROM acceptedorders (3.12)

Во втором случае, при вычислении запроса 3.10, оператор “||” строковый, а числовые значения (поле price) всегда преобразуются в строки.

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

К функциям преобразования типов относятся функции TO_CHAR(), TO_NUMBER(), TO_DATE(); вторым параметром этих функций обычно является специальная строка, определяющая способ форматирования значения. Ниже приведены примеры основных способов использования этих функций.

Запрос 3.13 преобразует числовое значение поля price в формат для денежного представления и в значение в научном формате.

SELECT price, TO_CHAR(price, 'U999,000.99'),

TO_CHAR(price, '9.9EEEE')

FROM acceptedorders; (3.13)

Запрос 3.14 иллюстрирует вывод даты в различных форматах.

SELECT SYSDATE,

TO_CHAR(SYSDATE, 'DD, Day Month YYYY')

"DD, Day Month YYYY",

TO_CHAR(SYSDATE, 'DD MM YYYY')

"DD MM YYYY" FROM DUAL (3.14)

Помимо многочисленных функций преобразования вида TO_XXXX(), можно использовать функцию CAST, имеющую следующий синтаксис: CAST(выражение AS тип)

Эта функция выполняет преобразование значения выражения к указанному типу, если это возможно. Ниже приведены примеры преобразования типов при помощи этой функции.

SELECT name, CAST(name AS VARCHAR(3)) "VARCHAR(3)",

CAST(name AS CHAR(3)) "CHAR(3)" FROM student (3.15)

Помимо преобразования собственно типа значений, функцию CAST можно использовать для изменения длины строковых значений, значение при этом усекается до требуемой длины, как в предыдущем примере.