Пользовательские функции и процедуры MS SQL. Функции ms sql

Создание и выполнение функций в СУБД MS SQL Server — МегаЛекции

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

Функции Scalar

Создание и изменение функции данного типа выполняется с помощью команды:

::={CREATE | ALTER } FUNCTION [владелец.] имя_функции( [ { @имя_параметра скаляр_тип_данных [=default]}[,...n]])RETURNS скаляр_тип_данных[WITH {ENCRYPTION | SCHEMABINDING} [,...n] ][AS]BEGINRETURN скаляр_выражениеEND

Рассмотрим назначение параметров команды.

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

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

Дополнительные параметры, с которыми должна быть создана функция, могут быть указаны посредством ключевого слова WITH. Благодаря ключевому слову ENCRYPTION код команды, используемый для создания функции, будет зашифрован, и никто не сможет просмотреть его. Эта возможность позволяет скрыть логику работы функции. Кроме того, в теле функции может выполняться обращение к различным объектам базы данных, а потому изменение или удаление соответствующих объектов может привести к нарушению работы функции. Чтобы избежать этого, требуется запретить внесение изменений, указав при создании этой функции ключевое слово SCHEMABINDING.

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

Когда в ходе выполнения кода функции встречается ключевое слово RETURN, выполнение функции завершается и как результат ее вычисления возвращается значение, указанное непосредственно после слова RETURN. Отметим, что в теле функции разрешается использование множества командRETURN, которые могут возвращать различные значения. В качестве возвращаемого значения допускаются как обычные константы, так и сложные выражения. Единственное условие – тип данных возвращаемого значения должен совпадать с типом данных, указанным после ключевого словаRETURNS.

ПримерСоздать и применить функцию скалярного типа для вычисления суммарного количества товара, поступившего за определенную дату. Владелец функции – пользователь с именем user1.

CREATE FUNCTION user1.sales(@data DATETIME)RETURNS INTASBEGINDECLARE @c INTSET @c=(SELECT SUM(количество) FROM Сделка WHERE дата=@data)RETURN (@c)END

Функции 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.

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

CREATE FUNCTION user1.itog()RETURNS TABLEASRETURN (SELECT TOP 2 Товар.Название FROM Товар INNER JOIN Склад ON Товар.КодТовара=Склад.КодТовара ORDER BY Склад.Остаток DESC)

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

SELECT НазваниеFROM user1.itog()

Функции Multi-statement

Создание и изменение функций типа Multi-statement выполняется с помощью следующей команды:

::={CREATE | ALTER }FUNCTION [владелец.] имя_функции( [ { @имя_параметра скаляр_тип_данных [=default]}[,...n]])RETURNS @имя_параметра TABLE [WITH {ENCRYPTION | SCHEMABINDING} [,...n] ][AS]BEGINRETURN END

Использование большей части параметров рассматривалось при описании предыдущих функций.

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

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

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

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

Рекомендуемые страницы:

Воспользуйтесь поиском по сайту:

megalektsii.ru

MS SQL 2005: оконные функции

Иван Бодягин (Merle)

Введение

Ввиду того, что в следующей версии MS SQL Server, выход которой ожидается в 2005 году, нововведений просто безумное количество, слона приходится есть по частям. Данный кусочек посвящен новой функциональности называемой «оконные функции» (Window Functions), также известной широкой общественности под именем «аналитических», или OLAP-функций.

Сама по себе идея не нова. С подачи IBM, Oracle, Informix и Compaq аналитические функции были добавлены в ANSI SQL 99. В Oracle поддержка подобной функциональности, со своими специфичными расширениями, появилась с версии 8i R2, в DB2 – с версии 7.1, да и имеющиеся в T-SQL на данный момент функции CUBE и ROLUP также можно отнести к аналитическим.

С одной стороны, Microsoft и так поставляет вместе c SQL Server достаточно мощный механизм для анализа данных – Analysis Services. Отчасти по этой причине Microsoft не спешила поддерживать инициативу введения некоторых OLAP-функций в стандарт SQL.

Но с другой стороны, при необходимости некоторого анализа данных не всегда есть возможность и желание поднимать еще один сервис. К тому же, строго говоря, аналитические функции не совсем правильно относить к OLAP, они не предназначены для многомерного анализа данных, работы с гиперкубами и сложными иерархиями, эти функции осуществляют лишь статистический анализ данных в готовой выборке... Тем не менее, это достаточно мощный и удобный инструмент, и поэтому, в конце концов, Microsoft также не осталась в стороне.

