Функции Oracle SQL
Курсовая работа
Функции Oracle SQL
Введение
oracle регистр строковый
арифметический
Основные идеи современной информационной технологии
базируются на концепции, согласно которой данные должны быть организованы в
базы данных с целью адекватного отображения изменяющегося реального мира и
удовлетворения информационных потребностей пользователей.
Актуальность: Компьютер является не более чем
устройством преобразования информации. Если информации не очень много, то
основное время при переработке занимает сам алгоритм преобразования. Если при
этом приходится работать с большими объемами данных - эффективность обработки
начинает напрямую зависеть от эффективности получения данных, их фильтрации и
пр. С целью упрощения процесса разработки, повышения производительности работы
подобных систем, были созданы различные СУБД. Они имеют свой собственный формат
хранения данных, свои алгоритмы их поиска и извлечения, но базовый язык
запросов у большинства из них один. И этот язык - SQL.
Цели:
1. Получение теоретических и практических знаний по функциям Oracle SQL;
. Применение теории на практике.
Задачи: Данный курс посвящен изучению функций Oracle SQL, а также основных
приемов работы с ними. В данной работе мы изучим функции, работающие с простыми
однострочными входными параметрами, возвращающие для каждой строки результат.
1.
Функции Oracle SQL
Функция аналогична оператору в том, что она манипулирует
элементами данных и возвращает результат. Функции отличаются от операторов
форматом, в котором они задаются со своими аргументами. Этот формат позволяет
функциям оперировать на нуле, одном, двух или большем количестве
аргументов:(argument, argument,…)
Функции могут использоваться для выполнения расчетов с
данными, преобразования типов данных, изменения форматов вывода дат и т.д.
Функции SQL бывают двух основных типов:
1. однострочные (или скалярные)
функции;
2. групповые (или агрегатные) функции.
Эти функции различаются количеством строк, на которых они
оперируют. Однострочная функция возвращает единственное значение для каждой
строки таблицы, в то время как групповая функция возвращает единственное
значение для целой группы строк.
Однострочные функции. Однострочные функции
могут появляться в предложениях SELECT, WHERE и ORDER BY команды SELECT. В
качестве аргументов они могут принимать константы, заданные пользователем,
значения переменных, имена столбцов таблицы БД или выражения, составленные с
помощью операторов и функций.
1.1 Список функций
Все однострочные функции, как правило, разбивают на несколько
групп по типам данных их аргументов и возвращаемых значений. Выделяют:
· числовые функции;
· символьные функции;
· функции для работы с
датами;
· функции преобразования.
1.2 Символьные функции
Символьные функции оперируют на строковых значениях. Для
упрощения работы со строками имеется ряд встроенных функций, что значительно
облегчает различные операции.
Функции преобразования регистра символов
Функция определения длины строки LENGTH(строка), возвращает
количество символов в строке, включая концевые пробелы.
SELECT LENGTH(string) FROM
DUAL вернет значение 7.
Функции преобразования регистров символов UPPER(строка),
LOWER(строка), INITCAP(строка). Для преобразования символов к верхнему регистру
используется функция UPPER().
SELECT UPPER(string) FROM DUAL вернет STRING.
Если необходимо преобразовать символы строки к нижнему
регистру используется функция LOWER ().
SELECT LOWER(String) FROM
DUAL вернет STRING.
Функция INITCAP преобразовывает каждый первый символ слова к
верхнему регистру, а все остальные символы к нижнему при условии, что
символ-разделитель между словами пробел.
SELECT INITCAP (string1
string2) FROM DUAL вернет строку STRING1 STRING2.
Функции для обрезания начальных и концевых пробелов
LTRIM(строка), RTRIM(строка), TRIM(строка). Соответственно первая функция
обрезает все начальные пробелы строки, вторая - все концевые, а третья все
начальные и концевые.
SELECT LTRIM(‘str1’) FROM DUAL вернет строку str1,
SELECT RTRIM(‘str2’) FROM DUAL вернет строку str2,
SELECT TRIM(‘str3’) FROM DUAL вернет строку str3.
Функция замены части строки другой строкой REPLACE (исходная
строка, заменяемая подстрока, заменяющая подстрока). Для большей ясности
рассмотрим пример:
В некотором текстовом поле таблицы хранится число, причем
символ-разделитель между целой и дробной частью в некоторых полях «.», а нам
для дальнейшей обработки данных нужно, чтобы он во всех полях должен быть «,».
Для этого воспользуемся функцией REPLACE следующим образом:
REPLACE (field1,’.’, ’,’) и все символы «.» в поле field будут заменены на
символ «,».
SELECT REPLACE (‘My_string’,
’_’, ’@’) FROM DUAL вернет строку My@string.
Функции преобразования данных к другим типам данных:
TO_CHAR(число) преобразует число в текст, TO_NUMBER(строка) преобразует текст в
число, TO_DATE (строка, формат даты) преобразует строку в дату определенного
формата.
SELECT TO_CHAR(123) FROM DUAL вернет строку 123,
SELECT TO_NUMBER(‘12345’) FROM
DUAL вернет число 12345,
SELECT TO_DATE («01.01.2010’,
’dd.mon.yyyy’) FROM DUAL вернет дату 01.JAN.2010.
Функция определения вхождения подстроки в строку INSTR
(исходная строка, подстрока, номер символа). Данная функция позволяет
определять номер символа в исходной строке, с которого начинается искомая
подстрока (если такая есть). Иначе возвращается 0. Например, нам нужно
определить все должности в таблице Table1, в наименовании которых встречается
подстрока «менеджер». Для этого вполне подойдет следующий оператор:
SELECT*FROM TABLE1 WHERE INSTR (POST, ‘менеджер’, 1) > 0.
То есть оператор SELECT выведет только те записи из таблицы
TABLE1, где искомая подстрока «менеджер» будет найдена. Причем поиск будет
осуществляться с первого символа. Если поиск нужно осуществлять с другой
позиции, то номер символа для начала поиска указывается в третьем параметре.
SELECT INSTR (‘Small string’,
‘string’, 1) FROM DUAL вернет значение 7,
SELECT INSTR (‘Small string’,
‘String’, 1) FROM DUAL вернет значение 0.
Функция выделения в исходной строке подстроки SUBSTR
(исходная строка, номер начального символа, количество символов). Рассмотрим
такой пример, в пользовательской таблице хранится адрес в виде наименование
населенного пункта, название улицы, номер дома. Причем мы точно знаем, что для
наименования населенного пункта отводится строго 20 символов (если в
наименовании населенного пункта меньше чем 20 символов, то остальная часть
заполняется пробелами), для наименования улицы 30 символов, для номера дома 3
символа. Далее нам необходимо перенести все адреса из нашей таблицы в другую,
при этом все 3 компонента адреса должны быть в разных полях. Для выделения компонент адреса применим функцию SUBSTR().
SELECT SUBSTR (TABLE_1.ADDRESS,
1,20) CITY, SUBSTR (TABLE_1.ADDRESS, 21,30) STREET, SUBSTR (TABLE_1.ADDRESS,
52, 3) TOWN FROM TABLE_1;
Конечно, для переноса данных необходимо воспользоваться
оператором INSERT, но для понимания работы функции SUBSTR вполне подойдет
рассмотренный пример.
SELECT SUBSTR (‘My_string’, 4,
3) FROM DUAL вернет строку str.
Рассмотренные выше функции можно использовать во входных
параметрах. Так если нам нужно выделить все символы, после какого-то
определенного, то в функцию SUBSTR можно передать номер искомого символа из
функции INSTR. Например, если нужно перенести все символы из поля таблицы,
которые расположены после «,» то можно использовать такую конструкцию:
SELECT SUBSTR (My_string, INSTR
(My_string, ‘, ’,1), LENGTH (My_string) - INSTR (My_string, ‘, ’, 1)+1) FROM
DUAL.
Для определения начального символа мы вызываем функцию
INSTR(), которая вернет номер символа первого вхождения подстрок «,». Далее мы
определяем количество символов до конца строки как разницу длины строки и
номера первого вхождения подстроки.
Для определения кода символа используется функция ASCII
(строка), которая возвращает код 1 символа строки. Например:
SELECT ASCII(W) FROM DUAL вернет значение 87.
Обратная функция преобразования кода символа в символ CHR
(число).
SELECT CHR (87) FROM DUAL вернет символ W.
Функции манипулирования символьными строками
Oracle предлагает обширный набор функций для манипулирования
строковыми данными:
CHR(N) - Возвращает символ ASCII кода для десятичного кода N;
ASCII(S) - Возвращает десятичный ASCII код первого символа
строки;
INSTR (S2. S1.pos[, N] - Возвращает позицию строки S1 в
строке S2 большую или равную pos. N - число вхождений;
LENGHT(S) - Возвращает длину строки;
LOWER(S) - Заменяет все символы строки на прописные символы;
INITCAP(S) - Устанавливает первый символ каждого слова в
строке на заглавный, а остальные символы каждого слова - на прописные;
SUBSTR (S, pos, [, len]) - Выделяет в строке S подстроку
длиной len, начиная с позиции pos;
LPAD (S, N[, A]) - Возвращает строку S, дополненную слева
символами A до числа символов N. Символ - наполнитель по умолчанию - пробел;
RPAD (S, N[, A]) - Возвращает строку S, дополненную справа
символами A до числа символов N. Символ - наполнитель по умолчанию - пробел;
LTRIM (S, [S1]) - Возвращает усеченную слева строку S.
Символы удаляются до тех пор, пока удаляемый символ входит в строку - шаблон S1
(по умолчанию - пробел);
RTRIM (S, [S1]) - Возвращает усеченную справа строку S.
Символы удаляются до тех пор, пока удаляемый символ входит в строку - шаблон S1
(по умолчанию - пробел);
TRANSLATE (S, S1, S2) - Возвращает строку S, в которой все
вхождения строки S1 замещены строкой S2. Если S1 <> S2, то символы,
которым нет соответствия, исключаются из результирующей строки;
REPLACE (S, S1, [, S2]) - Возвращает строку S, для которой
все вхождения строки S1 замещены на подстроку S2. Если S2 не указано, то все
вхождения подстроки S1, удаляются из результирующей строки;
NVL (X, Y) - Если Х есть NULL, то возвращает в Y либо строку,
либо число, либо дату в зависимости от исходного типа Y;
SOUNDEX(S) - Возвращает фонетическое представление строки;
1.3 Лабораторная работа. Работа со строковыми функциями
Строковые функции в запросах Oracle SQL, функции UPPER(),
CONCAT(), SUBSTR().
Задание:
Вам потребовалось создать для каждого сотрудника
идентификатор, который должен выглядеть как 3 первые символа имени плюс два
первых символа фамилии. Все символы этого идентификатора должны быть
представлены в верхнем регистре.
Напишите запрос, который возвращал бы из таблицы hr.employees
информацию об имени и фамилии сотрудника, а также идентификатор сотрудника в
соответствии с поставленными условиями. Результат выполнения запроса должен
быть таким, как представлено на рис. 3.1-1.
Решение:
Код соответствующего запроса может быть таким:
SELECT first_name AS «Имя»,
last_name As «Фамилия», UPPER (CONCAT(SUBSTR (first_name, 1, 3), SUBSTR
(last_name, 1,2))) AS «Идентификатор» FROM hr.employees.
2.
Числовые функции
Числовые
функции принимают в качестве аргументов и возвращают в качестве результата
числовые значения. В следующей таблице приведены некоторые из числовых функций:
)
ABS(n) - Возвращает абсолютную величину n.
)
MOD (m, n) - Возвращает остаток от деления m на n. Если n=0, возвращает m;
)
WER (m, n) - Возвращает m в степени n. Основание m и степень n могут быть
любыми числами, но если m отрицательно, то n должно быть целым.
)
ROUND (m[, n]) - Возвращает m, округленное до n позиций после десятичной точки;
если n опущено, то до целого, если n отрицательно, округляется целая часть
числа. n должно быть целым.
)
SQRT(n) - Возвращает квадратный корень из n. n должно быть неотрицательным.
)
TRUNC (m[, n]) - Возвращает m, усеченное до n цифр после десятичной точки. Если
n опущено, усечение выполняется до целого. n может быть отрицательным, что
приводит к усечению (обнулению) n цифр слева от десятичной точки.
2.1 Функция Round
Функция округления ROUND (число_1, число_2). Округляет первый
переданный параметр до количества разрядов, переданного во втором параметре.
Если второй параметр не указан, то он принимается равным 0, то есть округление
производится до целого значения. Примеры:
SELECT ROUND (101.34) FROM DUAL
вернет значение 101,
SELECT ROUND (100. 1268,2) FROM
DUAL вернет значение 100.13
SELECT ROUND (1234000.3254, -2)
FROM DUAL вернет значение 1234000,
SELECT ROUND (-100.122, 2) FROM
DUAL вернет значение -100.12.
2.2 Функция Trunc
Функция усечения значения TRUNC (число_1, число_2).
Возвращает усеченное значение первого параметра до количества десятичных
разрядов, указанного во втором параметре. Примеры:
SELECT TRUNC (150.58) FROM DUAL вернет значение 150;
SELECT TRUNC (235.4587, 2) FROM DUAL вернет значение 235.45;
SELECT TRUNC (101.23, -1) FROM
DUAL вернет значение 100;
.3 Функция Mod
Функция MOD (число_1, число_2) возвращает остаток от деления
первого параметра на второй. Пример:
SELECT MOD (5, 3) FROM DUAL вернет значение 2.
Примечание. Если второй параметр равен 0, то функция
возвращает первый параметр.
3. Работа с датами
В Oracle SQL имеется единый тип данных DATE, хранящей для
каждой величины, представленной в этом типе, значение года, месяца, дня, часов,
минут и секунд. Дата и время также могут храниться в форматах CHAR и NUMBER,
допускающих преобразование к типу DATE с помощью функции TO_DATE.
Константы даты / времени и формат представления
даты
Внешнее представление даты и времени - символьная константа,
преобразование которой во внутреннее представление определяется форматом,
заданным в NLS_DATE_FORMAT.
Спецификаторы NLS (National Language Support - поддержка
национальных языков) позволяют пользователю работать в среде с локализованными
средами окружения. Они определяют многие значения по умолчанию. От
спецификаторов NLS, определяющих соглашения на среду окружения, зависят
принимаемые по умолчанию аргументы ряда функций Oracle SQL, в том числе -
функций TO_CHAR, TO_DATE, TO_NUMBER.
3.1 Функция SYSDATE()
Для определения текущей даты и времени применяется функция
SYSDATE. Область применения данной функции намного шире, чем может показаться
на первый взгляд. В первую очередь, это контроль за вводом данных в БД. Во
многих таблицах выделяется отдельные поля для сохранения даты последнего
внесения изменений. Также очень удобно контролировать некие входные параметры
для отчетов, особенно если они не должны быть больше чем текущая дата. Помимо
даты данная функция возвращает еще и время с точностью до секунд. Пример:
SELECT SYSDATE FROM DUAL вернет дату ‘22.05.2010
14:51:20’
Пример (использования SYSDATE): Получить сегодняшнюю дату:
SELECT SYSDATE FROM
dual; Результат: SYSDATE.
3.2 Арифметические операции с датами
Данные типа DATE могут быть операндами операций увеличения,
уменьшения и вычитания.
В операциях увеличения и уменьшения тип первого операнда -
DATE, тип второго операнда - NUMBER. Тип результата - DATE.
В операции вычитания оба операнда должны иметь тип DATE,
результат имеет тип NUMBER.
Число типа NUMBER в арифметике даты и времени
интерпретируется как количество дней. Дробная часть числа - дробные части дня,
то есть, часы, минуты, секунды. Некоторые функции Oracle обеспечивают
дополнительную поддержку арифметики даты и времени.
3.3 Использование арифметических операторов с
датами
ADD_MONTHS (d, n) - возвращает дату d плюс n месяцев (n может
быть только целым);
MONTHS_BETWEEN (d1, d2) - возвращает, количество месяцев
между датами d1 и d2 (возможно, с дробной частью);
LAST_DAY(d) - возвращает дату последнего дня того месяца, к
которому относится дата d;
NEXT_DAY (d, c) - возвращает дату, соответствующую следующему
появлению дня недели, заданного текстовым значением c, после даты d;
ROUND (d, fmt) - возвращает дату, округленную до формата,
заданного вторым (необязательным) параметром;
TRUNC (d, fmt) - возвращает дату, усеченную до формата,
заданного вторым (необязательным) параметром.
3.4 Функции для работы с датами. Примеры
Функции для работы с датами оперируют на значениях типа DATE.
Все функции дат возвращают значения типа DATE, кроме функции MONTHS_BETWEEN,
которая возвращает целое значение. В следующей таблице приведены некоторые из
функций для работы с датами:
) ADD_MONTHS (дата, n) - Возвращает заданную дату,
увеличенную на n месяцев. Аргумент n может быть любым целым числом. Если
заданная дата попадает на последний день месяца, или если результирующий
месяц имеет меньше дней, чем компонента дня заданной даты, то
результирующий день будет последним днем результирующего месяца. В противном
случае результирующий день остается таким же, как компонента дня заданной даты.
) LAST_DAY(дата) - Возвращает дату последнего дня
месяца, содержащего указанную дату.
) MONTHS_BETWEEN (дата1, дата2) - Возвращает
количество месяцев между датой1 и датой2. Если дата1 -
более поздняя дата, чем дата2, то результат положителен; в противном
случае - отрицателен. Если дата и дата2 попадают на одинаковые
дни своих месяцев или на последние дни своих месяцев, то результат будет целым
числом; в противном случае Oracle вычисляет дробную часть результата.
) NEXT_DAY (дата, символ) - Возвращает дату
первого дня недели, имеющего имя символ и следующего за заданной датой.
Аргумент символ может задавать порядковый номер или название дня недели.
) ROUND (дата [, fmt]) - Возвращает заданную дату,
округленную к единицам, заданным моделью формата fmt. Если fmt опущена, то дата
округляется до ближайшего дня на момент полуночи.
) SYSDATE - Возвращает текущую дату и время, не требует
аргументов.
) TRUNC (дата [, fmt]) - Возвращает заданную дату,
в которой порция времени усечена к единицам, заданным моделью формата fmt. Если
fmt опущена, то дата округляется до ближайшего дня на момент полуночи.
Если необходимо определить последний день месяца, то для
этого вполне подойдет функции LAST_DAY(дата). Её можно использовать для
определения количества дней, оставшихся в месяце:
SELECT LAST_DAY(SYSDATE) -
SYSDATE FROM DUAL.
В результате выполнения данного оператора будет выведено
количество дней от текущей даты до конца месяца. Пример:
SELECT LAST_DAY («15-FEB-2010’)
FROM DUAL вернет дату «28.02.2010’.
Функция для определения количества месяцев между датами
MONTHS_BETWEEN (дата_1, дата_2).Примеры:
SELECT MONTHS_ BETWEEN
(«01-JUL-2009’, «01-JAN-2010’) FROM DUAL вернет значение -6;
Примечание. Если дни месяцев совпадают, то функция
возвращает целое число, в противном случае результат будет дробным, причем
количество дней в месяце будет принято 31.
Функция NEXT_DAY (дата, день недели) позволяет определить
следующую дату от даты, переданной в первом параметре, которая соответствует
дню недели, переданном во втором параметре. Пример: SELECT NEXT_DAY
(«01-JUL-2009’, ’mon’) FROM DUAL вернет дату ‘06.07.2009’, то есть
следующий понедельник после 1 июля 2009 наступил 6 числа.
Усечение даты. Функция TRUNC (дата, формат), также как и
рассмотренная выше, может не иметь второго параметра. В таком случае усечение
будет производиться до ближайшего дня. Примеры:
SELECT TRUNC(SYSDATE) FROM
DUAL вернет дату «22.05.2010’;
SELECT TRUNC (SYSDATE, ‘WW’) FROM
DUAL вернет дату «01.05.2010’;
SELECT TRUNC (SYSDATE, ‘Day’) FROM
DUAL вернет дату ‘16.05.2010’.
3.5 Лабораторная работа. Применение функций для
работы с датой / временем и арифметических функций
Функции для работы с датой / временем в Oracle SQL, функции MONTHS_BETWEEN(), SYSDATE().
Задание:
Напишите запрос, который бы возвращал информацию об именах и
фамилиях сотрудников из таблицы hr.employees, а также о дате приема каждого сотрудника на
работу и количестве полных месяцев, которое каждый сотрудник отработал по
настоящее время (настоящее время определяется по часам вашего компьютера).
Результат запроса должен выглядеть так, как представлено на
рис. 3.2-1.
Примечание:
Значение в столбце «Проработано месяцев» у вас может быть
другим, поскольку другим будет время на часах компьютера.
Решение:
Код соответствующего запроса может быть таким:
SELECT first_name AS «Имя», last_name
As «Фамилия», Salary AS «Оклад», HIRE_DATE
As «Дата приема на работу», TRUNC
(MONTHS_BETWEEN (SYSDATE, HIRE_DATE)) AS «Проработано месяцев» FROM hr.employees.
4. Функции преобразования
Функции преобразования преобразуют значения из одного типа
данных в другой. В следующей таблице приведены некоторые из функций
преобразования:
) TO_CHAR (число|дата [, fmt]) - Преобразует число
или дату в символьную строку VARCHAR2 с моделью формата fmt.
) TO_NUMBER(char) - Преобразует строку символов char
содержащую цифры, в число.
) TO_DATE (char [, fmt]) - Преобразует строку символов char с
датой в значение типа DATE в соответствии с заданной моделью fmt.
.1 Неявное и явное преобразование типов данных
Неявное преобразование типа данных может
оказать отрицательное влияние на производительность, особенно если тип данных
столбца преобразуется к типу данных константы, а не наоборот. Неявное
преобразование зависит от контекста, в котором оно происходит и, возможно, не
будет работать одинаково в каждом случае. Например, неявное преобразование
значения типа данных VARCHAR2 может возвратить неожиданный год в зависимости от
значения параметра NLS_DATE_FORMAT. Примером неявного преобразования может быть:
SELECT* FROM emp WHERE hiredate
between '01-JAN-1981' and '01-APR-1981'.
Явное преобразование типов данных:
SELECT* FROM emp WHERE
hiredate between TO_DATE ('01-JAN-1981', 'DD-MON-YYYY') and TO_DATE
('01-APR-1981', 'DD-MON-YYYY');
Различие состоит в том, что я ЯВНО
выполнил преобразование из символьного представления к дате с помощью функции TO_DATE,
в которой я указал правильную маску формата. Явное преобразование требует
дополнительного набора. Использование явных преобразований следует отнести к
лучшим методам для любой базы данных и любого языка программирования.
4.2 Функция TO_CHAR с датами
Чаще всего функция TO_CHAR используется для преобразования
даты при выводе. Дело в том, что Oracle выводит даты при выборке в формате,
установленном по умолчанию. Это может быть, например, формат ‘dd-mm-yy’,
‘dd-mon-yy’ или ‘dd. mm. yy’. Как правило, формат, используемый по умолчанию,
не содержит информации о времени, квартале месяца, столетии и пр. На практике
часто возникает необходимость вывести более полную информацию, задав
необходимый формат.
SELECT TO_CHAR (SYSDATE, 'dd -
mm - yyyy - hh24 - mi - ss') data FROM dual;
SELECT TO_CHAR (SYSDATE, 'dd
«of» Month «year» yyyy') data FROM dual.
В данном примере число состоит из одной цифры, поэтому перед
ним выводится ведущий 0, так как в модели формата указано, что число надо
выводить двумя цифрами. Кроме того, модель ‘Month’ предполагает, что название
месяца дополняется конечными пробелами до 9 символов. Чтобы убрать ведущие нули
и конечные пробелы, используется префикс FM. SELECT TO_CHAR
(SYSDATE, 'fmdd «of» Month «year» yyyy') data FROM dual;
SELECT TO_CHAR (SYSDATE,
'fmDAY: MONTH: YEAR') data FROM dual.
4.3 Элементы формата даты
) Знак пунктуации - Все знаки пунктуации дублируются в
результирующей строке символов.
) «Текст» - Текст, заключенный в двойные кавычки так же
дублируется.
) AM, A.M. - Показатель времени до полудня (с точками или без
точек).
) D - День недели (1-7), DD - День месяца (1 - 31), DDD -
День года (1 - 366).
) DAY - Название дня, дополненное пробелами до девяти
символов, DY - Сокращенное название дня.
) IW - Неделя года (1 - 52), (1 - 53) (в основе лежит
стандарт ISO).
) IYY, IY, I - Последние три, две или одна цифра года ISO,
IYYY - Четырех цифровое обозначение года, основанное на стандарте ISO.
) HH, HH12 - Час дня (1-12), HH24 - Час дня (0-23).
) MI - Минута (0-59), SS - Секунды (0-59).
) MM - Месяц (1 - 12), JAN = 1, DEC = 12.
) MONTH - Название месяца, дополненное пробелами до девяти
символов, MON - Сокращенное название месяца.
) PM P.M. - Показатель времени после полудня (с точками или
без точек).
) Q - Квартал года (1 - 4) С января по март - первый квартал.
) RM - Месяц, обозначенный римскими цифрами. (I - XII) JAN = I, DEC = XII, RR - Последние две
цифры года для других веков.
) SSSS - Секунды после полуночи (0 - 86399) Модель формата
'J.SSSSS' всегда будет давать в результате числовое значение.
) WW - Неделя года (1-53). Неделя 1 начинается с первого дня
года и продолжается до седьмого дня. таким образом, недели не всегда начинаются
с воскресенья как это принято в США, это что то вроде сквозного недельного
отсчета.
) Y, YYY - Год с запятой в указанной позиции, YEAR, SYEAR -
Год, записанный буквами, SYEAR возвращает даты до нашей эры как отрицательные
значения.
) YYYY, SYYYY - Четырех цифровой год. Возвращает даты до
нашей эры как отрицательные значения, YYY, YY, Y - Последние три, две или одна
цифра года.
.4 Использование суффиксов для вывода чисел
Задавая модель формата можно использовать знаки пунктуации
(/.,), а также символьные константы (заключенные в двойные кавычки), которые
будут включены в результат. Кроме того, можно использовать:
) Префикс FM - Подавляет конечные пробелы в названиях месяцев
и дней недели, оставляя результат переменной длины. Подавляются и ведущие нули
в числах. Повтор FM отменяет подавление.
) Суффикс TH - Для вывода порядковых числительных (например,
DDTH для вывода в формате «4th»).
) Суффикс SP - Для вывода чисел словами (например, DDSP для
вывода слова «FOUR»).
) Сочетание суффиксов SP и TH: SPTH или THSP - Для вывода
порядковых числительных словами (например, DDSPTH или DDTHSP для вывода слова
«FOURTH»).
.5 Лабораторная работа. Изменение выводимого
формата даты
Изменение выводимого формата даты в Oracle SQL, функция
TO_CHAR().
Задание:
Необходимо представить информацию из таблицы сервера Oracle в
соответствии с принятым на предприятии стандартом вывода данных, который
выглядит как год/месяц / число, например, 2008/02/20. Напишите запрос, который
бы выводил из таблицы hr.employees информацию об имени. фамилии и дате приема
на работу сотрудников в соответствии с описанным форматом. Вначале должны выводиться
те, кто был принят работу позже. Результат выполнения запроса должен выглядеть
так, как представлено на рис. Лаб. 3.3-1.
Рис. Лаб. 3.3-1
Решение:
Код соответствующего запроса может выглядеть следующим
образом:
SELECT first_name AS «Имя», last_name As
«Фамилия», TO_CHAR (HIRE_DATE, 'YYYY/MM/DD') As «Дата приема на работу» FROM
hr.employees ORDER BY HIRE_DATE DESC.
Функции преобразования данных к другим типам данных.
TO_CHAR(число) преобразует число в текст. TO_NUMBER(строка) преобразует текст в число.TO_CHAR (123)
FROM DUAL вернет строку 123, SELECT
TO_NUMBER (‘12345’) FROM DUAL вернет число 12345.
4.7 Лабораторная работа. Изменение формата
выводимых чисел
Изменения формата числовых значений в Oracle SQL, функция TO_CHAR для работы с числовыми
значениями.
Задание:
Напишите запрос, который бы выводил информацию о имени,
фамилии и зарплате сотрудников из таблицы hr.employees в формате,
представленном на рис. 3.4-1:
Рис. 3.4-1
При этом данные должны быть отсортированы таким образом,
чтобы первыми выводились строки для сотрудников с наибольшей зарплатой.
Примечание:
Некоторые значения зарплаты на рис. 3.4-1 были изменены,
поэтому они могут не совпадать с вашими значениями.
Решение:
Код соответствующего запроса может быть таким:
SELECT first_name AS «Имя», last_name As
«Фамилия», TO_CHAR (SALARY, 'L999999999.99') As «Оклад» FROM hr.employees ORDER
BY SALARY DESC.
4.8 Функции TO_NUMBER и TO_DATE
Функция преобразования строки в дату TO_DATE (строка,
формат). Возможные значения форматов уже рассмотрены выше, поэтому приведу
несколько примеров использования данной функции. Примеры:
SELECT TO_DATE («01.01.2010’,
‘DD.MM.YYYY’) FROM DUAL вернет дату ‘01.01.2010’;
SELECT TO_DATE («01.JAN.2010’,
‘DD.MON.YYYY’) FROM DUAL вернет дату ‘01.01.2009’;
SELECT TO_DATE («15-01-10’,
‘DD-MM-YY’) FROM DUAL вернет дату ‘15.01.2010’.
Функция преобразования строки в числовое значение TO_NUMBER
(строка, формат). Самые распространенные значения форматов перечислены в
таблице, поэтому рассмотрим применение данной функции на примерах. Примеры:
SELECT TO_NUMBER (‘100’) FROM
DUAL вернет число 100 SELECT
TO_NUMBER (‘0010.01’, «9999D99’) FROM DUAL вернет число 10.01;
SELECT TO_NUMBER ('500,000',
'999G999') FROM DUAL вернет число 500000.
4.9 Элемент RR в формате даты
Элемент формат даты и времени RR похож на элемент формате YY
даты и времени, но это обеспечивает дополнительную гибкость для хранения
значений даты и в других столетий. Элемент формата RR даты и времени позволяет
хранить даты 20-го века в 21-м веке, указав только две последние цифры года.
Если две последние цифры текущего года являются 00 до 49, то
возвращаемый год имеет те же первые две цифры, как в текущем году.
Если две последние цифры текущего года от 50 до 99, то первые
2 цифры возвращенного года являются 1 больше, чем в первые 2 цифр текущего
года.
Если две последние цифры текущего года являются 00 до 49, то
первые 2 цифры возвращенного года являются 1 меньше первых 2 цифр текущего
года.
Если две последние цифры текущего года от 50 до 99, то
возвращаемый год имеет те же первые две цифры, как в текущем году.
4.10 Функция NVL
Функция NVL, как правило, применяется чаще всего. Функция
получает два параметра: NVL (expr1, ехрг2). Если первый параметр expr1 не равен
NULL, то функция возвращает его значение. Если первый параметр NULL, то вместо
него функция возвращает значение второго параметра ехрг2.
Пример: Выберите NVL (supplier_city, н / а ') от поставщиков:
В заявлении SQL выше вернется н / ', если поле supplier_city
содержится нулевое значение. В противном случае он вернет значение
supplier_city.
Другой пример использования функции NVL в Oracle / PLSQL
является:
выберите supplier_id, NVL
(supplier_desc, supplier_name) от поставщиков.
Это SQL заявление будет вернуть supplier_name поле,
если supplier_desc содержится нулевое значение. В противном случае он
вернет supplier_desc.
Последний пример: используя функцию NVL в Oracle / PLSQL
является: выберите NVL (комиссия, 0) от продаж;
Это SQL заявление вернула значение 0, если комиссия
поле содержится нулевое значение. В противном случае, было бы вернуть комиссии
поле.
4.11 Преобразования NVL для различных типов
данных
Для преобразования неопределенного значения в фактическое
используется функция NVL: NVL (выражение1, выражение 2), где:
выражение1- Исходное или вычисленное значение,
которое может быть неопределенным.
выражение2 - Значение, которое подставляется вместо
неопределенного значения.
Примечание: Функцию NVL можно применять для
преобразования любого типа данных, но результат всегда будет того же типа, что
и выражение1.
Преобразование NVL для различных типов:- NVL (числовой
столбец, 9).- NVL (столбец даты, 01-ЯНВ-95').или VARCHAR2 - NVL (символы|столбец,
'Недоступно').
4.12 Лабораторная работа. Применение функции NVL
Функция NVL для работы с неопределенными значениями в Oracle SQL.
Задание:
Напишите запрос, который выводит информацию об имени и
фамилии сотрудников из таблицы hr.employees., а также ставку комиссии (столбец COMMISSION_PCT) для сотрудника. При
этом для тех сотрудников, для которых комиссия не определена, нужно вывести
значение 0. Результат выполнения запроса должен быть таким, как представлено на
рис. 3.5-1.
Рис. 3.5-1 (показаны значения начиная со строки 51)
Решение:
Код соответствующего запроса может быть таким:
SELECT first_name AS «Имя», last_name
As «Фамилия», NVL
(COMMISSION_PCT, 0) As «Ставка комиссии» FROM
hr.employees.
5. Функция DECODE
Функция DECODE выполняет ту же функцию,
что и CASE, единственным отличием является ее синтаксис. Надо отметить, что
исторически функция DECODE в SQL намного старше, но ORACLE, в виду более легкой
читаемости CASE, ввел также данную конструкцию в свой SQL.
DECODE (expression,
search_1, result_1, Search _ 2, result_2, ……search_n, result_n, default), где
expression - это то выражение, которое
сравнивается с search1, если оно совпадает, то выходит результат result1. Иначе
идет вторая проверка и так далее до n-ой проверки. В конце в случаи всех
неудачных проверок в результате выйдет default.
И перепишем верхний пример CASE, но при
этом уже используя DECODE. Как ниже из примера мы видим, что никакой разницы в
исполнение между CASE и DECODE нет.
SELECT job, sal,
CASE job WHEN 'CLERK' THEN sal*0.10 WHEN 'MANAGER' THEN sal*0.12 WHEN 'ANALYST'
THEN sal*0.15 ELSE 0 END BONUS,(job, 'CLERK', sal*0.10, ‘MANAGER', sal*0.12,
'ANALYST', sal*0. 15,0)
BONUS_2 FROM EMP.
5.1 Лабораторная работа. Применение функции DECODE
Функция DECODE для проверки условий в запросах Oracle SQL.
Задание:
Напишите запрос, который возвращает информацию об имени,
фамилии и должности сотрудников (столбец JOB_ID) на основе таблицы hr.employees. При этом:
· если в столбце JOB_ID для сотрудников находится
значение SA_REP, то должно выводиться «Торговый представитель»;
· если в столбце JOB_ID для сотрудников
находится значение SA_MAN, то должно выводиться «Менеджер по продажам»;
· если в этом столбце находится любое другое
значение, то должно выводиться «Другое».
Результат запроса должен быть таким, как представлено на рис.
3.6-1
Рис. 3.6-1
Код соответствующего запроса может быть таким:
SELECT first_name AS «Имя», last_name As «Фамилия», DECODE (JOB_ID, 'SA_REP', 'Торговый
представитель', 'SA_MAN', 'Менеджер по продажам', 'Другое') AS «Должность» FROM hr.employees.
5.2 Вложение функций
Однострочные функции могут быть вложены на
любую глубину, то есть значение, возвращаемое одной функцией, может быть
использовано в качестве аргумента другой функции. Вложенные функции вычисляются
от самого глубокого уровня к верхнему.
Пример: Для каждого служащего
заглавными буквами вывести первую букву его имени с точкой и фамилию в одном
столбце. Столбец назвать employees.
SELECT UPPER
(CONCAT(CONCAT (SUBSTR(first_name, 1,1), ’.’), last_name)) employees FROM
s_emp;
Заключение
Мы рассмотрели функции Oracle SQL и применение их на
практике. Сформировали концептуальные представления об основных принципах
работы с СУБД ORACLE, разработку баз данных в СУБД ORACLE, основные средства и
технологии СУБД ORACLE, возможности процедурного языка Oracle SQL, а также основные приемы
администрирования СУБД ORACLE, проиллюстрировали способы реализации основных
понятий о базе данных в ORACLE.
В данной работе была рассмотрена система управления базами
данных, как составная часть автоматизированного банка данных. Мы изучили
структуру СУБД, её классификацию, особенности языка SQL в базах данных. Было
выяснено, что СУБД ORACLE обладают рядом преимуществ: высокая надежность и
безопасность, возможность работы на платформе любой операционной системы.
Рассмотрев преимущества и недостатки СУБД, можно придти к выводу, что СУБД
ORACLE больше подходит для использования в крупных предприятиях и организациях,
что и подтверждается статистикой. В процессе изучения дисциплины и выполнения
курсовой работы мы усвоили функции Oracle SQL. Научились применять их
на практике.
Список литературы
1. Гринвальд Р., Стаковьяк Р., Стерн Д. «Основы
Oracle», 2009. - 464 с.;
2. Наместников А.М. «Построение баз данных в
среде Oracle. Практический курс», 2008. - 118 с.;
. Джеймс П., Джеральд П. «Введение в Oracle», 2006. - 704 с.;
. Мшира С., Бьюли А. «Секреты Oracle SQL», Символ-Плюс 2006;
. Андон Ф., Резниченко В. «Язык запросов SQL. Учебный курс», Питер, BNV - Киев, 2006;
. Майкл Мак-Локлин, ORACLE Database 11g. Программирование на языке
PL/SQL, Москва: Лори, 2013;
. М. Ричардс и др. «ORACLE7.3.
Энциклопедия пользователя» (К.: «ДиаСофт», 1997, 830 стр.);
. Сингх, Лей, Сафьян и др. «ORACLE7.3.
Руководство разработчика» (К.: «ДиаСофт», 1998, 730 стр.);
. Скотт Урман. «ORACLE 8. Программирование
на языке PL/SQL». (М.: «Лори», 1999, 608 стр.);
. С. Бобровски «ORACLE8: Архитектура» (М.:
«Лори», 1998, 210 стр.);
. С. Смирнов «Работаем с ORACLE». Учебное
пособие. (М.: «Гелиос», 1998, 318 стр.);
12. URL:
http://www.askit.ru/custom/ORACLE10sql/ORACLE10sql_plan.htm.