СУБД Oracle (PLSQL)

Общая информация

Основной прикладной программный интерфейс АСР «Гидра» выполнен в виде набора PL/SQL пакетов (packages) СУБД Oracle 11g (10g). Чтобы использовать данный API, необходимо установить Oracle Instant Client (версии Basic или Basic Light подойдут). После этого нужно настроить файл tnsnames.ora для подключения к БД.

Далее следует установить драйвер Oracle для применяемого языка программирования. Например, для PHP это OCI8 Extensions , для Python — cx_Oracle , для Ruby — ruby-oci8 .

Руководства по работе с Oracle для PHP: 1 , 2 , для Python: 1 , 2 , Ruby on Rails: 1 , 2 .

АСР «Гидра» не предоставляет приложениям возможности прямого доступа к таблицам БД. Для извлечения информации из БД используются представления (views ) и процедуры сервисных пакетов, а для добавления, модификации и удаления — только хранимые процедуры.

Работа с представлениями

С точки зрения клиентского приложения, работа с представлением аналогична работе с обычной RDBMS-таблицей с помощью языка SQL. В АСР «Гидра» из представлений можно делать только выборки (SELECT-запросы), а DDL-операции (запросы INSERT, UPDATE) не разрешаются.

Представления называются по стандартной схеме: <префикс>V<код>[_<постфикс>] (пример: SI_V_SUBJECTS). Справочник префиксов см. в разделе «Работа с хранимыми процедурами» — он является общим для пакетов и представлений. Постфиксы бывают следующие:

Поля (columns) представления именуются в венгерской нотации: <префикс>_<код> (пример: VC_NAME). Если поле содержит идентификатор какой-либо сущности, то его наименование оканчивается на ID (пример: N_USER_ID — идентификатор абонента).

Стандартные префиксы для наименования полей в представлениях:

Наименование поля или представления может любым, однако значительная часть их имеет стандартные наименования, связанные с основными сущностями, хранящимися в АСР:

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

Кроме того, существуют и другие распространенные стандартные обозначения:

Работа с хранимыми процедурами

Общая информация

Хранимые процедуры объединены в пакеты. Пакеты бывают двух типов — обычные (привилегированные) и сервисные (вспомогательные). Обычные пакеты используются для добавления, модификации и редактирования данных в БД, поэтому для пользования ими требуются, как правило, расширенные права доступа. Сервисные пакеты содержат вспомогательные процедуры, связанные с извлечением и/или преобразованием информации из БД.

Пакеты называются по стандартной схеме: <префикс><код>_PKG для обычных пакетов (пример: SI_SUBJECTS_PKG) и <префикс><код>_PKG_S — для сервисных (пример: SI_SUBJECTS_PKG_S).

Стандартные префиксы для названий пакетов (также являются стандартными и для представлений):

Вызов хранимой процедуры состоит из двух частей — <наименование пакета>.<наименование процедуры>.

Аргументы процедуры в АСР "Гидра", так же, как и поля в представлениях, именуются в венгерской нотации — префикс наименования аргумента обозначает его тип данных . Таблица соответствия:

Язык PL/SQL позволяет вызывать процедуры с именованными аргументами (см. примеры ниже). Этим можно и нужно пользоваться. Разработчики АСР «Гидра» стараются по возможности сохранять обратную совместимость, но при добавлении новых аргументов в объявление процедуры порядок их следования не сохраняется. Вызов процедуры с именованными аргументами позволяет сохранить обратную совместимость, так как в этом случае порядок их передачи не имеет значения.

Процедуры создания/редактирования записи в БД

Процедуры с наименованием вида _PUT находятся в привилегированных пакетах. Как правило, они предназначены сразу и для создания, и для редактирования записи. У таких процедур первый аргумент - идентификатор создаваемой/редактируемой записи с типом вызова IN OUT, поэтому числовую константу передавать в него нельзя. Если при вызове такой процедуры в первый аргумент передать значение NULL, то создастся новая запись, а в этот аргумент запишется значение идентификатора вновь созданной записи. Если же в процедуру _PUT передать идентификатор существующей записи, то она будет модифицирована.

Пример 1. Вызов процедуры создания физического лица:

DECLARE
  num_N_PERSON_ID_Created  NUMBER := NULL; -- пустой идентификатор говорит о том, что требуется создать новое физлицо
BEGIN
  SI_PERSONS_PKG.SI_PERSONS_PUT (
     num_N_PERSON_ID => num_N_PERSON_ID_Created,
     ...);
  -- При успешном выполнении процедуры в переменную num_N_PERSON_ID_Created запишется идентификатор вновь созданной записи.
  -- Значение идентификатора выдается системой автоматически.
END;

Пример 2. Редактирование существующего физического лица:

DECLARE
  num_N_PERSON_ID_Edit  NUMBER := 123455601; -- необходимо указать идентификатор существующего физлица, иначе при вызове будет ошибка.
BEGIN
  SI_PERSONS_PKG.SI_PERSONS_PUT (
     num_N_PERSON_ID => num_N_PERSON_ID_Edit,
     ...);
END;

Транзакции, исключения и сообщения

После вызова хранимой процедуры (либо группы процедур), которая изменяет данные в БД, необходимо зафиксировать (commit) либо откатить (rollback) транзакцию. Ответственность за это лежит на вызывающем приложении.

При вызове из приложения хранимой процедуры может возникнуть исключительная ситуация (exception). В этом случае Oracle выполняет автоматический откат транзакции. Вызывающему приложению в обязательном порядке следует перехватывать возникшие исключения и выводить их пользователю и/или в лог. Вместе с сообщением Oracle передаёт и стек вызова, который следует тоже выводить в лог.

