Ускорение режима все партии — различия между версиями

Материал из wiki.standart-n.ru
Перейти к: навигация, поиск
Строка 1: Строка 1:
−
=I вариант=
+
=Выполнить скрипт=
  
−
 
−
==Кэшируемая таблица==
 
−
<pre>
 
 
/******************************************************************************/
 
/******************************************************************************/
−
/***              Generated by IBExpert 07.03.2016 15:48:01               ***/
+
/***              Generated by IBExpert 08.03.2016 11:37:13               ***/
 
/******************************************************************************/
 
/******************************************************************************/
  
Строка 12: Строка 9:
 
/******************************************************************************/
 
/******************************************************************************/
 
SET SQL DIALECT 3;
 
SET SQL DIALECT 3;
 +
 +
  
 
/******************************************************************************/
 
/******************************************************************************/
 
/***                                Tables                                ***/
 
/***                                Tables                                ***/
 
/******************************************************************************/
 
/******************************************************************************/
 +
 +
  
 
CREATE TABLE WAREBASEPARTS (
 
CREATE TABLE WAREBASEPARTS (
Строка 76: Строка 77:
 
     MNN            DM_TEXT /* DM_TEXT = VARCHAR(250) */
 
     MNN            DM_TEXT /* DM_TEXT = VARCHAR(250) */
 
);
 
);
 +
 +
 +
  
 
/******************************************************************************/
 
/******************************************************************************/
 
/***                              Primary Keys                              ***/
 
/***                              Primary Keys                              ***/
 
/******************************************************************************/
 
/******************************************************************************/
 +
 
ALTER TABLE WAREBASEPARTS ADD CONSTRAINT PK_WAREBASEPARTS PRIMARY KEY (PART_ID);
 
ALTER TABLE WAREBASEPARTS ADD CONSTRAINT PK_WAREBASEPARTS PRIMARY KEY (PART_ID);
 +
 +
 +
/******************************************************************************/
 +
/***                                Indices                                ***/
 +
/******************************************************************************/
 +
 +
CREATE INDEX WAREBASEPARTS_IDX1 ON WAREBASEPARTS (BCODE_IZG);
 +
CREATE INDEX WAREBASEPARTS_IDX2 ON WAREBASEPARTS (SNAME);
 +
CREATE INDEX WAREBASEPARTS_IDX3 ON WAREBASEPARTS (SERIA);
 +
CREATE INDEX WAREBASEPARTS_IDX4 ON WAREBASEPARTS (SORIG_NAME);
 +
CREATE INDEX WAREBASEPARTS_IDX5 ON WAREBASEPARTS (DOCAGENT);
 +
 +
 +
/******************************************************************************/
 +
/***                              Privileges                              ***/
 +
/******************************************************************************/
 +
 +
 +
/******************************************************************************/
 +
 +
/******************************************************************************/
 +
/***      Following SET SQL DIALECT is just for the Database Comparer      ***/
 +
/******************************************************************************/
 +
SET SQL DIALECT 3;
 +
 +
 +
 +
/******************************************************************************/
 +
/***                                Views                                  ***/
 +
/******************************************************************************/
 +
 +
 +
/* View: VW_WAREBASEPARTS */
 +
CREATE OR ALTER VIEW VW_WAREBASEPARTS(
 +
    PART_ID,
 +
    WARE_ID,
 +
    SNAME,
 +
    SIZG,
 +
    SCOUNTRY,
 +
    ORIG_CODE,
 +
    SORIG_NAME,
 +
    SORIG_IZG,
 +
    SORIG_COUNTRY,
 +
    BCODE_IZG,
 +
    PRICE,
 +
    PRICE_O,
 +
    PRICE_Z,
 +
    PRICE_R,
 +
    QUANT,
 +
    BARCODE,
 +
    BARCODE1,
 +
    DEP,
 +
    KRITK,
 +
    GODENDO,
 +
    SERIA,
 +
    SUM_NDSO,
 +
    SERT,
 +
    DATESERT,
 +
    KEMVSERT,
 +
    SDSERT,
 +
    REGN,
 +
    NGTD,
 +
    EDIZM,
 +
    DOC_ID,
 +
    DOCNUM,
 +
    DOCDATE,
 +
    DOCCAPTION,
 +
    DOCAGENT,
 +
    DOCVNUM,
 +
    DOCVSHIFT,
 +
    INSERTDT,
 +
    UPDATEDT,
 +
    ENDDT,
 +
    NDS,
 +
    REALQUANT,
 +
    PART_PARENT_ID,
 +
    NAME_ID,
 +
    IZG_ID,
 +
    COUNTRY_ID,
 +
    NAC,
 +
    BLOCK_QUANT,
 +
    BLOCK_COUNT,
 +
    MMBSH,
 +
    PART_TYPE,
 +
    BASE_AGENT_ID,
 +
    SBASE_AGENT_ID,
 +
    CONTRACT_ID,
 +
    SCONTRACT_ID,
 +
    GROUP_ID,
 +
    ISFOLDER,
 +
    MNN)
 +
AS
 +
