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

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

Copyright © Oracle. All rights reserved.

2. Рассматриваемые вопросы

Различные типы функций в SQL.
Использование функций различных типов:
символьных, числовых и типа “дата“ в
командах SELECT.
Функции преобразования данных и их
использование.
Copyright © Oracle. All rights reserved.

3. Функции SQL

Ввод
Вывод
Функция
Функция выполняет
действие
арг 1
арг 2
Значение
результата
арг n
Copyright © Oracle. All rights reserved.

4. Два типа функций SQL

Функции
Однострочные
Многострочные
Возвращают результат
для каждой строки
Возвращают один результат
для множества строк
Copyright © Oracle. All rights reserved.

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

Манипулируют элементами данных.
Принимают аргументы и возвращают одно
значение.
Работают с каждой строкой, возвращаемой
запросом.
Возвращают один результат на строку.
Могут изменять тип данных.
Могут быть вложенными.
Принимают аргументы, которые могут быть
столбцами или выражениями.
имя_функции [(арг1, арг2,...)]
Copyright © Oracle. All rights reserved.

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

Символьные
Общие
Однострочные
функции
Преобразования
Числовые
Даты
Copyright © Oracle. All rights reserved.

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

Символьные
функции
Функции преобразования
регистра символов
Функции манипулирования
символами
LOWER
UPPER
INITCAP
CONCAT
SUBSTR
LENGTH
INSTR
LPAD | RPAD
TRIM
REPLACE
Copyright © Oracle. All rights reserved.

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

Функции преобразования регистра
символов
Эти функции преобразуют регистр символьных строк
Функция
LOWER('SQL Course')
Результат
sql course
UPPER('SQL Course')
SQL COURSE
INITCAP('SQL Course')
Sql Course
Copyright © Oracle. All rights reserved.

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

Использование функций
преобразования регистра
Вывод номера служащего по фамилии Higgins, его
фамилии и отдела:
SELECT employee_id, last_name, department_id
FROM
employees
WHERE last_name = 'higgins';
no rows selected
SELECT employee_id, last_name, department_id
FROM
employees
WHERE LOWER(last_name) = 'higgins';
Copyright © Oracle. All rights reserved.

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

Функции манипулирования символами
Эти функции манипулируют символьными строками:
Функция
CONCAT('Hello', 'World')
Результат
HelloWorld
SUBSTR('HelloWorld',1,5)
Hello
LENGTH('HelloWorld')
10
INSTR('HelloWorld', 'W')
6
LPAD(salary,10,'*')
*****24000
RPAD(salary, 10, '*')
24000*****
REPLACE
('JACK and JUE','J','BL')
BLACK and BLUE
TRIM('H' FROM 'HelloWorld')
elloWorld
Copyright © Oracle. All rights reserved.

11. Функции манипулирования символами

Использование функций
манипулирования символами
1
SELECT employee_id, CONCAT(first_name, last_name) NAME,
job_id, LENGTH (last_name),
INSTR(last_name, 'a') "Contains 'a'?"
FROM
employees
WHERE SUBSTR(job_id, 4) = 'REP';
1
2
Copyright © Oracle. All rights reserved.
3
2
3

12. Использование функций манипулирования символами

Числовые функции
ROUND: округляет значение до заданного количества
десятичных знаков
TRUNC: усекает значение до заданного количества
десятичных знаков
MOD: возвращает остаток от деления
Функция
Результат
ROUND(45.926, 2)
45.93
TRUNC(45.926, 2)
45.92
MOD(1600, 300)
100
Copyright © Oracle. All rights reserved.

13. Числовые функции

Использование функции ROUND
1
2
SELECT ROUND(45.923,2), ROUND(45.923,0),
ROUND(45.923,-1)
FROM
DUAL;
1
3
2
DUAL – это фиктивная таблица, используемая для
получения результатов выполнения функций и
вычислений.
Copyright © Oracle. All rights reserved.
3

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

Использование функции TRUNC
1
2
SELECT TRUNC(45.923,2), TRUNC(45.923),
TRUNC(45.923,-1)
FROM
DUAL;
1
2
Copyright © Oracle. All rights reserved.
3
3

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

Использование функции MOD
Вычисление остатка от деления оклада на 5000 для
всех служащих, работающих в должности торгового
представителя.
SELECT last_name, salary, MOD(salary, 5000)
FROM
employees
WHERE job_id = 'SA_REP';
Copyright © Oracle. All rights reserved.

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

