1. Включай в свои процедуры строку - SET NOCOUNT ON: С каждым DML выражением, SQL server заботливо возвращает нам сообщение содержащее колличество обработанных записей. Данная информация может быть нам полезна во время отладки кода, но после будет совершенно бесполезной. Прописывая SET NOCOUNT ON, мы отключаем эту функцию. Для хранимых процедур содержащих несколько выражений или\и циклы данное действие может дать значительный прирост производительности, потому как колличество трафика будет значительно снижено.
CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
--Здесь код процедуры
SELECT column1 FROM dbo.TblTable1
--Перключение SET NOCOUNT в исходное состояние
SET NOCOUNT OFF;
GO
2. Используй имя схемы с именем объекта: Ну тут думаю понятно. Данная операция подсказывает серверу где искать объекты и вместо того чтобы беспорядочно шарится по своим закромам, он сразу будет знать куда ему нужно пойти и что взять. При большом колличестве баз, таблиц и хранимых процедур может значительно сэкономить наше время и нервы.
SELECT * FROM dbo.MyTable --Вот так делать хорошо
-- Вместо
SELECT * FROM MyTable --А так делать плохо
--Вызов процедуры
EXEC dbo.MyProc --Опять же хорошо
--Вместо
EXEC MyProc --Плохо!
3. Не используй префикс «sp_» в имени своих хранимых процедур: Если имя нашей процедуры начинается с «sp_», SQL Server в первую очередь будет искать в своей главной базе данных. Дело в том, что данный префикс используется для личных внутренних хранимых процедур сервера. Поэтому его использование может привести к дополнительным расходам и даже неверному результату, если процедура с таким же имененем как у вас будет найдена в его базе.
4. Используй IF EXISTS (SELECT 1) вместо IF EXISTS (SELECT *): Чтобы проверить наличие записи в другой таблице, мы используем выражение IF EXISTS. Данное выражение возвращает true если из внутреннего выражения возвращается хоть одно изначение, не важно «1», все колонки или таблица. Возращаемые данные, в принципе никак не используются. Таким образом для сжатия трафика во время передачи данных логичнее использовать «1», как показано ниже:
IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = "MyTable" AND type = "U")
5. Используй TRY-Catch для отлова ошибок: До 2005 сервера после каждого запроса в процедуре писалось огромное колличество проверок на ошибки. Больше кода всегда потребляет больше ресурсов и больше времени. С 2005 SQL Server"ом появился более правильный и удобный способ решения этой проблемы:
BEGIN TRY
--код
END TRY
BEGIN CATCH
--код отлова ошибки
END CATCH
Заключение
В принципе на сегодня у меня всё. Еще раз повторюсь, что здесь лишь те приёмы, которые использовал лично я в своей практике, и могу ручаться за их эффективность.
P.S.
Мой первый пост, не судите строго.
Когда следует использовать хранимые процедуры и когда я должен использовать представления в SQL Server?
Разрешения позволяют создавать динамические запросы, где мы можем передавать параметры?
Какой из них самый быстрый, и на каком основании он быстрее, чем другой?
Просмотры или хранимые процедуры постоянно сохраняют память?
Что это значит, если кто-то скажет, что представления создают виртуальную таблицу, а процедуры создают таблицу материалов?
Пожалуйста, дайте мне знать о более точках, если они есть.
Solutions Collecting From Web of "В чем разница между хранимой процедурой и представлением?"
Вид представляет собой виртуальную таблицу. Вы можете присоединиться к нескольким таблицам в представлении и использовать представление для представления данных, как если бы данные поступали из одной таблицы.
Хранимая процедура использует параметры для выполнения функции … будь то обновление и вставка данных или возврат отдельных значений или наборов данных.
Создание представлений и хранимых процедур – содержит некоторую информацию от Microsoft о том, когда и почему использовать их.
Скажем, у меня есть две таблицы:
tbl_user Столбцы: .user_id, .user_name, .user_pw
tbl_profile Столбцы: .profile_id, .user_id .profile_description
Поэтому, если я нахожусь в запросе из этих таблиц ALOT … вместо того, чтобы делать соединение в КАЖДОЙ peice sql, я бы определил вид, например:
CREATE View vw_user_profile AS Select A.user_id, B.profile_description FROM tbl_user A left join tbl_profile B on A.user_id = b.user_id GO
Поэтому в будущем, если я хочу запросить profile_description по идентификатору пользователя … все, что мне нужно сделать, это
SELECT profile_description FROM vw_user_profile WHERE user_id = @ID
Этот код можно использовать в хранимой процедуре, например:
Create procedure dbo.getDesc @ID int AS begin SELECT profile_description FROM vw_user_profile WHERE user_id = @ID END GO
Поэтому позже я могу позвонить
Dbo.getDesc 25
и я получу описание для идентификатора пользователя 25, где 25 – ваш параметр.
Очевидно, что МНОГО больше, но это всего лишь основная идея.
Сначала вам нужно понять, что оба – разные вещи. Хранимые процедуры лучше всего использовать для операторов INSERT-UPDATE-DELETE. и Представления используются для операторов SELECT. и вы должны использовать оба.
В представлениях вы не можете изменять данные.
Просмотры: Это виртуальная таблица, состоящая из одной или нескольких строк и столбцов из разных реальных таблиц базы данных. Это шаблон строк и столбцов нескольких таблиц. Вы не можете передавать какие-либо параметры здесь.
Хранимые процедуры: они представляют собой набор предварительно выполненных SQL-заявлений, в которых вы можете отправлять параметры в качестве входных данных и получать выходные данные.
Представления могут использоваться в Хранимой процедуре, но Хранимая процедура не может использоваться в Views …!
Процедура хранилища используется, когда простого SQL просто недостаточно. Процедуры хранения содержат переменные, циклы и вызовы других хранимых процедур. Это язык программирования, а не язык запросов.
Представления являются статическими. Подумайте о них как о новых таблицах с определенным макетом, а данные в них создаются «на лету», используя запрос, с которым вы его создали. Как и в любой таблице SQL, вы можете сортировать и фильтровать ее с помощью WHERE , GROUP BY и ORDER BY .
Это зависит от того, что вы делаете.
Это зависит от базы данных. Простые представления просто запускают запрос и фильтруют результат. Но такие базы данных, как Oracle, позволяют создать «материализованное» представление, которое в основном представляет собой таблицу, которая автоматически обновляется при изменении базовых данных вида.
Материализованное представление позволяет создавать индексы в столбцах представления (особенно на вычисленных столбцах, которые не существуют нигде в базе данных).
Я не понимаю, о чем вы говорите.
Основное различие заключается в том, что когда вы запрашиваете представление, это определение вставляется в ваш запрос. Процедура также может давать результаты запроса, но она скомпилирована и так быстро. Другим вариантом являются индексированные представления.
SQL View – это виртуальная таблица, основанная на запросе SQL SELECT. Представление ссылается на одну или несколько существующих таблиц базы данных или другие представления. Это мгновенный снимок базы данных, тогда как хранимая процедура представляет собой группу операторов Transact-SQL, составленную в единый план выполнения.
Просмотр – простая демонстрация данных, хранящихся в таблицах базы данных, тогда как хранимая процедура представляет собой группу операторов, которые могут быть выполнены.
Представление быстрее, поскольку оно отображает данные из таблиц, на которые ссылается, тогда как процедура хранилища выполняет sql-инструкции.
Проверьте эту статью: Просмотр против хранимых процедур. Именно то, что вы ищете
@Patrick правильно с тем, что он сказал, но, чтобы ответить на ваши другие вопросы, View создаст себя в памяти, и в зависимости от типа Joins, Data и если будет сделано какое-либо агрегирование, это может быть довольно голодный вид.
Хранимые процедуры выполняют всю свою обработку либо с использованием Temp Hash Table, например, # tmpTable1, либо в памяти с помощью @ tmpTable1. В зависимости от того, что вы хотите сказать.
Хранимая процедура похожа на функцию, но называется ее прямым именем. вместо функций, которые фактически используются внутри самого запроса.
Очевидно, большую часть времени таблицы памяти быстрее, если вы не извлекаете много данных.
Махеш не совсем прав, когда он предполагает, что вы не можете изменять данные в представлении. Итак, с точки зрения Патрика
CREATE View vw_user_profile AS Select A.user_id, B.profile_description FROM tbl_user A left join tbl_profile B on A.user_id = b.user_id
Я могу обновить данные … в качестве примера я могу сделать любой из этих …
Update vw_user_profile Set profile_description="Manager" where user_id=4
Update tbl_profile Set profile_description="Manager" where user_id=4
Вы не можете ВСТАВИТЬ в это представление, так как не все поля во всей таблице присутствуют, и я предполагаю, что PROFILE_ID является первичным ключом и не может быть NULL. Однако иногда вы можете вставить INSERT в представление …
Я создал представление для существующей таблицы, используя …
Create View Junk as SELECT * from
Insert into junk (Code,name) values ("glyn","Glyn Roberts"), ("Mary","Maryann Roberts")
DELETE from Junk Where ID>4
И INSERT, и DELETE работали в этом случае
Очевидно, вы не можете обновлять какие-либо поля, которые агрегированы или рассчитаны, но любое представление, которое является просто прямым представлением, должно быть обновляемым.
Если представление содержит более одной таблицы, вы не можете вставлять или удалять, но если представление является подмножеством одной таблицы, то вы обычно можете.
В дополнение к приведенным выше комментариям я хотел бы добавить несколько замечаний о Views.
- Представления могут использоваться для скрытия сложности. Представьте себе сценарий, в котором 5 человек работают над проектом, но только один из них слишком хорош с базой данных, например сложными объединениями. В таком сценарии он может создавать виды, которые могут быть легко запрошены другими членами команды, поскольку они запрашивают какую-либо одну таблицу.
- Безопасность может быть легко реализована Views. Предположим, что мы сотрудник таблицы, который содержит чувствительные столбцы, такие как Зарплата , номер SSN . Эти столбцы не должны отображаться для пользователей, которым не разрешено их просматривать. В этом случае мы можем создать представление, которое будет выбирать столбцы в таблице, не требующие авторизации, такие как имя , возраст и т. Д., Не подвергая уязвимые столбцы (например, о зарплате и т. Д., О которых мы упоминали ранее). Теперь мы можем удалить разрешение для прямого запроса к таблице Employee и просто сохранить разрешение на чтение в представлении. Таким образом, мы можем реализовать безопасность с помощью Views.
При работе с 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", которая посвящена обработке исключений,
разрешению ошибочных ситуаций в хранимых процедурах и работе с массивами.
Объявление процедуры
CREATE PROCEDURE [({IN|OUT|INOUT } [,…])]
[DYNAMIC RESULT SET ]
BEGIN [ATOMIC ]
END
Ключевые слова
. IN (Input) – входной параметр
. OUT (Output) – выходной параметр
. INOUT – входной и выходной, а также поле (без параметров)
. DYNAMIC RESULT SET показывает, что процедура может открыть указанное число курсоров, которые останутся открытыми после возврата из процедуры
Примечания
Не рекомендуется использовать много параметров в хранимых процедурах (в первую очередь больших чисел и символьных строк) из-за перегрузки сети и стека. На практике в существующих диалектах Transact-SQL, PL/SQL и Informix наблюдается существенное отличие от стандарта, как в объявлении и использовании параметров, объявлении переменных, так и в вызове подпрограмм. Microsoft рекомендует применять следующую аппроксимацию для оценки размера КЭШа хранимых процедур:
=(максимальное количество одновременно работающих пользователей)*(размер самого большого плана выполнения)*1.25. Определение размера плана выполнения в страницах можно сделать с помощью команды: DBCC MEMUSAGE.
Вызов процедуры
Во многих существующих СУБД вызов хранимых процедур выполняется с помощью оператора:
EXECUTE PROCEDURE [(][)]
Примечание : вызов хранимых процедур может быть сделан из приложения, другой хранимой процедуры или в интерактивном режиме.
Пример объявления процедуры
CREATE PROCEDURE Proc1 AS //объявляем процедуру
DECLARE Cur1 CURSOR FOR SELECT SName, City FROM SalesPeople WHERE Rating>200 //объявляем курсор
OPEN Cur1 //открываем курсор
FETCH NEXT FROM Cur1 //считываем данные из курсора
WHILE @@Fetch_Status=0
BEGIN
FETCH NEXT FROM Cur1
END
CLOSE Cur1 //закрываем курсор
DEALLOCATE Cur1
EXECUTE Proc1 //запускаем процедуру
Полиморфизм
Две подпрограммы с одним и тем же именем могут быть созданы в одной и той же схеме, если параметры этих двух подпрограмм являются в такое мере отличными друг от друга, чтобы их можно было различать. Для того, чтобы различать две подпрограммы с одним и тем же именем в одной схеме, каждой из них дается альтернативное и уникальное имя (specific name). Такое имя может быть явно указано, когда определяется подпрограмма. При вызове подпрограмм при наличии нескольких одинаковых имен определение нужной подпрограммы осуществляется в несколько шагов:
. Первоначально определяются все процедуры с указанным именем, а если таковых нет, то все функции с заданным именем.
. Для дальнейшего анализа оставляются только те подпрограммы, по отношению к которым данный пользователь обладает привилегией на исполнение (EXECUTE).
. Для них отбираются те, у которых число параметров соответствует числу аргументов вызова. Проверяются указанные типы данных у параметров и их позиции.
. Если осталось более одной подпрограммы, то выбирается та, квалификационное имя которой короче.
На практике в Oracle полиморфизм поддерживается для функций, объявленных только в пакете, DB@ — в разных схема, а в Sybase и MS SQL Server перегрузка запрещена.
Удаление и изменение процедур
Для удаления процедуры используется оператор:
Для изменения процедуры используется оператор:
ALTER PROCEDURE [([{IN|OUT|INOUT }])]
BEGIN [ATOMIC ]
END
Привилегии на выполнение процедур
GRANT EXECUTE ON TO |PUBLIC [WITH GRANT OPTION ]
Системные процедуры
Многие СУБД (включая SQL Server) имеют определенный набор встроенных системных хранимых процедур, которые можно использовать в своих целях.