Сначала несколько слов о том, что же из себя представляют и как работают аналитические функции в общем виде. Одно из главных отличий аналитических функций от обычных, скалярных, заключается в том, что этот класс операторов работает с готовой выборкой. Сначала формируется выборка, выполняются все объединения, условия WHERE, GROUP BY, HAVING – все, кроме сортировки, и только затем к практически готовому набору данных применяется аналитическая функция. Именно поэтому аналитические функции можно указывать лишь в списке выборки или в условии сортировки.

В общем случае принцип работы аналитических функций можно представить примерно следующим образом. Допустим, что у нас есть результирующий набор данных, полученный вышеописанным способом – выполнено все, кроме сортировки. На каждую запись в этом наборе накладывается так называемое «окно», размеры и положение которого определяются в соответствии с некоторым аналитическим условием (собственно отсюда и название этого класса функций – «оконные функции», window functions). В это виртуальное «окно» попадают несколько других записей из того же набора, то есть целая группа записей. При этом «окно» может быть сформировано таким образом, что в него попадут вовсе не соседние записи, а практически произвольные записи из набора, и даже сама текущая запись, на основании которой формируется «окно», может в это «окно» не попасть (в дальнейшем термин «окно» будет использоваться для обозначения именно такого набора записей). Когда «окно» сформировано, аналитическая функция вычисляет агрегированное значение по записям, входящим в «окно», и переходит к следующей записи. Для этой записи формируется новое «окно», снова вычисляется агрегированное значение - и так для всех записей в выборке. При этом размер и положение «окна» от записи к записи может меняться, в таких случаях используют термин «скользящее окно» (sliding window).

Спектр применения аналитических функций достаточно широк - это различного рода распределения (ранговое (ranking), кумулятивное и т.д.), процентирование (percentile), линейная регрессия, стандартные отклонения, дисперсии, нарастающие итоги, а также прочая низшая математика и высшая бухгалтерия. :)

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

Текущая реализация

На данный момент в MS SQL Server 2005 реализованы два типа аналитических функций – агрегатные и функции ранжирования.

Агрегатные функции

Поскольку аналитическая функция возвращает агрегированный результат обработки записей, попавших в «окно», то самые обычные агрегатные функции теперь имеют возможность выступить в качестве аналитических. Отличие состоит в том, что «обычные» агрегаты уменьшают степень детализации результирующего набора, а в аналитическом варианте степень детализации не уменьшается. Это относится не только к агрегатам, но и к другим типам аналитических функций. Разберем на примере.

Допустим, у нас есть таблица с операциями клиентов, содержащая ID транзакции, ID клиента и сумму сделки, всего 15 записей...

Сравним результат выполнения двух запросов. В одном SUM выступает в качестве обычного агрегата:

А в другом уже в качестве аналитической функции:

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

Самое время разобраться с синтаксисом – он довольно прост. После функции указывается конструкция

где <value_expression> – список полей, по которым производится группировка, при этом использование алиасов или выражений не допускается. Собственно, таким образом и формируется «окно» для работы аналитической функции. В «окно» попадают все записи, сгруппированные по указанной колонке. Эта группировка делает практически то же самое, что и оператор GROUP BY, но с парой отличий. Во-первых, как уже говорилось, такая группировка производится по уже сформированной выборке, а во-вторых, она распространяется только на тот агрегат, после которого идет конструкция OVER (…), а не на все колонки. И если есть необходимость использовать две аналитические функции в одном запросе, то для каждой функции конструкция OVER (…) указывается отдельно.

Строго говоря, результат запроса с аналитической суммой полностью аналогичен результату такого запроса, написанного в «старом стиле»:

Более того, планы обоих запросов также абсолютно идентичны. Но, во-первых, запись с применением аналитических агрегатов выглядит короче и понятнее, а во вторых, не стоит забывать, что мы имеем дело все еще не с финальной версией продукта и, возможно, к релизу сервер научится их оптимизировать.

В качестве аналитических функций могут также выступать и собственноручно написанные агрегаты.

Функции ранжирования

Помимо обычных агрегатов, для аналитических запросов вводятся функции ранжирования. Эти функции возвращают ранг каждой записи внутри «окна». В общем случае рангом является некое число отражающее положение или «вес» записи относительно других записей в том же наборе. Формируется «окно» точно так же, как и в случае агрегатных функций – с помощью группировки. Однако, поскольку результат работы функций ранжирования зависит от порядка обработки записей, то обязательно должен быть указан порядок записей внутри «окна» посредством конструкции ORDER BY. В зависимости от используемой функции некоторые записи могут получать один и тот же ранг. Функции ранжирования являются не детерминированными, то есть при одних и тех же входных значениях они могут возвращать разный результат.

