Лабораторна робота 14

Користувацькі функції

 

Мета роботи: навчитися створювати користувацькі функції в середовищі SQL Server Management Studio.

 

Теоретичні відомості

У середовищі SQL Server Management Studio всі користувацькі функції знаходяться в теці «Функції», що розташована в теці «Програмування» в оглядачі об'єктів (рис.1).

Описание: D:\Lutsk NTU\Предмети\БД\КН\ЕНП\practic\pr14.files\image001.png

Рис.1

 

 Розпочнемо із створення скалярних користувацьких функцій. Для створення нової скалярної користувацької функції в оглядачі об'єктів клікніть правою кнопкою миші по теці «Функції» і в меню, що з'явилося, виберіть пункт «Створити Скалярна функція». З'явиться вікно нової скалярної користувацької функції (рис.2).

Описание: D:\Lutsk NTU\Предмети\БД\КН\ЕНП\practic\pr14.files\image002.jpg

Рис.2.

 

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

1. Область визначення імені функції (Scalar_Function_Name);

2. Параметри, що передаються у функцію (@param1). Визначення параметрів аналогічно визначенню параметрів в збережуваній процедурі;

3. Тип даних значення, що повертається функцією;

4. Область оголошення змінних, що використовуються у середині функції.

Оголошення змінних має наступний синтаксис:

DECLARE @<Ім'я змінної> <Тип даних>

5. Тіло самої користувацької функції, містить команди мови T - SQL;

6. Команда RETURN повертає результат виконання функції. Має наступний синтаксис:

RETURN @<Ім'я змінної з результатом>

Змінна має бути того ж типу даних, який був вказаний в пункті 3.

 

Приклад виконання лабораторної роботи

Створимо скалярну користувацьку функцію, що обчислює середнє трьох величин. У вікні нової користувацької функції наберіть код представлений на рис.3.

Описание: D:\Lutsk NTU\Предмети\БД\КН\ЕНП\practic\pr14.files\image003.jpg

 

Рис.3.

 

Розглянемо детальніше код цієї скалярної користувацької функції:

1. CREATE FUNCTION FuncMeanValue визначає ім'я створюваної функції як FuncMeanValue;

2. @Value1, @Value2, @Value3 визначають три параметри процедури Value1, Value2 і Value3. Цим параметрам можна присвоїти цілі числа (тип даних int);

3. RETURNS real показує, що функція повертає дійсні числа (тип даних real);

4. DECLARE @Result real оголошується змінна @Result для зберігання результату роботи функції дійсного типу (тип даних real);

5. SELECT @Result = (@Value1 + @Value2 + @Value3) / 3 обчислює середнє і поміщає результат в змінну @Result;

6. RETURN @Result повертає значення змінної @Result.

 

Для створення функції, виконаємо вищеописаний код, натиснувши кнопку «Виконати» на панелі інструментів. У нижній частині вікна з кодом з'явиться повідомлення «Виконання команд успішно завершене».

Для перевірки роботи створеної скалярної користувацької функції необхідно створити новий порожній запит, натиснувши на кнопку «Створити запит» на панелі інструментів. У вікні, що з'явилося, з порожнім запитом наберіть команду SELECT dbo.FuncMeanValue (3, 5, 4) і натисніть кнопку «Виконати» на панелі інструментів (рис.4).

Описание: D:\Lutsk NTU\Предмети\БД\КН\ЕНП\practic\pr14.files\image004.jpg

Рис.4

 

У нижній частині вікна з кодом з'явиться результат виконання нової скалярної користувацької функції.

Тепер перейдемо до створення табличних користувацьких функцій. Для створення табличної користувацької функції в оглядачі об'єктів клікніть правою кнопкою миші по теці «Функції» і в меню, що з'явилося, виберіть пункт «Створити Вбудована функція, що повертає табличне значення…». З'явиться вікно нової табличної користувацької функції (рис.5).

Описание: D:\Lutsk NTU\Предмети\БД\КН\ЕНП\practic\pr14.files\image005.jpg

Рис. 5

Таблична користувацька функція складається з наступних розділів:

1.   Область визначення імені функції (Inline_Function_Name);

2. Параметри, що передаються у функцію (@param1, @param2);

3. RETURNS TABLE показує, що функція повертає таблицю;

4. Тіло самої користувацької функції складається з команди SELECT мови T - SQL.

Розглянемо створення табличної користувацької функції, що вибирає інформацію про художників по країні їх проживання. У вікні нової користувацької функції (рис. 5) наберемо наступний код (рис.6) :

Описание: D:\Lutsk NTU\Предмети\БД\КН\ЕНП\practic\pr14.files\image006.jpg

Рис.6.

З коду функції видно, що вона приймає один параметр типу varchar (назва країни) і реалізується запитом:

Описание: D:\Lutsk NTU\Предмети\БД\КН\ЕНП\practic\pr14.files\image007.png

З таблиці «Authors» вибираються прізвища, імена, по батькові, дати народження і смерті художників, при цьому таблиця «Authors» з'єднується (INNER JOIN) з таблицею «Countries» по стовпцю «CountryCode». Умовою з'єднання є рівність утримуваного поля «CountryCode» параметру, що передається у функцію.

Результат виконання цієї функції приведений на рис. 7.

Описание: D:\Lutsk NTU\Предмети\БД\КН\ЕНП\practic\pr14.files\image008.jpg

Рис.7

Робота з табличною функцією здійснюється так само, як і із звичайною таблицею.

Завдання

Створити функції у відповідності із завданням для свого варіанту.