Однострочные функции

1. Однострочные функции

Введение в набор встроенных
функций языка SQL
1
А.М. Гудов

2.

Цели занятия
• Различные типы функций в SQL.
• Основные концепции использования функций.
• Включение в команды SELECT функций
различных типов — символьных, числовых и
типа “дата“.
• Функции преобразования данных и их
использование.
2
А.М. Гудов

3.

Обзор функций в SQL
Функции используются для:
• Выполнения расчетов с данными.
• Изменения отдельных единиц данных.
• Управления выводом групп строк.
• Изменения формата вывода дат.
• Преобразования типов данных в столбцах.
3
А.М. Гудов

4.

Два типа функций в SQL
• Однострочные
– Символьные
Функция
– Числовые
– Функции даты
– Функции
преобразования
• Многострочные
Однострочная
Многострочная
– Групповые
4
А.М. Гудов

5.

Однострочные функции: синтаксис
Однострочные функции:
• Манипулируют элементами данных.
• Принимают аргументы и возвращают одно
значение.
• Работают с каждой строкой, возвращаемой
запросом.
• Возвращают один результат на строку.
• Изменяют тип данных.
• Могут быть вложенными.
Синтаксис:
function_name (column|expression, [arg1, arg2,...])
5
А.М. Гудов

6.

Символьные функции
6
LOWER
Преобразование в нижний регистр
UPPER
Преобразование в верхний регистр
INITCAP
Преобразование начальных букв
в верхний регистр
CONCAT
Конкатенация значений
SUBSTR
Возврат подстроки
LENGTH
Возврат количества символов
NVL
Преобразование
неопределенного значения
А.М. Гудов

7.

Функции преобразования регистра
Преобразование регистра для строки символов
LOWER('SQL Course')
sql course
UPPER('SQL Course')
SQL COURSE
INITCAP('SQL Course')
Sql Course
SQL> SELECT first_name, last_name
2 FROM
s_emp
3 WHERE
last_name = 'PATEL';
no rows returned
SQL> SELECT first_name, last_name
2 FROM
s_emp
3 WHERE UPPER(last_name) = 'PATEL';
FIRST_NAME
LAST_NAME
-------------------- -------------------Vikram
Patel
Radha
Patel
7
А.М. Гудов

8.

Символьные и числовые функции
Работа с символьными строками:
• CONCAT('Good', 'String')
GoodString
• SUBSTR('String',1,3)
Str
• LENGTH('String')
6
Числовые функции:
8
ROUND
Округляет значение до заданной
точности
TRUNC
Усекает значение до заданного
количества десятичных знаков
MOD
Возвращает остаток от деления
А.М. Гудов

9.

Функции ROUND, TRUNC, MOD
ROUND (45.923, 2)
ROUND (45.923, 0)
ROUND (45.923, -1)
TRUNC (45.923, 2)
TRUNC (45.923)
TRUNC (45.923, -1)
45.92
46
50
45.92
45
40
Вычисление остатка от деления одного значения
на другое
9
MOD(1600,300)
100
А.М. Гудов

10.

Формат даты Oracle
Oracle хранит данные во внутреннем цифровом формате.
- Век, год, месяц, число, минуты, секунды
По умолчанию дата выдается в формате DD-MON-YY
Функция SYSDATE возвращает дату и время
DUAL - это фиктивная таблица, используемая для
просмотра SYSDATE.
Арифметические операции с датами:
• Результатом прибавления числа к дате и вычитания
числа из даты является дата.
• Результатом вычитания одной даты из другой является
количество дней, разделяющих эти даты.
• Прибавление часов к дате производится путем деления
количества часов на 24.
10
А.М. Гудов

11.

Функции для работы с датами
11
MONTHS_BETWEEN Число месяцев, разделяющих
две даты
ADD_MONTHS
Добавление календарных
месяцев к дате
NEXT_DAY
Ближайшая дата, когда
наступит заданный день
недели
LAST_DAY
Последняя дата текущего
месяца
ROUND
Округление до целых суток
TRUNC
Отсечение части даты,
обозначающей время
А.М. Гудов

12.

Функции для работы с датами
12
MONTHS_BETWEEN('01-SEP-95','11-JAN-94')
1.9774194
ADD_MONTHS('11-JAN-94',6)
'11-JUL-94'
NEXT_DAY('01-SEP-95','FRIDAY')
'08-SEP-95'
LAST_DAY('01-SEP-95')
'30-SEP-95'
ROUND('25-MAY-95','MONTH')
01-JUN-95
ROUND('25-MAY-95 ','YEAR')
01-JAN-95
TRUNC('25-MAY-95 ','MONTH')
01-MAY-95
TRUNC('25-MAY-95 ','YEAR')
01-JAN-95
А.М. Гудов

13.

Функции преобразования
• Функция TO_CHAR преобразует число или
строку даты в строку символов.
• Функция TO_NUMBER преобразует строку
символов, состоящую из цифр, в число.
• Функция TO_DATE преобразует строку
символов с датой в значение типа “дата“.
• Функции преобразования могут использовать
модель формата, состоящую из нескольких
элементов.
13
А.М. Гудов

14.

Функция TO_CHAR с датами
TO_CHAR(date, 'fmt')
Модель формата:
• Должна быть заключена в апострофы. Различает
символы верхнего и нижнего регистров.
• Может включать любые разрешенные элементы
формата даты.
• Использует элемент fm для удаления конечных
пробелов и ведущих нулей.
• Отделяется от значения даты запятой.
14
А.М. Гудов

15.

