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

Храни́мая процеду́ра — объект базы данных, представляющий собой набор SQL-инструкций, который компилируется один раз и хранится на сервере. Хранимые процедуры очень похожи на обыкновенные процедуры языков высокого уровня, у них могут быть входные и выходные параметры и локальные переменные, в них могут производиться числовые вычисления и операции над символьными данными, результаты которых могут присваиваться переменным и параметрам. В хранимых процедурах могут выполняться стандартные операции с базами данных (как DDL, так и DML). Кроме того, в хранимых процедурах возможны циклы и ветвления, то есть в них могут использоваться инструкции управления процессом исполнения.

Хранимые процедуры похожи на определяемые пользователем функции (UDF). Основное различие заключается в том, что пользовательские функции можно использовать как и любое другое выражение в SQL запросе, в то время как хранимые процедуры должны быть вызваны с помощью функции CALL:

CALL процедура(…)

или

EXECUTE процедура(…)

Хранимые процедуры могут возвращать множества результатов, то есть результаты запроса SELECT. Такие множества результатов могут обрабатываться, используя курсоры, другими сохраненными процедурами, возвращая указатель результирующего множества, либо же приложениями. Хранимые процедуры могут также содержать объявленные переменные для обработки данных и курсоров, которые позволяют организовать цикл по нескольким строкам в таблице. Стандарт SQL предоставляет для работы выражения IF, LOOP, REPEAT, CASE и многие другие. Хранимые процедуры могут принимать переменные, возвращать результаты или изменять переменные и возвращать их, в зависимости от того, где переменная объявлена.

Реализация хранимых процедур варьируется от одной СУБД к другой. Большинство крупных поставщиков баз данных поддерживают их в той или иной форме. В зависимости от СУБД, хранимые процедуры могут быть реализованы на различных языках программирования, таких, как SQL, Java, C или C++. Хранимые процедуры, написанные не на SQL, могут самостоятельно выполнять SQL-запросы, а могут и не выполнять. Все более широкое использование хранимых процедур привело к появлению процедурных элементов в языке SQL стандарта SQL:1999 и SQL: 2003 в части SQL/PSM. Это сделало SQL императивным языком программирования. Большинство СУБД предлагает собственные проприетарные и расширения производителя, сверх SQL/PSM.

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

Хранимые процедуры обычно создаются с помощью языка SQL и конкретной его реализации в выбранной СУБД. Например, для этих целей в СУБД Microsoft SQL Server существует язык Transact-SQL, в Oracle — PL/SQL, в InterBase и Firebird — PSQL, в PostgreSQL — PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, в IBM DB2 — SQL/PL (англ.), в Informix — SPL. MySQL достаточно близко следует стандарту SQL:2003, её язык похож на SQL/PL.

В некоторых СУБД возможно использование хранимых процедур, написанных на любом языке программирования, способном создавать независимые исполняемые файлы, например, на C++ или Delphi. В терминологии Microsoft SQL Server такие процедуры называются расширенными хранимыми процедурами и являются просто функциями, содержащимися в Win32-DLL. А, например, в Interbase и Firebird для функций, вызываемых из DLL/SO, определено другое название — UDF (User Defined Function). В MS SQL 2005 появилась возможность написания хранимых процедур на любом языке .NET, а от расширенных хранимых процедур в будущем планируется отказаться. СУБД Oracle, в свою очередь, допускает написание хранимых процедур на языке Java. В IBM DB2 написание хранимых процедур и функций на обычных языках программирования является традиционным способом, поддерживаемым с самого начала, а процедурное расширение SQL было добавлено в эту СУБД только в достаточно поздних версиях, после его включения в стандарт ANSI. Также процедуры на Java и С поддерживает Informix.

В СУБД Oracle хранимые процедуры могут объединяться в так называемые пакеты (англ. packages). Пакет состоит из двух частей — спецификации (англ. package specification), в которой указывается определение хранимой процедуры, и тела (англ. package body), где находится её реализация. Таким образом Oracle позволяет отделить интерфейс программного кода от его реализации.

В СУБД IBM DB2 хранимые процедуры можно объединять в [publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.apdv.sqlpl.doc/doc/c0053740.html модули].

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

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

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

Кроме собственно выполнения запроса, хранимые процедуры позволяют также производить вычисления и манипуляцию данными — изменение, удаление, выполнять DDL-операторы (не во всех СУБД!) и вызывать другие хранимые процедуры, выполнять сложную транзакционную логику. Один-единственный оператор позволяет вызвать сложный сценарий, который содержится в хранимой процедуре, что позволяет избежать пересылки через сеть сотен команд и, в особенности, необходимости передачи больших объёмов данных с клиента на сервер.

В большинстве СУБД при первом запуске хранимой процедуры она компилируется (выполняется синтаксический анализ и генерируется план доступа к данным). В дальнейшем её обработка осуществляется быстрее. В СУБД Oracle выполняется интерпретация хранимого процедурного кода, сохраняемого в словаре данных. Начиная с версии Oracle 10g поддерживается так называемая естественная компиляция (native compilation) хранимого процедурного кода в Си и затем в машинный код целевой машины, после чего при вызове хранимой процедуры происходит прямое выполнение её скомпилированного объектного кода.