Работа с датами
Oracle хранит данные во внутреннем цифровом
формате: век, год, месяц, число, часы, минуты,
секунды.
По умолчанию дата выдается в формате
DD-MON-RR (число- месяц-год).
– Можно задавать год двумя цифрами и хранить
дату 21 века, если текущая дата 20 века.
– Можно хранить дату 20 века в 21 веке тем же
способом.
SELECT last_name, hire_date
FROM
employees
WHERE hire_date < '01-FEB-88';
Copyright © Oracle. All rights reserved.

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

SYSDATE – эта функция, которая возвращает:
дату
время
Copyright © Oracle. All rights reserved.

18.

Арифметические операции с датами
Результатом прибавления числа к дате и
вычитания числа из даты является дата.
Результатом вычитания одной даты из другой
является количество дней, разделяющих эти
даты.
Прибавление часов к дате производится путем
деления количества часов на 24.
Copyright © Oracle. All rights reserved.

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

Использование арифметических
операторов с датами
SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM
employees
WHERE department_id = 90;
Copyright © Oracle. All rights reserved.

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

Функции для работы с датами
Функция
MONTHS_BETWEEN
Результат
Число месяцев, разделяющих две
даты
ADD_MONTHS
LAST_DAY
Добавление календарных
месяцев к дате
Ближайшая дата, когда
наступит заданный день недели
Последняя дата текущего месяца
ROUND
Округление даты
TRUNC
Усечение даты
NEXT_DAY
Copyright © Oracle. All rights reserved.

21. Использование арифметических операторов с датами

Использование функций для работы с
датами
Функция
Результат
MONTHS_BETWEEN
('01-SEP-95','11-JAN-94')
19.6774194
ADD_MONTHS ('11-JAN-94',6)
'11-JUL-94'
NEXT_DAY
('01-SEP-95','FRIDAY')
'08-SEP-95'
LAST_DAY
('01-FEB-95')
'28-FEB-95'
Copyright © Oracle. All rights reserved.

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

Использование функций для работы с
датами
Предположим, что SYSDATE = '25-JUL-03':
Функция
ROUND(SYSDATE,'MONTH')
Результат
01-AUG-03
ROUND(SYSDATE ,'YEAR')
01-JAN-04
TRUNC(SYSDATE ,'MONTH')
TRUNC(SYSDATE ,'YEAR')
01-JUL-03
01-JAN-03
Copyright © Oracle. All rights reserved.

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

Обзор практического занятия 3, часть 1
Составление запроса для вывода текущей
даты.
Составление запросов, требующих
использования числовых, символьных
функций и функций для работы с датами.
Вычисление продолжительности работы
служащего в месяцах и годах.
Copyright © Oracle. All rights reserved.

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

Функции преобразования
Преобразование
типа данных
Неявное
преобразование
типа данных
Явное
преобразование
типа данных
Copyright © Oracle. All rights reserved.

25. Обзор практического занятия 3, часть 1

Неявное преобразование типов данных
Для операций присваивания Oracle может
автоматически выполнять следующие
преобразования:
Исходный формат
VARCHAR2 или CHAR
Новый формат
NUMBER
VARCHAR2 или CHAR
DATE
NUMBER
VARCHAR2
DATE
VARCHAR2
Copyright © Oracle. All rights reserved.

26. Функции преобразования

Неявное преобразование типов данных
При вычислении выражений Oracle может
автоматически выполнять следующие
преобразования:
Исходный формат
VARCHAR2 или CHAR
Новый формат
NUMBER
VARCHAR2 или CHAR
DATE
Copyright © Oracle. All rights reserved.

27. Неявное преобразование типов данных

Явное преобразование типов данных
TO_NUMBER
ЧИСЛО
TO_DATE
ДАТА
СИМВОЛ
TO_CHAR
TO_CHAR
Copyright © Oracle. All rights reserved.

28. Неявное преобразование типов данных

Явное преобразование типов данных
TO_NUMBER
ЧИСЛО
TO_DATE
ДАТА
СИМВОЛ
TO_CHAR
TO_CHAR
Copyright © Oracle. All rights reserved.

29. Явное преобразование типов данных

'NLS_DATE_LANGUAGE = language'
Использование функции TO_CHAR с
датами
TO_CHAR(date, 'модель_формата','NLS_DATE_LANGUAGE = язык')
Модель формата:
• Должна быть заключена в апострофы.
• Различает символы верхнего и нижнего
регистров.
• Может включать любые разрешенные
элементы формата даты.
• Использует элемент fm для удаления конечных
пробелов и ведущих нулей.
• Отделяется от значения даты запятой.
Copyright © Oracle. All rights reserved.

30. Явное преобразование типов данных

