Встроенные и хранимые функции в SQL. Хранимые процедуры и триггеры

Требования к вызываемым функциям

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

• Все параметры функции должны иметь режим использования IN . Режимы IN OUT и OUT в функциях , встраиваемых в SQL-код, недопустимы.
• Типы данных параметров функций и тип возвращаемого значения должны распоз­наваться сервером Oracle. PL/SQL дополняет основные типы Oracle, которые пока не поддерживаются базой данных. Речь идет о типах BOOLEAN , BINARY_INTEGER , ассо­циативных массивах, записях PL/SQL и определяемых программистом подтипах.
• Функция должна храниться в базе данных. Функция, определенная на стороне клиента, не может вызываться в командах SQL, так как SQL не сможет разрешить ссылку на эту функцию.

По умолчанию пользовательские функции, вызываемые в SQL , оперируют данными одной строки, а не столбца (как агрегатные функции SUM , MIN и AVG). Чтобы соз­дать агрегатные функции, вызываемые в SQL , необходимо использовать интерфейс ODCIAggregate , который является частью среды Oracle Extensibility Framework . За подробной информацией по этой теме обращайтесь к документации Oracle.

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

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

• Хранимые функции не могут модифицировать таблицы баз данных и выполнять команды DDL (CREATE TABLE , DROP INDEX и т. д.), INSERT , DELETE , MERGE и UPDATE . Эти ограничения ослабляются, если функция определена как автономная транзакция . В таком случае любые вносимые ею изменения осуществляются не­зависимо от внешней транзакции, в которой выполняется запрос.
• Хранимые функции, которые вызываются удаленно или в параллельном режиме, не могут читать или изменять значения переменных пакета. Сервер Oracle не поддер­живает побочные эффекты, действие которых выходит за рамки сеанса пользователя.
• Хранимая функция может изменять значения переменных пакета, только если она вызывается в списке выборки либо в предложении VALUES или SET . Если хранимая функция вызывается в предложении WHERE или GROUP BY , она не может изменять значения переменных пакета.
• До выхода Oracle8 пользовательские функции не могли вызывать процедуру RAISE_ APPLICATION_ERROR .
• Хранимая функция не может вызывать другой модуль (хранимую процедуру или функцию), не соответствующий приведенным требованиям.
• Хранимая функция не может обращаться к представлению, которое нарушает любое из предшествующих правил. Представлением (view) называется хранимая команда SELECT , в которой могут вызываться хранимые функции.
• До выхода Oracle11g для передачи параметров функциям могла использоваться только позиционная запись. Начиная с Oracle11g, допускается передача параметров по имени и смешанная запись.

Непротиворечивость чтения и пользовательские функции

Модель непротиворечивости чтения в базе данных Oracle проста и понятна: после выполнения запрос «видит» данные в том состоянии, в котором они существовали (были зафиксированы в базе данных) на момент начала запроса, с учетом результатов изменений, вносимых командами DML текущей транзакции. Таким образом, если мой запрос был выполнен в 9:00 и продолжает работать в течение часа, даже если за это время другой пользователь внесет в данные изменения, они не отразятся в моем запросе.

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

FUNCTION total_sales (id_in IN account.account_id%TYPE) RETURN NUMBER IS CURSOR tot_cur IS SELECT SUM (sales) total FROM orders WHERE account_id = id_in AND TO_CHAR (ordered_on, "YYYY") = TO_CHAR (SYSDATE, "YYYY"); tot_rec tot_cur%ROWTYPE; BEGIN OPEN tot_cur; FETCH tot_cur INTO tot_rec; CLOSE tot_cur; RETURN tot_rec.total; END; SELECT name, total_sales (account_id) FROM account WHERE status = "ACTIVE";

Таблица account содержит 5 миллионов активных строк, а таблица orders - 20 миллио­нов. Я запускаю запрос в 10:00, на его завершение уходит около часа. В 10:45 приходит некто, обладающий необходимыми привилегиями, удаляет все строки из таблицы orders и закрепляет транзакцию. По правилам модели непротиворечивости чтения Oracle сеанс, в котором выполняется запрос, не должен рассматривать эти строки как удаленные до завершения запроса. Но при следующем вызове из запроса функция total_sales не найдет ни одной строки и вернет NULL - и так будет происходить до завершения запроса.

При выполнении запросов из функций, вызываемых в коде SQL, необходимо внимательно следить за непротиворечивостью чтения. Если эти функции вызываются в продолжитель­ных запросах или транзакциях, вероятно, вам стоит выполнить следующую команду для обеспечения непротиворечивости чтения между командами SQL текущей транзакции: SET TRANSACTION READ ONLY

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

Определение подпрограмм PL/SQL в командах SQL (12.1 и выше)

Разработчики уже давно могли вызывать свои функции PL/SQL из команд SQL . До­пустим, я создал функцию с именем BETWNSTR , которая возвращает подстроку с заданной начальной и конечной позицией:

FUNCTION betwnstr (string_in IN VARCHAR2 , start_in IN PLS_INTEGER , end_in IN PLS_INTEGER) продолжение # RETURN VARCHAR2 IS BEGIN RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1)); END;

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

SELECT betwnstr (last_name, 3, 5) FROM employees

Эта возможность позволяет «расширить» язык SQL функциональностью, присущей конкретному приложению, и повторно использовать алгоритмы (вместо копирования). К недостаткам выполнения пользовательских функций в SQL следует отнести необ­ходимость переключения контекста между исполнительными ядрами SQL и P L/SQL . Начиная с Oracle Database 12c вы можете определять функции и процедуры PL/SQL в секции WITH подзапроса, чтобы затем использовать их как любую встроенную или пользовательскую функцию. Эта возможность позволяет консолидировать функцию и запрос в одной команде:

