Разделы презентаций
- Разное
- Английский язык
- Астрономия
- Алгебра
- Биология
- География
- Геометрия
- Детские презентации
- Информатика
- История
- Литература
- Математика
- Медицина
- Менеджмент
- Музыка
- МХК
- Немецкий язык
- ОБЖ
- Обществознание
- Окружающий мир
- Педагогика
- Русский язык
- Технология
- Физика
- Философия
- Химия
- Шаблоны, картинки для презентаций
- Экология
- Экономика
- Юриспруденция
SQL
Содержание
- 1. SQL
- 2. Хранимые процедурыХранимая процедура – это последовательность компилированных
- 3. Хранимые процедуры сложнее, чем представления. Хранимые процедуры
- 4. Первоначальное выполнение хранимой процедуры:Лексический анализ разбивает ХП
- 5. Дерево запроса считывается и окончательно оптимизируется.При последующих
- 6. Преимущества использования ХПВыполняются быстрее, чем последовательность отдельных
- 7. Преимущества использования ХП (продолжение): Могут вызывать
- 8. Создание и запуск ХП.CREATE PROC имя-процедуры[параметры]ASЗапрос SQLЗапускEXEC имя-процедуры[параметры]
- 9. Пример 1 – процедура без параметровВывести, когда
- 10. Пример 2 – процедура с входным параметромТоже,
- 11. Пример 3 – процедура с входными параметрамиДля
- 12. Пример 4 – процедура с входными параметрами
- 13. Пример 5 – процедура с входными и
- 14. Использование хранимых процедур для всех повторяющихся действий в базе данных является хорошим стилем программирования.
- 15. Триггеры- это особые хранимые процедуры, автоматически выполняемые
- 16. ТриггерыВсе производимые Т модификации рассматриваются как одна
- 17. Триггеры чаще всего используются для:Проверки корректности введенных
- 18. Триггеры чаще всего используются для:Для выполнения дополнительной
- 19. Создание триггераCREATE TRIGGER [владелец.]имя_триггераON [владелец.]имя таблицы или
- 20. ТриггерыПо умолчанию триггер выполняется после изменения данных,
- 21. Пример 1Создать триггер, который будет при каждом
- 22. ЗапускINSERT (Табельный Номер, ФИО, Должность)VALUES (’33п28’,’Иванов ВВ’,’доцент’)
- 23. Пример 2Триггер срабатывает при удалении экземпляра некоторой
- 24. ЗаданиеСоздать триггер, который срабатывает при удалении из
- 25. Индексыэто набор ссылок, упорядоченных по определенному столбцу
- 26. ФайлыИндексированный файл – это основной файл, содержащий
- 27. Индексы (создание)CREATE INDEX имя_индекса ON имя_таблицы (столбцы для индексации)
- 28. Индексы повышают производительность операций выборки, но ухудшают
- 29. Индексы. Что следует учитывать:Выбирайте для индексации данные,
- 30. Транзакция- это последовательность операций, производимых над БД,
- 31. ТранзакцияИнициализация Т м.б. вызвана как пользователем так и прикладной программой. Т особенно важны для многопользовательских СУБД.
- 32. Проектирование транзакции заключается в определении:данных, используемых Т,функциональных
- 33. Свойства транзакцийАтомарность – транзакция должна быть выполнена
- 34. Свойства транзакцийИзолированность – означает, что конкурирующие за
- 35. ФИКСАЦИЯ ИЛИ ОТКАТ?Если все операторы выполнены успешно
- 36. Модель транзакцийСтандартом ANSI/ISO SQL определена модель транзакций и функции операторовCOMMIT - фиксацияROLLBACK - откат
- 37. Транзакция завершается одним из 4-х возможных вариантов:
- 38. Транзакция завершается одним из 4-х возможных вариантов:Успешное
- 39. ПРИМЕРВ таблице Ведомость изменить значение поля Оценка
- 40. Журнал транзакцийЖТ – это особая часть БД,
- 41. Журнал транзакций (продолжение)Используется стратегия «упреждающей» записи (протокол
- 42. Проблемы параллельного выполнения транзакций. Пропавшие изменения,Проблемы промежуточных данных,Проблемы несогласованных данных,Проблемы строк-фантомов.
- 43. Как бороться с проблемами? Сериализация транзакций.Выработать
- 44. Сериализация транзакций.Самый популярный механизм реализации сериализации Т – механизм блокировок.
- 45. БлокировкиЧтобы запретить нескольким пользователям одновременно изменять данные
- 46. Режимы блокировок:РазделяемыйОбновленияМонопольныйНамерения СхемыМассового обновления
- 47. Разделяемая блокировкаПрименяется только для чтения и позволяет
- 48. Блокировка обновленияПрименяется, когда допустимо обновление ресурса. Только
- 49. Моно Блокировка намерения польная блокировкаПрименяется для модификации
- 50. Блокировка намеренияприменяется для иерархического упорядочения блокировок.
- 51. Блокировка массового обновленияПрименяется при копировании больших объемов данных в таблицу с указанием TABLOCK.
- 52. Скачать презентанцию
Хранимые процедурыХранимая процедура – это последовательность компилированных операторов Transact-SQL, выполняемых в виде пакета и хранящихся в системной базе данных SQL Server.
Слайды и текст этой презентации
Слайд 2Хранимые процедуры
Хранимая процедура – это последовательность компилированных операторов Transact-SQL, выполняемых
Слайд 3Хранимые процедуры сложнее, чем представления.
Хранимые процедуры содержат как входные,
так и выходные параметры.
Хранимые процедуры могут содержать инструкции, которые
управляют потоком кода, например IF и WHILE.
Слайд 4Первоначальное выполнение хранимой процедуры:
Лексический анализ разбивает ХП на отдельные компоненты.
Разрешение
ссылок – компоненты,ссылающиеся на объекты БД, сопоставляются с этими объектами.
В
таблице syscomments сохраняется исходный текст ХП, а в sysobjects – ее название.Создается предварительный план выполнения запроса (нормализованный план или дерево запроса) и сохраняется в sysprocedures.
Слайд 5
Дерево запроса считывается и окончательно оптимизируется.
При последующих вызовах ХП выполняется
только шаг 5.
План выполнения ХП после первого выполнения хранится в
быстродействующем процедурном кэше.ХП могут принимать аргументы при запуске и возвращать значения.
Слайд 6Преимущества использования ХП
Выполняются быстрее, чем последовательность отдельных операторов;
Необходимые для выполнения
операторы уже содержатся в БД;
Они находятся в исполняемом формате;
Поддерживается модульное
программирование;
Слайд 7
Преимущества использования ХП (продолжение):
Могут вызывать другие ХП и функции;
Могут быть
вызваны из прикладных программ других типов;
Уменьшается нагрузка на сеть, т.к.
для запуска достаточно передать иногда только имя ХП.
Слайд 8Создание и запуск ХП.
CREATE PROC имя-процедуры
[параметры]
AS
Запрос SQL
Запуск
EXEC имя-процедуры
[параметры]
![Создание и запуск ХП.CREATE PROC имя-процедуры[параметры]ASЗапрос SQLЗапускEXEC имя-процедуры[параметры]](/img/thumbs/9efbcbb6fe1bb908bd06cd3becdcfe9f-800x.jpg "SQL Создание и запуск ХП.CREATE PROC имя-процедуры[параметры]ASЗапрос SQLЗапускEXEC имя-процедуры[параметры]")
Слайд 9Пример 1 – процедура без параметров
Вывести, когда и какой предмет
сдавал студент Иванов ВВ.
CREATE PROC my_proc1
AS
SELECT Предмет.Название,Ведомость.Дата
FROM
Студенты INNER JOIN (Предметы INNER JOIN Ведомость ON Предметы.КодПредмета=Ведомость.КодПредмета) ON Студент.НомерЗачетки=Ведомость.НомерЗачеткиWHERE Студент.ФИО=‘Иванов ВВ’;
Запуск:
EXEC my_proc1
Слайд 10Пример 2 – процедура с входным параметром
Тоже, только для любого
студента
CREATE PROC my_proc2
@F varchar(20)
AS
SELECT Предмет.Название,Ведомость.Дата
FROM Студенты INNER JOIN
(Предметы INNER JOIN Ведомость ON Предметы.КодПредмета=Ведомость.КодПредмета) ON Студент.НомерЗачетки=Ведомость.НомерЗачеткиWHERE Студент.ФИО= @F;
Запуск:
EXEC my_proc2 ‘Сидоров ВВ’ или
EXEC my_proc2 @F= ‘Сидоров ВВ’
Слайд 11Пример 3 – процедура с входными параметрами
Для некоторого студента снизить
оплату на 5%.
CREATE PROC my_proc3
@N varchar(5), @P float
AS
UPDATE Оплата
SET Сумма= Сумма*(1- @P )
WHERE Оплата.НомерЗачетки=@N;
Запуск:
EXEC my_proc3 ‘ИС230’, 0.05 или
EXEC my_proc3 @N= ‘ИС230’, @P= 0.05
Слайд 12Пример 4 – процедура с входными параметрами и значениями по
умолчанию
CREATE PROC my_proc4
@N varchar(5)= ‘ИС230’,
@P float=0.05
AS
UPDATE Оплата
SET
Сумма= Сумма*(1- @P )WHERE Оплата.НомерЗачетки=@N;
Запуск:
EXEC my_proc4 ‘ИС450’, 0.1 или
EXEC my_proc4 @N=‘ИС450’, @P = 0.1
EXEC my_proc4 @P = 0.1
EXEC my_proc4
Слайд 13Пример 5 – процедура с входными и выходными параметрами
Вывести общую
сумму, уплаченную студентами за конкретный месяц
CREATE PROC my_proc5
@m int
@S float
outputAS
SELECT @S=SUM(Оплата.Сумма)
FROM Оплата
GROUP BY Month(Оплата.Дата)
HAVING Month(Оплата.Дата)= @m;
Запуск:
DECLARE @SS float
EXEC my_proc5 10, @SS output
SELECT @SS
Слайд 14Использование хранимых процедур для всех повторяющихся действий в базе данных
является хорошим стилем программирования.
Слайд 15Триггеры
- это особые хранимые процедуры, автоматически выполняемые при обращении к
БД с целью изменения данных.
Т могут быть связаны с
выполнением операций INSERT, UPDATE, DELETE или их комбинаций.Т определяются для конкретных таблиц.
таблица может иметь любое количество любых триггеров.
Слайд 16Триггеры
Все производимые Т модификации рассматриваются как одна транзакция (срабатывает как
транзакция).
Создает Т только владелец БД.
При выполнении команд модификации данных сервер
создает две таблицы:inserted и deleted, где хранятся списки соответствующих строк. Они связаны только с Т их создавшим, никакой другой Т не имеет к ним доступа.
Слайд 17Триггеры чаще всего используются для:
Проверки корректности введенных данных и выполнении
сложных ограничений целостности данных, которые трудно, если вообще возможно, поддерживать
с помощью ограничений целостности, установленных для таблиц;Для выполнения действий по отношению к другим таблицам, основанных на изменениях которые были сделаны в какой-то таблице;
Слайд 18Триггеры чаще всего используются для:
Для выполнения дополнительной проверки и отмены
введения данных (например, чтобы удостоверится, что разрешенная для клиента сумма
кредита не превышена, в противном случае операция блокируется);Для подсчета значений вычисляемых полей или обновления меток даты/времени.
Слайд 19Создание триггера
CREATE TRIGGER [владелец.]имя_триггера
ON [владелец.]имя таблицы или представления
FOR {AFTER INSTEAD
OF} {INSERT UPDATE DELETE} [WITH ENCRYPTION]
AS оператор_SQL
![Создание триггераCREATE TRIGGER [владелец.]имя_триггераON [владелец.]имя таблицы или представленияFOR {AFTER INSTEAD OF} {INSERT UPDATE DELETE} [WITH ENCRYPTION]AS оператор_SQL](/img/thumbs/551743a306fb315f832568318596c6b5-800x.jpg "SQL Создание триггераCREATE TRIGGER [владелец.]имя_триггераON [владелец.]имя таблицы или представленияFOR {AFTER INSTEAD OF}")
Слайд 20Триггеры
По умолчанию триггер выполняется после изменения данных, но если указать
параметр INSTEAD OF, то такой триггер будет выполняться вместо изменения
данных.В Т можно использовать функцию @@ROWCOUNT,
которая возвращает количество строк обработанных последней командой.
Слайд 21Пример 1
Создать триггер, который будет при каждом добавлении или изменении
данных таблицы «Преподаватели» возвращать сообщение о количестве измененных записей.
CREATE
TRIGGER tr_ПРЕПON Преподаватели
FOR INSERT, UPDATE
AS raiserror (‘Произведено изменений таблицы’,0,1 @@rowcount)
RETURN
Слайд 23Пример 2
Триггер срабатывает при удалении экземпляра некоторой книги, например, в
случае утери. Он проверяет остался ли хотя бы один экземпляр
этой книги, если нет, то книга удаляется из каталога.CREATE TRIGGER DEL_EXEMP ON Экземпляры
FOR DELETE
AS
DECLARE @Ntek int (кол-во оставшихся экземпляров книги)
DECLARE @DEL_EX varchar(12) (шифр удаленного экземпляра)
BEGIN
SELECT @DEL_EX=ISBN FROM deleted
EXEC @Ntek = COUNT_EX @DEL_EX (вызов ХП, определяющей кол-во экземпляров книги с зад. шифром)
IF @Ntek =0 DELETE FROM Книги WHERE Книги.ISBN= @DEL_EX END
GO
Слайд 24Задание
Создать триггер, который срабатывает при удалении из учебного плана некоторой
дисциплины, он проверяет осталась ли хотя бы еще одна дисциплина
у преподавателя, который ее читал. Если нет, то преподавателя увольняют, т.е. удаляют соответствующую запись в таблице преподаватель.
Слайд 25Индексы
это набор ссылок, упорядоченных по определенному столбцу таблицы,
это наборы
уникальных значений для некоторой таблицы с соответствующими ссылками на данные,
это
средство логической сортировки данных для повышения скорости поиска и выполнения в последующем сортировки.Индекс также ускоряет поиск, как предметный указатель в книге.
Слайд 26Файлы
Индексированный файл – это основной файл, содержащий данные отношения, для
которого создан индексный файл.
Индексный файл – это файл, в котором
каждая запись состоит из двух значений: данных поля по которому индексировали и указателя, который связывает с соответствующим кортежем индексированного файла
Слайд 28Индексы повышают производительность операций выборки, но ухудшают производительность операций-действий;
Для хранения
данных индекса требуется много места на диске;
Не все данные подходят
для индексации. Данные, неявляющиеся по своей сути уникальными (ГРУППА а таблице СТУДЕНТ), не дадут большого эффекта;Индексы. Что следует учитывать:
Слайд 29Индексы. Что следует учитывать:
Выбирайте для индексации данные, которые часто используются
для фильтрации и сортировки данных;
В качестве индекса можно использовать несколько
столбцов.
Слайд 30Транзакция
- это последовательность операций, производимых над БД, рассматриваемая СУБД как
единое целое, и переводящая ее из одного непротиворечивого (согласованного) состояния
в другое непротиворечивое (согласованное) состояние.Каждая Т начинается при целостном состоянии БД и сохраняет это состояние после своего завершения.
Слайд 31Транзакция
Инициализация Т м.б. вызвана как пользователем так и прикладной программой.
Т особенно важны для многопользовательских СУБД.
Слайд 32Проектирование транзакции заключается в определении:
данных, используемых Т,
функциональных характеристик Т,
выходных
данных, формируемых Т,
степени важности и интенсивности использования.
Слайд 33Свойства транзакций
Атомарность – транзакция должна быть выполнена целиком или не
выполнена вовсе.
Согласованность – гарантирует, что по мере выполнения транзакций данные
переходят из одного согласованного состояния в другое, т.е. транзакция не разрушает взаимной согласованности данных.
Слайд 34Свойства транзакций
Изолированность – означает, что конкурирующие за доступ к БД
транзакции физически обрабатываются последовательно, изолированно друг от друга, но для
пользователей иллюзия параллельности.Долговечность – если транзакция завершена успешно, то те изменения в данных, которые были ею произведены, не могут быть потеряны ни при каких обстоятельствах.
Слайд 35ФИКСАЦИЯ ИЛИ ОТКАТ?
Если все операторы выполнены успешно и не произошло
никаких сбоев программного или аппаратного обеспечения, то ФИКСАЦИЯ, т.е. все
результаты транзакции становятся постоянными.Если в процессе выполнения транзакции случилось нечто, что ее нормальное завершение становится невозможным, то БД возвращается в свое исходное состояние, т.е. ОТКАТ.
Слайд 36Модель транзакций
Стандартом ANSI/ISO SQL определена модель транзакций и функции операторов
COMMIT
- фиксация
ROLLBACK - откат
Слайд 37Транзакция завершается одним из 4-х возможных вариантов:
Оператор COMMIT означает
успешное завершение транзакции; его использование делает постоянными изменения, внесенные в
БД в текущей транзакции.Оператор ROLLBACK прерывает транзакцию, отменяя изменения, сделанные в БД в рамках этой транзакции; новая транзакция начинается непосредственно после использования ROLLBACK.
Слайд 38Транзакция завершается одним из 4-х возможных вариантов:
Успешное завершение программы, в
которой была инициирована текущая транзакция, означает успешное ее завершение (как
будто был использован оператор COMMIT).Ошибочное завершение программы прерывает транзакцию (как будто был использован оператор ROLLBACK).
Слайд 39ПРИМЕР
В таблице Ведомость изменить значение поля Оценка на 0, если
записано значение NULL.
BEGIN TRAN updete_оценка
UPDETE Ведомость SET оценка=0 WHERE оценка
IS NULL COMMIT TRAN updete_оценка
GO
Слайд 40Журнал транзакций
ЖТ – это особая часть БД, недоступная пользователям СУБД
и поддерживаемая с особой тщательностью, в которую поступают записи обо
всех изменениях основной части БД.ЖТ сохраняет промежуточные состояния БД необходимые для отката транзакции.
Предназначен для поддержки надежности хранения данных во внешней памяти.
Слайд 41Журнал транзакций (продолжение)
Используется стратегия «упреждающей» записи (протокол WAL) – запись
об изменении любого объекта БД должна попасть во внешнюю память
журнала раньше, чем измененный объект попадет во внешнюю память основной части БД.
Слайд 42Проблемы параллельного выполнения транзакций.
Пропавшие изменения,
Проблемы промежуточных данных,
Проблемы несогласованных данных,
Проблемы
строк-фантомов.
Слайд 43Как бороться с проблемами?
Сериализация транзакций.
Выработать процедуру согласованного выполнения Т:
В
ходе выполнения Т пользователь видит только согласованные данные,
При параллельном выполнении
2-х Т СУБД гарантирует принцип независимого выполнения Т
Слайд 44Сериализация транзакций.
Самый популярный механизм реализации сериализации Т – механизм блокировок.
Слайд 45Блокировки
Чтобы запретить нескольким пользователям одновременно изменять данные в базе и
считывать «грязные» данные используется блокировка.
Блокирование обеспечивает логическую целостность транзакций и
данных.Уровни гранулярности - блокируемые ресурсы включают запись данных, страницу данных, экстент, таблицу или всю БД. Самый низкий уровень гранулярности – блокировка записи.
Слайд 47Разделяемая блокировка
Применяется только для чтения и позволяет параллельным транзакциям одновременно
считывать данные из одного и того же источника.
Слайд 48Блокировка обновления
Применяется, когда допустимо обновление ресурса. Только одна транзакция единовременно
может наложить ее. Если транзакция действительно производит изменение, то эта
блокировка преобразуется в монопольную, иначе – в разделяемую.
Слайд 49Моно Блокировка намерения польная блокировка
Применяется для модификации данных. При этом
никакая другая транзакция не может читать или изменять этот ресурс.
Слайд 50Блокировка намерения
применяется для иерархического упорядочения блокировок.
Блокировка схемы
применяется
при выполнении операции, затрагивающей схему таблицы, например, при добавлении поля
в таблицу или компиляции запросов.
Слайд 51Блокировка массового обновления
Применяется при копировании больших объемов данных в таблицу
с указанием TABLOCK.
