Управление данными, синергия 3 семестр, шпаргалка, краткое содержание учебника. Управление данными 3 сем. Управление данными

• Название: Управление данными
• Анкор: Управление данными, синергия 3 семестр, шпаргалка, краткое содержание учебника
• Дата: 22.10.2022
• Размер: 0.55 Mb.
• Имя файла: Управление данными 3 сем.docx
• Тип: Документы; #748915
• страница: 9 из 11

Хранимые процедуры

Хранимые процедуры представляют собой - группы связанных между собой операторов SQL; набор команд, состоящий из одного или нескольких операторов SQL или функций и сохраняемый в БД в откомпилированном виде.

Типы: системные (для выполнения различных административных действий, префикс sp_), пользовательские (реализуют те или иные действия), временные (локальные - имя нач. с символа # и глобальные - имя нач. с символа ##).

Команда создания и изменения:

<определение_процедуры>::=

{CREATE | ALTER } PROC[EDURE] имя_процедуры

    [;номер]

[{@имя_параметра тип_данных } [VARYING ]

   [=default][OUTPUT] ][,...n]

[WITH { RECOMPILE | ENCRYPTION | RECOMPILE,

   ENCRYPTION }]

[FOR REPLICATION]

AS

   sql_оператор [...n]

CREATE PROCEDURE - разместить процедуру в конкретной БД. При обращении из тела процедуры к объектам той же БД можно исп. укороченные имена (без указания имени БД). Номер в имени – это идентификационный номер хранимой процедуры, однозначно определяющий ее в группе процедур. Для передачи входных и выходных данных исп. параметры, имена которых должны начинаться с символа @ (имена лок. переменных не совпадают с именами параметров). Тип данных CURSOR только как выходной параметр процедуры (с ключевым словом OUTPUT - предназначен для возвращения данных, значение соответствующего параметра задано только с помощью локальной переменной). Ключевое слово VARYING применяется совместно с параметром OUTPUT, имеющим тип CURSOR (выходным параметром будет результирующее множество). Ключевое слово DEFAULT - значение параметра по умолчанию. RECOMPILE - система создает план выполнения хранимой процедуры при каждом ее вызове. Параметр FOR REPLICATION востребован при репликации данных и включении создаваемой хранимой процедуры в качестве статьи в публикацию. Ключевое слово ENCRYPTION предписывает серверу выполнить шифрование кода хранимой процедуры. Ключевое слово AS размещается в начале тела процедуры, т.е. набора команд SQL. Выход из хранимой процедуры можно осуществить посредством команды RETURN.

Команда удаления процедуры:

DROP PROCEDURE {имя_процедуры} [,...n]

Комадна выполнения процедуры:

[[ EXEC [ UTE] имя_процедуры [;номер]

[[@имя_параметра=]{значение | @имя_переменной}

   [OUTPUT ]|[DEFAULT ]][,...n]

Если вызов хранимой процедуры не является единственной командой в пакете, то присутствие команды EXECUTE обязательно, также команда требуется для вызова процедуры из тела другой процедуры или триггера. Использование ключевого слова OUTPUT при вызове процедуры разрешается только для параметров, которые были объявлены при создании процедуры с ключевым словом OUTPUT. DEFAULT - для параметров, для которых определено значение по умолчанию. При вызове процедуры указываются либо имена параметров со значениями, либо только значения без имени параметра.
Триггеры

Одна из разновидностей хранимых процедур, исполнение происходит при выполнении для таблицы какого-либо оператора языка манипулирования данными (DML), используются для проверки целостности данных, а также для отката транзакций.

Триггер - это откомпилированная SQL-процедура, исполнение которой обусловлено наступлением определенных событий внутри реляционной БД.

Каждый триггер привязывается к конкретной таблице. Формат команды CREATE TRIGGER:

<Определение_триггера>::=

  CREATE TRIGGER имя_триггера

  BEFORE | AFTER <триггерное_событие>

  ON <имя_таблицы>

  [REFERENCING

    <список_старых_или_новых_псевдонимов>]

  [FOR EACH { ROW | STATEMENT}]

  [WHEN(условие_триггера)]

  <тело_триггера>

Триггерные события состоят из вставки, удаления и обновления строк в таблице. Время запуска триггера: BEFORE (триггер запускается до выполнения связанных с ним событий), AFTER (после). Выполняемые триггером действия задаются для каждой строки (FOR EACH ROW), охваченной данным событием, или только один раз для каждого события (FOR EACH STATEMENT).

Обозначение <список_старых_или_новых_псевдонимов> относится к таким компонентам, как старая или новая строка (OLD/NEW) либо старая или новая таблица (OLD TABLE/NEW TABLE).

Реализация триггеров в среде MS SQL Server. Оператор создания или изменения:

<Определение_триггера>::=

{CREATE | ALTER} TRIGGER имя_триггера

ON {имя_таблицы | имя_просмотра }

[WITH ENCRYPTION ]

{

{ { FOR | AFTER | INSTEAD OF }

{ [ DELETE] [,] [ INSERT] [,] [ UPDATE] }

[ WITH APPEND ]

[ NOT FOR REPLICATION ]

AS

   sql_оператор[...n]

} |

{ {FOR | AFTER | INSTEAD OF } { [INSERT] [,]

  [UPDATE] }

[ WITH APPEND]

[ NOT FOR REPLICATION]

AS

{ IF UPDATE(имя_столбца)

[ {AND | OR} UPDATE(имя_столбца)] [...n]

|

IF (COLUMNS_UPDATES(){оператор_бит_обработки}

  бит_маска_изменения)

{оператор_бит_сравнения }бит_маска [...n]}

sql_оператор [...n]

}

}

Допускается обращение внутри триггера к другим базам данных, в том числе и расположенным на удаленном сервере. Имя триггера должно быть уникальным в пределах БД. WITH ENCRYPTION - сервер выполняет шифрование кода триггера, чтобы никто, включая администратора, не мог получить к нему доступ и прочитать его.

Типы триггеров:

1. 
   AFTER - выполняется после успешного выполнения вызвавших его команд. Операции: INSERT, UPDATE,DELETE. sp_settriggerorder (хранимая процедура) - порядок выполнения триггеров в случае нескольких для одной таблицы.
   
2. 
   INSTEAD OF - вызывается вместо выполнения команд (для таблицы и просмотра). Триггеры по типу команд, на которые они реагируют: INSERT TRIGGER – запускаются при попытке вставки данных с помощью команды INSERT, UPDATE TRIGGER – запускаются при попытке изменения данных с помощью команды UPDATE, DELETE TRIGGER – запускаются при попытке удаления данных с помощью команды DELETE.
   

Конструкции [ DELETE] [,] [ INSERT] [,] [ UPDATE] и FOR | AFTER | INSTEAD OF } { [INSERT] [,] [UPDATE] определяют, на какую команду будет реагировать триггер (должна быть указана хотя бы одна команда, допускается создание реагирующего на две или на все три команды). Аргумент WITH APPEND позволяет создавать несколько триггеров каждого типа. NOT FOR REPLICATION - запрещается запуск триггера во время выполнения модификации таблиц механизмами репликации. Конструкция AS sql_оператор[...n] определяет набор SQL-операторов и команд, которые будут выполнены при запуске триггера.

Программирование триггера. Для каждого триггера создается свой комплект таблиц inserted и deleted:

1. 
   Команда INSERT – в таблице inserted содержатся все строки, вставляющиеся в таблицу; в таблице deleted не будет ни одной строки; после завершения триггера все строки из inserted переместятся в исходную таблицу.
   
2. 
   Команда DELETE – в таблице deleted будут содержаться все строки, которые удаляются; триггер может проверить каждую строку и определить, разрешено ли ее удаление; в inserted не окажется ни одной строки;
   
3. 
   Команда UPDATE – при ее выполнении в таблице deleted нах. старые значения строк, которые будут удалены при успешном завершении триггера. Новые значения строк содержатся в таблице inserted. Эти строки добавятся в исходную таблицу после успешного выполнения триггера.
   

Для получения информации о количестве строк, которое будет изменено при успешном завершении триггера - функция @@ROWCOUNT. Требования транзакции – должны быть выполнены либо все модификации, либо ни одной.

Внутри триггера допускается применение команд управления транзакциями. При обнаружении нарушения ограничений целостности для прерывания выполнения триггера и отмены всех изменений, которые пытался выполнить пользователь - команда ROLLBACK TRANSACTION. Для получения списка столбцов, измененных при выполнении команд INSERT или UPDATE, вызвавших выполнение триггера, можно использовать функцию COLUMNS_UPDATED(). Она возвращает двоичное число, каждый бит которого, начиная с младшего, соответствует одному столбцу таблицы (в порядке следования столбцов при создании таблицы). Если бит установлен в значение «1», то соответствующий столбец был изменен. Факт изменения столбца определяет и функция UPDATE (имя_столбца).

Команда удаления триггера:

DROP TRIGGER {имя_триггера} [,...n]
Тема 5. Обеспечение целостности данных в БД
Архитектура БД:

1. 
   Логический уровень - средство представления концептуальной модели: таблицы и типы данных, тип данных hierarchyid (позв. создавать отношения между элементами данных в таблице, для того, чтобы задать позицию в иерархии связей между строками таблицы), пространственные типы данных (данные, определяющие географические расположения и формы, преимущественно на Земле: geography, geometry), ключи, индексы, представления, сборки (файлы динамической библиотеки, использующиеся в экземпляре SQL Server для развертывания функций, хранимых процедур, триггеров, определяемых пользователем статистических вычислений и типов), ограничения, правила, значения по умолчанию.
   
2. 
   Физический уровень - внутреннее представление данных в памяти ЭВМ: файлы (физические), единицы обмена между внешней и оперативной памятью.
   

Файлы и файловые группы. Типы файлов: первичные файлы данных (указывают на остальные файлы БД, рекомендованное расширение - MDF), вторичные файлы данных (все файлы, за исключением первичного, реком. расш. - NDF), файлы журналов (сод. все сведения журналов, используемые для восстановления БД, реком. расш. - MDF, NDF, LDF).

Расположение всех файлов БД записывается в первичный файл БД и в специальную служебную структуру - БД master. Имена файлов SQL: logical_file_name - для ссылки на физический файл во всех инструкциях Transact-SQL, os_file_name - имя физического файла, включая путь к каталогу.

Страницы и экстенты. Страница - основная единица хранилища данных и обмена информацией между внешней и оперативной памятью (8 Кб). Экстент - коллекция, состоящая из восьми страниц или 64 Кб (основные единицы организации пространства): однородные (принадлежат одному объекту), смешанные (находятся в общем пользовании у не более восьми объектов).

Страницы файлов данных: первая страница - нулевой номер + каждый файл БД имеет цифровой идентификатор. Первая страница (0) - это страница заголовка файла, вторая (1) - PFS, третья (2) - GAM, далее (3) - SGAM.

Организация таблиц и индексов. Хранятся в виде коллекции страниц 8 Кб, в одной или неск. секциях. Секция - пользовательская единица организации данных. Виды фрагментов файла: данные с типами небольших размеров (данные IN_ROW_DATA), данные с типами больших размеров (LOB_DATA), данные переменной длины (переполненные строки ROW_OVERFLOW_DATA). Куча - последовательность строк таблицы, которые не имет кластеризированного индекса.

Управление работой с эстентами и свободным местом. Типы карт для записи сведений об использовании экстентов: глобальная карта распределения (GAM) - какие экстенты были задействованы (64000 эксентов по одному биту, 0 - свободен, 1 - задействован), общая глобальная карта рапределения (SGAM) - какие экстенты в дан. момент используются в качестве смешанных экстентов и имеют как мин одну неиспользуемую страницу ().

Отслеживание свободного места. Состояние размещения каждой страницы и информация о том, была ли отдельная страница использована, а также кол-во свободного места на каждой странице записывается на страницы PFS (Page Free Space).
Транзакция - последовательность операторов манипулирования данными.

Характеристики транзакций ACID: неделимость, согласованность, изолированность, устойчивость.

Блокировки. При выполнении транзакции сервер накладывает на данные блокировки: блокировка записи, блокировка чтения.

Протокол доступа к данным:

- транзакция, результатом действия которой на строку данных в таблице является ее извлечение, обязана наложить блокировку чтения на эту строку;

- транзакция, предназначенная для модификации строки данных, накладывает на нее блокировку записи;

- если запрашиваемая блокировка на строку отвергается из-за уже имеющейся блокировки, то транзакция переводится в режим ожидания до тех пор, пока блокировка не будет снята;

- блокировка записи сохраняется вплоть до конца выполнения транзакции.

Уровни блокирования:

1. 
   уровень 0 - запрещение «загрязнения» данных (изменять данные может только одна транзакция);
   
2. 
   уровень 1 - запрещение «грязного» чтения (другая транзакция не сможет прочитать данные до завершения первой);
   
3. 
   уровень 2 - запрещение неповторяемого чтения (если транзакция считывает данные, то другая транзакция не сможет их изменить);
   
4. 
   уровень 3 - запрещение фантомов (если транзакция обращается к данным, то другая транзакция не сможет добавить новые или удалить имеющие строки, которые могут быть считаны при выполнении транзакции).