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

1

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

3 Однострочные Функция - Символьные - Числовые - Функции даты Однострочная - Функции преобразования Многострочные - Групповые Многострочная

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

5 Символьные Числовые Функции работы с датой Функции преобразования Общие функции

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

7 Преобразование регистра для строки символов LOWER('SQL Course')sql course UPPER('SQL Course')SQL COURSE INITCAP('SQL Course')Sql Course

8 Вывод имени и фамилии всех служащих с фамилией Patel. SQL> SELECT first_name, last_name 2> FROM s_emp 3> WHERE last_name = 'PATEL'; no rows selected SQL> SELECT first_name, last_name 2> FROM s emp 3> WHERE UPPER(last_name) = 'PATEL'; FIRST_NAMELAST_NAME VikramPatel RadhaPatel

9 Работа с символьными строками CONCAT('Good', 'String')GoodString SUBSTR('String',1,3)Str LENGTH('String')6

10 LPAD RPAD LPAD(строка', n [,символ]) Возврат подстроки, дополненной заданными символами до необходимого размера слева или справа SQL> SELECT LPAD('Page 1',15,'*.') LPAD_example 2 FROM DUAL; LPAD example *.*.*.*.*Page 1

11 ROUNDОкругляет значение до заданной точности. TRUNCУсекает значение до заданного количества десятичных знаков. MODВозвращает остаток от деления.

12 ROUND (45.923,2) ROUND (45.923,0)46 ROUND (45.923,-1)50 TRUNC (45.923,2)45.92 TRUNC (45.923)45 TRUNC (45.923,-1)40

13 Вычисление остатка от деления одного значения на другое MOD(1600,300) 100

14 Oracle поддерживает оба типа и дата и время, хотя по-другому, чем в стандарте SQL2. Oracle использует один тип DATE, а не два - для даты и времени. Тип DATE хранится в специальном внутреннем формате, который включает не только месяц, день, и год, но также и час, минута, и секунда. create table x(a int, b date);

15 "DD-MON-YY"

16 ' '

17 Oracle хранит данные во внутреннем цифровом формате. - Век, год, месяц, число, часы, минуты, секунды По умолчанию дата выдается в формате DD-MON-YY (число- месяц-год) Функция SYSDATE возвращает дату и время DUAL - это фиктивная таблица, используемая для просмотра SYSDATE. Пример: вывод текущей даты с помощью таблицы DUAL. SQL> SELECT SYSDATE 2> FROM DUAL;

18 Результатом прибавления числа к дате и вычитания числа из даты является дата. Результатом вычитания одной даты из другой является количество дней, разделяющих эти даты. Прибавление часов к дате производится путем деления количества часов на 24.

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

20 MONTHS_BETWEEN('01-SEP-95','11-JAN-94') 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'

21 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

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

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

24 YYYY - полный год цифрами YEAR - год прописью ММ -двузначное цифровое обозначение месяца MONTH - полное название месяца DY - трехзначное алфавитное сокращенное название дня недели DAY - полное название дня

25 Форматы времени

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

27 Элементы, связанные со временем, задают формат части даты, указывающей время. - HH24:MI:SS AM15:45:32 РМ Символьные строки добавляются в кавычках, - DD " Of " MONTH12 of OCTOBER Числовые суффиксы используются для вывода числительных словами. - ddspthfourteenth

SELECT last_name, TO_CHAR (start_date, 2 'fmDD "of" Month YYYY') HIREDATE 3 FROM s_emp 4 WHERE start_date LIKE '%9" title="Пример Вывод имен и дат найма всех служащих, нанятых в 1991 году. Вывод дат должен производиться в формате типа "7 February 1991". SQL> SELECT last_name, TO_CHAR (start_date, 2 'fmDD "of" Month YYYY') HIREDATE 3 FROM s_emp 4 WHERE start_date LIKE '%9" class="link_thumb"> 28 Пример Вывод имен и дат найма всех служащих, нанятых в 1991 году. Вывод дат должен производиться в формате типа "7 February 1991". SQL> SELECT last_name, TO_CHAR (start_date, 2 'fmDD "of" Month YYYY') HIREDATE 3 FROM s_emp 4 WHERE start_date LIKE '%91'; LAST_NAMEHIREDATE Nagayama17 of June 1991 Urguhart18 of January 1991 Havel27 of February 1991 SELECT last_name, TO_CHAR (start_date, 2 'fmDD "of" Month YYYY') HIREDATE 3 FROM s_emp 4 WHERE start_date LIKE '%9"> SELECT last_name, TO_CHAR (start_date, 2 'fmDD "of" Month YYYY') HIREDATE 3 FROM s_emp 4 WHERE start_date LIKE '%91'; LAST_NAMEHIREDATE Nagayama17 of June 1991 Urguhart18 of January 1991 Havel27 of February 1991"> SELECT last_name, TO_CHAR (start_date, 2 'fmDD "of" Month YYYY') HIREDATE 3 FROM s_emp 4 WHERE start_date LIKE '%9" title="Пример Вывод имен и дат найма всех служащих, нанятых в 1991 году. Вывод дат должен производиться в формате типа "7 February 1991". SQL> SELECT last_name, TO_CHAR (start_date, 2 'fmDD "of" Month YYYY') HIREDATE 3 FROM s_emp 4 WHERE start_date LIKE '%9">

29 ТО_СНАR(число, fmt) Форматы, используемые с функцией TO_CHAR для вывода символьного значения в виде числа: 9 - цифра. 0 - вывод нуля. $ - плавающий знак доллара. L - плавающий символ местной валюты. - вывод десятичной точки., - вывод разделителя троек цифр.

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

31 Работа с любыми типами данных NVL DECODE GREATEST LEAST

32 Аналогична серии вложенных операторов IF-THEN-ELSE Выражение последовательно сравнивается со значениями Значение_по_умолчанию возвращается, если выражение не соответствует ни одному из значений DECODE (выражение, значение 1, результат 1, значение 2, результат 2, … значение, результат, значение_по_умолчанию)

33 SQL> SELECT DECODE (deptno, 10, 'ACCOUNTING', 2 20, 'RESEARCH', 3 30, 'SALES', 4 40, 'OPERATION', 5 'NONE') 6 FROM DEPT; DECODE(DEP ACCOUNTING RESEARCH SALES OPERATION

34 Однострочные функции могут быть вложены на любую глубину. Вложенные функции вычисляются от самого глубокого уровня к внешнему. F3(F2(Fl(col,arg1),arg2),arg3) Шаг 1 = результат 1 Шаг 2 = результат 2 Шаг 3 = результат 3

35 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')

36 Однострочные функции могут быть вложены на любую глубину. Однострочные функции работают с символьными, числовыми данными и данными типа "дата". К функциям преобразования относятся ТО_CHAR, TO_DATE, TO_NUMBER. SYSDATE - псевдо столбец, используемый для вывода текущей даты и времени. DUAL - фиктивная таблица в базе данных.