select
 +
    PART_ID,
 +
    WARE_ID,
 +
    SNAME,
 +
    SIZG,
 +
    SCOUNTRY,
 +
    ORIG_CODE,
 +
    SORIG_NAME,
 +
    SORIG_IZG,
 +
    SORIG_COUNTRY,
 +
    BCODE_IZG,
 +
    PRICE,
 +
    PRICE_O,
 +
    PRICE_Z,
 +
    PRICE_R,
 +
    QUANT,
 +
    BARCODE,
 +
    BARCODE1,
 +
    DEP,
 +
    KRITK,
 +
    GODENDO,
 +
    SERIA,
 +
    SUM_NDSO,
 +
    SERT,
 +
    DATESERT,
 +
    KEMVSERT,
 +
    SDSERT,
 +
    REGN,
 +
    NGTD,
 +
    EDIZM,
 +
    DOC_ID,
 +
    DOCNUM,
 +
    DOCDATE,
 +
    DOCCAPTION,
 +
    DOCAGENT,
 +
    DOCVNUM,
 +
    DOCVSHIFT,
 +
    INSERTDT,
 +
    UPDATEDT,
 +
    ENDDT,
 +
    NDS,
 +
    REALQUANT,
 +
    PART_PARENT_ID,
 +
    NAME_ID,
 +
    IZG_ID,
 +
    COUNTRY_ID,
 +
    NAC,
 +
    BLOCK_QUANT,
 +
    BLOCK_COUNT,
 +
    MMBSH,
 +
    PART_TYPE,
 +
    BASE_AGENT_ID,
 +
    SBASE_AGENT_ID,
 +
    CONTRACT_ID,
 +
    SCONTRACT_ID,
 +
    GROUP_ID,
 +
    ISFOLDER,
 +
    MNN
 +
from WAREBASEPARTS
 +
;
 +
 +
 +
  
 
/******************************************************************************/
 
/******************************************************************************/
 
/***                              Privileges                              ***/
 
/***                              Privileges                              ***/
 
/******************************************************************************/
 
/******************************************************************************/
−
</pre>
 
  
  
−
==Процедура для обновления данной таблицы==
 
−
<pre>
 
 
SET TERM ^ ;
 
SET TERM ^ ;
  
 
create or alter procedure UPDPR_WAREBASEPARTS (
 
create or alter procedure UPDPR_WAREBASEPARTS (
 
     IN_PART_ID DM_ID,
 
     IN_PART_ID DM_ID,
−
     IN_WARE_ID DM_UUID_NULL)
+
     IN_WARE_ID DM_UUID_NULL,
 +
    IN_DOC_ID DM_ID,
 +
    IN_AGENT_ID DM_ID)
 
as
 
as
 
declare variable PART_ID DM_ID not null;
 
declare variable PART_ID DM_ID not null;
Строка 154: Строка 313:
 
declare variable MNN DM_TEXT;
 
declare variable MNN DM_TEXT;
 
begin
 
begin
−
  if (:in_part_id is not null) then
+
 
−
  begin
+
  for select p.id, p.ware_id,
−
    for select
+
          vname.svalue, vizg.svalue, vcountry.svalue, w.orig_code, vorig_name.svalue, vorig_izg.svalue, vorig_country.svalue, w.barcode,
−
      PART_ID, WARE_ID, SNAME, SIZG, SCOUNTRY, ORIG_CODE, SORIG_NAME, SORIG_IZG, SORIG_COUNTRY, BCODE_IZG, PRICE, PRICE_O, PRICE_Z, PRICE_R, QUANT, BARCODE, BARCODE1, DEP, KRITK, GODENDO, SERIA, SUM_NDSO, SERT, DATESERT, KEMVSERT, SDSERT, REGN, NGTD, EDIZM, DOC_ID, DOCNUM, DOCDATE, DOCCAPTION, DOCAGENT, DOCVNUM, DOCVSHIFT, INSERTDT, UPDATEDT, ENDDT, NDS, REALQUANT, PART_PARENT_ID, NAME_ID, IZG_ID, COUNTRY_ID, NAC, BLOCK_QUANT, BLOCK_COUNT, MMBSH, PART_TYPE, BASE_AGENT_ID, SBASE_AGENT_ID, CONTRACT_ID, SCONTRACT_ID, GROUP_ID, ISFOLDER, MNN
+
          p.price, p.price_o, p.price_z, p.price_r, wb.quant, p.barcode, p.barcode1, p.dep,
−
    from vw_warebaseparts where part_id = :in_part_id and part_id>0
