как называются модули или подпрограммы sql server в которых содержится повторно используемый код

Хранимые процедуры в T-SQL — создание, изменение, удаление

В Microsoft SQL Server для реализации и автоматизации своих собственных алгоритмов (расчётов) можно использовать хранимые процедуры, поэтому сегодня мы с Вами поговорим о том, как они создаются, изменяются и удаляются.

Но сначала немного теории, чтобы Вы понимали, что такое хранимые процедуры и для чего они нужны в T-SQL.

Примечание! Начинающим программистам рекомендую следующие полезные материалы на тему T-SQL:

Что такое хранимые процедуры в T-SQL?

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

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

В хранимых процедурах, в отличие от функций, уже можно выполнять операции модификации данных такие как: INSERT, UPDATE, DELETE. Также в процедурах можно использовать SQL инструкции практически любого типа, например, CREATE TABLE для создания таблиц или EXECUTE, т.е. вызов других процедур. Исключение составляет несколько типов инструкций таких как: создание или изменение функций, представлений, триггеров, создание схем и еще несколько других подобных инструкций, например, также нельзя в хранимой процедуре переключать контекст подключения к базе данных (USE).

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

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

Примеры работы с хранимыми процедурами в Microsoft SQL Server

Исходные данные для примеров

Все примеры ниже будут выполнены в Microsoft SQL Server 2016 Express. Для того чтобы продемонстрировать, как работают хранимые процедуры с реальными данными, нам нужны эти данные, давайте их создадим. Например, давайте создадим тестовую таблицу и добавим в нее несколько записей, допустим, что это будет таблица, содержащая список товаров с их ценой.

Данные есть, теперь давайте переходить к созданию хранимых процедур.

Создание хранимой процедуры на T-SQL – инструкция CREATE PROCEDURE

Хранимые процедуры создаются с помощью инструкции CREATE PROCEDURE, после данной инструкции Вы должны написать название Вашей процедуры, затем в случае необходимости в скобочках определить входные и выходные параметры. После этого Вы пишите ключевое слово AS и открываете блок инструкций ключевым словом BEGIN, закрываете данный блок словом END. Внутри данного блока Вы пишите все инструкции, которые реализуют Ваш алгоритм или какой-то последовательный расчет, иными словами, программируете на T-SQL.

Для примера давайте напишем хранимую процедуру, которая будет добавлять новую запись, т.е. новый товар в нашу тестовую таблицу. Для этого мы определим три входящих параметра: @CategoryId – идентификатор категории товара, @ProductName — наименование товара и @Price – цена товара, данный параметр будет у нас необязательный, т.е. его можно будет не передавать в процедуру (например, мы не знаем еще цену), для этого в его определении мы зададим значение по умолчанию. Эти параметры в теле процедуры, т.е. в блоке BEGIN…END можно использовать, так же как и обычные переменные (как Вы знаете, переменные обозначаются знаком @). В случае если Вам нужно указать выходные параметры, то после названия параметра указывайте ключевое слово OUTPUT (или сокращённо OUT).

В блоке BEGIN…END мы напишем инструкцию добавления данных, а также в завершении процедуры инструкцию SELECT, чтобы хранимая процедура вернула нам табличные данные о товарах в указанной категории с учетом нового, только что добавленного товара. Также в этой хранимой процедуре я добавил обработку входящего параметра, а именно удаление лишних пробелов в начале и в конце текстовой строки с целью исключения ситуаций, когда случайно занесли несколько пробелов.

Вот код данной процедуры (его я также прокомментировал).

Запуск хранимой процедуры на T-SQL – команда EXECUTE

Запустить хранимую процедуру, как я уже отмечал, можно с помощью команды EXECUTE или EXEC. Входящие параметры передаются в процедуры путем простого их перечисления и указания соответствующих значений после названия процедуры (для выходных параметров также нужно указывать команду OUTPUT). Однако название параметров можно и не указывать, но в этом случае необходимо соблюдать последовательность указания значений, т.е. указывать значения в том порядке, в котором определены входные параметры (это относится и к выходным параметрам).

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

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

Изменение хранимой процедуры на T-SQL – инструкция ALTER PROCEDURE

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

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

Удаление хранимой процедуры на T-SQL – инструкция DROP PROCEDURE

В случае необходимости можно удалить хранимую процедуру, это делается с помощью инструкции DROP PROCEDURE.

Например, давайте удалим созданную нами тестовую процедуру.

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

У меня все, надеюсь, материал был Вам интересен и полезен, пока!

Источник

Хранимые процедуры (компонент Database Engine)

обрабатывают входные параметры и возвращают вызывающей программе значения в виде выходных параметров;

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

возвращают значение состояния вызывающей программе, таким образом передавая сведения об успешном или неуспешном завершении (и причины последнего).

