Ответы к экзамену по БД (542444), страница 8
Текст из файла (страница 8)
Т-SQL. Хранимые процедуры и их назначение. Типы хранимых процедур.Операторы создания, запуска, изменения и удаления хранимых процедур.Пример хранимой процедуры.Хранимые процедуры представляют собой набор команд, состоящий из одного или нескольких операторовSQL или функций и сохраняемый в базе данных в откомпилированном виде. Выполнение в базеданных хранимых процедур вместо отдельных операторов SQL дает пользователю следующиепреимущества:• Хранимые процедуры регистрируются на сервере.• Хранимые процедуры могут иметь атрибуты безопасности (например, разрешения) и цепочкивладения, кроме того, к ним можно прикреплять сертификаты.Пользователи могут обладать разрешениями на выполнение хранимых процедур вместо прямыхразрешений для работы с объектами, на которые ссылаются эти процедуры.• Хранимые процедуры позволяют сделать защиту приложений более надежной.Параметризованные хранимые процедуры могут защитить приложения от атак, осуществляемыхпутем инжекции кода SQL.• Хранимые процедуры поддерживают модульное программирование.Процедуру можно создать один раз и потом по мере необходимости вызывать ее в программе любоечисло раз.
Это делает обслуживание приложения более удобным и позволяет унифицировать доступприложений к базе данных.• Хранимые процедуры представляют собой именованный код, дающий возможность отсроченногосвязывания.Это обеспечивает уровень косвенности, упрощающий развитие программного кода.• Хранимые процедуры позволяют уменьшить сетевой трафик.Операцию, занимающую сотни строк программного кода Transact-SQL, можно выполнить в однойинструкции, которая обрабатывает процедуру, а не отправляет этот код по сети.Хранимые процедуры существуют независимо от таблиц или каких-либо других объектов баз данных.
Онивызываются клиентской программой, другой хранимой процедурой или триггером. Разработчик можетуправлять правами доступа к хранимой процедуре, разрешая или запрещая ее выполнение. Изменятькод хранимой процедуры разрешается только ее владельцу или члену фиксированной роли базы данных. Принеобходимости можно передать права владения ею от одного пользователя к другому.Типы хранимых процедурВ SQL Server имеется несколько типов хранимых процедур.• Системные хранимые процедуры предназначены для выполнения различных административныхдействий. Практически все действия по администрированию сервера выполняются с их помощью.Системные хранимые процедуры имеют префикс sp_, хранятся в системной базе данных и могут бытьвызваны в контексте любой другой базы данных.• Пользовательские хранимые процедуры реализуют те или иные действия.
Хранимые процедуры –полноценный объект базы данных. Вследствие этого каждая хранимая процедура располагается вконкретной базе данных, где и выполняется.• Временные хранимые процедуры существуют лишь некоторое время, после чего автоматическиуничтожаются сервером. Они делятся на локальные и глобальные. Локальные временные хранимыепроцедуры могут быть вызваны только из того соединения, в котором созданы. При создании такойпроцедуры ей необходимо дать имя, начинающееся с одного символа #. Как и все временныеобъекты, хранимые процедуры этого типа автоматически удаляются при отключении пользователя,перезапуске или остановке сервера. Глобальные временные хранимые процедуры доступны длялюбых соединений сервера, на котором имеется такая же процедура. Для ее определения достаточнодать ей имя, начинающееся с символов ##.
Удаляются эти процедуры при перезапуске или остановкесервера, а также при закрытии соединения, в контексте которого они были созданы.Создание, изменение и удаление хранимых процедурСоздание хранимой процедуры предполагает решение следующих задач:• определение типа создаваемой хранимой процедуры: временная или пользовательская.
Кроме этого,можно создать свою собственную системную хранимую процедуру, назначив ей имя спрефиксом sp_ и поместив ее в системную базу данных.•планирование прав доступа. При создании хранимой процедуры следует учитывать, что она будетиметь те же права доступа к объектам базы данных, что и создавший ее пользователь;• определение параметров хранимой процедуры. Подобно процедурам, входящим в составбольшинства языков программирования, хранимые процедуры могут обладать входными ивыходными параметрами;• разработка кода хранимой процедуры.
Код процедуры может содержать последовательность любыхкоманд SQL, включая вызов других хранимых процедур.Создание новой и изменение имеющейся хранимой процедуры осуществляется с помощью следующейкоманды:[{CREATE | ALTER } PROCEDURE] имя_процедуры[{@имя_параметра тип_данных }AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }Удаление хранимой процедуры осуществляется командой:DROP PROCEDURE {имя_процедуры} [,...n]Для выполнения хранимой процедуры используется команда:[[ EXEC UTE] имя_процедуры[[@имя_параметра=]{значение | @имя_переменной}Для обращения к процедуре можно использовать команды:EXEC my_proc1 или my_proc1Пример Процедура без параметров.
Создать процедуру для уменьшения цены товара первого сорта на 10%.CREATE PROC my_proc2ASUPDATE Товар SET Цена=Цена*0.9WHERE Сорт=’первый’22. Т-SQL. Курсоры: назначение, описание, применение. ПримерКурсор — ссылка на контекстную область памяти. В некоторых реализациях информационно-логическогоязыка SQL— получаемый при выполнении запроса результирующий набор и связанный с ним указательтекущей записи.Методика использования курсора языка Transact-SQL в хранимой процедуре или триггере такова:1. Воспользуйтесь инструкцией DECLARE CURSOR, чтобы связать курсор языка Transact-SQL с инструкциейSELECT.
Инструкция DECLARE CURSOR определяет также характеристики курсора, например имякурсора и тип курсора (read-only или forward-only).2. Воспользуйтесь инструкцией OPEN для выполнения инструкции SELECT и заполнения курсора.3. Воспользуйтесь инструкцией FETCH INTO для выборки отдельных строк и перемещения данных длякаждого столбца в указанную переменную. После этого другие инструкции языка Transact-SQL могутссылаться на эти переменные для доступа к выбранным значениям данных. Курсоры языка TransactSQL не поддерживают выборку группы строк.4. После завершения работы с курсором примените инструкцию CLOSE.
Закрытие курсора освобождаетнекоторые ресурсы, например результирующий набор курсора и его блокировки на текущей строке,однако структура курсора будет доступна для обработки, если снова выполнить инструкцию OPEN.Поскольку курсор все еще существует на этом этапе, повторно использовать его имя не удастся.5. Инструкция DEALLOCATE полностью освобождает все ресурсы, выделенные курсору, в том числе имякурсора. После освобождения курсора его необходимо перестроить заново с помощью инструкцииDECLARE.Пример:--Объявляем переменные для хранения фамилии и имениDECLARE @LastName varchar(50), @FirstName varchar(50);--Объявляем курсор на фамилию имяDECLARE contact_cursor CURSOR FORSELECT LastName, FirstName FROM Person.PersonWHERE LastName LIKE 'B%'ORDER BY LastName, FirstName;--Открываем курсорOPEN contact_cursor;-- Делаем fetch, по сути шаг, выбор следующего FETCH NEXT.
Запоминаем данные в переменные-- Замечание: Переменные в таком же порядке, как столбцы в SELECTFETCH NEXT FROM contact_cursorINTO @LastName, @FirstName;-- Проверяем @@FETCH_STATUS есть ли еще строки.WHILE @@FETCH_STATUS = 0BEGIN-- Печатаем содержимое переменных.PRINT 'Contact Name: ' + @FirstName + ' ' + @LastName-- Сдвигаем курсор дальше, запоминаем в переменныеFETCH NEXT FROM contact_cursorINTO @LastName, @FirstName;END--Закрываем курсорCLOSE contact_cursor;--Удаляем курсорDEALLOCATE contact23.
Т-SQL. Триггеры и их назначение. Типы триггеров. Операторы создания,изменения, включения/отключения, удаления триггеров. Ограниченияиспользования триггеров. Примеры.Триггер (англ. trigger) — это хранимая процедура особого типа, которую пользователь не вызываетнепосредственно, а исполнение которой обусловлено наступлением определенного события (действием)Основное назначение триггеров состоит в автоматическом использовании их в качестве реакции нанекоторые события, происходящие с таблицами, с которыми связанны триггеры. Это свойство триггеровпозволяет использовать их для реализации сложных форм ограничений целостности данных.
Кроме того,рассматриваемое свойство превращает сервер из пассивного наблюдателя за происходящими изменениямиданных в систему, оперативно реагирующую на такие изменения. Правила, в соответствие с которымиосуществляются активные действия сервера, определяются триггерамиТипы триггеровSQL Server поддерживает три основных типа триггеров: триггеры DML, триггеры DDL и триггеры входа.• Триггеры DDL выполняются, когда на сервере или в базе данных возникает событие языкаопределения данных (DDL). Эти события в основном соответствуют инструкциям языка Transact-SQL,начинающимся ключевыми словами CREATE, ALTER или DROP. Системные хранимые процедуры,выполняющие операции, подобные операциям DDL, также могут запускать триггеры DDL.•ПримерВ следующем примере показано, как триггер DDL может быть использован для предотвращенияизменения или удаления любой таблицы базы данных.CREATE TRIGGER safetyON DATABASEFOR DROP_TABLE, ALTER_TABLEASPRINT 'You must disable Trigger "safety" to drop or alter tables!'ROLLBACK ;Триггеры входа вызывают срабатывание хранимых процедур в ответ на событие LOGON.
Это событиевызывается при установке пользовательского сеанса с экземпляром SQL Server. Можно использоватьтриггеры входа для проверки и управления сеансами сервера, например для отслеживания входов всистему, ограничения входов в SQL Server или ограничения числа сеансов для конкретного имени входа•DML-триггеры выполняются при возникновении событий языка обработки данных (DDL) в базеданных.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
