Ускорение режима все партии

Материал из wiki.standart-n.ru
Перейти к: навигация, поиск

I вариант

/******************************************************************************/
/***               Generated by IBExpert 07.03.2016 15:48:01                ***/
/******************************************************************************/

/******************************************************************************/
/***      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);


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

SET TERM ^ ;

create or alter procedure UPDPR_WAREBASEPARTS (
    IN_PART_ID DM_ID,
    IN_WARE_ID DM_UUID_NULL)
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 not null;
declare variable IZG_ID DM_UUID not null;
declare variable COUNTRY_ID DM_UUID 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
  if (:in_part_id is not null) then
  begin
    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 part_id = :in_part_id
    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;

    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

  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
    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 ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT ON VW_WAREBASEPARTS 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;


/******************************************************************************/
/***      Following SET SQL DIALECT is just for the Database Comparer       ***/
/******************************************************************************/
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);
end
^


SET TERM ; ^


/******************************************************************************/
/***      Following SET SQL DIALECT is just for the Database Comparer       ***/
/******************************************************************************/
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);
end
^


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