Преимущества хранимых процедур

В следующем списке описываются преимущества использования процедур.

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

Большая безопасность
Многие пользователи и клиентские программы могут выполнять операции с базовыми объектами базы данных посредством процедур, даже если у них нет прямых разрешений на доступ к базовым объектам. Процедура проверяет, какие из процессов и действий могут выполняться, и защищает базовые объекты базы данных. Это устраняет необходимость предоставлять разрешения на уровне индивидуальных объектов и упрощает формирование уровней безопасности.

Предложение EXECUTE AS может быть указано в инструкции CREATE PROCEDURE, чтобы разрешить олицетворение других пользователей или разрешить пользователям или приложениям выполнять определенные действия баз данных без необходимости иметь прямые разрешения на базовые объекты и команды. Например, для некоторых действий, таких как TRUNCATE TABLE, предоставить разрешения нельзя. Чтобы выполнить инструкцию TRUNCATE TABLE, у пользователя должны быть разрешения ALTER на нужную таблицу. Предоставление разрешений ALTER не всегда подходит, так как фактические разрешения пользователя выходят за пределы возможности усечения таблицы. Заключив инструкцию TRUNCATE TABLE в модуль и указав, что этот модуль должен выполняться от имени пользователя, у которого есть разрешения на изменение таблицы, можно предоставить разрешение на усечение таблицы пользователю с разрешением EXECUTE для этого модуля.

При вызове процедуры через сеть виден только вызов на выполнение процедуры. Следовательно, злоумышленники не смогут видеть имена объектов таблиц и баз данных, внедрять свои инструкции Transact-SQL или выполнять поиск важных данных.

Использование параметров в процедурах помогает предотвратить атаки типа «инъекция SQL». Поскольку входные данные параметра обрабатываются как литеральные значения, а не как исполняемый код, злоумышленнику будет труднее вставить команду в инструкции Transact-SQL в процедуре и создать угрозу безопасности.

Процедуры могут быть зашифрованы, что позволяет замаскировать исходный код. Дополнительные сведения см. в статье SQL Server Encryption.

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

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

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

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

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

