Рубрика: Строчные функции и преобразование типов

Описание функций в SQL

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

 

Использование функций обработки строк, чисел и данных в команде SELECT

• Функция INITCAP принимает исходную строку и возвращает результат в котором каждое слово начинается с заглавной буквы
• Функция для получения длины строки, включая пробелы и специальные символы – это фунция LENGTH
• Функция INSTR возвращает позицию n-ого вхождения заданной подстроки в строке
• Функция SUBSTR возвращает подстроку по заданной позиции из исходной строки
• Функция REPLACE заменяет все вхождения искомого элемента в исхожной строке и возвращает новое значение
• Функция MOD выполняет операцию деления с остатком и возвращает остаток от деления
• Численная функция ROUND округляет заданное значение вверх или вниз с указанной точностью
• Функция SYSDATE часто используется при запросах в таблицу DUAL для получения текущего системного времени и даты
• Разница между двумя датам это всегда число, которое представляет из себя количество дрей между двумя датами
• Функция MONTHS_BETWEEN вычисляет количество месяцев между двумя датами, и она основана на допущении что в неопределённом месяце 31 день
• Функция LAST_DAY вычисляет последний день месяца получаемого из заданной даты

 

Описание функций конвертации

• Явная конвертация происходит при вызове функций типа TO_CHAR для изменения типа данных. Функция TO_CHAR преобразует дату и число в строку
• Строки явно преобразуются в дату используя функцию конвертации TO_DATE
• Строки явно преобразуются в число используя функцию TO_NUMBER

 

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

• Функция TO_CHAR возвращает значение типа VARCHAR2
• Маска (или модель) форматирования устанавливает шаблон которой строка должна удовлетворять для корректного и целостного корвентирования значения в элемент типа ДАТА
• Элементы строки, такие как месяц или день полученные из даты с помощью функции TO_CHAR автоматически обрамляются пробелом, но такое поведение можно отменить используя параметр fm
• В функции TO_DATE можно использовать параметр fx который требует точного совпадения формата значения заданной маске

 

Использование выражений ветвления в команде SELECT

• Вложенные функции используют результат вычисления одной функции как входной параметр для другой
• Функция NVL возвращает либо исходное значение, либо альтернативное значение если исходный элемент равен NULL
• Функция NVL2 возвращает новый элемент if-null если исходное значение проверяемного параметра равно NULL инче возвращает альтернативное значение if-not-null
• Функция NULLIF проверяет два элемента на идентичность. Если они одинаковы, то функция возвращает NULL иначе первый из сравниваемых элементов
• Фунция COALESCE возвращает первое не-NULL значение из списка параметров. Если все параметры NULL, то возвращается NULL
• Функция DECODE и выражение CASE используются для реализации IF-THEN-ELSE логики.

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

 

Вложенные функции

 

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

 

Function1(parameter1, parameter2, …) = result

 

Замена параметра функции на вызов другой функции может привести к появлению выражений вида

 

F1( param1.1, F2( param2.1, param2.2, F3( param3.1)), param1.3)

 

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

1. Вычисляется функция F3(param1) и возвращаемое значение используется как третий параметр для функции 2, назовём его param2.3
2. Затем вычисляется функция F2(param1, param2.2, param2.3) и возвращаемое значение используется как второй параметр функции F1 – param1.2
3. И наконец вычисляется функция F1(param1, param2, param1.3) и результат возвращается в вызывающую программу.

Таким образом функция F3 находится на третьем уровне вложенности.

Рассмотрим запрос

 

select next_day(last_day(sysdate)-7, ‘tue’) from dual;

 

1. В этом запросе три функции, от нижнего уровня к верхнему – SYSDATE, LAST_DAY, NEXT_DAY. Запрос выполняется следующим образом
2. Выполняется самая вложенная функция SYSDATE. Она возвращает текущее системное время. Предположим, что текущая дата 28 октября 2009 года
3. Далее вычисляется результат функция второго уровня LAST_DAY. LAST_DATE(’28-OCT-2009’) возвращает последний день окбября 2009 года, то есть значение 31 октябрая 2009.
4. Затем происходит вычитания из этой даты семи дней – получается 24 октября.
5. И наконец вычисляется функция NEXT_DAY(’24-OCT-2009’, ‘tue’), и запрос возвращает последний вторник октября – что в нашем примере 27-OCT-2009.

 

Tip

Достаточно сложно разбираться и строить сложные выражения используя много вложенных вызовов функций, но это приходит со временем и практикой. Можно разбивать такие выражения на части и тестировать отдельно. Таблица DUAL очень полезно для тестирования з.апросов и результатов вызова функций. Можно тестировать и отлаживать небольшие компоненты, которые затем совмещать в одно большое нужное выражение.

 

Функции ветвления

 

Функции ветвления, также известные как ЕСЛИ-ТО-ИНАЧЕ, используется для определения пути выполнения в зависимости от каких-либо обстоятельств. Функции ветвления возвращают разные результат основываясь не результате вычисления условия. В группе таких функций выделяют функции работы со значением NULL: NVL, NVL2, NULLIF и COALESCE. И также общие функции, представленные функцией DECODE и выражением CASE. Функция DECODE является Oracle функцией, тогда как CASE выражение присутствует в стандарте ANSI SQL.

 

Функция NVL

 

Функция NVL проверяет значение столбца или выражения любого типа данных на значение NULL. Если значение NULL – она возвращает альтернативное не-NULL значение по умолчанию, иначе возвращается исходное значение.

У функции NVL два обязательных параметра и синтаксис NVL(original, ifnull) где original это исходное значение для проверки и ifnull результат возвращаемый функцией если original значение равно NULL. Тип данных параметров ifnull и original должен быть совместим. То есть либо тип данных должен быть одинаковым или должна быть возможность неявной конвертации значений из одного типа в другой. Функция NVL возвращает значение такого же типа данных как тип данных параметра original. Рассмотрим три запроса

 

Query 1: select nvl(1234) from dual;