Элементы модели формата даты
Элемент
YYYY
Результат
YEAR
Год прописью (на английском)
MM
Двузначное цифровое обозначение
месяца
MONTH
Полное название месяца
MON
Трехзначное алфавитное сокращенное
название месяца
DY
Трехзначное алфавитное сокращенное
название дня недели
DAY
Полное название дня недели
DD
Номер дня месяца
Полный год цифрами
Copyright © Oracle. All rights reserved.

31. Использование функции TO_CHAR с датами

Элементы модели формата даты
Элементы, которые задают формат части даты,
обозначающей время:
HH24:MI:SS AM
Символьные строки добавляются в кавычках:
DD "of" MONTH
15:45:32 PM
12 of OCTOBER
Числовые суффиксы используются для
вывода числительных прописью:
ddspth
fourteenth
Copyright © Oracle. All rights reserved.

32. Элементы модели формата даты

Использование функции TO_CHAR с
датами
SELECT last_name,
TO_CHAR(hire_date, 'fmDD Month YYYY')
AS HIREDATE
FROM
employees;
…
Copyright © Oracle. All rights reserved.

33.

Использование функции TO_CHAR с
числами
TO_CHAR(число, 'модель_формата')
Форматы, используемые с функцией TO_CHAR
для вывода числового значения в виде
символьной строки:
Элемент
9
Результат
0
Вывод нуля
$
Плавающий знак доллара
L
Плавающий символ местной валюты
.
Вывод десятичной точки
,
Вывод разделителя троек цифр
Цифра
Copyright © Oracle. All rights reserved.

34. Элементы модели формата даты

Использование функции TO_CHAR с
числами
SELECT TO_CHAR(salary, '$99,999.00') SALARY
FROM
employees
WHERE last_name = 'Ernst';
Copyright © Oracle. All rights reserved.

35.

Использование функций
TO_NUMBER и TO_DATE
Преобразование символьной строки в числовой
формат с использованием функции TO_NUMBER:
TO_NUMBER(char[, 'модель_формата'])
Преобразование символьной строки в формат
даты с использованием функции TO_DATE:
TO_DATE(char[, 'модель_формата'])
В этих функциях можно использовать
модификатор fx. В функции TO_DATE он задает
точное соответствие символьного аргумента и
модели формата даты.
Copyright © Oracle. All rights reserved.

36. Использование функции TO_CHAR с датами

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

37. Использование функции TO_CHAR с числами

Пример формата даты RR
Чтобы найти сотрудников, принятых на работу до
1990 года, используйте формат RR . Выполнение
команды даст одинаковый результат, независимо
от того, когда выполнялась команда (сейчас или в
1999 году):
SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-YYYY')
FROM employees
WHERE hire_date < TO_DATE('01-Jan-90','DD-Mon-RR');
Copyright © Oracle. All rights reserved.

38.

Вложенные функции
Однострочные функции могут быть вложены
на любую глубину.
Вложенные функции вычисляются от самого
глубокого уровня к внешнему.
F3(F2(F1(столбец,арг1),арг2),арг3)
Шаг 1 = Результат 1
Шаг 2 = Результат 2
Шаг 3 = Результат 3
Copyright © Oracle. All rights reserved.

39. Использование функции TO_CHAR с числами

Вложенные функции
SELECT last_name,
UPPER(CONCAT(SUBSTR (LAST_NAME, 1, 8), '_US'))
FROM
employees
WHERE department_id = 60;
Copyright © Oracle. All rights reserved.

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

Общие функции
Эти функции работают с любыми типами данных
и обрабатывают неопределенные значения:
• NVL (выражение1, выражение2)
• NVL2 (выражение1, выражение2, выражение3)
• NULLIF (выражение1, выражение2)
• COALESCE (выражение1, выражение2, ...,
выражениеn)
Copyright © Oracle. All rights reserved.

41. Using the TO_NUMBER and TO_DATE Functions

Функция NVL
Преобразует неопределенное значение в
действительное:
• Используемые типы данных – DATE,
символьные (CHARACTER) и числовые (NUMBER).
Типы данных должны совпадать:
– NVL(commission_pct,0)
– NVL(hire_date,'01-JAN-97')
– NVL(job_id,'No Job Yet')
Copyright © Oracle. All rights reserved.

42. Формат даты RR