Временные процедуры
Временные процедуры — это один из видов пользовательских процедур. Временные процедуры схожи с постоянными процедурами, за исключением того, что они хранятся в базе данных tempdb. Существует два вида временных процедур: локальные и глобальные. Они отличаются друг от друга именами, видимостью и доступностью. Имена локальных временных процедур начинаются с одного знака диеза (#); они видны только текущему соединению пользователя и удаляются, когда закрывается соединение. Имена глобальных временных процедур начинаются с двух знаков диеза (##); они видны любому пользователю и удаляются после окончания последнего сеанса, использующего процедуру.

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

Расширенные хранимые процедуры в будущих версиях SQL Serverбудут удалены. Не используйте его при работе над новыми приложениями и как можно быстрее измените приложения, в которых он в настоящее время используется. Вместо них рекомендуется создавать процедуры CLR. Этот метод более надежен и безопасен, чем использование расширенных хранимых процедур.

Источник

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

Создание и выполнение процедур

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

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

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

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

Создадим хранимую процедуру для извлечения данных из этой таблицы:

Поскольку команда CREATE PROCEDURE должна вызываться в отдельном пакете, то после команды USE, которая устанавливает текущую базу данных, используется команда GO для определения нового пакета.

После имени процедуры должно идти ключевое слово AS.

Для отделения тела процедуры от остальной части скрипта код процедуры нередко помещается в блок BEGIN. END:

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

Выполнение процедуры

Для выполнения хранимой процедуры вызывается команда EXEC или EXECUTE :

Удаление процедуры

Для удаления процедуры применяется команда DROP PROCEDURE :

Источник

Новый стандарт хранимых процедур в языке SQL

1. Введение

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

Во время подготовки этой статьи только что успешно завершилась редакционная сессия ISO, которая явилась завершающим шагом в принятии SQL-92/PSM как международного стандарта. Статья может содержать некоторые упущения или неточности, поскольку окончательная версия стандарта еще недоступна.

Всюду в ней для ссылки на SQL-92/PSM используется сокращение PSM-96. Отметим, что во включенных в статью фрагментах кода не предусматривается обработка ошибок, и они необязательно решают какую-либо полезную задачу.

2. Многооператорные процедуры

Стандарт SQL-92 предусматривает возможность исполнения отдельных операторов SQL в программе, написанной на включающем языке, например C или КОБОЛЕ. Например, программа, которая вставляет некоторого студента в список и далее регистрирует его, может быть написана следующим образом:

После исполнения каждого из операторов INSERT возвращается значение SQLSTATE, которое показывает, успешно или неудачно завершилось его исполнение.

2.1. Составные операторы

Составной оператор (BEGIN/END) позволяет сгруппировать вместе включенные в него операторы.

В среде клиент-сервер это может сократить число посылаемых сообщений, обеспечивая тем самым повышение производительности. Более высокая производительность достигается, вероятно, даже и в централизованной среде.

2.1.1. Атомарность

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

2.1.2. Переменные

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

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

2.1.3. Курсоры

Составные операторы допускают объявление курсоров. Как и в случае с переменными, эти локальные курсоры создаются, когда начинается исполнение составного оператора, и уничтожаются, когда его исполнение завершается.

2.1.4. Указатели условий

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

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

2.1.4.1. Условия

Значения SQLSTATE показывают, успешно или неудачно завершаются операторы SQL. Значение SQLSTATE содержит двухлитерное значение класса и трехлитерное значение подкласса.

Имя условия может объявляться в составном операторе и по выбору пользователя может ассоциироваться со значением SQLSTATE.

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

2.1.4.2. Действие

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

2.1.4.3. Результат

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

Указатель UNDO может быть специфицирован только в составном операторе, для которого специфицировано также ATOMIC.

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

Если ошибка сериализации имеет место во время исполнения одного из операторов вставки INSERT, то исполняется указатель условия. Аннулируется всякая работа, выполненная при вставках. Если i меньше или равно 3, то указатель условия увеличивает i, и исполнение продолжается после атомарного составного оператора. Оператор WHILE (пока) будет вызывать повторение вставок. Если i больше, чем 3, то вновь сигнализируется исключительная ситуация. Это означает, что исполнение всего этого составного оператора заканчивается, и некоторый указатель условия вне области этого примера будет обрабатывать такую исключительную ситуацию.

2.1.4.4. Необрабатываемые условия

Если указатель для обработки условия завершения или исключительной ситуации, которая имела место в некоторой лексической области, отсутствует, то такое условие называется необрабатываемым.

Необрабатываемое условие завершения, например, такое как предупреждение, будет продолжать исполнение после того оператора, который вызвал условие завершения. Необрабатываемое условие исключительной ситуации вызовет повторную сигнализацию условия исключительной ситуации. Если это условие опять окажется необрабатываемым в текущем дереве исполнения, то это условие исключительной ситуации окончательно возвращается клиентскому приложению. Обработка условий более подробно обсуждалась в предыдущем томе SIGMOD Record [3].

2.2. Операторы управления логикой исполнения

PSM-96 предоставляет операторы управления логикой исполнения, которые можно видеть в современных языках программирования.

Оператор ASSIGNMENT (присваивание) присваивает значение переменной или параметру.

Оператор IF (если) исполняет список операторов, заданных во фразе THEN (тогда), если в результате вычисления условия получено TRUE («истина»), а не FALSE («ложь») или UNKNOWN («неизвестно»).

Оператор CASE (случай) имеет две разновидности. В первой из них единственное значение, указанное в начале оператора CASE, проверяется на равенство со значением, заданным для каждой ветви этого оператора.

В другой разновидности каждая ветвь оператора CASE имеет свое собственное проверяемое условие, значение которого вычисляется.

Если ни для одной из ветвей оператора CASE заданные условия не имеют значения «истина», то оператор CASE порождает исключительную ситуацию.

PSM-96 предусматривает несколько типов оператора цикла:

В приведенном ниже примере оператор WHILE используется для того, чтобы заполнить символьную строку точками.

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

2.2.1. Оператор FOR

Оператор FOR (для) отличается от такого оператора, используемого в языках программирования третьего поколения (3GL). Оператор FOR неявным образом открывает курсор, осуществляет выборку строк курсора и исполняет тело оператора FOR по одному разу для каждой строки, а затем закрывает курсор.

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

3. Хранимые подпрограммы

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

3.1. Хранимые процедуры

В клиентских SQL-процедурах могут использоваться следующие виды параметров: input (входной), output (выходной), input и output (входной и выходной) либо none (без параметров). Заключение о виде параметра делается на основе характера использования этого параметра в теле процедуры. В PSM-96 вид параметра может быть явным образом специфицирован автором данной процедуры.

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

3.2. Хранимые функции

SQL-92 поддерживает только хранимые процедуры, но не функции. В PSM-96 добавлена возможность определения функций, которые могут храниться в базе данных точно так же, как и процедуры. Процедуры и функции вместе называются подпрограммами.

В описании функции должна специфицироваться фраза RETURNS (возвращает), указывающая тип значения, которое она будет возвращать. Функции могут иметь только параметры вида IN (входные), что предполагается по умолчанию.

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

3.3. Привилегии

Список привилегий для объектов SQL был расширен включением привилегии EXECUTE (исполнить) для подпрограмм.

Чтобы вызвать подпрограмму, пользователь должен обладать предоставленной ему привилегией EXECUTE на эту подпрограмму.

3.4. Полиморфизм и имена

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

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

3.4.1. Особые имена

Для того чтобы различать две подпрограммы с одним и тем же именем в одной и той же схеме, каждой их таких подпрограмм дается альтернативное и уникальное имя, которое называется ее особым именем (specific name). Такое имя может быть явно специфицировано, когда создается подпрограмма. Если же оно не было специфицировано, оно будет выбираться автоматически. Теперь подпрограмма может идентифицироваться в операторах DROP (удалить), GRANT (предоставить привилегию) или REVOKE (лишить привилегии) несколькими способами:

3.4.2. Вызов подпрограмм

В PSM-96 можно использовать путь как атрибут схемы и модуля. Путь содержит список схем, которые будут исследоваться при поиске подпрограмм-кандидатов на вызов.

PSM-96 определяет отношение предшествования на множестве сравнимых типов данных, которое используется для оценки того, какая подпрограмма является наиболее подходящей для конкретного вызова:

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

Для определения той подпрограммы, которая фактически выбирается для вызова, требуется несколько шагов.

1. Если вызов осуществляется с помощью оператора CALL, то отыскиваются все процедуры с заданным именем. В противном случае отыскиваются все функции с заданным именем.

2. Для дальнейшего анализа оставляются только те подпрограммы, по отношению к которым данный пользователь обладает привилегией на исполнение (EXECUTE).

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

5. Для каждого аргумента, действуя слева направо, поступаем следующим образом 6) :

