Лабораторна
робота 14
Користувацькі функції
Мета роботи: навчитися створювати користувацькі функції в середовищі SQL Server Management Studio.
Теоретичні відомості
У середовищі SQL Server Management Studio всі користувацькі функції знаходяться в теці «Функції», що розташована в теці «Програмування» в оглядачі об'єктів (рис.1).
Рис.1
Розпочнемо із створення скалярних користувацьких функцій. Для створення нової скалярної користувацької функції в оглядачі об'єктів клікніть правою кнопкою миші по теці «Функції» і в меню, що з'явилося, виберіть пункт «Створити → Скалярна функція». З'явиться вікно нової скалярної користувацької функції (рис.2).
Рис.2.
Синтаксис скалярної користувацької функції схожий на синтаксис збережуваної процедури. Але є і істотні відмінності (рис.2):
1. Область визначення імені функції (Scalar_Function_Name);
2. Параметри, що передаються у функцію (@param1). Визначення параметрів аналогічно визначенню параметрів в збережуваній процедурі;
3. Тип даних значення, що повертається функцією;
4. Область оголошення змінних, що використовуються у середині функції.
Оголошення змінних має наступний синтаксис:
DECLARE @<Ім'я змінної> <Тип даних>
5. Тіло самої користувацької функції, містить команди мови T - SQL;
6. Команда RETURN повертає результат виконання функції. Має наступний синтаксис:
RETURN @<Ім'я змінної з результатом>
Змінна має бути того ж типу даних, який був вказаний в пункті 3.
Приклад виконання лабораторної роботи
Створимо скалярну користувацьку функцію, що обчислює середнє трьох величин. У вікні нової користувацької функції наберіть код представлений на рис.3.
Рис.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).
Рис.4
У нижній частині вікна з кодом з'явиться результат виконання нової скалярної користувацької функції.
Тепер перейдемо до створення табличних користувацьких функцій. Для створення табличної користувацької функції в оглядачі об'єктів клікніть правою кнопкою миші по теці «Функції» і в меню, що з'явилося, виберіть пункт «Створити → Вбудована функція, що повертає табличне значення…». З'явиться вікно нової табличної користувацької функції (рис.5).
Рис. 5
Таблична користувацька функція складається з наступних розділів:
1. Область визначення імені функції (Inline_Function_Name);
2. Параметри, що передаються у функцію (@param1, @param2);
3. RETURNS TABLE показує, що функція повертає таблицю;
4. Тіло самої користувацької функції складається з команди SELECT мови T - SQL.
Розглянемо створення табличної користувацької функції, що вибирає інформацію про художників по країні їх проживання. У вікні нової користувацької функції (рис. 5) наберемо наступний код (рис.6) :
Рис.6.
З коду функції видно, що вона приймає один параметр типу varchar (назва країни) і реалізується запитом:
З таблиці «Authors» вибираються прізвища, імена, по батькові, дати народження і смерті художників, при цьому таблиця «Authors» з'єднується (INNER JOIN) з таблицею «Countries» по стовпцю «CountryCode». Умовою з'єднання є рівність утримуваного поля «CountryCode» параметру, що передається у функцію.
Результат виконання цієї функції приведений на рис. 7.
Рис.7
Робота з табличною функцією здійснюється так само, як і із звичайною таблицею.
Завдання
Створити функції у відповідності із завданням для свого варіанту.