А.М. Гудов 1 ОДНОСТРОЧНЫЕ ФУНКЦИИ

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

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

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

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

А.М. Гудов 6 Символьные функции LOWERLOWERПреобразование в нижний регистр UPPERUPPERПреобразование в верхний регистр INITCAPINITCAPПреобразование начальных букв в верхний регистр CONCATCONCATКонкатенация значений SUBSTRSUBSTRВозврат подстроки LENGTHLENGTHВозврат количества символов NVLNVLПреобразование неопределенного значения

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

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

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

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

А.М. Гудов 11 Функции для работы с датами MONTHS_BETWEENMONTHS_BETWEENЧисло месяцев, разделяющих две даты ADD_MONTHSADD_MONTHSДобавление календарных месяцев к дате NEXT_DAYNEXT_DAYБлижайшая дата, когда наступит заданный день недели LAST_DAYLAST_DAYПоследняя дата текущего месяца ROUNDROUNDОкругление до целых суток TRUNCTRUNCОтсечение части даты, обозначающей время

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

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

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

А.М. Гудов 15 Элементы формата даты YYYYYYYY - полный год цифрами YEARYEAR - год прописью MMMM - двузначное цифровое обозначение месяца MONTHMONTH - полное название месяца DYDY - трехзначное алфавитное сокращенное название дня недели DAYDAY - полное название дня Элементы, которые задают формат части даты, обозначающей время. –HH24:MI:SS AM –HH24:MI:SS AM15:45:32 PM Символьные строки добавляются в кавычках. –DD " of " MONTH –DD " of " MONTH12 of OCTOBER Числовые суффиксы используются для вывода числительных прописью. –ddspth –ddspthfourteenth

А.М. Гудов 16 Текущий год Заданная дата 27-OCT-9527-OCT-1727-OCT-1727-OCT-95 Формат RR Формат YY Формат даты RR Год, заданный двузначным числом Если две последних цифры текущего года равны: Возвращаемая дата относится к текущему столетию. Возвращаемая дата относится к столетию после текущего. Возвращаемая дата относится к столе-тию перед текущим. Возвращаемая дата относится к текущему столетию..

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

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

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

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

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

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

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

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