Просмотр определяемых пользователем функций
Вы можете получить сведения об определении или свойствах определяемой пользователем функции в SQL Server с помощью SQL Server Management Studio или Transact-SQL. Возможность просмотреть определение функции может понадобиться, чтобы понять, как его данные извлекаются из исходных таблиц, или чтобы увидеть данные, определенные функцией.
Если имя объекта, на который ссылается функция, было изменено, необходимо изменить функцию так, чтобы в ее тексте использовалось новое имя. Поэтому, прежде чем переименовать объект, отобразите список его зависимостей, чтобы определить, отразится ли планируемое изменение на каких-либо функциях.
Разрешения
Для sys.sql_expression_dependencies поиска всех зависимостей от функции требуется разрешение VIEW DEFINITION для базы данных и разрешение sys.sql_expression_dependencies SELECT для базы данных. Определения системных объектов, например полученные в OBJECT_DEFINITION, видимы для всех.
Использование среды SQL Server Management Studio
Отображение свойств определяемой пользователем функции
- В обозревателе объектов выберите знак плюса рядом с базой данных, содержащей функцию, в которую вы хотите просмотреть свойства, а затем выберите знак плюса, чтобы развернуть папку Programmability .
- Выберите знак «плюс», чтобы развернуть папку «Функции «.
- Выберите знак плюса, чтобы развернуть папку, содержащую функцию, в которую вы хотите просмотреть свойства:
- Table-valued Function
- Скалярная функция
- Агрегатная функция
- Щелкните правой кнопкой мыши функцию, свойства которой необходимо просмотреть, и выберите пункт Свойства. Следующие свойства отображаются в диалоговом окне Свойства функции —имя_функции.
Имя функции Description База данных Имя базы данных, содержащей эту функцию. Сервер Имя текущего экземпляра сервера. Пользователь Имя пользователя этого соединения. Дата создания Дата создания функции. Выполнить как Контекст выполнения для функции. Имя Имя текущей функции. Схема Схема, которой принадлежит функция. Системный объект Указывает принадлежность функции к системным объектам. Значения: True и False . Значения NULL по стандарту ANSI Указывает, был ли объект создан с параметром ANSI NULL. Encrypted Указывает, зашифрована ли функция. Значения: True и False . Тип функции Тип определяемой пользователем функции. Заключенный в кавычки идентификатор Показывает, был ли объект создан с параметром «заключенный в кавычки идентификатор». Привязка к схеме Указывает, привязана ли функция к схеме. Возможные значения: True и False. Сведения о функциях, связанных с схемой, см. в разделе SCHEMABINDING CREATE FUNCTION (Transact-SQL).
Использование Transact-SQL
Получение определения и свойств функции
- В обозревателе объектов подключитесь к экземпляру ядра СУБД.
- На стандартной панели выберите пункт Создать запрос.
- Скопируйте один из следующих примеров и вставьте его в окне запроса, а затем нажмите Выполнить. В следующем примере кода возвращается имя функции, определение и соответствующие свойства.
USE AdventureWorks2022; GO -- Get the function name, definition, and relevant properties SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, o.type_desc, sm.definition, sm.uses_ansi_nulls, sm.uses_quoted_identifier, sm.is_schema_bound, sm.execute_as_principal_id -- using the two system tables sys.sql_modules and sys.objects FROM sys.sql_modules AS sm JOIN sys.objects AS o ON sm.object_id = o.object_id -- from the function 'dbo.ufnGetProductDealerPrice' WHERE sm.object_id = OBJECT_ID('dbo.ufnGetProductDealerPrice') ORDER BY o.type; GO В следующем примере кода возвращается определение примера функции dbo.ufnGetProductDealerPrice .
USE AdventureWorks2022; GO -- Get the definition of the function dbo.ufnGetProductDealerPrice SELECT OBJECT_DEFINITION (OBJECT_ID('dbo.ufnGetProductDealerPrice')) AS ObjectDefinition; GO Получение зависимостей функции
- В обозревателе объектов подключитесь к экземпляру ядра СУБД.
- На стандартной панели выберите пункт Создать запрос.
- Скопируйте приведенный ниже пример в окно запроса и нажмите кнопку Выполнить.
USE AdventureWorks2022; GO -- Get all of the dependency information SELECT OBJECT_NAME(sed.referencing_id) AS referencing_entity_name, o.type_desc AS referencing_desciption, COALESCE(COL_NAME(sed.referencing_id, sed.referencing_minor_id), '(n/a)') AS referencing_minor_id, sed.referencing_class_desc, sed.referenced_class_desc, sed.referenced_server_name, sed.referenced_database_name, sed.referenced_schema_name, sed.referenced_entity_name, COALESCE(COL_NAME(sed.referenced_id, sed.referenced_minor_id), '(n/a)') AS referenced_column_name, sed.is_caller_dependent, sed.is_ambiguous -- from the two system tables sys.sql_expression_dependencies and sys.object FROM sys.sql_expression_dependencies AS sed INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id -- on the function dbo.ufnGetProductDealerPrice WHERE sed.referencing_id = OBJECT_ID('dbo.ufnGetProductDealerPrice'); GO Определяемые пользователем функции
В языках программирования обычно имеется два типа подпрограмм:
- хранимые процедуры;
- определяемые пользователем функции (UDF).
Как уже было рассмотрено в предыдущей статье, хранимые процедуры состоят из нескольких инструкций и имеют от нуля до нескольких входных параметров, но обычно не возвращают никаких параметров. В отличие от хранимых процедур, функции всегда возвращают одно значение. В этом разделе мы рассмотрим создание и использование .
Создание и выполнение определяемых пользователем функций
Определяемые пользователем функции создаются посредством инструкции CREATE FUNCTION, которая имеет следующий синтаксис:
Параметр schema_name определяет имя схемы, которая назначается владельцем создаваемой UDF, а параметр function_name определяет имя этой функции. Параметр @param является входным параметром функции (формальным аргументом), чей тип данных определяется параметром type. Параметры функции — это значения, которые передаются вызывающим объектом определяемой пользователем функции для использования в ней. Параметр default определяет значение по умолчанию для соответствующего параметра функции. (Значением по умолчанию также может быть NULL.)
Предложение RETURNS определяет тип данных значения, возвращаемого UDF. Это может быть почти любой стандартный тип данных, поддерживаемый системой баз данных, включая тип данных TABLE. Единственным типом данных, который нельзя указывать, является тип данных timestamp.
Определяемые пользователем функции могут быть либо скалярными, либо табличными. Скалярные функции возвращают атомарное (скалярное) значение. Это означает, что в предложении RETURNS скалярной функции указывается один из стандартных типов данных. Функция является табличной, если предложение RETURNS возвращает набор строк.
Параметр WITH ENCRYPTION в системном каталоге кодирует информацию, содержащую текст инструкции CREATE FUNCTION. Таким образом, предотвращается несанкционированный просмотр текста, который был использован для создания функции. Данная опция позволяет повысить безопасность системы баз данных.
Альтернативное предложение WITH SCHEMABINDING привязывает UDF к объектам базы данных, к которым эта функция обращается. После этого любая попытка модифицировать объект базы данных, к которому обращается функция, претерпевает неудачу. (Привязка функции к объектам базы данных, к которым она обращается, удаляется только при изменении функции, после чего параметр SCHEMABINDING больше не задан.)
Для того чтобы во время создания функции использовать предложение SCHEMABINDING, объекты базы данных, к которым обращается функция, должны удовлетворять следующим условиям:
- все представления и другие UDF, к которым обращается определяемая функция, должны быть привязаны к схеме;
- все объекты базы данных (таблицы, представления и UDF) должны быть в той же самой базе данных, что и определяемая функция.
Параметр block определяет блок BEGIN/END, содержащий реализацию функции. Последней инструкцией блока должна быть инструкция RETURN с аргументом. (Значением аргумента является возвращаемое функцией значение.) Внутри блока BEGIN/END разрешаются только следующие инструкции:
- инструкции присвоения, такие как SET;
- инструкции для управления ходом выполнения, такие как WHILE и IF;
- инструкции DECLARE, объявляющие локальные переменные;
- инструкции SELECT, содержащие списки столбцов выборки с выражениями, значения которых присваиваются переменным, являющимися локальными для данной функции;
- инструкции INSERT, UPDATE и DELETE, которые изменяют переменные с типом данных TABLE, являющиеся локальными для данной функции.
По умолчанию инструкцию CREATE FUNCTION могут использовать только члены предопределенной роли сервера sysadmin и предопределенной роли базы данных db_owner или db_ddladmin. Но члены этих ролей могут присвоить это право другим пользователям с помощью инструкции GRANT CREATE FUNCTION.
В примере ниже показано создание функции ComputeCosts:
USE SampleDb; -- Эта функция вычисляет возникающие дополнительные общие затраты, -- при увеличении бюджетов проектов GO CREATE FUNCTION ComputeCosts (@percent INT = 10) RETURNS DECIMAL(16, 2) BEGIN DECLARE @addCosts DEC (14,2), @sumBudget DEC(16,2) SELECT @sumBudget = SUM (Budget) FROM Project SET @addCosts = @sumBudget * @percent/100 RETURN @addCosts END;Функция ComputeCosts вычисляет дополнительные расходы, возникающие при увеличении бюджетов проектов. Единственный входной параметр, @percent, определяет процентное значение увеличения бюджетов. В блоке BEGIN/END сначала объявляются две локальные переменные: @addCosts и @sumBudget, а затем с помощью инструкции SELECT переменной @sumBudget присваивается общая сумма всех бюджетов. После этого функция вычисляет общие дополнительные расходы и посредством инструкции RETURN возвращает это значение.
Вызов определяемой пользователем функции
Определенную пользователем функцию можно вызывать с помощью инструкций Transact-SQL, таких как SELECT, INSERT, UPDATE или DELETE. Вызов функции осуществляется, указывая ее имя с парой круглых скобок в конце, в которых можно задать один или несколько аргументов. Аргументы — это значения или выражения, которые передаются входным параметрам, определяемым сразу же после имени функции. При вызове функции, когда для ее параметров не определены значения по умолчанию, для всех этих параметров необходимо предоставить аргументы в том же самом порядке, в каком эти параметры определены в инструкции CREATE FUNCTION.
В примере ниже показан вызов функции ComputeCosts в инструкции SELECT:
USE SampleDb; -- Вернет проект "p2 - Gemini" SELECT Number, ProjectName FROM Project WHERE Budget < dbo.ComputeCosts(25);Инструкция SELECT в примере отображает названия и номера всех проектов, бюджеты которых меньше, чем общие дополнительные расходы по всем проектам при заданном значении процентного увеличения.
В инструкциях Transact-SQL имена функций необходимо задавать, используя имена, состоящие из двух частей: schema name и function name, поэтому в примере мы использовали префикс схемы dbo.
Возвращающие табличное значение функции
Как уже упоминалось ранее, функция является возвращающей табличное значение, если ее предложение RETURNS возвращает набор строк. В зависимости от того, каким образом определено тело функции, возвращающие табличное значение функции классифицируются как встраиваемые (inline) и многоинструкционные (multistatement). Если в предложении RETURNS ключевое слово TABLE указывается без сопровождающего списка столбцов, такая функция является встроенной. Инструкция SELECT встраиваемой функции возвращает результирующий набор в виде переменной с типом данных TABLE.
Многоинструкционная возвращающая табличное значение функция содержит имя, определяющее внутреннюю переменную с типом данных TABLE. Этот тип данных указывается ключевым словом TABLE, которое следует за именем переменной. В эту переменную вставляются выбранные строки, и она служит возвращаемым значением функции.
Создание возвращающей табличное значение функции показано в примере ниже:
USE SampleDb; GO CREATE FUNCTION EmployeesInProject (@projectNumber CHAR(4)) RETURNS TABLE AS RETURN (SELECT FirstName, LastName FROM Works_on, Employee WHERE Employee.Id = Works_on.EmpId AND ProjectNumber = @projectNumber)Функция EmployeesInProject отображает имена всех сотрудников, работающих над определенным проектом, номер которого задается входным параметром @projectNumber. Тогда как функция в общем случае возвращает набор строк, предложение RETURNS в определение данной функции содержит ключевое слово TABLE, указывающее, что функция возвращает табличное значение. (Обратите внимание на то, что в примере блок BEGIN/END необходимо опустить, а предложение RETURN содержит инструкцию SELECT.)
Использование функции Employees_in_Project приведено в примере ниже:
USE SampleDb; SELECT * FROM EmployeesInProject('p3')
Возвращающие табличное значение функции и инструкция APPLY
Реляционная инструкция APPLY позволяет вызывать возвращающую табличное значение функцию для каждой строки табличного выражения. Эта инструкция задается в предложении FROM соответствующей инструкции SELECT таким же образом, как и инструкция JOIN. Инструкция APPLY может быть объединена с табличной функцией для получения результата, похожего на результирующий набор операции соединения двух таблиц. Существует две формы инструкции APPLY:
- CROSS APPLY
- OUTER APPLY
Инструкция CROSS APPLY возвращает те строки из внутреннего (левого) табличного выражения, которые совпадают с внешним (правым) табличным выражением. Таким образом, логически, инструкция CROSS APPLY функционирует так же, как и инструкция INNER JOIN.
Инструкция OUTER APPLY возвращает все строки из внутреннего (левого) табличного выражения. (Для тех строк, для которых нет совпадений во внешнем табличном выражении, он содержит значения NULL в столбцах внешнего табличного выражения.) Логически, инструкция OUTER APPLY эквивалентна инструкции LEFT OUTER JOIN.
Применение инструкции APPLY показано в примерах ниже:
USE SampleDb; GO -- Создать функцию CREATE FUNCTION GetJob(@empid AS INT) RETURNS TABLE AS RETURN SELECT Job FROM Works_on WHERE EmpId = @empid AND Job IS NOT NULL AND ProjectNumber = 'p1';Функция GetJob() возвращает набор строк с таблицы Works_on. В примере ниже этот результирующий набор "соединяется" предложением APPLY с содержимым таблицы Employee:
USE SampleDb; -- Используется CROSS APPLY SELECT E.Id, FirstName, LastName, Job FROM Employee as E CROSS APPLY GetJob(E.Id) AS A -- Используется OUTER APPLY SELECT E.Id, FirstName, LastName, Job FROM Employee as E OUTER APPLY GetJob(E.Id) AS AРезультатом выполнения этих двух функций будут следующие две таблицы (отображаются после выполнения второй функции):
В первом запросе примера результирующий набор табличной функции GetJob() "соединяется" с содержимым таблицы Employee посредством инструкции CROSS APPLY. Функция GetJob() играет роль правого ввода, а таблица Employee - левого. Выражение правого ввода вычисляется для каждой строки левого ввода, а полученные строки комбинируются, создавая конечный результат.
Второй запрос похожий на первый (но в нем используется инструкция OUTER APPLY), который логически соответствует операции внешнего соединения двух таблиц.
Возвращающие табличное значение параметры
Во всех версиях сервера, предшествующих SQL Server 2008, задача передачи подпрограмме множественных параметров была сопряжена со значительными сложностями. Для этого сначала нужно было создать временную таблицу, вставить в нее передаваемые значения, и только затем можно было вызывать подпрограмму. Начиная с версии SQL Server 2008, эта задача упрощена, благодаря возможности использования возвращающих табличное значение параметров, посредством которых результирующий набор может быть передан соответствующей подпрограмме.
Использование возвращающего табличное значение параметра показано в примере ниже:
USE SampleDb; CREATE TYPE departmentType AS TABLE (Number CHAR(4), DepartmentName CHAR(40), Location CHAR(40)); GO CREATE TABLE #moscowTable (Number CHAR(4), DepartmentName CHAR(40), Location CHAR(40)); GO CREATE PROCEDURE InsertProc @Moscow departmentType READONLY AS SET NOCOUNT ON INSERT INTO #moscowTable (Number, DepartmentName, Location) SELECT * FROM @Moscow GO DECLARE @Moscow AS departmentType; INSERT INTO @Moscow (Number, DepartmentName, Location) SELECT * FROM department WHERE location = 'Москва'; EXEC InsertProc @Moscow;В этом примере сначала определяется табличный тип departmentType. Это означает, что данный тип является типом данных TABLE, вследствие чего он разрешает вставку строк. В процедуре InsertProc объявляется переменная @Moscow с типом данных departmentType. (Предложение READONLY указывает, что содержимое этой таблицы нельзя изменять.) В последующем пакете в эту табличную переменную вставляются данные, после чего процедура запускается на выполнение. В процессе исполнения процедура вставляет строки из табличной переменной во временную таблицу #moscowTable. Вставленное содержимое временной таблицы выглядит следующим образом:
Использование возвращающих табличное значение параметров предоставляет следующие преимущества:
- упрощается модель программирования подпрограмм;
- уменьшается количество обращений к серверу и получений соответствующих ответов;
- таблица результата может иметь произвольное количество строк.
Изменение структуры определяемых пользователями инструкций
Язык Transact-SQL также поддерживает инструкцию ALTER FUNCTION, которая модифицирует структуру определяемых пользователями инструкций (UDF). Эта инструкция обычно используется для удаления привязки функции к схеме. Все параметры инструкции ALTER FUNCTION имеют такое же значение, как и одноименные параметры инструкции CREATE FUNCTION.
Для удаления UDF применяется инструкция DROP FUNCTION. Удалить функцию может только ее владелец или член предопределенной роли db_owner или sysadmin.
Определяемые пользователем функции и среда CLR
В предыдущей статье мы рассмотрели способ создания хранимых процедур из управляемого кода среды CLR на языке C#. Этот подход можно использовать и для определяемых пользователем функций (UDF), с одним только различием, что для сохранения UDF в виде объекта базы данных используется инструкция CREATE FUNCTION, а не CREATE PROCEDURE. Кроме этого, определяемые пользователем функции также применяются в другом контексте, чем хранимые процедуры, поскольку UDF всегда возвращают значение.
В примере ниже показан исходный код определяемых пользователем функций (UDF), реализованный на языке C#:
using System.Data.SqlTypes; public class BudgetPercent < private const float percent = 12; public static SqlDouble ComputeBudget(float budget) < return budget * percent; >>В исходном коде определяемых пользователем функций в примере вычисляется новый бюджет проекта, увеличивая старый бюджет на определенное количество процентов. Вы можете использовать инструкцию CREATE ASSEMBLY для создания сборки CLR в базе данных, как это было показано ранее. Если вы прорабатывали примеры из предыдущей статьи и уже добавили сборку CLRStoredProcedures в базу данных, то вы можете обновить эту сборку, после ее перекомпиляции с новым классом (CLRStoredProcedures это имя моего проекта классов C#, в котором я добавлял определение хранимых процедур и функций, у вас сборка может называться иначе):
USE SampleDb; GO ALTER ASSEMBLY CLRStoredProcedures FROM 'D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll' WITH PERMISSION_SET = SAFEИнструкция CREATE FUNCTION в примере ниже сохраняет метод ComputeBudget в виде объекта базы данных, который в дальнейшем можно использовать в инструкциях для манипулирования данными.
USE SampleDb; GO CREATE FUNCTION RecomputeBudget (@budget Real) RETURNS FLOAT AS EXTERNAL NAME CLRStoredProcedures.BudgetPercent.ComputeBudgetИспользование одной из таких инструкций, инструкции SELECT, показано в примере ниже:
USE SampleDb; -- Вернет 4098 SELECT dbo.RecomputeBudget (341.5);Определяемую пользователем функцию можно поместить в разных местах инструкции SELECT. В примерах выше она вызывалась в предложениях WHERE, FROM и в списке выбора оператора SELECT.
SQL-Ex blog
В предыдущих статьях этой серии я делал упор на создании объектов базы данных, которые вы могли использовать, чтобы начать работать с MySQL. Вы узнали как построить исходную базу данных, а затем добавить в нее таблицы, представления и хранимые процедуры. В этой статье я обсуждаю еще один важный тип объекта - хранимую функцию, процедуру, которая хранится в базе данных и может вызываться по требованию, аналогично пользовательской скалярной функции в SQL Server или в других системах баз данных.
Хранимые функции работают во многом сходно с встроенными функциям MySQL. Вы можете вызвать в выражении функцию любого типа, например, в таких предложениях запроса, как SELECT, WHERE или ORDER BY. Например, вы могли бы использовать встроенную функцию CAST в предложении SELECT для преобразовании типа данных столбца, в частности, CAST(plane_id AS CHAR). Выражение преобразует столбец plane_id (целочисленный) к символьному типу данных. В том же стиле вы можете использовать хранимую функцию в выражении, применяя собственную логику к столбцу plane_id или любому другому столбцу.
- Хранимые функции. Функции, которые вы создаете как объекты базы данных с помощью оператора CREATE FUNCTION.
- Подгружаемые функции. Функции, которые компилируются как библиотечные файлы, а затем загружаются на сервер динамически при выполнении оператора CREATE FUNCTION.
- Естественные функции. Функции, которые добавляются на сервер путем модификации исходного кода MySQL и компиляции его в mysqld.
Подготовка среды MySQL
Как и ранее, примеры в этой статье будут основаны на базе данных travel. Вы уже должны иметь её, в этом случае пропустите данный раздел. Если нет, начните с выполнения следующего скрипта SQL для создания базы данных и таблиц в ней:
DROP DATABASE IF EXISTS travel;
CREATE DATABASE travel;
USE travel;
CREATE TABLE manufacturers (
manufacturer_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
manufacturer VARCHAR(50) NOT NULL,
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_update TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (manufacturer_id) )
ENGINE=InnoDB AUTO_INCREMENT=1001;
CREATE TABLE airplanes (
plane_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
plane VARCHAR(50) NOT NULL,
manufacturer_id INT UNSIGNED NOT NULL,
engine_type VARCHAR(50) NOT NULL,
engine_count TINYINT NOT NULL,
max_weight MEDIUMINT UNSIGNED NOT NULL,
wingspan DECIMAL(5,2) NOT NULL,
plane_length DECIMAL(5,2) NOT NULL,
parking_area INT GENERATED ALWAYS AS
((wingspan * plane_length)) STORED,
icao_code CHAR(4) NOT NULL,
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_update TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (plane_id),
CONSTRAINT fk_manufacturer_id FOREIGN KEY (manufacturer_id)
REFERENCES manufacturers (manufacturer_id) )
ENGINE=InnoDB AUTO_INCREMENT=101;
Таблица airplanes имеет внешний ключ, который ссылается на таблицу manufacturers, поэтому вы должны создавать таблицы в указанном здесь порядке. После создания таблиц вы можете добавить в них некоторые данные, чтобы можно было протестировать вашу функцию. Для заполнения таблиц выполните следующие операторы INSERT:
INSERT INTO manufacturers (manufacturer)
VALUES ('Airbus'), ('Beechcraft'), ('Piper');
INSERT INTO airplanes
(plane, manufacturer_id, engine_type, engine_count,
max_weight, wingspan, plane_length, icao_code)
VALUES
('A380-800', 1001, 'jet', 4, 1267658, 261.65, 238.62, 'A388'),
('A319neo Sharklet', 1001, 'jet', 2, 166449, 117.45, 111.02, 'A319'),
('ACJ320neo (Corporate Jet version)', 1001, 'jet', 2, 174165,
117.45, 123.27, 'A320'),
('A300-200 (A300-C4-200, F4-200)', 1001, 'jet', 2, 363760, 147.08,
175.50, 'A30B'),
('Beech 390 Premier I, IA, II (Raytheon Premier I)', 1002, 'jet',
2, 12500, 44.50, 46.00, 'PRM1'),
('Beechjet 400 (from/same as MU-300-10 Diamond II)', 1002, 'jet',
2, 15780, 43.50, 48.42, 'BE40'),
('1900D', 1002, 'Turboprop', 2,17120, 57.75, 57.67, 'B190'),
('PA-24-400 Comanche', 1003, 'piston', 1, 3600, 36.00, 24.79, 'PA24'),
('PA-46-600TP Malibu Meridian, M600', 1003, 'Turboprop', 1, 6000,
43.17, 29.60, 'P46T'),
('J-3 Cub', 1003, 'piston', 1, 1220, 38.00, 22.42, 'J3');
Как и в случае с операторами CREATE TABLE, вы должны выполнять операторы INSERT в указанном порядке, чтобы не нарушать ограничение внешнего ключа на таблице airplanes. Теперь мы можем начать создавать хранимые функции.
Создание хранимой функции в MySQL
Чтобы добавить хранимую функцию в базу данных MySQL, вы можете использовать оператор CREATE FUNCTION. Этот оператор подобен в некоторых аспектах оператору CREATE PROCEDURE. В обоих случаях вы должны задать имя объекта и определить тело процедуры. Вы можете также включить необязательное предложение DEFINER, одну или более характеристик и один или более параметров.
- Хранимая функция может возвращать только одно значение, в то время как хранимая процедура может вернуть несколько значений или целый результирующий набор.
- Хранимая функция поддерживает только входные параметры. Хранимая процедура поддерживает параметры IN, OUT и INOUT в любом сочетании.
- Хранимая функция может включать предложение RETURNS в определении перед телом функции. Это предложение указывает тип данных возвращаемого функцией значения. Хранимые процедуры не имеют этого предложения.
- Тело хранимой функции должно включать оператор RETURN, который указывает возвращаемое значение функции. Тело функции не должно включать никаких других операторов за исключением оператора RETURN. Если оно включает другие операторы, только оператор RETURN может возвращать значение.
DELIMITER //
CREATE FUNCTION lbs_to_kg(lbs MEDIUMINT UNSIGNED)
RETURNS MEDIUMINT UNSIGNED
DETERMINISTIC
BEGIN
RETURN (lbs * 0.45359237);
END//
DELIMITER ;
Функция называется lbs_to_kg и включает один входной параметр с именем lbs. Вы не обязаны включать параметр при определении функции, но обычно вы захотите иметь хотя бы один. Если вы добавляете больше одного, необходимо разделять их запятыми.
Определение параметров заключается в скобки и включает тип данных параметра, MEDIUMINT UNSIGNED. Я выбрал этот тип данных, поскольку я в конечном итоге хочу использовать эту функцию для столбца max_weight в таблице airplanes, который также определен с этим типом данных.
Кроме того, я использовал тип данных MEDIUMINT UNSIGNED в предложении RETURNS. Предложение указывает, что возвращаемое функцией значение должно быть целым в диапазоне, допускаемым этим типом данных. Я выяснил, что этот тип данных надежен, поскольку один фунт эквивалентен 0.45359237 килограммов, поэтому возвращаемое значение не сможет превзойти максимальное значение в столбце max_weight.
Если вы захотите поддерживать более широкий диапазон значений, можете вместо этого использовать тип данных INT или BIGINT для параметра lbs и предложения RETURNS. Это обеспечит вам большую гибкость, если вы захотите использовать функцию для преобразования значений, превосходящих значения в столбце max_weight.
За предложением RETURNS следует характеристика DETERMINISTIC. Характеристика - это один из нескольких вариантов, которые вы можете добавить в определение функции и которые по разному влияют на поведение функции. Например, вы можете добавить характеристику для указания языка тела функции или определения его природы. Это те же самые характеристики, которые могут использоваться в хранимых процедурах.
Характеристика DETERMINISTIC указывает, что функция будет возвращать одни и те же результаты для одних и тех же входных параметров при каждом вызове функции. По умолчанию функция считается недетерминистической, если не указано обратное. Использование характеристики DETERMINISTIC может помочь оптимизатору выбрать лучший план выполнения. Однако назначение этой характеристики для недетерминистической функции может привести оптимизатор к некорректному выбору.
Тело функции идет после перечисленных характеристик. В нашем случае я использовал синтаксис BEGIN…END для установки составного оператора, хотя здесь имеется только один оператор RETURN. Часто ваш код будет включать составной оператор - блок одного или нескольких операторов SQL - и я хочу быть уверенным, что вы понимаете, как включить их в определение функции. Как и для хранимых процедур, ничего необычного нет в том, что разработчики используют составной оператор, даже если он включает единственный оператор SQL.
Оператор RETURN определяет простое математическое выражение, которое умножает значение входного параметра lbs на 0.45359237 для получения числа килограммов для заданного веса. Результат этих вычислений является возвращаемым значением функции при ее выполнении.
Предыдущий пример также включает два оператора DELIMITER, которые окружают определение функции. Первый оператор DELIMITER изменяет разделитель на двойной прямой слэш (//), а второй оператор DELIMITER меняет разделитель обратно на точку с запятой (которая принимается по умолчанию). Как вы видели в предыдущей статье, это позволяет передать на сервер все определение функции как единый оператор.
Проверка вновь созданной хранимой функции
После выполнения оператора CREATE FUNCTION вы можете проверить, что функция была добавлена в базу данных travel, обратившись к навигатору, как показано на Рис.1. (Вам может потребоваться обновить навигатор, чтобы увидеть новую функцию.)
Рис.1 Просмотр функции в навигаторе
Из навигатора вы можете открыть определение функции на вкладке Routine, щелкнув по иконке с гаечным ключом рядом с именем функции. На рис.2 показано определение функции на вкладке Routine. Оператор CREATE FUNCTION почти идентичен тому, который вы создали, за исключением добавления предложения DEFINER после ключевого слова CREATE.
Рис.2 Просмотр определения функции на вкладке Routine
Как и в случае с представлениями и хранимыми процедурами, предложение DEFINER указывает, какой аккаунт был назначен в качестве создателя объекта. Я выполнял оператор CREATE FUNCTION, когда был зарегистрирован под аккаунтом root на моем экземпляре локальной MySQL, поэтому это имя пользователя было добавлено в определение. По умолчанию MySQL использует аккаунт пользователя, который выполняет оператор CREATE FUNCTION, но вы можете указать другой аккаунт, если он имеет соответствующие разрешения.
Вы могли обратить внимание, что определение функции на вкладке Routine не имеет операторов DELIMITER или пользовательского разделителя. Однако если бы вы обновили определение и щелкнули Apply, Workbench добавил бы эти элементы за вас. (Могли также добавиться оператор DROP function, который необходимо выполнить перед оператором CREATE FUNCTION.)
Другим способом проверки, что функция была создана, является запрос к представлению routines в базе данных INFORMATION_SCHEMA:
SELECT * FROM information_schema.routines
WHERE routine_schema = 'travel';
Результаты должны включать функцию lbs_to_kg вместе с другими хранимыми процедурами и хранимыми функциями, которые были созданы в базе данных. Если вы используете установку из последней статьи, ваша база данных может включать хранимую процедуру get_plane_info.
В предыдущем примере я включил предложение WHERE, которое ограничивает результаты базой данных travel. Однако вы можете еще более ограничить результаты, указав также имя функции в предложении WHERE, и какой столбец или столбцы будут возвращаться. Например, следующий оператор SELECT ограничивает результаты столбцом routine_definition и функцией lbs_to_kg в базе данных travel:
SELECT routine_definition
FROM information_schema.routines
WHERE routine_schema = 'travel'
AND routine_name = 'lbs_to_kg';
Теперь оператор должен вернуть единственное значение, хотя его может быть трудно прочитать. Как и в случае с хранимыми процедурами, вы можете просмотреть значение целиком в отдельном окне. Выполните щелчок правой кнопкой на значении непосредственно в результатах и щелкните Open Value in Viewer. MySQL откроет окно, в котором будет показано значение, как на рис.3. (Выберите вкладку Text, если она еще не выбрана.)
Рис.3 Проверка тела функции в просмотрщике
Как видно, в окне выводится только тело функции, которое в нашем случае представляет составной оператор, включающий оператор RETURN.
Использование хранимых процедур в запросе MySQL
После проверки создания вашей функции вам следует проверить, что она работает как предполагалось. Один способ - это создать простой оператор SELECT, который только лишь вызывает функцию lbs_to_kg. Например, следующий оператор SELECT включает только предложение SELECT, и это предложение содержит только одно выражение:
SELECT lbs_to_kg(132) AS max_kg;
Выражение вызывает функцию lbs_to_kg, передавая значение параметра 132. Выражение также дает имя выходному столбцу (max_kg). Оператор должен вернуть значение 60.
Во многих случаях вы захотите использовать хранимую функцию не только для того, чтобы выполнить простой оператор SELECT. Например, вы можете использовать хранимую функцию для преобразования или увеличения значений столбца при извлечении данных, что я сделал следующим оператором SELECT:
SELECT a.plane, max_weight AS max_lbs,
lbs_to_kg(max_weight) AS max_kg
FROM airplanes a INNER JOIN manufacturers m
ON a.manufacturer_id = m.manufacturer_id
WHERE m.manufacturer = 'airbus'
ORDER BY a.plane;
Оператор соединяет таблицы airplanes и manufacturers по столбцу manufacturer_id в каждой таблице. Предложение SELECT оператора включает выражение, которое использует функцию lbs_to_kg для преобразования столбца max_weight в килограммы и возвращения столбца с именем max_kg. Возвращаемые оператором результаты показаны на рис.4.
Рис.4 Использование хранимой функции в запросе
Результаты включают исходный вес (в фунтах) в столбце max_lbs и вес в килограммах в столбце max_kg после преобразования значений max_weight. Включив оба веса, вы можете быстро сравнить их, чтобы получить общее представление о том, что функция возвращает ожидаемые результаты.
Обновление хранимой функции в MySQL
Как говорилось ранее, хранимые функции MySQL несколько подобны хранимым процедурам. Например, они обе поддержвают характеристики, входные параметры и предложение DEFINER. Есть еще одно важное сходство. Вы можете менять только характеристики. Вы не можете изменить тело ли другие элементы оператора. Вместо этого вы должны сначала удалить функцию, а затем снова создать её, вводя новые элементы.
Чтобы удалить хранимую процедуру, вы можете использовать оператор DROP FUNCTION, как показано в следующем примере:
DROP FUNCTION IF EXISTS lbs_to_kg;
Предложение IF EXISTS не является обязательным, но это удобный способ избежать генерации ошибок при попытке удалить несуществующую функцию. Это предложение особенно полезно, когда вы разрабатываете схему базы данных и регулярно обновляете объекты.
После удаления функции вы можете модифицировать определение под новые требования. Например, следующий оператор CREATE FUNCTION снова создает функцию lbs_to_kg, но теперь добавляет оператор DECLARE и конструкцию IF в составной оператор:
DELIMITER //
CREATE FUNCTION lbs_to_kg(lbs MEDIUMINT UNSIGNED)
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
DECLARE msg VARCHAR(50);
IF lbs > 999999 THEN SET msg =
CONCAT(ROUND((lbs * 0.45359237), 0),
' kg exceeds airport weight limits.');
ELSEIF lbs >= 100000 AND lbs CONCAT(ROUND((lbs * 0.45359237), 0),
' kg exceeds runway weight limits.');
ELSE SET msg = CONCAT(ROUND((lbs * 0.45359237), 0),
' kg within weight limits.');
END IF;
RETURN msg;
END//
DELIMITER ;
Оператор DECLARE объявляет локальную переменную msg и назначает ей тип данных VARCHAR. Обратите внимание, что предложение RETURNS также обновилось, чтобы соответствовать по типу переменной msg. Эта переменная затем может использоваться в финальном операторе RETURN для формированя значения, возвращаемого функцией.
Составной оператор также включает оператор IF. Оператор начинается с предложения начального условия, за которым следует предложение ELSEIF, а затем предложение ELSE. Каждое предложение применяет одну и ту же логику на основе входного параметра lbs. Если значение lbs попадает в заданный диапазон, переменная msg устанавливается в предопределенное значение для этого диапазона. (Мы обсудим условные операторы более подробно в следующих статьях.)
Значение msg сначала определяется преобразованием значения lbs в килограммы, а затем конкатенацией результатов со строкой (тело сообщения). Например, если значение lbs больше чем 99999, то переменная msg устанавливается в число килограммов плюс сообщение ' kg exceeds the airport weight limits.' ( kg превышает предел по весу для аэропорта).
Чтобы реализовать эту логику, каждое условное предложение включает также две встроенные функции: ROUND и CONCAT. Функция ROUND округляет килограммы до целого числа, а функция CONCAT соединяет округленные килограммы с указанным текстом. Напрмер, если вес в фунтах составляет 120000, оператор IF установит значение переменной в '54431 kg exceeds runway weight limits.' (54431 кг превышает пределы по весу для взлетной полосы). Вы сами можете увидеть это, выполнив следующий оператор SELECT:
SELECT lbs_to_kg(120000) AS max_kg;
Оператор должен вернуть результаты, показанные на рис.5.
Рис.5 Просмотр результатов, возвращаемых обновленной хранимой процедурой
Вы можете также использовать функцию lbs_to_kg в более сложном операторе SELECT, точно так же, как вы делали ранее:
SELECT m.manufacturer, a.plane,
max_weight AS max_lbs,
lbs_to_kg(max_weight) AS max_kg
FROM airplanes a INNER JOIN manufacturers m
ON a.manufacturer_id = m.manufacturer_id
ORDER BY m.manufacturer, a.plane;
Теперь каждая из возвращаемых строк включает одно из трех сообщений в столбце max_kg. Сообщение основано на числе фунтов в столбце max_weight, которое передается в функцию через параметр. На рис.6 показаны результаты, возвращаемые оператором SELECT.
Рис.6 Использоване хранимой функции в выражениях запроса
Конечно, ваша функция может содержать значительно более сложное тело, чем то, которое я написал здесь, но от этих примеров только требуется дать вам понимание того, что вы можете делать с помощью хранимых функций, и насколько полезными они могут быть при построении запросов.
Изменение хранимой функции в MySQL
Как упоминалось ранее, единственными элементами определения хранимой функции, которые вы можете изменить, являются характеристики. Для этого вы можете использовать оператор ALTER FUNCTION. Например, следующий оператор добавляет характристику COMMENT и характеристику SQL SECURITY:
ALTER FUNCTION lbs_to_kg
COMMENT 'converts weight to kilograms and generates message'
SQL SECURITY INVOKER;
Характеристика COMMENT просто добавляет комментарий, который описывает назначение функции. Характеристика SQL SECURITY предписывает MySQL выполнять код в контексте безопасности аккаунта пользователя, который вызывает функцию, а не использование аккаунта определителя (значение по умолчанию).
После выполненя оператора ALTER FUNCTION вы можете проверить, что характеристики были добавлены, просмотром определения функции на вкладке Routine, что показано на рис.7.
Рис.7 Просмотр определения хранимой функции на вкладке Routine
Оператор CREATE FUNCTION теперь включает три характеристики - одну исходную и две, добавленные при выполнении оператора ALTER FUNCTION.
Работа с хранимыми функциям в MySQL
Хотя хранимые функции похожи на хранимые процедуры, они предназначены совершенно для других целей: для возврата значения, которое может использоваться выражением при расчетах. По этой причине хранимые функции могут быть исключительно полезны и достойны для включения в ваш арсенал инструментов, особенно в силу того, что они легко создаются и выполняются. Однако они могут также повлиять на производительность, если плохо реализованы. Например, если ваш запрос возвращает тысячи строк, то чересчур сложная функция может уронить вашу систему, т.к. MySQL пытается применить логику функции к каждой строке. При надлежащем использовании хранимые функции могут стать невероятно выгодными, особенно если вы станете более искусными в построении запросов SQL.
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
Как посмотреть тело функции в sql
SQL-функции выполняют произвольный список операторов SQL и возвращают результат последнего запроса в списке. В простом случае (не с множеством) будет возвращена первая строка результата последнего запроса. (Помните, что понятие « первая строка » в наборе результатов с несколькими строками определено точно, только если присутствует ORDER BY .) Если последний запрос вообще не вернёт строки, будет возвращено значение NULL.
Кроме того, можно объявить SQL-функцию как возвращающую множество (то есть, несколько строк), указав в качестве возвращаемого типа функции SETOF некий_тип , либо объявив её с указанием RETURNS TABLE( столбцы ) . В этом случае будут возвращены все строки результата последнего запроса. Подробнее это описывается ниже.
Тело SQL-функции должно представлять собой список SQL-операторов, разделённых точкой с запятой. Точка с запятой после последнего оператора может отсутствовать. Если только функция не объявлена как возвращающая void , последним оператором должен быть SELECT , либо INSERT , UPDATE или DELETE с предложением RETURNING .
Любой набор команд на языке SQL можно скомпоновать вместе и обозначить как функцию. Помимо запросов SELECT , эти команды могут включать запросы, изменяющие данные ( INSERT , UPDATE и DELETE ), а также другие SQL-команды. (В SQL -функциях нельзя использовать команды управления транзакциями, например COMMIT , SAVEPOINT , и некоторые вспомогательные команды, в частности VACUUM .) Однако последней командой должна быть SELECT или команда с предложением RETURNING , возвращающая результат с типом возврата функции. Если же вы хотите определить функцию SQL, выполняющую действия, но не возвращающую полезное значение, вы можете объявить её как возвращающую тип void . Например, эта функция удаляет строки с отрицательным жалованьем из таблицы emp :
CREATE FUNCTION clean_emp() RETURNS void AS ' DELETE FROM emp WHERE salary < 0; ' LANGUAGE SQL; SELECT clean_emp(); clean_emp ----------- (1 row)
Примечание
Прежде чем начинается выполнение команд, разбирается всё тело SQL-функции. Когда SQL-функция содержит команды, модифицирующие системные каталоги (например, CREATE TABLE ), действие таких команд не будет видимо на стадии анализа последующих команд этой функции. Так, например, команды CREATE TABLE foo (. ); INSERT INTO foo VALUES(. ); не будут работать, как ожидается, если их упаковать в одну SQL-функцию, так как foo не будет существовать к моменту разбору команды INSERT . В подобных ситуациях вместо SQL-функции рекомендуется использовать PL/PgSQL .
Синтаксис команды CREATE FUNCTION требует, чтобы тело функции было записано как строковая константа. Обычно для этого удобнее всего заключать строковую константу в доллары (см. Подраздел 4.1.2.4). Если вы решите использовать обычный синтаксис с заключением строки в апострофы, вам придётся дублировать апострофы ( ' ) и обратную косую черту ( \ ) (предполагается синтаксис спецпоследовательностей) в теле функции (см. Подраздел 4.1.2.1).
36.4.1. Аргументы SQL -функций
К аргументам SQL-функции можно обращаться в теле функции по именам или номерам. Ниже приведены примеры обоих вариантов.
Чтобы использовать имя, объявите аргумент функции как именованный, а затем просто пишите это имя в теле функции. Если имя аргумента совпадает с именем какого-либо столбца в текущей SQL-команде внутри функции, имя столбца будет иметь приоритет. Чтобы всё же перекрыть имя столбца, дополните имя аргумента именем самой функции, то есть запишите его в виде имя_функции . имя_аргумента . (Если и это имя будет конфликтовать с полным именем столбца, снова выиграет имя столбца. Неоднозначности в этом случае вы можете избежать, выбрав другой псевдоним для таблицы в SQL-команде.)
Старый подход с нумерацией позволяет обращаться к аргументам, применяя запись $ n : $1 обозначает первый аргумент, $2 — второй и т. д. Это будет работать и в том случае, если данному аргументу назначено имя.
Если аргумент имеет составной тип, то для обращения к его атрибутам можно использовать запись с точкой, например: аргумент . поле или $1. поле . И опять же, при этом может потребоваться дополнить имя аргумента именем функции, чтобы сделать имя аргумента однозначным.
Аргументы SQL-функции могут использоваться только как значения данных, но не как идентификаторы. Например, это приемлемо:
INSERT INTO mytable VALUES ($1);
а это не будет работать:
INSERT INTO $1 VALUES (42);
Примечание
Возможность обращаться к аргументам SQL-функций по именам появилась в PostgreSQL 9.2. В функциях, которые должны работать со старыми серверами, необходимо применять запись $ n .
36.4.2. Функции SQL с базовыми типами
Простейшая возможная функция SQL не имеет аргументов и просто возвращает базовый тип, например integer :
CREATE FUNCTION one() RETURNS integer AS $$ SELECT 1 AS result; $$ LANGUAGE SQL; -- Альтернативная запись строковой константы: CREATE FUNCTION one() RETURNS integer AS ' SELECT 1 AS result; ' LANGUAGE SQL; SELECT one(); one ----- 1
Заметьте, что мы определили псевдоним столбца в теле функции для её результата (дали ему имя result ), но этот псевдоним не виден снаружи функции. Вследствие этого, столбец результата получил имя one , а не result .
Практически так же легко определяются функции SQL , которые принимают в аргументах базовые типы:
CREATE FUNCTION add_em(x integer, y integer) RETURNS integer AS $$ SELECT x + y; $$ LANGUAGE SQL; SELECT add_em(1, 2) AS answer; answer -------- 3
Мы также можем отказаться от имён аргументов и обращаться к ним по номерам:
CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$ SELECT $1 + $2; $$ LANGUAGE SQL; SELECT add_em(1, 2) AS answer; answer -------- 3
Вот более полезная функция, которую можно использовать, чтобы дебетовать банковский счёт:
CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS integer AS $$ UPDATE bank SET balance = balance - debit WHERE accountno = tf1.accountno; SELECT 1; $$ LANGUAGE SQL;
Пользователь может выполнить эту функцию, чтобы дебетовать счёт 17 на 100 долларов, так:
SELECT tf1(17, 100.0);
В этом примере мы выбрали имя accountno для первого аргумента, но это же имя имеет столбец в таблице bank . В команде UPDATE имя accountno относится к столбцу bank.accountno , так для обращения к аргументу нужно записать tf1.accountno . Конечно, мы могли бы избежать этого, выбрав другое имя для аргумента.
На практике обычно желательно получать от функции более полезный результат, чем константу 1, поэтому более реалистично такое определение:
CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS integer AS $$ UPDATE bank SET balance = balance - debit WHERE accountno = tf1.accountno; SELECT balance FROM bank WHERE accountno = tf1.accountno; $$ LANGUAGE SQL;
Эта функция изменяет баланс и возвращает полученное значение. То же самое можно сделать в одной команде, применив RETURNING :
CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS integer AS $$ UPDATE bank SET balance = balance - debit WHERE accountno = tf1.accountno RETURNING balance; $$ LANGUAGE SQL;
36.4.3. Функции SQL со сложными типами
В функциях с аргументами составных типов мы должны указывать не только, какой аргумент, но и какой атрибут (поле) этого аргумента нам нужен. Например, предположим, что emp — таблица, содержащая данные работников, и это же имя составного типа, представляющего каждую строку таблицы. Следующая функция double_salary вычисляет, каким было бы чьё-либо жалование в случае увеличения вдвое:
CREATE TABLE emp ( name text, salary numeric, age integer, cubicle point ); INSERT INTO emp VALUES ('Bill', 4200, 45, '(2,1)'); CREATE FUNCTION double_salary(emp) RETURNS numeric AS $$ SELECT $1.salary * 2 AS salary; $$ LANGUAGE SQL; SELECT name, double_salary(emp.*) AS dream FROM emp WHERE emp.cubicle ~= point '(2,1)'; name | dream ------+------- Bill | 8400
Обратите внимание на запись $1.salary позволяющую выбрать одно поле из значения строки аргумента. Также заметьте, что в вызывающей команде SELECT указание имя_таблицы .* выбирает всю текущую строку таблицы как составное значение. На строку таблицы можно сослаться и просто по имени таблицы, например так:
SELECT name, double_salary(emp) AS dream FROM emp WHERE emp.cubicle ~= point '(2,1)';
Однако это использование считается устаревшим, так как провоцирует путаницу. (Подробнее эти две записи составных значений строки таблицы описаны в Подразделе 8.16.5.)
Иногда бывает удобно образовать составное значение аргумента на лету. Это позволяет сделать конструкция ROW . Например, так можно изменить данные, передаваемые функции:
SELECT name, double_salary(ROW(name, salary*1.1, age, cubicle)) AS dream FROM emp;
Также возможно создать функцию, возвращающую составной тип. Например, эта функция возвращает одну строку emp :
CREATE FUNCTION new_emp() RETURNS emp AS $$ SELECT text 'None' AS name, 1000.0 AS salary, 25 AS age, point '(2,2)' AS cubicle; $$ LANGUAGE SQL;
В этом примере мы задали для каждого атрибута постоянное значение, но вместо этих констант можно подставить любые вычисления.
Учтите два важных требования относительно определения функции:
Порядок в списке выборки внутреннего запроса должен в точности совпадать с порядком следования столбцов в таблице, связанной с составным типом. (Имена столбцов, как показывает пример выше, для системы значения не имеют.)
Вы должны привести выражения в соответствие с определением составного типа, либо вы получите такие ошибки:
ERROR: function declared to return emp returns varchar instead of text at column 1
Ту же функцию можно определить другим способом:
CREATE FUNCTION new_emp() RETURNS emp AS $$ SELECT ROW('None', 1000.0, 25, '(2,2)')::emp; $$ LANGUAGE SQL;
Здесь мы записали SELECT , который возвращает один столбец нужного составного типа. В данной ситуации этот вариант на самом деле не лучше, но в некоторых случаях он может быть удобной альтернативой — например, если нам нужно вычислить результат, вызывая другую функцию, которая возвращает нужное составное значение.
Мы можем вызывать эту функцию напрямую, либо указав её в выражении значения:
SELECT new_emp(); new_emp -------------------------- (None,1000.0,25,"(2,2)")
либо обратившись к ней, как к табличной функции:
SELECT * FROM new_emp(); name | salary | age | cubicle ------+--------+-----+--------- None | 1000.0 | 25 | (2,2)
Второй способ более подробно описан в Подразделе 36.4.7.
Когда используется функция, возвращающая составной тип, может возникнуть желание получить из её результата только одно поле (атрибут). Это можно сделать, применяя такую запись:
SELECT (new_emp()).name; name ------ None
Дополнительные скобки необходимы во избежание неоднозначности при разборе запроса. Если вы попытаетесь выполнить запрос без них, вы получите ошибку:
SELECT new_emp().name; ERROR: syntax error at or near "." LINE 1: SELECT new_emp().name; ^
(ОШИБКА: синтаксическая ошибка (примерное положение: "."))
Функциональную запись также можно использовать и для извлечения атрибутов:
SELECT name(new_emp()); name ------ None
Как рассказывалось в Подразделе 8.16.5, запись с указанием поля и функциональная запись являются равнозначными.
Ещё один вариант использования функции, возвращающей составной тип, заключается в передаче её результата другой функции, которая принимает этот тип строки на вход:
CREATE FUNCTION getname(emp) RETURNS text AS $$ SELECT $1.name; $$ LANGUAGE SQL; SELECT getname(new_emp()); getname --------- None (1 row)
36.4.4. Функции SQL с выходными параметрами
Альтернативный способ описать результаты функции — определить её с выходными параметрами, как в этом примере:
CREATE FUNCTION add_em (IN x int, IN y int, OUT sum int) AS 'SELECT x + y' LANGUAGE SQL; SELECT add_em(3,7); add_em -------- 10 (1 row)
Это по сути не отличается от версии add_em , показанной в Подразделе 36.4.2. Действительная ценность выходных параметров в том, что они позволяют удобным способом определить функции, возвращающие несколько столбцов. Например:
CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int) AS 'SELECT x + y, x * y' LANGUAGE SQL; SELECT * FROM sum_n_product(11,42); sum | product -----+--------- 53 | 462 (1 row)
Фактически здесь мы определили анонимный составной тип для результата функции. Показанный выше пример даёт тот же конечный результат, что и команды:
CREATE TYPE sum_prod AS (sum int, product int); CREATE FUNCTION sum_n_product (int, int) RETURNS sum_prod AS 'SELECT $1 + $2, $1 * $2' LANGUAGE SQL;
Но предыдущий вариант зачастую удобнее, так как он не требует отдельно заниматься определением составного типа. Заметьте, что имена, назначаемые выходным параметрам, не просто декоративные, а определяют имена столбцов анонимного составного типа. (Если вы опустите имя выходного параметра, система выберет имя сама.)
Заметьте, что выходные параметры не включаются в список аргументов при вызове такой функции из SQL. Это объясняется тем, что PostgreSQL определяет сигнатуру вызова функции, рассматривая только входные параметры. Это также значит, что при таких операциях, как удаление функции, в ссылках на функцию учитываются только типы входных параметров. Таким образом, удалить эту конкретную функцию можно любой из этих команд:
DROP FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int); DROP FUNCTION sum_n_product (int, int);
Параметры функции могут быть объявлены как IN (по умолчанию), OUT , INOUT или VARIADIC . Параметр INOUT действует как входной (является частью списка аргументов при вызове) и как выходной (часть типа записи результата). Параметры VARIADIC являются входными, но обрабатывается специальным образом, как описано далее.
36.4.5. Функции SQL с переменным числом аргументов
Функции SQL могут быть объявлены как принимающие переменное число аргументов, с условием, что все « необязательные » аргументы имеют один тип данных. Необязательные аргументы будут переданы такой функции в виде массива. Для этого в объявлении функции последний параметр помечается как VARIADIC ; при этом он должен иметь тип массива. Например:
CREATE FUNCTION mleast(VARIADIC arr numeric[]) RETURNS numeric AS $$ SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i); $$ LANGUAGE SQL; SELECT mleast(10, -1, 5, 4.4); mleast -------- -1 (1 row)
По сути, все фактические аргументы, начиная с позиции VARIADIC , собираются в одномерный массив, как если бы вы написали
SELECT mleast(ARRAY[10, -1, 5, 4.4]); -- это не будет работать
На самом деле так вызвать эту функцию нельзя, или, по крайней мере, это не будет соответствовать определению функции. Параметру VARIADIC соответствуют одно или несколько вхождений типа его элемента, но не его собственного типа.
Но иногда бывает полезно передать функции с переменными параметрами уже подготовленный массив; особенно когда одна функция с переменными параметрами хочет передавать свой массив параметров другой. Также это более безопасный способ вызывать такую функцию, существующую в схеме, где могут создавать объекты недоверенные пользователи; см. Раздел 10.3. Это можно сделать, добавив VARIADIC в вызов:
SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]);
Это предотвращает разворачивание переменного множества параметров функции в базовый тип, что позволяет сопоставить с ним значение типа массива. VARIADIC можно добавить только к последнему фактическому аргументу вызова функции.
Также указание VARIADIC даёт единственную возможность передать пустой массив функции с переменными параметрами, например, так:
SELECT mleast(VARIADIC ARRAY[]::numeric[]);
Простой вызов SELECT mleast() не будет работать, так как переменным параметрам должен соответствовать минимум один фактический аргумент. (Можно определить вторую функцию с таким же именем mleast , но без параметров, если вы хотите выполнять такие вызовы.)
Элементы массива, создаваемые из переменных параметров, считаются не имеющими собственных имён. Это означает, что передать функции с переменными параметрами именованные аргументы нельзя (см. Раздел 4.3), если только при вызове не добавлено VARIADIC . Например, этот вариант будет работать:
SELECT mleast(VARIADIC arr => ARRAY[10, -1, 5, 4.4]);
А эти варианты нет:
SELECT mleast(arr => 10); SELECT mleast(arr => ARRAY[10, -1, 5, 4.4]);
36.4.6. Функции SQL со значениями аргументов по умолчанию
Функции могут быть объявлены со значениями по умолчанию для некоторых или всех входных аргументов. Значения по умолчанию подставляются, когда функция вызывается с недостаточным количеством фактических аргументов. Так как аргументы можно опускать только с конца списка фактических аргументов, все параметры после параметра со значением по умолчанию также получат значения по умолчанию. (Хотя запись с именованными аргументами могла бы ослабить это ограничение, оно всё же остаётся в силе, чтобы позиционные ссылки на аргументы оставались действительными.) Независимо от того, используете вы эту возможность или нет, она требует осторожности при вызове функций в базах данных, где одни пользователи не доверяют другим; см. Раздел 10.3.
CREATE FUNCTION foo(a int, b int DEFAULT 2, c int DEFAULT 3) RETURNS int LANGUAGE SQL AS $$ SELECT $1 + $2 + $3; $$; SELECT foo(10, 20, 30); foo ----- 60 (1 row) SELECT foo(10, 20); foo ----- 33 (1 row) SELECT foo(10); foo ----- 15 (1 row) SELECT foo(); -- не работает из-за отсутствия значения по умолчанию для первого аргумента ERROR: function foo() does not exist
(ОШИБКА: функция foo() не существует) Вместо ключевого слова DEFAULT можно использовать знак = .
36.4.7. Функции SQL , порождающие таблицы
Все функции SQL можно использовать в предложении FROM запросов, но наиболее полезно это для функций, возвращающих составные типы. Если функция объявлена как возвращающая базовый тип, она возвращает таблицу с одним столбцом. Если же функция объявлена как возвращающая составной тип, она возвращает таблицу со столбцами для каждого атрибута составного типа.
CREATE TABLE foo (fooid int, foosubid int, fooname text); INSERT INTO foo VALUES (1, 1, 'Joe'); INSERT INTO foo VALUES (1, 2, 'Ed'); INSERT INTO foo VALUES (2, 1, 'Mary'); CREATE FUNCTION getfoo(int) RETURNS foo AS $$ SELECT * FROM foo WHERE fooid = $1; $$ LANGUAGE SQL; SELECT *, upper(fooname) FROM getfoo(1) AS t1; fooid | foosubid | fooname | upper -------+----------+---------+------- 1 | 1 | Joe | JOE (1 row)
Как показывает этот пример, мы можем работать со столбцами результата функции так же, как если бы это были столбцы обычной таблицы.
Заметьте, что мы получаем из данной функции только одну строку. Это объясняется тем, что мы не использовали указание SETOF . Оно описывается в следующем разделе.
36.4.8. Функции SQL , возвращающие множества
Когда SQL-функция объявляется как возвращающая SETOF некий_тип , конечный запрос функции выполняется до завершения и каждая строка выводится как элемент результирующего множества.
Это обычно используется, когда функция вызывается в предложении FROM . В этом случае каждая строка, возвращаемая функцией, становится строкой таблицы, появляющейся в запросе. Например, в предположении, что таблица foo имеет то же содержимое, что и раньше, мы выполняем:
CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$ SELECT * FROM foo WHERE fooid = $1; $$ LANGUAGE SQL; SELECT * FROM getfoo(1) AS t1;
Тогда в ответ мы получим:
fooid | foosubid | fooname -------+----------+--------- 1 | 1 | Joe 1 | 2 | Ed (2 rows)
Также возможно выдать несколько строк со столбцами, определяемыми выходными параметрами, следующим образом:
CREATE TABLE tab (y int, z int); INSERT INTO tab VALUES (1, 2), (3, 4), (5, 6), (7, 8); CREATE FUNCTION sum_n_product_with_tab (x int, OUT sum int, OUT product int) RETURNS SETOF record AS $$ SELECT $1 + tab.y, $1 * tab.y FROM tab; $$ LANGUAGE SQL; SELECT * FROM sum_n_product_with_tab(10); sum | product -----+--------- 11 | 10 13 | 30 15 | 50 17 | 70 (4 rows)
Здесь ключевая особенность заключается в записи RETURNS SETOF record , показывающей, что функция возвращает множество строк вместо одной. Если существует только один выходной параметр, укажите тип этого параметра вместо record .
Часто бывает полезно сконструировать результат запроса, вызывая функцию, возвращающую множество, несколько раз, передавая при каждом вызове параметры из очередных строк таблицы или подзапроса. Для этого рекомендуется применить ключевое слово LATERAL , описываемое в Подразделе 7.2.1.5. Ниже приведён пример использования функции, возвращающей множество, для перечисления элементов древовидной структуры:
SELECT * FROM nodes; name | parent -----------+-------- Top | Child1 | Top Child2 | Top Child3 | Top SubChild1 | Child1 SubChild2 | Child1 (6 rows) CREATE FUNCTION listchildren(text) RETURNS SETOF text AS $$ SELECT name FROM nodes WHERE parent = $1 $$ LANGUAGE SQL STABLE; SELECT * FROM listchildren('Top'); listchildren -------------- Child1 Child2 Child3 (3 rows) SELECT name, child FROM nodes, LATERAL listchildren(name) AS child; name | child --------+----------- Top | Child1 Top | Child2 Top | Child3 Child1 | SubChild1 Child1 | SubChild2 (5 rows)
В этом примере не делается ничего такого, что мы не могли бы сделать, применив простое соединение, но для более сложных вычислений возможность поместить некоторую логику в функцию может быть весьма удобной.
В настоящее время функции, возвращающие множества, могут также вызываться в списке выборки запроса. Для каждой строки, которая генерируется самим запросом, вызывается функция, возвращающая множество, и для каждого элемента набора её результатов генерируется отдельная строка. Заметьте, однако, что эта возможность считается устаревшей и может быть ликвидирована в будущих выпусках. Предыдущий пример можно было бы также переписать с применением запросов следующим образом:
SELECT listchildren('Top'); listchildren -------------- Child1 Child2 Child3 (3 rows) SELECT name, listchildren(name) FROM nodes; name | listchildren --------+-------------- Top | Child1 Top | Child2 Top | Child3 Child1 | SubChild1 Child1 | SubChild2 (5 rows)
Заметьте, что в последней команде SELECT для Child2 , Child3 и т. д. строки не выдаются. Это происходит потому, что listchildren возвращает пустое множество для этих аргументов, так что строки результата не генерируются. Это же поведение мы получаем при внутреннем соединении с результатом функции с применением LATERAL .
Примечание
Если последняя команда функции — INSERT , UPDATE или DELETE с RETURNING , эта команда будет всегда выполняться до завершения, даже если функция не объявлена с указанием SETOF или вызывающий запрос не выбирает все строки результата. Все дополнительные строки, выданные предложением RETURNING , просто игнорируются, но соответствующие изменения в таблице всё равно произойдут (и будут завершены до выхода из функции).
Примечание
Ключевая проблема использования функций, возвращающих множества, в списке выборки, а не в предложении FROM , заключается в том, что при вызове в одном списке выборки нескольких таких функций, результат будет не вполне разумным. (На самом деле, если вы сделаете это, вы получите выходные строки в количестве, равном наименьшему общему кратному чисел строк, которые будут выданы всеми функциями, возвращающими множества.) Синтаксис LATERAL даёт более ожидаемые результаты при вызове нескольких таких функций и поэтому рекомендуется использовать его.
36.4.9. Функции SQL , возвращающие таблицы ( TABLE )
Есть ещё один способ объявить функцию, возвращающую множества, — использовать синтаксис RETURNS TABLE( столбцы ) . Это равнозначно использованию одного или нескольких параметров OUT с объявлением функции как возвращающей SETOF record (или SETOF тип единственного параметра, если это применимо). Этот синтаксис описан в последних версиях стандарта SQL, так что этот вариант может быть более портируемым, чем SETOF .
Например, предыдущий пример с суммой и произведением можно также переписать так:
CREATE FUNCTION sum_n_product_with_tab (x int) RETURNS TABLE(sum int, product int) AS $$ SELECT $1 + tab.y, $1 * tab.y FROM tab; $$ LANGUAGE SQL;
Запись RETURNS TABLE не позволяет явно указывать OUT и INOUT для параметров — все выходные столбцы необходимо записать в списке TABLE .
36.4.10. Полиморфные функции SQL
Функции SQL могут быть объявлены как принимающие и возвращающие полиморфные типы anyelement , anyarray , anynonarray , anyenum и anyrange . За более подробным объяснением полиморфизма функций обратитесь к Подразделу 36.2.5. В следующем примере полиморфная функция make_array создаёт массив из двух элементов произвольных типов:
CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$ SELECT ARRAY[$1, $2]; $$ LANGUAGE SQL; SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray; intarray | textarray ----------+----------- | (1 row)
Обратите внимание на приведение типа 'a'::text , определяющее, что аргумент имеет тип text . Оно необходимо, если аргумент задаётся просто строковой константой, так как иначе он будет воспринят как имеющий тип unknown , а массив типов unknown является недопустимым. Без этого приведения вы получите такую ошибку:
ERROR: could not determine polymorphic type because input has type "unknown"
(ОШИБКА: не удалось определить полиморфный тип, так как входные аргументы имеют тип "unknown")
Функция с полиморфными аргументами может иметь фиксированный тип результата, однако обратное не допускается. Например:
CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$ SELECT $1 > $2; $$ LANGUAGE SQL; SELECT is_greater(1, 2); is_greater ------------ f (1 row) CREATE FUNCTION invalid_func() RETURNS anyelement AS $$ SELECT 1; $$ LANGUAGE SQL; ERROR: cannot determine result data type DETAIL: A function returning a polymorphic type must have at least one polymorphic argument.
(ОШИБКА: не удалось определить тип результата; ПОДРОБНОСТИ: Функция, возвращающая полиморфный тип, должна иметь минимум один полиморфный аргумент.)
Полиморфизм можно применять и с функциями, имеющими выходные аргументы. Например:
CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray) AS 'select $1, array[$1,$1]' LANGUAGE SQL; SELECT * FROM dup(22); f2 | f3 ----+--------- 22 | (1 row)
Полиморфизм также можно применять с функциями с переменными параметрами. Например:
CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS $$ SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i); $$ LANGUAGE SQL; SELECT anyleast(10, -1, 5, 4); anyleast ---------- -1 (1 row) SELECT anyleast('abc'::text, 'def'); anyleast ---------- abc (1 row) CREATE FUNCTION concat_values(text, VARIADIC anyarray) RETURNS text AS $$ SELECT array_to_string($2, $1); $$ LANGUAGE SQL; SELECT concat_values('|', 1, 4, 2); concat_values --------------- 1|4|2 (1 row)
36.4.11. Функции SQL с правилами сортировки
Когда функция SQL принимает один или несколько параметров сортируемых типов данных, правило сортировки определяется при каждом вызове функции, в зависимости от правил сортировки, связанных с фактическими аргументами, как описано в Разделе 23.2. Если правило сортировки определено успешно (то есть не возникло конфликтов между неявно установленными правилами сортировки аргументов), оно неявно назначается для всех сортируемых параметров. Выбранное правило будет определять поведение операций, связанных с сортировкой, в данной функции. Например, для показанной выше функции anyleast , результат
SELECT anyleast('abc'::text, 'ABC');
будет зависеть от правила сортировки по умолчанию, заданного в базе данных. С локалью C результатом будет строка ABC , но со многими другими локалями это будет abc . Нужное правило сортировки можно установить принудительно, добавив предложение COLLATE к одному из аргументов функции, например:
SELECT anyleast('abc'::text, 'ABC' COLLATE "C");
С другой стороны, если вы хотите, чтобы функция работала с определённым правилом сортировки, вне зависимости от того, с каким она была вызвана, вставьте предложения COLLATE где требуется в определении функции. Эта версия anyleast всегда будет сравнивать строки по правилам локали en_US :
CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS $$ SELECT min($1[i] COLLATE "en_US") FROM generate_subscripts($1, 1) g(i); $$ LANGUAGE SQL;
Но заметьте, что при попытке применить правило к несортируемому типу данных, возникнет ошибка.
Если для фактических аргументов не удаётся определить общее правило сортировки, функция SQL считает, что им назначено правило сортировки по умолчанию для их типа данных (обычно это то же правило сортировки, что определено по умолчанию для базы данных, но оно может быть и другим для параметров доменных типов).
Поведение сортируемых параметров можно воспринимать как ограниченную форму полиморфизма, применимую только к текстовым типам данных.
| Пред. | Наверх | След. |
| 36.3. Пользовательские функции | Начало | 36.5. Перегрузка функций |