WITH FUNCTION betwnstr (string_in IN VARCHAR2, start_in IN PLS_INTEGER, end_in IN PLS_INTEGER) RETURN VARCHAR2 IS BEGIN RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1)); END; SELECT betwnstr (last_name, 3, 5) FROM employees

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

Впрочем, для определения функций в секции WITH есть и другие причины. В SQL можно вызвать пакетную функцию, но нельзя сослаться на константу, объявленную в пакете (если только команда SQL не выполняется внутри блока PL/SQL), как показано в сле­дующем примере:

SQL> CREATE OR REPLACE PACKAGE pkg 2 IS 3 year_number CONSTANT INTEGER:= 2013; 4 END; 5 / Package created. SQL> SELECT pkg.year_number FROM employees 2 WHERE employee_id = 138 3 / SELECT pkg.year_number FROM employees * ERROR at line 1: ORA-06553: PLS-221: "YEAR_NUMBER" is not a procedure or is undefined

Классическое обходное решение основано на определении функции в пакете и ее по­следующем вызове:

SQL> CREATE OR REPLACE PACKAGE pkg 2 IS 3 FUNCTION year_number 4 RETURN INTEGER; 5 END; 6 / Package created. SQL> CREATE OR REPLACE PACKAGE BODY pkg 2 IS 3 c_year_number CONSTANT INTEGER:= 2013; 4 5 FUNCTION year_number 6 RETURN INTEGER 7 IS 8 BEGIN 9 RETURN c_year_number; 10 END; 11 END; 12 / Package body created. SQL> SELECT pkg.year_number 2 FROM employees 3 WHERE employee_id = 138 4 / YEAR NUMBER ------------ 2013

Для простого обращения к значению константы в команде SQL потребуется слишком много кода и усилий. Начиная с версии 12.1 это стало излишним - достаточно создать функцию в секции WITH:

WITH FUNCTION year_number RETURN INTEGER IS BEGIN RETURN pkg.year_number; END; SELECT year_number FROM employees WHERE employee_id = 138

Функции PL/SQL , определяемые в SQL, также пригодятся при работе с автономными базами данных, доступными только для чтения. Хотя в таких базах данных невозмож­но создавать «вспомогательные» функции PL/SQL , вы можете определять их прямо в запросах.

Механизм WITH FUNCTION стал чрезвычайно полезным усовершенствованием языка SQL. Тем не менее каждый раз, когда вы планируете его использование, стоит задать себе один вопрос: «Потребуется ли эта функциональность в нескольких местах приложения?»

Если вы ответите на него положительно, следует решить, компенсирует ли выигрыш по производительности от применения WITH FUNCTION потенциальные потери от копи­рования и вставки этой логики в нескольких командах SQL.

Учтите, что в версии 12.1 в блоках PL/SQL невозможно выполнить статическую коман­ду select с секцией with function . Безусловно, это выглядит очень странно, и я уверен, что в 12.2 такая возможность появится, но пока при попытке выполнения следующего кода будет выдана ошибка:

SQL> BEGIN 2 WITH FUNCTION full_name (fname_in IN VARCHAR2, lname_in IN VARCHAR2) 3 RETURN VARCHAR2 4 IS 5 BEGIN 6 RETURN fname_in || " " || lname_in; 7 END; 8 9 SELECT LENGTH (full_name (first_name, last_name)) 10 INTO c 11 FROM employees; 12 13 DBMS_OUTPUT.put_line ("count = " || c); 14 END; 15 / WITH FUNCTION full_name (fname_in IN VARCHAR2, lname_in IN VARCHAR2) * ERROR at line 2: ORA-06550: line 2, column 18: PL/SQL: ORA-00905: missing keyword

Помимо конструкции WITH FUNCTION, в версии 12.1 также появилась директива UDF для улучшения быстродействия функций PL/SQL, выполняемых из SQL.

В языках программирования обычно имеется два типа подпрограмм:

хранимые процедуры;

определяемые пользователем функции (UDF).

Как уже было рассмотрено в предыдущей статье, хранимые процедуры состоят из нескольких инструкций и имеют от нуля до нескольких входных параметров, но обычно не возвращают никаких параметров. В отличие от хранимых процедур, функции всегда возвращают одно значение. В этом разделе мы рассмотрим создание и использование определяемых пользователем функций (User Defined Functions - UDF) .

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

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

CREATE FUNCTION function_name [({@param } type [= default]) {,...} RETURNS {scalar_type | [@variable] TABLE} {block | RETURN (select_statement)} Соглашения по синтаксису

Параметр 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:

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

В инструкциях Transact-SQL имена функций необходимо задавать, используя имена, состоящие из двух частей: schema name и function name, поэтому в примере мы использовали префикс схемы dbo.

Возвращающие табличное значение функции

Как уже упоминалось ранее, функция является возвращающей табличное значение, если ее предложение RETURNS возвращает набор строк. В зависимости от того, каким образом определено тело функции, возвращающие табличное значение функции классифицируются как встраиваемые (inline) и многоинструкционные (multistatement) . Если в предложении RETURNS ключевое слово TABLE указывается без сопровождающего списка столбцов, такая функция является встроенной. Инструкция SELECT встраиваемой функции возвращает результирующий набор в виде переменной с типом данных TABLE.

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

Создание возвращающей табличное значение функции показано в примере ниже:

Функция 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 возвращает те строки из внутреннего (левого) табличного выражения, которые совпадают с внешним (правым) табличным выражением. Таким образом, логически, инструкция CROSS APPLY функционирует так же, как и инструкция INNER JOIN.

Инструкция OUTER APPLY возвращает все строки из внутреннего (левого) табличного выражения. (Для тех строк, для которых нет совпадений во внешнем табличном выражении, он содержит значения NULL в столбцах внешнего табличного выражения.) Логически, инструкция OUTER APPLY эквивалентна инструкции LEFT OUTER JOIN.

Применение инструкции APPLY показано в примерах ниже:

Функция 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#, в котором я добавлял определение хранимых процедур и функций, у вас сборка может называться иначе).

CREATE FUNCTION - создать функцию

Синтаксис

CREATE [ OR REPLACE ] FUNCTION имя ([ [ режим_аргумента ] [ имя_аргумента ] тип_аргумента [ { DEFAULT | = } выражение_по_умолчанию ] [, ...] ]) [ RETURNS тип_результата | RETURNS TABLE ( имя_столбца тип_столбца [, ...]) ] { LANGUAGE имя_языка | TRANSFORM { FOR TYPE имя_типа } [, ... ] | WINDOW | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | PARALLEL { UNSAFE | RESTRICTED | SAFE } | COST стоимость_выполнения | ROWS строк_в_результате | SET параметр_конфигурации { TO значение | = значение | FROM CURRENT } | AS " определение " | AS " объектный_файл ", " объектный_символ " } ... [ WITH ( атрибут [, ...]) ]

Описание

Команда CREATE FUNCTION определяет новую функцию. CREATE OR REPLACE FUNCTION создаёт новую функцию, либо заменяет определение уже существующей. Чтобы определить функцию, необходимо иметь право USAGE для соответствующего языка.

Если указано имя схемы, функция создаётся в заданной схеме, в противном случае - в текущей. Имя новой функции должно отличаться от имён существующих функций с такими же типами аргументов в этой схеме. Однако функции с аргументами разных типов могут иметь одно имя (это называется перегрузкой ).

Чтобы заменить текущее определение существующей функции, используйте команду CREATE OR REPLACE FUNCTION . Но учтите, что она не позволяет изменить имя или аргументы функции (если попытаться сделать это, на самом деле будет создана новая, независимая функция). Кроме того, CREATE OR REPLACE FUNCTION не позволит изменить тип результата существующей функции. Чтобы сделать это, придётся удалить функцию и создать её заново. (Это означает, что если функция имеет выходные параметры (OUT), то изменить типы параметров OUT можно, только удалив функцию.)

Когда команда CREATE OR REPLACE FUNCTION заменяет существующую функцию, владелец и права доступа к этой функции не меняются. Все другие свойства функции получают значения, задаваемые командой явно или по умолчанию. Чтобы заменить функцию, необходимо быть её владельцем (или быть членом роли-владельца).

Если вы удалите и затем вновь создадите функцию, новая функция станет другой сущностью, отличной от старой; вам потребуется так же удалить существующие правила, представления, триггеры и т. п., ссылающиеся на старую функцию. Поэтому, чтобы изменить определение функции, сохраняя ссылающиеся на неё объекты, следует использовать CREATE OR REPLACE FUNCTION . Кроме того, многие дополнительные свойства существующей функции можно изменить с помощью ALTER FUNCTION .

Владельцем функции становится создавший её пользователь.

Чтобы создать функцию, необходимо иметь право USAGE для типов её аргументов и возвращаемого типа.

Параметры

имя

Имя создаваемой функции (возможно, дополненное схемой). режим_аргумента

Режим аргумента: IN (входной), OUT (выходной), INOUT (входной и выходной) или VARIADIC (переменный). По умолчанию подразумевается IN . За единственным аргументом VARIADIC могут следовать только аргументы OUT . Кроме того, аргументы OUT и INOUT нельзя использовать с предложением RETURNS TABLE . имя_аргумента

Имя аргумента. Некоторые языки (включая SQL и PL/pgSQL) позволяют использовать это имя в теле функции. Для других языков это имя служит просто дополнительным описанием, если говорить о самой функции; однако вы можете указывать имена аргументов при вызове функции для улучшения читаемости (см. Раздел 4.3). Имя выходного аргумента в любом случае имеет значение, так как оно определяет имя столбца в типе результата. (Если вы опустите имя выходного аргумента, система выберет для него имя по умолчанию.) тип_аргумента

Тип данных аргумента функции (возможно, дополненный схемой), при наличии аргументов. Тип аргументов может быть базовым, составным или доменным, либо это может быть ссылка на столбец таблицы.

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

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

Выражение, используемое для вычисления значения по умолчанию, если параметр не задан явно. Результат выражения должен сводиться к типу соответствующего параметра. Значения по умолчанию могут иметь только входные параметры (включая INOUT). Для всех входных параметров, следующих за параметром с определённым значением по умолчанию, также должны быть определены значения по умолчанию. тип_результата

Тип возвращаемых данных (возможно, дополненный схемой). Это может быть базовый, составной или доменный тип, либо ссылка на тип столбца таблицы. В зависимости от языка реализации здесь также могут допускаться «псевдотипы » , например cstring . Если функция не должна возвращать значение, в качестве типа результата указывается void .

В случае наличия параметров OUT или INOUT , предложение RETURNS можно опустить. Если оно присутствует, оно должно согласовываться с типом результата, выводимым из выходных параметров: в качестве возвращаемого типа указывается RECORD , если выходных параметров несколько, либо тип единственного выходного параметра.

Указание SETOF показывает, что функция возвращает множество, а не единственный элемент.

Имя выходного столбца в записи RETURNS TABLE . По сути это ещё один способ объявить именованный выходной параметр (OUT), но RETURNS TABLE также подразумевает и RETURNS SETOF . тип_столбца

Тип данных выходного столбца в записи RETURNS TABLE . имя_языка

Имя языка, на котором реализована функция. Это может быть sql , c , internal , либо имя процедурного языка, определённого пользователем, например, plpgsql . Стиль написания этого имени в апострофах считается устаревшим и требует точного совпадения регистра. TRANSFORM { FOR TYPE имя_типа } [, ... ] }

Устанавливает список трансформаций, которые должны применяться при вызове функции. Трансформации выполняют преобразования между типами SQL и типами данных, специфичными для языков; см. CREATE TRANSFORM . Преобразования встроенных типов обычно жёстко предопределены в реализациях процедурных языков, так что их здесь указывать не нужно. Если реализация процедурного языка не может обработать тип и трансформация для него отсутствует, будет выполнено преобразование типов по умолчанию, но это зависит от реализации. WINDOW

Указание WINDOW показывает, что создаётся не простая, а оконная функция . В настоящее время это имеет смысл только для функций, написанных на C. Атрибут WINDOW нельзя изменить, модифицируя впоследствии определение функции. IMMUTABLE
STABLE
VOLATILE

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

Характеристика IMMUTABLE (постоянная) показывает, что функция не может модифицировать базу данных и всегда возвращает один и тот же результат при определённых значениях аргументов; то есть, она не обращается к базе данных и не использует информацию, не переданную ей явно в списке аргументов. Если функция имеет такую характеристику, любой её вызов с аргументами-константами можно немедленно заменить значением функции.

Характеристика STABLE (стабильная) показывает, что функция не может модифицировать базу данных и в рамках одного сканирования таблицы она всегда возвращает один и тот же результат для определённых значений аргументов, но этот результат может быть разным в разных операторах SQL. Это подходящий выбор для функций, результаты которых зависят от содержимого базы данных и настраиваемых параметров (например, текущего часового пояса). (Но этот вариант не подходит для триггеров AFTER , желающих прочитать строки, изменённые текущей командой.) Также заметьте, что функции семейства current_timestamp также считаются стабильными, так как их результаты не меняются внутри транзакции.

Характеристика VOLATILE (изменчивая) показывает, что результат функции может меняться даже в рамках одного сканирования таблицы, так что её вызовы нельзя оптимизировать. Изменчивы в этом смысле относительно немногие функции баз данных, например: random() , currval() и timeofday() . Но заметьте, что любая функция с побочными эффектами должна быть классифицирована как изменчивая, даже если её результат вполне предсказуем, чтобы её вызовы не были соптимизированы; пример такой функции: setval() .

За дополнительными подробностями обратитесь к Разделу 35.6 . LEAKPROOF

Характеристика LEAKPROOF (герметичная) показывает, что функция не имеет побочных эффектов. Она не раскрывает информацию о своих аргументах, кроме как возвращая результат. Например, функция, которая выдаёт сообщение об ошибке с некоторыми, но не всеми значениями аргументов, либо выводит значения аргументов в сообщении об ошибке, не является герметичной. Это влияет на то, как система выполняет запросы к представлениям, созданным с барьером безопасности (с указанием security_barrier), или к таблицам с включённой защитой строк. Во избежание неконтролируемой утечки данных система будет проверять условия из политик защиты и определений представлений с барьерами безопасности перед любыми условиями, которые задаёт пользователь в самом запросе и в которых задействуются негерметичные функции. Функции и операторы, помеченные как герметичные, считаются доверенными и могут выполняться перед условиями из политик защиты и представлений с барьерами безопасности. При этом функции, которые не имеют аргументов или которым не передаются никакие аргументы из представления с барьером безопасности или таблицы, не требуется помечать как герметичные, чтобы они выполнялись до условий, связанных с безопасностью. См. CREATE VIEW и Раздел 38.5 . Это свойство может установить только суперпользователь. CALLED ON NULL INPUT
RETURNS NULL ON NULL INPUT
STRICT

CALLED ON NULL INPUT (по умолчанию) показывает, что функция будет вызвана как обычно, если среди её аргументов оказываются значения NULL. В этом случае ответственность за проверку значений NULL и соответствующую их обработку ложится на разработчика функции.

Указание RETURNS NULL ON NULL INPUT или STRICT показывает, что функция всегда возвращает NULL, получив NULL в одном из аргументов. Такая функция не будет вызываться с аргументами NULL, вместо этого автоматически будет полагаться результат NULL. [EXTERNAL ] SECURITY INVOKER
[EXTERNAL ] SECURITY DEFINER

Характеристика SECURITY INVOKER (безопасность вызывающего) показывает, что функция будет выполняться с правами пользователя, вызвавшего её. Этот вариант подразумевается по умолчанию. Вариант SECURITY DEFINER (безопасность определившего) определяет, что функция выполняется с правами пользователя, создавшего её.

Ключевое слово EXTERNAL (внешняя) допускается для соответствия стандарту SQL, но является необязательным, так как, в отличие от SQL, эта характеристика распространяется на все функции, а не только внешние. PARALLEL

Указание PARALLEL UNSAFE означает, что эту функцию нельзя выполнять в параллельном режиме и присутствие такой функции в операторе SQL приводит к выбору последовательного плана выполнения. Это характеристика функции по умолчанию. Указание PARALLEL RESTRICTED означает, что функцию можно выполнять в параллельном режиме, но только в ведущем процессе группы. PARALLEL SAFE показывает, что функция безопасна для выполнения в параллельном режиме без ограничений.

Функции должны помечаться как небезопасные для параллельного выполнения, если они изменяют состояние базы данных, вносят изменения в транзакции, например, используя подтранзакции, обращаются к последовательностям или пытаются сохранять параметры (например, используя setval). Ограниченно параллельными должны помечаться функции, которые обращаются к временным таблицам, состоянию клиентского подключения, курсорам, подготовленным операторам или разнообразному состоянию обслуживающего процесса, которое система не может синхронизировать в параллельном режиме (например, setseed может выполнять только ведущий процесс группы, так как изменения, внесённые другим процессом, не передаются ведущему). Вообще, если функция помечена как безопасная, тогда как она является ограниченной или небезопасной, либо если она помечена как ограниченно безопасная, не являясь безопасной, при попытке вызвать её в параллельном запросе она может выдавать ошибки или неверные результаты. Функции на языке C при неправильной пометке теоретически могут проявлять полностью неопределённое поведение, так как система никак не может защититься от произвольного кода на C, но чаще все они будут вести себя не хуже, чем любая другая функция. В случае сомнений функцию следует помечать как небезопасную (UNSAFE), что и имеет место по умолчанию. стоимость_выполнения

Положительное число, задающее примерную стоимость выполнения функции, в единицах cpu_operator_cost . Если функция возвращает множество, это число задаёт стоимость для одной строки. Если стоимость не указана, для функций на C и внутренних функций она считается равной 1 единице, а для функций на всех других языках - 100 единицам. При больших значениях планировщик будет стараться не вызывать эту функцию чаще, чем это необходимо. строк_в_результате

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

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

Если в определение функции добавлено SET , то действие команды SET LOCAL , выполняемой внутри функции для того же параметра, ограничивается телом функции: предыдущее значение параметра так же будет восстановлено при завершении функции. Однако обычная команда SET (без LOCAL) переопределяет предложение SET , как и предыдущую команду SET LOCAL: действие такой команды будет сохранено и после завершения функции, если только не произойдёт откат транзакции.

За подробными сведениями об именах и значениях параметров обратитесь к SET и Главе 18 . определение

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

Часто бывает полезно заключать определение функции в доллары (см. Подраздел 4.1.2.4), а не в традиционные апострофы. Если не использовать доллары, все апострофы и обратные косые черты в определении функции придётся экранировать, дублируя их. объектный_файл , объектный_символ

Эта форма предложения AS применяется для динамически загружаемых функций на языке C, когда имя функции в коде C не совпадает с именем функции в SQL. Строка объектный_файл задаёт имя файла, содержащего динамически загружаемый объект, а объектный_символ - символ скомпонованной функций, то есть имя функции в исходном коде на языке C. Если объектный символ опущен, предполагается, что он совпадает с именем определяемой SQL-функции. В C имена всех функций должны быть различными, поэтому перегружаемым функциям, реализованным на C, нужно давать разные имена (например, включать в имена C обозначения типов аргументов).

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

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

IsStrict

Равнозначно указанию STRICT или RETURNS NULL ON NULL INPUT . isCachable

Свойство isCachable - устаревший эквивалент IMMUTABLE ; оно всё ещё поддерживается ради обратной совместимости.

Имена атрибутов являются регистронезависимыми.

За дополнительной информацией о разработке функций обратитесь к Разделу 35.3 .

Перегрузка

Postgres Pro допускает перегрузку функций; то есть, позволяет использовать одно имя для нескольких различных функций, если у них различаются типы входных аргументов. Независимо от того, используете вы эту возможность или нет, она требует предосторожности при вызове функций в базах данных, где одни пользователи не доверяют другим; см. Раздел 10.3 .

Две функции считаются совпадающими, если они имеют одинаковые имена и типы входных аргументов, параметры OUT игнорируются. Таким образом, например, эти объявления вызовут конфликт:

CREATE FUNCTION foo(int) ... CREATE FUNCTION foo(int, out text) ...

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

CREATE FUNCTION foo(int) ... CREATE FUNCTION foo(int, int default 42) ...

Вызов foo(10) завершится ошибкой из-за неоднозначности в выборе вызываемой функции.

Замечания

В объявлении аргументов функции и возвращаемого значения допускается полный синтаксис описания типа SQL . Однако модификаторы типа в скобках (например, поле точности для типа numeric) команда CREATE FUNCTION не учитывает. Так что, например, CREATE FUNCTION foo (varchar(10)) ... создаст такую же функцию, что и CREATE FUNCTION foo (varchar) ... .

При замене существующей функции с помощью CREATE OR REPLACE FUNCTION есть ограничения на изменения имён параметров. В частности, нельзя изменить имя, уже назначенное любому входному параметру (хотя можно добавить имена ранее безымянным параметрам). Также, если у функции более одного выходного параметра, нельзя изменять имена выходных параметров, так как это приведёт к изменению имён столбцов анонимного составного типа, описывающего результат функции. Эти ограничения позволяют гарантировать, что существующие вызовы функции не перестанут работать после её замены.

Если функция объявлена как STRICT с аргументом VARIADIC , при оценивании строгости проверяется, что весь переменный массив в целом не NULL. Если же в этом массиве содержатся элементы NULL, функция будет вызываться.

Примеры

Ниже приведено несколько простых вводных примеров. За дополнительными сведениями и примерами обратитесь к Разделу 35.3 .

CREATE FUNCTION add(integer, integer) RETURNS integer AS "select $1 + $2;" LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT;

Функция увеличения целого числа на 1, использующая именованный аргумент, на языке PL/pgSQL :

CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$ BEGIN RETURN i + 1; END; $$ LANGUAGE plpgsql;

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

CREATE FUNCTION dup(in int, out f1 int, out f2 text) AS $$ SELECT $1, CAST($1 AS text) || " is text" $$ LANGUAGE SQL; SELECT * FROM dup(42);

То же самое можно сделать более развёрнуто, явно объявив составной тип:

CREATE TYPE dup_result AS (f1 int, f2 text); CREATE FUNCTION dup(int) RETURNS dup_result AS $$ SELECT $1, CAST($1 AS text) || " is text" $$ LANGUAGE SQL; SELECT * FROM dup(42);

Ещё один способ вернуть несколько столбцов - применить функцию TABLE:

CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text) AS $$ SELECT $1, CAST($1 AS text) || " is text" $$ LANGUAGE SQL; SELECT * FROM dup(42);

Однако пример с TABLE отличается от предыдущих, так как в нём функция на самом деле возвращает не одну, а набор записей.

Разработка защищённых функций SECURITY DEFINER

Так как функция SECURITY DEFINER выполняется с правами пользователя, создавшего её, необходимо позаботиться о том, чтобы её нельзя было использовать не по назначению. В целях безопасности, в пути search_path следует исключить любые схемы, доступные на запись недоверенным пользователям. Это не позволит злонамеренным пользователям создать свои объекты (например, таблицы, функции и операторы), которые замаскируют объекты, используемые функцией. Особенно важно в этом отношении исключить схему временных таблиц, которая по умолчанию просматривается первой, а право записи в неё по умолчанию имеют все. Соответствующую защиту можно организовать, поместив временную схему в конец списка поиска. Для этого следует сделать pg_temp последней записью в search_path . Безопасное использование демонстрирует следующая функция:

CREATE FUNCTION check_password(uname TEXT, pass TEXT) RETURNS BOOLEAN AS $$ DECLARE passed BOOLEAN; BEGIN SELECT (pwd = $2) INTO passed FROM pwds WHERE username = $1; RETURN passed; END; $$ LANGUAGE plpgsql SECURITY DEFINER -- Установить безопасный путь поиска: сначала доверенная схема(ы), затем "pg_temp". SET search_path = admin, pg_temp;

Эта функция должна обращаться к таблице admin.pwds , но без предложения SET или с предложением SET , включающим только admin , её можно «обмануть», создав временную таблицу pwds .

До PostgreSQL 8.3 предложение SET отсутствовало, так что старые функции могут содержать довольно сложную логику для сохранения, изменения и восстановления переменной search_path . Существующее теперь предложение SET позволяет сделать это намного проще.

Также следует помнить о том, что по умолчанию право выполнения для создаваемых функций имеет роль PUBLIC (за подробностями обратитесь к GRANT ). Однако часто требуется разрешить доступ к функциям, работающим в контексте определившего, только некоторым пользователям. Для этого необходимо отозвать стандартные права PUBLIC и затем дать права на выполнение индивидуально. Чтобы не образовалось окно, в котором новая функция будет недоступна совсем, создайте её и назначьте права в одной транзакции. Например, так:

BEGIN; CREATE FUNCTION check_password(uname TEXT, pass TEXT) ... SECURITY DEFINER; REVOKE ALL ON FUNCTION check_password(uname TEXT, pass TEXT) FROM PUBLIC; GRANT EXECUTE ON FUNCTION check_password(uname TEXT, pass TEXT) TO admins; COMMIT;

Совместимость

Команда CREATE FUNCTION определена в SQL:1999 и более поздних стандартах. Версия, реализованная в Postgres Pro , близка к стандартизированной, но соответствует ей не полностью. В частности, непереносимы атрибуты, а также различные языки реализаций.

Для совместимости с другими СУБД режим_аргумента можно записать после имя_аргумента или перед ним, но стандарту соответствует только первый вариант.

Для определения значений по умолчанию для параметров стандарт SQL поддерживает только синтаксис с ключевым словом DEFAULT . Синтаксис со знаком = используется в T-SQL и Firebird.

Внимание! Данная работа построена на основе перевода раздела «17.1. Stored Routines and the Grant Tables» описания ПО MySQL 5.0.19, «Reference Manual. It documents MySQL 5.0 through 5.0.19. Document generated on: 2006-01-23 (revision:995)»
``Сначала прочти все, а потом пробуй примеры"

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

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

Хранимые программы (процедуры и функции) поддерживаются в MySQL 5.0. Хранимые процедуры - набор SQL -выражений, который может быть сохранен на сервере. Как только это сделано, клиенту уже не нужно повторно передавать запрос, а требуется просто вызвать хранимую программу.

Это может быть полезным тогда, когда:

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

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

Хранимые подпрограммы также позволяют вам использовать библиотеки функций, хранимые в БД сервера. Эта возможность представлена для многих современных языков программирования, которые позволяют вызывать их непосредственно (например, используя классы).

MySQL следует в синтаксисе за SQL:2003 для хранимых процедур, который уже используется в IBM"s DB2.

От слов к делу…

При создании, модификации, удалении хранимых подпрограмм сервер манипулирует с таблицей mysql.proc

Начиная с MySQL 5.0.3 требуются следующие привилегии:

CREATE ROUTINE для создания хранимых процедур

ALTER ROUTINE необходимы для изменения или удаления процедур. Эта привилегия автоматически назначается создателю процедуры (функции)

EXECUTE привилегия потребуется для выполнения подпрограммы. Тем не менее, автоматически назначается создателю процедуры (функции). Также, по умолчанию, SQL SECURITY параметр для подпрограммы DEFINER , который разрешает пользователям, имеющим доступ к БД вызывать подпрограммы, ассоциированные с этой БД.

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

Хранимая подпрограмма представляет собой процедуру или функцию. Хранимые подпрограммы создаются с помощью выражений CREATE PROCEDURE или CREATE FUNCTION . Хранимая подпрограмма вызывается, используя выражение CALL , причем только возвращающие значение переменные используются в качестве выходных. Функция может быть вызвана подобно любой другой функции и может возвращать скалярную величину. Хранимые подпрограммы могут вызывать другие хранимые подпрограммы.

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

• Когда подпрограмма вызывается, то подразумевается, что надо произвести вызов USE db_name (и отменить использование базы, когда подпрограмма завершилась, и база больше не потребуется)
• Вы можете квалифицировать обычные имена с именем базы данных. Это может быть использовано, чтобы ссылаться на подпрограмму, которая - не в текущей базе данных. Например, для выполнения хранимой процедуры p или функции f которые связаны с БД test , вы можете сказать интерпретатору команд так: CALL test.p() или test.f() .
• Когда база данных удалена, все загруженные подпрограммы связанные с ней тоже удаляются. В MySQL 5.0.0, загруженные подпрограммы - глобальные и не связанны с базой данных. Они наследуют по умолчанию базу данных из вызывающего оператора. Если USE db_name выполнено в пределах подпрограммы, оригинальная текущая БД будет восстановлена после выхода из подпрограммы (Например текущая БД db_11 , делаем вызов подпрограммы, использующей db_22 , после выхода из подпрограммы остается текущей db_11)

MySQL поддерживает полностью расширения, которые разрешают юзать обычные SELECT выражения (без использования курсоров или локальных переменных) внутри хранимых процедур. Результирующий набор, возвращенный от запроса, а просто отправляется напрямую клиенту. Множественный SELECT запрос генерирует множество результирующих наборов, поэтому клиент должен использовать библиотеку, поддерживающую множественные результирующие наборы.

CREATE PROCEDURE - создать хранимую процедуру.

CREATE FUNCTION - создать хранимую функцию.

Синтаксис:

CREATE PROCEDURE имя_процедуры ([параметр_процедуры[,...]])
[характеристёика...] тело_подпрограммы

CREATE FUNCTION имя_функции ([параметр_функции[,...]])
RETURNS тип
[характеристика...] тело_подпрограммы

параметр_процедуры:
[ IN | OUT | INOUT ] имя_параметра тип
параметр_функции:
имя_параметра тип

тип:
Любой тип данных MySQL

характеристика:
LANGUAGE SQL
| DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT "string"

тело_подпрограммы:
Правильное SQL выражение.

Рассмотрим все на практике.

Сначала создадим хранимую процедуру следующим запросом:

CREATE PROCEDURE `my_proc`(OUT t INTEGER(11))
NOT DETERMINISTIC
SQL SECURITY INVOKER
COMMENT ""
BEGIN
select val1+val2 into "t" from `my` LIMIT 0,1;
END;

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

После этого вызовем ее:

CALL my_proc(@a);
SELECT @a;

Для отделения внутреннего запроса от внешнего всегда используют разделитель отличный от обычно (для задания используют команду DELIMITER <строка/символ>)

Вот еще один пример с учетом всех требований.

Mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
-> BEGIN
-> SELECT COUNT(*) INTO param1 FROM t;
-> END;
-> //

mysql> delimiter ;
mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @a;
+------+
| @a |
+------+
| 3 |
+------+
1 row in set (0.00 sec)

Весь процесс можно пронаблюдать на рисунке ниже:

Триггеры

Поддержка триггеров появилась в MySQL начиная с версии 5.0.2.

Триггер - поименованный объект БД, который ассоциирован с таблицей и активируемый при наступлении определенного события, события связанного с этой таблицей.

Например, нижеприведенный код создает таблицу и INSERT триггер. Триггер суммирует значения, вставляемые в один из столбцов таблицы.

Mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
-> FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.06 sec)

Объявим переменную sum и присвоим ей значение 1. После этого при каждой вставке в таблицу account значение этой переменной будет увеличивать согласно вставляемой части.

Замечание . Если значение переменной не инициализировано, то триггер работать не будет!

Синтаксис создания триггера

CREATE

TRIGGER имя_триггера время_триггера событие_срабатывания_триггера
ON имя_таблицы FOR EACH ROW выражение_выполняемое_при_срабатывании_триггера

Если с именем триггера и именем пользователя все понятно сразу, то о «времени триггера» и «событии» поговорим отдельно.

время_триггера

Определяет время свершения действия триггера. BEFORE означает, что триггер выполнится до завершения события срабатывания триггера, а AFTER означает, что после. Например, при вставке записей (см. пример выше) наш триггер срабатывал до фактической вставки записи и вычислял сумму. Такой вариант уместен при предварительном вычислении каких-то дополнительных полей в таблице или параллельной вставке в другую таблицу.

событие_срабатывания_триггера

Здесь все проще. Тут четко обозначается, при каком событии выполняется триггер.

• INSERT: т.е. при операциях вставки или аналогичных ей выражениях (INSERT, LOAD DATA, и REPLACE)
• UPDATE: когда сущность (строка) модифицирована
• DELETE: когда запись удаляется (запросы, содержащие выражения DELETE и/или REPLACE)

CREATE [ OR REPLACE ] FUNCTION
имя_функции ([ [ метод_аргумента ][ имя_аргумента ] тип_ аргумента [,…] ])
RETURNS тип_возвращаемого_значения
AS " определение "
LANGUAGE " язык "
[ WITH ( атрибут […])]

• CREATE FUNCTION имя_функции ([[ метод_аргумента ] [имя_аргумента ] тип_ аргумента [,…] ]) - после ключевых слов CREATE FUNCTION указывается имя создаваемой функции, после чего в круглых скобках перечисляются аргументы, разделенные запятыми. Для каждого аргумента достаточно указать только тип, но при желании можно задать метод (in, out, inout ; по умолчанию in ) и имя.Если список в круглых скобках пуст, функция вызывается без аргументов (хотя сами круглые скобки обязательно должны присутствовать как в определении функции, так и при ее использовании). Ключевые слова OR REPLACE используются для изменения уже существующей функции.
  
• RETURNS тип_возвращаемого_значения -тип данных, возвращаемый функцией .
  
• AS "определение "- программное определение функции. В процедурных языках (таких, как PL/pgSQL) оно состоит из кода функции. Для откомпилированных функций С указывается абсолютный системный путь к файлу, содержащему объектный код.
  
