Хранимая процедура (англ. 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, как того требует процедура. Параметру можно присвоить не только постоянное значение, но и значение другой переменной.
Использование хранимых функций СУБД для реализации бизнес-логики или её части, всегда было камнем преткновения. С одной стороны баррикад 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, или упрётся в проблемы масштабирования СУБД при использовании хранимых функций. Поэтому - принимайте верные решения:)
Знаете ли Вы, в чем ложность понятия "физический вакуум"?
Физический вакуум - понятие релятивистской квантовой физики, под ним там понимают низшее (основное) энергетическое состояние квантованного поля, обладающее нулевыми импульсом, моментом импульса и другими квантовыми числами. Физическим вакуумом релятивистские теоретики называют полностью лишённое вещества пространство, заполненное неизмеряемым, а значит, лишь воображаемым полем. Такое состояние по мнению релятивистов не является абсолютной пустотой, но пространством, заполненным некими фантомными (виртуальными) частицами. Релятивистская квантовая теория поля утверждает, что, в согласии с принципом неопределённости Гейзенберга, в физическом вакууме постоянно рождаются и исчезают виртуальные, то есть кажущиеся (кому кажущиеся?), частицы: происходят так называемые нулевые колебания полей. Виртуальные частицы физического вакуума, а следовательно, он сам, по определению не имеют системы отсчета, так как в противном случае нарушался бы принцип относительности Эйнштейна, на котором основывается теория относительности (то есть стала бы возможной абсолютная система измерения с отсчетом от частиц физического вакуума, что в свою очередь однозначно опровергло бы принцип относительности, на котором постороена СТО). Таким образом, физический вакуум и его частицы не есть элементы физического мира, но лишь элементы теории относительности, которые существуют не в реальном мире, но лишь в релятивистских формулах, нарушая при этом принцип причинности (возникают и исчезают беспричинно), принцип объективности (виртуальные частицы можно считать в зависимсоти от желания теоретика либо существующими, либо не существующими), принцип фактической измеримости (не наблюдаемы, не имеют своей ИСО).
Когда тот или иной физик использует понятие "физический вакуум", он либо не понимает абсурдности этого термина, либо лукавит, являясь скрытым или явным приверженцем релятивистской идеологии.
Понять абсурдность этого понятия легче всего обратившись к истокам его возникновения. Рождено оно было Полем Дираком в 1930-х, когда стало ясно, что отрицание эфира в чистом виде, как это делал великий математик, но посредственный физик Анри Пуанкаре , уже нельзя. Слишком много фактов противоречит этому.
Для защиты релятивизма Поль Дирак ввел афизическое и алогичное понятие отрицательной энергии, а затем и существование "моря" двух компенсирующих друг друга энергий в вакууме - положительной и отрицательной, а также "моря" компенсирующих друг друга частиц - виртуальных (то есть кажущихся) электронов и позитронов в вакууме.
При работе с SQL Server пользователи могут создавать собственные процедуры, реализующие те или иные действия. Хранимые процедуры являются полноценными объектами базы данных, а потому каждая из них хранится в конкретной базе данных. Непосредственный вызов хранимой процедуры возможен, только если он осуществляется в контексте той базы данных, где находится процедура.
Типы хранимых процедур
В SQL Server имеется несколько типов хранимых процедур.
Системные хранимые процедуры предназначены для выполнения различных административных действий. Практически все действия по администрированию сервера выполняются с их помощью. Можно сказать, что системные хранимые процедуры являются интерфейсом, обеспечивающим работу с системными таблицами, которая, в конечном счете, сводится к изменению, добавлению, удалению и выборке данных из системных таблиц как пользовательских, так и системных баз данных. Системные хранимые процедуры имеют префикс sp_, хранятся в системной базе данных и могут быть вызваны в контексте любой другой базы данных.
Пользовательские хранимые процедуры реализуют те или иные действия. Хранимые процедуры – полноценный объект базы данных. Вследствие этого каждая хранимая процедура располагается в конкретной базе данных, где и выполняется.
Временные хранимые процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Они делятся на локальные и глобальные. Локальные временные хранимые процедуры могут быть вызваны только из того соединения, в котором созданы. При создании такой процедуры ей необходимо дать имя, начинающееся с одного символа #. Как и все временные объекты, хранимые процедуры этого типа автоматически удаляются при отключении пользователя, перезапуске или остановке сервера. Глобальные временные хранимые процедуры доступны для любых соединений сервера, на котором имеется такая же процедура. Для ее определения достаточно дать ей имя, начинающееся с символов ##. Удаляются эти процедуры при перезапуске или остановке сервера, а также при закрытии соединения, в контексте которого они были созданы.
Триггеры
Триггеры являются одной из разновидностей хранимых процедур. Их исполнение происходит при выполнении для таблицы какого-либо оператора языка манипулирования данными (DML). Триггеры используются для проверки целостности данных, а также для отката транзакций.
Триггер – это откомпилированная SQL-процедура, исполнение которой обусловлено наступлением определенных событий внутри реляционной базы данных. Применение триггеров большей частью весьма удобно для пользователей базы данных. И все же их использование часто связано с дополнительными затратами ресурсов на операции ввода/вывода. В том случае, когда тех же результатов (с гораздо меньшими непроизводительными затратами ресурсов) можно добиться с помощью хранимых процедур или прикладных программ, применение триггеров нецелесообразно.
Триггеры – особый инструмент SQL-сервера, используемый для поддержания целостности данных в базе данных. С помощью ограничений целостности, правил и значений по умолчанию не всегда можно добиться нужного уровня функциональности. Часто требуется реализовать сложные алгоритмы проверки данных, гарантирующие их достоверность и реальность. Кроме того, иногда необходимо отслеживать изменения значений таблицы, чтобы нужным образом изменить связанные данные. Триггеры можно рассматривать как своего рода фильтры, вступающие в действие после выполнения всех операций в соответствии с правилами, стандартными значениями и т.д.
Триггер представляет собой специальный тип хранимых процедур, запускаемых сервером автоматически при попытке изменения данных в таблицах, с которыми триггеры связаны. Каждый Триггер привязывается к конкретной таблице. Все производимые им модификации данных рассматриваются как одна транзакция. В случае обнаружения ошибки или нарушения целостности данных происходит откат этой транзакции. Тем самым внесение изменений запрещается. Отменяются также все изменения, уже сделанные триггером.
Создает триггер только владелец базы данных. Это ограничение позволяет избежать случайного изменения структуры таблиц, способов связи с ними других объектов и т.п.
Триггер представляет собой весьма полезное и в то же время опасное средство. Так, при неправильной логике его работы можно легко уничтожить целую базу данных, поэтому триггеры необходимо очень тщательно отлаживать.
В отличие от обычной подпрограммы, триггер выполняется неявно в каждом случае возникновения триггерного события, к тому же он не имеет аргументов. Приведение его в действие иногда называют запуском триггера. С помощью триггеров достигаются следующие цели:
проверка корректности введенных данных и выполнение сложных ограничений целостности данных, которые трудно, если вообще возможно, поддерживать с помощью ограничений целостности, установленных для таблицы;
выдача предупреждений, напоминающих о необходимости выполнения некоторых действий при обновлении таблицы, реализованном определенным образом;
накопление аудиторской информации посредством фиксации сведений о внесенных изменениях и тех лицах, которые их выполнили;
поддержка репликации.
Основной формат команды CREATE TRIGGER показан ниже:
<Определение_триггера>::=
CREATE TRIGGER имя_триггера
BEFORE | AFTER <триггерное_событие>
ON <имя_таблицы>
<список_старых_или_новых_псевдонимов>]
<тело_триггера>
Триггерные события состоят из вставки, удаления и обновления строк в таблице. В последнем случае для триггерного события можно указать конкретные имена столбцов таблицы. Время запуска триггера определяется с помощью ключевых слов BEFORE (Триггер запускается до выполнения связанных с ним событий) или AFTER (после их выполнения).
Выполняемые триггером действия задаются для каждой строки (FOR EACH ROW), охваченной данным событием, или только один раз для каждого события (FOR EACH STATEMENT).
Неправильно написанные триггеры могут привести к серьезным проблемам, таким, например, как появление "мертвых" блокировок. Триггеры способны длительное время блокировать множество ресурсов, поэтому следует обратить особое внимание на сведение к минимуму конфликтов доступа.
Триггер может быть создан только в текущей базе данных, но допускается обращение внутри триггера к другим базам данных, в том числе и расположенным на удаленном сервере.
Хранимые процедуры
Предметом этой главы является один из наиболее мощных
инструментов, предлагаемых разработчикам приложений баз данных 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", которая посвящена обработке исключений,
разрешению ошибочных ситуаций в хранимых процедурах и работе с массивами.