+
          (select intvalue from vals where id=wb.name_id),
 +
          p.godendo, p.seria, p.sum_ndso, p.sert, p.datesert, p.kemvsert, p.sdsert, p.regn, p.ngtd, p.edizm, p.doc_id,
 +
          d.docnum, d.docdate, d.caption, a.caption, d.vnum, d.vshift,
 +
          p.insertdt, wb.updatedt, p.enddt, p.nds, wb.realquant, p.parent_id,
 +
          w.name_id, w.izg_id, w.country_id,
 +
          p.nac,
 +
          wb.block_quant, wb.block_count,
 +
          (select membership from PR_MEMBERSHIPS('PARTS=' || p.id || ';PARTS.NAME_ID=' || w.name_id || ';PARTS.IZG_ID=' || w.izg_id ||';',ascii_char(13)||ascii_char(10),1)),--mmbsh,
 +
          p.part_type, p.base_agent_id,
 +
          (select ab.caption from agents ab where ab.id=p.BASE_AGENT_ID),
 +
          p.contract_id,
 +
          (select caption from contracts c where c.id=p.contract_id), --scontract_id,
 +
          p.group_id, 1, vname.preparedvalue
 +
      from parts p
 +
      inner join WARES w on p.ware_id=w.id
 +
      inner join docs d on p.doc_id=d.id
 +
      inner join agents a on a.id = d.agent_id
 +
      inner join vals vname on w.name_id=vname.id
 +
      inner join vals vizg on w.izg_id=vizg.id
 +
      inner join vals vcountry on w.country_id=vcountry.id
 +
      inner join vals vorig_name on w.orig_name_id=vorig_name.id
 +
      inner join vals vorig_izg on w.orig_izg_id=vorig_izg.id
 +
      inner join vals vorig_country on w.orig_country_id=vorig_country.id
 +
      left join warebase wb on p.id=wb.part_id
 +
      where (p.id>0) and ( (p.id = :IN_PART_ID or :IN_PART_ID is null) and (p.ware_id = :IN_WARE_ID or :IN_WARE_ID is null) and (p.doc_id = :IN_DOC_ID or :IN_DOC_ID is null) and (d.agent_id = :IN_AGENT_ID or :IN_AGENT_ID is null) )
 
     into
 
     into
 
       :PART_ID, :WARE_ID, :SNAME, :SIZG, :SCOUNTRY, :ORIG_CODE, :SORIG_NAME, :SORIG_IZG, :SORIG_COUNTRY, :BCODE_IZG, :PRICE, :PRICE_O, :PRICE_Z, :PRICE_R, :QUANT, :BARCODE, :BARCODE1, :DEP, :KRITK, :GODENDO, :SERIA, :SUM_NDSO, :SERT, :DATESERT, :KEMVSERT, :SDSERT, :REGN, :NGTD, :EDIZM, :DOC_ID, :DOCNUM, :DOCDATE, :DOCCAPTION, :DOCAGENT, :DOCVNUM, :DOCVSHIFT, :INSERTDT, :UPDATEDT, :ENDDT, :NDS, :REALQUANT, :PART_PARENT_ID, :NAME_ID, :IZG_ID, :COUNTRY_ID, :NAC, :BLOCK_QUANT, :BLOCK_COUNT, :MMBSH, :PART_TYPE, :BASE_AGENT_ID, :SBASE_AGENT_ID, :CONTRACT_ID, :SCONTRACT_ID, :GROUP_ID, :ISFOLDER, :MNN
 
       :PART_ID, :WARE_ID, :SNAME, :SIZG, :SCOUNTRY, :ORIG_CODE, :SORIG_NAME, :SORIG_IZG, :SORIG_COUNTRY, :BCODE_IZG, :PRICE, :PRICE_O, :PRICE_Z, :PRICE_R, :QUANT, :BARCODE, :BARCODE1, :DEP, :KRITK, :GODENDO, :SERIA, :SUM_NDSO, :SERT, :DATESERT, :KEMVSERT, :SDSERT, :REGN, :NGTD, :EDIZM, :DOC_ID, :DOCNUM, :DOCDATE, :DOCCAPTION, :DOCAGENT, :DOCVNUM, :DOCVSHIFT, :INSERTDT, :UPDATEDT, :ENDDT, :NDS, :REALQUANT, :PART_PARENT_ID, :NAME_ID, :IZG_ID, :COUNTRY_ID, :NAC, :BLOCK_QUANT, :BLOCK_COUNT, :MMBSH, :PART_TYPE, :BASE_AGENT_ID, :SBASE_AGENT_ID, :CONTRACT_ID, :SCONTRACT_ID, :GROUP_ID, :ISFOLDER, :MNN
Строка 169: Строка 352:
 
         ) matching (part_id);
 
         ) matching (part_id);
 
       end
 
       end
−
  end
+
end
−
 
+
^
−
  if (:in_ware_id is not null) then
+
−
  begin
+
−
    for select
+
−
      PART_ID, WARE_ID, SNAME, SIZG, SCOUNTRY, ORIG_CODE, SORIG_NAME, SORIG_IZG, SORIG_COUNTRY, BCODE_IZG, PRICE, PRICE_O, PRICE_Z, PRICE_R, QUANT, BARCODE, BARCODE1, DEP, KRITK, GODENDO, SERIA, SUM_NDSO, SERT, DATESERT, KEMVSERT, SDSERT, REGN, NGTD, EDIZM, DOC_ID, DOCNUM, DOCDATE, DOCCAPTION, DOCAGENT, DOCVNUM, DOCVSHIFT, INSERTDT, UPDATEDT, ENDDT, NDS, REALQUANT, PART_PARENT_ID, NAME_ID, IZG_ID, COUNTRY_ID, NAC, BLOCK_QUANT, BLOCK_COUNT, MMBSH, PART_TYPE, BASE_AGENT_ID, SBASE_AGENT_ID, CONTRACT_ID, SCONTRACT_ID, GROUP_ID, ISFOLDER, MNN
+
−
    from vw_warebaseparts where ware_id = :in_ware_id and part_id>0
+
−
    into
+
−
      :PART_ID, :WARE_ID, :SNAME, :SIZG, :SCOUNTRY, :ORIG_CODE, :SORIG_NAME, :SORIG_IZG, :SORIG_COUNTRY, :BCODE_IZG, :PRICE, :PRICE_O, :PRICE_Z, :PRICE_R, :QUANT, :BARCODE, :BARCODE1, :DEP, :KRITK, :GODENDO, :SERIA, :SUM_NDSO, :SERT, :DATESERT, :KEMVSERT, :SDSERT, :REGN, :NGTD, :EDIZM, :DOC_ID, :DOCNUM, :DOCDATE, :DOCCAPTION, :DOCAGENT, :DOCVNUM, :DOCVSHIFT, :INSERTDT, :UPDATEDT, :ENDDT, :NDS, :REALQUANT, :PART_PARENT_ID, :NAME_ID, :IZG_ID, :COUNTRY_ID, :NAC, :BLOCK_QUANT, :BLOCK_COUNT, :MMBSH, :PART_TYPE, :BASE_AGENT_ID, :SBASE_AGENT_ID, :CONTRACT_ID, :SCONTRACT_ID, :GROUP_ID, :ISFOLDER, :MNN
