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

Функция CONCAT(strl, str2)

Данная функция выполняет конкатенацию строк strl и str2. Если один из аргументов равен NULL, то он воспринимается как пустая строка. Если оба аргумента равны NULL, то функция возвращает NULL. Пример:

SELECT CONCAT("У попа \ "была собака") x1,
CONCATCTest" , NULL) х2,
CONCAT(NULL, "Test") х3,
CONCAT(NULL, NULL) x4
FROM dual

У попа была собака

Для конкатенации строк Oracle поддерживает специальный оператор конкатенации «||», который работает аналогично функции CONCAT, на­пример:

SELECT CONCAT("У попа \ "была собака") x1, "У попа " || "была собака" х2
FROM dual

Не следует путать оператор конкатенации «||», эквивалентный вызо­ву функции CONCAT, и оператор «+», применяемый в арифметических операциях. В Oracle это разные операторы, но за счет автоматического приведения типов возможны трудноуловимые ошибки, например:

SELECT "5" + "3" x1
FROM dual

В данном случае возвращается числовое значение 8, а не текстовая строка «53». Это связано с тем, что, обнаружив арифметическую опе­рацию «+», Oracle автоматически пытается привести аргументы к типу NUMBER.

Функция LOWER(str)

Функция LOWER преобразует все символы строки str в строчные. Пример:

SELECT LOWER("TeXt DATA") X
FROM dual

Функция UPPER(str)

Функция UPPER преобразует все символы строки str в прописные. Пример:

SELECT UPPER("TeXt DATA") X
FROM dual

Функция INITCAP(str)

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

SELECT INITCAPCИваноВ петр сиДорович") X
FROM dual

Функции LTRIM(str [,set]) и RTRIM(str [,set])

Функция LTRIM удаляет все символы с начала строки до первого сим­вола, которого нет в наборе символов set. По умолчанию set состоит из одного пробела и может не указываться. Функция RTRIM аналогич­на LTRIM, но удаляет символы, начиная от конца строки. Рассмотрим несколько примеров:

SELECT LTRIM(" TeXt DATA") X1,
LTRIM(" _ # TeXt DATA", " #_") X2,
LTRIM(" 1234567890 TeXt DATA", " 1234567890") X3
FROM dual

Функция REPLACE(str, search_str, [,replace_str])

Функция REPLACE осуществляет поиск образца search_str в строке str и каждое найденное вхождение заменяет на replace_str. По умолча­нию replace_str равен пустой строке, поэтому вызов функции REPLACE с двумя аргументами приводит к удалению всех найденных вхождений. Поиск подстроки ведется с учетом регистра. Пример:

SELECT REPLACE("У попа была собака", "собака", "кошка") x1,
REPLACE("У попа была злая собака", "злая") х2,
REPLACE("У попа была собака", "Собака", "Кошка") х3
FROM dual

У попа была кошка	У попа была собака	У попа была собака

Функция TRANSLATE(str, from_mask, to_mask)

Функция TRANSLATE анализирует строку str и заменяет в ней все символы, встречающиеся в строке from_mask, на соответствующие сим­волы из to_mask. Для корректной работы функции строки from_mask и to_mask должны иметь одинаковую длину или строка from_mask долж­на быть длиннее, чем to_mask. Если from_mask длиннее, чем to_mask, и в процессе обработки строки str обнаружатся символы, соответствующие одному из символов from_mask, и при этом им не найдется соответствия в to_mask, то такие символы будут удалены из строки str. Если передать from_mask или to_mask, равное NULL, то функция возвратит значение NULL. Сравнение производится с учетом регистра.

SELECT TRANSLATE("Test 12345", "е2\ "Е!") x1,
TRANSLATE("Test 12345", "e234", "E") x2
FROM dual

Данная функция удобна для решения ряда практических задач, свя­занных с перекодировкой символов или с поиском запрещенных симво­лов. Например, необходимо проанализировать пароль и выяснить, содер­жит ли он хотя бы одну цифру. Реализация данной проверки при помощи TRANSLATE имеет вид:

IF TRANSLATE(PassWd, "0123456789", "*") = PassWd THEN
ADD_ERR0R("Ошибка - Пароль должен содержать хотя бы одну цифру!");
RETURN 1;
END IF;

Другой пример: идет подготовка числа к его преобразованию в NUMBER. Необходимо заменить разделители десятичных знаков «,» и «.» на «.» и удалить пробелы. Реализация данной операции при помощи TRANSLATE имеет вид:

SELECT TRANSLATE("123 455,23", "., ", " . . ") x1,
TRANSLATE("-123 455.23", "., ", " . . ") х2
FROM dual

Функция SUBSTR(str, m [,n])

Функция SUBSTR возвращает фрагмент строки str, начиная с символа m длиной n символов. Длину можно не указывать - в этом случае возвра­щается строка от символа m и до конца строки str. Нумерация символов идет с 1. Если указать m = 0, то копирование все равно начнется с первого символа. Задание отрицательного значения m приводит к тому, что симво­лы отсчитываются от конца строки, а не от начала. Задание значений m, превышающих по абсолютному значению длину строки, приводит к тому, что функция возвращает NULL.

SELECT SUBSTR("У попа была собака", 13) x1,
SUBSTR("У попа была собака", -6) х2,
SUBSTR("Эto тестовый текст", 5, 8) х3,
SUBSTR("У попа была собака", 150) х4
FROM dual

		текстовый

Функция INSTR(str, search_str [,n[,m]])

Функция INSTR возвращает позицию первого символа m-ro фрагмента строки str, совпадающего со строкой search_str. Сравнение ведется с n-го символа строки str, при сравнении учитывается регистр. По умолчанию n = m = 1, то есть поиск ведется от начала строки и возвращается позиция первого найденного фрагмента. В случае неуспешного поиска функция возвращает 0.

SELECT INSTR("y попа была собака", "собака") x1,
INSTR("y попа была собака", "кошка") х2,
INSTR("Это текст для демонстрации поиска текста", "текст", 1, 2) х3,
INSTR(‘11111000000001", "1", 7) х4
FROM dual

С данной функций, равно как и со всеми остальными в Oracle, часто допускаются типовые ошибки, связанные с обработкой значения NULL. Если str=NULL, то функция вернет NULL, а не ноль! Это необходимо учитывать при построении различных условий. Например, данный фраг­мент программы на PL/SQL как раз не учитывает эту особенность:

IF INSTR(TXT_VAR, "*") = 0 THEN
...
END IF ;

В данном случае правильно было бы написать так:

IF NVL(INSTR(TXT_VAR, "*"), 0) = 0 THEN
...
END IF;

Функции LENGTH (str) и LENGTHB (str)

Функция LENGTH (str) возвращает длину строки str в символах. Для пустой строки и значения NULL функция возвращает NULL, поэтому сов­местно с данной функцией рекомендуется использовать NVL.

SELECT LENGTH("У попа была собака") x1,
LENGTH("") х2,
LENGTH(NULL) х3,
NVL(LENGTH(""), 0) х4
FROM dual

Функция LENGTHB аналогична функции LENGTH, но возвращает длину строки в байтах.

Функция ASCII(str)

Возвращает ASCII-код первого символа строки str в случае применения кодировки ASCII и значение первого байта многобайтного символа при использовании кодировки на основе многобайтных символов. Пример:

SELECT ASCII("Test") x1 FROM dual

Функция CHR(n)

Возвращает символ по его коду.

SELECT CHR(64) x1
FROM dual

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

1) Функция определения длины строки LENGTH(строка), возвращает количество символов в строке, включая концевые пробелы.

SELECT LENGTH(‘string ’) FROM DUAL вернет значение 7.

2) Функции преобразования регистров символов UPPER(строка), LOWER(строка), INITCAP(строка). Для преобразования символов к верхнему регистру используется функция UPPER().

SELECT UPPER(‘string’) FROM DUAL вернет STRING.

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

SELECT LOWER(‘STrinG’) FROM DUAL вернет string.

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

SELECT INITCAP(‘string1 string2’) FROM DUAL вернет строку String1 String2.

3) Функции для обрезания начальных и концевых пробелов LTRIM(строка), RTRIM(строка), TRIM(строка). Соответственно первая функция обрезает все начальные пробелы строки, вторая – все концевые, а третья все начальные и концевые.

SELECT LTRIM(‘ str1’) FROM DUAL вернет строку str1,
SELECT RTRIM(‘str2 ’) FROM DUAL вернет строку str2,
SELECT TRIM(‘ str3 ’) FROM DUAL вернет строку str3.

4) Функция замены части строки другой строкой REPLACE(исходная_строка, заменяемая_подстрока, заменяющая_подстрока). Для большей ясности рассмотрим пример, в некотором текстовом поле таблицы хранится число. Причем символ-разделитель между целой и дробной частью в некоторых полях «.», а нам для дальнейшей обработки данных нужно, чтобы он во всех полях должен быть «,». Для этого воспользуемся функцией REPLACE следующим образом. REPLACE(field1, ’.’, ’,’) и все символы «.» в поле field будут заменены на символ «,».

SELECT REPLACE(‘My_string’,’_’,’@’) FROM DUAL вернет строку My@string.

5) Функции преобразования данных к другим типам данных. TO_CHAR(число) преобразует число в текст. TO_NUMBER(строка) преобразует текст в число. TO_DATE(строка, формат_даты) преобразует строку в дату определенного формата.

SELECT TO_CHAR(123) FROM DUAL вернет строку 123,
SELECT TO_NUMBER(‘12345’) FROM DUAL вернет число 12345,
SELECT TO_DATE(’01.01.2010’,’dd.mon.yyyy’) FROM DUAL вернет дату 01.JAN.2010.

6) Функция определения вхождения подстроки в строку INSTR(исходная_строка, подстрока, номер_символа). Даная функция позволяет определять номер символа в исходной строке с которого начинается искомая подстрока (если такая есть). Иначе возвращается 0. Например нам нужно определить все должности в таблице Table1, в наименовании которых встречается подстрока «менеджер». Для этого вполне подойдет следующий оператор

SELECT * FROM TABLE1 WHERE INSTR(POST, ‘менеджер’, 1) > 0.

То есть оператор SELECT выведет только те записи из таблицы TABLE1 где искомая подстрока «менеджер» будет найдена. Причем поиск будет осуществляться с первого символа. Если поиск нужно осуществлять с другой позиции, то номер символа для начала поиска указывается в третьем параметре.

SELECT INSTR(‘Small string’, ‘string’, 1) FROM DUAL вернет значение 7,
SELECT INSTR(‘Small string’, ‘String’, 1) FROM DUAL вернет значение 0.

7) Функция выделения в исходной строке подстроки SUBSTR(исходная_строка, номер_начального_символа, количество_символов). Рассмотрим такой пример, в пользовательской таблице хранится адрес в виде наименование населенного пункта, название улицы, номер дома. Причем мы точно знаем, что для наименования населенного пункта отводится строго 20 символов (если наименовании населенного пункта меньше чем 20 символов, то остальная часть заполняется пробелами), для наименования улицы 30 символов, для номера дома 3 символа. Далее нам необходимо перенести все адреса из нашей таблицы в другую и при этом все 3 компонента адреса должны быть в разных полях. Для выделения компонент адреса применим функцию SUBSTR().

SELECT SUBSTR(TABLE_1.ADDRESS, 1, 20) CITY, SUBSTR(TABLE_1.ADDRESS, 21, 30) STREET, SUBSTR(TABLE_1.ADDRESS, 52, 3) TOWN FROM TABLE_1

Конечно для переноса данных необходимо воспользоваться оператором INSERT, но для понимания работы функции SUBSTR вполне подойдет рассмотренный пример.
SELECT SUBSTR(‘My_string’, 4, 3) FROM DUAL вернет строку str.

Рассмотренные выше функции можно использовать во входных параметрах. Так если нам нужно выделить все символы, после какого-то определенного, то в функцию SUBSTR можно передать номер искомого символа из функции INSTR. Например если нужно перенести все символы из поля таблицы, которые расположены после «,» то можно использовать такую конструкцию
SELECT SUBSTR(My_string, INSTR(My_string, ‘,’, 1), LENGTH(My_string)- INSTR(My_string, ‘,’, 1)+1) FROM DUAL.
Для определения начального символа мы вызываем функцию INSTR(), которая вернет номер символа первого вхождения подстрок «,». Далее мы определяем количество символов до конца строки как разницу длины строки и номера первого вхождения подстроки.

8) Для определения кода символа используется функция ASCII(строка), которая возвращает код 1 символа строки. Например

SELECT ASCII(W) FROM DUAL вернет значение 87.

