Хранимая процедура объект. Хранимые процедуры. Изменение структуры хранимых процедур
Хранимая процедура (англ. 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, как того требует процедура. Параметру можно присвоить не только постоянное значение, но и значение другой переменной.
Включай в свои процедуры строку - SET NOCOUNT ON:
С каждым DML выражением, SQL server заботливо возвращает нам сообщение содержащее колличество обработанных записей. Данная информация может быть нам полезна во время отладки кода, но после будет совершенно бесполезной. Прописывая SET NOCOUNT ON, мы отключаем эту функцию. Для хранимых процедур содержащих несколько выражений или\и циклы данное действие может дать значительный прирост производительности, потому как колличество трафика будет значительно снижено.
Transact-SQL
Используй имя схемы с именем объекта:
Ну тут думаю понятно. Данная операция подсказывает серверу где искать объекты и вместо того чтобы беспорядочно шарится по своим закромам, он сразу будет знать куда ему нужно пойти и что взять. При большом колличестве баз, таблиц и хранимых процедур может значительно сэкономить наше время и нервы.
Transact-SQL
SELECT * FROM dbo.MyTable --Вот так делать хорошо -- Вместо SELECT * FROM MyTable --А так делать плохо --Вызов процедуры EXEC dbo.MyProc --Опять же хорошо --Вместо EXEC MyProc --Плохо!
Не используй префикс «sp_» в имени своих хранимых процедур:
Если имя нашей процедуры начинается с «sp_», SQL Server в первую очередь будет искать в своей главной базе данных. Дело в том, что данный префикс используется для личных внутренних хранимых процедур сервера. Поэтому его использование может привести к дополнительным расходам и даже неверному результату, если процедура с таким же имененем как у вас будет найдена в его базе.
Используй IF EXISTS (SELECT 1) вместо IF EXISTS (SELECT *):
Чтобы проверить наличие записи в другой таблице, мы используем выражение IF EXISTS. Данное выражение возвращает true если из внутреннего выражения возвращается хоть одно изначение, не важно «1», все колонки или таблица. Возращаемые данные, в принципе никак не используются. Таким образом для сжатия трафика во время передачи данных логичнее использовать «1», как показано ниже.
Хранимые процедуры SQL представляют собой исполняемый программный модуль, который может храниться в в виде различных объектов. Другими словами, это объект, в котором содержатся SQL-инструкции. Эти хранимые процедуры могут быть выполнены в клиенте прикладных программ, чтобы получить хорошую производительность. Кроме того, такие объекты нередко вызываются из других сценариев или даже из какого-либо другого раздела.
Введение
Многие считают, что они похожи на процедуры различных (соответственно, кроме MS SQL). Пожалуй, это действительно так. У них есть схожие параметры, они могут выдавать схожие значения. Более того, в ряде случаев они соприкасаются. Например, они сочетаются с базами данных DDL и DML, а также с функциями пользователя (кодовое название - UDF).
В действительности же хранимые процедуры SQL обладают широким спектром преимуществ, которые выделяют их среди подобных процессов. Безопасность, вариативность программирования, продуктивность - все это привлекает пользователей, работающих с базами данных, все больше и больше. Пик популярности процедур пришелся на 2005-2010 годы, когда вышла программа от "Майкрософт" под названием «SQL Server Management Studio». С ее помощью работать с базами данных стало гораздо проще, практичнее и удобнее. Из года в год такой набирал популярность в среде программистов. Сегодня же является абсолютно привычной программой, которая для пользователей, «общающихся» с базами данных, встала наравне с «Экселем».
При вызове процедуры она моментально обрабатывается самим сервером без лишних процессов и вмешательства пользователя. После этого можно осуществлять любые удаление, исполнение, изменение. За все это отвечает DDL-оператор, который в одиночку совершает сложнейшие действия по обработке объектов. Причем все это происходит очень быстро, а сервер фактически не нагружается. Такая скорость и производительность позволяют очень быстро передавать большие объемы информации от пользователя на сервер и наоборот.
Для реализации данной технологии работы с информацией существует несколько языков программирования. К ним можно отнести, например, PL/SQL от Oracle, PSQL в системах InterBase и Firebird, а также классический «майкрософтовский» Transact-SQL. Все они предназначены для создания и выполнения хранимых процедур, что позволяет в крупных обработчиках баз использовать собственные алгоритмы. Это нужно и для того, чтобы те, кто осуществляет управление такой информацией, могли защитить все объекты от несанкционированного доступа сторонних лиц и, соответственно, создания, изменения или удаления тех или иных данных.
Продуктивность
Эти объекты баз данных могут быть запрограммированы различными путями. Это позволяет пользователям выбирать тип используемого способа, который будет наиболее подходящим, что экономит силы и время. Кроме того, процедура сама обрабатывается, что позволяет избежать огромных временных затрат на обмен между сервером и пользователем. Также модуль можно перепрограммировать и изменить в нужное направление в абсолютно любой момент. Особенно стоит отметить скорость, с которой происходит запуск хранимой процедуры SQL: это процесс происходит быстрее иных, схожих с ним, что делает его удобным и универсальным.
Безопасность
Такой тип обработки информации отличается от схожих процессов тем, что он гарантирует повышенную безопасность. Это обеспечивается за счет того, что доступ других пользователей к процедурам может быть исключен целиком и полностью. Это позволит администратору проводить операции с ними самостоятельно, не опасаясь за перехват информации или несанкционированный доступ к базе данных.
Передача данных
Связь между хранимой процедурой SQL и клиентским приложением заключается в использовании параметров и возвращаемых значениях. Последним не обязательно передавать данные в хранимую процедуру, однако эта информация (в основном по запросу пользователя) и перерабатывается для SQL. После того как хранимая процедура завершила свою работу, она отсылает пакеты данных обратно (но, опять же, по желанию) к вызвавшему его приложению, используя различные методы, с помощью которых может быть осуществлен как вызов хранимой процедуры SQL, так и возврат, например:
Передача данных с помощью параметра типа Output;
Передача данных с помощью оператора возврата;
Передача данных с помощью оператора выбора.
А теперь разберемся, как же выглядит этот процесс изнутри.
1. Создание EXEC-хранимой процедуры в SQL
Вы можете создать процедуру в MS SQL (Managment Studio). После того как создастся процедура, она будет перечислена в программируемый узел базы данных, в которой процедура создания выполняется оператором. Для выполнения хранимые процедуры SQL используют EXEC-процесс, который содержит имя самого объекта.
При создании процедуры ее название появляется первым, после чего производится один или несколько параметров, присвоенных ему. Параметры могут быть необязательными. После того как параметр(ы), то есть тело процедуры, будут написаны, нужно провести некоторые необходимые операции.
Дело в том, что тело может иметь локальные переменные, расположенные в ней, и эти переменные являются локальными также по отношению к процедурам. Другими словами, их можно рассматривать только внутри тела процедуры Microsoft SQL Server. Хранимые процедуры в таком случае считаются локальными.
Таким образом, чтобы создать процедуру, нам нужно имя процедуры и, по меньшей мере, один параметр в качестве тела процедуры. Обратите внимание, что отличным вариантом в таком случае является создание и выполнение процедуры с именем схемы в классификаторе.
Тело процедуры может иметь любой вид из например, такие как создание таблицы, вставки одного или нескольких строк таблицы, установление типа и характера базы данных и так далее. Тем не менее тело процедуры ограничивает выполнение некоторых операций в нем. Некоторые из важных ограничений перечислены ниже:
Тело не должно создавать какой-либо другой хранимой процедуры;
Тело не должно создать ложное представление об объекте;
Тело не должно создавать никаких триггеров.
2. Установка переменной в тело процедуры
Вы можете сделать переменные локальными для тела процедуры, и тогда они будут находиться исключительно внутри тела процедуры. Хорошей практикой является создание переменных в начале тела хранимой процедуры. Но также вы можете устанавливать переменные в любом месте в теле данного объекта.
Иногда можно заметить, что несколько переменных установлены в одной строке, и каждый переменный параметр отделяется запятой. Также обратите внимание, что переменная имеет префикс @. В теле процедуры вы можете установить переменную, куда вы хотите. К примеру, переменная @NAME1 может объявлена ближе к концу тела процедуры. Для того чтобы присвоить значение объявленной переменной используется набор личных данных. В отличие от ситуации, когда объявлено более одной переменной в одной строке, в такой ситуации используется только один набор личных данных.
Часто пользователи задают вопрос: «Как назначить несколько значений в одном операторе в теле процедуры?» Что ж. Вопрос интересный, но сделать это гораздо проще, чем вы думаете. Ответ: с помощью таких пар, как «Select Var = значение». Вы можете использовать эти пары, разделяя их запятой.
В самых различных примерах люди показывают создание простой хранимой процедуры и выполнение ее. Однако процедура может принимать такие параметры, что вызывающий ее процесс будет иметь значения, близкие к нему (но не всегда). Если они совпадают, то внутри тела начинаются соответствующие процессы. Например, если создать процедуру, которая будет принимать город и регион от вызывающего абонента и возвращать данные о том, сколько авторов относятся к соответствующим городу и региону. Процедура будет запрашивать таблицы авторов базы данных, к примеру, Pubs, для выполнения этого подсчета авторов. Чтобы получить эти базы данных, к примеру, Google загружает сценарий SQL со страницы SQL2005.
В предыдущем примере процедура принимает два параметра, которые на английском языке условно будут называться @State и @City. Тип данных соответствует типу, определенному в приложении. Тело процедуры имеет внутренние переменные @TotalAuthors (всего авторов), и эта переменная используется для отображения их количества. Далее появляется раздел выбора запроса, который все подсчитывает. Наконец, подсчитанное значение выводится в окне вывода с помощью оператора печати.
Как в SQL выполнить хранимую процедуру
Есть два способа выполнения процедуры. Первый путь показывает, передавая параметры, как разделенный запятыми список выполняется после имени процедуры. Допустим, мы имеем два значения (как в предыдущем примере). Эти значения собираются с помощью переменных параметров процедуры @State и @City. В этом способе передачи параметров важен порядок. Такой метод называется порядковая передача аргументов. Во втором способе параметры уже непосредственно назначены, и в этом случае порядок не важен. Этот второй метод известен как передача именованных аргументов.
Процедура может несколько отклоняться от типичной. Все так же, как и в предыдущем примере, но только здесь параметры сдвигаются. То есть параметр @City хранится первым, а @State хранится рядом со значением по умолчанию. Параметр по умолчанию выделяется обычно отдельно. Хранимые процедуры SQL проходят как просто параметры. В этом случае, при условии, параметр «UT» заменяет значение по умолчанию «СА». Во втором исполнении проходит только одно значение аргумента для параметра @City, и параметр @State принимает значение по умолчанию «СА». Опытные программисты советуют, чтобы все переменные по умолчанию располагались ближе к концу списка параметров. В противном случае исполнение не представляется возможным, и тогда вы должны работать с передачей именованных аргументов, что дольше и сложнее.
4. Хранимые процедуры SQL Server: способы возврата
Существует три важных способа отправки данных в вызванной хранимой процедуре. Они перечислены ниже:
Возврат значения хранимой процедуры;
Выход параметра хранимых процедур;
Выбор одной из хранимых процедур.
4.1 Возврат значений хранимых процедур SQL
В этой методике процедура присваивает значение локальной переменной и возвращает его. Процедура может также непосредственно возвращать постоянное значение. В следующем примере, мы создали процедуру, которая возвращает общее число авторов. Если сравнить эту процедуру с предыдущими, вы можете увидеть, что значение для печати заменяется обратным.
Теперь давайте посмотрим, как выполнить процедуру и вывести значение, возвращаемое ей. Выполнение процедуры требует установления переменной и печати, которая проводится после всего этого процесса. Обратите внимание, что вместо оператора печати вы можете использовать Select-оператор, например, Select @RetValue, а также OutputValue.
4.2 Выход параметра хранимых процедур SQL
Ответное значение может быть использовано для возврата одной переменной, что мы и видели в предыдущем примере. Использование параметра Output позволяет процедуре отправить одно или несколько значений переменных для вызывающей стороны. Выходной параметр обозначается как раз-таки этим ключевым словом «Output» при создании процедуры. Если параметр задан в качестве выходного параметра, то объект процедуры должен присвоить ему значение. Хранимые процедуры SQL, примеры которых можно увидеть ниже, в таком случае возвращаются с итоговой информацией.
В нашем примере будет два выходных имени: @TotalAuthors и @TotalNoContract. Они указываются в списке параметров. Эти переменные присваивают значения внутри тела процедуры. Когда мы используем выходные параметры, вызывающий абонент может видеть значение, установленное внутри тела процедуры.
Кроме того, в предыдущем сценарии две переменные объявляются, чтобы увидеть значения, которые установливают хранимые процедуры MS SQL Server в выходном параметре. Тогда процедура выполняется путем подачи нормального значения параметра «CA». Следующие параметры являются выходными и, следовательно, объявленные переменные передаются в установленном порядке. Обратите внимание, что при прохождении переменных выходное ключевое слово также задается здесь. После того, как процедура выполнена успешно, значения, возвращаемые с помощью выходных параметров, выводятся на окно сообщений.
4.3 Выбор одной из хранимых процедур SQL
Эта техника используется для возврата набора значений в виде таблицы данных (RecordSet) к вызывающей хранимой процедуре. В этом примере SQL хранимая процедура с параметрами @AuthID запрашивает таблицу «Авторы» путем фильтрации возвращаемых записей с помощью этого параметра @AuthId. Оператор Select решает, что должно быть возвращено вызывающему хранимой процедуры. При выполнении хранимой процедуры AuthId передается обратно. Такая процедура здесь всегда возвращает только одну запись или же вообще ни одной. Но хранимая процедура не имеет каких-либо ограничений на возвращение более одной записи. Нередко можно встретить примеры, в которых возвращение данных с использованием избранных параметров с участием вычисленных переменных происходит путем предоставления нескольких итоговых значений.
В заключение
Хранимая процедура является довольно серьезным программным модулем, возвращающим или передающим, а также устанавливающим необходимые переменные благодаря клиентскому приложению. Поскольку хранимая процедура выполняется на сервере сама, обмена данными в огромных объемах между сервером и клиентским приложением (для некоторых вычислений) можно избежать. Это позволяет снижать нагрузки на сервера SQL, что, конечно же, идет на руку их держателям. Одним из подвидов являются хранимые процедуры T SQL, однако их изучение необходимо тем, кто занимается созданием внушительных баз данных. Также существует большое, даже огромное количество нюансов, которые могут быть полезны при изучении хранимых процедур, однако это нужно больше для тех, кто планирует плотно заняться программированием, в том числе профессионально.
В MySQL 5 есть много новых функций, одной из самых весомых из которых является создание хранимых процедур. В этом уроке я расскажу о том, что они из себя представляют, а также о том, как они могут облегчить вам жизнь.
Введение
Хранимая процедура - это способ инкапсуляции повторяющихся действий. В хранимых процедурах можно объявлять переменные, управлять потоками данных, а также применять другие техники программирования.
Причина их создания ясна и подтверждается частым использованием. С другой стороны, если вы поговорите с теми, кто работает с ними нерегулярно, то мнения разделятся на два совершенно противоположных фланга. Не забывайте об этом.
За
- Разделение логики с другими приложениями. Хранимые процедуры инкапсулируют функциональность; это обеспечивает связность доступа к данным и управления ими между различными приложениями.
- Изоляция пользователей от таблиц базы данных. Это позволяет давать доступ к хранимым процедурам, но не к самим данным таблиц.
- Обеспечивает механизм защиты. В соответствии с предыдущим пунктом, если вы можете получить доступ к данным только через хранимые процедуры, никто другой не сможет стереть ваши данные через команду SQL DELETE.
- Улучшение выполнения как следствие сокращения сетевого трафика. С помощью хранимых процедур множество запросов могут быть объединены.
Против
- Повышение нагрузки на сервер баз данных в связи с тем, что большая часть работы выполняется на серверной части, а меньшая - на клиентской.
- Придется много чего подучить. Вам понадобится выучить синтаксис MySQL выражений для написания своих хранимых процедур.
- Вы дублируете логику своего приложения в двух местах: серверный код и код для хранимых процедур, тем самым усложняя процесс манипулирования данными.
- Миграция с одной СУБД на другую (DB2, SQL Server и др.) может привести к проблемам.
Инструмент, в котором я работаю, называется MySQL Query Browser, он достаточно стандартен для взаимодействия с базами данных. Инструмент командной строки MySQL - это еще один превосходный выбор. Я рассказываю вам об этом по той причине, что всеми любимый phpMyAdmin не поддерживает выполнение хранимых процедур.
Кстати, я использую элементарную структуру таблиц, чтобы вам было легче разобраться в этой теме. Я ведь рассказываю о хранимых процедурах, а они достаточно сложны, чтобы вникать еще и в громоздкую структуру таблиц.
Шаг 1: Ставим ограничитель
Ограничитель - это символ или строка символов, который используется для указания клиенту MySQL, что вы завершили написание выражения SQL. Целую вечность ограничителем был символ точки с запятой. Тем не менее, могут возникнуть проблемы, так как в хранимой процедуре может быть несколько выражений, каждое из которых должно заканчиваться точкой с запятой. В этом уроке я использую строку “//” в качестве ограничителя.
Шаг 2: Как работать с хранимыми процедурами
Создание хранимой процедуры
DELIMITER // CREATE PROCEDURE `p2` () LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER COMMENT "A procedure" BEGIN SELECT "Hello World !"; END//
Первая часть кода создает хранимую процедуру. Следующая - содержит необязательные параметры. Затем идет название и, наконец, тело самой процедуры.
Названия хранимых процедур чувствительны к регистру. Вам также нельзя создавать несколько процедур с одинаковым названием. Внутри хранимой процедуры не может быть выражений, изменяющих саму базу данных.
4 характеристики хранимой процедуры:
- Language: в целях обеспечения переносимости, по умолчанию указан SQL.
- Deterministic: если процедура все время возвращает один и тот же результат, и принимает одни и те же входящие параметры. Это для репликации и процесса регистрации. Значение по умолчанию - NOT DETERMINISTIC.
- SQL Security: во время вызова идет проверка прав пользователя. INVOKER - это пользователь, вызывающий хранимую процедуру. DEFINER - это “создатель” процедуры. Значение по умолчанию - DEFINER.
- Comment: в целях документирования, значение по умолчанию - ""
Вызов хранимой процедуры
Чтобы вызвать хранимую процедуру, необходимо напечатать ключевое слово CALL, а затем название процедуры, а в скобках указать параметры (переменные или значения). Скобки обязательны.
CALL stored_procedure_name (param1, param2, ....) CALL procedure1(10 , "string parameter" , @parameter_var);
Изменение хранимой процедуры
В MySQL есть выражение ALTER PROCEDURE для изменения процедур, но оно подходит для изменения лишь некоторых характеристик. Если вам нужно изменить параметры или тело процедуры, вам следует удалить и создать ее заново.
Удаление хранимой процедуры
DROP PROCEDURE IF EXISTS p2;
Это простая команда. Выражение IF EXISTS отлавливает ошибку в случае, если такой процедуры не существует.
Шаг 3: Параметры
Давайте посмотрим, как можно передавать в хранимую процедуру параметры.
- 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): один параметр, одновременно входящий и возвращаемый.
Естественно, вы можете задавать несколько параметров разных типов.
Пример параметра IN
DELIMITER // CREATE PROCEDURE `proc_IN` (IN var1 INT) BEGIN SELECT var1 + 2 AS result; END//
Пример параметра OUT
DELIMITER // CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100)) BEGIN SET var1 = "This is a test"; END //
Пример параметра INOUT
DELIMITER // CREATE PROCEDURE `proc_INOUT` (OUT var1 INT) BEGIN SET var1 = var1 * 2; END //
Шаг 4: Переменные
Сейчас я научу вас создавать переменные и сохранять их внутри процедур. Вы должны объявлять их явно в начале блока BEGIN/END, вместе с их типами данных. Как только вы объявили переменную, вы можете использовать ее там же, где переменные сессии, литералы или имена колонок.
Синтаксис объявления переменной выглядит так:
DECLARE varname DATA-TYPE DEFAULT defaultvalue;
Давайте объявим несколько переменных:
DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR(50); DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT;
Работа с переменными
Как только вы объявили переменную, вы можете задать ей значение с помощью команд SET или SELECT:
DELIMITER // CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20)) BEGIN DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR(50); DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT; INSERT INTO table1 VALUES (a); SET str = "I am a string"; SELECT CONCAT(str,paramstr), today FROM table2 WHERE b >=5; END //
Шаг 5: Структуры управления потоками
MySQL поддерживает конструкции IF, CASE, ITERATE, LEAVE LOOP, WHILE и REPEAT для управления потоками в пределах хранимой процедуры. Мы рассмотрим, как использовать IF, CASE и WHILE, так как они наиболее часто используются.
Конструкция IF
С помощью конструкции IF, мы можем выполнять задачи, содержащие условия:
DELIMITER // CREATE PROCEDURE `proc_IF` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; IF variable1 = 0 THEN SELECT variable1; END IF; IF param1 = 0 THEN SELECT "Parameter value = 0"; ELSE SELECT "Parameter value <> 0"; END IF; END //
Конструкция CASE
CASE - это еще один метод проверки условий и выбора подходящего решения. Это отличный способ замены множества конструкций IF. Конструкцию можно описать двумя способами, предоставляя гибкость в управлении множеством условных выражений.
DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; CASE variable1 WHEN 0 THEN INSERT INTO table1 VALUES (param1); WHEN 1 THEN INSERT INTO table1 VALUES (variable1); ELSE INSERT INTO table1 VALUES (99); END CASE; END //
DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; CASE WHEN variable1 = 0 THEN INSERT INTO table1 VALUES (param1); WHEN variable1 = 1 THEN INSERT INTO table1 VALUES (variable1); ELSE INSERT INTO table1 VALUES (99); END CASE; END //
Конструкция WHILE
Технически, существует три вида циклов: цикл WHILE, цикл LOOP и цикл REPEAT. Вы также можете организовать цикл с помощью техники программирования “Дарта Вейдера”: выражения GOTO. Вот пример цикла:
DELIMITER // CREATE PROCEDURE `proc_WHILE` (IN param1 INT) BEGIN DECLARE variable1, variable2 INT; SET variable1 = 0; WHILE variable1 < param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //
Шаг 6: Курсоры
Курсоры используются для прохождения по набору строк, возвращенному запросом, а также обработки каждой строки.
MySQL поддерживает курсоры в хранимых процедурах. Вот краткий синтаксис создания и использования курсора.
DECLARE cursor-name CURSOR FOR SELECT ...; /*Объявление курсора и его заполнение */ DECLARE CONTINUE HANDLER FOR NOT FOUND /*Что делать, когда больше нет записей*/ OPEN cursor-name; /*Открыть курсор*/ FETCH cursor-name INTO variable [, variable]; /*Назначить значение переменной, равной текущему значению столбца*/ CLOSE cursor-name; /*Закрыть курсор*/
В этом примере мы проведем кое-какие простые операции с использованием курсора:
DELIMITER // CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT) BEGIN DECLARE a, b, c INT; DECLARE cur1 CURSOR FOR SELECT col1 FROM table1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur1; SET b = 0; SET c = 0; WHILE b = 0 DO FETCH cur1 INTO a; IF b = 0 THEN SET c = c + a; END IF; END WHILE; CLOSE cur1; SET param1 = c; END //
У курсоров есть три свойства, которые вам необходимо понять, чтобы избежать получения неожиданных результатов:
- Не чувствительный: открывшийся однажды курсор не будет отображать изменения в таблице, происшедшие позже. В действительности, MySQL не гарантирует то, что курсор обновится, так что не надейтесь на это.
- Доступен только для чтения: курсоры нельзя изменять.
- Без перемотки: курсор способен проходить только в одном направлении - вперед, вы не сможете пропускать строки, не выбирая их.
Заключение
В этом уроке я ознакомил вас с основами работы с хранимыми процедурами и с некоторыми специфическими свойствами, связанными с ней. Конечно, вам нужно будет углубить знания в таких областях, как безопасность, выражения SQL и оптимизация, прежде чем стать настоящим гуру MySQL процедур.
Вы должны подсчитать, какие преимущества даст вам использование хранимых процедур в вашем конкретном приложении, и только потом создавать лишь необходимые процедуры. В общем, я использую процедуры; по-моему, их стоит внедрять в проекты в следствие их безопасности, обслуживания кода и общего дизайна. К тому же, не забывайте, что над процедурами MySQL все еще ведется работа. Ожидайте улучшений, касающихся функциональности и улучшений. Прошу, не стесняйтесь делиться мнениями.
Хранимые процедуры
Предметом этой главы является один из наиболее мощных
инструментов, предлагаемых разработчикам приложений баз данных InterBase для
реализации бизнес-логики Хранимые процедуры (англ, stoied proceduies) позволяют
реализовать значительную часть логики приложения на уровне базы данных и таким
образом повысить производительность всего приложения, централизовать обработку
данных и уменьшить количество кода, необходимого для выполнения поставленных
задач Практически любое достаточно сложное приложение баз данных не обходится
без использования хранимых процедур.
Помимо этих широко известных
преимуществ использования хранимых процедур, общих для большинства реляционных
СУБД, хранимые процедуры InterBase могут играть роль практически полноценных
наборов данных, что позволяет использовать возвращаемые ими результаты в обычных
SQL-запросах.
Часто начинающие разработчики представляют себе хранимые
процедуры просто как набор специфических SQL-запросов, которые что-то делают
внутри базы данных, причем бытует мнение, что работать с хранимыми процедурами
намного сложнее, чем реализовать ту же функциональность в клиентском приложении,
на языке высокого уровня
Так что же такое хранимые процедуры в InterBase?
Хранимая процедура (ХП) - это часть метаданных базы данных, представляющая
собой откомпилированную во внутреннее представление InterBase подпрограмму,
написанную на специальном языке, компилятор которого встроен в ядро сервера
InteiBase
Хранимую процедуру можно вызывать из клиентских приложений, из
триггеров и других хранимых процедур. Хранимая процедура выполняется внутри
серверного процесса и может манипулировать данными в базе данных, а также
возвращать вызвавшему ее клиенту (т е триггеру, ХП, приложению) результаты
своего выполнения
Основой мощных возможностей, заложенных в ХП, является
процедурный язык программирования, имеющий в своем составе как модифицированные
предложения обычного SQL, такие, как INSERT, UPDATE и SELECT, так и средства
организации ветвлений и циклов (IF, WHILE), а также средства обработки ошибок и
исключительных ситуаций Язык хранимых процедур позволяет реализовать сложные
алгоритмы работы с данными, а благодаря ориентированности на работу с
реляционными данными ХП получаются значительно компактнее аналогичных процедур
на традиционных языках.
Надо отметить, что и для триггеров используется этот
же язык программирования, за исключением ряда особенностей и ограничений.
Отличия подмножества языка, используемого в триггерах, от языка ХП подробно
рассмотрены в главе "Триггеры" (ч 1).
Пример простой хранимой процедуры
Настало время создать первую хранимую процедуру и на ее примере
изучить процесс создания хранимых процедур. Но для начала следует сказать
несколько слов о том, как работать с хранимыми процедурами Дело в том, что своей
славой малопонятного и неудобного инструмента ХП обязаны чрезвычайно бедным
стандартным средствам разработки и отладки хранимых процедур. В документации по
InterBase рекомендуется создавать процедуры с помощью файлов SQL-скриптов,
содержащих текст ХП, которые подаются на вход интерпретатору isql, и таким
образом производить создание и модификацию ХП Если в этом SQL-скрипте на этапе
компиляции текста процедуры в BLR (о BLR см главу "Структура базы данных
InterBase" (ч. 4)) возникнет ошибка, то isql выведет сообщение о том, на какой
строке файла SQL-скрипта возникла эта ошибка. Исправляйте ошибку и повторяйте
все сначала. Про отладку в современном понимании этого слова, т. е. о
трассировке выполнения, с возможностью посмотреть промежуточные значения
переменных, речь вообще не идет. Очевидно, что такой подход не способствует
росту привлекательности хранимых процедур в глазах разработчика
Однако
помимо стандартного минималистского подхода к разработке ХП <_\ществ\ют также
инструменты сторонних разработчиков, которые делают работу с хранимыми
процедурами весьма удобной Большинство универсальных продуктов для работы с
InterBase, перечисленных в приложении "Инструменты администратора и разработчика
InterBase", предоставляют удобный инструментарий для работы с ХП. Мы рекомендуем
обязательно воспользоваться одним из этих инструментов для работы с хранимыми
процедурами и изложение материала будем вести в предположении, что у вас имеется
удобный GUI-инструмент, избавляющий от написания традиционных SQL-скриптов
Синтаксис хранимых процедур описывается следующим образом:
CREATE PROCEDURE name
[ (param
datatype [, param datatype ...]) ]
)]
AS
< procedure_body> =
[
<
block>
< vanable_declaration_list>
=
DECLARE VARIABLE var datatype;
BEGIN
< compound_statement>
[< compound_statement> ...]
END
< compound_statement> =
(
Выглядит довольно объемно и может быть
даже громоздко, но на самом деле все очень просто Для того чтобы постепенно
освоить синтаксис, давайте будем рассматривать постепенно усложняющиеся примеры.
Итак, вот пример очень простой хранимой процедуры, которая принимает на
входе два числа, складывает их и возвращает полученный результат:
CREATE PROCEDURE SP_Add(first_arg DOUBLE PRECISION,
second_arg DOUBLE PRECISION)
RETURNS (Result DOUBLE PRECISION)
AS
BEGIN
Result=first_arg+second_arg;
SUSPEND;
END
Как
видите, все просто: после команды CREATE PROCEDURE указывается имя вновь
создаваемой процедуры (которое должно быть уникальным в пределах базы данных) -
в данном случае SP_Add, затем в скобках через запятую перечисляются входные
параметры ХП - first_arg и second_arg - с указанием их типов.
Список входных
параметров является необязательной частью оператора CREATE PROCEDURE - бывают
случаи, когда все данные для своей работы процедура получает посредством
запросов к таблицам внутри тела процедуры.
В хранимых процедурах используются любые скалярные типы данных InteiBase He предусмотрено применение массивов и типов, определяемых пользователем, - доменов
Далее идет ключевое слово RETURNS, после которого в скобках
перечисляются возвращаемые параметры с указанием их типов - в данном случае
только один - Result.
Если процедура не должна возвращать параметры, то
слово RETURNS и список возвращаемых параметров отсутствуют.
После RETURNSQ
указано ключевое слово AS. До ключевого слова AS идет заголовок,
а после
него - течо
процедуры.
Тело хранимой процедуры представляет собой
перечень описаний ее внутренних (локальных) переменных (если они есть, подробнее
рассмотрим ниже), разделяемый точкой с запятой (;), и блок операторов,
заключенный в операторные скобки BEGIN END. В данном случае тело ХП очень
простое - мы просю складываем два входных аргумента и присваиваем их результат
выходному, а затем вызываем команду SUSPEND. Чуть позже мы разъясним суть
действия этой команды, а пока лишь отметим, что она нужна для передачи
возвращаемых параметров туда, откуда была вызвана хранимая процедура.
Разделители в хранимых процедурах
Обратите внимание, что оператор внутри процедуры заканчивается точкой с запятой (;). Как известно, точка с запятой является стандартным разделителем команд в SQL - она является сигналом интерпретатору SQL, что текст команды введен полностью и надо начинать его обрабатывать. Не получится ли так, что, обнаружив точку с запятой в середине ХП, интерпретатор SQL сочтет, что команда введена полностью и попытается выполнить часть хранимой процедуры? Это предположение не лишено смысла. Действительно, если создать файл, в который записать вышеприведенный пример, добавить команду соединения с базы данных и попытаться выполнить этот SQL-скрипт с помощью интерпретатора isql, то будет возвращена ошибка, связанная с неожиданным, по мнению интерпретатора, окончанием команды создания хранимой процедуры. Если создавать хранимые процедуры с помощью файлов SQL-скриптов, без использования специализированных инструментов разработчика InterBase, то необходимо перед каждой командой создания ХП (то же относи 1ся и к триггерам) менять разделитель команд скрипта на другой символ, отличный от точки с запятой, а после текста ХП восстанавливать его обратно. Команда isql, изменяющая разделитель предложений SQL, выглядит так:
SET TERM
Для типичного случая создания хранимой процедуры это выглядит так:
SET TERM ^;
CREATE
PROCEDURE some_procedure
... . .
END
^
SET TERM ;^
Вызов хранимой процедуры
Но вернемся к нашей хранимой процедуре. Теперь, когда она создана, ее надо как-то вызвать, передать ей параметры и получить возвращаемые результаты. Это сделать очень просто - достаточно написать SQL-запрос следующего вида:
SELECT *
FROM
Sp_add(181.35, 23.09)
Этот запрос вернет нам одну строку,
содержащую всего одно поле Result, в котором будет находиться сумма чисел 181.35
и 23.09 т. е. 204.44.
Таким образом, нашу процедуру можно использовать в
обычных SQL- запросах, выполняющихся как в клиентских программах, так и в других
ХП или триггерах. Такое использование нашей процедуры стало возможным из-за
применения команды SUSPEND в конце хранимой процедуры.
Дело в том, что в
InterBase (и во всех его клонах) существуют два типа хранимых процедур:
процедуры-выборки (selectable procedures) и исполняемые процедуры (executable
procedures). Отличие в работе этих двух видов ХП заключается в том, что
процедуры-выборки обычно возвращают множество наборов выходных параметров,
сгруппированных построчно, которые имеют вид набора данных, а исполняемые
процедуры мог)т либо вообще не возвращать параметры, либо возвращать только один
набор выходных параметров, перечисленных в Returns, где одну строку параметров.
Процедуры-выборки вызываются в запросах SELECT, а исполняемые процедуры - с
помощью команды EXECUTE PROCEDURE.
Оба вида хранимых процедур имеют
одинаковый синтаксис создания и формально ничем не отличаются, поэтому любая
исполнимая процедура может быть вызвана в SELECT-запросе и любая
процедура-выборка - с помощью EXECUTE PROCEDURE. Вопрос в том, как поведут себя
ХП при разных типах вызова. Другими словами, разница заключается в
проектировании процедуры для определенного типа вызова. То есть
процедура-выборка специально создается для вызова из запроса SELECT, а
исполняемая процедура - для вызова с использованием EXECUTE PROCEDURE. Давайте
рассмотрим, в чем же заключаются отличия при проектировании этих двух видов ХП.
Для того чтобы понять, как работает процедура-выборка, придется немного
углубиться в теорию. Давайте представим себе обычный SQL-запрос вида SELECT ID,
NAME FROM Table_example. В результате его выполнения мы получаем на выходе
таблицу, состоящую из двух столбцов (ID и NAME) и некоторого количества строк
(равного количеству строк в таблице Table_example). Возвращаемая в результате
этого запроса таблица называется также набором данных SQL Задумаемся же, как
формируется набор данных во время выполнения этого запроса Сервер, получив
запрос, определяет, к каким таблицам он относится, затем выясняет, какое
подмножество записей из этих таблиц необходимо включить в результат запроса.
Далее сервер считывает каждую запись, удовлетворяющую результатам запроса,
выбирает из нее нужные поля (в нашем случае это ID и NAME) и отсылает их
клиенту. Затем процесс повторяется снова - и так для каждой отобранной записи.
Все это отступление нужно для того, чтобы уважаемый читатель понял, что все
наборы данных SQL формируются построчно, в том числе и в хранимых процедурах! И
основное отличие процедур-выборок от исполняемых процедур в том, что первые
спроектированы для возвращения множества строк, а вторые - только для одной.
Поэтому они и применяются по-разному: процедура-выборка вызывается при помощи
команды SELECT, которая "требует" от процедуры отдать все записи, которая она
может вернуть. Исполняемая процедура вызывается с помощью EXECUTE PROCEDURE,
которая "вынимает" из ХП только одну строку, а остальные (даже если они есть!)
игнорирует.
Давайте рассмотрим пример процедуры-выборки, чтобы было
понятнее. Для > прощения создадим хранимую процедуру, которая работает точно
так же, как запрос SELECT ID, NAME FROM Table_Example, т е она просто делает
выборку полей ID и NAME из всей таблицы. Вот этот пример:
CREATE PROCEDURE Simple_Select_SP
RETURNS (
procID INTEGER,
procNAME VARCHAR(80))
AS
BEGIN
FOR
SELECT ID, NAME FROM
table_example
INTO:procID, :procNAME
DO
BEGIN
SUSPEND;
END
END
Давайте разберем действия этой процедуры, названной Simple_Select_SP. Как видите, она не имеет входных параметров и имеет два выходных параметра - ID и NAME. Самое интересное, конечно, заключено в теле процедуры. Здесь использована конструкция FOR SELECT:
FOR
SELECT ID, NAME
FROM table_example
INTO:procID,
:procNAME
DO
BEGIN
/*что-то делаем с переменными procID и procName*/
END
Этот кусочек кода
означает следующее: для каждой строки, выбранной из таблицы Table_example,
поместить выбранные значения в переменные procID и procName, а затем произвести
какие-то действия с этими переменными.
Вы можете сделать удивленное лицо и
спросить: "Переменные? Какие еще переменные 9 " Это нечто вроде
сюрприза этой главы - то, что в хранимых процедурах мы можем использовать
переменные. В языке ХП можно объявлять как собственные локальные переменные
внутри процедуры, так и использовать входные и выходные параметры в качестве
переменных.
Для того чтобы объявить локальную переменную в хранимой
процедуре, необходимо поместить ее описание после ключевого слова AS и до
первого слова BEGIN Описание локальной переменной выглядит так:
DECLARE VARIABLE
Например, чтобы объявить целочисленную локальную переменную Mylnt, нужно вставить между AS и BEGIN следующее описание
DECLARE VARIABLE Mylnt INTEGER;
Переменные в нашем примере начинаются с двоеточия. Это сделано потому,
что обращение к ним идет внутри SQL-команды FOR SELECT, поэтому для различения
полей в таблицах, которые используются в SELECT, и переменных необходимо
предварять последние двоеточием. Ведь переменные могут иметь точно такое же
название, как и поля в таблицах!
Но двоеточие перед именем переменной
необходимо использовать только внутри SQL-запросов. Вне текстов обращение к
переменной делается без двоеточия, например:
procName="Some name";
Но вернемся к телу нашей
процедуры. Предложение FOR SELECT возвращает данные не в виде таблицы - набора
данных, а по одной строчке. Каждое возвращаемое поле должно быть помещено в свою
переменную: ID => procID, NAME => procName. В части DO эти переменные
посылаются клиенту, вызвавшем) процед>р>, с помощью команды SUSPEND
Таким образом, команда FOR SELECT... DO организует цикл по записям,
выбираемым в части SELECT этой команды. В теле цикла, образуемого частью DO,
выполняется передача очередной сформированной записи клиенту с помощью команды
SUSPEND.
Итак, процедура-выборка предназначена для возвращения одной или
более строк, для чего внутри тела ХП организуется цикл, заполняющий
результирующие параметры-переменные. И в конце тела этого цикла обязательно
стоит команда SUSPEND, которая вернет очередную строку данных клиенту.
Циклы и операторы ветвления
Помимо команды FOR SELECT... DO, организующей цикл по записям какой-либо выборки, существует другой вид цикла - WHILE...DO, который позволяет организовать цикл на основе проверки любых условий. Вот пример ХП, использующей цикл WHILE.. DO. Эта процедура возвращает квадраты целых чисел от 0 до 99:
CREATE PROCEDJRE QUAD
RETURNS (QUADRAT INTEGER)
AS
DECLARE VARIABLE I
INTEGER;
BEGIN
I
= 1;
WHILE (i<100) DO
BEGIN
QUADRAT= I*I;
I=I+1;
SUSPEND;
END
END
В
результате выполнения запроса SELECT FROM QUAD мы получим таблицу, содержащую
один столбец QUADRAT, в котором будут квадраты целых чисел от 1 до 99
Помимо
перебора результатов SQL-выборки и классического цикла, в языке хранимых
процедур используется оператор IF...THEN..ELSE, позволяющий организовать
ветвление в зависимости от выполнения каких-либо \словий Его синтаксис похож на
большинство операторов ветвления в языках программирования высокого уровня,
вроде Паскаля и Си.
Давайте рассмотрим более сложный пример хранимой
процедуры, которая делает следующее.
- Вычисляет среднюю цену в таблице Table_example (см. глава "Таблицы Первичные ключи и генераторы")
- Далее для каждой записи в таблице делает след>ющ)ю проверку, если существующая цена (PRICE) больше средней цены, то устанавливает цену, равную величине средней цены, плюс задаваемый фиксированный процент
- Если существующая цена меньше или равна средней цене, то устанавливает цену, равную прежней цене, плюс половина разницы между прежней и средней ценой.
- Возвращает все измененные строки в таблице.
Для начала определим имя ХП, а также входные и выходные параметры Все это прописывается в заголовке хранимой процедуры
CREATE PROCEDURE IncreasePrices (
Percent2lncrease DOUBLE PRECISION)
RETURNS (ID INTEGER, NAME VARCHAR(SO), new_price DOUBLE
PRECISION) AS
Процедура будет называться
IncreasePrices, у нее один входной параметр Peiceni21nciease, имеющий тип DOUBLE
PRECISION, и 3 выходных параметра - ID, NAME и new_pnce. Обратите внимание, что
первые два выходных параметра имеют такие же имена, как и поля в таблице
Table_example, с которой мы собираемся работать Это допускается правилами языка
хранимых процедур.
Теперь мы должны объявить локальную переменную, которая
будет использоваться для хранения среднего значения Эго объявление будет
выглядеть следующим образом:
DECLARE VARIABLE avg_price DOUBLE PRECISION;
Теперь перейдем к телу хранимой
процедуры Откроем тело ХП
ключевым словом BEGIN.
Сначала нам
необходимо выполнить первый шаг нашего алгоритма - вычислить среднюю цену. Для
этого мы воспользуемся запросом следующего вида:
SELECT AVG(Price_l)
FROM
Table_Example
INTO:avg_price,-
Этот запрос использует агрегатную функцию AVG, которая возвращает
среднее значение поля PRICE_1 среди отобранных строк запроса - в нашем случае
среднее значение PRICE_1 по всей таблице Table_example. Возвращаемое запросом
значение помещается в переменную avg_price. Обратите внимание, что переменная
avg_pnce предваряется двоеточием -для того, чтобы отличить ее от полей,
используемых в запросе.
Особенностью данного запроса является то, что он
всегда возвращает строго одну-единственную запись. Такие запросы называются
singleton-запросами И только такие выборки можно использовать в хранимых
процедурах. Если запрос возвращает более одной строки, то его необходимо
оформить в виде конструкции FOR SELECT...DO, которая организует цикл для
обработки каждой возвращаемой строки
Итак, мы получили среднее значение
цены. Теперь необходимо пройтись по всей таблице, сравнить значение цены в
каждой записи со средней ценой и предпринять соответствующие действия
С
начала opганизуем перебор каждой записи из таблицы Table_example
FOR
SELECT ID, NAME,
PRICE_1
FROM Table_Example
INTO:ID, :NAME, :new_price
DO
BEGIN
/*_здесь оОрсшатыьаем каждую запись*/
END
При выполнении этой конструкции из таблицы
Table_example построчно будут выниматься данные и значения полей в каждой строке
будут присвоены переменным ID, NAME и new_pnce. Вы, конечно, помните, что эти
переменные объявлены как выходные параметры, но беспокоиться, что выбранные
данные будут возвращены как результаты, не стоит: тот факт, что выходным
параметрам что-либо присвоено, не означает, что вызывающий ХП клиент немедленно
получит эти значения! Передача параметров осуществляется только при исполнении
команды SUSPEND, а до этого мы можем использовать выходные параметры в качестве
обычных переменных - в нашем примере мы именно так и делаем с параметром
new_price.
Итак, внутри тела цикла BEGIN.. .END мы можем обработать значения
каждой строки. Как вы помните, нам необходимо выяснить, как существующая цена
соотносится со средней, и предпринять соответствующие действия. Эту процедуру
сравнения мы реализуем с помощью оператора IF:
IF
(new_price > avg_price) THEN /*если существующая цена больше средней
цены*/
BEGIN
/*то
установим новую цену, равную величине средней цены, плюс фиксированный процент
*/
new_price = (avg_price +
avg_price*(Percent2Increase/100));
UPDATE
Table_example
SET PRICE_1 = :new_price
WHERE ID = :ID;
END
ELSE
BEGIN
/* Если существующая цена
меньше или равна средней цене, то установим цену, равную прежней цене, плюс
половина разницы между прежней и средней ценой */
new_price = (new_pnce + ((avg_pnce new_price)/2)) ;
UPDATE Table_example
SET
PRICE_1 = :new_price
WHERE ID = .ID;
END
Как видите, получилось достаточно
большая конструкция IF, в которой трудно было бы разобраться, если бы не
комментарии, заключенные в символы /**/.
Для того чтобы изменить цену в
соответствии с вычисленной разницей, мы воспользуемся оператором UPDATE, который
позволяет модифицировать существующие записи - одну или несколько. Для того
чтобы однозначно указать, в какой записи нужно изменять цену, мы используем в
условии WHERE поле первичного ключа, сравнивая его со значением переменной, в
которой хранится значение ID для текущей записи: ID=:ID. Обратите внимание, что
переменная ID предваряется двоеточием.
После выполнения конструкции
IF...THEN...ELSE в переменных ID, NAME и new_price находятся данные, которые мы
должны возвратить клиент\, вызвавшему процедуру. Для этого после IF необходимо
вставить команду SUSPEND, которая перешлет данные туда, откуда вызвали ХП На
время пересылки действие процедуры будет приостановлено, а когда от ХП
потребуется новая запись, то она будет вновь продолжена, - и так будет
продолжаться до тех пор, пока FOR SELECT...DO не переберет все записи своего
запроса.
Надо отметить, что помимо команды SUSPEND, которая только
приостанавливает действие хранимой процедуры, существует команда EXIT, которая
прекращает хранимую процедуру после передачи строки. Однако командой EXIT
пользуются достаточно редко, поскольку она нужна в основном для того, чтобы
прервать цикл при достижении какого-либо условия
При этом в случае, когда
процедура вызывалась оператором SELECT и завершена по EXIT, последняя
извлеченная строка не будет возвращена. То есть, если вам нужно прервать
процедуру и все-таки >получить эту строку, надо воспользоваться
последовательностью
SUSPEND;
EXIT;
Основное назначение EXIT - получение
singleton-наборов данных, возвращаемых параметров путем вызова через EXECUTE
PROCEDURE. В этом случае устанавливаются значения выходных параметров, но из них
не формируется набор данных SQL, и выполнение процедуры завершается.
Давайте
запишем текст нашей хранимой процедуры полностью, чтобы иметь возможность
охватить ее логику одним взглядом:
CREATE PROCEDURE
IncreasePrices (
Percent2Increase DOUBLE
PRECISION)
RETURNS (ID INTEGER, NAME
VARCHAR(80),
new_price DOUBLE PRECISION)
AS
DECLARE VARIABLE avg_price DOUBLE
PRECISION;
BEGIN
SELECT AVG(Price_l)
FROM
Table_Example
INTO:avg_price;
FOR
SELECT ID, NAME,
PRICE_1
FROM Table_Example
INTO:ID, :NAME, :new_price
DO
BEGIN
/*здесь обрабатываем каждую запись*/
IF (new_pnce > avg_price) THEN /*если существующая цена больше
средней цены*/
BEGIN
/*установим новую цену, равную величине средней цены, плюс
фиксированный процент */
new_price = (avg_price +
avg_price*(Percent2lncrease/100));
UPDATE
Table_example
SET PRICE_1 = :new_price
WHERE ID = :ID;
END
ELSE
BEGIN
/* Если существующая цена
меньше или равна средней цене, то устанавливает цену, равную прежней цене, плюс
половина разницы между прежней и средней ценой */
new_price = (new_price + ((avg_price - new_price)/2));
UPDATE Table_example
SET
PRICE_1 = :new_price
WHERE ID = :ID;
END
SUSPEND;
END
END
Данный пример хранимой процедуры иллюстрирует применение основных конструкций языка хранимых процедур и триггеров. Далее мы рассмотрим способы применения хранимых процедур для решения некоторых часто возникающих задач.
Рекурсивные хранимые процедуры
Хранимые процедуры InterBase могут быть рекурсивными. Это
означает, что из хранимой процедуры можно вызвать саму себя. Допускается до 1000
уровней вложенности хранимых процедур, однако надо помнить о том, что свободные
ресурсы на сервере могут закончиться раньше, чем будет достигнута максимальная
вложенность ХП.
Одно из распространенных применений хранимых процедур - это
обработка древовидных структур, хранящихся в базе данных. Деревья часто
используются в задачах состава изделия, складских, кадровых и в других
распространенных приложениях.
Давайте рассмотрим пример хранимой процедуры,
которая выбирает все товары определенного типа, начиная с определенного уровня
вложенности.
Пусть у нас есть следующая постановка задачи: имеем справочник
товаров с иерархической структурой такого вида:
Товары
- Бытовая техника
- Холодильники
-
Трехкамерные
- Двухкамерные
- Однокамерные
-
Стиральные машины
- Вертикальные
- Фронтальные
- Классические
- Узкие
- Компьютерная техника
....
Эта структура справочника категорий товаров может иметь ветки различной глубины. а также нарастать со временем. Наша задача - обеспечить выборку всех конечных элементов из справочника с "разворачивание полного имени", начиная с любого узла. Например, если мы выбираем узел "Стиральные машины", то нам надо получить следующие категории:
Стиральные машины - Вертикальные
Стиральные машины - Фронтальные Классические
Стиральные машины - Фронтальные Узкие
Определим структуру таблиц для хранения информации справочника товаров. Используем упрощенную схему для организации дерева в одной таблице:
CREATE TABLE GoodsTree
(ID_GOOD
INTEGER NOT NULL,
ID_PARENT_GOOD INTEGER,
GOOD_NAME VARCHAR(80),
constraint pkGooci primary key (ID_GOOD));
Создаем одну таблицу GoodsTree, в которой всего 3 поля: ID_GOOD - умн кальный идентификатор категории, ID_PARENT_GOOD - идентификатор кшс гории-родителя для данной категории и GOOD_NAME - наименование катсш- рии. Чтобы обеспечить целостность данных в этой таблице, наложим на эту таблиц} ограничение внешнего ключа:
ALTER TABLE GoodsTree
ADD CONSTRAINT FK_goodstree
FOREIGN KEY (ID_PARENT_GOOD)
REFERENCES GOODSTPEE (ID__GOOD)
Таблица ссылается
сама на себя и данный внешний ключ следит за тем. чтобы в таблице не было ссылок
на несуществующих родителей, а также препятствует попыткам удалить категории
товаров, у которых есть потомки.
Давайте занесем в нашу таблицу следующие
данные:
| ID_GOOD
1
|
ID_PARENT_GOOD
0
|
GOOD_NAME
GOODS
|
Теперь, когда у нас есть место для хранения данных, мы можем
приступить к созданию хранимой процедуры, выполняющей вывод всех "окончательных"
категорий товаров в "развернутом" виде - например, для категории "Трехкамерные"
полное имя категории будет выглядеть как "Бытовая техника Холодильники
Трехкамерные".
В хранимых процедурах, обрабатывающих древообразные
структуры, сложилась своя терминология. Каждый элемент дерева называются узлом;
а отношения между ссылающимися друг на друга узлами называется отношениями
родитель-потомок. Узлы, находящиеся на самом конце дерева и не имеющие потомков,
называются "листьями".
У кашей хранимой процедуры входным параметром будет
идентификатор категории, начиная с которого мы должны будем начать развертку.
Хранимая процедура будет иметь следующий вид:
CREATE
PROCEDURE GETFULLNAME (ID_GOOD2SHOW INTEGER)
RETURNS (FULL_GOODS_NAME VARCHAR(1000),
ID_CHILD_GOOD INTEGER)
AS
DECLARE VARIABLE CURR_CHILD_NAME VARCHAR(80);
BEGIN
/*0рганизуем
внешний цикл FOR SELECT по непосредственным потомкам товара с
ID_GOOD=ID_GOOD2SHOW */
FOR SELECT gtl.id_good,
gtl.good_name
FROM GoodsTree gtl
WHERE gtl.id_parent_good=:ID_good2show
INTO:ID_CHILD_GOOD, :full_goods_name
DO
BEGIN
/"Проверка с помощью функции EXISTS, которая возвращает TRUE, если
запрос в скобках вернет хотя бы одну строку. Если у найденного узла с
ID_PARENT_GOOD = ID_CHILD_GOOD нет потомков, то он является "листом" дерева и
попадает в результаты */
IF (NOT EXISTS(
SELECT * FROM GoodsTree
WHERE GoodsTree.id_parent_good=:id_child_good))
THEN
BEGIN
/* Передаем "лист" дерева в результаты */
SUSPEND;
END
ELSE
/* Для узлов, у которых есть
потомки*/
BEGIN
/*сохраняем имя узла-родителя во временной переменной */
CURR_CHILD_NAME=full_goods_name;
/* рекурсивно запускаем эту процедуру */
FOR
SELECT ID_CHILD_GOOD,
full_goods_name
FROM GETFULLNAME
(:ID_CHILD_GOOD)
INTO:ID_CHILD_GOOD,
:full_goods_name
DO BEGIN
/*добавляем лмя узла-родителя к найденном., имени потомка с
помощью операции конкатенации строк || */
full_goods_name=CURR_CHILD_NAME| " " | f ull_goods_name,-
SUSPEND; /* возвращаем полное имя товара*/
END
END
END
END
Если мы выполним данную процедуру с входным параметром ID_GOOD2SHOW= 1, то получим следующее:
Как видите, с помощью рекурсивной хранимой процедуры мы прошлись по всему дереву категорий и вывели полное наименование категорий-"листьев", которые находятся на самых кончиках ветвей.
Заключение
На этом закончим рассмотрение основных возможностей языка хранимых
процедур. Очевидно, что полностью освоить разработку хранимых процедур при
чтении одной главы невозможно, однако здесь мы постарались представить и
объяснить основные концепции, связанные с хранимыми процедурами. Описанные
конструкции и приемы проектирования ХП могут быть применены в большинстве
приложений баз данных
Часть важных вопросов, связанных с разработкой
хранимых процедур, будет раскрыта в следующей главе - "Расширенные возможности
языка хранимых процедур InterBase", которая посвящена обработке исключений,
разрешению ошибочных ситуаций в хранимых процедурах и работе с массивами.