Встроенные и хранимые функции в SQL
30.04.2012 |Встроенные (системные) функции
1) Список стандартных функций
| Функция | Возвращаемый результат |
| Bit_Length(<строка>) | Количество битов в <строке> |
| Cast(<значение> As <тип данных>) | <значение>, преобразованное в указанный <тип данных> |
| Char[acter]_Length(<строка>) | Длина <строки> символов |
| Convert(<строка> using <функция>) | <строка>, преобразованная в соответствии с указанной <функцией> |
| Current_Date | Текущая дата |
| Current_Time(<точность>) | Текущее время с указанной <точностью> |
| Current_TimeStamp(<точность>) | Текущая дата и время с указанной <точностью> |
| Extract(<часть> from <значение>) | Указанная <часть> (Day, Hour и т.п.) из <значения> даты |
| Lower(<строка>) | <строка>, преобразованная к нижнему регистру |
| Octet_Length(<строка>) | Число байтов в <строке> |
| Position(<строка1> in <строка2>) | Позиция, с которой <строка1> входит в <строку2> |
| Substring(<строка> from | Часть <строки>, начинающаяся с позиции |
| Trim(Leading|Trailing|Both <символ> from <строка>) | <строка>, у которой удалены ведущие | концевые | с обоих сторон <символы> |
| Upper(<строка>) | <строка>, преобразованная к верхнему регистру |
| User | Определяет идентификатор пользователя |
2) Обзор функций MS SQL Server
Так в SQL Server предусмотрено много функций, разделенных на следующие группы:
→ Строковые;
→ Математические;
→ Преобразования;
→ Для работы с данными типа Text и Image;
→ Для работы с датами;
→ Системные;
→ Ниладические (нульместные – без параметров).
А так же целый ряд других функций.
3) Обзор функций Oracle
→ Для работы с ошибками;
→ Числовые;
→ Строковые;
→ Преобразования;
→ Трансляции, для работы с датами;
→ Различного назначения.
Объявление хранимой функции
CREATE FUNCTION <имя функции> ([<имя параметра> <тип данных> [(<размер>)], …]) RETURNS <тип данных> [(<размер>)] [[NOT] DETERMINISTIC] [CONTAINS SQL|READS SQL DATA|MODIFIES SQL DATA]
BEGIN
<SQL-операторы>
RETURN <возвращаемое значение>
END
Ключевые слова
• [NOT] DETERMINISTIC показывает, возвращает или нет функция одинаковые значения при одних и тех же входных значениях. Например, функция CURRENT_TIME является NOT DETERMINISTIC.
• CONTAINS SQL показывает, что в функции нет SQL-операторов, читающих и модифицирующих данные. Это значение установлено по умолчанию.
• READS SQL DATA показывает, что функция содержит инструкции SELECT или FETCH.
• MODIFIES SQL DATA показывает, что функция содержит инструкции INSERT, UPDATE или DELETE.
Ограничения на недетерминистские функции
В Oracle:
• Недетерминистские функции нельзя использовать при проверке ограничений в выражении CHECK. Кроме того, в ограничения нельзя включать вызов определяемых пользователем функций.
• Недетерминистские функции нельзя использовать в индексах, основанных на функциях.
В SQL Server пользовательская функция считается детерминистическое, если:
• Функция является привязанной к схеме, т.е. функция создана с использованием опции SCHEMABINDING, а это означает, что объекты, на которые ссылается данная функция, не могут изменяться или удаляться.
• Каждая функция (неважно, встроенная или определяемая пользователем), вызываемая из тела этой функции, является детерминистской.
• В теле функции отсутствуют ссылки на объекты БД (например, таблицы, представления и другие функции), выходившие за пределы области видимости.
• Функция не обращается к расширенным хранимым процедурам (которые могут изменять состояние БД).
Удаление и изменение хранимых функций
Для удаления функции используется оператор:
DROP FUNCTION <имя функции>
Для изменения функции используется оператор:
ALTER FUNCTION <имя функции> ([[{IN|OUT|INOUT}] <имя параметра> <тип данных> [(<размер>)],…])
BEGIN
<SQL-операторы>
RETURN <возвращаемое значение>
END
Хранимые функции в SQL Server
CREATE FUNCTION <имя функции> ([@<имя параметра> [AS] <тип данных> [(<размер>)] [=<значение по умолчанию>] [READONLY],…])
RETURNS <тип данных> [(<размер>)]
[AS]
BEGIN
<SQL-операторы>
RETURN <скалярное значение>
END
Пример:
USE B1;
GO
CREATE FUNCTION Quarter(@Dat DateTime) RETURNS int
BEGIN
DECLARE @ISQuarter int;
IF ((Month(@Dat)>=1) And (Month(@Dat)<=3)) SET @ISQuarter=1; IF ((Month(@Dat)>=4) And (Month(@Dat)<=6)) SET @ISQuarter=2; IF ((Month(@Dat)>=8) And (Month(@Dat)<=9)) SET @ISQuarter=3; IF ((Month(@Dat)>=10) And (Month(@Dat)<=12)) SET @ISQuarter=4; RETURN @ISQuarter END SELECT DISTINCT dbo. Quarter(Dat) AS QT From Orders
Результат: QT 4