+
−
    do
+
−
      begin
+
−
        update or insert into warebaseparts (
+
−
          PART_ID, WARE_ID, SNAME, SIZG, SCOUNTRY, ORIG_CODE, SORIG_NAME, SORIG_IZG, SORIG_COUNTRY, BCODE_IZG, PRICE, PRICE_O, PRICE_Z, PRICE_R, QUANT, BARCODE, BARCODE1, DEP, KRITK, GODENDO, SERIA, SUM_NDSO, SERT, DATESERT, KEMVSERT, SDSERT, REGN, NGTD, EDIZM, DOC_ID, DOCNUM, DOCDATE, DOCCAPTION, DOCAGENT, DOCVNUM, DOCVSHIFT, INSERTDT, UPDATEDT, ENDDT, NDS, REALQUANT, PART_PARENT_ID, NAME_ID, IZG_ID, COUNTRY_ID, NAC, BLOCK_QUANT, BLOCK_COUNT, MMBSH, PART_TYPE, BASE_AGENT_ID, SBASE_AGENT_ID, CONTRACT_ID, SCONTRACT_ID, GROUP_ID, ISFOLDER, MNN
+
−
        ) values (
+
−
          :PART_ID, :WARE_ID, :SNAME, :SIZG, :SCOUNTRY, :ORIG_CODE, :SORIG_NAME, :SORIG_IZG, :SORIG_COUNTRY, :BCODE_IZG, :PRICE, :PRICE_O, :PRICE_Z, :PRICE_R, :QUANT, :BARCODE, :BARCODE1, :DEP, :KRITK, :GODENDO, :SERIA, :SUM_NDSO, :SERT, :DATESERT, :KEMVSERT, :SDSERT, :REGN, :NGTD, :EDIZM, :DOC_ID, :DOCNUM, :DOCDATE, :DOCCAPTION, :DOCAGENT, :DOCVNUM, :DOCVSHIFT, :INSERTDT, :UPDATEDT, :ENDDT, :NDS, :REALQUANT, :PART_PARENT_ID, :NAME_ID, :IZG_ID, :COUNTRY_ID, :NAC, :BLOCK_QUANT, :BLOCK_COUNT, :MMBSH, :PART_TYPE, :BASE_AGENT_ID, :SBASE_AGENT_ID, :CONTRACT_ID, :SCONTRACT_ID, :GROUP_ID, :ISFOLDER, :MNN
+
−
        ) matching (part_id);
+
−
      end
+
−
  end
+
−
 
+
−
  --suspend;
+
−
end^
+
  
 
SET TERM ; ^
 
SET TERM ; ^
Строка 195: Строка 359:
 
/* Following GRANT statetements are generated automatically */
 
/* Following GRANT statetements are generated automatically */
  
−
GRANT SELECT ON VW_WAREBASEPARTS TO PROCEDURE UPDPR_WAREBASEPARTS;
+
GRANT SELECT ON VALS TO PROCEDURE UPDPR_WAREBASEPARTS;
 +
GRANT EXECUTE ON PROCEDURE PR_MEMBERSHIPS TO PROCEDURE UPDPR_WAREBASEPARTS;
 +
GRANT SELECT ON AGENTS TO PROCEDURE UPDPR_WAREBASEPARTS;
 +
GRANT SELECT ON CONTRACTS TO PROCEDURE UPDPR_WAREBASEPARTS;
 +
GRANT SELECT ON PARTS TO PROCEDURE UPDPR_WAREBASEPARTS;
 +
GRANT SELECT ON WARES TO PROCEDURE UPDPR_WAREBASEPARTS;
 +
GRANT SELECT ON DOCS TO PROCEDURE UPDPR_WAREBASEPARTS;
 +
GRANT SELECT ON WAREBASE TO PROCEDURE UPDPR_WAREBASEPARTS;
 
GRANT SELECT,INSERT,UPDATE ON WAREBASEPARTS TO PROCEDURE UPDPR_WAREBASEPARTS;
 
GRANT SELECT,INSERT,UPDATE ON WAREBASEPARTS TO PROCEDURE UPDPR_WAREBASEPARTS;
  
Строка 201: Строка 372:
  
 
GRANT EXECUTE ON PROCEDURE UPDPR_WAREBASEPARTS TO SYSDBA;
 
GRANT EXECUTE ON PROCEDURE UPDPR_WAREBASEPARTS TO SYSDBA;
−
</pre>
+
 
 +
SET SQL DIALECT 3;
 +
 
 +
 
 +
SET TERM ^ ;
 +
 
 +
 
 +
 
 +
CREATE OR ALTER TRIGGER AGENTS_WP_AIU0 FOR AGENTS
 +
ACTIVE AFTER INSERT OR UPDATE POSITION 0
 +
AS
 +
begin
 +
execute procedure updpr_warebaseparts(null, null, null, new.id);
 +
end
 +
^
 +
 
 +
 
 +
SET TERM ; ^
  
  
−
==Триггеры==
 
−
<pre>
 
−
/******************************************************************************/
 
−
/***      Following SET SQL DIALECT is just for the Database Comparer      ***/
 