9) Обратная функция преобразования кода символа в символ CHR(число).

SELECT CHR(87) FROM DUAL вернет символ W.

Функции для работы с числами в Oracle.

В СУБД Oracle имеется ряд функций для работы с числами. К ним относятся функции возведение числа в степень POWER(), округление ROUND() и т. д.

1) Функция ABS(число) возвращает абсолютное значение аргумента.
SELECT ABS(-3) FROM DUAL вернет значение 3.

2) Функция CEIL(число) возвращает наименьшее целое, большее или равное переданному параметру.
SELECT CEIL(4.5) FROM DUAL вернет значение 5.

3) Функция FLOOR(число) возвращает наибольшее целое, меньшее или равное переданному параметру.
SELECT FLOOR(3.8) FROM DUAL вернет значение 3.

4) Функция MOD(число_1, число_2) возвращает остаток от деления первого параметра на второй.
SELECT MOD(5, 3) FROM DUAL вернет значение 2. Примечание. Если второй параметр равен 0, то функция возвращает первый параметр.

5) Функция округления ROUND(число_1, число_2). Округляет первый переданный параметр до количества разрядов, переданного во втором параметре. Если второй параметр не указан, то он принимается равным 0, то есть округление производится до целого значения. Примеры
SELECT ROUND(101.34) FROM DUAL вернет значение 101,
SELECT ROUND(100.1268, 2) FROM DUAL вернет значение 100.13
SELECT ROUND(1234000.3254, -2) FROM DUAL вернет значение 1234000,
SELECT ROUND(-100.122, 2) FROM DUAL вернет значение -100.12.

6) Функция усечения значения TRUNC(число_1, число_2). Возвращает усеченное значение первого параметра до количества десятичных разрядов, указанного во втором параметре. Примеры
SELECT TRUNC(150.58) FROM DUAL вернет значение 150
SELECT TRUNC(235.4587, 2) FROM DUAL вернет значение 235.45
SELECT TRUNC(101.23, -1) FROM DUAL вернет значение 100

7) В СУБД Oracle имеется ряд тригонометрических функций SIN(число), COS(число), TAN(число) и обратные им ACOS(число), ASIN(число), ATAN(число). Они возвращают значение соответствующей названию тригонометрической функции. Для прямых функции параметром является значение угла в радианах, а для обратных – значение функции. Примеры
SELECT COS(0.5) FROM DUAL вернет значение 0.877582561890373
SELECT SIN(0.5) FROM DUAL вернет значение 0.479425538604203
SELECT TAN(0.5) FROM DUAL вернет значение 0.546302489843791
SELECT ACOS(0.5) FROM DUAL вернет значение 1.0471975511966
SELECT ASIN(0.5) FROM DUAL вернет значение 0.523598775598299
SELECT ATAN(0.5) FROM DUAL вернет значение 0.463647609000806

8) Гиперболические функции. SINH(число),
COSH(число), TANH(число). SINH() возвращает гиперболический синус переданного параметра, COSH() возвращает гиперболический косинус переданного параметра, TANH() возвращает гиперболический тангенс переданного параметра. Примеры
SELECT COSH(0.5) FROM DUAL вернет значение 1.12762596520638
SELECT SINH(0.5) FROM DUAL вернет значение 0.521095305493747 SELECT TANH(0.5) FROM DUAL вернет значение 0.46211715726001

9) Функция возведения в степень POWER(число_1, число_2). Примеры
SELECT POWER(10, 2) FROM DUAL вернет значение 100
SELECT POWER(100, -2) FROM DUAL вернет значение 0.0001

10) Логарифмические функции. LN(число) возвращает натуральный логарифм переданного параметра, LOG(число_1, число_2) возвращает логарифм второго переданного параметра по основанию, переданному первом параметре. Причем первый параметр должен быть больше нуля и не равен 1. Примеры
SELECT LN(5) FROM DUAL вернет значение 1.6094379124341
SELECT LOG(10, 3) FROM DUAL вернет значение 0.477121254719662

11) Функция извлечения квадратного корня SQRT(число). Пример
SELECT SQRT(4) FROM DUAL вернет значение 2.

12) Функция возведение числа е в степень EXP(число). Пример
SELECT EXP(2) FROM DUAL вернет значение 7.38905609893065.

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

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

1) ADD_MONTHS(дата, количество_месяцев) возвращает дату, отстоящую от даты, переданной в первом параметре на количество месяцев, указанном во втором параметре. Примеры
SELECT ADD_MONTHS(’01-JAN-2010’, 2) FROM DUAL вернет дату ’01.03.2010’
SELECT ADD_MONTHS(’01-JAN-2010’, -3) FROM DUAL вернет дату ’01.10.2009’
SELECT ADD_MONTHS(’30-JAN-2010’, 1) FROM DUAL вернет дату ’28.02.2010’

2) Для определения текущей даты и времени применяется функция SYSDATE. Область применения данной функции намного шире чем может показаться на первый взгляд. В первую очередь это контроль за вводом данных в БД. Во многих таблицах выделяется отдельное поля для сохранения даты последнего внесения изменений. Также очень удобно контролировать некие входные параметры для отчетов, особенно если они не должны быть больше чем текущая дата. Помимо даты данная функция возвращает еще и время с точностью до секунд. Пример
SELECT SYSDATE FROM DUAL вернет дату ‘22.05.2010 14:51:20’

3) Если необходимо определить последний день месяца, то для этого вполне подойдет функции LAST_DAY(дата). Её можно использовать для определения количества дней, оставшихся в месяце.
SELECT LAST_DAY(SYSDATE) – SYSDATE FROM DUAL.
В результате выполнения данного оператора будет выведено количество дней от текущей даты до конца месяца. Пример
SELECT LAST_DAY(’15-FEB-2010’) FROM DUAL вернет дату ’28.02.2010’.

4) Функция для определения количества месяцев между датами MONTHS_BETWEEN(дата_1, дата_2). Примеры
SELECT MONTHS_BETWEEN(’01-JUL-2009’, ’01-JAN-2010’) FROM DUAL вернет значение -6
SELECT MONTHS_BETWEEN(’01-JUL-2009’, ’10-JAN-2010’) FROM DUAL вернет значение -6.29032258064516.
Примечание. Если дни месяцев совпадают, то функция возвращает целое число, в противном случае результат будет дробным, причем количество дней в месяце будет принято 31.

