9.3. Функції мовиTransact-sql

Усі функції SQL Server діляться на: строго детерміновані, детерміновані і недетерміновані.

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

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

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

У цьому розумінні функція, яка не є строго детермінованою, може бути детермінованою, якщо звертається до даних.

Визначення 7.5. Недетермінована функція може повернути різні результати, якщо її кілька разів підряд викликати з одними і тими ж вхідними значеннями.

Наприклад, функція GETDATE() є недетермінованою. SQL Server накладає обмеження на різні класи детермінізму. Таким чином, недетерміновані функції слід використовувати обережно.

Для вбудованих функцій поняття "детермінований" і "строго детермінований" означає одне і те ж. Система перевіряє визначення призначених для користувача функцій Transact - SQL і не дозволяє створювати недетерміновані функції. Проте, функції, які звертаються до даних або не прив'язані до схеми, вважаються не строго детермінованими. Визначення функцій середовища CLR містить властивості детермінізму, доступу до даних і доступу до системних даних, але оскільки ці властивості не перевіряються системою, функції завжди вважаються не строго детермінованими.

Сфера застосування недетермінованих функцій обмежена. У індексованих представленнях, індексованих обчислюваних стовпцях, матеріалізованих обчислюваних стовпцях і визначеннях призначених для користувача функцій Transact - SQL можна викликати тільки детерміновані функції.

Якщо функція не є строго детермінованою, її продуктивність може не підлягати оптимізації. Для збереження коректності деякі кроки реорганізації плану виконання пропускаються. Крім того, кількість, порядок і час між викликами призначених для користувача функцій залежать від реалізації. Окрім постійних недетермінованих функцій RAND і сімейства GETDATE, кількість, порядок і час між викликами залежатимуть від вибраного плану.

SQL Server 2008 містить безліч вбудованих функцій, а також підтримує створення визначуваних користувачем функцій. Категорії вбудованих функцій приведені в таблиці 7.9.

Таблиця 7.9 - Категорії функцій SQL Server 2008

Категорія функції	Опис
Статистичні функції	Виконують агрегацію даних. Прикладами таких функцій є COUNT, SUM, MIN і MAX.
Функції налаштування	Скалярні функції, що повертають відомості про параметри конфігурації.
Криптографічні функції	Підтримують шифрування, дешифрування, цифрові підписи і їх перевірку.
Функції для роботи з курсорами	Повертають відомості про стан курсора.
Функції дати і часу	Змінюють значення дати і часу.
Математичні функції	Служать для виконання тригонометричних, геометричних і інших математичних операцій.
Функції метаданих	Повертають зведення про атрибути баз даних і об'єктів баз даних.
Функції, що ранжирують	Недетерміновані функції, що повертають значення, що ранжирує, для кожного рядка секції.
Функції наборів рядків	Повертають результуючі набори, які можна використовувати замість посилань на таблицю в інструкції Transact, - SQL.
Функції безпеки	Повертають відомості про користувачів і ролі.
Строкові функції	Змінюють значення char, varchar, nchar, nvarchar, binary і varbinary.
Системні функції	Працюють або повертають звіт про різні параметри і об'єкти рівня системи.
Системні статистичні функції	Повертають відомості про продуктивність SQL Server.
Функції обробки тексту і зображень	Змінюють значення text і image.

Усі математичні функції, окрім RAND, є детермінованими. Це означає, що вони повертають одні і ті ж результати кожного разу, коли викликаються з одними і тими ж вхідними значеннями. Функція RAND є детермінованою тільки у тому випадку, якщо заданий параметр початкового значення. Деякі математичні функції приведені в таблиці 7.10.

Таблиця 7.10 - Математичні функції

