Методические указания к практическим занятиям
жүктеу/скачать 22,46 Mb.
|
МУ Базы данных SQL Server2012 Винокурова
Лабораторная работа №5Пользовательские функцииЦели:Изучить порядок создания пользовательских функций. Освоить применение пользовательских функций. Пользовательские функции очень похожи на хранимые процедуры. Так же в них можно передавать параметры и они выполняют некоторые действия, однако их главным отличием от хранимых процедур является то, что они выво- дят (возвращают) какой то результат. Более того, они вызываются только при помощи оператора SELECT, аналогично встроенным функциям. Все пользова- тельские функции делятся на 2 вида: Скалярные функции — функции, которые возвращают число или текст, то есть одно или несколько значений; Табличные функции — функции, которые выводят результат виде табли- цы. Теперь рассмотрим создание и применение пользовательских функций. В Microsoft SQL Server все пользовательские функции находятся в папке “Functions” («Функции»), расположенной в папке “Programmability” («ПРо- граммирование») в обозревателе объектов (рис. 69). Рисунок 69 — Отображение функций в Обозревателе объектов Начнём с создания скалярных пользовательских функций. Для создания новой скалярной пользовательской функции в обозревателе объектов в БД “Students” в папке “Programmability” («Программирование»), щёлкните правой кнопкой мыши по папке “Functions” («Функции») и в появившемся меню выбе- рите пункт “New / Scalar-valued Function” («Создать / Скалярная функция»). Появится окно новой скалярной пользовательской функции (рис. 70). Рисунок 70 — Окно новой скалярной пользовательской функции Синтаксис скалярной пользовательской функции похож на синтаксис хранимой процедуры (см. лабораторную работу 4). Однако имеется ряд суще- ственных отличий (рис. 70): Область определения имени функции (Scalar_Function_Name); Параметры, передаваемые в процедуру (@Param1). Определение пара- метров аналогично определению параметров в хранимой процедуре (см. лабораторную работу 4); Тип данных значения, возвращаемого процедурой; Область объявления переменных, используемых внутри функции. Объяв- ление переменных имеет следующий синтаксис: DECLARE @<Имя переменной> <Тип данных> Тело самой пользовательской функции, содержит команды языка про- граммирования запросов SQL; Команда RETURN возвращающая результат выполнения функции. Имеет следующий синтаксис: RETURN @<Имя переменной с результатом> Переменная должна быть того же типа данных, который был указан в пункте 3. Создадим скалярную пользовательскую функцию, вычисляющую среднее трёх величин. В окне новой пользовательской функции наберите код, представ- ленный на рисунке 71. Рисунок 71 — Текст пользовательской функции, вычисляющей среднее значение трех величин Рассмотрим более подробно код данной скалярной пользовательской функции (рис. 71): CREATE FUNCTION [Функция средних трёх величин] — определяет имя создаваемой функции как «Функция средних трёх величин»; @Value1 int, @Value2 int, @Value3 int — определяют три параметра про- цедуры Value1, Value2 и Value3. Данным параметрам можно присвоить целые числа (тип данных int); RETURNS Real — показывает, что функция возвращает дробные числа (тип данных Real); DECLARE @Result Real — объявляется переменная @Result для хране- ния результата работы функции, т.е. дробного числа (тип данных Real); SELECT @Result = (@Valuel + @Value2 + @Value3) /3 — вычисляет среднее и помешает результат в переменную @Result; RETURN @Result — возвращает значение переменной @Result. Остальные фрагменты кода рассмотрены выше (рис. 70). Для создания функции выполним вышеописанный код, нажав кнопку ( ) на панели инструментов. В нижней части окна с кодом появиться сообщение “Conimand(s) completed successfully.” («Выполнение ко- манд успешно завершено.»). Закройте окно с кодом, щёлкнув мышью по кнопке закрытия , расположенной в верхнем правом углу окна с кодом функции. Появится окно сохранения функции. Сохраним функцию под именем «ФункцияСреднихТрехВеличин» в своей папке. Одновременно функция отоб- разится в окне Обозревателя объектов в папке “Programmability” («Программи- рование») / “Functions” («Функции») / “Scalar-valued Functions” («Скалярные функции») под именем dbo.[Функция средних трех величин]. Проверим работу созданной скалярной пользовательской функции. Для запуска пользовательской функции необходимо создать новый пустой запрос, нажав на кнопку ( ) на панели инструментов. В по- явившемся окне с пустым запросом наберите команду: SELECT dbo.[Функция средних трёх величин] (3, 5, 4) и нажмите кнопку ( ) на панели инструментов (рис. 72). В нижней части окна с кодом появится результат выполнения новой ска- лярной пользовательской функции: 4 (рис. 72). Рисунок 72 — Результат выполнения функции «Функция средних трех величин» Теперь создадим более сложную скалярную пользовательскую функцию, предназначенную для определения последнего дня месяца введённой даты. Создайте новую скалярную пользовательскую функцию так, как об этом сказано выше. В окне новой пользовательской функции наберите следующий код (рис. 73). Перейдём к рассмотрению кода (рис. 73). Код состоит из следующих групп команд: CREATE FUNCTION [Последний день месяца] — определяет имя созда- ваемой функции как «Последний день месяца»; @MyDate — определяют параметр процедуры @MyDaTe. Параметру можно присвоить значения дат или времени (тип данных DateTime); RETURNS DateTime — показывает, что функция возвращает дату или время (тип данных DateTime); DECLARE @Year int, DECLARE @Month int, DECLARE @Day int — объявляются переменные @Year, @Month и @Day для хранения цело- численных значений года, месяца и дня введённой даты (тип данных int). DECLARE @TmpDate VarChar(10) объявляет переменную @TmpDate для хранения промежуточного значения даты в строке длинной до 10 симво- лов (тип данных VarChar(10)). Рисунок 73 — Текст пользовательской функции «Последний день месяца» DECLARE @Result DateTime — объявляет переменную @Result для хра- нения результата — даты последнего дня месяца (тип данных DateTime). SET @Year = DatePart(yy, @MyDate), SET @Month = DatePart(mm, @MyDate), SET @Day = DatePart(dd, @MyDate) — определяются части введённой даты и помещаются в переменные @Year, @Month и @Day. Для определения частей даты используется функция DatePart, имеющая следующий синтаксис: DatePart (<часть даты>, <дата>). Здесь «часть да- ты» — это закодированная специальными символами определяемая часть даты (уу — год, mm — месяц, dd — день), «дата» — это дата, части кото- рой определяем. IF @Month=12 BEGIN SET @Month=l SET @Year=@Year+l ELSE
BEGIN
END Данный фрагмент кода выполняет следующие действия: если номер ме- сяца равен 12, то установить номер месяца (@Month) равным 1 и увели- чить год (@Year) на 1, иначе увеличить месяц на 1. SET @TmpDate = Convert(Varchar, @Month)+'/01/'+Convert(Varchar, @Year), SET @Result = Convert(DateTime, @TmpDate) — переводит числовые значения даты в дату в строковом формате и записывает её в переменную @TmpDate, затем переводит дату в строковом формате в тип данных да- ты и времени и помешает её в переменную @Result. Для конвертации ис- пользуется функция Convert, имеющая следующий синтаксис: Convert (<тип данных>, <значение>). Здесь «тип данных» — это тип данных, в который переводится «значе- ние». SET @Result = DateAdd(dd, -1, @Result) — из даты, хранимой в пермен- ной @Result, вычитается 1 день, для этого используется функция DateAdd, имеющая следующий синтаксис: DateAdd(<часть даты>, <количество периодов>, <дата>), Здесь «часть даты» — это закодированная специальными символами определяемая часть даты (см. функцию DatePart), «количество периодов» — это количество частей даты, прибавляемой к введённой дате (параметр «дата»). RETURN @Result — возвращает значение, хранимое в переменной @Result. Для создания функции выполним вышеописанный код, как и в случае с предыдущей функцией, нажав кнопку ( ). После появле- ния сообщения “Command(s) completed successfully.” («Выполнение команд успешно завершено.») закройте окно с кодом. Появится окно сохранения функции. Сохраним функцию под именем «ПоследнийДеньМесяца» в своей папке. Одновременно функция отобразится в окне Обозревателя объектов в папке “Programmability” («Программирование») / “Functions” («Функции») / “Scalar-valued Functions” («Скалярные функции») под именем dbo.[Последний день месяца]. Проверим работу функции «Последний день месяца», выполнив её. Со- здайте новый пустой запрос, затем в окне с пустым запросом наберите команду SELECT dbo.[Последний день месяца] ('12/02/14') и нажмите кнопку ( ) на панели инструментов (рис. 74). Рисунок 74 – Результат выполнения пользовательской функции «Последний день месяца» Появится результат выполнения новой скалярной пользовательской функции: 2014-12-31 (рис. 74). Теперь перейдём к созданию табличных пользовательских функций. Для создания табличной пользовательской функции в обозревателе объектов в БД Students в папке “Programmability” («Программирование») щёлкните правой кнопкой мыши по папке “Functions” («Функции») и в появившемся меню выбе- рите пункт “New Table-valued Function” («Создать / Встроенная функция, воз- вращающая табличное значение»). Появится окно новой табличной пользова- тельской функции (рис. 75) Рисунок 75 — Окно новой табличной пользовательской функции Рассмотрим структуру кода табличной пользовательской функции. Таб- личная пользовательская функция состоит из следующих разделов: Область определения имени функции (Inline_Function_Name); Параметры, передаваемые в процедуру (@Param1, @Рагаш2); RETURNS TABLE — показывает, что функция является табличной, т.е. возвращает таблицу; Тело самой пользовательской функции состоит из команды SELECT язы- ка программирования запросов SQL. Остальные разделы табличной пользовательской функции аналогичны таким же разделам хранимых процедур и скалярных пользовательских функ- ций. В заключении рассмотрим создание табличной пользовательской функ- ции «Функция отбора по возрасту», вычисляющих текущий возраст студентов в зависимости от их даты рождения. В окне новой пользовательской функции (рис. 75) наберите следующий код (рис. 76): Рисунок 76 — Текст пользовательской функции «Функция отбора по возрасту» Из кода, представленного на рисунке 76, видно, что данная табличная функция не имеет параметров и реализуется командой: SELECT Фамилия, Имя, Отчество, [Дата рождения], Возраст = DateDiff(yy, [Дата рождения], GetDate()) FROM Студенты. Из вышепредставленной команды видно, что из таблицы «Студенты» отображаются поля «Фамилия», «Имя», «Отчество» и «Дата рождения», а так- же вычислимое поле «Возраст». Поле «Возраст» вычисляется при помощи встроенной функции DateDiff, вычисляющей разницу между датами в опреде- лённых единицах измерения (частях даты) и имеющей следующий синтаксис: DateDiff(<часть даты>, <начальная дата>, <конечная дата>). Здесь «часть даты» — это закодированные специальными символами единицы измерения (часть даты) (уу — год, mm — месяц, dd — день), «началь- ная дата» — дата начала периода и «конечная дата» — дата конца периода. В нашем случае в качестве начальной даты берём дату рождения студента, а в ка- честве конечной даты берём текущую дату (функция GetDate()). Для создания функции, выполним вышеописанный код, как и в случае с предыдущей функцией. После появления сообщения “Command(s) completed successfully.” («Выполнение команд успешно завершено.») закройте окно с ко- дом. Появится окно сохранения функции. Сохраним функцию под именем «ФункцияОтбораПоВозрасту» в своей папке. Одновременно функция отобра- зится в окне Обозревателя объектов в папке “Programmability” («Программиро- вание») / “Functions” («Функции») / “Table-valued Functions” («Функции, воз- вращающие табличное значение») под именем dbo.[Функция отбора по возрас- ту]. Проверим работоспособность новой табличной пользовательской функ- ции. Создайте новый пустой запрос, затем в окне с пустым запросом наберите команду: SELECT * FROM dbo.[Функция отбора по возрасту] () и нажмите кнопку ( ) на панели инструментов (рис. 77). Рисунок 77 — Результат выполнения пользовательской функции «Функция отбора по возрасту» В нижней части окна появится таблица с фамилиями, именами, отчества- ми, датами рождения и возрастом студентов на данный момент времени (рис. 77). жүктеу/скачать 22,46 Mb. Достарыңызбен бөлісу:
|