5) Функция NEXT_DAY(дата, день_недели) позволяет определить следующую дату от даты, переданной в первом параметре, которая соответствует дню недели, переданном во втором параметре. Пример
SELECT NEXT_DAY(’01-JUL-2009’, ’mon’) FROM DUAL вернет дату ‘06.07.2009’, то есть следующий понедельник после 1 июля 2009 наступил 6 числа.

6) Округление даты ROUND(дата, формат). Второй параметр не обязателен, если его не указывать, то он принимается за ‘DD’, то есть округление будет произведено до ближайшего дня. Примеры
SELECT ROUND(SYSDATE) FROM DUAL вернет дату ‘23.05.2010’
SELECT ROUND(SYSDATE, MONTH) FROM DUAL вернет дату ‘01.06.2010’, округляется до ближайшего первого дня месяца.

7) Усечение даты. Функция TRUNC(дата, формат). Также как и рассмотренная выше может не иметь второго параметра. В таком случае усечение будет производиться до ближайшего дня. Примеры
SELECT TRUNC(SYSDATE) FROM DUAL вернет дату ’22.05.2010’
SELECT TRUNC(SYSDATE, ‘WW’) FROM DUAL вернет дату ’01.05.2010’
SELECT TRUNC(SYSDATE, ‘Day’) FROM DUAL вернет дату ‘16.05.2010’.

Функции преобразования данных в Oracle

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

1) TO_CHAR(данные, формат). На первый взгляд синтаксис довольно прост, но за счет второго параметра можно очень точно описать в какой формат преобразовать данные. Итак в строку можно преобразовать как дату, так и числовое значение. Рассмотрим вариант преобразования даты к строке. Значения самых распространенных форматов приведены в таблице, более полная информация содержится в технической документации.

Таблица значений форматов для преобразования числа в строку.

SELECT TO_CHAR(SYSDATE, ‘D-MONTH-YY’) FROM DUAL вернет строку ‘7-MAY -10’
SELECT TO_CHAR(SYSDATE, ‘DDD-MM-YYYY’) FROM DUAL вернет строку ‘142-05-2010’
SELECT TO_CHAR(SYSDATE, ‘Q-D-MM-YYY’) FROM DUAL вернет строку ‘2-7-05-010’
SELECT TO_CHAR(1050, ‘9.99EEEE) FROM DUAL вернет строку ‘ 1.050E+03’
SELECT TO_CHAR(1400, ‘9999V999’) FROM DUAL вернет строку ‘1400000’
SELECT TO_CHAR(48, ‘RM’) FROM DUAL вернет строку ‘ XLVIII’

2) Функция преобразования строки в дату TO_DATE(строка, формат). Возможные значения форматов уже рассмотрены выше, поэтому приведу несколько примеров использования данной функции. Примеры
SELECT TO_DATE(’01.01.2010’, ‘DD.MM.YYYY’) FROM DUAL вернет дату ‘01.01.2010’
SELECT TO_DATE(’01.JAN.2010’, ‘DD.MON.YYYY’) FROM DUAL вернет дату ‘01.01.2009’
SELECT TO_DATE(’15-01-10’, ‘DD-MM-YY’) FROM DUAL вернет дату ‘15.01.2010’.

3) Функция преобразования строки в числовое значение TO_NUMBER(строка, формат). Самые распространенные значения форматов перечислены в таблице, поэтому рассмотрим применение данной функции на примерах. Примеры
SELECT TO_NUMBER(‘100’) FROM DUAL вернет число 100
SELECT TO_NUMBER(‘0010.01’, ’9999D99’) FROM DUAL вернет число 10.01
SELECT TO_NUMBER("500,000","999G999") FROM DUAL вернет число 500000.

Введение

oracle регистр строковый арифметический

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

Актуальность: Компьютер является не более чем устройством преобразования информации. Если информации не очень много, то основное время при переработке занимает сам алгоритм преобразования. Если при этом приходится работать с большими объемами данных - эффективность обработки начинает напрямую зависеть от эффективности получения данных, их фильтрации и пр. С целью упрощения процесса разработки, повышения производительности работы подобных систем, были созданы различные СУБД. Они имеют свой собственный формат хранения данных, свои алгоритмы их поиска и извлечения, но базовый язык запросов у большинства из них один. И этот язык - SQL.

Цели:

1. Получение теоретических и практических знаний по функциям Oracle SQL;

2. Применение теории на практике.

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

Функции Oracle SQL

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

function (argument, argument,…)

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

1. однострочные (или скалярные) функции;

2. групповые (или агрегатные) функции.

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

Однострочные функции. Однострочные функции могут появляться в предложениях SELECT, WHERE и ORDER BY команды SELECT. В качестве аргументов они могут принимать константы, заданные пользователем, значения переменных, имена столбцов таблицы БД или выражения, составленные с помощью операторов и функций.

Список функций

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

· числовые функции;

· символьные функции;

· функции для работы с датами;

· функции преобразования.

Символьные функции

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

Функции преобразования регистра символов

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

SELECT LENGTH(string ) FROM DUAL вернет значение 7.

Функции преобразования регистров символов UPPER(строка), LOWER(строка), INITCAP(строка). Для преобразования символов к верхнему регистру используется функция UPPER().

SELECT UPPER(string ) FROM DUAL вернет STRING .

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

SELECT LOWER(String ) FROM DUAL вернет STRING .

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

SELECT INITCAP (string1 string2) FROM DUAL вернет строку STRING1 STRING2 .

Функции для обрезания начальных и концевых пробелов LTRIM(строка), RTRIM(строка), TRIM(строка). Соответственно первая функция обрезает все начальные пробелы строки, вторая - все концевые, а третья все начальные и концевые.