Использование функции NVL
1
SELECT last_name, salary, NVL(commission_pct, 0),
(salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
FROM employees;
…
1
Copyright © Oracle. All rights reserved.
2
2

43. Пример формата даты RR

Использование функции NVL2
SELECT last_name, salary, commission_pct,
1
NVL2(commission_pct,
2
'SAL+COMM', 'SAL') income
FROM
employees WHERE department_id IN (50, 80);
1
Copyright © Oracle. All rights reserved.
2

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

Использование функции NULLIF
1
SELECT first_name, LENGTH(first_name) "expr1",
2
last_name, LENGTH(last_name) "expr2",
NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM
employees;
…
1
Copyright © Oracle. All rights reserved.
2
3
3

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

Использование функции COALESCE
Преимущество функции COALESCE по
сравнению с функцией NVL состоит в том, что
функция COALESCE может обрабатывать
несколько альтернативных значений.
Если первое выражение определенно, функция
возвращает это выражение; в противном
случает она проверяет оставшиеся выражения
Copyright © Oracle. All rights reserved.

46. Общие функции

Использование функции COALESCE
SELECT last_name,
COALESCE(manager_id,commission_pct, -1) comm
FROM
employees
ORDER BY commission_pct;
…
Copyright © Oracle. All rights reserved.

47. Функция NVL

Условные выражения
Позволяют применять логические конструкции
ЕСЛИ-ТО-ИНАЧЕ (IF-THEN-ELSE) внутри
команды SQL
Два метода:
– выражение CASE
– функция DECODE
Copyright © Oracle. All rights reserved.

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

Выражение CASE
Помогает создавать условные запросы, которые
выполняют действия логического оператора
IF-THEN-ELSE:
CASE выражение
WHEN сравн_выражение1 THEN возвр_выражение1
[WHEN сравн_выражение2 THEN возвр_выражение2
WHEN сравн_выражениеn THEN возвр_выражениеn
ELSE else_выражение]
END
Copyright © Oracle. All rights reserved.

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

Использование выражения CASE
Помогает создавать условные запросы, которые
выполняют действия логического оператора
IF-THEN-ELSE:
SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP'
THEN 1.20*salary
ELSE
salary END
"REVISED_SALARY"
FROM
employees;
…
…
Copyright © Oracle. All rights reserved.

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

Расширенное (поисковое) выражение
CASE
CASE
WHEN условие1 THEN возвр_выражение1
[WHEN условие2 THEN возвр_выражение2
WHEN условиеN THEN возвр_выражениеN
ELSE else_выражение]
END
Copyright © Oracle. All rights reserved.

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

Использование расширенного
(поискового) выражения CASE
SELECT last_name,salary,
(CASE WHEN salary < 1000 THEN 'Low'
WHEN salary BETWEEN 1000 AND 3000 THEN 'Medium'
WHEN salary > 3000 THEN 'High'
ELSE 'N/A' END) salary
FROM employees
ORDER BY last_name;
Copyright © Oracle. All rights reserved.

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

Функция DECODE
Помогает создавать условные запросы, которые
выполняют действия логического условия CASE
или оператора IF-THEN-ELSE:
DECODE(столбец|выражение, вариант1, результат1
[, вариант2, результат2,...,]
[, результат_по_умолчанию])
Copyright © Oracle. All rights reserved.

53. Условные выражения

Использование функции DECODE
SELECT last_name, job_id, salary,
DECODE(job_id, 'IT_PROG', 1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP',
1.20*salary,
salary)
REVISED_SALARY
FROM
employees;
…
…
Copyright © Oracle. All rights reserved.

54. Выражение CASE

Использование функции DECODE
Показать ставку налога на заработную плату для
сотрудников 80 отдела:
SELECT last_name, salary,
DECODE (TRUNC(salary/2000, 0),
0, 0.00,
1, 0.09,
2, 0.20,
3, 0.30,
4, 0.40,
5, 0.42,
6, 0.44,
0.45) TAX_RATE
FROM
employees
WHERE department_id = 80;
Copyright © Oracle. All rights reserved.

55. Использование выражения CASE

Итоги
С помощью функций осуществляются:
• Вычисления с данными
• Изменение отдельных элементов данных
• Манипулирование выводом групп строк
• Изменение форматов дат для вывода
• Преобразование формата данных столбцов
• Обработка неопределенных значений
• Логическая обработка IF-THEN-ELSE
Copyright © Oracle. All rights reserved.

56. Расширенное (поисковое) выражение CASE

Обзор практического занятия 3 , часть 2
Составление запросов, требующих
использования числовых, символьных
функций и функций для работы с датами.
Использование конкатенации с функциями.
Составление запросов, нечувствительных к
регистру символов, для проверки полезности
символьных функций.
Вычисление продолжительности работы
служащего в месяцах и годах.
Определение даты аттестации служащего
Copyright © Oracle. All rights reserved.