Вызов хранимых процедур Oracle SQL и выполнение. TDD для хранимых процедур Oracle Вызов процедуры oracle sql

Хранимая процедура (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");

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

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

Общий формат процедуры PL/SQL выглядит так:

PROCEDURE [схема.]имя[(параметр[, параметр...]) ] IS [объявления] BEGIN исполняемые команды [ EXCEPTION обработчики исключений] END [имя];

Основные элементы этой структуры:

• схема - имя схемы, которой будет принадлежать процедура (необязательный аргумент). По умолчанию применяется имя схемы текущего пользователя. Если значение схемы отлично от имени схемы текущего пользователя, то этот пользователь должен обладать привилегиями для создания процедуры в другой схеме.
• имя - имя процедуры.
• параметр - необязательный список параметров, которые применяются для передачи данных в процедуру и возврата информации из процедуры в вызывающую программу.
• AUTHID - определяет, с какими разрешениями будет вызываться процедура: создателя (владельца) или текущего пользователя. В первом случае процедура выполняется с правами создателя, во втором - с правами вызывающего.
• объявления - объявления локальных идентификаторов этой процедуры. Если объявления отсутствуют, между ключевыми словами IS и BEGIN не будет никаких выражений.
• ACCESSIBLE BY (Oracle Database 12c) - ограничивает доступ к процедуре программными модулями, перечисленными в круглых скобках.
• исполняемые команды - команды, выполняемые процедурой при вызове. Между ключевыми словами BEGIN и END или EXCEPTION должна находиться по крайней мере одна исполняемая команда.
• обработчики исключений - необязательные обработчики исключений для процедуры. Если процедура не обрабатывает никаких исключений, слово EXCEPTION можно опустить и завершить исполняемый раздел ключевым словом END .

На рис. 1 показан код процедуры apply_discount , который содержит все четыре раздела, характерных для именованных блоков PL/SQL.

Рис. 1

Вызов процедуры

Процедура вызывается как исполняемая команда PL/SQL. Другими словами, ее вызов должен заканчиваться точкой с запятой (;) и может предшествовать другим командам SQL либо PL/SQL (если таковые имеются) в исполняемом разделе блока PL/SQL или следовать за ними:

BEGIN apply_discount(new_company_id, 0.15); END;

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

Display_store_summary; display_store_summary();

Заголовок процедуры

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

• Имя процедуры.
• Условие AUTHID (если имеется).
• Список параметров (если имеется).
• Список ACCESSIBLE BY (если имеется - новая возможность Oracle Database 12c).

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

Заголовок процедуры apply_discount из предыдущего раздела выглядит так:

PROCEDURE apply_discount (company_id_in IN company.company_id%TYPE , discount_in IN NUMBER)

Он состоит из типа модуля, имени и списка из двух параметров.

Тело процедуры

В теле процедуры содержится код, необходимый для реализации этой процедуры; тело состоит из объявления, исполняемого раздела и раздела исключений этой процедуры. Все, что следует за ключевым словом IS, образует тело процедуры. Разделы исключений и объявлений не являются обязательными. Если обработчики исключений отсутствуют, опустите ключевое слово EXCEPTION и завершите процедуру командой END .

Метка END

Вы можете указать имя процедуры за завершающим ключевым словом END:

PROCEDURE display_stores (region_in IN VARCHAR2) IS BEGIN ... END display_stores;

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

Команда RETURN

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

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

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

Погуглив немного, мы обнаружили, что в штатном инструментарии 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. Невзирая на многочисленные недостатки, этот пакет предоставляет платформу для разработки хранимых процедур, позволяя разработчикам с лёгкостью и уверенностью менять и расширять функционал существующих процедур.

К сожалению, тестовый репозиторий можно создать только в СУБД Oracle. Кроме того, попытки использовать сторонние СУБД типа PostgreSQL или даже MySQL в качестве базы данных для тестирования, заканчиваются крахом подсистемы теститования. Также выяснилось, что использование SQL Developer в системах непрерывной интеграции вызывает массу проблем, но это уже отдельная история.

и EXEC SP() и CALL SP() может использоваться в SQL * Plus для выполнения SP. Кстати, вы также можете использовать BEGIN SP(); END;

но есть некоторые различия.

CALL является Oracle SQL и должен работать везде. Другие клиенты БД, которые могут общаться с Oracle, могут поддерживать или не поддерживать SQL*Plus EXEC. Многие (например, Oracle SQL Developer, SQLWorkbench/J), но некоторые нет (Liquibase).

типы данных передаваемых параметров the CALL оператор должен быть SQL типов данных. Они не могут быть только типами данных PL/SQL, такими как BOOLEAN.

EXEC может использоваться для выполнения не только SP,но и произвольного оператора.

если SP не имеет параметров, вы можете использовать EXEC SP; синтаксис, но CALL требуются пустые скобки: CALL SP();

Если вы вызываете proc, который возвращает sys_refcursor с помощью Toad, существует разница между CALL и EXEC.

создать процедуру foo (i в номере, o out sys_refcursor) как начинать откройте для выберите i из dual; конец;

exec foo (1,:r); -- выводит 1 строку

вызов foo (1,: r); -- выводит 0 строк

Note: когда вы префикс параметра с двоеточием, жаба предложит вам тип (который в данном случае является курсором).

EXECUTE принимает строку в качестве параметра, который позволяет" выполнять " динамический sql. Выполнить в основном говорят... с помощью этой входной строки запустите SQL engine на содержимом.

вызов передает управление хранимой процедуре или модулю.

Как вы можете видеть, концептуально они совершенно разные. Однако, если вы просто выполняете процедуру, на практике для этого варианта использования они одинаковы.

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