На одном из наших недавних проектов мы столкнулись с серьёзной проблемой. Веб-приложение, которое мы разрабатывали, должно было использовать внутренюю базу данных финансовой организации. Из соображений безопасности, доступ был очень сильно ограничен: любые изменения необходимо было делать при помощи хранимых процедур, а читать данные - только при помощи представлений. Таким образом, приложение должно было выполнять сложные манипуляции данными, не имея никакого представления об их структуре. Основной загвоздкой для нас было то, что наше приложение попадало в зависимость от больших и сложных процедур, для которых не существовало автоматизированных тестов.


Погуглив немного, мы обнаружили, что в штатном инструментарии Oracle SQL Developer есть функционал для создания автоматизированных тестов. Мы тут же приступили к его изучению. И хотя тесты для самой сложной процедуры пришлось создавать уже после её написания, этот инструментарий всё же помог нам устранить несколько ошибок, а также существенно облегчил процесс расширения функционала и рефакторинга. Ниже я приведу пример использования TDD для построения хранимых процедур, а также поделюсь опытом в работе с инструментарием.

Пример использования

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

Структура данных

Приложение использует следующую структуру данных:


CREATE TABLE CLIENTS(ID NUMBER GENERATED BY DEFAULT AS IDENTITY NOT NULL, NAME NVARCHAR2(255) NOT NULL, BALANCE NUMBER(*,2) DEFAULT 0 NOT NULL, IS_ACTIVE NUMBER(1) DEFAULT 0 NOT NULL, IS_PREPAY NUMBER(1) DEFAULT 0 NOT NULL); CREATE TABLE MESSAGE_QUEUE(ID NUMBER GENERATED BY DEFAULT AS IDENTITY NOT NULL, ID_CLIENT NUMBER NOT NULL, SENDER VARCHAR2(20), RECIPIENT VARCHAR(20), MESSAGE NVARCHAR2(255) NOT NULL, QUEUED_ON TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, SEND_ON TIMESTAMP WITH TIME ZONE NULL, SENT_ON TIMESTAMP WITH TIME ZONE NULL); CREATE TABLE TRANSACTIONS(ID NUMBER GENERATED BY DEFAULT AS IDENTITY NOT NULL, ID_CLIENT NUMBER NOT NULL, VALUE NUMBER(*,2) NOT NULL, TRANSACTION_TIME TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP);

Для краткости, определения первичных и внешних ключей опущены.

Настройка окружения

Юнит-тестирование в SQL Developer использует базу данных для хранения тестов, их настроек, библиотеки, и результатов выполнения. В этих целях настоятельно рекомендуется создать пользователя для тестирования, затем создать в его базе данных репозиторий. Этот процесс более подробно описан в документации по юнит-тестированию .

Терминология тестирования Oracle

Терминология тестирования, которую использует Oracle несколько отличается от общепринятой терминоголии xUnit :



Неожиданности

Работая с приложением, мы обнаружили, что оно не всегда работает так, как мы ожидали:

  • Иногда, все пункты меню юнит-тестирования оказывались отключенными. В таких случаях необходимо щёлкнуть пункт меню View→Unit Test
  • Все тесты внутри сценария используют общий набор настройки и сброса контекста, что вполне логично. Но из-за того, что редактируются они через вкладку теста, создаётся ощущение, что их можно персонализировать для каждого теста отдельно.

Разработка с помощью тестирования

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


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


CREATE OR REPLACE PROCEDURE QUEUE_MESSAGE(V_ID_CLIENT IN NUMBER, V_SENDER IN VARCHAR2, V_RECIPIENT IN VARCHAR2, V_MESSAGE IN NVARCHAR2, V_IS_QUEUED OUT NUMBER) AS BEGIN NULL; END QUEUE_MESSAGE;

В случае с Oracle, имеет смысл задавать префикс для переменных, имя которых может совпасть с названием поля, так как в случае неясности, знаменитая СУБД решит спор в пользу поля. А во избежании беспорядка, проще давать префикс всем переменным без исключения.


Примечание

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

Первый сценарий

Для упрощения нашего примера, допустим, что стоимость одного сообщения - 0.03 каких-то денег. И, как это ни странно, для описания сценария вполне подходит Gherkin:


Дано: Активный пост-оплатный клиент Когда: Он отправляет сообщение То: Возвращается положительный результат, И стоимость сообщения фиксируется в журнале транзакций, К тому же, сообщение добавляется в очередь.

Самый быстрый способ создать тест - щёлкнуть правой кнопкой мыши на процедуре в дереве объектов, затем выбрать пункт меню Create Unit Test... . В появившемся окне можно сразу нажать кнопку Finish . Сценарий QUEUE_MESSAGE с единственным тестом должен появиться в панели Unit Test .

Настройка контекста

Сначала нам необходимо будет заполнить базу необходмыми данными. Для нас самым удобным оказалось использование режима PL/SQL для настройки и сброса контекста. Тем не менее, любой из вариантов легко использовать повторно при помощи публикации в библиотеку. Чтобы скопировать существующий шаг из библиотеки, достаточно выбрать его из выпадающего списка, затем нажать кнопку Copy . А если нужно использовать его без изменений, но вместо кнопки Copy необходимо нажать чекбокс Subscribe .


Осторожно!

Идея использовать существующую БД для тестирования может показаться привлекательной. Казалось бы, сохранил данные в настройке, и восстановил при сбросе контекста… Однако следует иметь в виду, что если в процессе выполнения тестов на любом этапе произошла непредвиденная ошибка, то база данных окажется в том виде, в каком она была во время ошибки, и сброс контекста выполнен не будет. Поэтому лучше всего использовать чистую базу данных, которую не страшно и несложно полностью пересоздать в случае порчи структуры или данных.


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

Сброс контекста

Чтобы иметь возможность повторного прогона тестов, необходимо очистить добавленные данные. Однадко, в нашем случае, можно просто очистить все таблицы, затрагиваемые тестами. Этот шаг также нужно сохранить в библиотеку для дальнейшего использования.

Вызов

Непосредственно выполнение теста определяется при помощи задания параметров хранимой процедуры. Здесь же задаются и значения выходных параметров для проверки. Проверку выходных параметров можно отключить при помощи чекбокса Test Result . Он относится к параметрам, заданным как в таблице, так и динамически.


Осторожно!

С виду может показаться, что задавать параметры мышкой в таблице очень удобно, однако необходимо иметь в виду, что эта таблица копированию не подлежит. Это особенно важно для процедур с большим количеством аргументов, так как для создания очередного теста их все придётся заново задавать вручную, особенно когда новый тест отличается от текущего всего лишь на одно значение. Динамический запрос (Dynamic Value Query), в отличие от таблицы, можно сохранять в библиотеке, а затем можно либо повторно использовать, либо копировать.


Как указано выше, динамический запрос более удобен в использовании. Стоит также отметить, что название выходных параметров в запросе должно быть дополнено знаком $ в конце названия:


select 1 as V_ID_CLIENT, "79052222222" as V_SENDER, "79161111111" as V_RECIPIENT, "Айда гулять!" AS V_MESSAGE, 1 as V_IS_QUEUED$ from DUAL
Примечание

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


Так как мы указали проверку выходного параметра, то уже можно запустить сценарий, и увидеть сбой. Если всё сделано правильно, система должно сообщить об ошибке. Любой другой сбой на этом этапе означает некорректную настройку.


Самый простой способ успокоить тест - внаглую вписать 1 в выходной параметр в теле процедуры: SELECT 1 INTO IS_QUEUED FROM DUAL;

Утверждения

Тест снова зелёный, но мы ещё не проверили все необходимые условия. Их можно проверить в других тестах того же сценария. Перед тем как создавать новый тест, стоит переименовать существующий из дефолтного "Test Implementation 1" в "Положительный результат", а весь сценарий - в "Активный пост-оплатный клиент отправляет сообщение".


Важно

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


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


Примечание

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


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


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


-- Please replace this code with either a boolean -- expression like this: -- RETURN FALSE; -- or else a code block which returns a boolean value -- similar to the following: DECLARE l_count NUMBER; BEGIN SELECT count(*) INTO l_count FROM dual; IF l_count <> 0 THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END;

Для нашей проверки мы можем использовать этот шаблон, заменив dual на MESSAGE_QUEUE , затем применив необходимые фильтры. Условие также придётся сменить с l_count <> 0 на l_count = 1 для большей точности. После этого можно смело сохранять функцию в библиотеку для дальнейшего использования.


Примечание

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


При прогоне тестов мы должны увидеть ошибку. Её очень легко исправить:


INSERT INTO MESSAGE_QUEUE(ID_CLIENT, SENDER, RECIPIENT, MESSAGE) VALUES(V_ID_CLIENT, V_SENDER, V_RECIPIENT, V_MESSAGE);

Теперь можно убедиться, что все тесты проходят с успехом.


Примечание

При работе с тестами репозиторий блокируется, поэтому по окончании работы необходимо либо закрыть SQL Developer, либо закрыть репозиторий (Deselect Repository).


И, напоследок, проверим запись транзакции. Для этого выберем следующий тип валидации - сравнение результатов запросов (Compare Query Results). Как и следует из названия, он работает очень просто: нужно указать два запроса, результаты которых совпадут. Так как точную дату и время узнать невозможно, можно довольствоваться люб значение в пределах 10 секунд:


-- Source query SELECT 1 AS ID_CLIENT, 0.03 AS SUM_VALUE FROM DUAL -- Target query SELECT ID_CLIENT, SUM(VALUE) FROM TRANSACTIONS WHERE TRANSACTION_TIME BETWEEN CURRENT_TIMESTAMP AND (CURRENT_TIMESTAMP - 1/24/6) GROUP BY ID_CLIENT;

После прогона тестов мы видим расплывчатую ошибку Validation одна недавняя транзакция: Compare query results check found differences . Где "одна недавняя транзакция" - название нашей последней проверки в библиотеке. И хотя этот вариант уже является ценным инструментом, было бы замечательно, если бы он мог показать чем же именно результаты отличаются.


Добавим нужный функционал в нашу процедуру:


INSERT INTO TRANSACTIONS(ID_CLIENT, VALUE) VALUES(V_ID_CLIENT, 0.03);
Отладка

После очередного прогона тестов вдруг выясняется, что ошибка никуда не делась. Вы, наверное, уже заметили ошибку в коде выше, однако в реальных условиях ситуации бывают куда более сложными. Так как разницы инструмент на показывает, придётся выяснять причину вручную. К сожалению, отладочный функционал SQL Developer здесь ничем помочь не в состоянии. Это значит, что нам придётся прогнать тест без выполнения сброса. Для этого можно создать ещё один сценарий - отладочный. А точнее два: один - без сброса, но с тем же динамическим запросом, что и в нерабочем тесте - для того чтобы разобраться в чём дело; а второй - без настройки контекста, но со сбросом - для того чтобы убрать за первым.


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

Второй сценарий

Теперь когда у нас есть сценарий успешной оправки сообщения, мы можем попробовать сценарий неудачной отправки. Например, когда пост-оплатный клиент неактивен:


Дано: Неактивный пост-оплатный клиент Когда: Он отправляет сообщение То: Возвращается отрицательный результат, и транзакция не фиксируется, и очередь сообщений остаётся неизменной.

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


Для настройки контекста копируем PL/SQL шаг "Активный пост-оплатный клиент", в котором заменяем 1 на 0 и публикуем в библиотеке под названием "Неактивный пост-оплатный клиент". Повторяем то же для динамического запроса, назвав новый запрос "Неотправленное сообщение". Для сброса контекста используем существующий шаг.


После прогона тест должен показать ошибку. Её очень легко исправить. Заменяем SELECT 1 INTO V_IS_QUEUED FROM DUAL на SELECT IS_ACTIVE INTO V_IS_QUEUED FROM CLIENTS WHERE ID=V_ID_CLIENT - и всё снова работает.


Затем необходимо проверить, чтобы транзакция не сохранялась. Для этого используем следующий тип проверки - сравнение таблиц (Compare tables). Поначалу может показаться, что сравнивать не с чем, однако, в настройке контекста есть возможность скопировать существующую таблицу во временную. Нам это прекрасно подходит - можно скопировать транзакции во временную таблицу, а после вызова процедуры сравнить результаты. Главное - не забыть эту таблицу удалить при сбросе контекста. Есть два варианта - восстановить, затем удаилить, и просто удалить. Так как восстанавливать нам нечего - выберем второй вариант. Обратите внимание, что как и в случае со сравнением запросов, единственный вариант обратной связи - есть совпадение или нет.


Полюбовавшись на ошибку после прогона тестов, можно подумать над решением. Например, можно обернуть вставку в условие, исользуя свеже-обновлённый V_IS_QUEUED:


IF V_IS_QUEUED = 1 THEN INSERT INTO TRANSACTIONS (ID_CLIENT, VALUE) VALUES (V_ID_CLIENT, 0.03); END IF;

Компилируем процедуру, прогоняем тесты - всё работает.


В заключение, нам надо проверить, что очередь сообщений осталась без изменений. И хотя руки чесались сразу же поместить вставку сообщения внутрь условия рядом со вставкой транзакции, это было бы поощрением нарушения дисциплины. Поэтому сначала создадим дополнительную проверку для этого утверждения. Следующий тип проверки - Запрос, не возвращающий записей (Query returning no rows). Так как мы полностью очищаем все данные после каждого теста, достаточно будет указать SELECT * FROM MESSAGE_QUEUE в качестве такого запроса.


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

Выводы

SQL Developer можно использовать для разработки хранимых процедур методом TDD. Невзирая на многочисленные недостатки, этот пакет предоставляет платформу для разработки хранимых процедур, позволяя разработчикам с лёгкостью и уверенностью менять и расширять функционал существующих процедур.

Добавить метки

Хранимая процедура (stored procedure) - это программа, которая вы­полняет некоторые действия с информацией в базе данных и при этом сама хранится в базе данных. В Oracle хранимые процедуры можно мож­но писать на языках PL/SQL и Java.

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

Хранимые процедуры используются для многих целей. Хотя админи­страторы баз данных используют их для выполнения рутинных задач ад­министрирования, главной областью их применения являются все же при­ложения баз данных. Эти процедуры могут вызываться из прикладных программ, написанных на таких языках, как Java, С#, С++ или VB.Net, а также из веб-сценариев, написанных на VBScript или JavaScript. Кроме того, эти процедуры можно вызывать в интерактивном режиме из команд­ной оболочки SQL*Plus.

Можно выделить следующие преимущества хранимых процедур:

В отличие от кода приложений, хранимые процедуры никогда не пере­даются на клиентские компьютеры. Она всегда находятся в базе данных и выполняются СУБД на том компьютере, где располагается сервер базы данных. Таким образом, они более безопасны, чем распространяемый код приложения, а кроме того, снижают сетевой трафик. Хранимые процеду­ры постепенно становятся предпочтительным режимом реализации логи­ки приложения в сети Интернет и корпоративных интрасетях. Еще одно преимущество хранимых процедур заключается в том, что SQL-операторы в них могут быть оптимизированы компилятором СУБД.

Пример хранимой процедуры

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

В листинге 4.6 изображена хранимая процедура, выполняющая эту за­дачу. Процедура, которая называется Customer_Insert, принимает четы­ре параметра: newname (имя нового клиента), newareacode (код региона), newphone (телефон) и artistnationality (национальность художника). Клю­чевое слово IN указывает на то, что все эти параметры являются входны­ми. Выходные параметры (которых у этой процедуры нет) обозначаются ключевым словом OUT, а параметры, играющие роль и входных и вы­ходных, - сочетанием IN OUT. Следует обратить внимание, что для па­раметра указывается только тип данных, а длина не указывается. Oracle определит длину из контекста.

Листинг 4.6.

