Управление хранимыми процедурами
Лекция 5. Вспомогательные объекты базы данных
В лекции рассматриваются часто используемые объекты в базах данных: хранимые процедуры и представления. Демонстрируется создание и управление этими объектами при помощи команд SQL.
Цель: сформировать понятие о назначении хранимых процедур и представлений.
Понятие хранимой процедуры
Хранимая процедура (Stored procedure )– это именованный набор операторов Transact-SQL, хранящийся на сервере.
Организация взаимодействия между клиентом и сервером с помощью хранимых процедур предполагает следующее: клиент по известному имени вызывает блок команд, хранящийся на сервере базы данных, сервер выполняет этот блок команд и возвращает клиенту результат. Таким образом, использование хранимых процедур снижает сетевой трафик и сокращает число запросов клиентов, т.к. вместо пересылки по сети нескольких операторов передается лишь имя вызываемой процедуры.
Хранимые процедуры являются самостоятельными объектами базы данных, к которым можно разрешать или запрещать доступ командами GRANT и DENY. Например, выполнение следующей команды запретит выполнение команд хранимой процедуры hello для пользователя mng:
DENY exec ON hello TO mng.
Хранимые процедуры схожи с процедурами других языков программирования и позволяют:
· включать различные операторы и вызывать другие хранимые процедуры;
· принимать входные параметры и возвращать значения в виде выходных параметров.
|
|
|
MS SQL Server поддерживает следующие виды хранимых процедур:
· системные процедуры – хранятся в системной базе данных master, их имена начинаются с символов sp _. Используются для решения специализированных системных задач: администрирования, безопасности и др.;
· пользовательские процедуры – создаются, хранятся и выполняются пользователями в контексте только той базы данных, для которой были созданы;
· временные процедуры – доступны только в активном соединении, после закрытия соединения удаляются автоматически. Имена таких процедур должны начинаться с символа #.
Для работы с хранимыми процедурами предназначены системные хранимые процедуры:
· sp_helptext ИмяПроцедуры – выводит код указанной хранимой процедуры;
· sp_help ИмяПроцедуры – выводит список параметров и их типов данных для указанной процедуры;
· sp_stored_procedures– возвращает список сохраненных процедур текущей базы данных.
Как и большинство объектов MS SQL Server хранимая процедура может быть создана с помощью средств Transact-SQL или с применением графического интерфейса Management Studio.
При создании хранимой процедуры необходимо учитывать следующие особенности:
|
|
|
· процедура может содержать неограниченно количество операторов, кроме операторов создания следующих объектов: процедуры, представления, правила, умолчания;
· создание процедуры может выполнить пользователь роли sysadmin, db _owner или db _ddladmin, а также имеющий право на выполнение команды CREATE PROC;
· количество параметров не должно превышать 2100.
Создание процедуры средствами Transact-SQL
Создание хранимой процедуры заключается в выполнении следующей команды:
CREATE PROCEDURE|PROC <sproc name>
[<parameter name> [<schema>.]<data type> [VARYING]
[= <default value>] [OUT[PUT]] [READONLY]
[, n...]
[WITH
RECOMPILE| ENCRYPTION | [EXECUTE AS { CALLER|SELF|OWNER|’<user name>’}]
[FOR REPLICATION]
AS
<code> | EXTERNAL NAME <assembly name>.<assembly class>.<method>
· Имя процедуры должно удовлетворять правилам именования объектов MS SQL Server;
· parameter name определяет имя параметра (должно начинаться с символа @), который будет использоваться для передачи входных или выходных данных (при указании ключевого слова OUTPUT);
· data type указывает, к какому типу данных должны относиться значения параметра;
· default value – позволяет определить значение по умолчанию, если при вызове процедуры параметр был не указан;
· опция READONLY создает параметр доступный только для чтения, если параметр имеет тип table, то указание READONLY обязательно;
|
|
|
· режим WITH ENCRYPTION запрещает дальнейший просмотр кода создаваемой хранимой процедуры, шифруя его;
· режим RECOMPILE указывает, что сервер не кэширует план исполнения процедуры, и процедура компилируется только во время выполнения.
После ключевогослова AS следуют или команды Transact-SQL, которые и составляют тело процедуры, или прописывается метод из указанной сборки .Net Framework.
Пример создания хранимой процедуры с шифрованием:
CREATE PROCEDURE HumanResources.uspEncryptThis
WITH ENCRYPTION
AS
SELECT BusinessEntityID, JobTitle, NationalIDNumber,
VacationHours, SickLeaveHours
FROM HumanResources.Employee;
Чтобы убедиться, что исходный текст процедуры недоступен, можно выполнить следующий код:
EXEC sp_helptext 'HumanResources.uspEncryptThis';
Результатом выполнения будет сообщение:
The text for object 'HumanResources.uspEncryptThis' is encrypted
(Текст объекта 'HumanResources.uspEncryptThis' зашифрован).
Выполнение процедуры
Хранимая процедура может быть выполнена с помощью оператора EXECUTE:
EXEC[UTE]
[@СтатусВозврата =] ИмяПроцедуры
[ [@параметр=] {Значение | Выражение} [OUTPUT] ] [,...]
При использовании выходного параметра, его необходимо описать с использование ключевого слова OUTPUT при создании процедуры, а также использовать это слово и при указании соответствующего параметра при вызове процедуры. В противном случае процедура не передает выходное значение.
|
|
|
Параметр @СтатусВозврата используется для получения значения кода возврата из хранимой процедуры, выполненного с помощью оператора RETURN Статус. Пользователю рекомендовано использовать положительные значения статуса.
Управление хранимыми процедурами
Изменение
Для изменения существующей процедуры используется оператор ALTER PROC, параметры этой команды аналогичны параметрам команды создания процедуры.
Переименование
Для этого необходимо использовать специальную системную хранимую процедуру:
sp_rename ‘ИмяОбъекта’ ‘НовоеИмяОбъекта’.
Удаление
Для удаления хранимой процедуры используется команда Transact‑SQL:
DROP PROC ИмяПроцедуры.
Представления
Представление (View) является еще одним объектом, составляющим логическую структуру любой базы данных. Представление для конечных пользователей выглядит как таблица, однако при этом не содержит данных, а лишь представляет их. Физически же представляемые данные расположены в различных таблицах базы данных.
Представление реализуется в виде сохраненного запроса, на основе которого и производится выборка из различных таблиц базы данных.
Представления обладают следующими преимуществами:
· обеспечивают конфиденциальность информации, т.к. позволяют отобразить только необходимую информацию, скрывая определенные поля;
· упрощают представление данных, т.к. пользователь работает с представлением как с единой таблицей, которая создана на основе выборки данных из нескольких таблиц;
· управляют правами доступа к данным, например, вместо того чтобы предоставлять права на выполнение запросов к определенным полям таблиц, проще разрешить выполнение запросов через представление.
MS SQL Server предоставляет различные способы создания представлений: с помощью средств Transact-SQL и в утилите администрирования Management Studio.
Дата добавления: 2021-01-21; просмотров: 72; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!