Преобразование вывода и встроенные функции

1.

Преобразование вывода и
встроенные функции

2.

SQL-функции
Входные значения
Выходные значения
Функция
Функция совершает
действие
арг 1
арг 2
Результат
арг n

3.

Два типа SQL-функций
Функции
Однострочные
Возвращают один результат
для одной строки
Многострочные
Возвращают один результат
для набора строк

4.

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

5.

Символьные функции
Символьные
функции
Функции преобразования
регистра
LOWER
UPPER
INITCAP
Функции, манипулирующие
символами
CONCAT
SUBSTR
LENGTH
INSTR
LPAD | RPAD
TRIM
REPLACE

6.

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

7.

Использование регистровых функций
Отобразить номер сотрудника, имя и номер отдела для сотрудника
Хиггинса:

8.

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

9.

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

10.

Числовые функции
• ROUND: округляет значение до указанного десятка
• TRUNC: обрезает значение до указанного десятка
• MOD: возвращает остаток от деления

11.

Использование функции ROUND
DUAL – общедоступная таблица, которую вы можете использовать
для просмотра результатов функций и вычислений.

12.

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

13.

Использование функции MOD
Для всех сотрудников с должностью торгового представителя (Sales
Representative) рассчитать остаток зарплаты после деления на
5000.

14.

Работа с датами
База данных Oracle хранит даты во внутреннем числовом формате:
век, год, месяц, день, часы, минуты и секунды. Формат
отображения даты по умолчанию — DD-MON-RR.
Позволяет хранить год, указывая последние две цифры года:

15.

Особенности хранения дат двумя
последними цифрами года

16.

Функция sysdate
SYSDATE возвращает системную дату и время:

17.

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

18.

Арифметические операции над датами

19.

Функции преобразования дат
Функция
Результат
MONTHS_BETWEEN
Кол-во месяцев между двумя датами
ADD_MONTHS
Добавление месяцев к дате
NEXT_DAY
Следующий день от даты
LAST_DAY
Последний день месяца
ROUND
Округление даты
TRUNC
Обрезка даты

20.

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

21.

Использование ROUND и TRUNC с датами
Для примера взята дата SYSDATE = '25-JUL-03'

22.

Функции преобразования
значений и выражения
ветвления

23.

Функции преобразования значений
Преобразование
значений
Явное преобразование
Неявное преобразование

24.

Неявное преобразование
Oracle может автоматически преобразовывать выражения
следующим образом:
При сравнении выражений Oracle также может преобразовать:

25.

26.

Использование TO_CHAR с датами
Маска формата даты:
• Должна быть заключена в одинарные кавычки
• Чувствительна к регистру
• Может представлять собой любой допустимый формат даты
• Отделяется от значения даты запятой
• fm может добавляться для игнорирования пробелов

27.

Элементы формата даты
Элемент
Значение
YYYY
Год в числовом формате
YEAR
Год на английском
MM
Номер месяца (два символа)
MONTH
Полное название месяца на английском
MON
Трехбуквенная аббревиатура месяца
DY
Трехбуквенная аббревиатура дня недели
DAY
Полное наименование дня недели
DD
Номер дня месяца

28.

Элементы формата даты
Возможно форматирование временно́й части даты:
HH24:MI:SS AM
15:45:32 PM
Добавление строки символов в двойных кавычках:
DD "of" MONTH
12 of OCTOBER
Суффиксы для порядковых числительных:
ddspth
fourteenth

29.

Использование TO_CHAR с датами

30.

Использование TO_CHAR с числами
Некоторые из элементов формата, которые можно использовать с
функцией TO_CHAR для отображения числового значения в виде
символа:
Элемент
9
Результат
0
Отображает 0
$
Плавающий знак доллара
L
Символ местной валюты
.
Печатает десятичную точку
,
Печатает запятую как разделитель разрядов
Отображает число

31.

Использование TO_CHAR с числами

32.

Использование TO_NUMBER и TO_DATE
TO_NUMBER преобразовывает строку символов в число:
TO_DATE преобразовывает строку символов в формат даты:
Эти функции имеют модификатор fx. Этот модификатор задает
точное соответствие символьного аргумента и модели формата
даты функции TO_DATE.

33.

Вложенные функции
• Однострочные функции могут иметь любой уровень вложенности
• Вложенные функции выполняются в порядке от внутренней к
внешней

34.

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

35.

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

36.

Общие функции
Следующие функции работают с любым типом данным и могут
обрабатывать NULL-значения:
• NVL (expr1, expr2)
• NVL2 (expr1, expr2, expr3)
• NULLIF (expr1, expr2)
• COALESCE (expr1, expr2, ..., exprn)

37.

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

38.

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

39.

NVL2
NVL2 (expr1, expr2, expr3)
Если первый параметр функции пуст, отображается значение
третьего параметра, а если значение первого параметра не пустое,
отображается значение второго параметра.

40.

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

41.

NULLIF
NULLIF(exp1,expr2)
Функция должна возвращать NULL, если exp1 и exp2 равны, иначе
возвращает первое значение

42.

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

43.

COALESCE
Coalesce(expr1, expr2, expr3….. exprn)
Преимущество функции COALESCE по сравнению с функцией NVL
состоит в том, что функция COALESCE может принимать несколько
альтернативных значений. Если первое выражение не равно null,
функция COALESCE возвращает это выражение; иначе ищет первое
непустое выражение. Если все пусто, вернется NULL.

44.

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

45.

Выражения ветвления
• CASE
• DECODE

46.

Выражение CASE
Облегчает условные запросы, выполняя работу оператора IF-THENELSE:

47.

Выражение CASE

48.

Выражение DECODE
Аналогично CASE, но представляет собой старый синтаксис:

49.

Выражение DECODE

50.

Выражение DECODE
Отобразить применимую налоговую ставку для каждого
сотрудника в отделе 80: