Заглавная страница Избранные статьи Случайная статья Познавательные статьи Новые добавления Обратная связь КАТЕГОРИИ: АрхеологияБиология Генетика География Информатика История Логика Маркетинг Математика Менеджмент Механика Педагогика Религия Социология Технологии Физика Философия Финансы Химия Экология ТОП 10 на сайте Приготовление дезинфицирующих растворов различной концентрацииТехника нижней прямой подачи мяча. Франко-прусская война (причины и последствия) Организация работы процедурного кабинета Смысловое и механическое запоминание, их место и роль в усвоении знаний Коммуникативные барьеры и пути их преодоления Обработка изделий медицинского назначения многократного применения Образцы текста публицистического стиля Четыре типа изменения баланса Задачи с ответами для Всероссийской олимпиады по праву Мы поможем в написании ваших работ! ЗНАЕТЕ ЛИ ВЫ?
Влияние общества на человека
Приготовление дезинфицирующих растворов различной концентрации Практические работы по географии для 6 класса Организация работы процедурного кабинета Изменения в неживой природе осенью Уборка процедурного кабинета Сольфеджио. Все правила по сольфеджио Балочные системы. Определение реакций опор и моментов защемления |
Группировка данных с помощью функций агрегирования
Подведение итогов по столбцам таблиц, а именно правильно - "агрегатные функции". Агрегатные (или их еще называют СТАТИЧЕСКИЕ) функции, позволяют подводить промежуточные итоги, оперируя числовыми или иного вида исчислимыми столбцами. Агрегатная функция принимает в качестве аргумента столбец таблицы целиком и возвращает одно значение. Например, агрегатная функция AVG() принимает столбец и вычисляет среднее значение всех чисел находящихся в столбце. То есть, берет все записи этого столбца, складывает все числа в этом столбце, а потом делит полученное на количество слагаемых Пример 15: использование функций агрегирования select AVG(min_salary), avg(max_salary) from jobs
Рисунок №34 – Группировка данных с функцией агригирования
Таблица 3 – Функции агрегирования SQL Server
Функции SUM и AVG могут использоваться только с числовыми выражениями. Остальные функции можно применять с выражениями любого типа. Использование опции DISTINCT позволяет не учитывать дублирующие друг друга значения из выборки перед применением функций SUM, AVG, COUNT. Использовать функции агрегирования в предложении WHERE нельзя. Тем не менее, можно задействовать предложение WHERE для выбора. А вот, так это выглядит в представлении синтаксиса команд:
Аргументом агрегирования может служить не только имя столбца, но и любое математическое выражение. Например, как в следующем запросе: Каков средний процент выполнения плана в вашей компании? Пример 16: select AVG(max_salary/min_salary) from jobs Рисунок №35 – Результат выборки Или же Пример 17: select AVG(max_salary/min_salary) sr_otn from jobs Рисунок 17 Вычисление суммы столбцов (пример 18): select sum(max_salary) max_sum, sum(min_salary) min_sum from jobs Рисунок №36 – Результат вычисления суммы столбцов Это снова числа. Так же можно работать со строками. Но учтите, что разные виды кодировок, применяемые в серверах SQL, могут давать различные результаты. Например, прописные буквы в моей системе идут первыми, потом строчные, а например в системе EBCDIC фирмы IBM все наоборот. А как, например, просто подсчитать количество записей? Для этого есть функция COUNT(). Эта функция всегда возвращает целое число, не зависимо от типа передаваемых данных. Пример 19: select COUNT(max_salary) from jobs Рисунок №37 – Результат выборки
Многотабличные запросы Объединять таблицы можно с помощью предложения WHERE. Общий синтаксис объединения следующий: SELECT < Список столбцов>FROM <Имя таблицы 1>, <Имя таблицы 2> [, <Имя таблицы 3>].....WHERE [<Имя таблицы 1>.]<Имя столбца> <Оператор объединения> [<Имя таблицы 2>.]<Имя столбца>Предложение FROM должно включать, по крайней мере, две таблицы, а столбцы, указанные в предложении WHERE, должны быть совместимы. Когда столбцы объединения имеют одинаковые имена, необходимо указать перед ними названия таблиц с точкой, для того чтобы их можно было различить.Обычно при связывании двух или более таблиц используют оператор «=», но можно задействовать и другие операторы. В таблице 4 приводятся операторы, которые можно использовать для связи таблиц.
Таблица 4 – Операторы связывания таблиц
В некоторых случаях необходимо объединить те или иные записи таблицы с другими записями той же таблицы. Такое объединение проще представить как объединение двух копий таблиц. По сути, запрос такого рода ничем не отличается от других многотабличных запросов, за исключением того, что таблицы абсолютно идентичны. Для того чтобы различать поля в копиях (на самом деле никаких копий не создается, но так понятнее), можно воспользоваться временными именами, которые присваивают в предложении FROM. Их чаще называют псевдонимами, но, если вам встретится «переменная диапазона» или «переменная корреляции», не пугайтесь – вы имеете дело с одним и тем же объектом. В предложении FROM вы отделяете пробелом имя таблицы от псевдонима. Итак, пример 20 иллюстрирует простой запрос из разных таблиц: select region_name, Country_ID, Country_name from regions,countries
Рисунок №38 - Результаты выборки
Пример 21 - Многотабличные запрос с одинаковыми столбцами select regions.region_id, region_name, country_name, country_id from regions, countries Рисунок №39 – Результаты выборки примера 21
Пример 22 - исключает совпадения, используя индивидуальное имя таблицы select countries.*, region_name from countries, regions
Рисунок №40 – Результаты выборки примера №22
Оно же с условием (пример 23): select countries.region_id, country_name, region_name from countries, regions where countries.region_id =3 Триггеры Триггером называется последовательность команд, выполняемая автоматически каждый раз при возникновении определенного события. Стандарт SQL99 предусматривает наличие триггеров, но никак не описывает их синтаксис, поэтому каждый производитель СУБД реализует триггеры по-своему. В частности, в Oracle триггеры пишутся на языке PL/SQL, что позволяет выполнять внутри триггеров достаточно сложные операции. Создание триггеров Триггер срабатывает при возникновении некоторого события. В Oracle таким событием является изменение (insert, update или delete) данных в таблице. Oracle позволяет также определить триггеры, срабатывающие при изменении схемы базы данных (create, alter или drop), при запуске или останове сервера, при начале или завершении сессии. Oracle позволяет создавать два типа триггеров: триггер на уровне команды (statement trigger) и триггер на уровне строки (row trigger). Первые выполняются всякий раз при выполнении команды, независимо от того, сколько строк затрагивает команда, пусть даже и ни одной. Вторые выполняются для каждой затронутой изменением строки. Чтобы указать, что триггер должен срабатывать для каждой строки, используется фраза for each row. В этой же фразе после ключевого слова when можно указать дополнительные условия срабатывания триггера.По времени срабатывания триггеры делятся на две категории: до выполнения команды (before), и после (after). При выдаче команды на изменение данных Oracle выполняет следующую последовательность действий: -Выполняются триггеры before на уровне команды (before statement);
-Для каждой строки, на которую действует команда -Выполняются триггеры before на уровне строки (before each row); -Выполняется изменение данных. При этом строка блокируется, и блокировка не отпускается до конца транзакции; -Выполняется проверка правил целостности для измененной строки; -Выполняются триггеры after на уровне строки (after each row). -Завершается проверка правил целостности; -Выполняются триггеры after на уровне команды (after statement). Определение этой модели рекурсивно, так как команды внутри триггеров могут изменять данные в других таблицах, вызывая тем самым выполнение триггеров для них. Если для таблицы создано несколько одинаковых (срабатывающих на одно и то же событие) триггеров, то порядок их выполнения непредсказуем. Триггеры before и after можно создавать только для физических таблиц. Кроме того, в Oracle 8 появилась возможность создавать триггеры для представлений. С их помощью можно организовать изменение представлений в случаях, когда это не поддерживается автома тически. Такие триггеры составляют третью категорию по времени срабатывания — «вместо» (instead of), и всегда работают на уровне строки. При выполнении команды, изменяющей данные представления, на самом деле вызывается триггер.
В основном триггеры используются для: 1. Реализации сложных ограничений целостности данных, которые невозможно осуществить через описательные ограничения, устанавливаемые при создании таблиц. 2. Организации всевозможных видов аудита. Например, слежения за изменениями в какой-либо важной таблице БД. 3. Автоматического оповещения других модулей о том, что делать в случае изменения информации содержащейся в таблице БД. 4. Для реализации так называемых "бизнес правил". 5. Для организации каскадных воздействий на таблицы БД.
Синтаксис команды для создания триггера, следующий: CREATE [OR REPLACE] TRIGGER имя_триггера BEFORE | AFTER активизирующее_событие ON ссылка_на_таблицу FOR EACH ROW [WHEN условие_срабатывания] тело_триггера Где: - имя_триггера - собственно имя вашего триггера. - активизирующее_событие - указывает момент активации триггера BEFORE до срабатывания оператора DML, AFTER после срабатывания оператора DML. - ссылка_на_таблицу - собственно таблица, для которой создан триггер. - FOR EACH ROW - если указано активируется от воздействия на строку если нет, то после любого оператора DML. - условие_срабатывания - если TRUE триггер срабатывает, если FALSE нет.
- тело_триггера - собственно тело триггера.
Для полноты картины определимся с таким фактом, что триггеры имеют собственное пространство имен (namespace). Что это означает, само понятие namespace - применимо вообще во многих языках программирования. В пределах одного пространства имен не может быть двух функций или процедур с одинаковым именем! Так вот, так как пространство имен у триггеров свое, то может иметь место ситуация, когда какой-либо триггер имеет тоже имя, что и процедура или функция в пределах одной схемы (не путать с пространством имен это разные вещи!). Но двух триггеров с одинаковым именем не бывает. Так же триггер, может иметь имя совпадающее с именем таблицы, для которой он создан. Но лучше этого не делать, а дать триггеру имя указывающее на то, что он производит. Вообще, по моему мнению, такое понятие как имя для триггера по большому счету архаично. Ведь триггер нельзя "позвать" из процедуры или функции - это запрещено. Можно было сделать что-то вроде универсальной цифровой маркировки. Хотя имя дает триггеру осмысленность, что улучшает чтение кода БД. Теперь давайте посмотрим на типы триггеров и моменты срабатывания и что все это значит (таблица 5). Итак:
Таблица 5 - Типы триггеров и моменты срабатывания
Приведем теперь пример 24 - создание триггера. Создадим вначале тестовую табличку: CREATE TABLE test( USAL VARCHAR2(50), TISP DATE)
Рисунок №41- Тестовая таблица
Затем создадим уже сам триггер: CREATE OR REPLACE TRIGGER testTrg AFTER INSERT OR DELETE OR UPDATE ON regions DECLARE BEGIN INSERT INTO test(USAL, TISP) VALUES(USER, SYSDATE); END testTrg;
Рисунок №42 – Выполнение команды создания триггера
Теперь вставим какое-либо значении в нашу таблицу: INSERT INTO REGIONS (region_id,region_name) values(7,'test7')
Рисунок №43 – Результат работы
Проверяем работу триггера, просмотрев нашу новую табличку: SELECT USAL, TO_CHAR(TISP,'DD/MM/YYYY, HH24:MI:SS') TISM FROM test
Рисунок №44 – Просмотр результата
Мы получили, что триггер работает. Теперь посмотрим как же он выглядит и как определен: SELECT TRIGGER_NAME, TRIGGER_TYPE, TABLE_NAME, TRIGGERING_EVENT FROM USER_TRIGGERS WHERE TRIGGER_NAME = 'TESTTRG'
Рисунок №45 – Просмотр определение триггера
Если необходимо его временно отключить то мы пишем:
ALTER TRIGGER TESTTRG DISABLE Рисунок 47 – Отключение триггера
Триггер отключился. Если обновить данные, то ничего не произойдет Ну а удаляется триггер операцией: DROP TRIGGER TESTTRG
Рисунок 48 – Удаление триггера
3. Задание для самостоятельной работы
1. Согласно предложенному вам варианту осуществите выборку данных: - по условию из одной таблицы; - из нескольких таблиц с сортировкой по условию; - всех столбцов из таблицы; - с использованием предикатов like, between; - с использованием выражения order by; - с использованием функций агрегирования.
2. Создайте любой из триггеров: вставки, модификации или удаления. 3. Произведите объединение выборок. 4 Контрольные вопросы 1. Общая структура команды SELECT. 2. Где и для каких целей применяется выборка? 3. Как просмотреть результаты выборки? 4. Приведите примеры условий формирования условий отбора? 5. Перечислить операторы, которые могут применяться в предложении WHERE? 6. Дайте определения функции агрегирования. 7. Перечислить функции агрегирования, опишите их назначение? 8. Какое предложение применяется для сортировки данных выборки, условия его применения? 9. Каким образом осуществляется выборка из нескольких страниц? 10. Приведите пример создания таблицы на основе выборки. 11. Дайте определения триггеру. 12. Приведите примеры возможных триггеров? 13. Описать синтаксис набора команд создания триггеров? 14. Что необходимо учитывать при использовании триггеров?
Лабораторная работа №5
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Последнее изменение этой страницы: 2017-02-09; просмотров: 499; Нарушение авторского права страницы; Мы поможем в написании вашей работы! infopedia.su Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав. Обратная связь - 3.89.72.221 (0.081 с.) |