Query 2: select nvl(null, 1234) from dual;

Query 3: select nvl(substr(‘abc’, 4), ‘No substring exists’) from dual;

 

Так как функции NVL необходимо два параметра, запрос 1 вернёт ошибку ORA-00909: invalid number of arguments. Запрос 2 вернёт 1234 так как проверяется значение NULL и оно равно NULL. Запрос три используется вложенную SUBSTR функцию которая пытается выделить четвёртый символ из строки длиной в три символа, возвращает значение NULL, а функция NVL возвращает строку ‘No sbustring exists’.

 

Tip

Функция NVL очень полезна при работе с числами. Она используется для конвертации NULL значений в 0, чтобы арифметические операции над числами не возвращали NULL

 

Функция NVL2

 

Функция NVL2 предоставляет больше функционала чем NVL, но служит также для обработки значения NULL. Она проверяет значение столбца или выражения любого типа на значение NULL. Если значение не равно NULL, то вовзращается второй параметр, иначе возвращается третий параметр, в отличии от функции NVL, которая в этом случае возвращает исходное значение.

У функции NVL2 три обязательных параметра и синтаксис NVL2(original, ifnotnull, ifnull), где original – это проверяемое значение, ifnotnull значение возвращаемое в случае если original не равно NULL и ifnull значение возвращаемое в случаем если original равно NULL. Типы данных параметров ifnotnull и ifnull должы быть совместимы, и они не могут быть типа LONG. Тип данных возвращаемых функцией NVL2 равен типу данных параметра ifnotnull. Рассмотрим несколько примеров

 

Query 1: select nvl2(1234, 1, ‘a string’) from dual;

Query 2: select nvl2(null, 1234, 5678) from dual;

Query 3: select nvl2(substr(‘abc’, 2), ‘Not bc’, ‘No substring’) from dual;

 

Параметра ifnotnull в запросе 1 это число, а параметр ifnull – это строка. Так как типы данных несовместимы, возвращается ошибка “ORA-01722: invalid number”. Запрос два возвращает ifnull параметр, так как original равно NULL и результатом будет 5678. Запрос три использует функция SUBSTR которая возвращает ‘bc’ и происходит вызов NVL2(‘bc’,’Not bc’,’No substring’) – который возвращает ifnotnull параметр – ‘Not bc’.

 

Функция NULLIF

 

Функция NULLIF проверяет два значения на идентичность. Если они одинаковы – возвращается NULL иначе возвращается первый параметр. У функции NULLIF два обязательных параметра и синтаксис NULLIF(ifunequal, comparison_item). Функция сравнивает два параметра и если они идентичны – возвращается NULL, иначе параметр ifunequal. Рассмотрим запросы

 

Query 1: select nullif(1234, 1234) from dual;

Query 2: select nullif(’24-JUL-2009′, ’24-JUL-09′) from dual;

 

Запрос один возвращает NULL так как параметры идентичны. Строки в запросе 2 не конвертируются в дату, а сравниваются как строки. Так как строки разной длины – возвращается параметра ifunequal 24-JUL-2009.

На рисунке 10-4 функция NULLIF вложена в функцию NVL2. В функции NULLIF в свою очередь используются функции SUBSTR и UPPER как части выражения в параметре ifunequal. Столбец EMAIL сравнивается с этим выражением, возвращающем первую букву имени, объединённую с фамилией для сотрудников у которых имя длиной в 4 символа. Когда эти значения равны, NULLIF вернёт NULL, иначение вернёт значение параметра ifunequal. Эти значения используюся как параметр для функции NVL2. NVL2 в свою очередь возвращает описание совпадали ли сравниваемые элементы или нет.

Рисунок 10-4 – Использование функции NULLIF

 

Функция COALESCE

 

Функция COALESCE возвращает первое значение не равное NULL из списка параметров. Если все параметры равны NULL, то возвращается NULL. У функции COALESCE два обязательных параметра и сколько угодно необязательных параметров и синтаксис COALESCE(expr1, expr2, …, exprn) где результатом будет expr1 если значение expr 1не NULL, иначе результатом будет expr2 если оно не NULL и т.д. COALESCE равно по смыслу вложенным функциям NVL

 

COALESCE(expr1, expr2) = NVL (expr1, expr2)

COALESCE(expr1, expr2, expr3) = NVL(expr1,NVL(expr2, expr3))

 

Тип данных возвращаемого значение если найдено не NULL значение равен типу данных первого не NULL значения. Для того чтобы избежать ошибки ‘ORA-00932: inconsistent data types’ все не NULL параметры должны быть совместимы с первым не NULL параметром. Рассмотрим три примера

 

Query 1: select coalesce(null, null, null, ‘a string’) from dual;

Query 2: select coalesce(null, null, null) from dual;

Query 3: select coalesce(substr(‘abc’, 4), ‘Not bc’, ‘No substring’) from dual;

 

Запрос 1 возвращает четвёртый параметр: строку, так как это первый не NULL параметр. Запрос два возвращает NULL так как все параметры равны NULL. Запрос 3 вычисляет первый параметр, получает значение NULL и возвращает второй параметр, так как он первый не NULL параметр.

 

Exam tip

Параметры функции NVL2 могут запутать если вы уже знакомы с функцие NVL. NVL(original, ifnull) возвращает original если значение не NULL, иначе ifnull. NVL2(original, ifnotnull, ifnull) возвращает ifnotnull если значение original не равно NULL иначе ifnull. Путаница происходит из-за того, что второй параметра функции NVL ifnull, тогда как у NVL2 это ifnotnull. Так что не надейтесь на позицию параметра в функции.

 

Функция DECODE

 