a) для каждой пары подпрограмм:
Если один из параметров в этой позиции имеет более высокое предшествование по сравнению с другим параметром в той же позиции, удаляем из числа претендентов подпрограмму с более высоким предшествованием.

6. Если осталось более одной подпрограммы, используется та, которая принадлежит схеме, появляющейся раньше в SQL-пути.

3.5. Недетерминированность и побочные эффекты

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

4. Хранимые модули

Клиентские модули в SQL-92 имеют следующий вид:

Модули некоторой формы могут храниться в базе данных в PSM-96.

Каждая подпрограмма в модуле может иметь SQL-тело либо внешнее тело. Имена, используемые для обращения к подпрограммам, представляют собой имена этих подпрограмм, которые они имели бы, если бы они были созданы вне модуля. Если бы модуль администрация был в схеме cat1.админ, то к подпрограмме нанять_учителя можно было бы обращаться как к cat1.админ.нанять_учителя.

Возможности оператора DROP (удалить) были расширены с тем, чтобы он мог удалять модули. Это действие уничтожает все подпрограммы, содержащиеся в данном модуле. Содержащиеся в модуле подпрограммы не могут быть удалены индивидуально.

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

Хранимый модуль может содержать объявленные в нем локальные временные таблицы, как это делается в клиентском модуле. Хранимый модуль не может, однако, содержать объявлений курсоров, так как при завершении работы над PSM-96 были обнаружены некоторые связанные с этим проблемы.

5. Внешние подпрограммы

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

Отображение параметров для внешних подпрограмм подобно отображению, которое имеет место, когда код во включающем языке программирования в клиентском приложении исполняет операторы SQL. Типы данных SQL должны отображаться в типы данных включающего языка. Значение в SQL, возможно, является неопределенным значением, которое должно быть представлено во включающем языке.

Автор может специфицировать два способа для этого типа отображения.

5.1. Стиль параметров GENERAL

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

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

5.2. Стиль параметров SQL

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

Параметры функции звучание2 имеют следующий смысл:

PSM-96 умалчивает, каким образом внешняя подпрограмма связывается с SQL-реализацией. Умалчивается также, исполняется ли такая подпрограмма в том же самом процессе или адресном пространстве, что и SQL-реализация.

5.3. Недетерминированность и побочные эффекты

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

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

Вместе с тем объявление, указывающее, что данная подпрограмма не модифицирует SQL-данные, отрабатывается обязательно. Попытка исполнения в такой подпрограмме какого-либо оператора DML приведет к возникновению исключительной ситуации.

6. Заключение

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

PSM-96 получит дальнейшее развитие в разрабатываемом стандарте SQL3. Обсуждаются, в частности, возможности, подобные именованным параметрам и параметрам по умолчанию. SQL3/PSM будет служить основой для определения методов в абстрактных типах данных (ADT) SQL3.

7. Литература

1) Andrew Eisenberg. New Standard for Stored Procedures in SQL. SIGMOD Record, V. 25, # 4, December 1996.

© Пер. с англ. М.Р. Когаловского.

Переведено и опубликовано с разрешения АСМ.

Источник