Некоторые решения с применением хранимых процедур. ( v.1.02.)

Раздел Подземелье Магов

С учетом замечаний читателей изменена нотация в задаче 1.

Язык SQL поначалу кажется очень неповоротливым. Но по мере его освоения приходит мысль о том, что здесь имеем дело с МНОЖЕСТВОМ записей, отвечающих определенным непротиворечивым условиям. Хранимые процедуры - мост между этим МНОЖЕСТВОМ записей и ОТДЕЛЬНОЙ записью, принадлежащей этому множеству. Вот решения некоторых задач с применением хранимых процедур. Применяемый SQL сервер - народный interbase\firebird.

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

Другой упрощенный пример: обещанный в firebird 1.0 по просьбам трудящихся select top(n) from ... - выборка первых n записей, отвечающих определенному условию.

Сырцы взяты из текущих проектов, но, думаю, применяемые решения будут понятны (и полезны).

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

Иногда бывает необходимо держать данные о физ лицах и юр лицах в разных таблицах.
Краткое описание таблиц

PERSON лица
NATUR физ лица
JURID юр лица
NAT_HIST история физ лиц
JUR_HIST история юр лиц
OWNER владельцы ценных бумаг
SECUR ценные бумаги

Имена внешних ключей деталей совпадают с соответствующими именами первичных ключей мастеров (мастер-деталь) плюс суффикс (иногда).
Владельцы ценных бумаг считаются просто ЛИЦАМИ, а какое это лицо и его ФИО (в случае физ лица) или НАЗВАНИЕ (в случае юр лица) отобразит хранимая процедура. CREATE TABLE PERSON( PERSON_CODE INTEGER NOT NULL PRIMARY KEY ); CREATE TABLE NATUR( NATUR_CODE INTEGER NOT NULL PRIMARY KEY , PERSON_CODE_E INTEGER NOT NULL , FOREIGN KEY (PERSON_CODE_E) REFERENCES PERSON (PERSON_CODE) ON UPDATE CASCADE ON DELETE CASCADE ); CREATE TABLE JURID( JURID_CODE INTEGER NOT NULL PRIMARY KEY , PERSON_CODE_E INTEGER NOT NULL , FOREIGN KEY (PERSON_CODE_E) REFERENCES PERSON (PERSON_CODE) ON UPDATE CASCADE ON DELETE CASCADE ); А вот и текст процедуры. CREATE PROCEDURE SP_ALL_OWNERS ( /*входные аргументы*/ NAME_FRAG VARCHAR(20), /*вызывающий обрамляет его в %%*/ SECUR_CODE INTEGER, BROKER_CODE INTEGER) RETURNS ( /*выходные аргументы*/ NAME VARCHAR(45), PERSON_CODE INTEGER, SECUR_CODE_G INTEGER, OWNER_CODE INTEGER) AS begin /*условия, общие для физ и юр лиц*/ for select SECUR_CODE_G, OWNER_CODE, PERSON_CODE_G from OWNER where OWNER.SECUR_CODE_G=:SECUR_CODE and OWNER.BROKER_CODE=:BROKER_CODE into :SECUR_CODE_G, :OWNER_CODE, :PERSON_CODE do begin /*условия, частные для физ лиц*/ for select FIO from NATUR ,NAT_HIST where NAT_HIST.FIO LIKE :NAME_FRAG and NATUR.PERSON_CODE_E=:PERSON_CODE and /*лицо*/ NATUR.NATUR_CODE=NAT_HIST.NATUR_CODE and NAT_HIST.VALID_NOW=1 into :NAME do suspend; /*условия, частные для юр лиц*/ for select FULL_NAME from JURID ,JUR_HIST where JUR_HIST.FULL_NAME LIKE :NAME_FRAG and JURID.PERSON_CODE_E=:PERSON_CODE_ and /*лицо*/ JURID.JURID_CODE=JUR_HIST.JURID_CODE JUR_HIST.VALID_NOW=1 into :NAME do suspend; end end^ при создании физ и юр лиц :

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