Возможности программирования

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

Для упрощения тестирования, независимости бизнес-логики приложений от СУБД существует подход, в котором СУБД выступает лишь в роли хранилища, с минимальным количеством хранимых процедур или полном отказе от них. При этом используется отображение программных сущностей бизнес-логики на хранилище. См. ORM (англ. Object-relational mapping, рус. Объектно-реляционное отображение)

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

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

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

Снижается вероятность таких действий как «внедрение SQL-кода», поскольку хорошо написанные хранимые процедуры дополнительно проверяют входные параметры перед тем, как передать запрос СУБД.

Ссылки

• [msdn.microsoft.com/ru-ru/library/ms131287.aspx Хранимые процедуры в MS SQL Server 2008 R2] (рус.)
• [dev.mysql.com/doc/refman/5.0/en/stored-routines.html Хранимые процедуры в MySQL] (англ.)
• [www.devshed.com/c/a/Oracle/Oracle-Stored-Procedures/ Хранимые процедуры в Oracle DB] (англ.)
• [www.benslade.com/tech/OldIntroToSybase/storproc.html Хранимые процедуры в Sybase DB] (англ.)
• [publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.sqlt.doc/ids_sqt_412.htm Хранимые процедуры в Informix] (англ.)

Отрывок, характеризующий Хранимая процедура

– Мой друг, – обращаясь к брату, сказала княжна Марья от кроватки, у которой она стояла, – лучше подождать… после…
– Ах, сделай милость, ты всё говоришь глупости, ты и так всё дожидалась – вот и дождалась, – сказал князь Андрей озлобленным шопотом, видимо желая уколоть сестру.
– Мой друг, право лучше не будить, он заснул, – умоляющим голосом сказала княжна.
Князь Андрей встал и, на цыпочках, с рюмкой подошел к кроватке.
– Или точно не будить? – сказал он нерешительно.
– Как хочешь – право… я думаю… а как хочешь, – сказала княжна Марья, видимо робея и стыдясь того, что ее мнение восторжествовало. Она указала брату на девушку, шопотом вызывавшую его.
Была вторая ночь, что они оба не спали, ухаживая за горевшим в жару мальчиком. Все сутки эти, не доверяя своему домашнему доктору и ожидая того, за которым было послано в город, они предпринимали то то, то другое средство. Измученные бессоницей и встревоженные, они сваливали друг на друга свое горе, упрекали друг друга и ссорились.
– Петруша с бумагами от папеньки, – прошептала девушка. – Князь Андрей вышел.
– Ну что там! – проговорил он сердито, и выслушав словесные приказания от отца и взяв подаваемые конверты и письмо отца, вернулся в детскую.
– Ну что? – спросил князь Андрей.
– Всё то же, подожди ради Бога. Карл Иваныч всегда говорит, что сон всего дороже, – прошептала со вздохом княжна Марья. – Князь Андрей подошел к ребенку и пощупал его. Он горел.
– Убирайтесь вы с вашим Карлом Иванычем! – Он взял рюмку с накапанными в нее каплями и опять подошел.
– Andre, не надо! – сказала княжна Марья.
Но он злобно и вместе страдальчески нахмурился на нее и с рюмкой нагнулся к ребенку. – Ну, я хочу этого, сказал он. – Ну я прошу тебя, дай ему.
Княжна Марья пожала плечами, но покорно взяла рюмку и подозвав няньку, стала давать лекарство. Ребенок закричал и захрипел. Князь Андрей, сморщившись, взяв себя за голову, вышел из комнаты и сел в соседней, на диване.
Письма всё были в его руке. Он машинально открыл их и стал читать. Старый князь, на синей бумаге, своим крупным, продолговатым почерком, употребляя кое где титлы, писал следующее:
«Весьма радостное в сей момент известие получил через курьера, если не вранье. Бенигсен под Эйлау над Буонапартием якобы полную викторию одержал. В Петербурге все ликуют, e наград послано в армию несть конца. Хотя немец, – поздравляю. Корчевский начальник, некий Хандриков, не постигну, что делает: до сих пор не доставлены добавочные люди и провиант. Сейчас скачи туда и скажи, что я с него голову сниму, чтобы через неделю всё было. О Прейсиш Эйлауском сражении получил еще письмо от Петиньки, он участвовал, – всё правда. Когда не мешают кому мешаться не следует, то и немец побил Буонапартия. Сказывают, бежит весьма расстроен. Смотри ж немедля скачи в Корчеву и исполни!»
Князь Андрей вздохнул и распечатал другой конверт. Это было на двух листочках мелко исписанное письмо от Билибина. Он сложил его не читая и опять прочел письмо отца, кончавшееся словами: «скачи в Корчеву и исполни!» «Нет, уж извините, теперь не поеду, пока ребенок не оправится», подумал он и, подошедши к двери, заглянул в детскую. Княжна Марья всё стояла у кроватки и тихо качала ребенка.
«Да, что бишь еще неприятное он пишет? вспоминал князь Андрей содержание отцовского письма. Да. Победу одержали наши над Бонапартом именно тогда, когда я не служу… Да, да, всё подшучивает надо мной… ну, да на здоровье…» и он стал читать французское письмо Билибина. Он читал не понимая половины, читал только для того, чтобы хоть на минуту перестать думать о том, о чем он слишком долго исключительно и мучительно думал.


