Главная страница Случайная страница
Разделы сайта
АвтомобилиАстрономияБиологияГеографияДом и садДругие языкиДругоеИнформатикаИсторияКультураЛитератураЛогикаМатематикаМедицинаМеталлургияМеханикаОбразованиеОхрана трудаПедагогикаПолитикаПравоПсихологияРелигияРиторикаСоциологияСпортСтроительствоТехнологияТуризмФизикаФилософияФинансыХимияЧерчениеЭкологияЭкономикаЭлектроника
⚡️ Для новых пользователей первый месяц бесплатно. А далее 290 руб/мес, это в 3 раза дешевле аналогов. За эту цену доступен весь функционал: напоминание о визитах, чаевые, предоплаты, общение с клиентами, переносы записей и так далее.
✅ Уйма гибких настроек, которые помогут вам зарабатывать больше и забыть про чувство «что-то мне нужно было сделать».
Сомневаетесь? нажмите на текст, запустите чат-бота и убедитесь во всем сами!
Создание пользовательских функций
|
|
Как и всякий другой язык программирования, T-SQL позволяет создавать пользовательские функции[2]. Эти функции в зависимости от типа возвращаемого значения могут быть скалярными или табличными.
Скалярные функции не могут возвращать значения с типами timestamp, text, ntext и image, а также с пользовательским типом. Скалярные пользовательские функции можно применять везде, где можно использовать возвращаемое значение встроенной функции.
На рис. 21.7 представлен шаблон скалярной функции в окне User-defined Function Properties, а на рис.21.5 — функция, заменившая этот шаблон.
Рис. 21.7
Шаблон скалярной функции в окне User-defined Function Properties
Кроме прочего, шаблон отображает синтаксис скалярной функции. Общий синтаксис пользовательской функции имеет следующий вид:
Синтаксис
скалярная функция (Scalar Function)
CREATE FUNCTION [ owner_name. ] function_name
([ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [,...n ] ])
RETURNS scalar_return_data_type
[ WITH < function_option> [ [, ]...n] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
встраиваемая табличная функция (Inline Table-valued Function) < $I[]встраиваемая табличная функция (Inline Table-valued Function)>
CREATE FUNCTION [ owner_name. ] function_name
([ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [,...n ] ])
RETURNS TABLE
[ WITH < function_option > [ [, ]...n ] ]
[ AS ]
RETURN [ (] select_stmt [) ]
многооператорная табличная функция (Multi-statement Table-valued Function) < $I[]многооператорная табличная функция (Multi-statement Table-valued Function)>
CREATE FUNCTION [ owner_name. ] function_name
([ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [,...n ] ])
RETURNS @return_variable TABLE < table_type_definition >
[ WITH < function_option > [ [, ]...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END
< function_option >:: =
{ ENCRYPTION | SCHEMABINDING }
< table_type_definition >:: =
({ column_definition | table_constraint } [,...n ])
Назначение аргументов:
owner_name — имя ID-пользователя-владельца.
function_name — имя пользовательской функции, которое должно отвечать правилам именования идентификаторов и быть уникальным для базы данных и ее владельца.
@parameter_name — параметр (аргумент) пользовательской функции, для которого можно указать значение по умолчанию (при помощи ключевого слова default). Функция может иметь до 1024 параметров. Имя параметра должно начинаться символом @.
scalar_parameter_data_type — тип данных параметра; можно использовать все скалярные типы данных, включая bigint и sql_variant. Нельзя использовать тип timestamp и определенный пользователем тип; нельзя также использовать нескалярные типы, такие как курсор и табличный тип.
scalar_return_data_type — тип данных, возвращаемых функцией; может быть любым скалярным типом, поддерживаемым SQL Server, кроме text, ntext, image и timestamp.
scalar_expression — скалярное выражение, определяющее возвращаемое значение скалярной функции.
TABLE — ключевое слово, указывающее на то, что возвращаемое значение table-valued-функции является таблицей.
Во встраиваемой табличной функции возвращаемое TABLE-значение определяется посредством единственной SELECT-инструкции. В многооператорной табличной функции @return_variable — это TABLE-переменная, используемая для сохранения и накопления возвращаемых функцией строк.
function_body — «тело» функции, содержащее набор операторов языка T-SQL; function_body используется только в скалярных и многооператорных табличных функциях. В скалярных функциях function_body — это набор операторов языка T-SQL, определяющих возвращаемое скалярное значение. В многооператорной табличной функции function_body — это набор операторов, предназначенный для заполнения возвращаемого табличного значения.
select_stmt — единственная SELECT-инструкция, определяющая возвращаемое значение во встраиваемой табличной функции.
ENCRYPTION — ключевое слово, указывающее, что SQL Server шифрует столбцы системной таблицы, содержащие текст в операторе CREATE FUNCTION. Это защищает функцию от публикаций при репликации.
SCHEMABINDING — ключевое слово, указывающее, что функция связана с объектами базы данных, на которые она ссылается. Если функция создается с опцией SCHEMABINDING, то объекты базы данных, на которые она ссылается, не могут изменяться (с использованием оператора ALTER) или удаляться (оператором DROP).
На рис. 21.8 представлена несложная скалярная пользовательская функция prc, которая возвращает общее количество товара из таблицы Запасы, используя в качестве аргумента код товара.
Рис. 21.8
Несложная скалярная пользовательская функция
На рис. 21.9 показана хранимая процедура нахождения ведомости товаров, использующая скалярную функцию prc, которая существенно упрощает код процедуры.
Рис. 21.9
Скалярная функция prc существенно упрощает код процедуры нахождения ведомости товаров
Рис. 21.10 демонстрирует вызов и результат работы процедуры, приведенной на рис. 21.9.
Рис. 21.10
Вызов и результат работы процедуры, приведенной на рис. 21.9
При написании приложений, выполняемых на сервере, также часто возникает необходимость в формировании уникальных строковых ключей. В главе «Процедуры и функции» подробно рассматривается алгоритм и приведен VB-код для решения подобной задачи. Этот же алгоритм несложно использовать для написания пользовательской функции на языке Transact-QSL 2000:
Листинг 21.4 Функция формировании уникальных строковых ключей на SQL Server
1 CREATE FUNCTION NextCode(@PrevCode as nvarchar(100))
2 RETURNS varchar(100)
3 AS
4 BEGIN
5 DECLARE @mas1 as varchar(37)
6 DECLARE @RetCode as nvarchar(100)
7 DECLARE @LenMas1 as Int
8 DECLARE @LenMasNewCode as Int
9 DECLARE @s as char, @i as Int
10 DECLARE @posi as Int
11 SET @mas1 = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
12 SET @RetCode = ''
13 SET @LenMasNewCode=LEN(@PrevCode)
14 SET @LenMas1 = Len(@mas1)
15 SET @i=@LenMasNewCode
16 WHILE @i > 0
17 BEGIN
18 SET @s = SUBSTRING(@PrevCode, @i, 1) /* текущий PrevCode-символ */
19 /*положение i-го символа в заданном диапазоне: */
20 SET @posi = CHARINDEX(@s, @mas1, 1)
21 IF @posi = @LenMas1
22 BEGIN
23 /* символ последний в диапазоне: */
24 SET @RetCode = SUBSTRING(@mas1, 1, 1) + @RetCode
25 END
26 ELSE
27 BEGIN
28 /* выбор следующего символа из диапазона
29 и окончание работы функции: */
30 SET @RetCode = SUBSTRING(@PrevCode, 1, @i - 1) +
31 SUBSTRING(@mas1, @posi + 1, 1) + @RetCode
32 SET @i = 0
33 END
34 SET @i = @i - 1
35 END
36 RETURN @RetCode /* возвращаемое значение функции */
37 END
Эта функция может использоваться при формировании строки, отличающейся от входной строки на «единицу», в процедурах, хранимых на SQL Server.
Табличная пользовательская функция возвращает таблицу. Назначение табличных функций — не в замене хранимых процедур или представлений, и они предоставляют б о льшие возможности, чем эти элементы. Подробности о табличных и многооператорных табличных функциях можно найти в справочной системе по SQL Server 2000.
|
|