Хранимые процедуры. Методические указания к выполнению
 Скачать 272.8 Kb.
|
|
Лабораторная работа. Создание динамических запросов при помощи хранимых процедур. Цель работы: изучить процесс создания динамических запросов при помощи хранимых процедур. Задание Создать процедуру, вычисляющую среднее трех чисел. Создать БД с таблицей студенты(ФИО, Курс, Возраст, Пол, Дата рождения, Группа), внести сведения о студентах группы (не менее десяти записей) Создать хранимую процедуру для отбора студентов из таблицы студенты по их «ФИО». Методические указания к выполнению Хранимая процедура – SQL запрос, который имеет параметры, то есть он выполняется как обычная процедура (мы задаем ее имя и передаем в хранимую процедуру значение параметров.) В зависимости от значения параметров хранимой процедуры мы получаем тот или иной результат запроса. Замечание: в SQL сервере хранимые процедуры реализуют динамические запросы, выполняемые на стороне сервера. Рассмотрим создание хранимых процедур при помощи команд языка SQL. Чтобы отобразить хранимые процедуры рабочей БД панели «Object Explorer», нужно выделить пункт «Stored Procedures». Чтобы создать новую процедуру при помощи команд языка SQL, нужно щелкнуть ЛКМ по кнопке  на панели инструментов. В рабочей области окна сервера появится вкладка SQLQuery1.sql, где нужно набрать код новой процедуры, который имеет следующий синтаксис: CREATE PROCEDURE <Имя процедуры> [@<Параметр1> <Тип1>[=<Значение1>], @<Параметр2> <Тип2>[=<Значение2>], …] [WITH ENCRYPTION] AS <Команды SQL> Здесь: Имя процедуры – имя создаваемой хранимой процедуры. Параметр1, Параметр2, … - параметры передаваемые в процедуру. Значение1, Значение2, … - значения параметров по умолчанию. Тип1, Тип2, … - типы данных параметров. WITH ENCRYPTION – включает шифрование данных. Команды SQL – SQL запрос, который выполняется при запуске процедур. Замечание: SQL запрос включает в себя параметры, если параметры сравниваются с какими-то полями или выражениями, то они должны иметь точно такой же тип данных, как эти поля или выражения. Замечание: после создания процедура помещается в раздел Stored Procedures текущей БД на панели «Object Explorer». Если дважды щелкнуть по процедуре ЛКМ, то она откроется для редактирования на вкладке «SQLQuery». Чтобы посмотреть информацию о хранимой процедуре, необходимо выполнить команду: EXEC SP_HELPTEXT <Имя процедуры> Хранимые процедуры могут быть запущены следующей командой EXEC <Имя процедуры> [<Параметр1>, <Параметр2>, …] Здесь: <Имя процедуры> - имя выполняемой процедуры; Параметр1, Параметр2, … - значение параметров. Пример: создание хранимой процедуры, который выводит имя студентов, у которых средний балл больше заданной величины: CREATE PROCEDURE СрБАЛЛ @X Real AS SELECT * FROM Студенты WHERE (Оценка1+ Оценка2+ Оценка3)/3>@X Команда вызова вышенабранной процедуры выглядит следующим образом: EXEC СрБАЛЛ 4 Команда выводит всех студентов, у которых средний балл больше 4. Для работы с хранимыми процедурами в обозревателе объектов необходимо выделить папку «Programmability/Stored Procedures» базы данных «Students» (см. рисунок 5.1).  Рисунок 5.1 Создадим процедуру, вычисляющую среднее трех чисел. Для создания новой хранимой процедуры щелкните ПКМ по папке «Stored Procedures» (см. рисунок 5.1) и в появившемся меню выберите пункт «New Stored Procedure». Появиться окно кода новой хранимой процедуры (см. рисунок 5.2).  Рисунок 5.2 Хранимая процедура имеет следующую структуру (см. рисунок 5.2): 1) область настройки параметров синтаксиса процедуры. Позволяет настраивать некоторые синтаксические правила, используемые при наборе кода процедуры. В нашем случае это: SET ANSI_NULLS ON включает использование значений NULL (Пусто) в кодировке ANSI, SET QUOTED_IDENTIFIER ON включает возможность использования двойных кавычек для определения идентификаторов; 2) область определения имени процедуры (Procedure_Name) и параметров передаваемых в процедуру (@Param1, @Param2). Определение параметров имеет следующий синтаксис: @<Имя параметра> <Тип данных> = <Значение по умолчанию> Параметры разделяются между собой запятыми; 3) начало тела процедуры, обозначается служебным словом «BEGIN»; 4) тело процедуры, содержит команды языка программирования запросов T-SQL; 5) конец тела процедуры, обозначается служебным словом «END». Замечание: в коде зеленым цветом выделяются комментарии. Они не обрабатываются сервером и выполняют функцию пояснений к коду. Строки комментариев начинаются с подстроки “–“. Далее в коде мы не будем отображать комментарии, они будут свернуты. Слева от раздела с комментариями будет стоять знак “+”, щелкнув по которому можно развернуть комментарий. Наберем код процедуры вычисляющей среднее трех чисел, как это показано на рисунке 5.3.  Рисунок 5.3 Рассмотрим код данной процедуры более подробно (см. рисунок 5.3): 1) CREATE PROCEDURE [Среднее трех величин] определяет имя создаваемой процедуры как «Среднее трех величин»; 2) @Value1 Real = 0, @Value2 Real = 0, @Value3 Real = 0 определяют три параметра процедуры Value1, Value2 и Value3. Данным параметрам можно присвоить дробные числа (Тип данных Real), значения по умолчанию равны 0; 3) SELECT 'Среднее значение'=(@Value1+@Value2+@Value3)/3 вычисляет среднее и выводит результат с подписью «Среднее значение». Остальные фрагменты кода рассмотрены выше (см. рисунок 10.2). Для создания процедуры, выполним вышеописанный код, нажав кнопку  .(Выполнить) на панели инструментов. В нижней части окна с кодом появиться сообщение «Command(s) completed successfully.». Закройте окно с кодом, щелкнув мышью по кнопке закрытия  .расположенной в верхнем правом углу окна с кодом процедуры. Проверим работоспособность созданной хранимой процедуры. Для запуска хранимой процедуры необходимо создать новый пустой запрос, нажав на кнопку  .(Новый запрос) на панели инструментов. В появившемся окне с пустым запросом наберите команду EXEC [Среднее трех величин] 1, 7, 9 и нажмите кнопку  на панели инструментов, как на рисунке 5.4. В нижней части окна с кодом появиться результат выполнения новой хранимой процедуры: Среднее значение 5,66667 (см. рисунок 5.4). Теперь создадим хранимую процедуру для отбора студентов из таблицы студенты по их «ФИО». Для этого создайте новую хранимую процедуру, как это описано выше, и наберите код новой процедуры как на рисунке 5.5.  Рисунок 5.4  Рисунок 5.5 Рассмотрим код процедуры «Отображение студентов по ФИО» более подробно (см. рисунок 5.5): 1) CREATE PROCRDURE [Отображение студентов по ФИО] определяет имя создаваемой процедуры как «Отображение студентов по ФИО»; 2) @FIO Varchar(50)='' определяют единственный параметр процедуры FIO. Параметру можно присвоить текстовые сроки переменной длины, длиной до 50 символов (Тип данных Varchar(50)), значения по умолчанию равны пустой строке; 3) SELECT * FROM dbo.Студенты WHERE ФИО=@FIO отобразить все поля (*) из таблицы студенты (dbo.Студенты), где значение поля ФИО равно значению параметра FIO (ФИО=@FIO). Выполним вышеописанный код и закроем окно с кодом, как описано выше и проверим работоспособность созданной хранимой процедуры. Создайте новый пустой запрос. В появившемся окне с пустым запросом наберите команду EXEC [Отображение студентов по ФИО] 'Иванов А.И.' и нажмите кнопку на панели инструментов (см. рисунок 5.6).  Рисунок 5.6 В нижней части окна с кодом появиться результат выполнения хранимой процедуры «Отображение студентов по ФИО» (рисунок 5.6). Теперь перейдем к более сложной задаче – отобразить студентов, у которых средний балл выше заданного. Создайте новую хранимую процедуру и наберите код новой процедуры, как на рисунке 5.7. Рассмотрим код процедуры «Отображение студентов по ФИО» более подробно (см. рисунок 5.7): 1) CREATE PROCRDURE [Отображение студентов по среднему баллу] определяет имя создаваемой процедуры как «Отображение студентов по среднему баллу»; 2) @Grade Real=0 определяют параметр процедуры Grade. Параметру можно присвоить дробные числа (Тип данных Real), значения по умолчанию равны 0; 3) SELECT * FROM [Запрос Студенты+Оценки] WHERE ([Оценка первого экзамена]+[Оценка второго экзамена]+[Оценка третьего экзамена]) /3>@Grade отобразит все поля (*) из запроса «Запрос Студенты+ Оценки» (Запрос Студенты+Оценки), где средний балл больше чем значение параметра Grade (([Оценка первого экзамена] + [Оценка второго экзамена]+[Оценка третьего экзамена])/3>@Grade).  Рисунок 5.7 Выполним вышеописанный код и закроем окно с кодом, как описано выше. Проверим, как работает запрос, описанный выше. Для этого создайте новый запрос и в нем наберите команду EXEC [Отображение студентов по среднему баллу] 3.5 и выполните ее (см. рисунок 5.8). В нижней части окна с кодом появится результат выполнения хранимой процедуры «Отображение студентов по среднему баллу» (см. рисунок 5.8). В заключение решим более сложную задачу – отображение студентов старше заданного возраста. Причем возраст будет автоматически вычисляться в зависимости от даты рождения. Создадим новую хранимую процедуру и наберем код новой процедуры, как представлено на рисунке 5.9.  Рисунок 5.8  Рисунок 5.9 Рассмотрим код создаваемой процедуры «Отображение студентов по возрасту» более подробно (см. рисунок 5.9): 1) CREATE PROCRDURE [Отображение студентов по возрасту] определяет имя создаваемой процедуры как «Отображение студентов по возрасту»; 2) @Age int=0 определяют параметр процедуры Grade. Параметру можно присвоить целые числа (Тип данных int), значения по умолчанию равны 0; 3) ФИО, [Запрос Студенты+Специальности].[Дата рождения], 'Возраст'=DATEDIFF(yy,[Запрос Студенты+Специальности].[Дата рождения], GETDATE()) отображает из запроса «Запроса Студенты+Специальности» (FROM [Запрос Студенты+Специальности]) поля «ФИО» (ФИО) и «Дата рождения» ([Запрос Студенты+Специальности].[Дата рождения]), а также отображает возраст студента ('Возраст') в годах (yy), вычисленный, исходя из его даты рождения и текущей даты (DATEDIFF(yy,[Запрос Студенты+ Специальности].[Дата рождения], GETDATE())). Более того, выводятся студенты возраст которых больше определенного в параметре «Age» (DATEDIFF(yy,[Запрос Студенты+Специальности].[Дата рождения], GETDATE())>@Age). Замечание: встроенная функция DATEDIFF вычисляющая количество периодов между двумя датами, имеет следующий синтаксис: DATEDIFF(<период>,<начальная дата>, <конечная дата>) Выполним код запроса «Отображение студентов по возрасту», а затем закроем окно с кодом, как описано выше. Проверим, как работает запрос. Для этого создадим новый запрос и в нем наберем команду EXEC [Отображение студентов по возрасту] 26 и выполните ее. Должен появиться результат аналогичный результату, представленному на рисунке 5.10.  Рисунок 5.10 |