Лабораторная работа: Пользовательские функции
Здесь «часть даты» — это закодированные специальными символами единицы измерения (часть даты) (yy — год, mm — месяц, dd — день), «начальная дата» — дата начала периода и «конечная дата» — дата конца периода. В нашем случае в качестве начальной даты берем дату рождения студента, а в качестве конечной даты берем текущую дату (функция GetDate ()). SET @TmpDate=Convert (Varchar… Читать ещё >
Лабораторная работа: Пользовательские функции (реферат, курсовая, диплом, контрольная)
Цель: научиться работать с пользовательскими функциями Теперь рассмотрим создание и применение пользовательских функций. В БД «Microsoft SQL Server 2008» все пользовательские функции находятся в папке «Functions» расположенной в папке «Programmability» в обозревателе объектов (рис. 12.1).
Рис. 12.1.
Начнем с создания скалярных пользовательских функций. Для создания новой скалярной пользовательской функции в обозревателе объектов, в БД «Students», в папке «Programmability», щелкните ПКМ по папке «Functions» и в появившемся меню выберите пункт «New/Scalar-valued Function». Появится окно новой скалярной пользовательской функции.
Рис. 12.2.
Синтаксис скалярной пользовательской функции похож на синтаксис хранимой процедуры (см. занятие 17). Однако имеется ряд существенных отличий (рис. 12.2):
Область определения имени функции (Inline_Function_Name);
Параметры, передаваемые в процедуру (@Param1). Определение параметров аналогично определению параметров в хранимой процедуре (см. занятие 5);
Тип данных значения возвращаемого процедурой;
Область объявления переменных, используемых внутри функции. Объявление переменных имеет следующий синтаксис:
DECLARE @.
Тело самой пользовательской функции, содержит команды языка программирования запросов T-SQL;
Команда RETURN возвращающая результат выполнения функции. Имеет следующий синтаксис:
RETURN @.
Переменная должна быть того же типа данных, который был указан в пункте 3.
Создадим скалярную пользовательскую функцию, вычисляющую среднее трех величин. В окне новой пользовательской функции наберите код представленный на рис. 12.3.
Рассмотрим более подробно код данной скалярной пользовательской функции (рис. 12.3):
CREATE FUNCTION [Функция средних трех величин] - определяет имя создаваемой функции как «Функция средних трех величин» ;
@Value1 Real, @Value2, @Value3 — определяют три параметра процедуры Value1, Value2 и Value3. Данным параметрам можно присвоить целые числа (Тип данных Int);
RETURNS Real — показывает, что функция возвращает дробные числа (Тип данных Real);
DECLARE @Result Real — объявляется переменная @Result для хранения результата работы функции, то есть дробного числа (Тип данных Real);
SELECT @Result=(@Value1+@Value2+@Value3)/3 — вычисляет среднее и помещает результат в переменную @Result ;
RETURN @Result — возвращает значение переменной @Result.
Остальные фрагменты кода рассмотрены выше (рис. 12.2).
Для создания функции, выполним вышеописанный код, нажав кнопку.
(Выполнить) на панели инструментов. В нижней части окна с кодом появиться сообщение «Command (s) completed successfully.». Закройте окно с кодом, щелкнув мышью по кнопке закрытия расположенной в верхнем правом углу окна с кодом функции.
Проверим работу созданной скалярной пользовательской функции. Для запуска пользовательской функции необходимо создать новый пустой запрос, нажав на кнопку.
(Новый запрос) на панели инструментов. В появившемся окне с пустым запросом наберите команду SELECT dbo. Функция средних трех величин] (3, 5, 4) и нажмите кнопку на панели инструментов (рис. 12.4).
Рис. 12.4.
В нижней части окна с кодом появится результат выполнения новой скалярной пользовательской функции: 4 (рис. 12.4).
Теперь создадим более сложную скалярную пользовательскую функцию, предназначенную для определения последнего дня месяца введенной даты. Создайте новую скалярную пользовательскую функцию, так как об этом сказано выше. В окне новой пользовательской функции наберите следующий код (рис. 12.5):
Перейдем к рассмотрению вышеприведенного кода (рис. 12.5). Код состоит из следующих групп команд:
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)).
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 (,). Здесь «часть даты» — это закодированная специальными символами определяемая часть даты (yy — год, mm — месяц, dd — день), «дата» — это дата, части которой определяем.
IF @Month=12.
BEGIN.
SET @Month=1.
SET @Year=@Year+1.
END.
ELSE.
BEGIN.
SET @Month=@Month+1.
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.» закройте окно с кодом.
Проверим работу функции «Последний день месяца» выполнив ее. Создайте новый пустой запрос, затем в окне с пустым запросом наберите команду SELECT dbo. Последний день месяца] ('12/07/08') и нажмите кнопку на панели инструментов (рис. 12.6).
Рис. 12.6.
Появится результат выполнения новой скалярной пользовательской функции: 2008;12−31 (рис. 12.6).
Теперь перейдем к созданию табличных пользовательских функций. Для создания табличной пользовательской функции в обозревателе объектов, в БД «Students», в папке «Programmability», щелкните ПКМ по папке «Functions» и в появившемся меню выберите пункт «New/Table-valued Function». Появится окно новой табличной пользовательской функции (рис. 12.7).
Рис. 12.7.
Рассмотрим структуру кода табличной пользовательской функции. Табличная пользовательская функция состоит из следующих разделов:
Область определения имени функции (Inline_Function_Name);
Параметры, передаваемые в процедуру (@Param1, @Param2);
RETURNS TABLE показывает что функция является табличной, то есть возвращает таблицу;
Тело самой пользовательской функции, состоит из команды SELECT языка программирования запросов T-SQL.
Остальные разделы табличной пользовательской функции аналогичны таким же разделам хранимых процедур и скалярных пользовательских функций.
В заключение рассмотрим создание табличной пользовательской функции «Функция отбора по возрасту», вычисляющих текущий возраст студентов в зависимости от их даты рождения. В окне новой пользовательской функции (рис. 12.7) наберите следующий код (рис. 12.8):
Рис. 12.8.
Из кода представленного на рис. 12.8 видно, что данная табличная функция не имеет параметров и реализуется командой.
SELECT ФИО, [Дата рождения], Возраст = DateDiff (yy, [Дата рождения], GetDate ()).
FROM Студенты Из вышепредставленной команды видно, что из таблицы «Студенты» отображаются поля «ФИО» и «Дата рождения», а также вычислимое поле «Возраст». Поле «Возраст» вычисляется при помощи встроенной функцииDateDiff вычисляющей различие между датами в определенных единицах измерения (частях даты) и имеющей следующий синтаксис:
DateDiff (, ,).
Здесь «часть даты» — это закодированные специальными символами единицы измерения (часть даты) (yy — год, mm — месяц, dd — день), «начальная дата» — дата начала периода и «конечная дата» — дата конца периода. В нашем случае в качестве начальной даты берем дату рождения студента, а в качестве конечной даты берем текущую дату (функция GetDate ()).
Для создания функции, выполним вышеописанный код, как и в случае с предыдущей функцией. После появления сообщения «Command (s) completed successfully.» закройте окно с кодом.
Проверим работоспособность новой табличной пользовательской функции. Создайте новый пустой запрос, затем в окне с пустым запросом наберите команду SELECT * FROM dbo. Функция отбора по возрасту]() и нажмите кнопку на панели инструментов (рис. 12.9).
Рис. 12.9.
В нижней части окна появиться таблица с фамилиями, датами рождения и возрастом студентов на данный момент времени (рис. 12.9).
Замечание: Обратите внимание на тот факт, что мы работаем с табличной функцией как с обыкновенной таблицей.
На этом мы заканчиваем рассмотрение пользовательских функций и переходим к рассмотрению целостности данных, диаграмм и триггеров. По окончании выполнения главы 6 обозреватель объектов будет иметь следующий вид (рис. 12.10):
Рис. 12.10.