Psst.. new poll here.
[email protected] webmail now available. Want one? Go here.
Cannot use outlook/hotmail/live here to register as they blocking our mail servers. #microsoftdeez
Obey the Epel!
Paste
Pasted by iceman ( 14 years ago )
procedure runImportKladr
is
vJob number;
vSql varchar2(4000);
begin
vSql := 'BEGIN CP$KLADR.importKladr(pJob=>:APP_JOB); END;';
vJob := APEX_PLSQL_JOB.SUBMIT_PROCESS(p_sql => vSql);
insert into kladr_import values(vJob, v('APP_USER'), sysdate, null);
end;
/*
*
* Процедура импорта классификатора адресов КЛАДР
* возможен запуск используя пакет APEX_PLSQL_JOB
*
*/
procedure importKladr(pJob in number default null) is
vStatus varchar2(4000);
/* cursor for constraints */
cursor cConstraints(pConstraintStatus in varchar2) is
select t.* from all_constraints t
where t.R_CONSTRAINT_NAME = 'KLADR_PK'
and t.status = upper(pConstraintStatus)
and t.CONSTRAINT_TYPE = 'R';
/* alter constraint */
procedure alterConstraint(
pTableName in varchar2,
pConstraintName in varchar2,
pStatus in varchar2) is
begin
execute immediate 'alter table '||pTableName||' '||pStatus||' constraint '||pConstraintName;
end;
/* UPDATE JOB STATUS */
procedure setStatus(pStatus in varchar2, err in boolean default false) is
begin
if pJob is not null then
if err then
setStatus('ERROR ON "' || vStatus || '"');
else
APEX_PLSQL_JOB.UPDATE_JOB_STATUS(
p_job => pJob,
p_status => pStatus);
vStatus := pStatus;
end if;
end if;
end;
/* Исполнение инструкции без прерывания программы */
procedure execDDL(pCode in varchar2) is
begin
execute immediate pCode;
exception when others then
null;
end;
begin
/* set started status */
setStatus(pStatus => 'IMPORT STARTED');
/* import SOCRBASE -> KLADR_DISTRICT_TYPE */
declare
vMesg blob;
begin
execDDL('drop index KLADR_DISTRICT_TYPE_ABBR_XIB');
setStatus(pStatus => 'INSTALL SOCRBASE.DBF: LOAD INTO TABLE');
vMesg := getImportFileInfo('SOCRBASE.DBF').blob_content;
execute immediate 'truncate table KLADR_DISTRICT_TYPE_TEMP';
dbase_pkg.load_table(vMesg, 'KLADR_DISTRICT_TYPE_TEMP');
commit;
execDDL('create index KLADR_DISTRICT_TYPE_ABBR_XIB on KLADR_DISTRICT_TYPE (abbreviation)');
end;
/* import KLADR -> KLADR_TEMP */
declare
vMesg blob;
begin
/* drop index */
setStatus(pStatus => 'INSTALL KLADR.DBF: DROP INDEX');
execDDL('drop index KLADR_TEMP_SOCR_IDX');
execDDL('drop index KLADR_TEMP_CODE_IDX');
execDDL('drop index KLADR_TEMP_CODE_BIDX');
execDDL('drop index KLADR_TEMP_CODE_B2IDX');
execDDL('drop index KLADR_TEMP_CODE_B3IDX');
setStatus(pStatus => 'INSTALL KLADR.DBF: LOAD INTO TABLE');
vMesg := getImportFileInfo('KLADR.DBF').blob_content;
execute immediate 'truncate table KLADR_TEMP';
dbase_pkg.load_table(vMesg, 'KLADR_TEMP');
commit;
/* create index */
setStatus(pStatus => 'INSTALL KLADR.DBF: CREATE NEW INDEX');
execDDL('create unique index KLADR_TEMP_CODE_IDX on KLADR_TEMP (code)');
execDDL('create bitmap index KLADR_TEMP_CODE_BIDX on KLADR_TEMP (substr(code, 12, 2))');
execDDL('create bitmap index KLADR_TEMP_CODE_B2IDX on KLADR_TEMP (substr(code, 6, 2))');
execDDL('create bitmap index KLADR_TEMP_CODE_B3IDX on KLADR_TEMP (substr(code, 9, 2))');
execDDL('create index KLADR_TEMP_SOCR_IDX on KLADR_TEMP (socr)');
end;
/* import STREET -> KLADR_STREET */
declare
vMesg blob;
begin
/* drop index */
setStatus(pStatus => 'INSTALL STREET.DBF: DROP INDEX');
execDDL('drop index KLADR_STREET_SOCR_IDX');
execDDL('drop index KLADR_STREET_CODE_IDX');
execDDL('drop index KLADR_STREET_CODE_BIDX');
setStatus(pStatus => 'INSTALL STREET.DBF: LOAD INTO TABLE');
vMesg := getImportFileInfo('STREET.DBF').blob_content;
execute immediate 'truncate table KLADR_STREET';
dbase_pkg.load_table(vMesg, 'KLADR_STREET');
commit;
/* create index */
setStatus(pStatus => 'INSTALL STREET.DBF: CREATE NEW INDEX');
execDDL('create unique index KLADR_STREET_CODE_IDX on KLADR_STREET (code)');
execDDL('create bitmap index KLADR_STREET_CODE_BIDX on KLADR_STREET (substr(code, 16, 2))');
execDDL('create index KLADR_STREET_SOCR_IDX on KLADR_STREET (socr)');
/* добавляем поля для оптимизации выборки */
setStatus(pStatus => 'INSTALL STREET.DBF: UPDATE TEMP TABLE');
update KLADR_STREET x
set x.parent_id = substr(x.code, 1, 11),
x.actual_flag = substr(x.code, 16, 2);
commit;
end;
/* kladr table drop index */
setStatus(pStatus => 'DROP KLADR TABLE INDEXES');
execDDL('drop index KLADR_DISTRICT_TYPE_XIF');
execDDL('drop index KLADR_LEVEL_NAME_XIE');
execDDL('drop index KLADR_LEVEL_XIB');
execDDL('drop index KLADR_NAME_XIE');
execDDL('drop index KLADR_OKATO_XIN');
execDDL('drop index KLADR_PARENTCHILD_XAK');
execDDL('drop index KLADR_PARENT_XAK');
execDDL('drop index KLADR_PARENT_XIF');
execDDL('drop index KLADR_REGION_CENTER_XIE');
execDDL('drop index KLADR_REGION_NAME_XIE');
execDDL('drop index KLADR_REGION_XIE');
execDDL('drop index KLADR_ZIP_XIN');
/* Обновляем типы */
setStatus(pStatus => 'IMPORT KLADR DISTRICT TYPES');
merge into KLADR_DISTRICT_TYPE k
using KLADR_DISTRICT_TYPE_TEMP n on (k.TYPE_ID = n.KOD_T_ST)
when not matched then
insert (TYPE_ID, TYPE_LEVEL, ABBREVIATION, TYPE_NAME)
values (n.KOD_T_ST, n."LEVEL", n.SCNAME, n.SOCRNAME);
commit;
/* 1 регионы */
setStatus(pStatus => 'IMPORT KLADR REGIONS');
merge into KLADR k
using (
select substr(x.code, 1, 11) code, substr(x.code, 12, 2) actual_flag,
t.type_id, x."INDEX" zip, x.ocatd, x.name
from KLADR_TEMP x
inner join KLADR_DISTRICT_TYPE t on x.socr = t.abbreviation and t.type_level = 1
where substr(x.code, 3, 9) = '000000000' and substr(x.code, 12, 2) in ('00', '51', '99')
) n on (k.kladr_id = n.code)
when not matched then
insert (kladr_id, actual_flag, district_type, district_level, zip, okato, district_name)
values (n.code, n.actual_flag, n.type_id, 1, n.zip, n.ocatd, n.name);
commit;
/* 2 районы */
setStatus(pStatus => 'IMPORT KLADR CITY');
merge into KLADR k
using (
select
substr(x.code, 1, 11) code,
substr(x.code, 1, 2) || '000000000' parent_id,
substr(x.code, 12, 2) actual_flag,
t.type_id,
to_number(x.status) status,
x."INDEX" zip,
x.ocatd,
x.name
from KLADR_TEMP x
inner join KLADR_DISTRICT_TYPE t on x.socr = t.abbreviation and t.type_level = 2
where substr(x.code, 6, 6) = '000000' and substr(x.code, 3, 3) != '000' and substr(x.code, 12, 2) in ('00', '51', '99')
) n on (k.kladr_id = n.code)
when not matched then
insert (kladr_id, parent_id, actual_flag, district_type, district_level, center_status, zip, okato, district_name)
values (n.code, n.parent_id, n.actual_flag, n.type_id, 2, n.status, n.zip, n.ocatd, n.name);
commit;
/* 3 населенные пункты */
setStatus(pStatus => 'IMPORT KLADR LOCALITY');
merge into KLADR k
using (
select
substr(x.code, 1, 11) code,
substr(x.code, 1, 5) || '000000' parent_id,
substr(x.code, 12, 2) actual_flag,
t.type_id, to_number(x.status) status, x."INDEX" zip, x.ocatd, x.name
from KLADR_TEMP x
inner join KLADR_DISTRICT_TYPE t on x.socr = t.abbreviation and t.type_level = 3
where substr(x.code, 9, 3) = '000' and substr(x.code, 6, 3) != '000' and substr(x.code, 12, 2) in ('00', '51', '99')
) n on (k.kladr_id = n.code)
when not matched then
insert (kladr_id, parent_id, actual_flag, district_type, district_level, center_status, zip, okato, district_name)
values (n.code, n.parent_id, n.actual_flag, n.type_id, 3, n.status, n.zip, n.ocatd, n.name);
commit;
/* 4 мелкие населенные пункты */
setStatus(pStatus => 'IMPORT KLADR SMALL LOCALITY');
begin
insert into KLADR
(kladr_id, parent_id, actual_flag, district_type,
district_level, zip, okato, district_name)
select n.code, n.parent_id, n.actual_flag,
t.type_id, 4, n.zip, n.ocatd, n.name
from (
select
substr(x.code, 1, 11) code,
substr(x.code, 1, 8) || '000' parent_id,
substr(x.code, 12, 2) actual_flag,
x.socr, x."INDEX" zip, x.ocatd, x.name
from KLADR_TEMP x
where substr(x.code, 12, 2) in ('00', '51', '99') -- and substr(x.code, 9, 3) != '000'
) n
inner join KLADR_DISTRICT_TYPE t on n.socr = t.abbreviation
where t.type_level = 4 and not exists (
select z.kladr_id from kladr z where z.kladr_id = n.code
);
commit;
end;
/* 5 улицы */
setStatus(pStatus => 'IMPORT KLADR STREETS');
begin
insert into KLADR
(kladr_id, parent_id, actual_flag, district_type,
district_level, zip, okato, district_name)
select
n.code, n.parent_id, n.actual_flag, t.type_id,
5, n.zip, n.ocatd, n.name
from (
select
substr(x.code, 1, 15) code, x.parent_id, x.actual_flag,
x."INDEX" zip, x.ocatd, x.name, x.socr
from KLADR_STREET x
where substr(x.code, 16, 2) in ('00', '51', '99')
) n
inner join KLADR_DISTRICT_TYPE t on n.socr = t.abbreviation
where t.type_level = 5
and not exists (select z.kladr_id from kladr z where z.kladr_id = n.code);
commit;
end;
/* update column on kladr table */
setStatus(pStatus => 'UPDATE HAS_CHILDRED COLUMN ON KLADR');
update KLADR k set k.has_children = 'Y'
where
exists (
select t.kladr_id
from KLADR t
where t.parent_id = k.kladr_id);
commit;
/* create indexes */
setStatus(pStatus => 'CREATE INDEXES ON KLADR TABLE');
execDDL('create index KLADR_DISTRICT_TYPE_XIF on KLADR (DISTRICT_TYPE)');
execDDL('create index KLADR_LEVEL_NAME_XIE on KLADR (DISTRICT_LEVEL, UPPER(DISTRICT_NAME))');
execDDL('create bitmap index KLADR_LEVEL_XIB on KLADR (DISTRICT_LEVEL)');
execDDL('create index KLADR_NAME_XIE on KLADR (UPPER(DISTRICT_NAME))');
execDDL('create index KLADR_OKATO_XIN on KLADR (OKATO)');
execDDL('create index KLADR_PARENTCHILD_XAK on KLADR (PARENT_ID, DISTRICT_LEVEL, ACTUAL_FLAG, HAS_CHILDREN)');
execDDL('create index KLADR_PARENT_XAK on KLADR (PARENT_ID, DISTRICT_LEVEL, ACTUAL_FLAG)');
execDDL('create index KLADR_PARENT_XIF on KLADR (PARENT_ID)');
execDDL('create index KLADR_REGION_CENTER_XIE on KLADR (SUBSTR(KLADR_ID,1,2), CENTER_STATUS)');
execDDL('create index KLADR_REGION_NAME_XIE on KLADR (SUBSTR(KLADR_ID,1,2), UPPER(DISTRICT_NAME), DISTRICT_LEVEL)');
execDDL('create index KLADR_REGION_XIE on KLADR (SUBSTR(KLADR_ID,1,2))');
execDDL('create index KLADR_ZIP_XIN on KLADR (ZIP)');
/* truncate temp tables */
setStatus(pStatus => 'TRUNCATE KLADR TEMP TABLES');
execute immediate 'truncate table KLADR_TEMP';
execute immediate 'truncate table KLADR_STREET';
execute immediate 'truncate table KLADR_DISTRICT_TYPE_TEMP';
/* delete temp files */
setStatus(pStatus => 'DELETE IMPORT FILES');
deleteImportFile(getImportFileInfo('SOCRBASE.DBF').id);
deleteImportFile(getImportFileInfo('KLADR.DBF').id);
deleteImportFile(getImportFileInfo('STREET.DBF').id);
/* set finished status */
setStatus(pStatus => 'IMPORT COMPLETED!');
exception when others then
setStatus('ERROR', true);
end;
Revise this Paste
Parent: 22693