SELECT LTRIM(`str1") FROM DUAL вернет строку str1,

SELECT RTRIM(`str2") FROM DUAL вернет строку str2,

SELECT TRIM(`str3") FROM DUAL вернет строку str3.

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

В некотором текстовом поле таблицы хранится число, причем символ-разделитель между целой и дробной частью в некоторых полях «.», а нам для дальнейшей обработки данных нужно, чтобы он во всех полях должен быть «,».

Для этого воспользуемся функцией REPLACE следующим образом: REPLACE (field1,".", ",") и все символы «.» в поле field будут заменены на символ «,».

SELECT REPLACE (`My_string", "_", "@") FROM DUAL вернет строку My@string.

Функции преобразования данных к другим типам данных: TO_CHAR(число) преобразует число в текст, TO_NUMBER(строка) преобразует текст в число, TO_DATE (строка, формат даты) преобразует строку в дату определенного формата.

SELECT TO_CHAR(123) FROM DUAL вернет строку 123,

SELECT TO_NUMBER(`12345") FROM DUAL вернет число 12345,

Функция определения вхождения подстроки в строку INSTR (исходная строка, подстрока, номер символа). Данная функция позволяет определять номер символа в исходной строке, с которого начинается искомая подстрока (если такая есть). Иначе возвращается 0. Например, нам нужно определить все должности в таблице Table1, в наименовании которых встречается подстрока «менеджер». Для этого вполне подойдет следующий оператор:

SELECT *FROM TABLE1 WHERE INSTR (POST, `менеджер", 1) > 0.

То есть оператор SELECT выведет только те записи из таблицы TABLE1, где искомая подстрока «менеджер» будет найдена. Причем поиск будет осуществляться с первого символа. Если поиск нужно осуществлять с другой позиции, то номер символа для начала поиска указывается в третьем параметре.

SELECT INSTR (`Small string", `string", 1) FROM DUAL вернет значение 7,

SELECT INSTR (`Small string", `String", 1) FROM DUAL вернет значение 0.

Функция выделения в исходной строке подстроки SUBSTR (исходная строка, номер начального символа, количество символов). Рассмотрим такой пример, в пользовательской таблице хранится адрес в виде наименование населенного пункта, название улицы, номер дома. Причем мы точно знаем, что для наименования населенного пункта отводится строго 20 символов (если в наименовании населенного пункта меньше чем 20 символов, то остальная часть заполняется пробелами), для наименования улицы 30 символов, для номера дома 3 символа. Далее нам необходимо перенести все адреса из нашей таблицы в другую, при этом все 3 компонента адреса должны быть в разных полях. Для выделения компонент адреса применим функцию SUBSTR().

SELECT SUBSTR (TABLE_1.ADDRESS, 1,20) CITY, SUBSTR (TABLE_1.ADDRESS, 21,30) STREET, SUBSTR (TABLE_1.ADDRESS, 52, 3) TOWN FROM TABLE_1;

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

SELECT SUBSTR (`My_string", 4, 3) FROM DUAL вернет строку str.

Рассмотренные выше функции можно использовать во входных параметрах. Так если нам нужно выделить все символы, после какого-то определенного, то в функцию SUBSTR можно передать номер искомого символа из функции INSTR. Например, если нужно перенести все символы из поля таблицы, которые расположены после «,» то можно использовать такую конструкцию:

SELECT SUBSTR (My_string, INSTR (My_string, `, ",1), LENGTH (My_string) - INSTR (My_string, `, ", 1)+1) FROM DUAL.

Для определения начального символа мы вызываем функцию INSTR(), которая вернет номер символа первого вхождения подстрок «,». Далее мы определяем количество символов до конца строки как разницу длины строки и номера первого вхождения подстроки.

Для определения кода символа используется функция ASCII (строка), которая возвращает код 1 символа строки. Например:

SELECT ASCII(W) FROM DUAL вернет значение 87.

Обратная функция преобразования кода символа в символ CHR (число).

SELECT CHR (87) FROM DUAL вернет символ W.

Функции манипулирования символьными строками

Oracle предлагает обширный набор функций для манипулирования строковыми данными:

CHR(N) - Возвращает символ ASCII кода для десятичного кода N;

ASCII(S) - Возвращает десятичный ASCII код первого символа строки;

INSTR (S2. S1.pos[, N] - Возвращает позицию строки S1 в строке S2 большую или равную pos. N - число вхождений;

LENGHT(S) - Возвращает длину строки;

LOWER(S) - Заменяет все символы строки на прописные символы;

INITCAP(S) - Устанавливает первый символ каждого слова в строке на заглавный, а остальные символы каждого слова - на прописные;

SUBSTR (S, pos, [, len]) - Выделяет в строке S подстроку длиной len, начиная с позиции pos;

UPPER (S) - Преобразует прописные буквы в строке на заглавные буквы;

LPAD (S, N[, A]) - Возвращает строку S, дополненную слева символами A до числа символов N. Символ - наполнитель по умолчанию - пробел;

RPAD (S, N[, A]) - Возвращает строку S, дополненную справа символами A до числа символов N. Символ - наполнитель по умолчанию - пробел;

LTRIM (S, ) - Возвращает усеченную слева строку S. Символы удаляются до тех пор, пока удаляемый символ входит в строку - шаблон S1 (по умолчанию - пробел);

RTRIM (S, ) - Возвращает усеченную справа строку S. Символы удаляются до тех пор, пока удаляемый символ входит в строку - шаблон S1 (по умолчанию - пробел);

TRANSLATE (S, S1, S2) - Возвращает строку S, в которой все вхождения строки S1 замещены строкой S2. Если S1 <> S2, то символы, которым нет соответствия, исключаются из результирующей строки;

REPLACE (S, S1, [, S2]) - Возвращает строку S, для которой все вхождения строки S1 замещены на подстроку S2. Если S2 не указано, то все вхождения подстроки S1, удаляются из результирующей строки;

NVL (X, Y) - Если Х есть NULL, то возвращает в Y либо строку, либо число, либо дату в зависимости от исходного типа Y;

SOUNDEX(S) - Возвращает фонетическое представление строки;

Лабораторная работа. Работа со строковыми функциями

Строковые функции в запросах Oracle SQL, функции UPPER(), CONCAT(), SUBSTR().

Задание:

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

Напишите запрос, который возвращал бы из таблицы hr.employees информацию об имени и фамилии сотрудника, а также идентификатор сотрудника в соответствии с поставленными условиями. Результат выполнения запроса должен быть таким, как представлено на рис. 3.1-1.

Решение:

Код соответствующего запроса может быть таким:

SELECT first_name AS «Имя», last_name As «Фамилия», UPPER (CONCAT(SUBSTR (first_name, 1, 3), SUBSTR (last_name, 1,2))) AS «Идентификатор» FROM hr.employees.

Символьная функция получает в качестве параметра одно или несколько символьных значений и возвращает символьное и числовое значение. Если символьная функция возвращает символьное значение, оно всегда имеет тип VARCHAR2 (переменная длина) - кроме функций UPPER и LOWER . Эти функции преобразуют заданную строку к верхнему или нижнему регистру соответственно и возвращают значение фиксированной длины типа CHAR , если переданные в аргументах строки имели тип CHAR .

Краткая сводка строковых функций

Как упоминалось ранее, PL/SQL предоставляет в распоряжение программиста широкий набор мощных, высокоуровневых строковых функций для получения информации о строках и модификации их содержимого. Следующий список дает представление об их возможностях и синтаксисе. За полной информацией о конкретных функциях обращайтесь к справочнику Oracle SQL Reference.

• ASCII(символ) Возвращает числовой код (NUMBER) представления заданного символа в наборе символов базы данных.
• ASCIISTR(строка1) Получает строку в любом наборе символов и преобразует ее в строку ASCII-символов. Все символы, отсутствующие в кодировке ASCII, представляются в форме \XXXX, где XXXX - код символа в Юникоде.

За информацией о Юникоде и кодах символов обращайтесь на сайт http://unicode.org.

• CHR(код)
  Возвращает символ типа VARCHAR2 (длина 1), соответствующий заданному коду. Функция является обратной по отношению к функции ASCII. У нее имеется разновидность, удобная при работе с данными в национальных наборах символов:

CHR(код USING NCHAR_CS)

Возвращает символ типа NVARCHAR2 из национального набора символов.

• COMPOSE(строка1)
  Получает строку символов в формате Юникода и возвращает ее в нормализованном виде. Например, ненормализованное представление "a\0303" определяет символ " a " с тильдой cверху (то есть a). Вызов COMPOSE("a\0303") возвращает значение " \00E3" - шестнадцатеричный код символа a в Юникоде.

В Oracle9i Release 1 функция COMPOSE могла вызываться только из SQL-команд; в программах PL/SQL она использоваться не могла. Начиная с Oracle9i Release2, функция COMPOSE также может использоваться в выражениях PL/SQL.

• CONCAT(строка1, строка2)
  Присоединяет строку2 в конец строки1. Аналогичного результата можно добиться при помощи выражения строка1 || строка2. Оператор || намного удобнее, поэтому функция CONCAT используется относительно редко.
• CONVERT(строка1, набор_символов)
  Преобразует строку из набора символов базы данных в заданный набор символов. При вызове также можно задать исходный набор символов:

CONVERT(строка1, конечный_набор, исходный_набор)

• DECOMPOSE(строка1)
  Получает строку в Юникоде и возвращает строку, в которой все составные символы разложены на элементы. Функция является обратной по отношению к COMPOSE . Например, вызов DECOMPOSE ("a") возвращает строку " a ~" (см. описание COMPOSE).

Существует две разновидности этой функции:

• DECOMPOSE(строка1 CANONICAL)
  Выполняет каноническую декомпозицию; полученный результат может быть восстановлен вызовом COMPOSE . Используется по умолчанию.
• DECOMPOSE(строка1)
  Декомпозиция выполняется в так называемом режиме совместимости. Восстановление вызовом COMPOSE может оказаться невозможным.

Функция DECOMPOSE , как и COMPOSE , не может напрямую вызываться в выражениях PL/SQL в Oracle9i Release 1; ее необходимо вызывать из инструкций SQL. Начиная с Oracle9i Release 2, это ограничение было снято.

• GREATEST(строка1, строка2, ...)
  Получает одну или несколько строк и возвращает строку, которая оказалась бы последней (то есть наибольшей) при сортировке входных строк по возрастанию. Также см. описание функции LEAST , обратной по отношению к GREATEST .
• INITCAP(строка1)
  Изменяет регистр символов строкового аргумента, переводя первую букву каждого слова строки в верхний регистр, а остальные буквы - в нижний. Словом считается последовательность символов, отделенная от остальных символов пробелом или символом, не являющимся буквенно-цифровым (например, # или _) . Например, вызов INITCAP (" this is lower") дает результат "This Is Lower".
• INSTR(строка1, строка2)
  Возвращает позицию, с которой строка2 входит в строку1; если вхождение не обнаружено, функция возвращает 0.

Существует несколько разновидностей этой функции:

• INSTR(строка1, строка2, начальная_позиция)
  Поиск строки2 в строке1 начинается с позиции, заданной последним параметром. По умолчанию поиск начинается с позиции 1, так что вызов INSTR(string1, string2, 1) эквивалентен вызову INSTR(string1, string2) .
• INSTR(строка1, строка2, отрицательная_начальная_позиция)
  Смещение начальной позиции задается не от начала, а от конца строки1.
• INSTR(строка1, строка2, начальная_позиция, n)
  Находит n-е вхождение строки2, начиная с заданной начальной позиции.
• INSTR(строка1, строка2, отрицательная_начальная_позиция, n)
  Находит n-е вхождение строки2, начиная с заданной начальной позиции от конца строки1.

Функция INSTR рассматривает строку как последовательность символов. Ее разновидности INSTRB, INSTR2 и INSTR4 рассматривают строку как последовательность байтов, кодовых единиц (code units) или кодовых индексов (code points) Юникода соответственно. Разновидность INSTRC рассматривает строку как последовательность полных символов Юникода. Например, строка "a\0303" , которая представляет собой разложенный эквивалент "\00E3", или a , рассматривается как один символ. Напротив, функция INSTR рассматривает "a\0303 " как последовательность из двух символов.

• LEAST(строка1, строка2, ...)
  Получает одну или несколько строк и возвращает строку, которая оказалась бы первой (то есть наименьшей) при сортировке входных строк по возрастанию. Также см. описание функции GREATEST , обратной по отношению к LEAST .
• LENGTH(строка1)
  Возвращает количество символов в строке. Разновидности LENGTHB, LENGTH2 и LENGTH4 возвращают количество байтов, кодовых единиц (code units) или кодовых индексов (code points) Юникода соответственно. Разновидность LENGTHC возвращает количество полных символов Юникода, нормализованных по мере возможности (то есть с преобразованием "a\0303 " в "\00E3").

Функция LENGTH обычно не возвращает нуль. Вспомните, что Oracle рассматривает пустую строку ("") как NULL , поэтому вызов LENGTH ("") фактически эквивалентен попытке получить длину NULL ; ее результат тоже будет равен NULL . Единственное исключение встречается при применении LENGTH к типу CLOB . Тип CLOB может содержать 0 байт и при этом быть отличным от NULL . В этом единственном случае LENGTH возвращает 0.

• LOWER(строка1)
  Преобразует все буквы заданной строки в нижний регистр. Функция является обратной по отношению к UPPER . Тип возвращаемого значения соответствует типу входных данных ( CHAR , VARCHAR2 , CLOB). Также см. NLS_LOWER .
• LPAD(строка1, итоговая_длина)
  Возвращает значение строки1, дополненное слева пробелами до итоговой_длины. У функции существует следующая разновидность:
• LPAD(строка1, итоговая_длина, заполнитель)
  Присоединяет достаточное количество полных или частичных вхождений заполнителя, чтобы общая длина строки достигла заданной итоговой_длины. Например, вызов LPAD ("Merry Christmas!", 25, "Ho! ") вернет результат " Ho! Ho! HMerry Christmas!".

• ?LTRIM(строка1)
  Удаляет пробелы с левого края строки1. Также см. описания функций TRIM (стандарт ISO) и RTRIM . У функции существует следующая разновидность:
• LTRIM(строка1, удаляемый_набор)
  Удаляет любые символы, входящие в строку удаляемый_набор, от левого края строки1.
• NCHR(код)
  Возвращает символ типа NVARCHAR2 (длина 1) , соответствующий заданному коду. Функция CHR с условием USING NCHAR_CS реализует ту же функциональность, что и NCHR .
• NLS_INITCAP (строка1)
  Возвращает версию строки1, которая должна относиться к типу NVARCHAR2 или NCHAR , в которой первая буква каждого слова переводится в верхний регистр, а остальные буквы - в нижний. Функция возвращает значение типа VARCHAR2 . «Словом» считается последовательность символов, отделенная от остальных символов пробелом или символом, не являющимся буквенно-цифровым.

Вы можете задать порядок сортировки, влияющий на определение «первой буквы»:

• NLS_INITCAP(строка1, "NLS_SORT=правило_сортировки")
  В этой форме синтаксиса правило_сортировки представляет собой одно из допустимых названий правил сортировки, перечисленных в руководстве Oracle Database Globalization Support Guide, Appendix A, раздел «Linguistic Sorts».

Следующий пример показывает, чем функция INITCAP отличается от NLS_INITCAP:

BEGIN DBMS_OUTPUT.PUT_LINE(INITCAP("ijzer")); DBMS_OUTPUT.PUT_LINE(NLS_INITCAP("ijzer","NLS_SORT=XDUTCH")); END; Результат: Ijzer IJzer

В нидерландском языке последовательность символов « ? » рассматривается как один символ. Функция NLS_INITCAP распознает это обстоятельство при задании правила NLS_SORT и правильно преобразует символы слова « ?zer » («железо» по-нидерландски).

• NLS_LOWER(строка1) и NLS_LOWER(строка1, "NLS_SORT=правило_сортировки ") Возвращает строку1, преобразованную в нижний регистр по правилам заданного языка. О том, как NLS_SORT может повлиять на результат преобразования, рассказано в описании функции NLS_INITCAP .
• NLS_UPPER(строка1) и NLS_UPPER(строка1, "NLS_SORT=правило_сортировки") Возвращает строку1, преобразованную в верхний регистр по правилам заданного языка. О том, как NLS_SORT может повлиять на результат преобразования, рассказано в описании функции NLS_INITCAP .
• NLSSORT(строка1) и NLSSORT(строка1, "NLS_SORT=правило_сортировки ") Возвращает строку байтов, которая может использоваться для сортировки строкового значения по правилам заданного языка. Строка возвращается в формате RAW . Например, сравнение двух строк по правилам французского языка выполняется так: IF NLSSORT(x, "NLS_SORT=XFRENCH") > NLSSORT(y, "NLS_SORT=XFRENCH") THEN... Если второй параметр не указан, функция использует порядок сортировки по умолчанию, назначенный для сеанса. Полный список правил приведен в руководстве Oracle Database Globalization Support Guide, Appendix A, раздел «Linguistic Sorts».
• REGEXP_COUNT, REGEXP_INSTR, REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR За описаниями этих функций, предназначенных для работы с регулярными выражениями , можно изучить .
• REPLACE(строка1, искомая_строка, замена) Возвращает строку, полученную в результате замены всех вхождений искомой_строки в строке1 строкой замена. Функция REPLACE может использоваться для замены всех вхождений определенной подстроки в одной инструкции.
• REPLACE(строка1, искомая_строка)
  Возвращает строку, полученную в результате удаления всех вхождений искомой_строки из строки1.
• RPAD(строка1, итоговая_длина)
  Возвращает значение строки1, дополненное справа пробелами до итоговой_длины. У функции существует следующая разновидность:
• RPAD(строка1, итоговая_длина, заполнитель)
  Присоединяет достаточное количество полных или частичных вхождений заполнителя, чтобы общая длина строки достигла заданной итоговой_длины. Вызов RPAD("Merry Christmas!", 25, "Ho! ") вернет результат "Merry Christmas! Ho! Ho!".

Функция RPAD дополняет строку справа, а парная ей функция LPAD - слева.

• RTRIM(строка1)
  Удаляет пробелы с правого края строки1. Также см. описания функций TRIM (стандарт ISO) и LTRIM . У функции существует следующая разновидность:
• RTRIM(строка1, удаляемый_набор)
  Удаляет любые символы, входящие в строку удаляемый_набор, с правого края строки1.
• SOUNDEX(строка1)
  Возвращает строку с «фонетическим представлением» аргумента.
  Пример:

SOUNDEX ("smith") --> "S530" SOUNDEX ("SMYTHE") --> ""S530" SOUNDEX ("smith smith") --> "S532" SOUNDEX ("smith z") --> "S532" SOUNDEX ("feuerstein") --> "F623" SOUNDEX ("feuerst") --> "F623"

При использовании функции SOUNDEX следует помнить несколько правил:

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

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

Алгоритм SOUNDEX ориентирован на английский язык; в других языках он может работать плохо (или не работать вообще).

• SUBSTR(строка1, начальная_позиция, длина)
  Возвращает подстроку из строки1, которая начинается с начальной_позиции и имеет заданную длину. Если количество символов до конца строки1 окажется меньше длины, возвращаются все символы от начальной позиции до конца строки. У функции существуют следующие разновидности:
• SUBSTR(строка1, начальная_позиция)
  Возвращает все символы от начальной_позиции до конца строки1.
• SUBSTR(строка1, отрицательная_начальная_позиция, длина)
  Начальная позиция подстроки отсчитывается от конца строки1.
• SUBSTR(строка1, отрицательная_начальная_позиция)
  Возвращает последние ABS(отрицательная_начальная_позиция) строки.

Функция SUBSTR рассматривает строку как последовательность символов. Ее разновидности SUBSTRB, SUBSTR2 и SUBSTR4 рассматривают строку как последовательность байтов, кодовых единиц (code units) или кодовых индексов (code points) Юникода соответственно. Разновидность SUBSTRC рассматривает строку как последовательность полных символов Юникода. Например, строка "a\0303" , которая представляет собой разложенный эквивалент "\00E3" , или a , рассматривается как один символ. Напротив, функция SUBSTR рассматривает "a\0303" как последовательность из двух символов.

• TO_CHAR(национальные_символьные_данные)
  Преобразует данные из национального набора символов в эквивалентное представление в наборе символов базы данных. Также см. TO_NCHAR .

Функция TO_CHAR также может использоваться для преобразования даты/ времени и чисел в удобочитаемую форму.

• TO_MULTI_BYTE(строка1)
  Преобразует однобайтовые символы в их многобайтовые эквиваленты. В некоторых многобайтовых кодировках, и прежде всего UTF-8, может существовать несколько вариантов представления одного символа. Скажем, в UTF-8 представление буквы "G" может содержать от 1 до 4 байт. Для перехода от однобайтового представления к многобайтовому используется функция TO_MULTI_BYTE . Данная функция является обратной по отношению к TO_SINGLE_BYTE .
• TO_NCHAR(символы_в_наборе_базы_данных)
  Преобразует данные из набора символов базы данных в эквивалентное представление в национальном наборе символов. Также см. TO_CHAR и TRANSLATE...USING.

Функция TO_NCHAR также может использоваться для преобразования даты/времени и чисел в удобочитаемую форму.

• TO_SINGLE_BYTE(строка1)
  Преобразует многобайтовые символы в их однобайтовые эквиваленты. Функция является обратной по отношению к TO_MULTI_BYTE .
• TRANSLATE(строка1, искомый_набор, набор_замены)
  Заменяет в строке1 каждое вхождение символа из искомого_набора соответствующим символом набора_замены. Пример:

TRANSLATE ("abcd", "ab", "12") --> "12cd"

Если искомый_набор содержит больше символов, чем набор_замены, «лишние» символы, не имеющие соответствия в наборе_замены, не включаются в результат. Пример:

TRANSLATE ("abcdefg", "abcd", "zyx") --> "zyxefg"

Буква « d » удалена, потому что она присутствует в искомом_наборе, но не имеет эквивалента в наборе_замены. Функция TRANSLATE заменяет отдельные символы, а функция REPLACE - целые строки.

• TRANSLATE(текст USING CHAR_CS) и TRANSLATE(текст USING NCHAR_CS)
  Преобразует символьные данные в набор символов базы данных (CHAR_CS) или в национальный набор символов (NCHAR_CS). Выходным типом данных будет VARCHAR2 или NVARCHAR2 в зависимости от того, выполняется ли преобразование к набору символов базы данных или национальному набору символов соответственно.

Функция TRANSLATE...USING входит в число функций SQL по стандарту ISO. Начиная с Oracle9i Release 1, можно просто присвоить значение VARCHAR2 переменной типа NVARCHAR2 , и наоборот - система неявно выполнит нужное преобразование. Если же вы хотите выполнить преобразование явно, используйте функции TO_CHAR и TO_NCHAR для преобразования текста в набор символов базы данных и национальный набор символов соответственно. Oracle рекомендует пользоваться указанными функциями вместо TRANSLATE...USING , потому что они поддерживают более широкий набор входных типов данных.

• TRIM(FROM строка1)
  Возвращает строку, полученную в результате удаления из строки1 всех начальных и конечных пробелов. У функции существуют следующие разновидности:
• TRIM(LEADING FROM ...)
  Удаление только начальных пробелов.
• TRIM(TRAILING FROM ...)
  Удаление только конечных пробелов.
• TRIM(BOTH FROM ...)
  Удаление как начальных, так и конечных пробелов (используется по умолчанию).
• TRIM (... удаляемый_символ FROM строка1)
  Удаление вхождений одного удаляемого_символа на выбор программиста.

Функция TRIM была включена в Oracle8i для обеспечения более полной совместимости со стандартом ISO SQL. Она сочетает в себе функциональность LTRIM и RTRIM , но отличается от них тем, что TRIM позволяет задать только один удаляемый символ, тогда как при использовании LTRIM и RTRIM можно задать набор удаляемых символов.

• UNISTR(строка1)
  Возвращает строку1, преобразованную в Юникод; таким образом, функция является обратной по отношению к ASCIISTR . Для представления непечатаемых символов во входной строке можно использовать запись \XXXX, где XXXX - кодовый индекс символа в Юникоде. Пример:

BEGIN DBMS_OUTPUT.PUT_LINE(UNISTR("Знак евро \20AC")); END; Знак евро €.

Функция предоставляет удобный доступ ко всему набору символов Юникода, в том числе и к тем, которые не могут вводиться непосредственно с клавиатуры.

• UPPER(строка1)
  Преобразует все буквы заданной строки в верхний регистр. Тип возвращаемого значения соответствует типу входных данных (CHAR, VARCHAR2 , CLOB). Функция является обратной по отношению к LOWER . Также см. NLS_UPPER .

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

Любое чётное число делится на два без остатка, любое нечётное число при делении на два вернёт остаток 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;

Преположим что текущая дата 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.

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

Функция ADD _MONTHS

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

Результатом первого запроса буде 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. Рассмотрим несколько запросов

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

Функция LAST _DAY

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

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

Функция ROUND округляет значение даты до заданной точности даты. Возвращаемое значение округляется либо к большему, либо r меньшему значению в зависимости от значения округляемого элемента. Эта функция требует один обязательный параметр и допускает один необязательные и синтаксис функции ROUND(source date, ). Параметром 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, ). Параметром source date может быть любая валидная дата. Параметр date precision format определяет уровень сокращения даты и необязателен, значение по умолчанию – сокращение до дня. Это значит что все значения времени обнуляются – 00 часов 00 минут 00 секунд. Сокращение до месяца вернёт дату равную первому дню месяца исходной даты. Сокращение до года – вернёт первый день года исходной даты. Рассмотрим запрос, использующий функцию с разными параметрами

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