Сжатие товаров — различия между версиями

Текущая версия на 14:36, 14 июля 2016

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

/* 1) Накатываем индексы, чтобы выполнялось быстрее */
CREATE INDEX DOC_DETAIL_ACTIVE_CANCELED_IDX3 ON DOC_DETAIL_ACTIVE_CANCELED (WARE_ID);
CREATE INDEX DOC_DETAIL_ACTIVE_NAMEID_IDX5 ON DOC_DETAIL_ACTIVE_NAMEID (WARE_ID);
CREATE INDEX DOC_DETAIL_DELETED_IDX8 ON DOC_DETAIL_DELETED (WARE_ID);
CREATE INDEX DOC_DETAIL_ACTIVE_IDX12 ON DOC_DETAIL_ACTIVE (WARE_ID);

/* 2) Делаем WARES_IDX1 не уникальным */
DROP INDEX WARES_IDX1;
CREATE INDEX WARES_IDX1 ON WARES (NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE);

/* 3) Правим UPDPR_REPARWARES */

SET TERM ^ ;

create or alter procedure UPDPR_REPARWARES
returns (
    ID DM_TEXT)
as
declare variable NAME_ID DM_TEXT;
declare variable IZG_ID DM_TEXT;
declare variable COUNTRY_ID DM_TEXT;
declare variable ORIG_CODE DM_TEXT;
declare variable ORIG_NAME_ID DM_TEXT;
declare variable ORIG_IZG_ID DM_TEXT;
declare variable ORIG_COUNTRY_ID DM_TEXT;
declare variable T_ID DM_TEXT;
declare variable BARCODE DM_TEXT;
begin
delete from DOC_DETAIL_ACTIVE_LOG;
for select min(id), NAME_ID, IZG_ID, COUNTRY_ID, BARCODE from wares
group by NAME_ID, IZG_ID, COUNTRY_ID, BARCODE
having count(1)>1
into id, NAME_ID,IZG_ID,COUNTRY_ID,BARCODE do
begin
for
select id from wares where NAME_ID=:NAME_ID and IZG_ID=:IZG_ID and COUNTRY_ID=:COUNTRY_ID and BARCODE=:BARCODE and id<>:id into :t_id do
begin
update DOC_DETAIL_ACTIVE set ware_id=:id where ware_id=:t_id;
update DOC_DETAIL_ACTIVE_CANCELED set ware_id=:id where ware_id=:t_id;
update DOC_DETAIL_ACTIVE_NAMEID set ware_id=:id where ware_id=:t_id;
/* update DOC_DETAIL_ACTIVE_TREB set ware_id=:id where ware_id=:t_id; */
update DOC_DETAIL_DELETED set ware_id=:id where ware_id=:t_id;
update OUT$ZAKAZ set ware_id=:id where ware_id=:t_id;
update PARTS set ware_id=:id where ware_id=:t_id;
update PARTS_LOG set ware_id=:id where ware_id=:t_id;
update WAREBASE set ware_id=:id where ware_id=:t_id;
update WAREBASEFOLDERS set ware_id=:id where ware_id=:t_id;
update WAREBASENAMEID set ware_id=:id where ware_id=:t_id;
delete from wares where id=:t_id;
end

end
/* suspend; */
end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT,DELETE ON DOC_DETAIL_ACTIVE_LOG TO PROCEDURE UPDPR_REPARWARES;
GRANT SELECT,DELETE ON WARES TO PROCEDURE UPDPR_REPARWARES;
GRANT SELECT,UPDATE ON DOC_DETAIL_ACTIVE TO PROCEDURE UPDPR_REPARWARES;
GRANT SELECT,UPDATE ON DOC_DETAIL_ACTIVE_CANCELED TO PROCEDURE UPDPR_REPARWARES;
GRANT SELECT,UPDATE ON DOC_DETAIL_ACTIVE_NAMEID TO PROCEDURE UPDPR_REPARWARES;
GRANT SELECT,UPDATE ON DOC_DETAIL_ACTIVE_TREB TO PROCEDURE UPDPR_REPARWARES;
GRANT SELECT,UPDATE ON DOC_DETAIL_DELETED TO PROCEDURE UPDPR_REPARWARES;
GRANT SELECT,UPDATE ON OUT$ZAKAZ TO PROCEDURE UPDPR_REPARWARES;
GRANT SELECT,UPDATE ON PARTS TO PROCEDURE UPDPR_REPARWARES;
GRANT SELECT,UPDATE ON PARTS_LOG TO PROCEDURE UPDPR_REPARWARES;
GRANT SELECT,UPDATE ON WAREBASE TO PROCEDURE UPDPR_REPARWARES;
GRANT SELECT,UPDATE ON WAREBASEFOLDERS TO PROCEDURE UPDPR_REPARWARES;
GRANT SELECT,UPDATE ON WAREBASENAMEID TO PROCEDURE UPDPR_REPARWARES;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE UPDPR_REPARWARES TO SYSDBA;

/* 4) Сжимаем WARES и создаем WARES_IDXU */
EXECUTE PROCEDURE UPDPR_REPARWARES;
CREATE UNIQUE INDEX WARES_IDXU ON WARES (NAME_ID,IZG_ID,COUNTRY_ID,BARCODE);