Статьи Королевства Дельфи


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


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

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

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

  1. Одновременное отображение физических и юридических лиц, отвечающих дополнительному условию.
  2. Перестройка баз данных из источника, не поддерживающего автоматической целостности ссылочной системы с проверкой уникальности первичных ключей и целостности внешних ключей.
  3. Выборка пакетами записей с фиксированным числом записей. Примеры из жизни - в поисковой системе отображаются страницы 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^ при создании физ и юр лиц :
  1. каждой записи физ лица соответствует одна запись лица;
  2. каждой записи юр лица соответствует одна запись лица;
  3. множества лиц физических и юридических не пересекаются;
  4. одной записи для физ лица соответствует хотя бы одна запись истории физ лица;
  5. одной записи для юр лица соответствует хотя бы одна запись истории юр лица.
Для автоматического выполнения этого условия надо физ и юр лица создавать следующими процедурами 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 ^ При удалении физ или юр лиц достаточно удалит ЛИЦО, все остальное будет удалено каскадно.




Начало  Назад  Вперед