Материал: 12БД

12. Лекція:

Тема: Функції користувача та вбудовані функції:

Мета: Дати визначення функцій користувача, навести приклади їх створення і використання. Розглянути різні типи функцій. Проаналізувати вбудовані функції язика SQL.

ПЛАН

1 Поняття функції користувача

2 Функції Scalar

3 Функції Inline

4 Функції Multi-statement

5 Вбудовані функції

Математичні функції

Рядкові функції

Функції для роботи з датою і часом

1 Поняття функції користувача

При реалізації на язиці SQL складних алгоритмів, які можуть бути потрібні більше одного разу, відразу постає питання про збереження розробленого коду для подальшого вживання. Цю задачу можна б було реалізувати за допомогою збережених процедур, проте їх архітектура не дозволяє використовувати процедури безпосередньо у виразах, оскільки вони вимагають проміжного привласнення поверненого значення змінної, яка потім і указується у виразі. Природно, подібний метод вживання програмного коду не дуже зручний. Багато розробників вже давно хотіли мати нагоду виклику розроблених алгоритмів безпосередньо у виразах.

Можливість створення призначених для користувача функцій була надана в середовищі MS SQL Server 2000. В інших реалізаціях SQL у розпорядженні користувача є тільки вбудовані функції, які забезпечують виконання найпоширеніших алгоритмів: пошук максимального або мінімального значення і ін.

Функції користувача є самостійними об'єктами бази даних, такі, наприклад, як хзбережені процедури або тригери. Функція користувача розташовується в певній базі даних і доступна тільки в її контексті.

В SQL Server є наступні класи функцій користувача:

• Scalar – функції повертають звичайне скалярне значення, кожна може включати безліч команд, об'єднуваних в один блок за допомогою конструкції BEGIN...END;

• Inline – функції містять всього одну команду SELECT і повертають користувачу набір даних у вигляді значення типу даних TABLE ;

• Multi-statement – функції також повертають користувачу значення типу даних TABLE, що містить набір даних, проте в тілі функції знаходиться безліч команд SQL (INSERT, UPDATE і т.д.). Саме з їх допомогою і формується набір даних, який повинен бути повернений після виконання функції .

Призначені для користувача функції схожі з береженими процедурами, але, на відміну від них, можуть застосовуватися в запитах так само, як і системні вбудовані функції. Призначені для користувача функції, що повертають таблиці, можуть стати альтернативою переглядам. Перегляди обмежені одним виразом SELECT, а призначені для користувача функції здатні включати додаткові вирази, що дозволяє створювати складніші і могутні конструкції.

2 Функції Scalar

Створення і зміна функції даного типу виконується за допомогою команди:

<визначення_скаляр_функції>::=

{CREATE | ALTER } FUNCTION [власник.] ім’я_функції

( [ { @ім’я_параметра скаляр_тип_даних

[=default]}[,...n]])

RETURNS скаляр_тип_даних

[WITH {ENCRYPTION | SCHEMABINDING} [,...n] ]

[AS]

BEGIN

<тіло_функції>

RETURN скаляр_вираз

END

Розглянемо призначення параметрів команди.

Функція може містити один або декілька вхідних параметрів або не містити жодного. Кожний параметр повинен мати унікальне в межах створюваної функції ім'я і починатися з символу " @ ". Після імені указується тип даних параметра. Додатково можна вказати значення, яке автоматично привласнюватиметься параметру (DEFAULT), якщо користувач явно не вказав значення відповідного параметра при виклику функції.

За допомогою конструкції RETURNS скаляр_тип_даних вказується, який тип даних матиме значення, що повертається функцією.

Додаткові параметри, з якими повинна бути створена функція, можуть бути вказані за допомогою ключового слова WITH. Завдяки ключовому слову ENCRYPTION код команди, що використовується для створення функції, буде зашифрований, і ніхто не зможе проглянути його. Ця можливість дозволяє приховати логіку роботи функції. Крім того, в тілі функції може виконуватися звернення до різних об'єктів бази даних, а тому зміна або видалення відповідних об'єктів може привести до порушення роботи функції. Щоб уникнути цього, вимагається заборонити внесення змін, вказавши при створенні цієї функції ключове слово SCHEMABINDING.

