Psst.. new poll here.
Psst.. new forums here.
Microsoft is blocking us again (TY IP Reputation!) so dont bother with any of their useless mail servers here and just use oauth login instead. Thank the nice Russians for causing that. :)
Paste
Pasted by iceman ( 16 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