Запросы SQL. Лабораторная работа 6 Изучить понятие хранимой процедуры. Выполнить хранимые процедуры примеров заданий 19 для базы данных db books
 Скачать 316.18 Kb.
|
|
Лабораторная работа №6 Изучить понятие хранимой процедуры. Выполнить хранимые процедуры примеров заданий 1-9 для базы данных DB_BOOKS. Хранимые процедуры представляют собой набор команд, состоящий из одного или нескольких операторов SQL или функций и сохраняемый в базе данных в откомпилированном виде. Типы хранимых процедур Системные хранимые процедуры предназначены для выполнения раз- личных административных действий. Системные хранимые процедуры имеют префикс sp_, хранятся в системной базе данных и могут быть вызваны в контексте любой другой базы данных. Пользовательские хранимые процедуры реализуют те или иные дейст- вия. Хранимые процедуры – полноценный объект базы данных. Вследствие этого каждая хранимая процедура располагается в конкретной базе дан- ных, где и выполняется. Временные хранимые процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Они делятся на ло- кальные и глобальные. Локальные временные хранимые процедуры могут быть вызваны только из того соединения, в котором созданы. При созда- нии такой процедуры ей необходимо дать имя, начинающееся с одного символа #. Как и все временные объекты, хранимые процедуры этого типа автоматически удаляются при отключении пользователя, перезапуске или остановке сервера. Глобальные временные хранимые процедуры доступны для любых соединений сервера, на котором имеется такая же процедура. Для ее определения достаточно дать ей имя, начинающееся с символов ##. Удаляются эти процедуры при перезапуске или остановке сервера, а также при закрытии соединения, в контексте которого они были созданы. Создание, изменение хранимых процедур Создание хранимой процедуры предполагает решение следующих за- дач: планирование прав доступа. При создании хранимой процедуры сле- дует учитывать, что она будет иметь те же права доступа к объектам базы данных, что и создавший ее пользователь; определение параметров храни- мой процедуры, хранимые процедуры могут обладать входными и выход- ными параметрами; разработка кода хранимой процедуры. Код процедуры может содержать последовательность любых команд SQL, включая вызов других хранимых процедур. Синтаксис оператора создания новой или изменения имеющейся хра- нимой процедуры в обозначениях MS SQL Server: {CREATE | ALTER } PROC[EDURE] имя_процедуры [;номер] [{@имя_параметра тип_данных } [VARYING ] [=default][OUTPUT] ][,...n] [WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION }] [FOR REPLICATION] AS sql_оператор [...n] Рассмотрим параметры данной команды. Используя префиксы sp_, #, ##, создаваемую процедуру можно опреде- лить в качестве системной или временной. Как видно из синтаксиса ко- манды, не допускается указывать имя владельца, которому будет принад- лежать создаваемая процедура, а также имя базы данных, где она должна быть размещена. Таким образом, чтобы разместить создаваемую хранимую процедуру в конкретной базе данных, необходимо выполнить команду CREATE PROCEDURE в контексте этой базы данных. При обращении из тела хранимой процедуры к объектам той же базы данных можно исполь- зовать укороченные имена, т. е. без указания имени базы данных. Когда же требуется обратиться к объектам, расположенным в других базах данных, указание имени базы данных обязательно. Для передачи входных и выходных данных в создаваемой хранимой процедуре имена параметров должны начинаться с символа @. В однойхранимой процедуре можно задать множество параметров, разделенных запятыми. В теле процедуры не должны применяться локальные перемен- ные, чьи имена совпадают с именами параметров этой процедуры. Для определения типа данных параметров хранимой процедуры под- ходят любые типы данных SQL, включая определенные пользователем. Однако тип данных CURSOR может быть использован только как выход- ной параметр хранимой процедуры, т.е. с указанием ключевого слова OUTPUT. Наличие ключевого слова OUTPUT означает, что соответствующий параметр предназначен для возвращения данных из хранимой процедуры. Однако это вовсе не означает, что параметр не подходит для передачи зна- чений в хранимую процедуру. Указание ключевого слова OUTPUT пред- писывает серверу при выходе из хранимой процедуры присвоить текущее значение параметра локальной переменной, которая была указана при вы- зове процедуры в качестве значения параметра. Отметим, что при указании ключевого слова OUTPUT значение соответствующего параметра при вы- зове процедуры может быть задано только с помощью локальной перемен- ной. Не разрешается использование любых выражений или констант, до- пустимое для обычных параметров. Ключевое слово VARYING применяется совместно с параметром OUTPUT, имеющим тип CURSOR. Оно определяет, что выходным пара- метром будет результирующее множество. Ключевое слово DEFAULT представляет собой значение, которое бу- дет принимать соответствующий параметр по умолчанию. Таким образом, при вызове процедуры можно не указывать явно значение соответствую- щего параметра. Так как сервер кэширует план исполнения запроса и компилированный код, при последующем вызове процедуры будут использоваться уже гото- вые значения. Однако в некоторых случаях все же требуется выполнять перекомпиляцию кода процедуры. Указание ключевого слова RECOMPILE предписывает системе создавать план выполнения хранимой процедуры при каждом ее вызове. Параметр FOR REPLICATION востребован при репликации данных и включении создаваемой хранимой процедуры в качестве статьи в публика- цию. Ключевое слово ENCRYPTION предписывает серверу выполнить шифрование кода хранимой процедуры, что может обеспечить защиту от использования авторских алгоритмов, реализующих работу хранимой про- цедуры. Ключевое слово AS размещается в начале собственно тела хранимой процедуры. В теле процедуры могут применяться практически все коман- ды SQL, объявляться транзакции, устанавливаться блокировки и вызываться другие хранимые процедуры. Выход из хранимой процедуры можно осуществить посредством команды RETURN. Удаление хранимой процедуры DROP PROCEDURE {имя_процедуры} [,...n] Выполнение хранимой процедуры [1] Для выполнения хранимой процедуры используется команда: [[ EXEC [ UTE] имя_процедуры [;номер] [[@имя_параметра=]{значение | @имя_переменной} [OUTPUT ]|[DEFAULT ]][,...n] Если вызов хранимой процедуры не является единственной командой в пакете, то присутствие команды EXECUTE обязательно. Более того, эта команда требуется для вызова процедуры из тела другой процедуры или триггера. Использование ключевого слова OUTPUT при вызове процедуры раз- решается только для параметров, которые были объявлены при создании процедуры с ключевым словом OUTPUT. Когда же при вызове процедуры для параметра указывается ключевое слово DEFAULT, то будет использовано значение по умолчанию. Естест- венно, указанное слово DEFAULT разрешается только для тех параметров, для которых определено значение по умолчанию. Из синтаксиса команды EXECUTE видно, что имена параметров могут быть опущены при вызове процедуры. Однако в этом случае пользователь должен указывать значения для параметров в том же порядке, в каком они перечислялись при создании процедуры. Присвоить параметру значение по умолчанию, просто пропустив его при перечислении, нельзя. Если же тре- буется опустить параметры, для которых определено значение по умолча- нию, достаточно явного указания имен параметров при вызове хранимой процедуры. Более того, таким способом можно перечислять параметры и их значения в произвольном порядке. Отметим, что при вызове процедуры указываются либо имена пара- метров со значениями, либо только значения без имени параметра. Их комбинирование не допускается. Использование RETURN в хранимой процедуре Позволяет выйти из процедуры в любой точке по указанному условию, а также позволяет передать результат выполнения процедуры числом, по которому можно судить о качестве и правильности выполнения процедуры. Задание 1. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команды EXEC Count_Books. Проверьте результат.  Рисунок 1 – Таблица «Books»  Рисунок 2 – Создание процедуры 1  Рисунок 3 – Проверка процедуры 1 Задание 2. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команды EXEC @page 400. Проверьте результат.  Рисунок 4 – Создание процедуры 2  Рисунок 5 – Проверка процедуры 2 Задание 3. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команды EXEC Count_Books_Titles 100, 'П%'. Проверьте результат.  Рисунок 6 – Создание процедуры 3  Рисунок 7 – Проверка процедуры 3 Задание 4. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите с помощью набора команд: Declare @q As int EXEC Count_Books_Itogo 100, 'П%', @q output select @q Проверьте результат.  Рисунок 8 – Создание процедуры 4  Рисунок 9 – Проверка процедуры 4 Задание 5. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команд: DECLARE @return_status int EXEC @return_status = checkname 1 SELECT 'Return Status' = @return_status  Рисунок 10 – Создание процедуры 5  Рисунок 11 – Проверка процедуры 5 Задание 6. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команды EXEC update_proc Рисунок 12 – Таблица Purchases до изменения  Рисунок 13 – Создание процедуры 6  Рисунок 14 – Проверка процедуры 6  Рисунок 15 – Таблица Purchases до изменения Задание 7. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команды: EXEC select_author @k='Пушкин '  Рисунок 16 – Создание процедуры 7  Рисунок 17 – Проверка процедуры 7 Задание 8. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команд: EXEC update_proc 4 или EXEC update_proc @p = 4 или EXEC update_proc --будет использовано значение по умолчанию.  Рисунок 18 – Создание процедуры 8  Рисунок 19– Проверка процедуры 8 Задание 9. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команд: DECLARE @c2 INT EXEC count_purchases ’2022.01.01, ’2022.02.01, @c2 OUTPUT SELECT @c2  Рисунок 18 – Создание процедуры 9  Рисунок 19 – Проверка процедуры 9 |