Проектирование информационных систем в Microsoft SQL Server 2008 и Visual Studio 2008

Пользовательские функции

Разбить на страницы
Показывать лекцию целиком

Цель: научиться работать с пользовательскими функциями

Теперь рассмотрим создание и применение пользовательских функций. В БД "Microsoft SQL Server 2008" все пользовательские функции находятся в папке ).

(рис 12.1)

Начнем с создания скалярных пользовательских функций. Для создания новой скалярной пользовательской функции в обозревателе объектов, в БД )

(рис 12.2)

Синтаксис скалярной пользовательской функции похож на синтаксис хранимой процедуры (см. ):

  • Область определения имени функции (Inline_Function_Name);
  • Параметры, передаваемые в процедуру (@Param1). Определение параметров аналогично определению параметров в хранимой процедуре (см. занятие 5);
  • Тип данных значения возвращаемого процедурой;
  • Область объявления переменных, используемых внутри функции. Объявление переменных имеет следующий синтаксис:
    DECLARE @<Имя переменной> <Тип данных>
  • Тело самой пользовательской функции, содержит команды языка программирования запросов T-SQL;
  • Команда RETURN возвращающая результат выполнения функции. Имеет следующий синтаксис:
    RETURN @<Имя переменной с результатом>
    Переменная должна быть того же типа данных, который был указан в пункте 3.
    •

    Создадим скалярную пользовательскую функцию, вычисляющую среднее трех величин. В окне новой пользовательской функции наберите код представленный на рис 12.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).

    Для создания функции, выполним вышеописанный код, нажав кнопку(Выполнить) на панели инструментов. В нижней части окна с кодом появиться сообщение расположенной в верхнем правом углу окна с кодом функции.

    Проверим работу созданной скалярной пользовательской функции. Для запуска пользовательской функции необходимо создать новый пустой запрос, нажав на кнопку(Новый запрос) на панели инструментов. В появившемся окне с пустым запросом наберите команду на панели инструментов (рис 12.4).

    (рис 12.4)

    В нижней части окна с кодом появится результат выполнения новой скалярной пользовательской функции: ).

    Теперь создадим более сложную скалярную пользовательскую функцию, предназначенную для определения последнего дня месяца введенной даты.

    Создайте новую скалярную пользовательскую функцию, так как об этом сказано выше. В окне новой пользовательской функции наберите следующий код (рис 12.5):

    (рис 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." закройте окно с кодом.

    Проверим работу функции ).

    (рис 12.6)

    Появится результат выполнения новой скалярной пользовательской функции: ).

    Теперь перейдем к созданию табличных пользовательских функций. Для создания табличной пользовательской функции в обозревателе объектов, в БД )

    (рис 12.7)

    Рассмотрим структуру кода табличной пользовательской функции. Табличная пользовательская функция состоит из следующих разделов:

  • Область определения имени функции (Inline_Function_Name);
  • Параметры, передаваемые в процедуру ( @Param1, @Param2 );
  • RETURNS TABLE показывает что функция является табличной, то есть возвращает таблицу;
  • Тело самой пользовательской функции, состоит из команды SELECT языка программирования запросов T-SQL.
    •

    Остальные разделы табличной пользовательской функции аналогичны таким же разделам хранимых процедур и скалярных пользовательских функций.

    В заключение рассмотрим создание табличной пользовательской функции ) наберите следующий код (рис 12.8):

    (рис 12.8)

    Из кода представленного на рис 12.8 видно, что данная табличная функция не имеет параметров и реализуется командой

    SELECT ФИО, [Дата рождения], Возраст = DateDiff(yy, [Дата рождения], GetDate())