На данный момент имееется 4 функции ранжирования, рассмотрим их по порядку:

ROW_NUMBER()

Сбылась голубая мечта жаждущих нумерации записей на сервере. :) Теперь такая возможность появилась, однако это не основное назначение данной функции… Все-таки она призвана нумеровать записи в указанном порядке внутри «окна». Но если в конструкции OVER опустить секцию PARTITION BY, то за «окно» будет принята вся выборка – что дает возможность пронумеровать все записи в должном порядке, причем порядок нумерации может не совпадать с порядком записей в результирующей выборке, то есть оператор ORDER BY внутри OVER(…), определяющий порядок сортировки записей внутри «окна», и, соответственно, порядок нумерации записей может не совпадасть с оператором ORDER BY в конструкции SELECT, определяющей порядок выдачи записей клиенту. Нумерация всегда начинается с единицы.

RANK()

Эта функция предназначена для ранжирования записей внутри «окна», но опять-таки, если колонка для группировки не задана явным образом, то за «окно» принимается вся выборка. Рангом каждой записи является количество уже ранжированных записей с более высоким рангом, чем текущая, плюс единица. Если встретятся несколько записей с одинаковым значением, по которому производится ранжирование, то этим записям будет присвоен одинаковый ранг. Однако при этом следующая запись с новым значением получит такой ранг, как будто бы предыдущие записи получили свой уникальный номер, то есть образуется дырка.

mirznanii.com

Новые аналитические функции MS SQL 2012

С выходом новой версии СУБД Microsoft SQL Server 2012 разработчикам доступны новые аналитические функции, которые позволяют разработчикам писать более эффективные и быстрые запросы, используя гораздо меньший объем кода. Всем известна классическая задача, когда для таблицы требуется написать запрос, который возвращает в текущей строке 2 столбца, первый из которых соответствует столбцу текущей строки, а второй соответствует тому же столбцу предыдущей строки. Пусть нам надо хранить цену за определенный товар на каждый день. Построим таблицу, в которой будет столбец с датой, идентификатором товара и ценой этого товара на день: dtDate date not null, iGoodId int not null, fPrice float not null, primary key clustered ( iGoodId asc, dtDate asc ) on [PRIMARY] insert into #goods ( dtDate, iGoodId, fPrice ) ( '2014-01-01', 1, 23 ), ( '2014-01-02', 1, 24 ), ( '2014-01-03', 1, 25 ), ( '2014-01-01', 2, 13 ), ( '2014-01-02', 2, 14 ), ( '2014-01-03', 2, 15 )

Требуется написать запрос, который для каждой строки таблицы покажет цену на данный товар за предыдущий день, который есть в таблице. Обычно для решения этой задачи использовался джойн таблицы на себя с условием равенства на iGoodId, а также выполнялся поиск максимальной даты из правой таблицы, которая меньше даты из левой таблицы. Либо делалось то же самое, но через связанный подзапрос. Теперь можно просто воспользоваться функцией lag, и сделать задачу одним запросом без всяких джойнов:

select dtDate, iGoodId, fPrice CurPrice, isnull ( lag ( fPrice ) over ( partition by iGoodId order by iGoodId asc, dtDate asc ), -1 ) PrevPrice from #goods

Функция lag может, например, использоваться для поиска тех дней, когда происходили изменения в ценах на товар. Для этого можно написать такой запрос:

select dtDate, iGoodId, fPrice CurPrice, isnull ( lag ( fPrice ) over ( partition by iGoodId order by iGoodId asc, dtDate asc ), -1 ) PrevPrice select dtDate, iGoodId, CurPrice where CurPrice <> PrevPrice Если требуется получить цену не за предыдущий, а за следующий день, то используется функция lead: select dtDate, iGoodId, fPrice CurPrice, isnull ( lead ( fPrice ) over ( partition by iGoodId order by iGoodId asc, dtDate asc ), -1 ) NextPrice from #goods

Добавим теперь к таблице новый столбец с объемом продаж товара за дату:

alter table #goods add mSells money not null default ( 100 )

Теперь посчитаем нарастающий итог. То есть для каждого товара и даты посчитаем сумму продаж с начальной даты в заданной партиции до текущей даты. Это опять-таки можно сделать, используя один запрос, без всяких джойнов. Используется расширение предложения over в агрегирующей функции:

select dtDate, iGoodId, sum ( mSells ) over ( partition by iGoodId order by iGoodId asc, dtDate asc rows between unbounded preceding and current row ) from #goods

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

