СУБД Oracle (PL/SQL)

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

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

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

Руководства по работе с Oracle:

• Python:
  • High performance Python applications with Oracle Database,
  • cx_Oracle module documentation by Anthony Tuininga;
• Ruby on Rails:
  • Developing Ruby applications with Oracle Database,
  • Ruby on Rails on Oracle: A Simple Tutorial by Richard Monson-Haefel,
  • Ruby on Rails with Oracle FAQ by Casimir Saternos;
• PHP:
  • High performance PHP applications with Oracle Database,
  • The Underground PHP and Oracle Manual by Christopher Jones and Alison Holloway,
  • The Oracle+PHP Cookbook by John Alphonse, Justin Kestelyn and The Oracle PHP Development Team.

АСР «Гидра» не предоставляет приложениям возможности прямого доступа к таблицам БД. Для извлечения информации из БД используются представления (views) и процедуры сервисных пакетов, а для добавления, модификации и удаления — только хранимые процедуры. Программная документация расположена во вложенных в данный раздел страницах. Документация генерируется автоматически на основании заголовков PL/SQL-пакетов в БД. Самая актуальная версия ее всегда находится в БД работающей у вас системы. Для ее просмотра достаточно установить, например, Oracle SQL Developer, подключиться к БД и в ней посмотреть заголовки пакетов (packages). В БД все объявления процедур, типов данных имеются в открытом виде. Также доступны представления (views) в исходном коде.

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

С точки зрения клиентского приложения, работа с представлением аналогична работе с обычной RDBMS-таблицей с помощью языка SQL. В АСР «Гидра» из представлений можно делать только выборки (SELECT-запросы), а DML-операции (запросы 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_SUBJECT_ID_Created  NUMBER := NULL; -- пустой идентификатор говорит о том, что требуется создать новое физлицо
BEGIN
  SI_PERSONS_PKG.SI_PERSONS_PUT (
     num_N_SUBJECT_ID => num_N_SUBJECT_ID_Created,
     ...);
  -- При успешном выполнении процедуры в переменную num_N_SUBJECT_ID_Created запишется идентификатор вновь созданной записи.
  -- Значение идентификатора выдается системой автоматически.
END;

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

DECLARE
  num_N_SUBJECT_ID_Edit  NUMBER := 123455601; -- необходимо указать идентификатор существующего физлица, иначе при вызове будет ошибка.
BEGIN
  SI_PERSONS_PKG.SI_PERSONS_PUT (
     num_N_SUBJECT_ID => num_N_SUBJECT_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.

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

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

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

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

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

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

SELECT *
FROM   SS_V_CONSTANTS;

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

Справочники

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

-- Подcчитать, сколько сейчас актуальных документов в разрезе типа документа
SELECT N_DOC_TYPE_ID,
       SI_REF_PKG_S.GET_NAME_BY_ID(N_DOC_TYPE_ID) VC_DOC_TYPE,
       COUNT(*) N_COUNT
FROM   SD_V_DOCUMENTS
WHERE  N_DOC_STATE_ID = TO_NUMBER(SYS_CONTEXT('CONST', 'DOC_STATE_Actual'))
GROUP BY N_DOC_TYPE_ID
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 приложение должно вызвать процедуру SS_AUTHENTICATION_PKG.LOGIN. Пример:

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

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

В сторонних приложениях, в которых возможен интенсивный вызов SS_AUTHENTICATION_PKG.LOGIN (чаще 1 раза в минуту), для предотвращения сильного роста таблицы с сессиями следует использовать инициализацию сессии в сочетании с SS_AUTHENTICATION_PKG.SWITCH_CONTEXT. Пример:

BEGIN
  SS_AUTHENTICATION_PKG.SWITCH_CONTEXT(
    vch_VC_CLN_SESSIONID  => 'my external id');
EXCEPTION 
  WHEN ERRORS_PKG_S.SESSION_DOES_NOT_EXIST OR
       ERRORS_PKG_S.SESSION_CLOSED         OR
       ERRORS_PKG_S.MULTIPLE_SESSIONS      
  THEN
    SS_AUTHENTICATION_PKG.LOGIN(
      vch_VC_IP            => '127.0.0.1',
      vch_VC_LOGIN         => 'rpc',
      vch_VC_PASSWORD      => 'password',
      vch_VC_APP_CODE      => 'NETSERV_HID',
      vch_VC_CLN_SESSIONID => 'my external id',
      vch_VC_CLN_APPID     => 'my app info');
END;
/

В версии 5.0 коды и определения ошибок перенесены в специальный пакет ERRORS_PKG_S из SS_AUTHENTICATION_PKG.

Описание исключений:

• ERRORS_PKG_S.SESSION_ALREADY_EXISTS — исключение, возникающее при вызове SS_AUTHENTICATION_PKG.LOGIN и передаче аргумента vch_VC_CLN_SESSIONID. Если сессия с идентификатором, переданным в vch_VC_CLN_SESSIONID существует, генерируется данное исключение. Процедура SWITCH_CONTEXT данное исключение не выбрасывает и его можно не обрабатывать.
• ERRORS_PKG_S.SESSION_DOES_NOT_EXIST — исключение, возникающие тогда, когда процедура SWITCH_CONTEXT не смогла подобрать переданную сессию.
• ERRORS_PKG_S.SESSION_CLOSED — исключение, возникающее, когда сессия с переданным идентификатором уже была закрыта заданием, закрывающим старые сессии.
• ERRORS_PKG_S.MULTIPLE_SESSIONS — исключение, возникающее при обнаружении нескольких сессий с заданным идентификатором.