У мовах програмування зазвичай є два типи підпрограм:
визначені користувачем функції (UDF).
Як вже було розглянуто в попередній статті, збережені процедури складаються з декількох інструкцій і мають від нуля до декількох вхідних параметрів, але зазвичай не повертають ніяких параметрів. На відміну від збережених процедур, функції завжди повертають одне значення. У цьому розділі ми розглянемо створення і використання визначаються користувачем функцій (User Defined Functions - UDF).
Створення та виконання визначених користувачем функцій
Визначені користувачем функції створюються за допомогою інструкції CREATE FUNCTION. яка має наступний синтаксис:
Параметр schema_name визначає ім'я схеми, яка призначається власником створюваної UDF, а параметр function_name визначає ім'я цієї функції. Параметр @param є вхідним параметром функції (формальним аргументом), чий тип даних визначається параметром type. Параметри функції - це значення, які передаються викликає об'єктом настройки користувача функції для використання в ній. Параметр default визначає значення за замовчуванням для відповідного параметра функції. (Заводське також може бути NULL.)
Пропозиція RETURNS визначає тип даних значення, що повертається UDF. Це може бути майже будь-який стандартний тип даних, підтримуваний системою баз даних, включаючи тип даних TABLE. Єдиним типом даних, який не можна вказувати, є тип даних timestamp.
Визначені користувачем функції можуть бути або скалярними, або табличними. Скалярні функції повертають атомарному (скалярний) значення. Це означає, що в реченні RETURNS скалярної функції вказується один зі стандартних типів даних. Функція є табличній, якщо пропозиція RETURNS повертає набір рядків.
Альтернативна пропозиція WITH SCHEMABINDING прив'язує UDF до об'єктів бази даних, до яких ця функція звертається. Після цього будь-яка спроба модифікувати об'єкт бази даних, до якого звертається функція, зазнає невдачі. (Прив'язка функції до об'єктів бази даних, до яких вона звертається, видаляється лише при зміні функції, після чого параметр SCHEMABINDING більше не заданий.)
Для того щоб під час створення функції використовувати пропозицію SCHEMABINDING, об'єкти бази даних, до яких звертається функція, повинні відповідати таким вимогам:
всі вистави та інші UDF, до яких звертається визначається функція, повинні бути прив'язані до схеми;
всі об'єкти бази даних (таблиці, уявлення і UDF) повинні бути в тій же самій базі даних, що і визначається функція.
Параметр block визначає блок BEGIN / END, що містить реалізацію функції. Останньою інструкцією блоку повинна бути інструкція RETURN з аргументом. (Значним аргументу є повертається функцією значення.) Всередині блоку BEGIN / END дозволяються тільки такі інструкції:
інструкції присвоєння, такі як SET;
інструкції для керування ходом виконання, такі як WHILE і IF;
інструкції DECLARE, що оголошують локальні змінні;
інструкції SELECT, що містять списки стовпців вибірки з виразами, значення яких присвоюються змінним, які є локальними для даної функції;
інструкції INSERT, UPDATE і DELETE, які змінюють змінні з типом даних TABLE, які є локальними для даної функції.
За замовчуванням інструкцію CREATE FUNCTION можуть використовувати тільки члени зумовленої ролі сервера sysadmin і визначеною ролі бази даних db_owner або db_ddladmin. Але члени цих ролей можуть привласнити це право іншим користувачам за допомогою інструкції GRANT CREATE FUNCTION.
У прикладі нижче показано створення функції ComputeCosts:
Функція ComputeCosts обчислює додаткові витрати, що виникають при збільшенні бюджетів проектів. Єдиний вхідний параметр, @percent, визначає процентне значення збільшення бюджетів. У блоці BEGIN / END спочатку оголошуються дві локальні змінні: @addCosts і @sumBudget, а потім за допомогою інструкції SELECT змінної @sumBudget присвоюється загальна сума всіх бюджетів. Після цього функція обчислює загальні додаткові витрати і за допомогою інструкції RETURN повертає це значення.
Виклик настройки користувача функції
Певну користувачем функцію можна викликати за допомогою інструкцій Transact-SQL, таких як SELECT, INSERT, UPDATE або DELETE. Виклик функції здійснюється, вказуючи її ім'я з парою круглих дужок в кінці, в яких можна задати один або кілька аргументів. Аргументи - це значення або вирази, які передаються вхідних параметрів, що визначаються відразу ж після імені функції. При виконанні функції, коли для її параметрів не визначені значення за замовчуванням, для всіх цих параметрів необхідно надати аргументи на тому ж самому порядку, в якому ці параметри визначені в інструкції CREATE FUNCTION.
У прикладі нижче показаний виклик функції ComputeCosts в інструкції SELECT:
Інструкція SELECT в прикладі відображає назви і номери всіх проектів, бюджети яких менше, ніж загальні додаткові витрати по всіх проектах при заданому значенні процентного збільшення.
В інструкціях Transact-SQL імена функцій необхідно ставити, використовуючи імена, що складаються з двох частин: schema name і function name, тому в прикладі ми використовували префікс схеми dbo.
Ті, що повертають табличне значення функції
Як уже згадувалося раніше, функція є повертає табличне значення, якщо її пропозицію RETURNS повертає набір рядків. Залежно від того, яким чином визначено тіло функції, які повертають табличне значення функції класифікуються як вбудовані (inline) і многоінструкціонние (multistatement). Якщо в реченні RETURNS ключове слово TABLE вказується без супроводжуючого списку стовпців, така функція є вбудованої. Інструкція SELECT вбудовується функції повертає результуючий набір у вигляді змінної з типом даних TABLE.
Многоінструкціонная повертає табличне значення функція містить ім'я, що визначає внутрішню змінну з типом даних TABLE. Цей тип даних вказується ключовим словом TABLE. яке слід за ім'ям змінної. У цю змінну вставляються вибрані рядки, і вона служить повертається значенням функції.
Створення повертає табличне значення функції показано в прикладі нижче:
Функція EmployeesInProject відображає імена всіх співробітників, що працюють над певним проектом, номер якого задається вхідним параметром @projectNumber. Тоді як функція в загальному випадку повертає набір рядків, пропозиція RETURNS в визначення даної функції містить ключове слово TABLE, яке вказує, що функція повертає табличне значення. (Зверніть увагу на те, що в прикладі блок BEGIN / END необхідно опустити, а пропозиція RETURN містить інструкцію SELECT.)
Використання функції Employees_in_Project приведено в прикладі нижче:
Ті, що повертають табличне значення функції і інструкція APPLY
Реляційна інструкція APPLY дозволяє викликати повертає табличне значення функцію для кожного рядка табличного вираження. Ця інструкція задається в реченні FROM відповідної інструкції SELECT таким же чином, як і інструкція JOIN. Інструкція APPLY може бути об'єднана з табличній функцією для отримання результату, схожого на результуючий набір операції з'єднання двох таблиць. Існує дві форми інструкції APPLY:
Інструкція CROSS APPLY повертає ті рядки з внутрішнього (лівого) табличного вираження, які збігаються із зовнішнім (правим) табличним виразом. Таким чином, логічно, інструкція CROSS APPLY функціонує так само, як і інструкція INNER JOIN.
Інструкція OUTER APPLY повертає всі рядки з внутрішнього (лівого) табличного вираження. (Для тих рядків, для яких немає збігів в зовнішньому табличному вираженні, він містить значення NULL в стовпці зовнішнього табличного вираження.) Логічно, інструкція OUTER APPLY еквівалентна інструкції LEFT OUTER JOIN.
Застосування інструкції APPLY показано в прикладах нижче:
Функція GetJob () повертає набір рядків з таблиці Works_on. У прикладі нижче цей результуючий набір "з'єднується" пропозицією APPLY з вмістом таблиці Employee:
Результатом виконання цих двох функцій будуть наступні дві таблиці (відображаються після виконання другої функції):
У першому запиті прикладу результуючий набір табличній функції GetJob () "з'єднується" з вмістом таблиці Employee за допомогою інструкції CROSS APPLY. Функція GetJob () грає роль правого введення, а таблиця Employee - лівого. Вираз правого введення обчислюється для кожного рядка лівого введення, а отримані рядки комбінуються, створюючи кінцевий результат.
Другий запит схожий на перший (але в ньому використовується інструкція OUTER APPLY), який логічно відповідає операції зовнішнього з'єднання двох таблиць.
Ті, що повертають табличне значення параметри
Використання повертає табличне значення параметра показано в прикладі нижче:
У цьому прикладі спочатку визначається табличний тип departmentType. Це означає, що даний тип є типом даних TABLE, внаслідок чого він дозволяє вставку рядків. У процедурі InsertProc оголошується змінна @Moscow з типом даних departmentType. (Пропозиція READONLY вказує, що вміст цієї таблиці можна змінювати.) У подальшому пакеті в цю табличну змінну вставляються дані, після чого процедура запускається на виконання. В процесі виконання процедура вставляє рядки з табличній змінної в тимчасову таблицю #moscowTable. Вставлене вміст тимчасової таблиці виглядає наступним чином:
Використання повертають табличне значення параметрів надає наступні переваги:
спрощується модель програмування підпрограм;
зменшується кількість звернень до сервера і отримань відповідних відповідей;
таблиця результату може мати довільну кількість рядків.
Зміна структури визначаються користувачами інструкцій
Мова Transact-SQL також підтримує інструкцію ALTER FUNCTION. яка модифікує структуру визначаються користувачами інструкцій (UDF). Ця інструкція зазвичай використовується для видалення прив'язки функції до схеми. Всі параметри інструкції ALTER FUNCTION мають таке ж значення, як і однойменні параметри інструкції CREATE FUNCTION.
Для видалення UDF застосовується інструкція DROP FUNCTION. Видалити функцію може тільки її власник або член зумовленої ролі db_owner або sysadmin.
Визначені користувачем функції і середовище CLR
У попередній статті ми розглянули спосіб створення збережених процедур з керованого коду середовища CLR на мові C #. Цей підхід можна використовувати і для визначених користувачем функцій (UDF), з одним тільки відмінністю, що для збереження UDF у вигляді об'єкта бази даних використовується інструкція CREATE FUNCTION, а не CREATE PROCEDURE. Крім цього, визначені користувачем функції також застосовуються в іншому контексті, ніж збережені процедури, оскільки UDF завжди повертають значення.
У прикладі нижче показаний вихідний код обумовлених користувачем функцій (UDF), реалізований на мові C #:
У вихідному коді визначаються користувачем функцій в прикладі обчислюється новий бюджет проекту, збільшуючи старий бюджет на певну кількість відсотків. Ви можете використовувати інструкцію CREATE ASSEMBLY для створення збірки CLR в базі даних, як це було показано раніше. Якщо ви опрацьовували приклади з попередньої статті і вже додали збірку CLRStoredProcedures в базу даних, то ви можете оновити цю збірку, після її перекомпіляції з новим класом (CLRStoredProcedures це ім'я мого проекту класів C #, в якому я додавав визначення процедур і функцій, у вас збірка може називатися інакше):
Інструкція CREATE FUNCTION в прикладі нижче зберігає метод ComputeBudget у вигляді об'єкта бази даних, який в подальшому можна використовувати в інструкціях для маніпулювання даними.
Використання однієї з таких інструкцій, інструкції SELECT, показано в прикладі нижче:
Яка визначається користувачем функцію можна помістити в різних місцях інструкції SELECT. У прикладах вище вона викликалася в пропозиціях WHERE, FROM і в списку вибору оператора SELECT.