Хранимая процедура (англ. stored procedure) – это именованный программный объект БД. В SQL Server есть хранимые процедуры нескольких типов.
Системные хранимые процедуры (англ. system stored procedure) поставляются разработчиками СУБД и используются для выполнения действий с системным каталогом или получения системной информации. Их названия обычно начинаются с префикса "sp_". Запускаются хранимые процедуры всех типов с помощью команды EXECUTE, которую можно сократить до ЕХЕС. Например, хранимая процедура sp_helplogins, запущенная без параметров, формирует два отчета об именах учетных записей (англ. logins) и соответствующих им в каждой БД пользователях (англ. users).
EXEC sp_helplogins;
Чтобы дать представление о действиях, выполняемых с помощью системных хранимых процедур, в табл. 10.6 приведены некоторые примеры. Всего же системных хранимых процедур в SQL Server более тысячи.
Таблица 10.6
Примеры системных хранимых процедур SQL Server
Пользователю доступно создание хранимых процедур в пользовательских БД и в БД для временных объектов. В последнем случае хранимая процедура будет являться временной. Так же как в случае с временными таблицами, название временной хранимой процедуры должно начинаться с префикса "#", если это локальная временная хранимая процедура, или с "##" – если глобальная. Локальная временная процедура может использоваться только в рамках соединения, в котором ее создали, глобальная – и в рамках других соединений.
Программируемые объекты SQL Server могут создаваться как с использованием средств Transact-SQL, так и с помощью сборок (англ. assembly) в среде CRL (Common Language Runtime) платформы Microsoft.Net Framework . В данном учебнике будет рассматриваться только первый способ.
Для создания хранимых процедур используется оператор CREATE PROCEDURE (можно сократить до PROC), формат которого приведен ниже:
CREATE {PROC I PROCEDURE) proc_name [ ; number ]
[{gparameter data_type }
[“default] |
[ WITH
[ FOR REPLICATION ]
AS {[ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
Если хранимая процедура (или триггер, функция, представление) создается с опцией ENCRYPTION, ее код преобразуется таким образом, что текст становится нечитаемым. В то же время, как отмечается в , используемый алгоритм перенесен из ранних версий SQL Server и не может рассматриваться в качестве надежного алгоритма защиты – существуют утилиты, позволяющие быстро выполнить обратное преобразование.
Опция RECOMPILE указывает на то, что при каждом вызове процедуры система будет перекомпилировать текст. В обычном случае скомпилированная при первом запуске процедура сохраняется в кэше, что позволяет увеличить быстродействие.
EXECUTE AS определяет контекст безопасности, в котором должна быть выполнена процедура. Далее указывается одно из значений f CALLER | SELF | OWNER | " user_name"). CALLER является значением по умолчанию и означает, что код будет выполняться в контексте безопасности пользователя, вызывающего этот модуль. Соответственно, пользователь должен иметь разрешения не только на сам программируемый объект, но и на другие затрагиваемые им объекты БД. EXECUTE AS SELF означает использование контекста пользователя, создающего или изменяющего программируемый объект. OWNER указывает, что код будет выполняться в контексте текущего владельца процедуры. Если для нее не определен владелец, то подразумевается владелец схемы, к которой она относится. EXECUTE AS "user_name" позволяет явно указать имя пользователя (в одинарных кавычках).
Для процедуры могут указываться параметры. Это локальные переменные, используемые для передачи значений в процедуру. Если параметр объявлен с ключевым словом OUTPUT (или сокращенно OUT), он является выходным: заданное ему в процедуре значение после ее окончания может быть использовано вызвавшей процедуру программой. Ключевое слово READONLY означает, что значение параметра не может быть изменено внутри хранимой процедуры.
Параметрам могут быть назначены значения но умолчанию, которые будут использованы, если при вызове процедуры значение параметра не будет указано в явном виде. Рассмотрим пример:
CREATE PROC surma (@а int, @b int=0,
©result int OUTPUT) AS
SET @result=0a+0b
Мы создали процедуру с тремя параметрами, причем у параметра @b значение по умолчанию =0, а параметр @result – выходной: через него возвращается значение в вызвавшую программу. Выполняемые действия достаточно просты – выходной параметр получает значение суммы двух входных.
При работе в SQL Server Management Studio созданную хранимую процедуру можно найти в разделе программируемых объектов БД (англ. Programmability) в подразделе для хранимых процедур (рис. 10.2).
При вызове процедуры в качестве входных параметров можно использовать как переменные, так и константы. Рассмотрим два примера. В первом входные параметры процедуры явно заданы константами, для выходного параметра в вызове указано ключевое слово OUTPUT. Во втором варианте в качестве первого входного параметра используется значение переменной, а для второго параметра с помощью ключевого слова DEFAULT указано, что должно быть использовано значение по умолчанию:
Рис. 10.2.
DECLARE @с int;
EXEC summa 10,5,@c OUTPUT;
PRINT 0c; – будет выведено 15
DECLARE Gi int = 5;
– при вызове используем значение по умолчанию
EXEC summa Gi,DEFAULT , 0с OUTPUT;
PRINT 0c; – будет выведено 5
Рассмотрим теперь пример с анализом кода возврата, с которым заканчивается процедура. Пусть надо подсчитать, сколько в таблице Bookl книг, изданных в заданном диапазоне лет. При этом если начальный год оказался больше конечного, процедура возвращает "1" и подсчет не проводит, иначе – считаем количество книг и возвращаем 0:
CREATE PROC dbo.rownum (0FirsYear int, GLastYear int, 0result int OUTPUT) AS
IF 0FirsYear>0LastYear RETURN 1
SET @result= (SELECT COUNT(*) FROM dbo.Bookl
WHERE BETWEEN 0FirsYear AND 0LastYear) ;
Рассмотрим вариант вызова данной процедуры, в котором код возврата сохраняется в целочисленной переменной 0ret, после чего анализируется его значение (в данном случае это будет 1). Используемая в операторе PRINT функция CAST служит для преобразования значения целочисленной переменной Gres к строковому типу:
DECLARE 0ret int, Gres int
EXEC Gret = rownum 2004, 2002, Gres OUT;
IF 0ret=l PRINT "Начальный год больше конечного"
PRINT "Число книг "+ CAST(Gres as varchar(20))
Хранимые процедуры могут не только считывать данные из таблицы, но и изменять данные и даже создавать таблицы и ряд других объектов БД.
Однако создавать схемы, функции, триггеры, процедуры и представления из хранимой процедуры нельзя.
Следующий пример иллюстрирует как эти возможности, так и вопросы, связанные с областью видимости временных объектов. Приведенная ниже хранимая процедура проверяет наличие временной таблицы #ТаЬ2; если этой таблицы нет, то создает ее. После этого в таблицу #ТаЬ2 заносятся значения двух столбцов, и содержимое таблицы выводится оператором SELECT:
CREATE PROC My_Procl (@id int, @name varchar(30))
IF OBJECT_ID("tempdb.dbo.#Tab21) IS NULL
INSERT INTO dbo.#Tab2 (id, name)VALUES (0id,0name)
SELECT * FROM dbo. #Tab2 –№1
Перед первым вызовом хранимой процедуры создадим используемую в ней временную таблицу #ТаЬ2. Обратите внимание на оператор ЕХЕС. В предыдущих примерах параметры передавались в процедуру "по позиции", а в данном случае используется другой формат передачи параметров – "по имени", явно указывается имя параметра и его значение:
CREATE TABLE dbo.#Tab2 (id int, name varchar(30));
EXEC My_Procl 0name="lvan", 0id=2;
SELECT * FROM dbo.#Tab2; –№2
В приведенном примере оператор SELECT отработает дважды: первый раз – внутри процедуры, второй раз – из вызывающего фрагмента кода (отмечен комментарием "№ 2").
Перед вторым вызовом процедуры удалим временную таблицу #ТаЬ2. Тогда одноименная временная таблица будет создана из хранимой процедуры:
DROP TABLE dbo.#Tab2;
EXEC My_Procl 0name="Ivan", 0id=2;
SELECT * FROM dbo.#Tab2; –№2
В этом случае данные выведет только оператор SELECT, находящийся внутри процедуры (с комментарием "Ха 1"). Выполнение SELECT "№ 2" приведет к ошибке, так как созданная в хранимой процедуре временная таблица на момент возврата из процедуры будет уже удалена из базы tempdb.
Удалить хранимую процедуру можно с помощью оператора DROP PROCEDURE. Его формат представлен ниже. Одним оператором можно удалить несколько хранимых процедур, перечислив их через запятую:
DROP (PROC I PROCEDURE) { procedure } [
Например, удалим ранее созданную процедуру summa:
DROP PROC summa;
Внести изменения в существующую процедуру (а фактически – переопределить ее) можно с помощью оператора ALTER PROCEDURE (допу
стимо сокращение PROC). За исключением ключевого слова ALTER, формат оператора практически совпадает с форматом CREATE PROCEDURE. Например, изменим процедуру dbo. rownum, установив ей опцию выполнения в контексте безопасности владельца:
ALTER PROC dbo.rownum (SFirsYear int,
SLastYear int, Sresult int OUTPUT)
WITH EXECUTE AS Owner – устанавливаемая опция
IF 0FirsYear>0LastYear RETURN 1 ELSE BEGIN
SET 0result= (SELECT COUNT(*) FROM dbo.Bookl
WHERE BETWEEN SFirsYear AND SLastYear);
В некоторых случаях может возникнуть необходимость в динамическом формировании команды и выполнении ее на сервере БД. Эта задача также может решаться с помощью оператора ЕХЕС. В приведенном ниже примере выполняется выборка записей из таблицы Bookl по условию равенства атрибута Year значению, задаваемому с помощью переменной:
DECLARE 0у int = 2000;
EXEC ("SELECT * FROM dbo.Bookl WHERE = "+@y) ;
Выполнение динамически сформированных инструкций создает предпосылки для реализации компьютерных атак типа "SQL-инъекция" (англ. SQL injection). Суть атаки заключается в том, что нарушитель внедряет в динамически формируемый запрос собственный код на SQL. Обычно это происходит, когда подставляемые параметры берут из результатов ввода данных пользователем.
Несколько изменим предыдущий пример:
DECLARE 0у varchar(100);
SET 0у="2ООО"; – это мы получили от пользователя
Если предположить, что присваиваемое в операторе SET строковое значение мы получили от пользователя (неважно каким образом, например, через веб-приложение), то пример иллюстрирует "штатное" поведение нашего кода.
DECLARE 0у varchar(100);
SET 0у="2000; DELETE FROM dbo.Book2"; – инъекция
EXEC ("SELECT * FROM dbo.Book2 WHERE ="+0y);
В рекомендуется по возможности использовать в подобных случаях системную хранимую процедуру sp_executcsql, которая позволяет контролировать тип параметров, что является одним из барьеров на пути SQL- инъекций. Не рассматривая в подробностях ее формат, разберем пример, аналогичный представленному ранее:
EXECUTE sp_executesql
N"SELECT * FROM dbo.Bookl WHERE =0y",
Здесь явно указывается тип используемого в запросе параметра, и SQL Server при выполнении будет его контролировать. Буква "N" перед кавычками указывает, что это литерная константа в формате Unicode, как того требует процедура. Параметру можно присвоить не только постоянное значение, но и значение другой переменной.
Храни́мая процеду́ра - объект базы данных, представляющий собой набор SQL-инструкций, который компилируется один раз и хранится на сервере. Хранимые процедуры очень похожи на обыкновенные процедуры языков высокого уровня, у них могут быть входные и выходные параметры и локальные переменные, в них могут производиться числовые вычисления и операции над символьными данными, результаты которых могут присваиваться переменным и параметрам. В хранимых процедурах могут выполняться стандартные операции с базами данных (как DDL, так и DML). Кроме того, в хранимых процедурах возможны циклы и ветвления, то есть в них могут использоваться инструкции управления процессом исполнения.
Хранимые процедуры похожи на определяемые пользователем функции (UDF). Основное различие заключается в том, что пользовательские функции можно использовать как и любое другое выражение в SQL запросе, в то время как хранимые процедуры должны быть вызваны с помощью функции CALL:
CALL процедура(…)
EXECUTE процедура(…)
Хранимые процедуры могут возвращать множества результатов, то есть результаты запроса SELECT. Такие множества результатов могут обрабатываться, используя курсоры, другими хранимыми процедурами, возвращая указатель результирующего множества, либо же приложениями. Хранимые процедуры могут также содержать объявленные переменные для обработки данных и курсоров, которые позволяют организовать цикл по нескольким строкам в таблице. Стандарт SQL предоставляет для работы выражения IF, LOOP, REPEAT, CASE и многие другие. Хранимые процедуры могут принимать переменные, возвращать результаты или изменять переменные и возвращать их, в зависимости от того, где переменная объявлена.
Реализация хранимых процедур варьируется от одной СУБД к другой. Большинство крупных поставщиков баз данных поддерживают их в той или иной форме. В зависимости от СУБД, хранимые процедуры могут быть реализованы на различных языках программирования, таких, как SQL, Java, C или C++. Хранимые процедуры написанные не на SQL могут самостоятельно выполнять SQL-запросы, а могут и не выполнять.
За
Разделение логики с другими приложениями. Хранимые процедуры инкапсулируют функциональность; это обеспечивает связность доступа к данным и управления ими между различными приложениями.
Изоляция пользователей от таблиц базы данных. Это позволяет давать доступ к хранимым процедурам, но не к самим данным таблиц.
Обеспечивает механизм защиты. В соответствии с предыдущим пунктом, если вы можете получить доступ к данным только через хранимые процедуры, никто другой не сможет стереть ваши данные через команду SQL DELETE.
Улучшение выполнения как следствие сокращения сетевого трафика. С помощью хранимых процедур множество запросов могут быть объединены.
Против
Повышение нагрузки на сервер баз данных в связи с тем, что большая часть работы выполняется на серверной части, а меньшая - на клиентской.
Придется много чего подучить. Вам понадобится выучить синтаксис MySQL выражений для написания своих хранимых процедур.
Вы дублируете логику своего приложения в двух местах: серверный код и код для хранимых процедур, тем самым усложняя процесс манипулирования данными.
Миграция с одной СУБД на другую (DB2, SQL Server и др.) может привести к проблемам.
Назначение и преимущества хранимых процедур
Хранимые процедуры позволяют повысить производительность, расширяют возможности программирования и поддерживают функции безопасности данных.
Вместо хранения часто используемого запроса, клиенты могут ссылаться на соответствующую хранимую процедуру. При вызове хранимой процедуры её содержимое сразу же обрабатывается сервером.
Кроме собственно выполнения запроса, хранимые процедуры позволяют также производить вычисления и манипуляцию данными - изменение, удаление, выполнять DDL-операторы (не во всех СУБД!) и вызывать другие хранимые процедуры, выполнять сложную транзакционную логику. Один-единственный оператор позволяет вызвать сложный сценарий, который содержится в хранимой процедуре, что позволяет избежать пересылки через сеть сотен команд и, в особенности, необходимости передачи больших объёмов данных с клиента на сервер.
В большинстве СУБД при первом запуске хранимой процедуры она компилируется (выполняется синтаксический анализ и генерируется план доступа к данным). В дальнейшем её обработка осуществляется быстрее. В СУБД Oracle выполняется интерпретация хранимого процедурного кода, сохраняемого в словаре данных. Начиная с версии Oracle 10g поддерживается так называемая естественная компиляция (native compilation) хранимого процедурного кода в Си и затем в машинный код целевой машины, после чего при вызове хранимой процедуры происходит прямое выполнение её скомпилированного объектного кода.
Возможности программирования
Созданную хранимую процедуру можно вызвать в любой момент, что обеспечивает модульность и стимулирует повторное использование кода. Последнее облегчает сопровождение базы данных, так как она становится изолированной от меняющихся бизнес-правил. Модифицировать хранимую процедуру в соответствии с новыми правилами можно в любой момент. После этого все приложения, использующие её, автоматически придут в соответствие с новыми бизнес-правилами без непосредственной модификации.
Безопасность
Использование хранимых процедур позволяет ограничить или вообще исключить непосредственный доступ пользователей к таблицам базы данных, оставив пользователям только разрешения на выполнение хранимых процедур, обеспечивающих косвенный и строго регламентированный доступ к данным. Кроме того, некоторые СУБД поддерживают шифрование текста (wrapping) хранимой процедуры.
Эти функции безопасности позволяют изолировать от пользователя структуру базы данных, что обеспечивает целостность и надежность базы.
Снижается вероятность таких действий как «внедрение SQL-кода», поскольку хорошо написанные хранимые процедуры дополнительно проверяют входные параметры перед тем, как передать запрос СУБД.
Реализация хранимых процедур
Хранимые процедуры обычно создаются с помощью языка 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 хранимые процедуры можно объединять в модули.
Синтаксис
CREATE PROCEDURE `p2` ()
SQL SECURITY DEFINER
COMMENT "A procedure"
SELECT "Hello World !";
Первая часть кода создает хранимую процедуру. Следующая - содержит необязательные параметры. Затем идет название и, наконец, тело самой процедуры.
4 характеристики хранимой процедуры:
Language: в целях обеспечения переносимости, по умолчанию указан SQL.
Deterministic: если процедура все время возвращает один и тот же результат, и принимает одни и те же входящие параметры. Это для репликации и процесса регистрации. Значение по умолчанию - NOT DETERMINISTIC.
SQL Security: во время вызова идет проверка прав пользователя. INVOKER - это пользователь, вызывающий хранимую процедуру. DEFINER - это “создатель” процедуры. Значение по умолчанию - DEFINER.
Comment: в целях документирования, значение по умолчанию - ""
Вызов хранимой процедуры
CALL stored_procedure_name (param1, param2, ....)
CALL procedure1(10 , "string parameter" , @parameter_var);
Изменение хранимой процедуры
В MySQL есть выражение ALTER PROCEDURE для изменения процедур, но оно подходит для изменения лишь некоторых характеристик. Если вам нужно изменить параметры или тело процедуры, вам следует удалить и создать ее заново.
Удаление хранимой процедуры
DROP PROCEDURE IF EXISTS p2;
Это простая команда. Выражение IF EXISTS отлавливает ошибку в случае, если такой процедуры не существует.
Параметры
CREATE PROCEDURE proc1 (): пустой список параметров
CREATE PROCEDURE proc1 (IN varname DATA-TYPE): один входящий параметр. Слово IN необязательно, потому что параметры по умолчанию - IN (входящие).
CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): один возвращаемый параметр.
CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): один параметр, одновременно входящий и возвращаемый.
Синтаксис объявления переменной выглядит так:
DECLARE varname DATA-TYPE DEFAULT defaultvalue;
Объявление процедуры
CREATE PROCEDURE [({IN|OUT|INOUT } [,…])]
[DYNAMIC RESULT SET ]
BEGIN [ATOMIC ]
END
Ключевые слова
. IN (Input) – входной параметр
. OUT (Output) – выходной параметр
. INOUT – входной и выходной, а также поле (без параметров)
. DYNAMIC RESULT SET показывает, что процедура может открыть указанное число курсоров, которые останутся открытыми после возврата из процедуры
Примечания
Не рекомендуется использовать много параметров в хранимых процедурах (в первую очередь больших чисел и символьных строк) из-за перегрузки сети и стека. На практике в существующих диалектах Transact-SQL, PL/SQL и Informix наблюдается существенное отличие от стандарта, как в объявлении и использовании параметров, объявлении переменных, так и в вызове подпрограмм. Microsoft рекомендует применять следующую аппроксимацию для оценки размера КЭШа хранимых процедур:
=(максимальное количество одновременно работающих пользователей)*(размер самого большого плана выполнения)*1.25. Определение размера плана выполнения в страницах можно сделать с помощью команды: DBCC MEMUSAGE.
Вызов процедуры
Во многих существующих СУБД вызов хранимых процедур выполняется с помощью оператора:
EXECUTE PROCEDURE [(][)]
Примечание : вызов хранимых процедур может быть сделан из приложения, другой хранимой процедуры или в интерактивном режиме.
Пример объявления процедуры
CREATE PROCEDURE Proc1 AS //объявляем процедуру
DECLARE Cur1 CURSOR FOR SELECT SName, City FROM SalesPeople WHERE Rating>200 //объявляем курсор
OPEN Cur1 //открываем курсор
FETCH NEXT FROM Cur1 //считываем данные из курсора
WHILE @@Fetch_Status=0
BEGIN
FETCH NEXT FROM Cur1
END
CLOSE Cur1 //закрываем курсор
DEALLOCATE Cur1
EXECUTE Proc1 //запускаем процедуру
Полиморфизм
Две подпрограммы с одним и тем же именем могут быть созданы в одной и той же схеме, если параметры этих двух подпрограмм являются в такое мере отличными друг от друга, чтобы их можно было различать. Для того, чтобы различать две подпрограммы с одним и тем же именем в одной схеме, каждой из них дается альтернативное и уникальное имя (specific name). Такое имя может быть явно указано, когда определяется подпрограмма. При вызове подпрограмм при наличии нескольких одинаковых имен определение нужной подпрограммы осуществляется в несколько шагов:
. Первоначально определяются все процедуры с указанным именем, а если таковых нет, то все функции с заданным именем.
. Для дальнейшего анализа оставляются только те подпрограммы, по отношению к которым данный пользователь обладает привилегией на исполнение (EXECUTE).
. Для них отбираются те, у которых число параметров соответствует числу аргументов вызова. Проверяются указанные типы данных у параметров и их позиции.
. Если осталось более одной подпрограммы, то выбирается та, квалификационное имя которой короче.
На практике в Oracle полиморфизм поддерживается для функций, объявленных только в пакете, DB@ — в разных схема, а в Sybase и MS SQL Server перегрузка запрещена.
Удаление и изменение процедур
Для удаления процедуры используется оператор:
Для изменения процедуры используется оператор:
ALTER PROCEDURE [([{IN|OUT|INOUT }])]
BEGIN [ATOMIC ]
END
Привилегии на выполнение процедур
GRANT EXECUTE ON TO |PUBLIC [WITH GRANT OPTION ]
Системные процедуры
Многие СУБД (включая SQL Server) имеют определенный набор встроенных системных хранимых процедур, которые можно использовать в своих целях.
В предыдущей статье данного цикла мы рассмотрели, как можно извлечь данные из таблиц, модифицировать их структуру, создавать, модифицировать и удалять базы данных и объекты, в них содержащиеся. В настоящей статье мы поговорим более подробно об объектах, характерных для серверных СУБД: представлениях, триггерах и хранимых процедурах.
В первой статье данного цикла, опубликованной в № 3’2000 нашего журнала, мы отмечали, что большинство современных серверных СУБД поддерживают представления, триггеры и хранимые процедуры. Представления также поддерживаются и многими настольными СУБД, например Access, dBase, Clipper.
Следует отметить, что триггеры и хранимые процедуры обычно пишутся на языках программирования, представляющих собой процедурные расширения языка SQL. Эти расширения содержат операторы, позволяющие описывать алгоритмы, например do…while, if…then…else, отсутствующие в самом языке SQL (если вы помните, SQL - непроцедурный язык, и на нем можно сформулировать задание, но нельзя описывать алгоритмы его выполнения). В отличие от языка SQL, подчиняющегося стандарту, его процедурные расширения никак не стандартизованы, и разные СУБД используют разные синтаксические конструкции для реализации одних и тех же алгоритмических конструкций, но обсуждение различий в синтаксисе расширений SQL для разных СУБД выходит за рамки данной статьи.
Для иллюстрации того, как можно использовать представления, триггеры и хранимые процедуры, мы выбрали Microsoft SQL Server 7.0 и базу данных NorthWind, входящую в комплект поставки этой СУБД.
Прежде чем выполнять примеры, обратите внимание на то, что реализация и способ хранения триггеров и хранимых процедур в используемой вами СУБД могут отличаться от приведенных в этой статье. Кроме того, для создания серверных объектов следует иметь соответствующие разрешения, предоставляемые администратором базы данных.
Отметим также, что некоторые ODBC-драйверы не поддерживают вызов хранимых процедур из клиентских приложений, даже если таковые поддерживаются самой СУБД. Однако в этом случае хранимые процедуры по-прежнему могут быть вызваны из триггеров.
Давайте начнем с представлений, затем обсудим хранимые процедуры и закончим главу обзором триггеров.
Представления
Представление - это виртуальная таблица, обычно содержащая набор колонок одной или нескольких таблиц. В действительности представление содержит не данные, а лишь SQL-запрос типа SELECT, указывающий, какие именно данные и из каких таблиц нужно взять при обращении к этому представлению. С этой точки зрения представление - это хранимый запрос.
В большинстве случаев представления используются для обеспечения безопасности данных. Например, некоторые категории пользователей могут иметь доступ к представлению, но не к таблицам, данные которых его формируют; кроме того, SQL-запрос может содержать параметр USER (имя, под которым зарегистрировался пользователь), и в этом случае данные, доступные при обращении к представлению, будут зависеть от имени пользователя.
Ниже перечислены основные характеристики представлений:
- представления ведут себя подобно таблицам;
- представления не содержат данных;
- представления могут использовать данные более чем из одной таблицы.
Для создания представления мы можем использовать SQL-предложение CREATE VIEW, для его модификации - предложение ALTER VIEW, а для удаления его - предложение DROP VIEW.
Мы начнем с оператора CREATE VIEW, позволяющего создать представление для текущей базы данных.
Предложение CREATE VIEW
Синтаксис предложения для создания представления напоминает SQL-предложение SELECT с несколькими дополнительными ключевыми словами. Ниже приведен его упрощенный синтаксис:
CREATE VIEW view_name AS select_statement
Аргумент view_name указывает на имя представления. Ключевое слово , используемое в Microsoft SQL Server, позволяет скрыть исходный текст предложения CREATE VIEW в таблице syscomments.
Ключевое слово AS указывает, какой запрос SELECT реально будет выполняться при обращении к представлению. Обратите внимание на то, что этот запрос не может содержать ключевых слов ORDER BY, COMPUTE или COMPUTE BY, INTO и не может ссылаться на временную таблицу.
Для модификации созданного ранее представления следует использовать предложение ALTER VIEW, кратко описанное в следующем разделе.
Предложение DROP VIEW
Это предложение используется для удаления представления из базы данных. Обратите внимание на то, что при удалении таблицы из базы данных удаляются и все представления, ссылающиеся на нее. Используя это предложение, мы должны указать имя удаляемого представления. После того как представление удалено, вся информация о нем удаляется из системных таблиц.
Еще один случай, когда представление нужно удалить, может возникнуть при условии, что структура таблиц, на которых оно основано, изменилась после создания представления. В этом случае можно удалить представление и затем создать его заново с помощью предложения CREATE VIEW.
Создание и использование представлений
Предложение CREATE VIEW используется для создания представлений, позволяющих извлекать данные, удовлетворяющие определенным требованиям. Представление создается в текущей базе данных и хранится как ее отдельный объект.
Наилучший способ создать представление - создать запрос SELECT и, проверив его, добавить недостающую часть предложения CREATE VIEW. Давайте рассмотрим исходный текст представления Products by Category в базе данных NorthWind (листинг 1).
Первая строка, выделенная жирным шрифтом, представляет собой то, чем отличается SQL-предложение для создания представления от обычного запроса SELECT, выполняющего работу по выбору данных. Предложение SELECT, содержащееся в этом представлении, выбирает поля из двух таблиц - поле CategoryName из таблицы CATEGORIES и поля ProductName, QuantityPerUnit, UnitsInStock, Discontinued из таблицы PRODUCTS. После этого данные двух таблиц связываются по полю CategoryID, и только те продукты, которые еще имеются на складе (см. критерий после ключевого слова WHERE), включаются в результирующий набор данных. Результат обращения к этому представлению показан на рис. 1 .
Теперь давайте создадим представление, показывающее все территории восточного региона. Это представление базируется на следующем запросе (листинг 2).
Убедившись в том, что предложение SELECT возвращает результаты, которые нам нужны, мы добавляем оператор CREATE VIEW и присваиваем создаваемому представлению имя EASTTERR (листинг 3).
Вместо создания текста представления вручную можно использовать визуальные инструменты, обычно входящие в состав СУБД. На рис. 2 показано, как то же самое представление может быть создано с помощью инструмента View Designer, который является составной частью Enterprise Manager, входящего в Microsoft SQL Server.
Верхняя часть View Designer позволяет указать, как связаны таблицы и какие поля будут отображаться в представлении. Ниже можно указать псевдонимы таблиц и полей, ограничения на их значения, способ отображения. Далее приведены исходный текст представления и результаты его выполнения.
Прежде чем мы закончим краткий обзор представлений, поговорим немного о том, как получить дополнительную информацию о них. В Microsoft SQL Server 7.0 мы можем использовать следующие системные хранимые процедуры:
- для получения сведений о представлении можно использовать системную хранимую процедуру sp_help. Например, sp_help EastTerr вернет сведения о только что созданном представлении;
- для получения исходного текста представления можно использовать хранимую процедуру sp_helptext;
- для того чтобы найти список таблиц, от которого зависит представление, можно использовать системную хранимую процедуру sp_depends;
- для переименования представления можно использовать системную хранимую процедуру sp_rename.
В данном разделе мы рассмотрели, как использовать представления для получения данных, удовлетворяющих тем или иным критериям. Однако вернемся к последнему примеру. В базе данных NorthWind имеется четыре региона, и для получения списка территорий всех регионов нам нужно четыре разных представления. Эту задачу можно было бы упростить, если бы мы могли передать значение RegionID в качестве параметра. Это можно сделать с помощью хранимой процедуры, о чем мы поговорим в следующем разделе.
Хранимые процедуры
Хранимая процедура - это скомпилированный набор SQL-предложений, сохраненный в базе данных как именованный объект и выполняющийся как единый фрагмент кода. Хранимые процедуры могут принимать и возвращать параметры. Когда пользователь создает хранимую процедуру, сервер компилирует ее и помещает в разделяемый кэш, после чего скомпилированный код может быть применен несколькими пользователями. Когда приложение использует хранимую процедуру, оно передает ей параметры, если таковые требуются, и сервер выполняет процедуру без перекомпиляции.
Хранимые процедуры позволяют повысить производительность приложений. Во-первых, по сравнению с обычными SQL-запросами, посылаемыми из клиентского приложения, они требуют меньше времени для подготовки к выполнению, поскольку они уже скомпилированы и сохранены. Во-вторых, сетевой трафик в этом случае также меньше, чем в случае передачи SQL-запроса, так как по сети передается меньшее количество данных. Рис. 3 иллюстрирует вызов хранимой процедуры клиентским приложением.
Хранимые процедуры автоматически перекомпилируются, если с объектами, на которые они влияют, произведены какие-либо изменения; иными словами, они всегда актуальны. Как уже было сказано выше, хранимые процедуры могут принимать параметры, что позволяет разным приложениям использовать одну и ту же процедуру, применяя различные наборы входных данных.
Хранимые процедуры обычно используются для поддержки ссылочной целостности данных и реализации бизнес-правил. В последнем случае достигается дополнительная гибкость, поскольку, если бизнес-правила изменяются, можно изменить только текст процедуры, не изменяя клиентских приложений.
Для создания, изменения и удаления процедур существуют специальные SQL-предложения - CREATE PROCEDURE, ALTER PROCEDURE и DROP PROCEDURE. Мы рассмотрим их в следующем разделе.
Предложение CREATE PROCEDURE
Предложение CREATE PROCEDURE используется для создания хранимой процедуры. Оно имеет следующий упрощенный синтаксис:
CREATE PROC proc_name [ {@parameter data_type} [= default] ] [...] AS sql_statements
Аргумент proc_name устанавливает имя хранимой процедуры, которое должно быть уникально в рамках текущей базы данных. Аргумент @parameter определяет параметр процедуры. В предложении CREATE PROCEDURE можно определить один или более параметров. Если для параметра нет значения по умолчанию, он должен быть передан пользователем (или клиентским приложением) при вызове процедуры. В Microsoft SQL Server 7.0 число параметров хранимой процедуры не должно превышать 1024; по умолчанию они могут иметь NULL-значения.
Отметим, однако, что некоторые универсальные механизмы доступа к данным могут накладывать дополнительные ограничения на число параметров хранимых процедур. Например, BDE-драйвер для Oracle 8 способен работать только с процедурами, число параметров которых не превышает 10.
Аргумент data_type указывает тип данных для параметра. Ключевое слово default может быть использовано для установки значений по умолчанию - это может быть константа или NULL. Если указано значение по умолчанию, процедура может быть вызвана без указания значения параметра. Если процедура использует параметр с ключевым словом LIKE, ее значение по умолчанию может содержать групповые символы (%, _, и [^]).
Ключевое слово OUTPUT показывает, что это возвращаемый параметр.
Ключевое слово AS указывает действие, которое процедура должна выполнить, в виде любого количества SQL-предложений и предложений на процедурном расширении SQL, характерном для данного сервера.
Процедура, созданная с помощью предложения CREATE PROCEDURE, будет сохранена в текущей базе данных. В Microsoft SQL Server имена процедур содержатся в системной таблице sysobjects, а исходный текст - в таблице syscomments.
Для изменения созданной ранее хранимой процедуры следует использовать предложение ALTER PROCEDURE, кратко описанное в следующем разделе.
Предложение DROP PROCEDURE
Это предложение используется для удаления хранимых процедур из базы данных. Предложение DROP PROCEDURE принимает один аргумент - имя удаляемой процедуры.
При удалении хранимой процедуры сведения о ней удаляются из системных таблиц sysobjects и syscomments.
Создание и использование хранимых процедур
В разделе, посвященном представлениям, мы обращали внимание на то, что было бы удобно, если бы мы могли передать в представление параметр, содержащий значение RegionID для выбора одного из четырех регионов в базе данных NorthWind. Давайте еще раз рассмотрим запрос, возвращающий список территорий региона:
SELECT Territories.TerritoryDescription, Region.RegionDescription FROM Territories INNER JOIN Region ON Territories.RegionID = Region.RegionID WHERE Territories.RegionID = 1
Чтобы выбрать другой регион, нам нужно изменить условие в предложении WHERE в последней строке запроса. Следовательно, если мы используем переменную (назовем ее RegID), мы сможем выбрать один из четырех регионов без изменения других частей запроса.
В базе данных NorthWind четыре региона с номерами от 1 до 4. Это означает, что переменная RegID должна быть целого типа. Код хранимой процедуры приведен ниже:
CREATE PROCEDURE ShowRegion @RegID int AS SELECT Territories.TerritoryDescription, Region.RegionDescription FROM Territories INNER JOIN Region ON Territories.RegionID = Region.RegionID WHERE Territories.RegionID = @RegID
Обратите внимание на то, что мы оставили почти весь текст запроса SELECT нетронутым (он выделен курсивом) и только добавили предложение CREATE PROCEDURE с именем вновь созданной хранимой процедуры (в первой строке), объявление параметра (во второй строке) и ключевое слово AS, указывающее на начало предложений, реально выполняющих действия.
Результат выполнения созданной процедуры в SQL Server Query Analyzer для RegID =2 показан на рис. 3 .
Очевидно, что мы можем применять хранимые процедуры не только для реализации расширенных версий представлений или «интеллектуальных» запросов SELECT. Хранимые процедуры предоставляют механизмы, позволяющие автоматизировать многие рутинные задачи.
В Microsoft SQL Server 7.0 мы также можем использовать системные хранимые процедуры для работы с обычными хранимыми процедурами:
- sp_stored_procedures - показывает список хранимых процедур;
- sp_helptext - показывает исходный текст хранимой процедуры;
- sp_depends - показывает сведения о зависимостях хранимых процедур;
- sp_procoption - устанавливает опции хранимых процедур или устанавливает их;
- sp_recompile - перекомпилирует процедуру в момент ее следующего вызова;
- sp_rename - меняет имя процедуры.
Системные хранимые процедуры
Поскольку мы говорим о Microsoft SQL Server, следует отметить огромное количество системных хранимых процедур, реализованных в нем. Имена системных хранимых процедур начинаются с SP_ или XP_ и хранятся в базе данных master. Выше мы уже описывали некоторые из часто используемых системных хранимых процедур.
Обратите внимание на то, что триггеры не должны возвращать пользователю данные.
В предложении CREATE TRIGGER можно использовать две специальные таблицы. Например, таблицы deleted и inserted имеют ту же структуру, что и таблица, для которой определен триггер, и содержат старое и новое значения записей, измененных пользователем. Например, мы можем использовать следующее SQL-предложение для поиска удаленных записей:
SELECT * FROM deleted
В табл. 3 показано содержимое таблиц deleted и inserted для всех возможных изменений данных.
Для изменения имеющегося триггера следует использовать предложение ALTER TRIGGER. Мы поговорим о нем в следующем разделе.
Для начала нам нужно добавить к таблице два новых поля, в которых будут содержаться эти сведения. Назовем их UpdatedBy (имя менеджера, обновившего запись последним) и UpdatedWhen (время, когда была изменена запись). Затем создадим триггер с именем KeepTrack. Вот его код:
CREATE TRIGGER KeepTrack ON Customers FOR INSERT, UPDATE AS UPDATE Customers SET Customers.UpdatedBy = USER_NAME(), Customers.UpdatedWhen = GETDATE() FROM inserted, Customers WHERE inserted.CustomerID = Customers.CustomerID
Как видно из исходного текста триггера, он выполняется после каждой операции INSERT и UPDATE в таблице Customers. Этот триггер будет сохранять имя менеджера (пользователя базы данных) в поле Customers.UpdatedBy и дату и время изменения - в поле Customers.UpdatedWhen. Эти данные извлекаются из временной таблицы inserted.
Как видим, этот триггер позволяет следить за изменениями и вставкой новых записей в таблице.
Перед тем как закончить краткий обзор триггеров, мы должны сообщить, где можно найти сведения об имеющихся триггерах. Таблица sysobjects хранит сведения о триггерах и их типах, а таблица syscomments содержит их исходный текст.
Заключение
В этой части мы рассмотрели несколько типов объектов баз данных - хранимые процедуры, представления и триггеры. Мы узнали следующее:
- Представление - это виртуальная таблица, обычно создаваемая как подмножество столбцов одной или нескольких таблиц. Для создания представления применяется предложение CREATE VIEW, для модификации - предложение ALTER VIEW, а для удаления - предложение DROP VIEW.
- Хранимая процедура - это скомпилированный набор SQL-предложений, сохраненный в базе данных как именованный объект и выполняющийся как единый фрагмент кода. Для создания хранимой процедуры применяется предложение CREATE PROCEDURE, для изменения - ALTER PROCEDURE, а для удаления - DROP PROCEDURE.
- Tриггер - это специальный тип хранимой процедуры, которая автоматически вызывается, когда данные в определенной таблице добавляются, удаляются или изменяются с помощью SQL-предложений INSERT, DELETE или UPDATE. Триггеры создаются с помощью предложения CREATE TRIGGER. Для изменения триггера используется предложение ALTER TRIGGER, а для удаления - предложение DROP TRIGGER.
КомпьютерПресс 12"2000
Использование хранимых функций СУБД для реализации бизнес-логики или её части, всегда было камнем преткновения. С одной стороны баррикад DBA и программисты БД, с другой - разработчики backend.
Рискну навлечь на себя гнев из обоих лагерей, но всё же просуммирую плюсы и минусы и изложу свои соображения о том, когда стоит писать код в хранимых функциях, а когда следует выносить наружу.
Начнём с аргументов против:
Размазывание бизнес-логики
Это, на самом деле не проблема СУБД и ХФ, как инструмента - это проблема их неверного использования. У программиста бд может возникнуть желание описать всю логику реализуемого действия в хранимой функции - действительно, ведь все данные вот они, под рукой. Если программист поддастся на искушение, а его руководитель не возразит, в будущем могут возникнуть проблемы с узостью интерфейса со внешней системой (например, с сервером приложений) - придётся добавлять новые параметры, усложнять логику и т.п. Это даже может привести к тому, что появятся «дублирующие» ХФ со слегка иным функционалом.Скудность языка СУБД
Есть такое дело. Традиционные языки для написания ХФ pl/sql, t-sql, pl/pgsql довольно примитивны по сравнению с современными языками общего назначения. Стоит заметить, что есть возможность писать ХФ и на более продвинутых языках, например Java в Oracle или Python в postgresql.Непереносимость хранимых функций
Имеется в виду несовместимость диалектов процедурных языков разных СУБД. Многоплатформенность как раз на уровне - благодаря поддержке разных ОС и архитектур в самих СУБД и независимости встроенных языков от внешней платформы. Здесь опять решение зависит от специфики проекта. Если проект тиражируемый, причём вы не контролируете платформу (классический пример - CMS), то переносимость вам необходима и использование ХФ - только добавит головной боли. Если же проект уникальный, либо внедрения будут происходить унифицировано (например в разных филиалах одной компании), то про непереносимость между разными СУБД можно забыть.Отсутствие необходимых навыков у команды и высокая «стоимость» соответствующих специалистов
Это, на мой взгляд, самый серьёзный аргумент против использования ХФ. Тут всё зависит от масштабов проекта. Грубо говоря, использование хранимого кода на стороне СУБД оправдано в средних-крупных enterprise проектах. Если проект помельче - овчинка выделки не стоит. Если проект огромный сверхнагруженный, то архитектура с ХФ и РСУБД упрётся в проблемы масштабирования - тут необходимо использование специфического хранилища и подхода к обработке данных.Теперь плюсы:
Скорость
При обработке даже небольших объёмов данных во внешнем приложении мы тратим дополнительное время на передачу по сети и преобразование данных в нужный нам формат. К тому же в СУБД уже встроены, отлажены и протестированы близкие к оптимальным алгоритмы обработки данных, вашим программистам незачем практиковаться в изобретении велосипедов.Сокрытие структуры данных
С ростом и эволюцией программной системы схема данных может и должна меняться. Хорошо спроектированный программный интерфейс на ХФ позволит менять схему данных не изменяя код внешних приложений (которых может быть несколько). Отсюда органично вытекает и разделение ролей разработчиков, которые работают с БД и знают её структуру, и разработчиков внешних приложений, которые должны знать лишь предоставляемый API. При использовании динамического SQL на стороне приложения, для подобного разделения вводятся дополнительные слои программных абстракций БД, различные ORM.Гибкое управление правами доступа
Хорошей практикой является ограничение пользователя, под которым «ходит» в базу клиентское приложение в правах таким образом, что он не имеет прав на чтение и изменение никаких объектов. Лишь выполняет разрешённые ему функции. Таким образом можно жёстко контролировать какие действия доступны клиенту, уменьшается вероятность нарушения целостности данных из-за ошибки клиентского приложения.Меньшая вероятность SQL injection
При использовании динамического SQL со стороны клиентской программы, клиентская программа передаёт СУБД SQL команды в виде строк, предварительно формируемых в коде. При формировании этих строк программисту нужно быть предельно внимательным, чтобы не допустить возможности непредусмотренной модификации SQL команды. При использовании ХФ SQL код на стороне приложения обычно статический, и выглядит, как простой вызов ХФ, параметры которой передаются не строками, а через placeholders (:variable) через механизм binding. Конечно это не исключает возможность SQL injection полностью (ведь можно умудриться в ХФ конкатенировать строку, переданную параметром с текстом динамически выполняемого SQL запроса), но значительно уменьшает её вероятность.Повторное использование SQL
Реализуя логику работы с данными в хранимом слое мы получаем привычную нам иерархическую модель повторного использования SQL кода.При использовании динамического SQL повторное использование запросов затруднено.
Например пусть есть система A на базе ХФ и система Б на базе динамического SQL. В обеих системах есть функция получения цены товара get_price. В случае A - это хранимая функция или отображение (view), в случае Б, допустим, процедура на java, через JDBC выполняющая SQL запрос. Есть задача - получить общую стоимость товара на складе. В случае A мы джоиним get_price прямо в запрос, получающий список товаров на складе (в случае, если get_price - view или ХФ на SQL, как например в PostgreSQL, то оптимизатор разворачивает запрос inline - тем самым получается один запрос, который быстро находит сумму).
В случае B есть два варианта - либо пробежать по курсору с выборкой товаров на складе и n раз вызвать get_price (а это значит что вся выборка должна передаться по сети на клиент) либо забыть про повторное использование и написать подзапрос, дублирующий тот, что был уже написан в get_price. Оба варианта - плохие.
Простая отладка SQL
Упрощается отладка (по сравнению с разнородной процедурой внешний код+sql)В системах с динамическим SQL (любые ORM) даже простая задача поиска проблемного куска SQL может оказаться сложной.
Семантическая и синтаксическая проверка SQL на этапе компиляции.
Возможность профилирования функций и поиска узких мест.
Возможность трассировки уже запущеной и работающей системы.
Автоматический контроль зависимостей - при изменении определения объекта инвалидируются зависимые сущности.
Когда писать бизнес-логику в БД?
Если важна скорость обработки данных
Обработка данных прямо на месте их хранения зачастую даёт значительный прирост скорости обработки. Становятся возможными такие оптимизации, как, например, агрегации на уровне хранилища данных - данные с массива даже не передаются на сервер СУБД, не говоря о клиенте.Когда важна целостность и непротиворечивость данных
В хранимых функциях с явным управлением транзакциями и блокировками проще обеспечить целостность данных и атомарность операций. Конечно всё это может быть реализовано и снаружи, но это отдельная и большая работа.Данные имеют сложную, но устоявшуюся структуру
Плоские и слабо взаимосвязанные структуры часто не требуют всего богатства инструментов обработки, которые предлагают СУБД. Для них можно использовать сверхбыстрые key-value хранилища и кеширование в памяти.Сложно организованные сильно связанные иерархические и сетевые структуры - явный показатель, что ваши знания РСУБД пригодятся!
Когда выносить код наружу?
Работа с внешними данными
Если специфика системы такова, что данных, приходящих на обработку снаружи (с датчиков, из других систем) больше, чем данных, сохраняемых в БД, то многие плюсы БД, как платформы программирования теряются. Оказывается проще обработать поступающие даннные снаружи и сохранить результат в БД, чем сначала всё пихать в БД, а потом обрабатывать. Здесь соблюдается тот же принцип - обрабатывать данные как можно ближе к источнику, о котором мы говорили выше применительно обработке данных, уже хранящихся в БД.Сложные алгоритмы
Сложные или высоко-оптимизированные алгоритмы-числодробилки лучше писать на более приспособленных для этого языках. Встроенные языки РСУБД очень мощны (в том смысле, что высокоуровневые, а не гибкие), но за счёт этого имеют высокий overhead.Highload
В сверхвысоконагруженных системах обычные подходы к сериализации транзакций и синхронизации серверов кластера становятся узким местом. Для таких систем характерны уникальные решения под конкретные задачи, универсальные и мощные системы РСУБД часто оказываются слишком медлительными при нагрузках в сотни тысяч конкурентных транзакций в секунду.Вывод такой, что чёткого алгоритма нет. Каждый раз решение остаётся за архитекторами и менеджером и от него зависит то, завязнет ли проект в проблемах с race conditions и неконсистентностью данных NoSQL, проблемах с производительностью и отладкой запросов ORM, или упрётся в проблемы масштабирования СУБД при использовании хранимых функций. Поэтому - принимайте верные решения:)