Між ключовими словами BEGIN...END указується набір команд, вони і будуть тілом функції.

Коли в ході виконання коду функції зустрічається ключове слово RETURN, виконання функції завершується і як результат її обчислення повертається значення, вказане безпосередньо після слова RETURN. Відзначимо, що в тілі функції дозволяється використовування безлічі команд RETURN, які можуть повертати різні значення. Як значення, що повертається, допускаються як звичайні константи, так і складні вирази. Єдина умова – тип даних значення, що повертається, повинен співпадати з типом даних, вказаним після ключового слова RETURNS.

Приклад 12.1. Створити і застосувати функцію скалярного типу для обчислення сумарної кількості товару, що поступив за певну дату. Власник функції – користувач з ім'ям user1.

CREATE FUNCTION user1.sales(@data DATETIME)

RETURNS INT

AS

BEGIN

DECLARE @c INT

SET @c=(SELECT SUM(кількість)

FROM Операція

WHERE дата=@data)

RETURN (@c)

END

Приклад 12.1. Створення функції скалярного типу для обчислення сумарної кількості товару, що поступив за певну дату.

Як вхідний параметр використовується дата. Функція повертає значення цілого типу, одержане з оператора SELECT шляхом підсумовування кількості товару з таблиці Операція. Умовою відбору записів для підсумовування є рівність дати операції значенню вхідного параметра функції.

Проілюструємо звернення до функції користувача: визначимо кількість товару, що поступив за 02.11.01:

DECLARE @kol INT

SET @kol=user1.sales ('02.11.01')

SELECT @kol

3 Функції Inline

Створення і зміна функції цього типу виконується за допомогою команди:

<визначення_табл_функції>::=

{CREATE | ALTER } FUNCTION [власник.]

ім’я_функції

( [ { @ім’я_параметра скаляр_тип_даних [=default]}[,...n]])

RETURNS TABLE

[ WITH {ENCRYPTION | SCHEMABINDING} [,...n] ]

[AS]

RETURN [(] SELECT_оператор [)]

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

Після ключового слова RETURNS завжди повинне указуватися ключове слово TABLE. Таким чином, функція даного типу повинна строго повертати значення типу даних TABLE. Структура значення типу TABLE, що повертається, не указується явно при описі власне типу даних. Натомість сервер автоматично використовуватиме для значення, що повертається, TABLE структуру, що повертається запитом SELECT, який є єдиною командою функції.

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

Значення типа TABLE, що повертається функцією, може бути використано безпосередньо в запиті, тобто в розділі FROM.

Приклад 12.2. Створити і застосувати функцію табличного типу для визначення двох найменувань товару з найбільшим залишком.

CREATE FUNCTION user1.itog()

RETURNS TABLE

AS

RETURN (SELECT TOP 2 Товар.Назва

FROM Товар INNER JOIN Склад ON Товар.КодТовара=Склад.КодТовара

ORDER Склад.Залишок DESC)

Приклад 12.2. Створення функції табличного типу для визначення двох найменувань товару з найбільшим залишком.

Використовувати функцію для отримання двох найменувань товару з найбільшим залишком можна таким чином:

SELECT Назва

FROM user1.itog()

4 Функції Multi-statement

Створення і зміна функцій типа Multi-statement виконується за допомогою наступної команди:

<визначення_мульти_функції>::=

{CREATE | ALTER }FUNCTION [власник.]

ім’я_функції ( [ { @ім’я_параметра скаляр_тип_даних [=default]}[,...n]])

RETURNS @ім’я_параметра TABLE <визначення_таблиці>

[WITH {ENCRYPTION | SCHEMABINDING} [,...n] ]

[AS]

BEGIN

<тіло_функції>

RETURN

END

Використовування більшої частини параметрів розглядалося при описі попередніх функцій.

Відзначимо, що функції даного типу, як і табличні, повертають значення типу TABLE. Проте, на відміну від табличних функцій, при створенні функцій Multi-statement необхідно явно задати структуру значення, що повертається. Вона указується безпосередньо після ключового слова TABLE і, таким чином, є частиною визначення типу даних, що повертається. Синтаксис конструкції <визначення_таблиці> повністю відповідає однойменним структурам, що використовуються при створенні звичайних таблиць за допомогою команди CREATE TABLE.

