Лекция: Создание динамических запросов при помощи хранимых процедур
Описывает создание динамических запросов при помощи хранимых процедур.
Цель: изучить процесс создания динамических запросов при помощи хранимых процедур
Хранимая процедура - SQL запрос, который имеет параметры, то есть он выполняется как обычная процедура (мы задаем ее имя и передаем в хранимую процедуру значение параметров.) В зависимости от значения параметров хранимой процедуры мы получаем тот или иной результат запроса.
Замечание. В SQL сервере хранимые процедуры реализуют динамические запросы, выполняемые на стороне сервера.
Рассмотрим создание хранимых процедур при помощи команд языка SQL. Чтобы отобразить хранимые процедуры рабочей БД панели " Object Explorer" нужно выделить пункт " Programmability-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.
На этом мы заканчиваем рассмотрение хранимых процедур. Дополнительную информацию можно найти в лабораторной работе №5.
5. Лабораторная работа: Хранимые процедуры
Описывает хранимые процедуры.
Цель: научиться работать с хранимыми процедурами
Перейдем к созданию хранимых процедур. Для работы с хранимыми процедурами в обозревателе объектов необходимо выделить папку " Programmability/Stored Procedures" базы данных " Students" (рис. 10.1).
Рис. 10.1.
Создадим процедуру, вычисляющую среднее трех чисел. Для создания новой хранимой процедуры щелкните ПКМ по папке " Stored Procedures" (рис. 10.1) и в появившемся меню выберите пункт " New Stored Procedure". Появиться окно кода новой хранимой процедуры (рис. 10.2).
увеличить изображение Рис. 10.2.
Хранимая процедура имеет следующую структуру (рис. 10.2):
Область настройки параметров синтаксиса процедуры. Позволяет настраивать некоторые синтаксические правила, используемые при наборе кода процедуры. В нашем случае это:
SET ANSI_NULLS ON - включает использование значений NULL (Пусто) в кодировке ANSI,
SET QUOTED_IDENTIFIER ON - включает возможность использования двойных кавычек для определения идентификаторов;
Область определения имени процедуры (Procedure_Name) и параметров передаваемых в процедуру (@Param1, @Param2). Определение параметров имеет следующий синтаксис:
@< Имя параметра> < Тип данных> = < Значение по умолчанию>
Параметры разделяются между собой запятыми;
Начало тела процедуры, обозначается служебным словом " BEGIN";
Тело процедуры, содержит команды языка программирования запросов T-SQL;
Конец тела процедуры, обозначается служебным словом " END".
Замечание: В коде зеленым цветом выделяются комментарии. Они не обрабатываются сервером и выполняют функцию пояснений к коду. Строки комментариев начинаются с подстроки " --". Далее в коде, мы не будем отображать комментарии, они будут свернуты. Слева от раздела с комментариями будет стоять знак " +", щелкнув по которому можно развернуть комментарий.
Наберем код процедуры вычисляющей среднее трех чисел, как это показано на рис. 10.3.
Рис. 10.3.
Рассмотрим код данной процедуры более подробно (рис. 10.3):
CREATE PROCEDURE [Среднее трех величин] - определяет имя создаваемой процедуры как " Среднее трех величин";
@Value1 Real = 0, @Value2 Real = 0, @Value3 Real = 0 - определяют три параметра процедуры Value1, Value2 и Value3. Данным параметрам можно присвоить дробные числа (Тип данных Real), значения по умолчанию равны 0;
SELECT 'Среднее значение'=(@Value1+@Value2+@Value3)/3 - вычисляет среднее и выводит результат с подписью " Среднее значение".
Остальные фрагменты кода рассмотрены выше (рис. 10.2).
Для создания процедуры, выполним вышеописанный код, нажав кнопку
(Выполнить) на панели инструментов. В нижней части окна с кодом появится сообщение " Command(s) completed successfully.". Закройте окно с кодом, щелкнув мышью по кнопке закрытия
расположенной в верхнем правом углу окна с кодом процедуры.
Проверим работоспособность созданной хранимой процедуры. Для запуска хранимой процедуры необходимо создать новый пустой запрос, нажав на кнопку
(Новый запрос) на панели инструментов. В появившемся окне с пустым запросом наберите команду EXEC [Среднее трех величин] 1, 7, 9 и нажмите кнопку
на панели инструментов (рис. 10.4).
увеличить изображение Рис. 10.4.
В нижней части окна с кодом появится результат выполнения новой хранимой процедуры: Среднее значение 5, 66667 (рис. 10.4).
Теперь создадим хранимую процедуру для отбора студентов из таблицы студенты по их " ФИО". Для этого создайте новую хранимую процедуру, как это описано выше, и наберите код новой процедуры как на рис. 10.5.
увеличить изображение Рис. 10.5.
Рассмотрим код процедуры " Отображение студентов по ФИО" более подробно (рис. 10.5):
CREATE PROCEDURE [Отображение студентов по ФИО] - определяет имя создаваемой процедуры как " Отображение студентов по ФИО";
@FIO Varchar(50)='' - определяют единственный параметр процедуры FIO. Параметру можно присвоить текстовые строки переменной длины, длинной до 50 символов (Тип данных Varchar(50)), значения по умолчанию равны пустой строке;
SELECT * FROM dbo.Студенты WHERE ФИО=@FIO - отобразить все поля (*) из таблицы студенты (dbo.Студенты), где значение поля ФИО равно значению параметра FIO (ФИО=@FIO).
Выполним вышеописанный код и закроем окно с кодом, как описано выше.
Проверим работоспособность созданной хранимой процедуры. Создайте новый пустой запрос. В появившемся окне с пустым запросом наберите команду EXEC [Отображение студентов по ФИО] 'Иванов А.И.' и нажмите кнопку
на панели инструментов (рис. 10.6).
увеличить изображение Рис. 10.6.
В нижней части окна с кодом появится результат выполнения хранимой процедуры " Отображение студентов по ФИО" (рис. 10.6).
Теперь перейдем к более сложной задаче - отобразить студентов, у которых средний балл выше заданного. Создайте новую хранимую процедуру и наберите код новой процедуры как на рис. 10.7.
увеличить изображение Рис. 10.7.
Рассмотрим код процедуры " Отображение студентов по среднему баллу" более подробно (рис. 10.7):
CREATE PROCEDURE [Отображение студентов по среднему баллу] - определяет имя создаваемой процедуры как " Отображение студентов по среднему баллу";
@Grade Real=0 - определяют параметр процедуры Grade. Параметру можно присвоить дробные числа (Тип данных Real), значения по умолчанию равны 0;
SELECT * FROM [Запрос Студенты+Оценки] WHERE ([Оценка первого экзамена]+[Оценка второго экзамена]+[Оценка третьего экзамена])/3> @Grade - отобразить все поля (*) из запроса " Запрос Студенты+Оценки" (Запрос Студенты+Оценки), где средний балл больше чем значение параметра Grade (([Оценка первого экзамена]+[Оценка второго экзамена]+[Оценка третьего экзамена])/3> @Grade).
Выполним вышеописанный код и закроем окно с кодом, как описано выше. Проверим, как работает запрос, описанный выше. Для этого, создайте новый запрос и в нем наберите команду EXEC [Отображение студентов по среднему баллу] 3.5 и выполните ее (Смотри выше) (рис. 10.8).
увеличить изображение Рис. 10.8.
В нижней части окна с кодом появиться результат выполнения хранимой процедуры " Отображение студентов по среднему баллу" (рис. 10.8).
В заключение решим более сложную задачу - отображение студентов старше заданного возраста. При чем возраст будет автоматически вычисляться в зависимости от даты рождения.
Создадим новую хранимую процедуру и наберем код новой процедуры как представлено на рис. 10.9.
увеличить изображение Рис. 10.9.
Рассмотрим код создаваемой процедуры " Отображение студентов по возрасту" более подробно (рис. 10.9):
CREATE PROCEDURE [Отображение студентов по возрасту] - определяет имя создаваемой процедуры как " Отображение студентов по возрасту";
@Age int=0 - определяют параметр процедуры Age. Параметру можно присвоить целые числа (Тип данных int), значения по умолчанию равны 0;
ФИО, [Запрос Студенты+Специальности].[Дата рождения], 'Возраст'=DATEDIFF(yy, [Запрос Студенты+Специальности].[Дата рождения], GETDATE()) - отображает из запроса " Запроса Студенты+Специальности" (FROM [Запрос Студенты+Специальности]) поля " ФИО" (ФИО) и " Дата рождения" ([Запрос Студенты+Специальности].[Дата рождения]), а также отображает возраст студента ('Возраст') в годах (yy), вычисленный исходя из его даты рождения и текущей даты (DATEDIFF(yy, [Запрос Студенты+Специальности].[Дата рождения], GETDATE())). Более того, выводятся студенты возраст которых больше определенного в параметре " Age" (DATEDIFF(yy, [Запрос Студенты+Специальности].[Дата рождения], GETDATE())> @Age).
Замечание: Встроенная функция DATEDIFF вычисляющая количество периодов между двумя датами, имеет следующий синтаксис: DATEDIFF(< период>, < начальная дата>, < конечная дата>)
Выполним код запроса " Отображение студентов по возрасту", а затем закроем окно с кодом, как описано выше. Проверим, как работает запрос. Для этого, создадим новый запрос и в нем наберем команду EXEC [Отображение студентов по возрасту] 26 и выполните ее. Должен появиться результат аналогичный результату, представленному на рис. 10.10.
увеличить изображение Рис. 10.10.
На этом мы заканчиваем описание хранимых процедур и переходим к рассмотрению пользовательских функций. В итоге, обозреватель объектов должен иметь вид как на рис. 10.11. Если созданные объекты не видны, выберите из контекстного меню для раздела Stored Procedures команду Refresh.
Рис. 10.11.
Лекция: Пользовательские функции
Содержит информацию по созданию пользовательских функций.
Цели:
Изучить порядок создания пользовательских функций
Освоить применение пользовательских функций
Пользовательские функции очень похожи на хранимые процедуры. Так же в них можно передавать параметры и они выполняют некоторые действия, однако их главным отличием от хранимых процедур является то, что они выводят (возвращают) какой то результат. Более того, они вызываются только при помощи оператора SELECT, аналогично встроенным функциям. Все пользовательские функции делятся на 2 вида:
Скалярные функции - функции, которые возвращают число или текст, то есть одно или несколько значений;
Табличные функции - функции, которые выводят результат в виде таблицы.
Для создания новой пользовательской функции используется команда CREATE FUNCTION имеющая следующий синтаксис:
CREATE FUNCTION < Имя функции>
([@< Параметр1> < Тип1> [=< Значение1> ],
@< Параметр2> < Тип2> [=< Значение2> ],...])
RETURNS < Тип> /TABLE
AS
RETURN([< Команды SQL> ])
Здесь:
Имя функции - имя создаваемой пользовательской функции.
Параметр1, Параметр2,.. - параметры передаваемые в функцию.
Значение1, Значение2, … - значения параметров по умолчанию.
Тип1, Тип2,... - типы данных параметров.
После служебного слова RETURNS в скалярных функциях ставится тип данных результата, который возвращает скалярная функция, либо ставится служебное слово TABLE в табличных функциях.
После служебного слова RETURN ставится SQL команда самой функции.
Замечание: После служебного слова RETURN может быть несколько команд, которые располагаются между словами BEGIN и END. В этом случае служебное слово RETURN не ставится.
Замечание: Тип данных параметра должен совпадать с типом данных выражения, в котором он используется.
Замечание: Если используются несколько SQL команд и BEGIN и END, то перед END нужно ставить команду RETURN < результат функции>.
Пример (скалярная пользовательская функция): Функция для вычисления среднего 3 чисел:
CREATE FUNCTION Среднее
(@X1 Int, @X2 Int, @X3 Int)
RETURNS Real
AS
BEGIN
DECLARE @Res Real
SET @Res =(@X1+@X2+@X3)/3
RETURN @Res
END
Замечание: Команда DECLARE создает переменную Res для хранения дробных чисел (тип данных Real).
Представленная выше пользовательская функция реализована при помощи нескольких команд SQL, но ее можно реализовать при помощи одной функции следующим образом:
CREATE FUNCTION Среднее
(@X1 Int, @X2 Int, @X3 Int)
RETURNS Real
AS
RETURN (SELECT (@X1+@X2+@X3)/3)
Созданная функция, вычисляющая среднее 6, 3 и 3, запускается следующим образом:
SELECT Среднее (6, 3, 3)
Результат будет 4.
Пример (табличная пользовательская функция): Из таблицы Студенты выводятся поля ФИО, дата рождения и столбец возраст, который вычисляется как разница дат в годах, между датой рождения и текущей датой (параметр CurDate).
CREATE FUNCTION Возраст
(@CurDate Date)
RETURNS TABLE
AS
RETURN (SELECT ФИО, [Дата рождения], Возраст = DATEDIFF (yy, [Дата рождения], @CurDate)
FROM Студенты)
Данная функция вызывается следующим образом:
SELECT * FROM Возраст ('12/17/2007')
В результате отобразятся студенты с их возрастом на 17 декабря 2007 года.
На этом мы заканчиваем рассмотрение пользовательских функций. Дополнительную информацию можно найти в лабораторной работе №6.
Лабораторная работа: Пользовательские функции
Содержит информацию о создании в " Microsoft SQL Server 2008" пользовательских функций.
Кардиналистский и ординалистский подходы Кардиналистский (количественный подход) к анализу полезности основан на представлении о возможности измерения различных благ в условных единицах полезности...
Обзор компонентов Multisim Компоненты – это основа любой схемы, это все элементы, из которых она состоит. Multisim оперирует с двумя категориями...
Словарная работа в детском саду Словарная работа в детском саду — это планомерное расширение активного словаря детей за счет незнакомых или трудных слов, которое идет одновременно с ознакомлением с окружающей действительностью, воспитанием правильного отношения к окружающему...
Приложение Г: Особенности заполнение справки формы ву-45
После выполнения полного опробования тормозов, а так же после сокращенного, если предварительно на станции было произведено полное опробование тормозов состава от стационарной установки с автоматической регистрацией параметров или без...
на панели инструментов. В рабочей области окна сервера появится вкладка SQLQuery1.sql, где нужно набрать код новой процедуры, который имеет следующий синтаксис:
CREATE PROCEDURE < Имя процедуры>
[@< Параметр1> < Тип1> [=< Значение1> ],
@< Параметр2> < Тип2> [=< Значение2> ], …]
[WITH ENCRYPTION]
AS < Команды SQL>
Здесь:
Рис. 10.1.
Создадим процедуру, вычисляющую среднее трех чисел. Для создания новой хранимой процедуры щелкните ПКМ по папке " Stored Procedures" (рис. 10.1) и в появившемся меню выберите пункт " New Stored Procedure". Появиться окно кода новой хранимой процедуры (рис. 10.2).
увеличить изображение Рис. 10.2.
Хранимая процедура имеет следующую структуру (рис. 10.2):
Рис. 10.3.
Рассмотрим код данной процедуры более подробно (рис. 10.3):
(Выполнить) на панели инструментов. В нижней части окна с кодом появится сообщение " Command(s) completed successfully.". Закройте окно с кодом, щелкнув мышью по кнопке закрытия
расположенной в верхнем правом углу окна с кодом процедуры.
Проверим работоспособность созданной хранимой процедуры. Для запуска хранимой процедуры необходимо создать новый пустой запрос, нажав на кнопку
увеличить изображение Рис. 10.4.
В нижней части окна с кодом появится результат выполнения новой хранимой процедуры: Среднее значение 5, 66667 (рис. 10.4).
Теперь создадим хранимую процедуру для отбора студентов из таблицы студенты по их " ФИО". Для этого создайте новую хранимую процедуру, как это описано выше, и наберите код новой процедуры как на рис. 10.5.
увеличить изображение Рис. 10.5.
Рассмотрим код процедуры " Отображение студентов по ФИО" более подробно (рис. 10.5):
увеличить изображение Рис. 10.6.
В нижней части окна с кодом появится результат выполнения хранимой процедуры " Отображение студентов по ФИО" (рис. 10.6).
Теперь перейдем к более сложной задаче - отобразить студентов, у которых средний балл выше заданного. Создайте новую хранимую процедуру и наберите код новой процедуры как на рис. 10.7.
увеличить изображение Рис. 10.7.
Рассмотрим код процедуры " Отображение студентов по среднему баллу" более подробно (рис. 10.7):
увеличить изображение Рис. 10.8.
В нижней части окна с кодом появиться результат выполнения хранимой процедуры " Отображение студентов по среднему баллу" (рис. 10.8).
В заключение решим более сложную задачу - отображение студентов старше заданного возраста. При чем возраст будет автоматически вычисляться в зависимости от даты рождения.
Создадим новую хранимую процедуру и наберем код новой процедуры как представлено на рис. 10.9.
увеличить изображение Рис. 10.9.
Рассмотрим код создаваемой процедуры " Отображение студентов по возрасту" более подробно (рис. 10.9):
увеличить изображение Рис. 10.10.
На этом мы заканчиваем описание хранимых процедур и переходим к рассмотрению пользовательских функций. В итоге, обозреватель объектов должен иметь вид как на рис. 10.11. Если созданные объекты не видны, выберите из контекстного меню для раздела Stored Procedures команду Refresh.
Рис. 10.11.