−
/******************************************************************************/
 
 
SET SQL DIALECT 3;
 
SET SQL DIALECT 3;
 +
 +
 
SET TERM ^ ;
 
SET TERM ^ ;
 +
 +
 +
 +
CREATE OR ALTER TRIGGER DOCS_WP_AIU0 FOR DOCS
 +
ACTIVE AFTER INSERT OR UPDATE POSITION 0
 +
AS
 +
begin
 +
  execute procedure updpr_warebaseparts(null, null, new.id, null);
 +
end
 +
^
 +
 +
 +
SET TERM ; ^
 +
 +
 +
SET SQL DIALECT 3;
 +
 +
 +
SET TERM ^ ;
 +
 +
 +
 
CREATE OR ALTER TRIGGER PARTS_WP_AIU0 FOR PARTS
 
CREATE OR ALTER TRIGGER PARTS_WP_AIU0 FOR PARTS
 
ACTIVE AFTER INSERT OR UPDATE POSITION 0
 
ACTIVE AFTER INSERT OR UPDATE POSITION 0
 
AS
 
AS
 
begin
 
begin
−
   execute procedure updpr_warebaseparts(new.id,null);
+
   execute procedure updpr_warebaseparts(new.id,null,null,null);
 
end
 
end
 
^
 
^
 +
 +
 
SET TERM ; ^
 
SET TERM ; ^
  
−
/******************************************************************************/
+
 
−
/***      Following SET SQL DIALECT is just for the Database Comparer      ***/
+
−
/******************************************************************************/
+
 
SET SQL DIALECT 3;
 
SET SQL DIALECT 3;
 +
 +
 
SET TERM ^ ;
 
SET TERM ^ ;
 +
 +
 +
 
CREATE OR ALTER TRIGGER WARES_WP_AIU0 FOR WARES
 
CREATE OR ALTER TRIGGER WARES_WP_AIU0 FOR WARES
 
ACTIVE AFTER INSERT OR UPDATE POSITION 0
 
ACTIVE AFTER INSERT OR UPDATE POSITION 0
 
AS
 
AS
 
begin
 
begin
−
   execute procedure updpr_warebaseparts(null,new.id);
+
   execute procedure updpr_warebaseparts(null,new.id,null,null);
 
end
 
end
 
^
 
^
 +
 +
 
SET TERM ; ^
 
SET TERM ; ^
−
</pre>
 
  
  
−
==Заполнение таблицы==
+
execute procedure updpr_warebaseparts(null,null,null,null);
−
<pre>
+
−
update parts p set p.id=p.id where p.id>0
+
−
</pre>
+

Версия 13:22, 8 марта 2016

Выполнить скрипт

/******************************************************************************/ /*** Generated by IBExpert 08.03.2016 11:37:13 ***/ /******************************************************************************/

/******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3;


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


CREATE TABLE WAREBASEPARTS ( 

   PART_ID         DM_ID NOT NULL /* DM_ID = BIGINT */,
   WARE_ID         DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */,
   SNAME           DM_TEXT /* DM_TEXT = VARCHAR(250) */,
   SIZG            DM_TEXT /* DM_TEXT = VARCHAR(250) */,
   SCOUNTRY        DM_TEXT /* DM_TEXT = VARCHAR(250) */,
   ORIG_CODE       DM_TEXT /* DM_TEXT = VARCHAR(250) */,
   SORIG_NAME      DM_TEXT /* DM_TEXT = VARCHAR(250) */,
   SORIG_IZG       DM_TEXT /* DM_TEXT = VARCHAR(250) */,
   SORIG_COUNTRY   DM_TEXT /* DM_TEXT = VARCHAR(250) */,
   BCODE_IZG       DM_TEXT /* DM_TEXT = VARCHAR(250) */,
   PRICE           DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
   PRICE_O         DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
   PRICE_Z         DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
   PRICE_R         DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
   QUANT           DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
   BARCODE         DM_TEXT /* DM_TEXT = VARCHAR(250) */,
   BARCODE1        DM_TEXT /* DM_TEXT = VARCHAR(250) */,
   DEP             DM_ID /* DM_ID = BIGINT */,
   KRITK           DM_ID /* DM_ID = BIGINT */,
   GODENDO         DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
   SERIA           DM_TEXT /* DM_TEXT = VARCHAR(250) */,
   SUM_NDSO        DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
   SERT            DM_TEXT /* DM_TEXT = VARCHAR(250) */,
   DATESERT        DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
   KEMVSERT        DM_TEXT /* DM_TEXT = VARCHAR(250) */,
   SDSERT          DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
   REGN            DM_TEXT /* DM_TEXT = VARCHAR(250) */,
   NGTD            DM_TEXT /* DM_TEXT = VARCHAR(250) */,
   EDIZM           DM_TEXT /* DM_TEXT = VARCHAR(250) */,
   DOC_ID          DM_ID /* DM_ID = BIGINT */,
   DOCNUM          DM_TEXT /* DM_TEXT = VARCHAR(250) */,
   DOCDATE         DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
   DOCCAPTION      DM_TEXT /* DM_TEXT = VARCHAR(250) */,
   DOCAGENT        DM_TEXT /* DM_TEXT = VARCHAR(250) */,
   DOCVNUM         DM_ID /* DM_ID = BIGINT */,
   DOCVSHIFT       DM_ID /* DM_ID = BIGINT */,
   INSERTDT        DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
   UPDATEDT        DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
   ENDDT           DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
   NDS             DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
   REALQUANT       DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
   PART_PARENT_ID  DM_ID /* DM_ID = BIGINT */,
   NAME_ID         DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */,
   IZG_ID          DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */,
   COUNTRY_ID      DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */,
   NAC             DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
   BLOCK_QUANT     DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
   BLOCK_COUNT     DM_STATUS /* DM_STATUS = INTEGER */,
   MMBSH           DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
   PART_TYPE       DM_STATUS /* DM_STATUS = INTEGER */,
   BASE_AGENT_ID   DM_ID /* DM_ID = BIGINT */,
   SBASE_AGENT_ID  DM_TEXT /* DM_TEXT = VARCHAR(250) */,
   CONTRACT_ID     DM_ID /* DM_ID = BIGINT */,
   SCONTRACT_ID    DM_TEXT /* DM_TEXT = VARCHAR(250) */,
   GROUP_ID        DM_ID /* DM_ID = BIGINT */,
   ISFOLDER        DM_STATUS /* DM_STATUS = INTEGER */,
   MNN             DM_TEXT /* DM_TEXT = VARCHAR(250) */

);



