Главная  
21.09.2017 г.
Некоторые решения с применением хранимых процедур Печать E-mail
Автор Administrator   
20.05.2010 г.

С учетом замечаний читателей изменена нотация в задаче 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 (
   <font color="#0000ff"> /*входные аргументы*/</font>
    NAME_FRAG VARCHAR(20),    <font color="#0000ff">/*вызывающий обрамляет его в %%*/</font>
    SECUR_CODE INTEGER,
    BROKER_CODE INTEGER)
RETURNS (
   <font color="#0000ff"> /*выходные аргументы*/</font>
 
    NAME VARCHAR(45),
    PERSON_CODE INTEGER,
    SECUR_CODE_G INTEGER,
    OWNER_CODE INTEGER)
AS
begin
  <font color="#0000ff">/*условия, общие для физ и юр лиц*/</font>
  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 
     <font color="#0000ff"> /*условия, частные для физ лиц*/</font>
      for select FIO
        from  NATUR ,NAT_HIST
        where NAT_HIST.FIO LIKE :NAME_FRAG and
              NATUR.PERSON_CODE_E=:PERSON_CODE and  <font color="#0000ff">/*лицо*/</font>
              NATUR.NATUR_CODE=NAT_HIST.NATUR_CODE and
              NAT_HIST.VALID_NOW=1
        into  :NAME
        do  suspend;
 
      <font color="#0000ff">/*условия, частные для юр лиц*/</font>
      for select FULL_NAME
        from  JURID ,JUR_HIST
        where JUR_HIST.FULL_NAME LIKE :NAME_FRAG and
              JURID.PERSON_CODE_E=:PERSON_CODE_ and<font color="#0000ff"> /*лицо*/</font>
              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;
        <font color="#0000ff">/*Создание ЛИЦА*/</font>
        EXECUTE PROCEDURE ADD_PERSON :x RETURNING_VALUES :masterkey, :error_code;
 
        IF (error_code=0) THEN
        BEGIN
                <font color="#0000ff">/*Создание физ лица*/</font>
                record_no=gen_id(NATUR_gen, 1);
                INSERT INTO NATUR (NATUR_CODE, PERSON_CODE_E) 
           VALUES (:record_no,:masterkey);
 
                <font color="#0000ff">/*Создание истории физ лица*/</font>
                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, c
         urrent_hist INTEGER)
AS
BEGIN
  BEGIN
        record_no=0;
        error_code=0;
        <font color="#0000ff">/*Создание ЛИЦА*/</font>
        EXECUTE PROCEDURE ADD_PERSON :x RETURNING_VALUES :masterkey, :error_code;
 
        IF (error_code=0) THEN
        BEGIN
                <font color="#0000ff">/*Создание юр лица*/</font>
                record_no=gen_id(JURID_gen, 1);
                INSERT INTO JURID (JURID_CODE, PERSON_CODE_E) 
          VALUES (:record_no,:masterkey);
               <font color="#0000ff"> /*Создание истории юр лица*/</font>
                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=<font color="#996600">'qq'</font>
      into :THE_CODE, :NAME
  do
  begin
    i=i+1;
    if ((SINCE<=i) AND (i<=TILL)) then
    begin
       suspend;
    end
 
    if (i>TILL) then
    begin
       exit;
    end
end
^
при n1>1 приведенное решение немного неоптимально, т.к. серверу приходится перебирать заново все записи, соответствующие поставленному условию.

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

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

Ivanovo State University of Chemical Technology is provided with the use of VP for educational purpose by the Academic Partner Program from Visual Paradigm.
Enterprise Architect
Sparx Systems Enterprise Arctitect provides Ivanovo State University of Chemical Technology with Enterprise Architect, Eclipse Integration, Visual Studio Integration, SysML Technology, Zachman Framework and much more for use in educational purposes, offered by the Enterprise Architect Academic Site License.
Новые файлы
ДатаНазвание
26.03.16Файл Лабораторная работа №11 - Многомерный поиск. Линейное программирование.pdf
26.03.16Файл Лабораторная работа №10 - Математическая обработка экспериментальных данных.pdf
26.03.16Файл Лабораторная работа №09 - Анализ моделей в виде систем линейных алгебраических уравнений.pdf
26.03.16Файл Лабораторная работа №08 - Приближенное решение алгебраических и трансцендентных уравнений.pdf
26.03.16Файл Лабораторная работа №07 - Подпрограммы.pdf