CREATE OR REPLACE PROCEDURE Customer_Insert (
newname IN char, newareacode IN char, newphone IN char,
artistnationality IN char
AS
rowcount integer(2);
CURSOR artistcursor IS SELECT ArtistID FROM ARTIST
WHERE Nationality = artistnationality;
BEGIN
SELECT Count(*) INTO rowcount FROM CUSTOMER
WHERE Name = newname AND AreaCode = newareacode AND PhoneNumber = newphone;
IF rowcount > 0 THEN BEGIN
DBMS_OUTPUT.PUT_LINE ("There is client in DB! Count is " I I rowcount); RETURN;
END; END IF;
INSERT INTO CUSTOMER
(CustomerlD, Name, AreaCode, PhoneNumber)
VALUES (CustID.NextVal, newname, newareacode, newphone);
FOR artist IN artistcursor LOOP
INSERT INTO CUSTOMER_ARTIST_INT (CustomerlD, ArtistID)
VALUES (CustID.CurrVal, artist.Artist ID); END LOOP;
DBMS_OUTPUT.PUT_LINE ("Client is added!");
END;
/

Раздел объявления переменных следует за ключевым словом AS. Опе­ратор SELECT определяет переменную-курсор (cursor variable) с именем artistcursor. Этот курсор выделяет из таблицы ARTIST для обработки строки всех художников заданной национальности.

В первой части процедуры проверяется, есть ли в базе информация о данном клиенте. В этом случае никакие действия не предпринимаются, а пользователю с помощью пакета Oracle DBMS_OUTPUT выводится со­ответствующее сообщение. Следует обратить внимание, что для вывода строки и значения переменной используется следующий синтаксис:

DBMS_OUTPUT.PUT_LINE ("<строка>" И <переменная>);

Пользователь получит это сообщение только в том случае, если проце­дура будет вызвана из SQL*Plus. В случае вызова процедуры иным путем, например с помощью браузера через Интернет, пользователь не увидит этого сообщения. Чтобы сообщить пользователю об ошибке, разработчик должен воспользоваться выходным параметром или сгенерировать исклю­чение.

Кроме того, чтобы такие сообщения стали видимыми, следует выпол­нить команду

Set serveroutput on;

Если при работе в SQL*Plus вы не видите сообщений, выводимых ва­шими процедурами, то, скорее всего, вы не выполнили этот оператор.

Оставшаяся часть процедуры в листинге 4.6 вставляет данные о новом клиенте и затем перебирает всех художников выбранной национальности. Обратите внимание на использование специальной конструкции PL/SQL FOR artist IN artistcursor. Эта конструкция выполняет несколько задач. Прежде всего, она открывает курсор и считывает первую строку. Затем она последовательно обрабатывает все строки под курсором и по оконча­нии обработки передает управление следующему оператору после FOR. Заметьте также, что обращение к столбцу ArtistID текущей строки про­исходит с использованием синтаксиса artist.ArtistID, где artist - это имя переменной цикла FOR, а не курсора.

После того как процедура написана, ее необходимо скомпилировать и сохранить в базе данных. Если текст процедуры сохранен в файле, то про­цедура будет скомпилирована и сохранена в базе данных автоматически после ввода команды

start Имя_файла_процедуры

Если вы что-то ввели неправильно, у вас могут возникнуть ошибки компиляции. К сожалению, SQL*Plus не покажет вам эти ошибки автома­тически, а выдаст сообщение "Warning: Procedure created with compilation errors "(Предупреждение: При компиляции процедуры обнаружены ошиб­ки). Чтобы увидеть ошибки, введите команду:

Show errors;

Если синтаксических ошибок не было, вы получите сообщение "Procedure created "(Процедура создана). Теперь вы можете вызвать эту процедуру с помощью команды EXECUTE или EXEC:

Exec Customer_Insert("Michael Bench", "203", "555-2014", "US");

Если возникнут ошибки на этапе выполнения процедуры, номера строк в отчете об ошибках не будут совпадать с номерами строк, которые вы можете видеть в своем текстовом редакторе.

Пример SQL и хранимых процедур DB2, которые можно использовать с провайдером данных JDBC.

Провайдер данных JDBC может обрабатывать наборы результатов, возвращенные хранимой процедурой. В хранимую процедуру можно передать строковые или числовые входные параметры. Ниже приведен синтаксис запуска хранимой процедуры: call[: индекс ] имя_процедуры [аргумент] ... Где: индекс Дополнительное целое число, указывающее, какой набор результатов должен использоваться провайдером данных. Этот параметр имеет смысл использовать, если хранимая процедура возвращает несколько наборов результатов, а вы хотите собрать значения только из одного из них. Если индекс не указан, то собираются и возвращаются данные из всех наборов результатов. имя_процедуры Имя хранимой процедуры, которую должен запустить провайдер данных. аргумент Входной аргумент хранимой процедуры. Несколько аргументов разделяются пробелами. Если имя аргумента содержит пробел, то заключите имя в кавычки. Если аргумент можно проанализировать как целое число, то он передается в хранимую процедуру как целочисленный аргумент. Любой аргумент, заключенный в кавычки, передается как строковый аргумент.

Примеры SQL Server

call sp_helpdb

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

call:2 sp_helpdb master

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

Если не указан индекс, то собираются данные из всех возвращенных наборов результатов. В этом случае убедитесь, что возвращенные данные совместимы с заданными атрибутами. Agent Builder создает атрибуты из первого возвращенного набора результатов; предполагается, что все ожидаемые дополнительные наборы результатов совместимы с первым набором.

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

Ниже приведен пример функции DB2, написанной на SQL. В примере показано, как возвратить результаты, которые может обработать провайдер данных JDBC Agent Builder: -- Этот сценарий запускается так: -- db2 -td# -vf db2sample.sql -- Эта процедура показывает, как возвратить запрос из -- хранимой процедуры DB2; этот запрос может использоваться -- провайдером JDBC Agent Builder. Хранимая процедура -- возвращает следующие столбцы: -- Имя Описание Тип данных -- current_timestamp Текущее системное время отметка времени -- lock_timeout Срок ожидания блокировки числовая шкала 0 -- user Пользователь сеанса строка из 128 символов DROP procedure db2sample# CREATE PROCEDURE db2sample() RESULT SETS 1 LANGUAGE SQL BEGIN ATOMIC -- Задать SQL для запроса DECLARE c1 CURSOR WITH HOLD WITH RETURN FOR SELECT CURRENT TIMESTAMP as current_timestamp, CURRENT LOCK TIMEOUT as lock_timeout, CURRENT USER as user FROM sysibm.sysdummy1; -- Передать запрос и возвратить данные OPEN c1; END#

Эту функцию можно вызвать из Agent Builder, используя синтаксис, определенный для других хранимых процедур. Для выполнения этой хранимой процедуры в качестве оператора JDBC нужно задать call db2sample .

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

Хранимые процедуры Oracle не возвращают наборы результатов. Вместо этого нужно создать функцию, которая возвращает указатель ссылки Oracle. Ниже приведен пример функции Oracle, написанной на PL/SQL ; в примере показано, как возвратить результаты, которые может обработать провайдер данных JDBC Agent Builder: CREATE OR REPLACE FUNCTION ITMTEST RETURN SYS_REFCURSOR IS v_rc SYS_REFCURSOR; НАЧАТЬ OPEN v_rc FOR SELECT * FROM ALL_CLUSTERS; RETURN v_rc; END;

Эту функцию можно вызвать из Agent Builder, используя синтаксис, определенный для других хранимых процедур. Для выполнения этой хранимой процедуры в качестве оператора JDBC нужно задать call ITMTEST . Так как функция Oracle должна возвращать ссылку указателя, функции Oracle могут обрабатывать только один набор результатов. Это значит, что опция индекса не поддерживается для Oracle, так как методов для возвращения нескольких наборов результатов нет.

Хранимая процедура или функция есть объект реляционной базы данных, который является поименнованным набором операторов SQL и, в случае СУБД Oracle, набором операторов PL/SQL , который может быть скомпилирован и необязательно сохранен в базе данных. Если процедура сохраняется в базе данных, то она называется хранимой процедурой или функцией. Описание хранимых процедур и функций хранится в словаре данных реляционной базы данных.

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

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

Хранимые процедуры и функции, как объекты базы данных, создаются командой CREATE и уничтожаются командой DROP . Команда создания хранимой процедуры имеет следующий синтаксис:

CREATE PROCEDURE [имя схемы].имя процедуры [имя [(параметр [, параметр, ...])] {IS|AS} программа на PL/SQL;

Ключевое слово OR REPLACE указывает на безусловное замещение старого текста процедуры. Если процедура с таким именем уже определена, но ключевое слово OR REPLACE не указано, то возвращается сообщение об ошибке и замещения старого текста не происходит.

При описании переменных хранимой процедуры ключевое слово DECLARE не используется. Блок описания переменных начинается сразу после ключевого слова IS или AS .

PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;

Исполнение созданной процедуры может быть выполнено оператором EXEC PL/SQL , как показано ниже:

Команда создания хранимой процедуры имеет следующий синтаксис:

CREATE FUNCTION [имя схемы].имя функции [имя [(параметр [, параметр, ...])] RETURN тип данных {IS|AS} программа на PL/SQL;

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

Пример. Для нашей учебной базы данных создадим функцию, которая считает число сотрудников, поступивших в организацию за определенный период:

Вызвать созданную хранимую функцию можно следующим образом:

Для уничтожения хранимой процедуры или функции в базе данных используется команда DROP в формате DROP [имя схемы].имя процедуры;

Или DROP [имя схемы].имя функции;

Особенности использования процедур и функций в СУБД Oracle

В этом подразделе рассмотрим некоторые особенности использования процедур и функций в PL/SQL :

  • Способы связывания формальных и фактических параметров в процедурах и функциях.
  • Использование для параметров значений по умолчанию.
  • Возникновение алиасных имен при передаче параметров.

При вызове процедур и функций в PL/SQL допускается передача параметров по имени и по позиции. Это означает, что вы указываете, как происходит связывание формальных и действительных параметров. Например, пусть имеется гипотетическая программа

DECLARE x1 INTEGER; x2 REAL; PROCEDURE proc1 (p1 INTEGER, p2 REAL) IS BEGIN ... END;

Процедуру Proc1 можно вызвать следующими эквивалентными способами:

BEGIN ... proc1 (x1, x2); -- передача параметров по позиции proc1 (p2 => x2, p1 => x1); -- передача параметров по имени proc1 (p1 => x1, p2 => x2); -- передача параметров по имени proc1 (x1, p2 => x2); -- передача параметров и по -- позиции, и по имени END;

При передаче параметра по позиции компилятор PL/SQL последовательно связывает первый фактический параметр с первым формальным параметром, второй фактический параметр - со вторым формальным параметром и так далее.

При передаче параметра по имени стрелка, называемая оператором связывания ( association operator ), связывает формальный параметр слева от стрелки с фактическим параметром справа от стрелки, причем порядок следования таких пар не имеет значения.

Можно комбинировать передачу параметра по позиции и по имени. Но при этом следует соблюдать требование того, чтобы передача параметра по позиции предшествовала передаче параметра по имени.

В процедуре или функции можно инициализировать параметр типа IN значением по умолчанию. Таким способом можно передавать различное число действительных параметров в процедуры или функции, принимая или переопределяя значение по умолчанию. Рассмотрим пример.

Пример. Для нашей учебной базы данных разработаем процедуру создания нового отдела таблице DEPARTAMENT.

PROCEDURE create_dept (new_dname CHAR DEFAULT "Новый", new_loc CHAR DEFAULT "Москва") IS BEGIN INSERT INTO departament VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;

Если при вызове процедуры фактический параметр не передается, то используется значение по умолчанию. Рассмотрим следующие вызовы этой процедуры:

create_dept; create_dept("Маркетинг"); create_dept("Маркетинг", Черноголовка);

При первом вызове не передается никаких параметров, так что используются значения параметра по умолчанию - Новый отдел в г. Москве. Во втором вызове передается один параметр - первый. Для второго используется значение по умолчанию. В третьем случае передаются оба параметра и значения по умолчанию не используются.

Для оптимизации выполнения процедур и функций компилятор PL/SQL может выбирать различные методы передачи параметров (по значению или по ссылке) для различных параметров в одном и том же вызове процедуры или функции. Когда компилятор выбирает метод передачи параметра по значению, то его значение копируется в процедуре или функции. Когда компилятор выбирает метод передачи параметра по ссылке, то адрес фактического параметра передается в процедуру или функцию. Может создаться ситуация когда переменная в процедуре или функции будет иметь два имени - так называемая проблема алиасных имен. В этом случае результат будет неопределенным. Это происходит, когда глобальная переменная передается по адресу, как в примере ниже.