Функції мови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