Помимо встроенного механизма исключений Oracle, АСР "Гидра" использует и собственный механизм работы с сообщениями. Он применяется в случаях, когда использование встроенного механизма невозможно, в частности, при массовых обработках или при необходимости вывести информационное сообщение либо предупреждение не нарушая ход выполнения процедуры и не выполняя откат транзакции. Чтобы выводить такие сообщения, следует после выполнения хранимой процедуры сделать выборку всех записей из специальной временной таблицы:

SELECT * FROM TT_V_MESSAGE_LOGS;

Если количество извлеченных из этой таблицы сообщений больше нуля, то необходимо вывести их все пользователю и/или в лог, после чего очистить таблицу:

DELETE FROM TT_MESSAGE_LOGS;

Константы

При использовании представлений и хранимых процедур часто необходимо использовать предопределенные константы. Обычно они применяются для указания типа и состояния документов, СУ или ОУ, указания единицы измерения, ставки налога и т.д. Константа состоит из кода и значения (почти всегда значение числовое).

Все используемые системой константы описаны в специальном сервисном пакете SS_CONSTANTS_PKG_S, а также доступны в глобальном контексте CONST и в представлении SS_V_CONSTANTS.

Для передачи констант в качестве значения аргумента хранимой процедуры рекомендуется использовать обращение через пакет: SS_CONSTANTS_PKG_S.<код константы>. Пример:

BEGIN
  -- Аннулировать документ с идентификатором 1577736201
  SD_DOCUMENTS_PKG.SD_DOCUMENTS_CHANGE_STATE (
     num_N_DOC_ID           => 1577736201,
     num_N_New_DOC_STATE_ID => SS_CONSTANTS_PKG_S.DOC_STATE_Canceled);
END;

Для SQL-запросов к представлениям необходимо применять обращение через контекст: SYS_CONTEXT('CONST', '<код константы>'). Пример:

-- Подсчитать количество актуальных инвойсов в системе
SELECT COUNT(*)
FROM   SD_V_DOCUMENTS
WHERE  N_DOC_TYPE_ID  = SYS_CONTEXT('CONST', 'DOC_TYPE_Invoice')
AND    N_DOC_STATE_ID = SYS_CONTEXT('CONST', 'DOC_STATE_Actual');

Приложения, постоянно взаимодействующие с АСР «Гидра», в целях ускорения работы и сокращения нагрузки на БД могут загружать (кэшировать) сразу все константы при инициализации. Для этого нужно сделать следующий запрос:

SELECT *
FROM   SS_V_CONSTANTS;

и сохранить полученные значения, например, в ассоциативном массиве, где ключом будет являться поле VC_NAME, а значением — поле VC_VALUE.

Справочники

Чтобы получить, например, название типа документа в текстовом представлении по его числовому идентификатору, удобно использовать процедуры из пакета SI_REF_PKG_S. Пример:

SQL> -- Подcчитать, сколько сейчас актуальных документов в разрезе типа документа
SQL> SELECT N_DOC_TYPE_ID,
  2         SI_REF_PKG_S.GET_NAME_BY_ID(N_DOC_TYPE_ID) VC_DOC_TYPE,
  3         COUNT(*) N_COUNT
  4  FROM   SD_V_DOCUMENTS
  5  WHERE  N_DOC_STATE_ID = SYS_CONTEXT('CONST', 'DOC_STATE_Actual')
  6  GROUP BY N_DOC_TYPE_ID
  7  ORDER BY N_COUNT DESC;

N_DOC_TYPE_ID VC_DOC_TYPE                                N_COUNT
------------- --------------------------------------------------
         5002 Платёжное поручение                          69950
         4002 Кассовый ордер                                9328
         1002 Договор на оказание услуг                     2817
         3002 Инвойс                                        1177
         6002 Счёт                                           515
         7002 Приказ по ценам                                171
        11002 Банковская выписка                              74
         9002 Базовый договор                                 25
        13002 Доп. соглашение                                 15
        20002 Заявка                                           6
        12002 Приказ по временным интервалам                   3
         8002 Отказ в обслуживании                             1

12 rows selected

Получение диапазона значений справочника

Для получения всего диапазона значений заданного справочника можно воспользоваться следующим SQL-запросом:

SELECT N_REF_ID,
       VC_CODE,
       VC_NAME
FROM   SI_V_REF
WHERE  N_REF_TYPE_ID = SI_REF_PKG_S.GET_ID_BY_CODE('<vch_VC_REF_NAME>')

<vch_VC_REF_NAME> — код справочника (например, REF_TYPE_Address_Type).

Получение значения справочного значения

Для получения значения конкротной справочной записи можно воспользоваться следующим SQL-запросом:

SELECT SI_REF_PKG_S.GET_ID_BY_CODE('<vch_VC_REF_CODE>')
FROM   DUAL

<vch_VC_REF_CODE> — код справочного значения (например, ADDR_TYPE_Subnet).

Начало работы

Для корректной работы в АСР «Гидра» после установления соединения с Oracle приложение должно вызвать процедуру MAIN.INIT. Пример:

BEGIN
  MAIN.INIT(
     vch_VC_IP        => '127.0.0.1',   -- IP-адрес, с которого выполняется вход в систему
     vch_VC_USER      => 'Payment_RPC', -- Логин пользователя АСР «Гидра» на приложение
     vch_VC_PASS      => 'secret123',   -- Пароль пользователя на приложение
     vch_VC_APP_CODE  => 'NETSERV_HID', -- Код приложения в АСР «Гидра»
     vch_VC_CLN_APPID => 'my app info'); -- Информационная строка приложения (название, версия и т. д.)
END;

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