/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/

CREATE TABLE TEST1 (
    ID      INTEGER NOT NULL,
    VVALUE1 VARCHAR(100),
    VVALUE2 VARCHAR(1000),
    TEST3ID INTEGER
);

CREATE TABLE TEST2 (
    ID          INTEGER NOT NULL,
    TEST1ID     INTEGER NOT NULL,
    DVALUE1     DOUBLE PRECISION,
    DVALUE2     DOUBLE PRECISION,
    DVALUE3     DOUBLE PRECISION,
    IVALUE1     INTEGER
);

CREATE TABLE TEST3 (
    ID         INTEGER NOT NULL,
    VVALUE1    VARCHAR(1000),
    DVALUE1    DOUBLE PRECISION
);

/******************************************************************************/
/***                              Primary keys                              ***/
/******************************************************************************/

ALTER TABLE TEST2 ADD CONSTRAINT PK_TEST2 PRIMARY KEY (ID);
ALTER TABLE TEST1 ADD CONSTRAINT PK_TEST1 PRIMARY KEY (ID);
ALTER TABLE TEST3 ADD CONSTRAINT PK_TEST3 PRIMARY KEY (ID);

/******************************************************************************/
/***                                Indices                                 ***/
/******************************************************************************/

CREATE INDEX TEST2_IDX2 ON TEST2 (TEST1ID);
CREATE INDEX TEST2_IDX3 ON TEST2 (IVALUE1);
CREATE INDEX TEST1_IDX1 ON TEST1 (DVALUE1);
CREATE INDEX TEST1_IDX2 ON TEST1 (TEST3ID);

create or alter procedure SP_SAVE_TEST3 (
    VVALUE1 varchar(1000),
    DVALUE1 double precision)
returns (
    ITEST3ID integer)
as
begin
  if (:VVALUE1 is null) then
    VVALUE1 = '';
  if (:DVALUE1 is null) then
    DVALUE1 = 1;

  if (exists(select *
             from TEST3
             where VVALUE1 = :VVALUE1)) then
  begin
    select first 1 ID
    from TEST3
    where VVALUE1 = :VVALUE1
    into :ITEST3ID;
  end
  else
  begin
    begin
      select max(ID) + 1
      from TEST3
      into :ITEST3ID;
      if (:ITEST3ID is null) then
        ITEST3ID = 1;
    end
    begin
      insert into TEST3
      values (:ITEST3ID, :VVALUE1, :DVALUE1);
    end
  end
  suspend;
end

execute block
                    as
                    declare variable ITEST1ID integer;
                    declare variable ITEST3ID integer;
                    declare variable ITEST2ID integer;
                    declare variable VVALUE1 varchar(100);
                    declare variable VVALUE2 varchar(1000);
                    declare variable VVALUE3 varchar(1000);
                    declare variable DPVALUE1 double precision;
                    declare variable DPVALUE2 double precision;
                    declare variable DPVALUE3 double precision;
                    declare variable DPVALUE4 double precision;
                    declare variable IVALUE1 integer;
                    begin
                    VVALUE1 = '';
                    VVALUE2 = '';
                    VVALUE3 = '';
                    DPVALUE1 = 0;
                    DPVALUE2 = 0;
                    DPVALUE3 = 0;
                    DPVALUE4 = 0;
                    IVALUE1 = 0;                  

                      /* Табличка test3 */
                      select ITEST3ID
                      from SP_SAVE_TEST3(:VVALUE3, :DPVALUE1)
                      into :ITEST3ID;

                      /*Проверяем наличие такой же записи TEST1 */
                      if (exists(select *
                                 from TEST1 M
                                 left join TEST3 U on (M.TEST3ID = U.ID)
                                 where M.VVALUE1 = :VVALUE1 and
                                       M.VVALUE2 = :VVALUE2 and
                                       U.VVALUE1 = :VVALUE3)) then
                      begin
                        select first 1 M.ID
                        from TEST1 M
                        left join TEST3 U on (M.TEST3ID = U.ID)
                        where M.VVALUE1 = :VVALUE1 and
                              M.VVALUE2 = :VVALUE2 and
                              U.VVALUE1 = :VVALUE3
                        into :ITEST1ID;
                      end
                      else
                      begin
                        begin
                          select max(ID) + 1
                          from TEST1
                          into :ITEST1ID;
                          if (:ITEST1ID is null) then
                            ITEST1ID = 1;
                        end
                        begin
                          insert into TEST1
                          values (:ITEST1ID, :VVALUE1, :VVALUE2, :ITEST3ID);
                        end
                      end
                        select max(ID) + 1
                        from TEST2
                        into :ITEST2ID;
                        if (:ITEST2ID is null) then
                          ITEST2ID = 1;

                        insert into TEST2
                        values (:ITEST2ID, :ITEST1ID, :DPVALUE2, :DPVALUE3, :DPVALUE4, :IVALUE1);

                    end