Билибин находился теперь в качестве дипломатического чиновника при главной квартире армии и хоть и на французском языке, с французскими шуточками и оборотами речи, но с исключительно русским бесстрашием перед самоосуждением и самоосмеянием описывал всю кампанию. Билибин писал, что его дипломатическая discretion [скромность] мучила его, и что он был счастлив, имея в князе Андрее верного корреспондента, которому он мог изливать всю желчь, накопившуюся в нем при виде того, что творится в армии. Письмо это было старое, еще до Прейсиш Эйлауского сражения.
«Depuis nos grands succes d'Austerlitz vous savez, mon cher Prince, писал Билибин, que je ne quitte plus les quartiers generaux. Decidement j'ai pris le gout de la guerre, et bien m'en a pris. Ce que j'ai vu ces trois mois, est incroyable.
«Je commence ab ovo. L'ennemi du genre humain , comme vous savez, s'attaque aux Prussiens. Les Prussiens sont nos fideles allies, qui ne nous ont trompes que trois fois depuis trois ans. Nous prenons fait et cause pour eux. Mais il se trouve que l'ennemi du genre humain ne fait nulle attention a nos beaux discours, et avec sa maniere impolie et sauvage se jette sur les Prussiens sans leur donner le temps de finir la parade commencee, en deux tours de main les rosse a plate couture et va s'installer au palais de Potsdam.
«J'ai le plus vif desir, ecrit le Roi de Prusse a Bonaparte, que V. M. soit accueillie еt traitee dans mon palais d'une maniere, qui lui soit agreable et c'est avec еmpres sement, que j'ai pris a cet effet toutes les mesures que les circonstances me permettaient. Puisse je avoir reussi! Les generaux Prussiens se piquent de politesse envers les Francais et mettent bas les armes aux premieres sommations.
«Le chef de la garienison de Glogau avec dix mille hommes, demande au Roi de Prusse, ce qu'il doit faire s'il est somme de se rendre?… Tout cela est positif.
«Bref, esperant en imposer seulement par notre attitude militaire, il se trouve que nous voila en guerre pour tout de bon, et ce qui plus est, en guerre sur nos frontieres avec et pour le Roi de Prusse . Tout est au grand complet, il ne nous manque qu'une petite chose, c'est le general en chef. Comme il s'est trouve que les succes d'Austerlitz aurant pu etre plus decisifs si le general en chef eut ete moins jeune, on fait la revue des octogenaires et entre Prosorofsky et Kamensky, on donne la preference au derienier. Le general nous arrive en kibik a la maniere Souvoroff, et est accueilli avec des acclamations de joie et de triomphe.
«Le 4 arrive le premier courrier de Petersbourg. On apporte les malles dans le cabinet du Marieechal, qui aime a faire tout par lui meme. On m'appelle pour aider a faire le triage des lettres et prendre celles qui nous sont destinees. Le Marieechal nous regarde faire et attend les paquets qui lui sont adresses. Nous cherchons – il n'y en a point. Le Marieechal devient impatient, se met lui meme a la besogne et trouve des lettres de l'Empereur pour le comte T., pour le prince V. et autres. Alors le voila qui se met dans une de ses coleres bleues. Il jette feu et flamme contre tout le monde, s'empare des lettres, les decachete et lit celles de l'Empereur adressees a d'autres. А, так со мною поступают! Мне доверия нет! А, за мной следить велено, хорошо же; подите вон! Et il ecrit le fameux ordre du jour au general Benigsen
«Я ранен, верхом ездить не могу, следственно и командовать армией. Вы кор д'арме ваш привели разбитый в Пултуск: тут оно открыто, и без дров, и без фуража, потому пособить надо, и я так как вчера сами отнеслись к графу Буксгевдену, думать должно о ретираде к нашей границе, что и выполнить сегодня.
«От всех моих поездок, ecrit il a l'Empereur, получил ссадину от седла, которая сверх прежних перевозок моих совсем мне мешает ездить верхом и командовать такой обширной армией, а потому я командованье оной сложил на старшего по мне генерала, графа Буксгевдена, отослав к нему всё дежурство и всё принадлежащее к оному, советовав им, если хлеба не будет, ретироваться ближе во внутренность Пруссии, потому что оставалось хлеба только на один день, а у иных полков ничего, как о том дивизионные командиры Остерман и Седморецкий объявили, а у мужиков всё съедено; я и сам, пока вылечусь, остаюсь в гошпитале в Остроленке. О числе которого ведомость всеподданнейше подношу, донеся, что если армия простоит в нынешнем биваке еще пятнадцать дней, то весной ни одного здорового не останется.