/******************************************************************************/ /*** Primary Keys ***/ /******************************************************************************/

ALTER TABLE WAREBASEPARTS ADD CONSTRAINT PK_WAREBASEPARTS PRIMARY KEY (PART_ID);


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

CREATE INDEX WAREBASEPARTS_IDX1 ON WAREBASEPARTS (BCODE_IZG); CREATE INDEX WAREBASEPARTS_IDX2 ON WAREBASEPARTS (SNAME); CREATE INDEX WAREBASEPARTS_IDX3 ON WAREBASEPARTS (SERIA); CREATE INDEX WAREBASEPARTS_IDX4 ON WAREBASEPARTS (SORIG_NAME); CREATE INDEX WAREBASEPARTS_IDX5 ON WAREBASEPARTS (DOCAGENT);


/******************************************************************************/ /*** Privileges ***/ /******************************************************************************/


/******************************************************************************/

/******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3;


/******************************************************************************/ /*** Views ***/ /******************************************************************************/


/* View: VW_WAREBASEPARTS */ CREATE OR ALTER VIEW VW_WAREBASEPARTS( 

   PART_ID,
   WARE_ID,
   SNAME,
   SIZG,
   SCOUNTRY,
   ORIG_CODE,
   SORIG_NAME,
   SORIG_IZG,
   SORIG_COUNTRY,
   BCODE_IZG,
   PRICE,
   PRICE_O,
   PRICE_Z,
   PRICE_R,
   QUANT,
   BARCODE,
   BARCODE1,
   DEP,
   KRITK,
   GODENDO,
   SERIA,
   SUM_NDSO,
   SERT,
   DATESERT,
   KEMVSERT,
   SDSERT,
   REGN,
   NGTD,
   EDIZM,
   DOC_ID,
   DOCNUM,
   DOCDATE,
   DOCCAPTION,
   DOCAGENT,
   DOCVNUM,
   DOCVSHIFT,
   INSERTDT,
   UPDATEDT,
   ENDDT,
   NDS,
   REALQUANT,
   PART_PARENT_ID,
   NAME_ID,
   IZG_ID,
   COUNTRY_ID,
   NAC,
   BLOCK_QUANT,
   BLOCK_COUNT,
   MMBSH,
   PART_TYPE,
   BASE_AGENT_ID,
   SBASE_AGENT_ID,
   CONTRACT_ID,
   SCONTRACT_ID,
   GROUP_ID,
   ISFOLDER,
   MNN)

AS select 

   PART_ID,
   WARE_ID,
   SNAME,
   SIZG,
   SCOUNTRY,
   ORIG_CODE,
   SORIG_NAME,
   SORIG_IZG,
   SORIG_COUNTRY,
   BCODE_IZG,
   PRICE,
   PRICE_O,
   PRICE_Z,
   PRICE_R,
   QUANT,
   BARCODE,
   BARCODE1,
   DEP,
   KRITK,
   GODENDO,
   SERIA,
   SUM_NDSO,
   SERT,
   DATESERT,
   KEMVSERT,
   SDSERT,
   REGN,
   NGTD,
   EDIZM,
   DOC_ID,
   DOCNUM,
   DOCDATE,
   DOCCAPTION,
   DOCAGENT,
   DOCVNUM,
   DOCVSHIFT,
   INSERTDT,
   UPDATEDT,
   ENDDT,
   NDS,
   REALQUANT,
   PART_PARENT_ID,
   NAME_ID,
   IZG_ID,
   COUNTRY_ID,
   NAC,
   BLOCK_QUANT,
   BLOCK_COUNT,
   MMBSH,
   PART_TYPE,
   BASE_AGENT_ID,
   SBASE_AGENT_ID,
   CONTRACT_ID,
   SCONTRACT_ID,
   GROUP_ID,
   ISFOLDER,
   MNN

from WAREBASEPARTS



/******************************************************************************/ /*** Privileges ***/ /******************************************************************************/


SET TERM ^ ;

create or alter procedure UPDPR_WAREBASEPARTS ( 

   IN_PART_ID DM_ID,
   IN_WARE_ID DM_UUID_NULL,
   IN_DOC_ID DM_ID,
   IN_AGENT_ID DM_ID)