Элементы формата даты
YYYY - полный год цифрами
YEAR - год прописью
MM - двузначное цифровое обозначение месяца
MONTH - полное название месяца
DY - трехзначное алфавитное сокращенное название
дня недели
• DAY - полное название дня
Элементы, которые задают формат части даты, обозначающей время.
– HH24:MI:SS AM
15:45:32 PM
Символьные строки добавляются в кавычках.
– DD " of " MONTH
12 of OCTOBER
Числовые суффиксы используются для вывода числительных
прописью.
– ddspth
15
fourteenth
А.М. Гудов

16.

Формат даты RR
Текущий год
1995
1995
2001
2001
Заданная дата
27-OCT-95
27-OCT-17
27-OCT-17
27-OCT-95
Формат RR
1995
2017
2017
1995
Формат YY
1995
1917
2017
2095
Год, заданный двузначным числом
0-49
Если две
последних
цифры
текущего
года равны:
16
0-49
50-99
50-99
Возвращаемая дата
относится к текущему
столетию.
Возвращаемая дата
относится к столе-тию
перед текущим.
Возвращаемая дата
относится к столетию
после текущего.
Возвращаемая дата
относится к текущему
столетию..
А.М. Гудов

17.

Функция TO_CHAR с числами
TO_CHAR(number, 'fmt')
Форматы, используемые с функцией TO_CHAR
для вывода символьного значения в виде числа
17
9
0
$
L
.
,
-
цифра.
вывод нуля.
плавающий знак доллара.
плавающий символ местной валюты
вывод десятичной точки.
вывод разделителя троек цифр.
А.М. Гудов

18.

Функция TO_CHAR с числами
SQL> SELECT
2
3
4 FROM
5 WHERE
'Order '||TO_CHAR(id)||
' was filled for a total of '
||TO_CHAR(total,'fm$9,999,999')
s_ord
ship_date = '21-SEP-92';
• Выходная строка, состоящая из символов “#”,
означает, что в модели формата недостаточно
символов слева от десятичной точки.
• Сервер Oracle7 округляет десятичные
значения, которые хранятся в базе данных, в
соответствии с заданной моделью формата.
18
А.М. Гудов

19.

Функции TO_NUMBER и TO_DATE
Преобразование строки символов в числовой
формат с помощью функции TO_NUMBER:
TO_NUMBER(char)
Преобразование строки символов в формат даты с
помощью функции TO_DATE:
TO_DATE ('10 September 1992', dd Month YYYY')
Использование элементов формата.
TO_DATE(char[, 'fmt'])
19
А.М. Гудов

20.

Вложенные однострочные функции
• Однострочные функции могут быть вложены на
любую глубину.
• Вложенные функции вычисляются от самого
глубокого уровня к внешнему.
F3(F2(F1(col,arg1),arg2),arg3)
Step 1 = Result 1
Step 2 = Result 2
Step 3 = Result 3
20
А.М. Гудов

21.

Вложенные функции
SQL> SELECT
last_name,
2
NVL(TO_CHAR(manager_id),'No Manager')
3 FROM
s_emp
4 WHERE
manager_id IS NULL;
1. Вычисление внутренней функции для преобразования числового значения в
строку символов:
Результат1=TO_CHAR(manager_id)
2. Вычисление внешней функции для замены неопределенного значения текстовой
строкой:
NVL(Результат1,'No Manager')
SQL> SELECT
2
3
4
5 FROM
6 ORDER BY
TO_CHAR(NEXT_DAY(ADD_MONTHS
(date_ordered,6), 'FRIDAY'),
'fmDay, Month ddth, YYYY')
"New 6 Month Review"
s_ord
date_ordered;
1. Вычисление внутренней функции:
2. Вычисление следующей функции:
3. Вычисление внешней функции:
21
Result1=ADD_MONTHS(date_ordered,6)
Result2=NEXT_DAY(Result1,'FRIDAY')
Result3=TO_CHAR(Result2,'fmDay, Month
ddth, YYYY')
А.М. Гудов

22.

Заключение
• Однострочные функции могут быть вложены
на любую глубину.
• Однострочные функции работают с
символьными, числовыми данными и
данными типа DATE.
• К функциям преобразования относятся
TO_CHAR, TO_DATE и TO_NUMBER.
• SYSDATE — псевдостолбец, используемый для
вывода текущей даты и времени.
• DUAL — фиктивная таблица в базе данных.
22
А.М. Гудов

23.

Практическое занятие (обзор)
• Составление запросов, требующих использования
числовых, символьных функций и функций для
работы с датами.
• Использование конкатенации с функциями.
• Составление запросов, нечувствительных к
регистру, для проверки полезности символьных
функций.
• Вычисление продолжительности работы
служащего в месяцах и годах.
• Определение даты аттестации служащего.
23
А.М. Гудов

24.

Практическое занятие (задания)
1. Однострочная функция возвращает один результат для группы строк?
(Да/Нет)
2. Можно ли применять к значениям дат любые арифметические операции?
(Да/Нет)
3. Как получить номер служащего, его фамилию и заработную плату,
повышенную на 15%?
4. Как получить фамилию каждого служащего и его должность в скобках ?
5. Вывести для каждого служащего фамилию, дату найма на работу и дату
пересмотра его заработной платы, которая приходится на первый
понедельник после шести месяцев работы. Формат даты - как “Eighth of May
1992”.
6. Вывести все наименования товаров, которые начинаются на слово “ski”.
7. Для каждого служащего вычислить количество месяцев со дня начала
работы до текущей даты. Результат отсортировать по количеству
отработанных месяцев.
24
А.М. Гудов