З SQL Server ви можете створювати ваші власні функції, що додають і розширюють функції, що надаються системою. Функції можуть отримувати 0 або більше параметрів і повертати скалярний значення або таблицю. Вхідні параметри можуть бути будь-якого типу, за винятком timestamp, cursor, table.
Сервер SQL підтримує три типи функцій певних користувачем:
1. Створення збереженої функції
Функція, що повертає таблицю:
2. Скалярні функції в Transact-SQL
Отже, подивимося спочатку на код створення скалярної функції:
Далі вказується ключове слово RETURNS, за яким йде опис типу значення, що повертається. Для скалярної функції е ?? то можуть бути будь-які типи (рядки, числа, дати і т.д.).
Між ключовими словами BEGIN і END у нас виконується наступний код:
У першому рядку оголошується змінна @Summ. Вона потрібна для зберігання проміжного результату розрахунків. Далі виконується запит SELECT, в якому відбувається пошук рядка за датою і назвою товару в таблиці товарів. У знайденій рядку перемножуються поля ціни і кількості, і результат записується в змінну @Summ.
Зверніть увагу, що в кінці запиту стоїть знак крапки з комою. Кожен запит повинен закінчуватися е ?? тім символом, але в більшості прикладів ми е ?? тім нехтували, але в функції відсутність символу ";" може привести до помилки.
В останньому рядку повертаємо результат. Для е ?? того потрібно написати ключове слово RETURN, після якого пишеться повертається значення або змінна. В даному випадку, повертатися буде вміст змінної @Summ.
Так як функція скалярная, то і повертається значення повинно бути скалярним і при е ?? тому відповідати типу, описаного після ключового слова RETURNS.
3. Використання функцій
Як виконати таку функцію? Так само, як і багато інших системні функції (наприклад, GETDATE ()). е ?? апример, наступний приклад використовує функцію в операторі SELECT:
Виконайте наступний запит і переконайтеся, що він повернув той же результат, що і створена нами функція:
Функції можна використовувати не тільки в операторі SELECT, а й безпосередньо, привласнюючи значення змінної. е ?? апример:
В е ?? тому прикладі ми оголосили змінну @Summ типу numeric (10,2). Саме такий тип повертає функція. У наступному рядку змінної присвоюється результат виконання Summ, за допомогою SET.
Давайте подивимося, що станеться, якщо передати функції такі параметри, при яких запит функції поверне більше одного рядка. У нашій таблиці товарів поєднання дати і назва не дає унікальності, тому що ми її порушили. Первинного ключа в таблиці також немає, і серед товарів у мене є чотири рядки, які мають свої точні копії. е-то порушує правило унікальності рядків в реляційних базах, але дуже наочно показує, що в реальному житті порушувати його не можна.
е езультатом буде тільки одне число, хоча рядки дві. е ?? який рядок з двох повернув сервер? е ?? і хто точно сказати не може, тому що вони обидві однакові і без єдиного відмінності. Пое ?? тому сервер скоріше за все повернув першу з рядків.
4. Функція, що повертає таблицю
У наступному прикладі ми створюємо функцію, яка буде повертати в якості результату таблицю. Як приклад, створимо функцію, яка буде повертати таблицю товарів, і для кожного рядка розрахуємо твір колонок кількості і ціни:
е ?? ачало функції таке ж, як у скалярною - вказуємо оператор CREATE FUNCTION і ім'я функції. Я спеціально створив е ?? ту функцію без параметрів, щоб ви побачили, як е ?? то робиться. е ?? е дивлячись на те, що параметрів немає, після імені повинні йти круглі дужки, в яких не треба нічого писати. Якщо не вказати дужок, то сервер поверне помилку і функція не буде створена.
е азніца є і в секції RETURNS, після якої вказується тип TABLE, що говорить про необхідність повернути таблицю. Після е ?? того йде ключове слово AS і RETURN, після якого має йти повертається значення. Для функції даного типу в секції RETURN потрібно в дужках вказати запит, результат якого і буде повертатися функцією.
Коли пишете запит, то все його поля повинні містити імена. Якщо одне з полів не має імені, то результатом виконання оператора CREATE FUNCTION буде помилка. У нашому прикладі останнє поле є результатом перемноження полів "Ціна" і "Кількість", а такі поля не мають імені, поетів ?? тому ми його ставимо за допомогою ключового слова AS.
Подивимося, як можна використовувати таку функцію за допомогою оператора SELECT:
Так як ми використовуємо простий оператор SELECT, то ми можемо і обмежувати висновок певними рядками, за допомогою обмежень в секції WHERE. е ?? апример, в наступному прикладі вибираємо з результату функції тільки ті рядки, в яких поле "Кількість" містить значення 1:
Функція повертає в якості результату таблиці, яку ви можете використовувати як будь-яку іншу таблицю бази даних. Давайте створимо приклад в якому можна буде побачити використання функції згідно з таблицями. Для початку створимо функцію, яка буде повертати ідентифікатор працівників таблиці tbPeoples і об'єднані в одне поле ПІБ:
е ассмотрім приклад, функція GetPeoples у нас повертає всі рядки таблиці. Щоб отримати тільки потрібне прізвище, потрібно писати запит типу:
В е ?? тому випадку будуть виконуватися два запити: е ?? той і ще один всередині функції. е ?? про якщо передавати прізвище в якості параметра в функцію і там зробити секцію WHERE, то можна обійтися і одним запитом SELECT:
5. Многооператорная функція повертає таблицю
Всі функції, створені в розділі 3.3.5 можуть повертати таблицю, що згенерувала тільки одним оператором SQL. е ?? як же тоді зробити можливість виконувати кілька операцій? е ?? апример, ви можете захотіти виконувати додаткові перевірки вхідних параметрів для забезпечення безпеки. Перевірки зайвими не буває, особливо вхідних даних і особливо, якщо е ?? ти вхідні дані вказуються користувачем.
Наступний приклад показує, як створити функцію, яка може повернути в якості результату таблицю, і при е ?? тому, в тілі функції можуть виконуватися кілька операторів:
Ось тут є одна відмінність від скалярних функцій - після RETURN ми вказували ім'я змінної, значення якої повинно стати результатом. В даному випадку нічого вказувати не треба. Ми вже оголосили змінну в секції RETURNS і описали формат е ?? тієї змінної. У тілі функції ми можемо і повинні наповнити е ?? ту змінну значеннями і саме е ?? то потрапить в результат.
Тепер подивимося на приклад створення функції:
В даному прикладі в якості результату оголошена змінна @ret, яка є таблицею з двох полів "idPeoples" типу int і "vcFIO" типу varchar довгою в 50 символів. У тілі функції в е ?? ту таблицю записуються значення з таблиці tbPeoples і виконується оператор RETURN, завершальний виконання функції.
У використанні, така функція нічим не відрізняється від розглянутих раніше. е ?? апример, наступний запит вибирає всі дані, які повертає функція:
6. Опції функцій
При створенні функцій можуть використовуватися такі опції SCHEMABINDING (прив'язати до схеми) і / або ENCRYPTION (шифрувати текст функції). Якщо друга опція нам вже відома по в'юшки і процедурам (дозволяє шифрувати вихідний код функції в системних таблицях), то друга зустрічається вперше, але при е ?? тому надає зручний засіб захисту даних.
Якщо функція створена з опцією SCHEMABINDING, то об'єкти бази даних, на які посилається функція, не можуть бути змінені (з використанням оператора ALTER) або видалені (за допомогою оператора DROP). е ?? апример, наступна функція використовує таблицю tbPeoples і при е ?? тому використовується опція SCHEMABINDING:
Функція може бути пов'язаною зі схемою, тільки якщо такі обмеження істини:
Створіть функцію і спробуйте після е ?? того видалити таблицю tbPeoples.
У відповідь на е ?? то сервер видасть повідомлення з помилкою про те, що об'єкт не може бути видалений, через присутність зовнішнього ключа. Навіть якщо позбутися від ключа, видалення буде неможливо, тому що на таблицю посилається функція, прив'язана до схеми.
Щоб побачити повідомлення без видалення ключа, давайте додамо до таблиці колонку, а потім спробуємо її видалити:
Створення пройде успішно, а ось під час видалення станеться помилка, з повідомленням про те, що існує обмеження, яке залежить від колонки. Ми ж не створювали жодних обмежень, а просто додали колонку і спробували її видалити. Обмеження вже давно існує, але не на окрему колонку, а на все колонки таблиці і е ?? то обмеження створено функцією GetPeoples2, яка пов'язана зі схемою.
7. Зміна функцій
Ви можете змінювати функцію за допомогою оператора ALTER FUNCTION. Загальний вигляд для кожного варіанта функції відрізняється. Давайте розглянемо кожен з них.
1. Загальний вигляд команди зміни скалярної функції:
2. Загальний вигляд зміни функції, що повертає таблицю:
3. Загальний вигляд команди зміни функції з безліччю операторів, що повертає таблицю.
Наступний приклад показує спрощений варіант команди, що змінює функцію: