Sql создать функцию. Разработка защищённых функций SECURITY DEFINER

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

Функции Scalar

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

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

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

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

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

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

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

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

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

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

Функции Inline

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

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

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

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

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

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

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

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

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

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

Функции Multi-statement

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

<определение_мульти_функции>::={CREATE | ALTER }FUNCTION [владелец.] имя_функции([ { @имя_параметра скаляр_тип_данных [=default]}[,...n]])RETURNS @имя_параметра TABLE <определение_таблицы> ]BEGIN<тело_функции>RETURN END

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

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

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

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

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

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

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

Введение

Многие считают, что они похожи на процедуры различных (соответственно, кроме MS SQL). Пожалуй, это действительно так. У них есть схожие параметры, они могут выдавать схожие значения. Более того, в ряде случаев они соприкасаются. Например, они сочетаются с базами данных DDL и DML, а также с функциями пользователя (кодовое название - UDF).

В действительности же хранимые процедуры SQL обладают широким спектром преимуществ, которые выделяют их среди подобных процессов. Безопасность, вариативность программирования, продуктивность - все это привлекает пользователей, работающих с базами данных, все больше и больше. Пик популярности процедур пришелся на 2005-2010 годы, когда вышла программа от "Майкрософт" под названием «SQL Server Management Studio». С ее помощью работать с базами данных стало гораздо проще, практичнее и удобнее. Из года в год такой набирал популярность в среде программистов. Сегодня же является абсолютно привычной программой, которая для пользователей, «общающихся» с базами данных, встала наравне с «Экселем».

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

Для реализации данной технологии работы с информацией существует несколько языков программирования. К ним можно отнести, например, PL/SQL от Oracle, PSQL в системах InterBase и Firebird, а также классический «майкрософтовский» Transact-SQL. Все они предназначены для создания и выполнения хранимых процедур, что позволяет в крупных обработчиках баз использовать собственные алгоритмы. Это нужно и для того, чтобы те, кто осуществляет управление такой информацией, могли защитить все объекты от несанкционированного доступа сторонних лиц и, соответственно, создания, изменения или удаления тех или иных данных.

Продуктивность

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

Безопасность

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

Передача данных

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

Передача данных с помощью параметра типа Output;

Передача данных с помощью оператора возврата;

Передача данных с помощью оператора выбора.

А теперь разберемся, как же выглядит этот процесс изнутри.

1. Создание EXEC-хранимой процедуры в SQL

Вы можете создать процедуру в MS SQL (Managment Studio). После того как создастся процедура, она будет перечислена в программируемый узел базы данных, в которой процедура создания выполняется оператором. Для выполнения хранимые процедуры SQL используют EXEC-процесс, который содержит имя самого объекта.

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

Дело в том, что тело может иметь локальные переменные, расположенные в ней, и эти переменные являются локальными также по отношению к процедурам. Другими словами, их можно рассматривать только внутри тела процедуры Microsoft SQL Server. Хранимые процедуры в таком случае считаются локальными.

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

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

Тело не должно создавать какой-либо другой хранимой процедуры;

Тело не должно создать ложное представление об объекте;

Тело не должно создавать никаких триггеров.

2. Установка переменной в тело процедуры

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

Иногда можно заметить, что несколько переменных установлены в одной строке, и каждый переменный параметр отделяется запятой. Также обратите внимание, что переменная имеет префикс @. В теле процедуры вы можете установить переменную, куда вы хотите. К примеру, переменная @NAME1 может объявлена ​​ближе к концу тела процедуры. Для того чтобы присвоить значение объявленной переменной используется набор личных данных. В отличие от ситуации, когда объявлено более одной переменной в одной строке, в такой ситуации используется только один набор личных данных.

Часто пользователи задают вопрос: «Как назначить несколько значений в одном операторе в теле процедуры?» Что ж. Вопрос интересный, но сделать это гораздо проще, чем вы думаете. Ответ: с помощью таких пар, как «Select Var = значение». Вы можете использовать эти пары, разделяя их запятой.

В самых различных примерах люди показывают создание простой хранимой процедуры и выполнение ее. Однако процедура может принимать такие параметры, что вызывающий ее процесс будет иметь значения, близкие к нему (но не всегда). Если они совпадают, то внутри тела начинаются соответствующие процессы. Например, если создать процедуру, которая будет принимать город и регион от вызывающего абонента и возвращать данные о том, сколько авторов относятся к соответствующим городу и региону. Процедура будет запрашивать таблицы авторов базы данных, к примеру, Pubs, для выполнения этого подсчета авторов. Чтобы получить эти базы данных, к примеру, Google загружает сценарий SQL со страницы SQL2005.

В предыдущем примере процедура принимает два параметра, которые на английском языке условно будут называться @State и @City. Тип данных соответствует типу, определенному в приложении. Тело процедуры имеет внутренние переменные @TotalAuthors (всего авторов), и эта переменная используется для отображения их количества. Далее появляется раздел выбора запроса, который все подсчитывает. Наконец, подсчитанное значение выводится в окне вывода с помощью оператора печати.

Как в SQL выполнить хранимую процедуру

Есть два способа выполнения процедуры. Первый путь показывает, передавая параметры, как разделенный запятыми список выполняется после имени процедуры. Допустим, мы имеем два значения (как в предыдущем примере). Эти значения собираются с помощью переменных параметров процедуры @State и @City. В этом способе передачи параметров важен порядок. Такой метод называется порядковая передача аргументов. Во втором способе параметры уже непосредственно назначены, и в этом случае порядок не важен. Этот второй метод известен как передача именованных аргументов.

Процедура может несколько отклоняться от типичной. Все так же, как и в предыдущем примере, но только здесь параметры сдвигаются. То есть параметр @City хранится первым, а @State хранится рядом со значением по умолчанию. Параметр по умолчанию выделяется обычно отдельно. Хранимые процедуры SQL проходят как просто параметры. В этом случае, при условии, параметр «UT» заменяет значение по умолчанию «СА». Во втором исполнении проходит только одно значение аргумента для параметра @City, и параметр @State принимает значение по умолчанию «СА». Опытные программисты советуют, чтобы все переменные по умолчанию располагались ближе к концу списка параметров. В противном случае исполнение не представляется возможным, и тогда вы должны работать с передачей именованных аргументов, что дольше и сложнее.

4. Хранимые процедуры SQL Server: способы возврата

Существует три важных способа отправки данных в вызванной хранимой процедуре. Они перечислены ниже:

Возврат значения хранимой процедуры;

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

Выбор одной из хранимых процедур.

4.1 Возврат значений хранимых процедур SQL

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

Теперь давайте посмотрим, как выполнить процедуру и вывести значение, возвращаемое ей. Выполнение процедуры требует установления переменной и печати, которая проводится после всего этого процесса. Обратите внимание, что вместо оператора печати вы можете использовать Select-оператор, например, Select @RetValue, а также OutputValue.

4.2 Выход параметра хранимых процедур SQL

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

В нашем примере будет два выходных имени: @TotalAuthors и @TotalNoContract. Они указываются в списке параметров. Эти переменные присваивают значения внутри тела процедуры. Когда мы используем выходные параметры, вызывающий абонент может видеть значение, установленное внутри тела процедуры.

Кроме того, в предыдущем сценарии две переменные объявляются, чтобы увидеть значения, которые установливают хранимые процедуры MS SQL Server в выходном параметре. Тогда процедура выполняется путем подачи нормального значения параметра «CA». Следующие параметры являются выходными и, следовательно, объявленные переменные передаются в установленном порядке. Обратите внимание, что при прохождении переменных выходное ключевое слово также задается здесь. После того, как процедура выполнена успешно, значения, возвращаемые с помощью выходных параметров, выводятся на окно сообщений.

4.3 Выбор одной из хранимых процедур SQL

Эта техника используется для возврата набора значений в виде таблицы данных (RecordSet) к вызывающей хранимой процедуре. В этом примере SQL хранимая процедура с параметрами @AuthID запрашивает таблицу «Авторы» путем фильтрации возвращаемых записей с помощью этого параметра @AuthId. Оператор Select решает, что должно быть возвращено вызывающему хранимой процедуры. При выполнении хранимой процедуры AuthId передается обратно. Такая процедура здесь всегда возвращает только одну запись или же вообще ни одной. Но хранимая процедура не имеет каких-либо ограничений на возвращение более одной записи. Нередко можно встретить примеры, в которых возвращение данных с использованием избранных параметров с участием вычисленных переменных происходит путем предоставления нескольких итоговых значений.

В заключение

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

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

Функция	Возвращаемый результат
Bit_Length()	Количество битов в
Cast(As)	, преобразованное в указанный
Char_Length()	Длина символов
Convert(using)	, преобразованная в соответствии с указанной
Current_Date	Текущая дата
Current_Time()	Текущее время с указанной
Current_TimeStamp()	Текущая дата и время с указанной
Extract(from)	Указанная (Day, Hour и т.п.) из даты
Lower()	, преобразованная к нижнему регистру
Octet_Length()	Число байтов в
Position(in)	Позиция, с которой входит в
Substring(from for)	Часть, начинающаяся с позиции и имеющая указанную
Trim(Leading|Trailing|Both from)	, у которой удалены ведущие | концевые | с обоих сторон
Upper()	, преобразованная к верхнему регистру
User	Определяет идентификатор пользователя

2) Обзор функций MS SQL Server
Так в SQL Server предусмотрено много функций, разделенных на следующие группы:
→ Строковые;
→ Математические;
→ Преобразования;
→ Для работы с данными типа Text и Image;
→ Для работы с датами;
→ Системные;
→ Ниладические (нульместные – без параметров).
А так же целый ряд других функций.

3) Обзор функций Oracle
→ Для работы с ошибками;
→ Числовые;
→ Строковые;
→ Преобразования;
→ Трансляции, для работы с датами;
→ Различного назначения.

Объявление хранимой функции

CREATE FUNCTION ([ [()], …]) RETURNS [()] [[NOT ] DETERMINISTIC ] [CONTAINS SQL|READS SQL DATA|MODIFIES SQL DATA ]
BEGIN

RETURN
END

Ключевые слова
. DETERMINISTIC показывает, возвращает или нет функция одинаковые значения при одних и тех же входных значениях. Например, функция CURRENT_TIME является NOT DETERMINISTIC.
. CONTAINS SQL показывает, что в функции нет SQL-операторов, читающих и модифицирующих данные. Это значение установлено по умолчанию.
. READS SQL DATA показывает, что функция содержит инструкции SELECT или FETCH.
. MODIFIES SQL DATA показывает, что функция содержит инструкции INSERT, UPDATE или DELETE.

Ограничения на недетерминистские функции

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

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

Удаление и изменение хранимых функций

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

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

ALTER FUNCTION ([[{IN|OUT|INOUT }] [()],…])
BEGIN

RETURN
END

Хранимые функции в SQL Server

CREATE FUNCTION ([@ [AS ] [()] [=] [READONLY ],…])
RETURNS [()]
[AS ]
BEGIN

RETURN
END

Пример:

USE B1;
GO
CREATE FUNCTION Quarter(@Dat DateTime) RETURNS int
BEGIN
DECLARE @ISQuarter int;
IF ((Month(@Dat)>=1) And (Month(@Dat)=4) And (Month(@Dat)=8) And (Month(@Dat)=10) And (Month(@Dat)