Функция DECODE реализует if-then-else логику проверяя первые два параметра на равенство и возвращая третье значение в случае их равенства или другое значение в случае неравенства. У функции DECODE три обязательных параметра и синтаксис DECODE(expr1, comp1, iftrue1, [comp2, iftrue2 … [compN, iftrueN], [iffalse]). Эти параметры используются как показано в слеующем примере псевдокода

 

IF expr1=comp1 then return iftrue1

Else if expr1=comp2 then return iftrue2

…

…

Else if exprN=compN then return iftrueN

Else return NULL|iffalse;

 

Вначале expr1 сравнивается с comp1. Если они равны возвращается значение iftrue1. Если expr1 не равно comp1, то что происходит дальше зависит от того заданы ли параметры comp2 и iftrue2. Если заданы, тов значение expr1 сравнивается с comp2. Если значения равны, то возвращается iftrue2. Если нет, то если есть пары параметров compN, iftrueN происходит сравнение expr1 и compN и в случае равнества возвращается iftrueN. Если не было найдено совпадение ни в одном наборе параметров, то возвращается или iffalse если этот параметр был задан, или NULL.

Все параметры в функции DECODE могут быть выражениями. Тип возвращаемого значения равен типу первого проверяющего элемента – параметра comp1. Выражение expr1 неявно преобразуется к типу данных параметра comp1. Все остальные доступные параметры comp1 … compN также неявно преобразуются к типу comp1. DECODE рассматривает значение NULL как равное другому значению NULL, т.е. если expr1 is NULL и comp3 is NULL, а comp2 не NULL, то возвращается значение iftrue3. Рассмотрим несколько примеров

 

Query 1: select decode(1234, 123, ‘123 is a match’) from dual;

Query 2: select decode(1234, 123, ‘123 is a match’, ‘No match’) from dual;

Query 3: select decode(‘search’, ‘comp1’, ‘true1’, ‘comp2’, ‘true2’, ‘search’, ‘true3’, substr(‘2search’, 2, 6), ‘true4’, ‘false’) from dual;

 

Запрос один сравнивает значение 1234 и 123. Так как они не равны то iftrue1 игнорируется и так как не определено значение iffalse то возвращается NULL. Запрос два идентичен запросу 1 за тем исключением что значение iffalse определено. Так как 1234 не равно 123 то возвращается iffalse – ‘No match’. Запрос три проверяет значения параметров на совпадения значению search. Параметры comp1 и comp2 не равны ‘search’ поэтому результаты iftrue1 и iftrue2 пропускаются. Совпадение найдено в третьей операции сравнения элемента comp3 (позиция параметра 6) и возвращается значение iftrue3 (параметр 7) которое равно ‘true3’. Так как совпадение найдено больше вычисления не производятся. То есть несмотря на то что значение comp4 (параметр 8) также совпадает с expr1 – это выражение никогда не рассчитывается так как совпадение было найдено в предыдущем сравнении.

 

Выражение CASE

 

Все языки программирования третьего и четвертого поколения реализуют конструкцию case. Как и функция DECODE, выражение CASE позволяет реализовывать if-then-else логику. Доступны два варианта использования выражения CASE. Простое CASE выражение устанавливает исходный элемент для сравнения единожды, а затем перечисляет все необходимые условия проверки. Сложный (searched) CASE вычисляет оба оператора для каждого условия.

У выражения CASE три обязательных параметра. Синтаксис выражения зависит от типа. Для простого CASE выражения он выглядит так

 

CASE search_expr

WHEN comparison_expr1 THEN iftrue1

[WHEN comparison _expr2 THEN iftrue2

…

WHEN comparison _exprN Then iftrueN

ELSE iffalse]

END

 

Выражение заключается в CASE … END блок и должно иметь хотя бы один WHEN … THEN элемент. В своей простейшей форме с одинм WHEN … THEN элементом параметр search_expr сравнивается с comparison_expr1 и, если они равны, возвращается iftrue1. Если нет, то возвращается значение NULL если не указан элемент ELSE. Если ELSE присутствует в выражении, то возвращается значение iffalse. Если в выражении больше чем один WHEN … THEN, то происходит сравнение значения search_expr пока не будет найдено совпадение.

Параметрами search_expr, comparison и iftrue могут быть значения столбца, выражения или литералы, но тип данных должен быть одинаковым. Рассмотрим следующий запрос

 

select

case substr(1234, 1, 3)

when ‘134’ then

‘1234 is a match’

when ‘1235’ then

‘1235 is a match’

when concat(‘1′, ’23’) then

concat(‘1′, ’23’)||’ is a match’

else

‘no match’

end

from dual;

 

Параметр search_expression рассчитываеся в функции SUBSTR и получается значение ‘123’. Первый WHEN … THEN сравнивает 134 и 123. Так как они не равны, проверяется следующий WHEN…THEN и сравнивается 123 с 12345 и значения снова не одинаковы. Третий WHEN … THEN сравнивает значение ‘123’ с выражением CONCAT(‘1’,’23’), которое даёт результат ‘123’. Сравниваемые значения равны, и возвращается результат третьего выражения ‘123 is a match’.

На рисунке 10-5 выполняется запрос, который выбирает столбцы LAST_NAME и HIRE_DATE из таблицы EMPLOYEES где значение DEPARTMENT_ID равны 10 или 60, а также два выражения и выражение CASE.

Рисунок 10-5 – сложное CASE выражение

 

Преположим что текущая дата 22 апреля 2016 года. Выражение с псевдонимом YEARS возвращает округленное в нижнюю сторону значения равного количеству месяцев с момента приёма на работу до текущей даты, разделённое на 12. Пять категорий лоялности сотрудника определены в зависимости от значения количества месяцев, проработанных в компании, делённых на число 60. Это значение формирует параметр condition1 для выражения CASE. Ни одна из строкв таблице не удовлетворяет первому условию, но удовлетворяет некоторым другим. Такие выражения называются сложными (searched) и синтаксис такого выражения

 

CASE

WHEN condition1 THEN iftrue1

[WHEN condition2 THEN iftrue2

…

WHEN conditionN THEN iftrueN

ELSE iffalse]

END

 

Сложное выражение заключается в конструкцию CASE … END и состоит минимум из одного WHEN…THEN блока. В простейшей форме с одним блоком WHEN…THEN вычисляется condition1 и если значение возвращает логическое ДА, то возвращается iftrue, иначе возвращается NULL если не указан ELSE iffalse, при наличии которого вместо NULL возвращается iffalse. Если в CASE выражении больше чем один блок WHEN…THEN то вычисление происходит пока не будет найдено совпадение. Запрос для получения результата, похожего на представленный на рисунке 10-5

 

select last_name, hire_date,

trunc(months_between(sysdate, hire_date)/12) years,

trunc(months_between(sysdate, hire_date)/60) «Years divided by 5»,

case

when trunc(months_between(sysdate, hire_date)/60) < 1 then ‘Intern’

when trunc(months_between(sysdate, hire_date)/60) < 2 then ‘Junior’

when trunc(months_between(sysdate, hire_date)/60) < 3 then ‘Intermediate’

when trunc(months_between(sysdate, hire_date)/60) < 4 then ‘Senior’

else

‘Furniture’

end Loyalty

from employees

where department_id in (60,10);

 

Чтобы убедиться что CASE вычисляет значения только до первого совпадения можно выполнить запрос

 

select case ‘A’

when ‘A’ then

‘X’

when ‘A’ then

‘X1’

else

‘X3’

end as f1

from dual;

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

 

Exam tip

Понимание широко используемых масок форматирования проверяется достаточно просто вопросами вида: какой результат вернёт вызов функций TO_CHAR(TO_DATE(’01-JAN-00′,’DD-MON-RR’),’Day’).

 

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

 

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

 

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

 

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

Таблица 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»

FROM 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 требует обязательного совпадения строки и маски форматирования. Если строка не совпадает с маской – возвращается ошибка. Рассмотрим несколько примеров

 

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

 

Exam tip

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

Функции, которые мы обсудим в этой части обычно используют встроенные PL/SQL код, сгруппированный в пакеты и поставляемый Oracle. Некоторые обрабатывают численные, символьные значения и значения даты, другие преобразуют данные в различные типы данных. Функции могут использовать вложенные вызовы и некоторые функции предназначены для работы со значением NULL. Функции условия CASE и DECODE позволяют отображать различный результат в зависимости от значений данных, что предоставляет возможность ветвления в контексте SQL запроса

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

 

Определение функции

 

Функция – это программа, которая может принимать (но необязательно) входные параметры, выполнять какие-либо операции и возвращать значение-литерал. Функция возвращает только одно значение за вызов.

Три важных компонента формируют базис определения функции. Первый – это список входных параметров. Он определяет ноль или более параметров, которые могут передаваться функции для обработки. Эти параметры, или аргументы, могут быть необязательными (иметь значение по умолчанию) и быть разными типами данных. Второй компонент – это тип данных вовзращаемого результата. После выполнения, только одно значение предопределенного типа данных возвращается функцией. Третий компонент инкапсулирует детали обработки выполняемой функцией и содержит программный код, который работает с входными параметрами, производит вычисления и возвращает значение.

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

Вызовы функций могут быть вложенными, к примеру, как F1(x, y, F2(a, b), z), где функция F2 принимает два входных параметра и возвращает третий из четырёх параметров для функции F1. Функции могут работать с любыми типами данных: наиболее часто используемые это символьные и числовые данные, а также данные типа дата. Этими параметрами функции могут быть столбцами или выражениями.

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

 

Типы функций

 

Функции можно глобально разделить на две категории: обрабатывающие строку (строчные функции) и обрабатывающие набор строк (функции группировки). Это выделение очень важно для понимания контекста где используются различные функции.

 

Строчные функции

 

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

Следующий запрос выбирает два столбца из таблицы REGIONS и выражение использующее функцию LENGTH и столбец REGION_NAME

 

select region_id, region_name, length(region_name) from regions;

 

Длина значения столбца REGION_NAME рассчитывается для каждой из четырёх строк в таблице REGIONS; функция выполняется четыре раза, возвращая каждый раз значение-литерал.

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

Помимо использования функций в разделе SELECT строчные функции можно использовать в разделах WHERE и ORDER BY.

 

Функции, работающие с набором данных

 

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

 

Использование функций, изменяющих регистр

 

Данные в таблицах могут заполняться из различных источников: программ, криптов и так далее. Не стоит полагаться что символьные данные будут вводиться в заранее определенном регистре. Строчные функции, изменяющие регистр предназначены для двух важных задач. Их можно использовать, во-первых, для изменения регистра данных при сохранении или выводе информации, либо в условиях WHERE для более гибкого поиска. Гораздо легче искать строку используя фиксированный регистр, вместро проверки всех комбинаций верхнего и нижнего регистра. Помните, что вызов функций не изменяет данные, которые хранятся в таблице. Они преобразуют данные результата запроса.

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

 

Функция LOWER

 

Функция LOWER заменяет все символы прописного регистра на эквивалентные символы строчного регистра. Синтакис функции LOWER(string). Рассмотрим пример запроса использующего эти функции

 

select lower(100+100), lower(‘SQL’), lower(sysdate) from dual

 

Преположим что текущая дата 17 декабря 2015 года. Результатом запроса будут строки ‘200’, ‘sql’ и ‘17-dec-2015’.  Численное выражение и дата неявно преобразуются в строку перед вызовом функции LOWER.

В следующем примере функция LOWER используется для поиска строк где буквы ‘U’ и ‘R’ в любом регистре идут друг за другом

 

select first_name, last_name, lower(last_name) from employees

where lower(last_name) like ‘%ur%’;

 

Можно написать аналогичный запрос без использования функции LOWER. Например так

 

select first_name, last_name from employees

where last_name like ‘%ur%’ or last_name like ‘%UR%’

or last_name like ‘%uR%’ or last_name like ‘%Ur%’

 

Этот запрос работает, но он слишком громоздкий, и количество операторов OR возрастает экспоненциально по мере увеличения строки.

 

Функция UPPER

 

Функция UPPER логическая противоположность функции LOWER и заменяет все строчные символы на их прописные эквиваленты. Синтаксис функции – UPPER(string). Рассмотрим пример

 

select * from countries where upper(country_name) like ‘%U%S%A%’;

 

Этот запрос выбирает строки из таблцы COUNTRIES где COUNTRY_NAME содержит буквы ‘U’, ‘S’, ‘A’ в любом регистре в этом порядке.

 

Функция INITCAP

 

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

 

select initcap(‘init cap or init_cap or init%cap’) from dual

 

Результатом этого запроса будет строка Init Cap Or Init_Cap Or Init%Cap

 

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

 

Функции работы со строками одна из самых мощных возможностей, предоставляемых Oracle. Они очень полезны и понятны практически без детальных объяснений и очень часто используются разными программистами при обработке данных. Часто используются вложенные вызовы этих функций. Оператор конкатенации может использоваться вместо функции CONCAT. Функции LENGTH, INSTR, SUBSTR и REPLACE могут дополнять друг друга, так же как RPAD, LPAD и TRIM.

 

Функция CONCAT

 

Функция CONCAT объединяет два литерала, столбца или выражения для составление одного большого выражения. У функции CONCAT два входных параметра. Синтаксис функции CONCAT(string1, string2) где string1 и string2 могут быть литералом, столбцом или выражением результат которого символьный литерал. Следующий пример показывает использование функции CONCAT

 

select concat(‘Today is:’,SYSDATE) from dual

 

Второй параметр функции это функция SYSDATE, которая возвращает текущее системное время. Значение преобразуется в строку и к ней присоединяется первый параметр. Если текущая системная дата 17 Декабря 2015 года, то запрос вернёт строку ‘Today is:17-DEC-2015’.

Рассмотрим как использовать функция для объединения трех элементов. Так как функция CONCAT может принимать только два входных параметра, то можно объединить только два элемента. В таком случае можно использовать вызов функции как параметр другово вызова функции. Тогда запрос будет выглядеть так

 

select concat(‘Outer1 ‘, concat(‘Inner1′,’ Inner2′)) from dual;

 

У первой функции два параметра: первый параметр это литерал ‘Outer1 ‘, а второй параметра это вложенная функция CONCAT. Вторая функция принимает два параметра: литерал ‘Inner1’ и литерал ‘ Inner2’. Результатом выполнения этого запроса будет строка ‘Outer1 Inner1 Inner 2’. Вложенные функции расмотрим чуть позже.

 

Функция LENGTH

 

Функция LENGTH возвращает число символов которые составляют строку. Пробелы, табуляция и специальные символы учитываются функцией LENGTH. У функции один параметра и синтаксис LENGTH(string). Рассмотрим запрос

 

select * from countries where length(country_name) > 10;

 

Функция LENGTH используется для выбора тех стран у которых длина названия больше чем десять символов.

 

Функции RPAD и LPAD

 

Функции RPAD и LPAD возвращают строку фиксированной длины и при необходимости дополняют исходное значение определенным набором символов слева или справа. Символами используемые для добавления могут быть литерал, значение столбца, выражение, пробел (значение по умолчанию), табуляция и спец символы. Функции LPAD и RPAD принимают три входных параметра и синтаксис LPAD(s, n, p) и RPAD(s, n, p) где s – значение строки для обработки, n – количество символов результата и p – символы для добавления. Если используется LPAD, то символы p добавляются слева до достижения длины n. Если RPAD – то справа. Обратите внимание что если длина s больше чем длина n – то результатом будет первые n символов значения s. Рассмотрим запросы на рисунке 10-1

Рисунок 10-1 – Использование функций RPAD и LPAD

 

Первый запрос не изменяет данные и результат не очень читабельный по сравнению с результатом второго запроса. RPAD используется для добавления пробелов там где необходимо для first_name и last_name чтобы все значения были фиксированной длины в 18 символов, и LPAD используется для добавления пробелов в начало значения salary до достижения длины 6 символов.

 

Функция TRIM

 

Функция TRIM убирает символы и начала или окончания строки чтобы сделать её потенцильно короче. Функция принимает обязательный параметр и необязательный. Синтаксис функции TRIM([trailing|leading|both] trimstring from string). Параметр входная строка (s) обязательный. Следующие пункты перечисляют параметры

• TRIM(s) убираются пробелы в начале в к конце строки
• TRIM(trailing trimstring from s) убирает символы trimgstring в конце строки
• TRIM(leading trimstring from s) убирает символы trimgstring в начале строки
• TRIM(both trimstring from s) OR TRIM(trimstring from s) убирают все символы trimstring в начале и в конце строки

 

Запрос

 

select trim(both ‘*’ from ‘****Hidden****’),

trim(leading ‘*’ from ‘****Hidden****’),

trim(trailing ‘*’ from ‘****Hidden****’) from dual;

 

Вернёт “Hidden”, “Hidden****”, и “****Hidden”. Обратите внимание что, указав всего один символ, все символы убираются если они последовательно повторяются.

Функция INSTR

 

Функция INSTR ищет подстроку в строке. Возвращается число, обозначающее позицию откуда n-ное вхождение начинается, начиная с позиции поиска, относительно начала строки. Если подстрока не найдена в строке – возвращается 0.

У функции INSTR два параметра обязательных и два параметра необязательных. Синтаксис функции INSTR(source string, search string, [search start position], [n occurrence]). Значение по умолчанию для search start position=1 или другими словами начало строки source string. Значение по умолчанию для n occurrence=1 или первое вхождение. Рассмотрим несколько примеров

 

Query 1: select instr(‘1#3#5#7#9#’, ‘#’) from dual;

Query 2: select instr(‘1#3#5#7#9#’, ‘#’ ,5) from dual;

Query 3: select instr(‘1#3#5#7#9#’, ‘#’, 3, 4) from dual;

 

Первый запрос ищет первое вхождение хеш-тега в строке и возвращает значение 2. Второй запрос ищет хеш-тег в строке начиная с пятого символа и находит первое вхождение с 6 символа. Третий запрос ищет четвертое вхождение хеш-тега начиная с третьего символа и находит его в позиции 10.

 

Функция SUBSTR

 

Функция SUBSTR возвращает подстроку определённой длины из исходной строки начиная с определённой позиции. Если начальная позиция больше чем длина исходной строки – возвращается значение NULL. Если длины исходной строки недостаточно для получения значения необходимой длины начиная с определённой позиции, то возвращается часть строки с исходного символа до конца строки.

У функции SUBSTR три параметра, первые два обязательны и синтаксис SUBSTR(source string, start position, [number of characters]). Значение по умолчанию для characters to extract = разница между длиной source string и start position. Рассмотрим следующие примеры

 

Query 1: select substr(‘1#3#5#7#9#’, 5) from dual;

Query 2: select substr(‘1#3#5#7#9#’, 5, 3) from dual;

Query 3: select substr(‘1#3#5#7#9#’, -3, 2) from dual;

 

Запрос 1 возвращает подстроку начиная с позиции 5. Так как третий параметр не указан, количество символов равно длине исходной строки минус начальная позиция и будет равно шести. Первый запрос вернёт подстроку ‘5#7#9#’. Запрос два возвращает три символа начиная с пятого символа и строка результат будет ‘5#7’. Запрос три начинается с позиции минус три. Отрицательная начальная позиции говорит Oracle о том, что начальная позиция рассчитывается от конца строки. Таким образом начальная позиция будет длина строки минус три и равна 8. Третий параметр равен двум и возвращается значение ‘#9’.

 

Функция REPLACE

 

Функция REPLACE заменяет все вхождения искомого элемента на значение строки для подстановки. Если длина заменяемого элемента не равна длине элемента, на который происходит замена, длина получаемой строки будет отличной от исходной строки. Если искомая подстрока не найдена, строка возвращается без изменений. Доступно три параметра, два первых обязательные и синтаксис вызова REPLACE(source string, search element, [replace element]). Если явно не указать параметр replace element, то из исходной строки удаляются все вхождения search element. Другими словами, replace element равно пустой строке. Если все символы исходной строки заменяются пустым replace element возвращается NULL. Рассмотрим несколько запросов

 

Query 1: select replace(‘1#3#5#7#9#’,’#’,’->’) from dual

Query 2: select replace(‘1#3#5#7#9#’,’#’) from dual

Query 3: select replace(‘#’,’#’) from dual

 

Хеш в первом запрос обозначает символ для поиска и строка для замены ‘->’. Хеш появляется в строке пять раз и заменяется, получаем итоговую строку ‘1->3->5->7->9->’. Запрос 2 не указывает явно строку для замены. Значением по умолчанию является пустая строка и результатом будет ‘13579’. Запрос номер три вернёт NULL.

 

Использование численных функций

 

В Oracle доступно множество встроенных функций для работы с числами. Существенной разницой между численными функция и другими является то, что эти функции принимают параметрами только числа и возвращают только числа. Oracle предоставляет численные функции для работы с тригонометрическими, экспоненциальными и логарифмическими выражениями и со многими другими. Мы сфокусируемся на простых численных строчных функциях: ROUND, TRUNC и MOD.

 

Функция ROUND

 

Функция ROUND округляет число в зависимости от необходимой точности. Возвращаемое значение округляется либо в большую, либо в меньшую сторону, в зависимости от значения последней цифры в необходимом разряде. Если значение точности n, то цифра, которая будет округляться будет на позиции n после запятой, а значение будет зависеть от цифры на позиции (n+1). Если значение точности отрицательное, то все цифры после разряда n слева от запятой будут 0, а значение n будет зависеть от n+1. Если значение цифры от которой зависит округление больше или равно 5, то округление происходит в большую сторону, иначе в меньшую.

Функция ROUND принимает два входных параметра и синтаксис ROUND(source number, decimal precision). Source number может быть любым числом. Параметр decimal precision определяет необходимую точность и необязателен. Если этот параметр не указан, значение по умолчанию будет 0, что обозначает необходимость округления до ближайшего целого числа.

Рассмотрим таблицу 10-1 для числа 1601.916. Отрицательные значения точности находятся слева от точки (целая часть), когда положительные считаются вправо от точки (дробная часть).

 

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

 

Query 1: select round(1601.916, 1) from dual;

Query 2: select round(1601.916, 2) from dual;

Query 3: select round(1601.916, -3) from dual;

Query 4: select round(1601.916) from dual;

 

Первый запрос использует параметр точности равные единице, что означает что число будет округлено до ближайшей десятой. Так как значение сотой части равно единице (меньше чем 5), то происходит округление в меньшую сторону и возвращается значение 1601.9. Точность второго запроса равна двойке, таким образом значение окружается до сотой. Так как значение тысячной части равно 6 (что больше 5), то значение сотой части округляется вверх и возвращается значение 1601.92. Значение параметра точности в третьем запросе равно минус трём. Так как значение отрицательное, это значит, что округление будет происходить, основываясь на значении третьей позиции слева от точки, во втором разряде (сотни), и значение 6. Так как 6 больше пяти, то происходит округление вверх и возвращается значение 2000. Запрос 4 вызывает функцию без параметра точности. Это означает что число округляется до ближайшего целого. Так как десятая часть равна 9, то значение округляется в большую сторону и возвращется значение 1602.

 

Численная функция TRUNC

 

Функия TRUNC сокращает значение числа основываясь на значение параметра точности. Сокращение отличается от округления тем, что при сокращении лишняя часть просто отрезается и не происходит никаких изменений остальных цифр числа. Если значение точности отрицательное, то входное значение сокращается на позиции слева от запятой. Синтаксис функции TRUNC(source number, decimal precision). Параметром source number может быть любое число и этот параметр обязателен. Параметр decimal precision определяет позицию округления и не обязателен, значением по умолчанию будет ноль, что означает сокращение до целого числа.

Если значение decimal precision равно одному, то число сокращается до десятых, если два, то до сотых и так далее. Рассмотрим несколько примеров использования этой функции

 

Query 1: select trunc(1601.916, 1) from dual;

Query 2: select trunc(1601.916, 2) from dual;

Query 3: select trunc(1601.916, -3) from dual;

Query 4: select trunc(1601.916) from dual;

 

В запросе 1 используется точность равная единице, что значит сокращение значения до десятых и возвращается значение 1601.9. Точность во втором запросе равна двум, исходное значение сокращается до сотых и возвращается значение 1601.91. Обратите внимание что получаемое значение будет отличаться от значения, возвращаемого функцией ROUND с такими же параметрами, так как при вызове ROUND произойдёт округление в большую сторону (6 больше 5). В запросе номер три используется отрицательное число как значение параметра точности. Позиция три слева от запятой означает что сокращение будет до третьего разряда (сокращаются сотни) как показано в таблице 10-1 и возвращаемое значение будет 1000. И наконец в четвертом запросе явно неуказано значение точности и сокращается дробная часть исходного числа. Результатом будет 1601.

 

 

 

 

Функция MOD

 

Функция MOD возвращает остаток от деления. Два числа, делимое (число которое делится) и делитель (число на которое делится) определяются как параметры и вычисляется операция деления. Если делимое делится на делитель нацело, то возвращается ноль, так как нет остатка. Если делитель ноль, то не происходит ошибки деления на ноль, а возвращается делимое. Если делитель больше чем делимое, возвращается делимое.

У функции MOD два входные параметра и синтаксис MOD(dividend, divisor). Параметры dividend и divisor могут быть численными литералами, столбцами или выражениями и могут быть положительными или отрицательными. Следующие примеры показывают использование этой функции

 

Query 1: select mod(6, 2) from dual

Query 2: select mod(5, 3) from dual

Query 3: select mod(7, 35) from dual

Query 4: select mod(5.2, 3) from dual

 

В запросе один 6 делится на два нацело без остатка и возвращается 0. В запросе два 5 делится на 3, целая часть будет 1 и возвращается остаток 2. В запросе номер три семь делится на 35. Так как делитель больше чем делимое – возвращается делимое, т.е. целая часть 0. Запрос четыре использует дробное число как делимое. Целой частью будет один и остаток будет 2.2.

 

Tip

Любое чётное число делится на два без остатка, любое нечётное число при делении на два вернёт остаток 1. Поэтому функцию MOD часто используют чтобы отличать чётные и нечётные числа.

 

Работа с датами

 

Функции работы с датами предлагают удобный способ решать задачи, связанные с датами без необходимости учитывать высокосные года, сколько дней в конкретном месяце. Вначале рассмотрим, как хранятся данные типа дата и форматирование даты, а также функцию SYSDATE. Затем рассмотрим функции ADD_MONTHS, MONTHS_BETWEEN, LAST_DAT, NEXT_DAY, ROUND и TRUNC.

 

Хранение даты в базе данных

 

База данных хранит данные как число, которое способно поддерживать расчёт века, года, месяца и дня, а также информации о времени, такой как час, минута и секунда. Когда происходит запрос к данным, на число накладывается определённое форматирование (маска), и по умолчанию маска отображает день, три первых буквы названия месяца и две цифры, отображающие год.

 

Функция SYSDATE

 

Функция SYSDATE не использует входные параметры и возвращает текущее время и дату установленную на сервере БД. По умолчанию функция SYSDATE возвращает дату в формате DD-MON-RR и отображает дату на сервере. Если сервер установлен в другом часовом поясе чем машина клиента, то время и дата, возвращаемые SYSDATE могут отличаться от локальных значений на клиентсой машине. Можно выполнить такой запрос для отображения системной даты на сервере

 

select sysdate from dual

 

Арифметика над датами

 

Следуещее уравнение отображает важный принцип при работе с датами

Date1 – Date2 = Num1

Дата может вычитаться из другой даты. Разница между двуми датами понимается как количество дней между ними. Любое число, включая дробные, может быть добавлено или вычтено из даты. В этом контексте число представляет собой количество дней. Сумма или разница между число и датой – это всегда дата. Этот принцип подразумевает что сложение, умножение или деление двух дат невозможен.

 

Функция MONTHS_BETWEEN

 

Функция MONTHS_BETWEEN возвращает количество месяцев между двумя обязательными входными параметрами. Синтаксис функции MONTHS_BETWEEN(date1, date2). Функция рассчитывает разницу между date1 и date2. Если date1 меньше чем date2, то возвращается отрицательное число. Возвращаемое значение может состоять из целой части, отражающей количество месяцев между двумя датами, и дробной части, отражающей сколько дней и часов осталось (основываясь на месяце равном 31 дню) после вычета целого количества месяцев. Целое число вовзращается если день сравниваемых месяцев одинаковый или последний день соответствующего месяца.

Следующие примеры используют функию MONTHS_BETWEEN

 

Query 1: select months_between(sysdate, sysdate-31) from dual;

Query 2: select months_between(’29-mar-2008′, ’28-feb-2008′) from dual;

Query 3: select months_between(’29-mar-2008′, ’28-feb-2008′) * 31 from dual;

 

Преположим что текущая дата 16 Апреля 2009. Запрос один вернёт один как количество месяцев между 16 апреля 2009 и 16 марта 2009. Запрос два неявно конвертирует литералы в даты используя формат DD-MON-YYYY. Так как часть о времени опущена Oracle установит значение времени 00.00.00 для обеих дат. Фукнция вернёт значение примерно равное 1.03225806. Целая часть результата обозначает что между датами один месяц. Между 28 февраля и 28 марта ровно один месяц. Тогда дробная часть должна показывать ровно один день. Результат включает в себя часы минуты и секунды, но в нашем случае временная составляющая дат одинаковая. Умножение 0.03225806 на 31 вернёт 1, так как дробная часть, возвращаемая MONTHS_BETWEEN, рассчитывается, допуская что месяц равен ровно 31 дню. Поэтому запрос номер три вернёт значение 32.

 

Exam tip

Популярной ошибкой является допущение что возвращаемый тип данных функции зависит от типа функции (функции работы с датой должны возвращать дату, функции обработки строк – строку). Это верное только для численных функций. Символьные функции и функции работы с датами могут возвращать значение любого типа данных. Например, INSTR явлется символьной функцией, а MONTS_BETWEEN функцией работы с датой, но обе они возвращают результатом число. Также часто ошибочно рассуждают что разница между датами – это дата, когда фактически это число.

 

Функция ADD_MONTHS

 

Функция ADD_MONTHS возвращает дату, полученную путём добавления определённого количества месяцев к исходной дате. У этой функции два обязательных параметра и синтаксис ADD_MONTHS(start date, number of months). Значение параметра number of months может быть отрицательным, тогда исходное значение будет уменьшаться на это количество месяцев и дробным, но учитываться будет только целая часть. Следующие три запроса показывают использование функции ADD_MONTHS

Query 1: select add_months(’07-APR-2009′, 1) from dual;

Query 2: select add_months(’31-DEC-2008′, 2.5) from dual;

Query 3: select add_months(’07-APR-2009′, -12) from dual;

 

 

Результатом первого запроса буде 7 мая 2009, так как день остаётся одинаковым если это возможно и месяц увеличивается на один. Во втором запросе число месяцев дробное, что игнорируется, то есть этот запроса равен ADD_MONTHS(’31-DEC-2008’,2). Добавление двух месяцев должно вернуть 31-FEB-2009, но такой даты не существует, поэтому возвращается последний день месяца. В последнем примере используется отрицательное число для параметра кол-во месяцев и возвращается дата 07-APR-2008 что на двенадцать месяцев раньше, чем исходное значение.

 

Функция NEXT_DAY

 

Функция NEXT_DATE возвращает следующий ближайший заданный день недели после исходной даты. У этой функции два обязательных параметра и синтаксис NEXT_DAY(start date, day of the week). Функция выичсляет значение, когда заданный day of the week наступит после start date. Параметр day of the week может быть задан как числом, так и строкой. Допустимые значения определяются параметром NLS_DATE_LANGUAGE и по умолчанию используются три первые буквы названия дня недели в любом регистре (SUN, mon etc) или целые числа где 1 равно воскресенью, 2 – понедельник и так далее. Также имена дней недели могут быть более чем три символа; например, воскресенье можно указать как sun, sund, Sunday. Рассмотрим несколько запросов

 

Query 1: select next_day(’01-JAN-2009′, ‘tue’) from dual;

Query 2: select next_day(’01-JAN-2009′, ‘WEDNE’) from dual;

Query 3: select next_day(’01-JAN-2009′, 5) from dual;

 

1 января 2009 года это четверг. Следущий вторник будет через 5 дней, 6 января 2009 года. Второй запрос вернёт 7 января 2009 – следующая среда после 1 января. Третий запрос использует число как параметр и если у вас установлены Американские значения, то пятый день — это четверг. Следующий четверг после 1 января ровно через неделю – 8 января 2009 года.

 

Функция LAST_DAY

 

Функция LAST_DAY возвращает дату последнего дня месяца исходной даты. Эта функция требует один обязательные параметр и синтаксис LAST_DAY(start date). Функция выбирает месяц исходной даты и затем расчитывает последний день месяца. Следующий запрос вернёт 31 января 2009 года

 

select last_day(’01-JAN-2009′) from dual;

 

Функция ROUND для работы с датами

 

Функция ROUND округляет значение даты до заданной точности даты. Возвращаемое значение округляется либо к большему, либо r меньшему значению в зависимости от значения округляемого элемента. Эта функция требует один обязательный параметр и допускает один необязательные и синтаксис функции ROUND(source date, [date precision forma]). Параметром source data может быть любой элемент типа данных дата. Параметр date precision format определяет уровень округления и значение по умолчанию – день. Параметром date precision format может быть век (CC) год YYYY квартал Q месяц M неделя W день DD час HH минута MI.

Округления до века эквивалентно добавление единицы к текущему веку. Округление до месяца будет в большую сторону если день больше 16 иначе будет округление до первого дня месяца. Если месяц от одного до шести округление будет до начала текущего года, иначе вернётся дата начала следующего года. Рассмотрим запрос

Предположим, что этот запрос был выполнен 17 апреля 2009 года в 00:05. Вначале происходит округление текущей даты до дня (параметр точности явно неуказан). Так как время 00:05 то день не округляется в большую сторону.Так как 1 апреля 2009 года это среда, то второй столбец вернёт среду той недели, в которую входит исходная дата. Первая среда недели, в которую входит 19 апреля – это 15 апреля 2009 года. Третий столбец оругляет месяц до следующего (так как 17 больше 16) и возвращает 01 мая 2009. Поледний столбец округляет дату до года и возвращает 1 явнваря 2009 года, так как апрель это 4ый месяц.

 

Функция TRUNC при работе с датами

 

Функция TRUNC сокращает дату основываясь на параметре точности. У этой функции один параметр обязательный и один нет и синтаксис вызова TRUNC(source date, [date precision format]). Параметром source date может быть любая валидная дата. Параметр date precision format определяет уровень сокращения даты и необязателен, значение по умолчанию – сокращение до дня. Это значит что все значения времени обнуляются – 00 часов 00 минут 00 секунд. Сокращение до месяца вернёт дату равную первому дню месяца исходной даты. Сокращение до года – вернёт первый день года исходной даты. Рассмотрим запрос, использующий функцию с разными параметрами

Этот запрос выполнятся 17 апреля в 00:05. Первый столбец сокращает системную дату до дня, время преобразуется из 00:05 в 00:00 (параметр точности явно неуказан, используется значение по умолчанию) и возвращается текущий день. Второй столбец сокращает дату до такого же дня недели, который был первого числа месяца (среда) и возвращает среду текущей недели – 15 апреля. Третий столбец сокращает дату до месяца и возвращает первый день месяца – 1 апреля. Четвертый столбец сокращает дату до года и возвращает первый день года.