as declare variable PART_ID DM_ID not null; declare variable WARE_ID DM_UUID not null; declare variable SNAME DM_TEXT; declare variable SIZG DM_TEXT; declare variable SCOUNTRY DM_TEXT; declare variable ORIG_CODE DM_TEXT; declare variable SORIG_NAME DM_TEXT; declare variable SORIG_IZG DM_TEXT; declare variable SORIG_COUNTRY DM_TEXT; declare variable BCODE_IZG DM_TEXT; declare variable PRICE DM_DOUBLE; declare variable PRICE_O DM_DOUBLE; declare variable PRICE_Z DM_DOUBLE; declare variable PRICE_R DM_DOUBLE; declare variable QUANT DM_DOUBLE; declare variable BARCODE DM_TEXT; declare variable BARCODE1 DM_TEXT; declare variable DEP DM_ID; declare variable KRITK DM_ID; declare variable GODENDO DM_DATETIME; declare variable SERIA DM_TEXT; declare variable SUM_NDSO DM_DOUBLE; declare variable SERT DM_TEXT; declare variable DATESERT DM_DATETIME; declare variable KEMVSERT DM_TEXT; declare variable SDSERT DM_DATETIME; declare variable REGN DM_TEXT; declare variable NGTD DM_TEXT; declare variable EDIZM DM_TEXT; declare variable DOC_ID DM_ID; declare variable DOCNUM DM_TEXT; declare variable DOCDATE DM_DATETIME; declare variable DOCCAPTION DM_TEXT; declare variable DOCAGENT DM_TEXT; declare variable DOCVNUM DM_ID; declare variable DOCVSHIFT DM_ID; declare variable INSERTDT DM_DATETIME; declare variable UPDATEDT DM_DATETIME; declare variable ENDDT DM_DATETIME; declare variable NDS DM_DOUBLE; declare variable REALQUANT DM_DOUBLE; declare variable PART_PARENT_ID DM_ID; declare variable NAME_ID DM_UUID_NULL not null; declare variable IZG_ID DM_UUID_NULL not null; declare variable COUNTRY_ID DM_UUID_NULL not null; declare variable NAC DM_DOUBLE; declare variable BLOCK_QUANT DM_DOUBLE; declare variable BLOCK_COUNT DM_STATUS; declare variable MMBSH DM_TEXT1024; declare variable PART_TYPE DM_STATUS; declare variable BASE_AGENT_ID DM_ID; declare variable SBASE_AGENT_ID DM_TEXT; declare variable CONTRACT_ID DM_ID; declare variable SCONTRACT_ID DM_TEXT; declare variable GROUP_ID DM_ID; declare variable ISFOLDER DM_STATUS; declare variable MNN DM_TEXT; begin 

  for select p.id, p.ware_id,
          vname.svalue, vizg.svalue, vcountry.svalue, w.orig_code, vorig_name.svalue, vorig_izg.svalue, vorig_country.svalue, w.barcode,
          p.price, p.price_o, p.price_z, p.price_r, wb.quant, p.barcode, p.barcode1, p.dep,
          (select intvalue from vals where id=wb.name_id),
          p.godendo, p.seria, p.sum_ndso, p.sert, p.datesert, p.kemvsert, p.sdsert, p.regn, p.ngtd, p.edizm, p.doc_id,
          d.docnum, d.docdate, d.caption, a.caption, d.vnum, d.vshift,
          p.insertdt, wb.updatedt, p.enddt, p.nds, wb.realquant, p.parent_id,
          w.name_id, w.izg_id, w.country_id,
          p.nac,
          wb.block_quant, wb.block_count,
          (select membership from PR_MEMBERSHIPS('PARTS=' || p.id || ';PARTS.NAME_ID=' || w.name_id || ';PARTS.IZG_ID=' || w.izg_id ||';',ascii_char(13)||ascii_char(10),1)),--mmbsh,
          p.part_type, p.base_agent_id,
          (select ab.caption from agents ab where ab.id=p.BASE_AGENT_ID),
          p.contract_id,
          (select caption from contracts c where c.id=p.contract_id), --scontract_id,
          p.group_id, 1, vname.preparedvalue
     from parts p
      inner join WARES w on p.ware_id=w.id
      inner join docs d on p.doc_id=d.id
      inner join agents a on a.id = d.agent_id
      inner join vals vname on w.name_id=vname.id
      inner join vals vizg on w.izg_id=vizg.id
      inner join vals vcountry on w.country_id=vcountry.id
      inner join vals vorig_name on w.orig_name_id=vorig_name.id
      inner join vals vorig_izg on w.orig_izg_id=vorig_izg.id
      inner join vals vorig_country on w.orig_country_id=vorig_country.id
      left join warebase wb on p.id=wb.part_id
     where (p.id>0) and ( (p.id = :IN_PART_ID or :IN_PART_ID is null) and (p.ware_id = :IN_WARE_ID or :IN_WARE_ID is null) and (p.doc_id = :IN_DOC_ID or :IN_DOC_ID is null) and (d.agent_id = :IN_AGENT_ID or :IN_AGENT_ID is null) )
   into
     :PART_ID, :WARE_ID, :SNAME, :SIZG, :SCOUNTRY, :ORIG_CODE, :SORIG_NAME, :SORIG_IZG, :SORIG_COUNTRY, :BCODE_IZG, :PRICE, :PRICE_O, :PRICE_Z, :PRICE_R, :QUANT, :BARCODE, :BARCODE1, :DEP, :KRITK, :GODENDO, :SERIA, :SUM_NDSO, :SERT, :DATESERT, :KEMVSERT, :SDSERT, :REGN, :NGTD, :EDIZM, :DOC_ID, :DOCNUM, :DOCDATE, :DOCCAPTION, :DOCAGENT, :DOCVNUM, :DOCVSHIFT, :INSERTDT, :UPDATEDT, :ENDDT, :NDS, :REALQUANT, :PART_PARENT_ID, :NAME_ID, :IZG_ID, :COUNTRY_ID, :NAC, :BLOCK_QUANT, :BLOCK_COUNT, :MMBSH, :PART_TYPE, :BASE_AGENT_ID, :SBASE_AGENT_ID, :CONTRACT_ID, :SCONTRACT_ID, :GROUP_ID, :ISFOLDER, :MNN
   do
     begin
       update or insert into warebaseparts (
        PART_ID, WARE_ID, SNAME, SIZG, SCOUNTRY, ORIG_CODE, SORIG_NAME, SORIG_IZG, SORIG_COUNTRY, BCODE_IZG, PRICE, PRICE_O, PRICE_Z, PRICE_R, QUANT, BARCODE, BARCODE1, DEP, KRITK, GODENDO, SERIA, SUM_NDSO, SERT, DATESERT, KEMVSERT, SDSERT, REGN, NGTD, EDIZM, DOC_ID, DOCNUM, DOCDATE, DOCCAPTION, DOCAGENT, DOCVNUM, DOCVSHIFT, INSERTDT, UPDATEDT, ENDDT, NDS, REALQUANT, PART_PARENT_ID, NAME_ID, IZG_ID, COUNTRY_ID, NAC, BLOCK_QUANT, BLOCK_COUNT, MMBSH, PART_TYPE, BASE_AGENT_ID, SBASE_AGENT_ID, CONTRACT_ID, SCONTRACT_ID, GROUP_ID, ISFOLDER, MNN
       ) values (
         :PART_ID, :WARE_ID, :SNAME, :SIZG, :SCOUNTRY, :ORIG_CODE, :SORIG_NAME, :SORIG_IZG, :SORIG_COUNTRY, :BCODE_IZG, :PRICE, :PRICE_O, :PRICE_Z, :PRICE_R, :QUANT, :BARCODE, :BARCODE1, :DEP, :KRITK, :GODENDO, :SERIA, :SUM_NDSO, :SERT, :DATESERT, :KEMVSERT, :SDSERT, :REGN, :NGTD, :EDIZM, :DOC_ID, :DOCNUM, :DOCDATE, :DOCCAPTION, :DOCAGENT, :DOCVNUM, :DOCVSHIFT, :INSERTDT, :UPDATEDT, :ENDDT, :NDS, :REALQUANT, :PART_PARENT_ID, :NAME_ID, :IZG_ID, :COUNTRY_ID, :NAC, :BLOCK_QUANT, :BLOCK_COUNT, :MMBSH, :PART_TYPE, :BASE_AGENT_ID, :SBASE_AGENT_ID, :CONTRACT_ID, :SCONTRACT_ID, :GROUP_ID, :ISFOLDER, :MNN
       ) matching (part_id);
     end