Для автоматического выполнения этого условия надо физ и юр лица создавать следующими процедурами CREATE PROCEDURE ADD_NATUR_E (name VARCHAR(45)) RETURNS (record_no INTEGER, error_code INTEGER, masterkey INTEGER, current_hist INTEGER) AS BEGIN BEGIN record_no=0; error_code=0; /*Создание ЛИЦА*/ EXECUTE PROCEDURE ADD_PERSON :x RETURNING_VALUES :masterkey, :error_code; IF (error_code=0) THEN BEGIN /*Создание физ лица*/ record_no=gen_id(NATUR_gen, 1); INSERT INTO NATUR (NATUR_CODE, PERSON_CODE_E) VALUES (:record_no,:masterkey); /*Создание истории физ лица*/ EXECUTE PROCEDURE ADD_NAT_HIST :record_no, 1 RETURNING_VALUES :current_hist, :error_code; UPDATE NAT_HIST SET FIO = :name WHERE NAT_HIST_CODE = :current_hist; END END END ^ CREATE PROCEDURE ADD_JURID_E (name VARCHAR(45)) RETURNS (record_no INTEGER, error_code INTEGER, masterkey INTEGER, current_hist INTEGER) AS BEGIN BEGIN record_no=0; error_code=0; /*Создание ЛИЦА*/ EXECUTE PROCEDURE ADD_PERSON :x RETURNING_VALUES :masterkey, :error_code; IF (error_code=0) THEN BEGIN /*Создание юр лица*/ record_no=gen_id(JURID_gen, 1); INSERT INTO JURID (JURID_CODE, PERSON_CODE_E) VALUES (:record_no,:masterkey); /*Создание истории юр лица*/ EXECUTE PROCEDURE ADD_JUR_HIST :record_no, 1 RETURNING_VALUES :current_hist, :error_code; UPDATE JUR_HIST SET FULL_NAME = :name WHERE JUR_HIST_CODE = :current_hist; END END END ^ При удалении физ или юр лиц достаточно удалит ЛИЦО, все остальное будет удалено каскадно.

Для отображения в детали (мастер-деталь) результата, возвращаемого хранимой процедурой, в компоненте TIBQuery, как известно можно создать запрос с параметром.

select * from SP_ALL_OWNERS('%некто%', :SECUR_CODE) order by NAME;') а назначив свойство qryDetail.DataSource=masterDataSource, можно дать понять IBX-у, что значение параметра :OWNER надо искать в текущей записи указанного мастера.

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

Проверка уникальности первичных ключей
Описание таблиц:

SPORG1 - буферная таблица, полученная средствами, типа IBpump, без определения уникальных полей, первичных ключей и т.д. После выполнения процедуры в ней остаются "плохие" записи
OK_SPORG1 - итоговая таблица с описанием первичных ключей

CREATE PROCEDURE TEST_UNIQ_SPORG1 ( X INTEGER) AS declare variable iCode integer; begin iCode=0; for select code from SPORG1 into :iCode do begin insert into OK_SPORG1 SELECT * FROM SPORG1 where code=:iCode; delete from SPORG1 where code=:iCode; WHEN SQLCODE -803 DO BEGIN iCode=iCode; end end end ^ Проверка целостности внешних ключей
Описание таблиц:

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

CREATE PROCEDURE TEST_INTEG_CLIENT1 ( X INTEGER) AS declare variable iCode integer; begin for select tel from CLIENT_STREET into :iCode do begin insert into OK_CLIENT_STREET SELECT * FROM CLIENT_STREET where tel=:iCode; delete from CLIENT_STREET where tel=:iCode; WHEN SQLCODE -530 DO BEGIN iCode=iCode; end end end ^ При ошибке, оказывается, процедура не вылетает с откатом текущей транзакции, а просто возвращает код ошибки и ЦИКЛ ПРОДОЛЖАЕТСЯ.

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

То же самое наблюдалось при закачивании аналогичной таблицы из формата dbf в interbase с применением препроцессора gpre и низкоуровневым доступом к формату dbf (будет описано в следующей статье).

3. Выборка пакетами записей с фиксированным числом записей.

CREATE PROCEDURE SHOW_PART( SINCE INTEGER, TILL INTEGER) RETURNS ( THE_CODE integer, NAME varchar(10)) AS declare variable i integer; begin i=0; for select THE_CODE, NAME from MY_TABLE where NAME='qq' into :THE_CODE, :NAME do begin i=i+1; if ((SINCETILL) then begin exit; end end ^ при n1>1 приведенное решение немного неоптимально, т.к. серверу приходится перебирать заново все записи, соответствующие поставленному условию.

Верхняя допустимая граница TILL, как известно, определяется простым select count()-ом

Выборку производить следующим образом
select * from SHOW_PART(1,3); - показать с первой по третью записи, удовлетворяющие заданному в процедуре условию.

Кубанычбек Тажмамат уулу,
16 мая 2001г.

Содержание раздела