- •Использование однострочных функций.
- •1. Функции языкаOracleSql.
- •2. Типы функций
- •3. Однострочные функции (Single-rowFunctions)
- •3.1 Символьные функции
- •3.1.2 Функции манипулирования символами
- •Instr(last_name, 'a') as "Contains 'a'?"
- •3.2 Числовые функции.
- •3.3 Работа с датами.
- •3.3.1 Арифметические операции с датами.
- •3.4 Функции для работы с датами.
- •3.5 Функции преобразования (Conversion Functions)
- •3.5.2 Явное преобразование типов.
- •3.5.6 Формат даты rr
- •3.6 Общие функции (GeneralFunctions)
- •3.6.1 Функция nvl.
- •3.6.2 Функция nvl2
- •3.7.2 Функция decode
Использование однострочных функций.
Цели: После освоение материала данной лекции, студенты должны быть в состоянии выполнить следующие действия:
1. Описать типы функций языка OracleSQL;
2. Использовать различные типы функций: символьные, числовые и типа «дата» - в команде SELECT;
3. Назвать и использовать функции преобразования данных.
1. Функции языкаOracleSql.
Функции являются очень мощным средством SQL и используются в следующих целях:
выполнения вычислений над данными;
изменения отдельных единиц данных;
управления выводом групп строк;
форматирования чисел и дат для вывода;
преобразования типов данных.
2. Типы функций
3. Однострочные функции (Single-rowFunctions)
Эти функции работают только с однойстрокой и возвращают по одному результату для каждой строки.
Аргументом может быть:
константа, заданная пользователем;
значение переменной;
имя столбца;
выражение.
Особенности однострочных функций:
обрабатывают каждую строку, возвращаемую запросом;
возвращают по одному результату для строки;
могут изменять тип данных: тип данных на выходе может отличаться от типа данных, к которым обращается пользователь;
могут принимать один или несколько аргументов;
могут использоваться в предложениях SELECT,WHEREиORDERBY;
могут быть вложенными.
3.1 Символьные функции
3.1.1 Функции преобразования регистра символов:
Функция |
Назначение |
LOWER(столбец | выражение) |
Преобразует алфавитные символы в нижний регистр. |
INITCAP(столбец | выражение) |
Преобразует символы по правилу: первая буква каждого слова становится заглавной, остальные – строчные. |
UPPER(столбец | выражение) |
Преобразует алфавитные символы в верхний регистр. |
Пример 1
Функция |
Результат |
LOWER('SQL Course') |
sql course |
INITCAP('SQL Course') |
Sql Course |
UPPER('SQL Course') |
SQL COURSE |
Пример 2
Выбрать номер, фамилию и номер отдела, в котором работает сотрудник по фамилии Higgins:
SELECT employee_id, last_name, department_id
FROM employees
WHERE last_name = 'higgins';
no rows selected
используем функцию LOWER:
SELECT employee_id, last_name, department_id
FROM employees
WHERE LOWER(last_name) = 'higgins' ;
3.1.2 Функции манипулирования символами
Функция |
Назначение |
CONCAT(char1, char2) |
Присоединяет к строке char1 строку char2. |
SUBSTR(char, position [, substring_length ]) |
Возвращает часть строки char, начиная с символа position, длиною в substring_length символов. Если position отрицательно, отсчёт начинается с конца строки char. Если число substring_length не указано, возвращаются все символы до конца строки. |
LENGTH(char) |
Возвращает длину строки char. |
INSTR(string, substring [, position [, occurrence ] ]) |
Ищет подстроку substring в строке string. Возвращает номер позиции первого символа подстроки substring в строке string. Необязательный параметр position задаёт номер символа в строке string, с которого начнётся поиск. Необязательный параметр occurrence задаёт число обнаружений подстроки в строке. По умолчанию значения параметров position и occurrence равны 1. |
LPAD(expr1, n [, expr2 ]) |
Дополняет символьное значение expr1 слева до длины n заданной последовательностью символов expr2. Если expr2 не указано, по умолчанию используется символ «пробел». |
RPAD(expr1 , n [, expr2 ]) |
Дополняет символьное значение expr1 справа до длины n заданной последовательностью символов expr2. Если expr2 не указано, по умолчанию используется символ «пробел». |
REPLACE(char, search_string [, replacement_string ] ) |
Производит замену каждого вхождения подстроки search_string в строке char на подстроку replacement_string. Если в качестве подстроки replacement_string указано неопределённое значение NULL или данный параметр не указан вовсе, то каждое вхождение подстроки replacement_string удаляется. |
TRIM([ { { LEADING | TRAILING | BOTH } [ trim_character ] | trim_character } FROM ] trim_source ) |
Позволяет удалять начальные LEADING или конечные TRAILING символы trim_character или и те, и другие BOTH из строки trim_source. Если значение trim_character не указано, то по умолчанию оно равно символу «пробел». |
Пример 1.
Функция |
Результат |
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 |
Пример 2.
SELECT employee_id, CONCAT(first_name, last_name) AS NAME,
job_id, LENGTH(last_name),