• LANGUAGE "язык ". Название языка, на котором написана функция. В аргументе может передаваться имя любого процедурного языка (такого, как plpgsql или plperl, если соответствующая поддержка была установлена при компиляции),С или SQL.
  [
• WITH (атрибут [. ...]) ] -атрибут может принимать два значения: iscachable и isstrict.
  iscachable . Оптимизатор может использовать предыдущие вызовы функций для ускоренной обработки будущих вызовов с тем же набором аргументов. Кэширование обычно применяется при работе с функциями, сопряженны­ми с большими затратами ресурсов, но возвращающими один и тот же ре­зультат при одинаковых значениях аргументов.
  isstrict . Функция всегда возвращает NULL в случае, если хотя бы один из ее аргументов равен NULL. При передаче атрибута isstrict результат возвраща­ется сразу, без фактического выполнения функции.
  

В PostgreSQL c оздание функций на языке С разрешено только суперпользователям, поскольку эти функции могут содержать системные вызовы,представляющие потенциальную угрозу для безопасности системы. Рассмотрим создание функций sql и plpgsql .

Создание функций SQL

CREATE [ OR REPLACE ] FUNCTION
имя_функции ([ [ метод_аргумента ][ имя_аргумента ] тип_ аргумента [,…] ])
RETURNS тип_возвращаемого_значения
AS "
оператор SQL ;
[оператор SQL ;
… ]
"
LANGUAGE sql
[ WITH ( атрибут […])] ;

• В теле функции sql могут стоять только операторы языка SQL, любые (Select, insert, delete,create,...) за исключением операторов управления транзакциями (commit, rollback...). Возвращаемым значением является результат выполнения оператора SELECT, его тип должен совпадать с типом, указанным после RETURNS . Если в теле функции несколько операторов SELECT, функция вернет результат выполнения последнего такого оператора. Если функция sql не содержит операторов SELECT, тип результата для нее следует указать void (фактически это процедура).
  

Примеры создания и использования функций sql

Пример 1. Создание функции, возвращающей столбец текстовых значений

CREATE FUNCTION onef (integer) RETURNS SETOF character AS "
-- Функция возвращает имена поставщиков с рейтингом больше $1
select names from s where rg>$1;
" LANGUAGE sql;

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

SELECT onef(10) AS sname ;

Результат

Пример 2. Создание функции, возвращающей столбец записей

CREATE FUNCTION manyf (integer) RETURNS SETOF record AS "
- - Функция возвращает сведения о поставщиках с рейтингом больше $1
select ns,names,rg,town from s where rg>$1 order by ns;
" LANGUAGE sql;

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

Использование функции, возвращающей столбец записей

SELECT manyf(10) AS result ;

Результат

Создание функций plpgSQL

CREATE [ OR REPLACE ] FUNCTION
имя _ функции ([ [ метод _ аргумента ][ имя _ аргумента ] тип _ аргумента [,…] ])
RETURNS тип_возвращаемого_значения
AS "
[ DECLARE
объявления ]
BEGIN
оператор;
[ оператор;… ]
THEN

[ WHEN условие [ OR условие ... ] THEN
операторы обработки исключения;
... ]
END;
"
LANGUAGE plpgsql
[ WITH ( атрибут […])] ;

• В теле функции обращение к параметрам осуществляется по имени или по номеру: $1 – первый параметр, $2- второй параметр и т.д.
  
• В теле функции plpgsql кроме операторов SQL могут применяться конструкции языка PL/pgSQL, представляющего собой процедурное расширение SQL. Это могум быть операторы присваивания, условные операторы, циклы и т.п. Результат возвращается командой RETURN. В теле функции plpgsql обязательно должна быть хотя бы одна команда RETURN, кроме случая, когда тип результата void. Подробнее см. http://www.postgresql.org/docs/8.2/interactive/extend.html
• Локальные переменные, используемые в теле функции, объявляются в блоке DECLARE
  DECLARE
  имя_переменной тип_переменной;
  [ имя_переменной тип_переменной;… ]
  
• В блоке EXCEPTION обрабатываются ошибки. Условие – наименование ошибки (перечень см. http://www.sbin.org/doc/pg/doc/errcodes-appendix.html в колонке Constant). Если все ошибки обрабатываются по одной схеме (или вам просто лень подумать, какие здесь могут быть ошибки и найти, как они называются), этот блок может выглядеть так
  EXCEPTION
  WHEN others THEN
  RAISE exception " message of error";

  Функция RAISE уровня exception генерирует исключение и выдает сообщение об ошибке (подробнее о функции RAISE см. http://www.sbin.org/doc/pg/doc/plpgsql-errors-and-messages.html ).
  

Примеры создания и использования функций plpgsql

Пример 3. Создание функции , возвращающей целое значение

CREATE FUNCTION apf (character) RETURNS integer AS "
DECLARE
i integer;
-- Функция вычисляет количество поставок детали $1
BEGIN
select count(*) from spj into i where spj.np=$1;
-- возвращение результата
return i;
END;
" LANGUAGE plpgsql;

Пример 4. Создание функции, возвращающей вещественное значение

CREATE FUNCTION avgves (character) RETURNS real AS "
DECLARE
aves real;
-- Функция вычисляет средний вес поставок детали $1
BEGIN
select avg(spj.kol * p.ves) from spj,p into aves where spj.np=$1 and spj.np=p.np ;
-- возвращение результата
return aves ;
END;
" LANGUAGE plpgsql;

Пример 5. Использование функций в классическом SELECT

SELECT p.np, p.namep,
apf (p.np) AS kol , avgves (p.np) as sves,
apf(p.np)* avgves(p.np) as oves FROM p;

Результат

Np	namep	kol	sves	oves
P1	Гайка	1	1200	1200
P2	Болт	2	2550	5100
P3	Винт	9	6611.11	59500.001953125
P4	Винт	2	9100	18200
P5	Кулачок	4	3300	13200
P6	Блюм	4	6175	24700

Пример 6. Использование функций, нестандартный вариант (только PostgreSql)

SELECT apf("P5") AS kol ;

Результат

kol

4