FROM Студенты

    Из вышепредставленной команды видно, что из таблицы "Студенты" отображаются поля "ФИО" и "Дата рождения", а также вычислимое поле "Возраст". Поле "Возраст" вычисляется при помощи встроенной функции DateDiff вычисляющей различие между датами в определенных единицах измерения (частях даты) и имеющей следующий синтаксис:

    DateDiff(<часть даты>, <начальная дата>, <конечная дата>).

    Здесь "часть даты" - это закодированные специальными символами единицы измерения (часть даты) (yy - год, mm - месяц, dd - день), "начальная дата" - дата начала периода и "конечная дата" - дата конца периода. В нашем случае в качестве начальной даты берем дату рождения студента, а в качестве конечной даты берем текущую дату (функция GetDate() ).

    Для создания функции, выполним вышеописанный код, как и в случае с предыдущей функцией. После появления сообщения "Command(s) completed successfully." закройте окно с кодом.

    Проверим работоспособность новой табличной пользовательской функции. Создайте новый пустой запрос, затем в окне с пустым запросом наберите команду на панели инструментов (рис 12.9).

    (рис 12.9)

    В нижней части окна появиться таблица с фамилиями, датами рождения и возрастом студентов на данный момент времени (рис 12.9).

    Замечание: Обратите внимание на тот факт, что мы работаем с табличной функцией как с обыкновенной таблицей.

    На этом мы заканчиваем рассмотрение пользовательских функций и переходим к рассмотрению целостности данных, диаграмм и триггеров. По окончании выполнения главы 6 обозреватель объектов будет иметь следующий вид (рис 12.10):

    (рис 12.10)
    Страницы:

    Цель: научиться работать с пользовательскими функциями

    Теперь рассмотрим создание и применение пользовательских функций. В БД "Microsoft SQL Server 2008" все пользовательские функции находятся в папке ).

    (рис 12.1)

    Начнем с создания скалярных пользовательских функций. Для создания новой скалярной пользовательской функции в обозревателе объектов, в БД )

    (рис 12.2)

    Синтаксис скалярной пользовательской функции похож на синтаксис хранимой процедуры (см. ):

  • Область определения имени функции (Inline_Function_Name);
  • Параметры, передаваемые в процедуру (@Param1). Определение параметров аналогично определению параметров в хранимой процедуре (см. занятие 5);
  • Тип данных значения возвращаемого процедурой;
  • Область объявления переменных, используемых внутри функции. Объявление переменных имеет следующий синтаксис:
    DECLARE @<Имя переменной> <Тип данных>
  • Тело самой пользовательской функции, содержит команды языка программирования запросов T-SQL;
  • Команда RETURN возвращающая результат выполнения функции. Имеет следующий синтаксис:
    RETURN @<Имя переменной с результатом>
    Переменная должна быть того же типа данных, который был указан в пункте 3.
    •

    Создадим скалярную пользовательскую функцию, вычисляющую среднее трех величин. В окне новой пользовательской функции наберите код представленный на рис 12.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).

    Для создания функции, выполним вышеописанный код, нажав кнопку(Выполнить) на панели инструментов. В нижней части окна с кодом появиться сообщение расположенной в верхнем правом углу окна с кодом функции.

    Проверим работу созданной скалярной пользовательской функции. Для запуска пользовательской функции необходимо создать новый пустой запрос, нажав на кнопку(Новый запрос) на панели инструментов. В появившемся окне с пустым запросом наберите команду на панели инструментов (рис 12.4).

    (рис 12.4)

    В нижней части окна с кодом появится результат выполнения новой скалярной пользовательской функции: ).

    Теперь создадим более сложную скалярную пользовательскую функцию, предназначенную для определения последнего дня месяца введенной даты.

    Создайте новую скалярную пользовательскую функцию, так как об этом сказано выше. В окне новой пользовательской функции наберите следующий код (рис 12.5):

    (рис 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." закройте окно с кодом.

    Проверим работу функции ).

    (рис 12.6)

    Появится результат выполнения новой скалярной пользовательской функции: ).

    Теперь перейдем к созданию табличных пользовательских функций. Для создания табличной пользовательской функции в обозревателе объектов, в БД )

    (рис 12.7)

    Рассмотрим структуру кода табличной пользовательской функции. Табличная пользовательская функция состоит из следующих разделов:

  • Область определения имени функции (Inline_Function_Name);
  • Параметры, передаваемые в процедуру ( @Param1, @Param2 );
  • RETURNS TABLE показывает что функция является табличной, то есть возвращает таблицу;
  • Тело самой пользовательской функции, состоит из команды SELECT языка программирования запросов T-SQL.
    •

    Остальные разделы табличной пользовательской функции аналогичны таким же разделам хранимых процедур и скалярных пользовательских функций.

    В заключение рассмотрим создание табличной пользовательской функции ) наберите следующий код (рис 12.8):

    (рис 12.8)

    Из кода представленного на рис 12.8 видно, что данная табличная функция не имеет параметров и реализуется командой

    SELECT ФИО, [Дата рождения], Возраст = DateDiff(yy, [Дата рождения], GetDate())
FROM Студенты

    Из вышепредставленной команды видно, что из таблицы "Студенты" отображаются поля "ФИО" и "Дата рождения", а также вычислимое поле "Возраст". Поле "Возраст" вычисляется при помощи встроенной функции DateDiff вычисляющей различие между датами в определенных единицах измерения (частях даты) и имеющей следующий синтаксис:

    DateDiff(<часть даты>, <начальная дата>, <конечная дата>).

    Здесь "часть даты" - это закодированные специальными символами единицы измерения (часть даты) (yy - год, mm - месяц, dd - день), "начальная дата" - дата начала периода и "конечная дата" - дата конца периода. В нашем случае в качестве начальной даты берем дату рождения студента, а в качестве конечной даты берем текущую дату (функция GetDate() ).

    Для создания функции, выполним вышеописанный код, как и в случае с предыдущей функцией. После появления сообщения "Command(s) completed successfully." закройте окно с кодом.

    Проверим работоспособность новой табличной пользовательской функции. Создайте новый пустой запрос, затем в окне с пустым запросом наберите команду на панели инструментов (рис 12.9).

    (рис 12.9)

    В нижней части окна появиться таблица с фамилиями, датами рождения и возрастом студентов на данный момент времени (рис 12.9).

    Замечание: Обратите внимание на тот факт, что мы работаем с табличной функцией как с обыкновенной таблицей.

    На этом мы заканчиваем рассмотрение пользовательских функций и переходим к рассмотрению целостности данных, диаграмм и триггеров. По окончании выполнения главы 6 обозреватель объектов будет иметь следующий вид (рис 12.10):

    (рис 12.10)
    Предыдущее занятие Следующее занятие
    Вернуться к учебному плану
    ^