Функція	Повертане значення
ABS(n)	Абсолютне значення величини n
CEILING(n)	Найменше ціле, більше або рівне n
COS(n)	Косинус кута n, вираженого в радіанах
ЕХР(n)	e в мірі n
FLOOR(n)	Найбільше ціле, менше або рапное n
LOG(n)	Натуральний логарифм n, де n >0
LOG10(n)	Десятковий логарифм n, де n >0
POWER(w, n)	w в мірі n
ROUND(n[, m])	n, закруглене до m позицій після десяткової точки (за умовчанням m дорівнює нулю)
SIGN(n)	-- 1 (якщо n < 0); 0 (0 (якщо n = 0); 1 (якщо n > 0)
SIN(n)	Синус n (кута, вираженого в радіанах)
SQRT(n)	Квадратний корінь від n. Якщо n < 0, повертає значення NULL
TAN(n)	Тангенс кута n, вираженого в радіанах

Арифметичні функції, такі як ABS, CEILING, DEGREES, FLOOR, POWER, RADIANS і SIGN, повертають значення того ж типу, що і вхідне значення. Тригонометричні і інші функції, включаючи EXP, LOG, LOG10, SQUARE і SQRT, перетворять вхідні значення в тип float і повертають значення типу float.

Приклад 7.17. Наступний код повертає квадратний корінь чисел від 1.00 до 10.00:

DECLARE @myvalue float;

SET @myvalue = 1.00;

WHILE @myvalue < 10.00

BEGIN

SELECT SQRT(@myvalue);

SET @myvalue = @myvalue + 1

END;

GO

При цьому повертається наступний набір даних :

------------------------

1.0

------------------------

1.4142135623731

------------------------

1.73205080756888

------------------------

2.0

------------------------

2.23606797749979

------------------------

2.44948974278318

------------------------

2.64575131106459

------------------------

2.82842712474619

------------------------

3.0

Приклад 7.18. Застосування функції CEILING з позитивними, негативними і нульовими значеннями:

SELECT CEILING($123.45), CEILING($- 123.45), CEILING($0.0)

GO

При цьому повертається наступний набір даних :

--------- --------- -------------------------

124.00 -123.00 0.00

((1 row(s) affected)

Приклад 7.19. Застосування функції SIGN для повернення значення знаку для чисел від - 1 до 1:

DECLARE @value real

SET @value = - 1

WHILE @value < 2

BEGIN

SELECT SIGN(@value)

SET NOCOUNT ON

SELECT @value = @value + 1

SET NOCOUNT OFF

END

SET NOCOUNT OFF

GO

При цьому повертається наступний набір даних :

((1 row(s) affected)

------------------------

-1.0

((1 row(s) affected)

------------------------

0.0

((1 row(s) affected)

------------------------

1.0

((1 row(s) affected)

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

Таблиця 7.11 - Сроковые функції

Функція	Повертане значення
Символьні функції, що повертають символьні значення
CHAR(n)	Перетворить код ANCII n в cимвол. Повертає NULL, якщо число лежить поза діапазоном від 0 до 255.
LOWER(char)	Символьний рядок char, усі букви якої перетворені в рядкові (переведені в нижній регістр).
UPPER(char)	Символьний рядок char, в якій усі букви перетворені в прописні (переведені у верхній регістр).
LTRIM(char)	Символьний рядок char, в якій видалені усі пропуски на початку рядка.
RTRIM(char)	Символьний рядок char, в якій видалені усі пропуски у кінці рядка.
REPLACE(char, search_string, replacement_string)	Символьний рядок char, в якій усі фрагменти search_string замінені на replacement_string. Аргумент char може містити символьні або двійкові дані. Аргумент search_string може мати символьний або двійковий тип даних і не може бути порожнім рядком. Повертає NULL, якщо який-небудь з аргументів має значення NULL.
SUBSTRING(char, m, n)	Фрагмент символьного рядка char, що починається з символу з номером m, завдовжки n символів.
SOUNDEX(char)	Символьний рядок, що містить фонетичне представлення для char, англійською мовою
Символьні функції, що повертають числові значення
ASCII(char)	Повертає код ASCII першого символу рядка char в кодуванні, прийнятому в базі даних
CHARINDEX(char1, вираження)	Повертає позицію рядка char1 усередині рядка, заданого вираженням. Вираження, як правило, задає ім'я стовпця.
PATINDEX(char1, вираження)	Повертає позицію початкового символу першого входження заданого підрядка char1 в рядку, заданому вираженням. Вираження, як правило, задає ім'я стовпця.
LENGTH(char)	Довжина рядка char в символах

Приклад 7.20. Функції UPPER і RTRIM використовуються для отримання з таблиці Contact прізвищ усіх людей, при цьому усі символи прізвища перетворяться в символи верхнього регістра, відповідний символьний рядок обрізується і виробляється її зчеплення з рядком імені :

USE AdventureWorks;

GO

SELECT UPPER(RTRIM(LastName)) + ', ' + FirstName AS Name

FROM Person.Contact

ORDER BY LastName;

GO

Приклад 7.21. Рядок cde замінюється в рядку abcdefghi рядком xxx :

SELECT REPLACE('abcdefghicde ',' cde ',' xxx');

GO

При цьому результат виглядає таким чином:

------------

abxxxfghixxx

((1 row(s) affected)

Усі значення системної дати і часу наслідує від операційної системи комп'ютера, на якому працює екземпляр SQL Server. У таблиці 7.12 приведені функції дати і часу.

Таблиця 7.12 - Функції дати і часу

Функція	Призначення
Високоточні функції системної дати і часу
SYSDATETIME()	Повертає значення типу datetime2(7), яке містить дату і час комп'ютера, на якому запущений екземпляр SQL Server. Зміщення часового поясу не включається.
SYSDATETIMEOFFSET()	Повертає значення типу datetimeoffset(7), яке містить дату і час комп'ютера, на якому запущений екземпляр SQL Server. Зміщення часового поясу включається.
SYSUTCDATETIME()	Повертає значення типу datetime2(7), яке містить дату і час комп'ютера, на якому запущений екземпляр SQL Server. Повертана дата і час відображуються у форматі UTC.
Функції системної дати і часу меншої точності
CURRENT_TIMESTAMP	Повертає значення типу datetime2(7), яке містить дату і час комп'ютера, на якому запущений екземпляр SQL Server. Зміщення часового поясу не включається.
GETDATE()	Повертає значення типу datetime2(7), яке містить дату і час комп'ютера, на якому запущений екземпляр SQL Server. Зміщення часового поясу не включається.
GETUTCDATE()	Повертає значення типу datetime2(7), яке містить дату і час комп'ютера, на якому запущений екземпляр SQL Server. Повертана дата і час відображуються у форматі UTC.
Функції, одержуючі компоненти дати і часу
DATENAME(datepart, date)	Повертає рядок символів, що представляє вказану частину datepart заданої дати.
DATEPART(datepart, date)	Повертає ціле число, що представляє вказаний компонент datepart вказаної дати date.
DAY(date)	Повертає ціле число, що представляє день вказаної дати date.
MONTH(date)	Повертає ціле число, що представляє місяць вказаної дати date.
YEAR(date)	Повертає ціле число, що представляє рік вказаної дати date.

Приклад 7.22. Отримання поточних системної дати і часу :

SELECT SYSDATETIME()

, SYSDATETIMEOFFSET()

, SYSUTCDATETIME()

, CURRENT_TIMESTAMP

, GETDATE()

, GETUTCDATE();

При цьому повертаються наступні значення:

SYSDATETIME() - 2009-08-30 13:10:02.0474381;

SYSDATETIMEOFFSET() - 2009-08-30 13:10:02.0474381 -07:00;

SYSUTCDATETIME() - 2009-08-30 20:10:02.0474381;

CURRENT_TIMESTAMP - 2009-08-30 13:10:02.047;

GETDATE() - 2009-08-30 13:10:02.047;

GETUTCDATE() - 2009-08-30 20:10:02.047.

Приклад 7.23. Отримання поточної системної дати :

SELECT CONVERT (date, SYSDATETIME())

, CONVERT (date, SYSDATETIMEOFFSET())

, CONVERT (date, SYSUTCDATETIME())

, CONVERT (date, CURRENT_TIMESTAMP)

, CONVERT (date, GETDATE())

, CONVERT (date, GETUTCDATE());

При цьому усі функції повертають: 2009-08-30.

Отримання поточного системного часу :

SELECT CONVERT (time, SYSDATETIME())

, CONVERT (time, SYSDATETIMEOFFSET())

, CONVERT (time, SYSUTCDATETIME())

, CONVERT (time, CURRENT_TIMESTAMP)

, CONVERT (time, GETDATE())

, CONVERT (time, GETUTCDATE());

При цьому повертаються наступні значення:

SYSDATETIME() - 13:18:45.3490361

SYSDATETIMEOFFSET() - 13:18:45.3490361

SYSUTCDATETIME() - 20:18:45.3490361

CURRENT_TIMESTAMP - 13:18:45.3470000

GETDATE() - 13:18:45.3470000

GETUTCDATE() - 20:18:45.3470000