Встроенные функции Transact-SQL. Встроенные sql функции

Встроенные и хранимые функции в SQL

Встроенные (системные) функции

1) Список стандартных функций

Функция	Возвращаемый результат
Bit_Length()	Количество битов в
Cast( As )	, преобразованное в указанный
Char[acter]_Length()	Длина символов
Convert( using )	, преобразованная в соответствии с указанной
Current_Date	Текущая дата
Current_Time()	Текущее время с указанной
Current_TimeStamp()	Текущая дата и время с указанной
Extract( from )	Указанная (Day, Hour и т.п.) из даты
Lower()	, преобразованная к нижнему регистру
Octet_Length()	Число байтов в
Position( in )	Позиция, с которой входит в
Substring( from for )	Часть , начинающаяся с позиции и имеющая указанную
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;GOCREATE FUNCTION Quarter(@Dat DateTime) RETURNS intBEGINDECLARE @ISQuarter int;IF ((Month(@Dat)>=1) And (Month(@Dat)=4) And (Month(@Dat)=8) And (Month(@Dat)=10) And (Month(@Dat)

Результат: QT 4

all4study.ru

Встроенные функции Transact-SQL — Клёвый код

APPLOCK_MODE( 'database_principal' , 'resource_name' , 'lock_owner' )	Возвращает режим блокировки ресурса resource_name, с владельцем блокировки lock_owner, и правами пользоватля или ролью database_principal.
APPLOCK_TEST ( 'database_principal' , 'resource_name' , 'lock_mode' , 'lock_owner' )	Возвращает 0 когда блокировка не может быть предоставлена указанному владельцу, иначе 1.
@@PROCID	Возвращает идентификатор текущегей хранимой процедуры, пользовательской функции или триггера. Не вызывается из CLR среды или внутрипроцессного поставщика доступа к данным.
APP_NAME()	Возвращает имя приложения, для текущего сеанса.
ASSEMBLYPROPERTY('assembly_name', 'property_name')	Возвращает свойство property_name сборки assembly_name.
COL_LENGTH ( 'table' , 'column' )	Возвращает длину в байтах столбца column в таблице table.
COL_NAME(tab_id, col_id)	Возвращает имя столбца с идентификатором col_id из таблицы, имеющей идентификатор tab_id.
INDEX_COL ( '[ database_name . [ schema_name ] .\| schema_name ] table_or_view_name', index_id , key_id )	Возвращает имя индексированного столбца в таблице table_or_view_name. Столбец определяется по идентификатору индекса index_id и позиции столбца key_id в этом индексе.
COLUMNPROPERTY (id, col, property)	Возвращает свойство property стобца col, в таблице или процедуры заданной идентификатором id.
DATABASE_PRINCIPAL_ID ( 'principal_name' )	Возвращает идентификатор принципала по его имени name, если не указать имя выводиться идентификатор текущего принципала.
DATABASEPROPERTYEX (database, property)	Возвращает значение свойств property, из базы данных database.
DB_ID (['db_name'])	Возвращает идентификатор базы данных с именем db_name. Если имя не задано, возвращает идентификатор текущей базы данных.
DB_NAME ([db_id])	Возвращает имя базы данных с идентификатором b_id. Если идентификатор не задан, возвращает имя текущей базы данных.
FILE_ID ( file_name )	Возвращает идентификатор файла в текущей базе данных с имем file_name.
FILE_IDEX ( file_name )	Возвращает идентификатор файла данных, файла журнала, или полнотекстового файла в текущей базе данных с имем file_name.
FILE_NAME ( file_id )	Возвращает имя файла в текущей базе данных с идентификатором file_id.
FILEPROPERTY ( file_name , property )	Возвращает свойство property файла file_name.
FILEGROUP_ID ( 'filegroup_name' )	Возвращает по имени файловой группы filegroup_name, её идентификатор.
FILEGROUP_NAME (filegroup_id)	Возвращает имя файловой группы, по её идентификатору filegroup_id.
FILEGROUPPROPERTY ( filegroup_name, property )	Возвращает свойство property файловой группы filegroup_name.
FULLTEXTCATALOGPROPERTY ('catalog_name', 'property')	Возвращает свойство property полнотекстового каталога catalog_name. Появилась с 2014 версии.
FULLTEXTSERVICEPROPERTY ('property')	Возвращает свойство property механизма полнотекстового поиска.
INDEXKEY_PROPERTY ( object_ID, index_ID, key_ID, property )	Возвращает свойства property ключей индекса. Для XML-индексов возвращает NULL.
INDEXPROPERTY ( object_ID, index_or_statistics_name, property )	Возвращает свойство property именованного индекса или статистическое свойство таблицы. Заданной идентификационным номером, именем индекса, или статистики, или имененм свойства. Для XML-индексов возвращает NULL.
NEXT VALUE FOR [ database_name . ] [ schema_name . ] sequence_name [ OVER (<over_order_by_clause>) ]	Формирует номер последовательности из указанного объекта последовательности.
OBJECT_DEFINITION ( object_id )	Возвращает исходный код, указанного объекта. Если не хватает прав или произошла ошибка, возвращает NULL.
OBJECT_ID ( '[ database_name . [ schema_name ] . \| schema_name . ] object_name' [,'object_type' ] )	Возвращает идентификатор объекта базы данных, заданного именем object_name.
SCOPE_IDENTITY()	Возвращает последнее значение идентификатора, созданные в таблицах во время текущего сеанса.
OBJECT_NAME ( object_id [, database_id ] )	Возвращает имя объекта базы данных, заданного своим идентификатором object_id.
OBJECT_SCHEMA_NAME ( object_id [, database_id ] )	Возвращает имя схемы базы данных.
OBJECTPROPERTY ( id, property )	Возвращает информацию об объектах текущей базы данных.
OBJECTPROPERTYEX ( id, property )	Возвращает информацию об объектах текущей базы данных.
ORIGINAL_DB_NAME ()	Возвращает имя базы данных, указанное при подключении к базе данных.
PARSENAME ( 'object_name', object_piece )	Возвращает часть имени базы данных object_name. Часть объекта выберается параметром object_piece.
SCHEMA_ID ( [ schema_name ] )	Возвращает идентификатор схемы schema_name. Если имя схемы является недопустимым, возвращает NULL.
SCHEMA_NAME ( [ schema_id ] )	Возвращает имя схемы, по идентификатору schema_id. Если идентификатор является недопустимым, возвращает NULL.
SERVERPROPERTY ( propertyname )	Возвращает свойство propertyname экземпляра сервера.
STATS_DATE ( object_id, stats_id )	Возвращает дату последнего обновления статистики для таблицы или индексированного представления.
TYPE_ID ( [ schema_name ] type_name )	Возвращает идентификатор типа данных type_name.
TYPE_NAME ( type_id )	Возвращает короткое имя типа, по идентификатору type_id.
TYPEPROPERTY (type, property)	Возвращает свойство property, типа данных type.

coolcode.ru

SQL - Функции

Функция	Описание
DATEDIFF	Возвращает дату после добавления определенного интервала времени / даты
DATE_ADD	Возвращает время / дату-время после добавления определенного временного интервала
DATE_FORMAT	Возвращает текущую дату
DATE_SUB	Возвращает текущую дату
DAY	Возвращает текущее время
DAYNAME	Возвращает текущую дату и время
DAYOFMONTH	Возвращает текущее время
DAYOFWEEK	Извлекает значение даты из выражения даты или даты и времени
DAYOFYEAR	Возвращает разницу в днях между двумя значениями даты
EXTRACT	Возвращает дату после добавления определенного интервала времени / даты
FROM_DAYS	Форматирует дату, указанную маской формата
HOUR	Возвращает дату после вычитания определенного интервала времени / даты
LAST_DAY	Возвращает дневную часть значения даты
LOCALTIME	Возвращает имя дня недели для даты
LOCALTIMESTAMP	Возвращает дневную часть значения даты
MAKEDATE	Возвращает индекс недели недели для значения даты
MAKETIME	Возвращает день года для значения даты
MICROSECOND	Извлекает части с даты
MINUTE	Возвращает значение даты из числового представления дня
MONTH	Возвращает часовую часть значения даты
MONTHNAME	Возвращает последний день месяца на заданную дату
NOW	Возвращает текущую дату и время
PERIOD_ADD	Возвращает текущую дату и время
PERIOD_DIFF	Возвращает дату определенного годового и дневного значения
QUARTER	Возвращает время для определенного часа, минуты, второй комбинации
SECOND	Возвращает микросекундную часть значения даты
SEC_TO_TIME	Возвращает минутную часть значения даты
STR_TO_DATE	Возвращает месячную часть значения даты
SUBDATE	Возвращает полное название месяца для даты
SUBTIME	Возвращает текущую дату и время
SYSDATE	Принимает период и добавляет к нему определенное количество месяцев
TIME	Возвращает разницу в месяцах между двумя периодами
TIME_FORMAT	Возвращает четвертную часть значения даты
TIME_TO_SEC	Возвращает вторую часть значения даты
TIMEDIFF	Преобразует числовые секунды в значение времени
TIMESTAMP	Принимает строку и возвращает дату, заданную маской формата
TO_DAYS	Возвращает дату, после которой вычитается определенный интервал времени / даты
WEEK	Возвращает значение time / datetime после вычитания определенного временного интервала
WEEKDAY	Возвращает текущую дату и время
WEEKOFYEAR	Извлекает значение времени из выражения time / datetime
YEAR	Форматирует время, указанное маской формата
YEARWEEK	Преобразует значение времени в числовые секунды

unetway.com

Хранимые функции Transact-SQL

С SQL Server вы можете создавать ваши собственные функции, добавляющие и расширяющие функции, предоставляемые системой. Функции могут получать 0 или более параметров и возвращать скалярное значение или таблицу. Входные параметры могут быть любого типа, исключая timestamp, cursor, table.

Сервер SQL поддерживает три типа функций определенных пользователем:

Скалярные функции – похожи на встроенные функции;
Функция, возвращающая таблицу - возвращает результат единичного оператора SELECT. Он похож на объект просмотра, но имеет большую эластичность благодаря использованию параметров, и расширяет возможности индексированного объекта просмотра;
Многооператорная функция - возвращает таблицу созданную одним или несколькими операторами Transact-SQL, чем напоминает хранимые процедуры. В отличие от процедур, на такие функции можно ссылаться в WHERE как на объект просмотра.

1. Создание хранимой функции

Создание функций очень похоже на создание процедур и объектов просмотра. эедаром мы рассматриваем все эти темы в одной главе. Для создания функции используется оператор CREATE FUNCTION. В зависимости от типа, Объявление будет отличаться. э ассмотрим все три типа объявления.

Скалярная функция:

CREATE FUNCTION [ owner_name. ] function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS scalar_return_data_type [ WITH [ [,] ...n] ] [ AS ] BEGIN function_body RETURN scalar_expression END

Функция, возвращающая таблицу:

CREATE FUNCTION [ owner_name. ] function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS TABLE [ WITH [ [,] ...n ] ] [ AS ] RETURN [ ( ] select-stmt [ ) ]

Многооператорные функции:

CREATE FUNCTION [ owner_name. ] function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS @return_variable TABLE [ WITH [ [,] ...n ] ] [ AS ] BEGIN function_body RETURN END ::= { ENCRYPTION | SCHEMABINDING } :: = ( { column_definition | table_constraint } [ ,...n ] )

2. Скалярные функции в Transact-SQL

Давайте для примера создадим функцию, которая будет возвращать скалярное значение. эапример, результат перемножение цены на количество указанного товара. Товар будет идентифицироваться по названию и дате, ведь мы договорились, что сочетание этих полей дает уникальность. эо будьте осторожны, при тестировании запроса, если в разделе 3.2.8 вы выполнили запрос на изменение данных и создали дубликаты покупок за 1.1.2005-го года.

Итак, посмотрим сначала на код создание скалярной функции:

CREATE FUNCTION GetSumm (@name varchar(50), @date datetime) RETURNS numeric(10,2) BEGIN DECLARE @Summ numeric(10,2) SELECT @Summ = Цена*Количество FROM Товары WHERE [эазвание товара]=@name AND Дата=@date; RETURN @Summ END

После оператора CREATE FUNCTION мы указываем имя функции. Далее, в скобках идут параметры, которые необходимо передать. Да, параметры должны передаваться через запятую в круглых скобках. В этом объявление отличается от процедур и эту разницу необходимо помнить.

Далее указывается ключевое слово RETURNS, за которым идет описание типа возвращаемого значения. Для скалярной функции это могут быть любые типы (строки, числа, даты и т.д.).

Код, который должна выполнять функция пишется между ключевыми словами BEGIN (начало) и END (конец). В коде можно использовать любые операторы Transact-SQL, которые мы изучали ранее. Итак, объявление нашей функции в упрощенном виде можно описать следующим образом:

CREATE FUNCTION GetSumm (@name varchar(50), @date datetime) RETURNS numeric(10,2) BEGIN -- Код функции END

Между ключевыми словами BEGIN и END у нас выполняется следующий код:

-- Объявление переменной DECLARE @Summ numeric(10,2) -- Выполнение запроса на выборку суммы SELECT @Summ = Цена*Количество FROM Товары WHERE [эазвание товара]=@name AND Дата=@date; -- Возврат результата RETURN @Summ

В первой строке объявляется переменная @Summ. Она нужна для хранения промежуточного результата расчетов. Далее выполняется запрос SELECT, в котором происходит поиск строки по дате и названию товара в таблице товаров. В найденной строке перемножаются поля цены и количества, и результат записывается в переменную @Summ.

Обратите внимание, что в конце запроса стоит знак точки с запятой. Каждый запрос должен заканчиваться этим символом, но в большинстве примеров мы этим пренебрегали, но в функции отсутствие символа ";" может привести к ошибке.

В последней строке возвращаем результат. Для этого нужно написать ключевое слово RETURN, после которого пишется возвращаемое значение или переменная. В данном случае, возвращаться будет содержимое переменной @Summ.

Так как функция скалярная, то и возвращаемое значение должно быть скалярным и при этом соответствовать типу, описанному после ключевого слова RETURNS.

3. Использование функций

Как выполнить такую функцию? Да также, как и многие другие системные функции (например, GETDATE()). эапример, следующий пример использует функцию в операторе SELECT:

SELECT dbo.GetSumm('Картофель', '03.03.2005')

В этом примере, оператор SELECT возвращает результат выполнения функции GetSumm. Функция принадлежит пользователю dbo, поэтому перед именем я указал владельца. После имени в скобках должны быть перечислены параметры в том же порядке, что и при объявлении функции. В данном примере я запрашиваю затраты на картофель, купленный 3.3.2005.

Выполните следующий запрос и убедитесь, что он вернул тот же результат, что и созданная нами функция:

SELECT Цена*Количество FROM Товары WHERE [эазвание товара]='Картофель' AND Дата='03.03.2005'

Функции можно использовать не только в операторе SELECT, но и напрямую, присваивая значение переменной. эапример:

DECLARE @Summ numeric(10,2) SET @Summ=dbo.GetSumm('Картофель', '03.03.2005') PRINT @Summ

В этом примере мы объявили переменную @Summ типа numeric(10,2). Именно такой тип возвращает функция. В следующей строке переменной присваивается результат выполнения Summ, с помощью SET.

Давайте посмотрим, что произойдет, если передать функции такие параметры, при которых запрос функции вернет более одной строки. В нашей таблице товаров сочетание даты и название не дает уникальности, потому что мы ее нарушили. Первичного ключа в таблице также нет, и среди товаров у меня есть четыре строки, которые имеют свои точные копии. это нарушает правило уникальности строк в реляционных базах, но очень наглядно показывает, что в реальной жизни нарушать его нельзя.

Итак, в моей таблице есть две покупки хлеба 1.1.2005-го числа. Попробую запросить у функцию сумму:

SELECT dbo.GetSumm('Хлеб', '01.01.2005')

э езультатом будет только одно число, хотя строки две. э какую строку из двух вернул сервер? эикто точно сказать не может, потому что они обе одинаковые и без единого различия. Поэтому сервер скорей всего вернул первую из строк.

4. Функция, возвращающая таблицу

В следующем примере мы создаем функцию, которая будет возвращать в качестве результата таблицу. В качестве примера, создадим функцию, которая будет возвращать таблицу товаров, и для каждой строки рассчитаем произведение колонок количества и цены:

CREATE FUNCTION GetPrice() RETURNS TABLE AS RETURN ( SELECT Дата, [эазвание товара], Цена, Количество, Цена*Количество AS Сумма FROM Товары )

эачало функции такое же, как у скалярной – указываем оператор CREATE FUNCTION и имя функции. Я специально создал эту функцию без параметров, чтобы вы увидели, как это делается. эе смотря на то, что параметров нет, после имени должны идти круглые скобки, в которых не надо ничего писать. Если не указать скобок, то сервер вернет ошибку и функция не будет создана.

э азница есть и в секции RETURNS, после которой указывается тип TABLE, что говорит о необходимости вернуть таблицу. После этого идет ключевое слово AS и RETURN, после которого должно идти возвращаемое значение. Для функции данного типа в секции RETURN нужно в скобках указать запрос, результат которого и будет возвращаться функцией.

Когда пишете запрос, то все его поля должны содержать имена. Если одно из полей не имеет имени, то результатом выполнения оператора CREATE FUNCTION будет ошибка. В нашем примере последнее поле является результатом перемножения полей "Цена" и "Количество", а такие поля не имеют имени, поэтому мы его задаем с помощью ключевого слова AS.

Посмотрим, как можно использовать такую функцию с помощью оператора SELECT:

SELECT * FROM GetPrice()

Так как мы используем простой оператор SELECT, то мы можем и ограничивать вывод определенными строками, с помощью ограничений в секции WHERE. эапример, в следующем примере выбираем из результата функции только те строки, в которых поле "Количество" содержит значение 1:

SELECT * FROM GetPrice() WHERE Количество=1

Функция возвращает в качестве результата таблице, которую вы можете использовать как любую другую таблицу базы данных. Давайте создадим пример в котором можно будет увидеть использование функции в связи с таблицами. Для начала создадим функцию, которая будет возвращать идентификатор работников таблицы tbPeoples и объединенные в одно поле ФИО:

CREATE FUNCTION GetPeoples() RETURNS TABLE AS RETURN ( SELECT idPeoples, vcFamil+' '+vcName+' '+vcSurName AS FIO FROM tbPeoples )

Функция возвращает нам идентификатор строки, с помощью которого мы легко можем связать результат с таблицей телефонов. Попробуем сделать это с помощью простого SQL запроса:

SELECT * FROM GetPeoples() p, tbPhoneNumbers pn WHERE p.idPeoples=pn.idPeoples

Как видите, функции, возвращающие таблицы очень удобны. Они больше, чем процедуры похожи на объекты просмотра, но при этом позволяют принимать параметры. Таким образом, можно сделать так, чтобы сама функция возвращала нам только то, что нужно. Вьюшки такого не могут делать по определению. Чтобы получить нужные данные, вьюшка должна выполнить свой SELECT запрос, а потом уже во внешнем запросе мы пишем еще один оператор SELECT, с помощью которого ограничивается вывод до необходимого. Таким образом, выполняется два запроса SELECT, что для большой таблицы достаточно накладно. Функция же может сразу вернуть только то, что нужно.

э ассмотрим пример, функция GetPeoples у нас возвращает все строки таблицы. Чтобы получить только нужную фамилию, нужно писать запрос типа:

SELECT * FROM GetPeoples() WHERE FIO LIKE 'ПОЧЕЧКИэ%'

В этом случае будут выполняться два запроса: этот и еще один внутри функции. эо если передавать фамилию в качестве параметра в функцию и там сделать секцию WHERE, то можно обойтись и одним запросом SELECT:

CREATE FUNCTION GetPeoples1(@Famil varchar(50)) RETURNS TABLE AS RETURN ( SELECT idPeoples, vcFamil+' '+vcName+' '+vcSurName AS FIO FROM tbPeoples WHERE vcFamil=@Famil )

5. Многооператорная функция возвращающая таблицу

Все функции, созданные в разделе 3.3.5 могут возвращать таблицу, сгенерированную только одним оператором SQL. э как же тогда сделать возможность выполнять несколько операций? эапример, вы можете захотеть выполнять дополнительные проверки входных параметров для обеспечения безопасности. Проверки лишними не бывает, особенно входных данных и особенно, если эти входные данные указываются пользователем.

Следующий пример показывает, как создать функцию, которая может вернуть в качестве результата таблицу, и при этом, в теле функции могут выполняться несколько операторов:

CREATE FUNCTION имя (параметры) RETURNS имя_переменной TABLE (описание вида таблицы, в которой будет представлен результат) AS BEGIN Выполнение любого количества операций RETURN END

это упрощенный вид создания процедуры. Более полный вид мы рассматривали в начале главы, а сейчас я упростил объявление, чтобы проще было его разбирать.

Объявление больше похоже на создание скалярных функций. Первая строка без изменений. В секции RETURNS объявляется переменная, которая имеет тип TABLE. После этого, в скобках нужно описать поля результирующей таблицы. После ключевого слова AS идtт пара операторов BEGIN и END, между которыми может выполняться какое угодно количество операций. Выполнение операций заканчивается ключевым словом RETURN.

Вот тут есть одно отличие от скалярных функций – после RETURN мы указывали имя переменной, значение которой должно стать результатом. В данном случае ничего указывать не надо. Мы уже объявили переменную в секции RETURNS и описали формат этой переменной. В теле функции мы можем и должны наполнить эту переменную значениями и именно это попадет в результат.

Теперь посмотрим на пример создания функции:

CREATE FUNCTION getFIO () RETURNS @ret TABLE (idPeoples int primary key, vcFIO varchar(100)) AS BEGIN INSERT @ret SELECT idPeoples, vcFamil+' '+vcName+' '+vcSurName FROM tbPeoples; RETURN END

В данном примере в качестве результата объявлена переменная @ret, которая является таблицей из двух полей "idPeoples" типа int и "vcFIO" типа varchar длинной в 50 символов. В теле функции в эту таблицу записываются значения из таблицы tbPeoples и выполняется оператор RETURN, завершающий выполнение функции.

В использовании, такая функция ничем не отличается от рассмотренных ранее. эапример, следующий запрос выбирает все данные, которые возвращает функция:

SELECT * FROM GetFIO()

6. Опции функций

При создании функций могут использоваться следующие опции SCHEMABINDING (привязать к схеме) и/или ENCRYPTION (шифровать текст функции). Если вторая опция нам уже известна по вьюшкам и процедурам (позволяет шифровать исходный код функции в системных таблицах), то вторая встречается впервые, но при этом предоставляет удобное средство защиты данных.

Если функция создана с опцией SCHEMABINDING, то объекты базы данных, на которые ссылается функция, не могут быть изменены (с использованием оператора ALTER) или удалены (с помощью оператора DROP). эапример, следующая функция использует таблицу tbPeoples и при этом используется опция SCHEMABINDING:

CREATE FUNCTION GetPeoples2(@Famil varchar(50)) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT idPeoples, vcFamil+' '+vcName+' '+vcSurName AS FIO FROM dbo.tbPeoples WHERE vcFamil=@Famil )

Функция может быть связанной со схемой, только если следующие ограничения истины:

все функции объявленные пользователем и просмотрщики на которые ссылается функция, также связаны со схемой с помощью опции SCHEMABINDING;
объекты, на которые ссылается функция, должны использовать имя из двух частей именования: owner.objectname. При создании функции GetPeoples2 ссылка на таблицу указана именно в таком формате – dbo.tbPeoples;
Функция и объекты должны быть расположены в одной базе данных;
Пользователь, который создает функцию, имеет право доступа ко всем объектам, на которые ссылается функция.

Создайте функцию и попробуйте после этого удалить таблицу tbPeoples.

DROP TABLE tbPeoples

В ответ на это сервер выдаст сообщение с ошибкой о том, что объект не может быть удален, из-за присутствия внешнего ключа. Даже если избавиться от ключа, удаление будет невозможно, потому что на таблицу ссылается функция, привязанная к схеме.

Чтобы увидеть сообщение без удаления ключа, давайте добавим к таблице колонку, а потом попробуем ее удалить:

-- Добавим колонку ALTER TABLE dbo.tbPeoples ADD vcTemp VARCHAR(30) NOT NULL default '' -- Попробуем ее удалить ALTER TABLE dbo.tbPeoples DROP COLUMN vcTemp

Создание пройдет успешно, а вот во время удаления произойдет ошибка, с сообщением о том, что существует ограничение, которое зависит от колонки. Мы же не создавали никаких ограничений, а просто добавили колонку и попытались ее удалить. Ограничение уже давно существует, но не на отдельную колонку, а на все колонки таблицы и это ограничение создано функцией GetPeoples2, которая связана со схемой.

7. Изменение функций

Вы можете изменять функцию с помощью оператора ALTER FUNCTION. Общий вид для каждого варианта функции отличается. Давайте рассмотрим каждый из них.

1. Общий вид команды изменения скалярной функции:

ALTER FUNCTION [ owner_name. ] function_name ( [ { @parameter_name scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS scalar_return_data_type [ WITH [,...n] ] [ AS ] BEGIN function_body RETURN scalar_expression END

2. Общий вид изменения функции, возвращающей таблицу:

ALTER FUNCTION [ owner_name. ] function_name ( [ { @parameter_name scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS TABLE [ WITH [ ,...n ] ] [ AS ] RETURN [ ( ] select-stmt [ ) ]

3. Общий вид команды изменения функции с множеством операторов, возвращающей таблицу.

ALTER FUNCTION [ owner_name. ] function_name ( [ { @parameter_name scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS @return_variable TABLE [ WITH [ ,...n ] ] [ AS ] BEGIN function_body RETURN END ::= { ENCRYPTION | SCHEMABINDING } :: = ( { column_definition | table_constraint } [ ,...n ] )

Следующий пример показывает упрощенный вариант команды, изменяющей функцию:

ALTER FUNCTION dbo.tbPeoples AS -- эовое тело функции

8. Удаление функций

Если вы внимательно читали об объектах просмотра и функциях, то не трудно догадаться, как можно удалить функцию. Конечно же для этого используется оператор DROP FUNCTION:

DROP FUNCTION dbo.GetPeoples2

skeitol.ru

Встроенные функции ORACLE SQL

Встроенные функции ORACLE SQL Функция – это оператор ORACLE SQL, который может принимать один или несколько параметров и результат выполнения которого может быть подставлен в выражение. В частности, функции могут изменять внешнее представление данных (например, переводить дату в соответствующий день недели), выполнять их статистическую обработку или изменять содержание. Все функции делятся на две большие группы: однострочные и групповые. Однострочные функции выполняют операции, которые могут повлиять на каждую строку таблицы в отдельности. В отличие от них групповые функции предназначены для получения агрегированной информации о некоторых подмножествах данных.

Системные переменныеСистемные переменные создаются СУБД и содержат информацию о среде, в которой функционирует база данных. Три системные переменные, описанные далее, позволяют определять системную дату и время, идентификатор пользователя, выполняющего SQL-оператор, и имя компьютера, с которого пользователь вводит команды.SYSDATEФункция SYSDATE возвращает текущие дату и время с точки зрения сервера ORACLE. Например:

-- вывод системной даты экранSELECT SYSDATE FROM DUAL;

Таблица DUAL является системной таблицей ORACLE. Она используется в запросах SELECT в тех случаях, когда выражение, указанное в параметре SELECT, необходимо выполнить только один раз, причем часто результат выражения не зависит от данных, находящихся в других таблицах.Функцию SYSDATE можно использовать и в других операторах DML для вставки значения текущей даты и времени в поле таблицы. Поскольку функция SYSDATE возвращает результат типа DATE, к результату этой функции можно прибавлять целые или отрицательные числа для получения прошлой или будущей даты:

-- получение даты, которая наступит через неделюSELECT SYSDATE+7 FROM DUAL;

USERФункция USER возвращает идентификатор пользователя ORACLE, который вызвал содержащий ее SQL-оператор:

SELECT USER FROM DUAL;

USERENVФункция USERENV может возвращать множество разных сведений о вычислительной среде, из которой был вызван содержащий ее SQL-оператор. Наибольший интерес представляет имя компьютера, на котором работает пользователь. Его можно получить, введя следующую команду:

SELECT USERENV(‘TERMINAL’) FROM DUAL;

Числовые функцииROUND и TRUNCФункция ROUND округляет числа с любой заданной точностью. Она имеет следующий синтаксис:

ROUND(входное_значение, число_знаков_после_десятичной_точки)

Функция TRUNC усекает число, понижая его точность. Она имеет синтаксис аналогичный функции ROUND. Число знаков после точки в обеих функциях может быть как положительным, так и отрицательным. В последнем случае округление будет происходить до ближайших десятков, сотен, тысяч и т.д.Разница в использовании функций ROUND и TRUNC видна на следующем примере:

SELECT ROUND(1234.5678, 3) FROM DUAL;SELECT TRUNC(1234.5678, 3) FROM DUAL;

Результат выполнения функции ROUND будет равен 1234.568, а результат выполнения функции TRUNC равен 1234.567.Функция TRUNC также часто применяется при необходимости выделить дату из значения типа DATE. Эта задача часто возникает, если необходимо сравнить между собой две даты без учета времени. Округлив значение типа DATE до целого при помощи функции TRUNC можно получить чистую дату без компоненты времени. Например:

SELECT TRUNC(SYSDATE) FROM DUAL;

Текстовые функцииUPPER, LOWER и INITCAPЭти три функции меняют регистр переданного им текста. Функция UPPER преобразует все переданные ей символы в заглавные, LOWER – в строчные, а INITCAP преобразует в заглавные первые символы каждого слова, а остальные делает строчными.Первые две функции часто применяются тогда, когда необходимо сравнить между собой две строки без учета регистра содержащихся в них букв. Последняя функция очень удобна, если необходимо корректно вывести фамилию, имя и отчество даже тогда, когда изначально в базе данных по ошибке или умышленно информация была занесена не корректно. LENGTHПри помощи функции LENGTH можно определить длину символьного поля. Например, допустим, что названия товаров периодически печатаются в каталоге, и рассматривается возможность уменьшения размера страницы каталога, а новый размер страницы требует сокращения длины названия товаров с 25 до 15 символов. Чтобы принять окончательное решение необходимо знать, какие и сколько названий придется изменить. Это можно определить при помощи следующей команды:

SUBSTRПри помощи функции SUBSTR (от слова substring – подстрока) можно вырезать из символьного значения какого-либо столбца подстроку заданной длины. Функция имеет следующий синтаксис:

SUBSTR(исходный_текст, начальная_позиция, количество_символов)

Это может понадобиться сделать, если информация в символьном поле содержит в себе некоторую структуру, и нужно выделить из этой структуры ее составные части.

INSTRДанная функция используется для поиска подстроки в строке и для определения номера символа в исходной строке, в которой найдена подстрока. Функция имеет следующий синтаксис:

INSTR(исходный_текст, подстрока, позиция_начального_символа)

Как правило, исходный текст представляет собой имя столбца, содержащего длинную строку, в которой нужно произвести поиск подстроки. Если подстрока не будет найдена, функция вернет значение 0. Функция INSTR чаще всего используется не просто для отбора строк, содержащих ту или иную подстроку (это логичнее и быстрее сделать при помощи LIKE), а для определения позиции, с которой начинается подстрока для того, чтобы использовать это значение как промежуточный результат в других функциях. Например:

-- выделение из адреса названия городаSELECT SUBSTR(Address, INSTR(Address, ‘г.’, 1),INSTR(Address, ‘,’, 1)- INSTR(Address, ‘г.’, 1)) ГородFROM Students;

Операция конкатенации строкКонкатенация (сложение) строк осуществляется при помощи операции ||. Данная операция заменяет операцию +, которая используется для чисел. Все аргументы операции конкатенации строк СУБД автоматически сначала преобразовывает в строки, поэтому при помощи данной операции можно соединять аргументы разных типов.

LTRIM и RTRIMФункции предназначены для удаления избыточных пробелов в начале или конце текстовой строки. Функция обычно используется для удаления избыточных пробелов при выводе содержимого полей типа CHAR, либо удаления избыточных пробелов, получившихся в результате выполнения какой-либо текстовой функции.

Функции работы с датамиADD_MONTHSФункция ADD_MONTHS возвращает дату с тем же днем месяца, что и в исходной дате, но отнесенную на заданное количество месяцев в будущее или прошлое. Причем функция достаточно интеллектуальна, чтобы определить, является ли указанный день последним днем месяца, и соответствующим образом скорректировать конечный результат. Например:

-- определить номер зачетки студентов, начавших увлекаться каким-либо хобби менее 2-х месяцев от текущей датыSELECT N_zFROM Students_HobbyWHERE Data_Start

LAST_DAYФункция LAST_DAY возвращает последний день любого месяца, указанного в переданной ей дате.

MONTHS_BETWEENФункция MONTS_BETWEEN возвращает количество месяцев, разделяющих две даты. Применив функцию TRUNC, можно получить количество целых месяцев. Например:

-- определение количества месяцев до текущей даты, прошедших с момента начала увлечения хобби SELECT N_z, TRUNC(MONTHS_BETWEEN(Data_Start, SYSDATE), 0)FROM Students_Hobby;

Функции преобразования данныхTO_CHARФункция TO_CHAR преобразует дату, время или число в текст. Ее основная ценность в том, что она позволяет в широких пределах управлять отображением дат, времени и чисел. По умолчанию числа показываются с тем количеством десятичных знаков, которые содержат. Что же касается дат, то они отображаются в формате, который мало кто использует в повседневной жизни, и по умолчанию не содержат времени. Функция TO_CHAR позволяет исправить эти ситуации. Синтаксис функции имеет следующий вид:

TO_CHAR(входное_значение, код_формата)

Код формата состоит из одного или нескольких элементов, определяющих, как будут представлены дата и время, либо число. В таблицах 2 и 3 представлен неполный перечень элементов формата, которые могут быть использованы для форматированного вывода даты, времени и чисел. Например:

-- отображение даты и времени рождения студентов-- в формате дд.мм.гггг чч:мм:ссSELECT S_name,F_name, TO_CHAR(Date_B, ‘dd.mm.yyyy hh34:mi:ss’FROM Students;

Таблица 1. Элементы формата даты и времени, используемого в функции TO_CHARЭлементОписание

-/,.;:‘любой текст’Воспроизведение соответствующих знаков препинания и текста в кавычках

DДень недели (1-7)

DAYНазвание дня недели, дополненное пробелами до девяти символов

DDДень месяца (1-31)

DDDДень года (1-366)

DYСокращенное название дня недели

MMМесяц года (1-12)

MONСокращенное название месяца года

MONTHНазвание месяца года

RMРимский номер месяца

QКвартал года

YYГод (две цифры)

YYYYГод (четыре цифры)

YEARГод в буквенном написании

WWНеделя года (1-53), в котором первая неделя начинается в первый день года и продолжается до седьмого дня года

WНеделя месяца (1-5), в котором первая неделя начинается в первый день месяца и заканчивается в седьмой день месяца

HHЧас дня (1-12)

Hh34Час дня (0-23)

MIМинуты (0-59)

SSСекунды (0-59)

AMA.M.PMP.M.Обозначение времени до или после полудня (с точками или без точек) Таблица 2. Элементы формата чисел, используемого в функции TO_CHARЭлементОписание

$Отображение знака доллара перед значением

LОтображение знака местной денежной единицы в указанной позиции

, (запятая)Помещает запятую в указанной позиции

. (точка)Помещает точку в указанной позиции

RN или rnОтображение числа римскими цифрами верхнего или нижнего регистра (только для целых чисел от 1 до 3999) TO_DATEФункция TO_DATE преобразует текстовое представление даты (и/или времени) в действительные значения даты/времени. Функция имеет синтаксис аналогичный синтаксису функции TO_CHAR и используется, например, при вводе данных в таблицы при неизвестном формате, принятом в той или иной базе данных по умолчанию.

Прочие функцииDECODEФункция DECODE транслирует одно множество данных в другое, используя определенные значения до и после. Алгоритм работы данной функции схож с оператором CASE, существующим в большинстве языков программирования высокого уровня. Синтаксис функции DECODE имеет следующий вид:

DECODE(источник_данных,входное_значение_1, выходное_значение_1,входное_значение_2, выходное_значение_2,…[выходное_значение_при_остутствии_совпадений])

Например, функция DECODE может использоваться для приведения к единому стандарту, принятому в организации, названия групп товаров:

SELECT DECODE(Name,‘Косметика’, ‘Косметика и парфюмерия’,‘Авто’, ‘Автомобили’,‘Компьютеры’, ‘Компьютеры и оргтехника’,Name) “Name”FROM Groups;

NVLФункция NVL возвращает указанное значение вместо NULL. Эта функция имеет следующий синтаксис:

NVL(входное_значение, результат_если_NULL)

Функцию NVL можно применять к столбцам и значениям любых типов, но следует соблюдать правило: типы параметров функции входное_значение и результат_если_NULL должны совпадать. Например:

-- для студентов, для которых не указаны телефоны,выдастся фраза нет телефона в поле N_telSELECT F_Name,S_Name, NVL(N_tel, ‘нет телефона’) FROM Students;

www.freedocs.xyz

Встроенные функции | SQL Программирование

Встроенные функции несколько компенсируют непроцедурный характер языка SQL. Они способны выполнять великое множество задач, варьирующихся от округления чисел до манипуляций со строками, преобразований типов данных и функций со сложной процедурной логикой подстановки, таких как DECODE.

Несмотря на то что количество функций, определенных стандартом SQL92/99, относительно невелико (хотя и было расширено), каждый производитель СУБД добавил к этому множеству собственный набор полезных средств. Таким образом, совершенно не удивительно, что состав фунций, предлагаемых разными СУБД, существенно различается как по функциональности, так и по деталям реализации и синтаксису. В настоящей главе был представлен исчерпывающий обзор некоторых наиболее важных функций, а также особенностей их реализации в конкретных СУБД. Классификация функций была выполнена на основе типов данных, с которыми проводятся операции, а также общей функциональности. Некоторые функции, реализующие нестандартные операции, были выделены в особый подкласс “прочие”. Функции системы безопасности будут подробно рассмотрены в главе 12, а функции XML — в главе 15.ГЛАВ ОПераторы в SQL определены как символы и ключевые слова, используемые для осуществления заданных действий над одним или несколькими выражениями, называемыми Операндами или Аргументами. Арифметические операторы и операторы конкатенации строк Логические операторы Приоритет операторов Оператор присваивания Операторы сравнения Битовые операторы Арифметические операторы используют для числовых вычислений; они интуитивно понятны (если пользователь, конечно, закончил начальную школу) и могут применяться практически во всех предложениях SQL. В табл. 11.1 представлен полный список арифметических операторов.