Набір даних, що повертаються, повинен формуватися за допомогою команд INSERT, виконуваних в тілі функції. Крім того, в тілі функції допускається використовування різних конструкцій язика SQL, які можуть контролювати значення, розміщувані у вихідному наборі рядків. При роботі з командою INSERT вимагається явно вказати ім'я того об'єкту, куди необхідно вставити рядки. Тому у функціях типа Multi-statement, на відміну від табличних, необхідно привласнити якесь ім'я об'єкту з типом даних TABLE – воно і указується як значення, що повертається.

Завершення роботи функції відбувається в двох випадках: якщо виникають помилки виконання і якщо з'являється ключове слово RETURN. На відміну від функцій скалярного типу, при використовуванні команди RETURN не потрібно указувати значення, що повертається. Сервер автоматично поверне набір даних типа TABLE, ім'я і структура якого була вказана після ключового слова RETURNS. В тілі функції може бути вказане більше однієї команди RETURN.

Необхідно відзначити, що робота функції завершується тільки за наявності команди RETURN. Це твердження вірне і у тому випадку, коли йдеться про досягнення кінця тіла функції – самою останньою командою винна бути команда RETURN.

Приклад 12.3. Створити і застосувати функцію (типу multi-statement), яка для деякого співробітника виводить список всіх його підлеглих (підлеглих як безпосередньо йому, так і опосередковано через інших співробітників).

Список співробітників з вказівкою кожного керівника представлений в таблиці emp_mgr з наступною структурою:

CREATE TABLE emp_mgr

(emp CHAR(2) PRIMARY KEY -- співробітник

mgr CHAR(2)) -- керівник

Приклад даних в таблиці emp_mgr показаний нижче. Для спрощення ілюстрації імена співробітників і їх начальників представлені буквами латинського алфавіту. У директора організації начальника немає (NULL).

emp mgr

а NULL

b а

с а

d а

e f

f b

g b

i с

к d

CREATE FUNCTION fn_findReports(@id_emp CHAR(2))

RETURNS @report TABLE(empid CHAR(2) PRIMARY KEY mgrid CHAR(2))

AS

BEGIN

DECLARE @r INT

DECLARE @t TABLE(empid CHAR(2)

PRIMARY KEY mgrid CHAR(2)

pr INT DEFAULT 0)

INSERT @t SELECT emp,mgr,0

FROM emp_mgr

WHERE emp=@id_emp

SET @r=@@ROWCOUNT

WHILE @r>0

BEGIN

UPDATE @t SET pr=1 WHERE pr=0

INSERT @t SELECT e.emp, e.mgr,0

FROM emp_mgr e @t t

WHERE e.mgr=t.empid

AND t.pr=1

SET @r=@@ROWCOUNT

UPDATE @t SET pr=2 WHERE pr=1

END

INSERT @report SELECT empid, mgrid

FROM @t

RETURN

END

Приклад 11.3. Створення функції, яка для деякого співробітника виводить список всіх його підлеглих.

Застосуємо створену функцію для визначення списку підлеглих співробітника ‘b’:

SELECT * FROM fn_findReports('b')

Оператор повертає наступні значення:

emp mgr

b а

e f

f b

g b

Список підлеглих співробітника ‘a’ створюється за допомогою оператора

SELECT * FROM fn_findReports('a')

emp mgr

а NULL

b а

с а

d а

e f

f b

g b

i с

к d

Інший оператор формує список підлеглих співробітника ‘e’:

SELECT * FROM fn_findReports('e')

emp mgr

e f

Список підлеглих співробітника ‘c’ створює наступний оператор:

SELECT * FROM fn_findReports('c')

emp mgr

с а

i с

Видалення будь-якої функції здійснюється командою:

DROP FUNCTION {[ власник.] ім’я_функції } [,...n]

5 Вбудовані функції

Вбудовані функції, що є у розпорядженні користувачів при роботі з SQL, можна умовно розділити на наступні групи:

• математичні функції ;

• рядкові функції ;

• функції для роботи з датою і часом ;

• функції конфігурування;

• функції системи безпеки;

• функції управління метаданими;

• статистичні функції.

Математичні функції

Короткий огляд математичних функцій представлений в таблиці.

Таблиця 12.1.