end ^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT ON VALS TO PROCEDURE UPDPR_WAREBASEPARTS; GRANT EXECUTE ON PROCEDURE PR_MEMBERSHIPS TO PROCEDURE UPDPR_WAREBASEPARTS; GRANT SELECT ON AGENTS TO PROCEDURE UPDPR_WAREBASEPARTS; GRANT SELECT ON CONTRACTS TO PROCEDURE UPDPR_WAREBASEPARTS; GRANT SELECT ON PARTS TO PROCEDURE UPDPR_WAREBASEPARTS; GRANT SELECT ON WARES TO PROCEDURE UPDPR_WAREBASEPARTS; GRANT SELECT ON DOCS TO PROCEDURE UPDPR_WAREBASEPARTS; GRANT SELECT ON WAREBASE TO PROCEDURE UPDPR_WAREBASEPARTS; GRANT SELECT,INSERT,UPDATE ON WAREBASEPARTS TO PROCEDURE UPDPR_WAREBASEPARTS;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE UPDPR_WAREBASEPARTS TO SYSDBA;

SET SQL DIALECT 3;


SET TERM ^ ;


CREATE OR ALTER TRIGGER AGENTS_WP_AIU0 FOR AGENTS ACTIVE AFTER INSERT OR UPDATE POSITION 0 AS begin 

execute procedure updpr_warebaseparts(null, null, null, new.id);

end ^


SET TERM ; ^


SET SQL DIALECT 3;


SET TERM ^ ;


CREATE OR ALTER TRIGGER DOCS_WP_AIU0 FOR DOCS ACTIVE AFTER INSERT OR UPDATE POSITION 0 AS begin 

 execute procedure updpr_warebaseparts(null, null, new.id, null);

end ^


SET TERM ; ^


SET SQL DIALECT 3;


SET TERM ^ ;


CREATE OR ALTER TRIGGER PARTS_WP_AIU0 FOR PARTS ACTIVE AFTER INSERT OR UPDATE POSITION 0 AS begin 

 execute procedure updpr_warebaseparts(new.id,null,null,null);

end ^


SET TERM ; ^


SET SQL DIALECT 3;


SET TERM ^ ;


CREATE OR ALTER TRIGGER WARES_WP_AIU0 FOR WARES ACTIVE AFTER INSERT OR UPDATE POSITION 0 AS begin 

 execute procedure updpr_warebaseparts(null,new.id,null,null);

end ^


SET TERM ; ^


execute procedure updpr_warebaseparts(null,null,null,null);

Источник — «http://wiki.standart-n.ru/index.php?title=Ускорение_режима_все_партии&oldid=3610»