select dtDate, iGoodId, fPrice CurPrice, first_value ( fPrice ) over ( partition by iGoodId order by iGoodId asc, dtDate asc ) FirstPrice, last_value ( fPrice ) over ( partition by iGoodId order by iGoodId asc, dtDate asc ) LastPrice from #goods

tsql-tasks.blogspot.com

SQL | Пользовательские функции и процедуры MS SQL

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

Определить длину строки A.
Заменить в строке A все подстроки B на пустые подстроки.
Определить длину строки, полученной во втором шаге.
Из длины, полученной на первом шаге вычесть длину, полученный в третьем шаге.
Результат, полученный в четвертом шаге, поделить на длину подстроки B.

Оформим приведенный алгоритм в t-SQL в виде функции с двумя параметрами, где переменная @where – это строка A, а @what – подстрока B:

create function [dbo].[substring_cnt] (@where varchar(max), @what varchar(100)) returns int as begin return ((datalength(@where) - datalength(replace(@where, @what, ''))) / datalength(@what)) end

В своей следующей записи я покажу как реализовать приведенную в этой статье функцию в виде CLR-функции.

This entry was posted in SQL and tagged MS SQL Server, t-SQL on 2016-01-17 by Ruslan Yunusov.

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

create function [dbo].[last_charindex] (@what varchar(1000), @where varchar(1000)) returns int as begin set @where = reverse(@where) set @what = reverse(@what) return (case when charindex(@what, @where) > 0 then len(@where) - (len(@what) + charindex(@what, @where)) + 2 else 0 end) end This entry was posted in SQL and tagged MS SQL Server, t-SQL, строковые функции on 2015-04-29 by Ruslan Yunusov.

В MS SQL Server есть очень удобная и замечательная встроенная функция datediff, которая возвращает разницу между двумя датами. Например, следующий запрос вернет разницу в годах между 31-м декабря 2014 года и 1-м января 2015 года:

select datediff(year, '2014-12-31', '2015-01-01')

Результат запроса будет равен 1, хотя разница между двумя всего 1 день. Дело в том, что datediff возвращает количество пересеченных границ указанных аргументом datepart, за период времени между аргументами startdate и enddate функции.Такой результат не очень устраивает нас, если мы хотим определить количество полных лет между двумя датами, т.е. возраст на определенную дату. Немного дополним вывод этой функции и оформим в виде функции:

create function [dbo].fullAge (@startdate datetime , @enddate datetime) returns int as begin return (datediff(year, @startdate, @enddate) - case when month(@startdate) < month(@enddate) then 0 when month(@startdate) > month(@enddate) then 1 when day(@startdate) > day(@enddate) then 1 else 0 end) end

Здесь вначале находится разница в годах между двумя календарными датами функцией datediff и от неё вычитается 1, если в календарном году даты @enddate месяц и дата переменной @startdate еще не наступили относительно переменной @enddate.

This entry was posted in SQL and tagged MS SQL Server, t-SQL, функции времени on 2015-03-22 by Ruslan Yunusov.

По григорианскому календарю год считается високосным если он кратен 4, но при этом не кратен 100, либо кратен 400. С учетом этого можем написать функцию для определения “високосности” года.

create function [dbo].[isLeapYear] (@date date) returns bit as begin return (case when year(@date) % 4 = 0 and year(@date) % 100 != 0 then 1 when year(@date) % 400 = 0 then 1 else 0 end) end

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

This entry was posted in SQL and tagged MS SQL Server, t-SQL, функции времени on 2015-02-19 by Ruslan Yunusov.

Узнать количество дней в месяце в t-SQL можно таким образом:

declare @date datetime = getdate() declare @dayCount int set @dayCount = day(dateadd(dd, -day(dateadd(mm, 1, @date)), dateadd(mm, 1, @date))) select @dayCount

В этом коде количество дней в месяце определяется функцией day от последнего числа месяца. Последнее число месяца в моем случае находится добавлением к текущей дате одного месяца и “вычитанием” от неё дня месяца полученной даты. Начиная с MS SQL 2012ой версии, последнее число можно найти с использованием встроенной функции EOMonth. Т.е. необходимо третью строку в коде выше заменить следующей строкой:

set @dayCount = day(eomonth(@date))

В итоге наша функция будет выглядеть так:

create function [dbo].[monthDayCount] (@date datetime) returns int as begin return (day(dateadd(dd, -day(dateadd(mm, 1, @date)), dateadd(mm, 1, @date)))) end This entry was posted in SQL and tagged MS SQL Server, t-SQL, функции времени on 2015-02-14 by Ruslan Yunusov.

blog.selectfrom.ru