EugeneBoss3
Member | Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
/******************************************************************************/
/*** Generated by IBExpert 2007.02.16 18.03.2007 17:29:26 ***/
/******************************************************************************/
SET SQL DIALECT 3;
SET NAMES WIN1251;
/**SET CLIENTLIB 'FBCLIENT.DLL';**/
CREATE DATABASE 'LOCALHOST:C:\Data\KLADR.FDB'
USER 'SYSDBA'
PASSWORD 'masterkey'
PAGE_SIZE 8192
DEFAULT CHARACTER SET WIN1251;
/******************************************************************************/
/*** Domains ***/
/******************************************************************************/
CREATE DOMAIN D_ADDRESS_ABBREV_ID AS
SMALLINT
NOT NULL;
CREATE DOMAIN D_ADDRESS_ABBREV_LEVEL AS
SMALLINT
NOT NULL
CHECK ((Value >= 1) and (Value <= 6));
CREATE DOMAIN D_ADDRESS_ABBREV_NAME AS
VARCHAR(40)
NOT NULL;
CREATE DOMAIN D_ADDRESS_ABBREV_POS AS
SMALLINT
DEFAULT 0
NOT NULL
CHECK ((Value >= 0) and (Value <= 2));
CREATE DOMAIN D_ADDRESS_ABBREV_POSA AS
SMALLINT
DEFAULT -1
NOT NULL
CHECK ((Value >= -1) and (Value <=2));
CREATE DOMAIN D_ADDRESS_ABBREV_SHNAME AS
VARCHAR(10)
NOT NULL;
CREATE DOMAIN D_ADDRESS_ACTUAL AS
SMALLINT
DEFAULT 0
NOT NULL
CHECK ((Value = 0) or (Value = 1));
CREATE DOMAIN D_ADDRESS_GNINMB AS
SMALLINT
DEFAULT 0
NOT NULL;
CREATE DOMAIN D_ADDRESS_ID AS
INTEGER
NOT NULL;
CREATE DOMAIN D_ADDRESS_INDEX AS
INTEGER
DEFAULT 0
NOT NULL
CHECK ((Value = 0) or (Value >= 100000 and Value <= 999999));
CREATE DOMAIN D_ADDRESS_LID AS
BIGINT
NOT NULL;
CREATE DOMAIN D_ADDRESS_NAME AS
VARCHAR(40)
NOT NULL;
CREATE DOMAIN D_ADDRESS_REGION AS
SMALLINT
NOT NULL
CHECK ((Value >= 0) and (Value < 100));
CREATE DOMAIN D_ADDRESS_STATUS AS
SMALLINT
DEFAULT 0
NOT NULL;
CREATE DOMAIN D_ADDRESS_UNO AS
SMALLINT
DEFAULT 0
NOT NULL;
CREATE DOMAIN D_ADDRESS_VERSION AS
SMALLINT
DEFAULT 0
NOT NULL;
CREATE DOMAIN D_OCATOREGION_ID AS
INTEGER
NOT NULL
CHECK ((Value >= 0) and (Value < 100));
CREATE DOMAIN D_OCATO_ID AS
INTEGER
NOT NULL
CHECK ((Value >= 0) and (Value < 1000));
/******************************************************************************/
/*** Exceptions ***/
/******************************************************************************/
CREATE EXCEPTION EXC_MANYVALUES 'Список адресов превашает 100. Задайте более строгие критерии поиска.';
SET TERM ^ ;
/******************************************************************************/
/*** Stored Procedures ***/
/******************************************************************************/
CREATE PROCEDURE GET_ADDRESS_LIST_2 (
SRCH_STR VARCHAR(40),
ADDRESS_TYPE SMALLINT,
MAX_COUNT SMALLINT)
RETURNS (
ADR_REG SMALLINT,
ADR_ID BIGINT,
ADR_FULLNAME VARCHAR(500))
AS
BEGIN
EXIT;
END^
CREATE PROCEDURE GET_ADDRESS_LIST_20 (
SRCH_STR VARCHAR(40),
ADDRESS_TYPE SMALLINT,
MAX_COUNT SMALLINT)
RETURNS (
ADR_REG SMALLINT,
ADR_ID BIGINT,
ADR_VERS SMALLINT,
ADR_FULLNAME VARCHAR(500))
AS
BEGIN
EXIT;
END^
CREATE PROCEDURE GET_FULLADDRESS_2 (
ADDRESS_TYPE SMALLINT,
CR SMALLINT,
CA BIGINT)
RETURNS (
SA VARCHAR(500))
AS
BEGIN
EXIT;
END^
CREATE PROCEDURE GET_FULLADDRESS_20 (
ADDRESS_TYPE SMALLINT,
CR SMALLINT,
CA BIGINT,
CV SMALLINT)
RETURNS (
SA VARCHAR(500))
AS
BEGIN
EXIT;
END^
CREATE PROCEDURE GET_REGION_LIST (
TYPEBASE SMALLINT)
RETURNS (
ADDRESSREGION_ID SMALLINT,
ADDRESS_NAME VARCHAR(100))
AS
BEGIN
EXIT;
END^
SET TERM ; ^
/******************************************************************************/
/*** Tables ***/
/******************************************************************************/
CREATE TABLE KLADR_ABBREV (
ABBREV_LEVEL D_ADDRESS_ABBREV_LEVEL NOT NULL,
ABBREV_ID D_ADDRESS_ABBREV_ID NOT NULL,
ABBREV_POS D_ADDRESS_ABBREV_POS,
ABBREV_NAME D_ADDRESS_ABBREV_NAME,
ABBREV_SHNAME D_ADDRESS_ABBREV_SHNAME
);
CREATE TABLE KLADR_ADDRESS_2 (
ADDRESSREGION_ID D_ADDRESS_REGION NOT NULL,
ADDRESS_ID D_ADDRESS_LID NOT NULL,
ADDRESS_PAR D_ADDRESS_LID,
ADDRESS_NAME D_ADDRESS_NAME,
ABBREV_LEVEL D_ADDRESS_ABBREV_LEVEL,
ABBREV_ID D_ADDRESS_ABBREV_ID,
ABBREV_POS D_ADDRESS_ABBREV_POSA,
ADDRESS_UNO D_ADDRESS_UNO,
ADDRESS_GNINMB D_ADDRESS_GNINMB,
ADDRESS_STATUS D_ADDRESS_STATUS,
ADDRESS_INDEX D_ADDRESS_INDEX,
OCATOREGION_ID D_OCATOREGION_ID,
OCATO1_ID D_OCATO_ID,
OCATO2_ID D_OCATO_ID,
OCATO3_ID D_OCATO_ID
);
CREATE TABLE KLADR_ADDRESS_20 (
ADDRESSREGION_ID D_ADDRESS_REGION NOT NULL,
ADDRESS_ID D_ADDRESS_LID NOT NULL,
ADDRESS_VERSION D_ADDRESS_VERSION,
ADDRESS_ACTUAL D_ADDRESS_ACTUAL,
ADDRESS_NAME D_ADDRESS_NAME,
ABBREV_LEVEL D_ADDRESS_ABBREV_LEVEL,
ABBREV_ID D_ADDRESS_ABBREV_ID,
ABBREV_POS D_ADDRESS_ABBREV_POSA,
ADDRESS_UNO D_ADDRESS_UNO,
ADDRESS_GNINMB D_ADDRESS_GNINMB,
ADDRESS_STATUS D_ADDRESS_STATUS,
ADDRESS_INDEX D_ADDRESS_INDEX,
OCATOREGION_ID D_OCATOREGION_ID,
OCATO1_ID D_OCATO_ID,
OCATO2_ID D_OCATO_ID,
OCATO3_ID D_OCATO_ID
);
CREATE TABLE KLADR_ADDRESS_20_DEPEND (
ADDRESSREGION_ID D_ADDRESS_REGION,
ADDRESS_ID D_ADDRESS_LID,
ADDRESS_VERSION D_ADDRESS_VERSION,
ADDRESS_PAR D_ADDRESS_LID,
ADDRESS_PARVERS D_ADDRESS_VERSION
);
/******************************************************************************/
/*** Views ***/
/******************************************************************************/
/* View: VW_ADDRESS_2 */
CREATE VIEW VW_ADDRESS_2(
ADDRESSREGION_ID,
ADDRESS_ID,
ADDRESS_PAR,
ADDRESS_NAME,
ABBREV_LEVEL,
ABBREV_ID,
ABBREV_POS,
ADDRESS_UNO,
ADDRESS_GNINMB,
ADDRESS_STATUS,
ADDRESS_INDEX,
OCATOREGION_ID,
OCATO1_ID,
OCATO2_ID,
OCATO3_ID,
ABBREV_MAINPOS,
ABBREV_NAME,
ABBREV_SHNAME,
ADDRESS_VERSION,
ADDRESS_PARVERS,
ADDRESS_ACTUAL)
AS
select
K.ADDRESSREGION_ID, K.ADDRESS_ID, K.ADDRESS_PAR, K.ADDRESS_NAME,
K.ABBREV_LEVEL, K.ABBREV_ID, K.ABBREV_POS, K.ADDRESS_UNO, K.ADDRESS_GNINMB,
K.ADDRESS_STATUS, K.ADDRESS_INDEX,
K.OCATOREGION_ID, K.OCATO1_ID, K.OCATO2_ID, K.OCATO3_ID,
S.ABBREV_POS, S.ABBREV_NAME, S.ABBREV_SHNAME, 0, 0, 0
from KLADR_ADDRESS_2 K
left join KLADR_ABBREV S on ((S.ABBREV_LEVEL = K.ABBREV_LEVEL) and (S.ABBREV_ID = K.ABBREV_ID))
where (ADDRESS_ID > 0) and (K.ABBREV_LEVEL < 5)
;
/* View: VW_ADDRESS_20 */
CREATE VIEW VW_ADDRESS_20(
ADDRESSREGION_ID,
ADDRESS_ID,
ADDRESS_VERSION,
ADDRESS_ACTUAL,
ADDRESS_NAME,
ABBREV_LEVEL,
ABBREV_ID,
ABBREV_POS,
ADDRESS_UNO,
ADDRESS_GNINMB,
ADDRESS_STATUS,
ADDRESS_INDEX,
OCATOREGION_ID,
OCATO1_ID,
OCATO2_ID,
OCATO3_ID,
ADDRESS_PAR,
ADDRESS_PARVERS,
ABBREV_MAINPOS,
ABBREV_NAME,
ABBREV_SHNAME)
AS
select
K.ADDRESSREGION_ID, K.ADDRESS_ID, K.ADDRESS_VERSION, K.ADDRESS_ACTUAL,
K.ADDRESS_NAME, K.ABBREV_LEVEL, K.ABBREV_ID, K.ABBREV_POS, K.ADDRESS_UNO,
K.ADDRESS_GNINMB, K.ADDRESS_STATUS, K.ADDRESS_INDEX,
K.OCATOREGION_ID, K.OCATO1_ID, K.OCATO2_ID, K.OCATO3_ID,
D.ADDRESS_PAR, D.ADDRESS_PARVERS,
S.ABBREV_POS, S.ABBREV_NAME, S.ABBREV_SHNAME
from KLADR_ADDRESS_20 K
left join KLADR_ADDRESS_20_DEPEND D on
(K.ADDRESSREGION_ID = D.ADDRESSREGION_ID and K.ADDRESS_ID = D.ADDRESS_ID and K.ADDRESS_VERSION = D.ADDRESS_VERSION)
left join KLADR_ABBREV S on ((S.ABBREV_LEVEL = K.ABBREV_LEVEL) and (S.ABBREV_ID = K.ABBREV_ID))
where (K.ABBREV_LEVEL < 5) and (D.ADDRESS_ID > 0)
;
/* View: VW_REGIONS_2 */
CREATE VIEW VW_REGIONS_2(
ADDRESSREGION_ID,
ADDRESS_ID,
ADDRESS_PAR,
ADDRESS_NAME,
ABBREV_LEVEL,
ABBREV_ID,
ABBREV_POS,
ADDRESS_UNO,
ADDRESS_GNINMB,
ADDRESS_STATUS,
ADDRESS_INDEX,
OCATOREGION_ID,
OCATO1_ID,
OCATO2_ID,
OCATO3_ID,
ABBREV_MAINPOS,
ABBREV_NAME,
ABBREV_SHNAME,
ADDRESS_VERSION,
ADDRESS_PARVERS,
ADDRESS_ACTUAL)
AS
select
K.ADDRESSREGION_ID, K.ADDRESS_ID, K.ADDRESS_PAR, K.ADDRESS_NAME,
K.ABBREV_LEVEL, K.ABBREV_ID, K.ABBREV_POS, K.ADDRESS_UNO, K.ADDRESS_GNINMB,
K.ADDRESS_STATUS, K.ADDRESS_INDEX,
K.OCATOREGION_ID, K.OCATO1_ID, K.OCATO2_ID, K.OCATO3_ID,
S.ABBREV_POS, S.ABBREV_NAME, S.ABBREV_SHNAME, 0, 0, 0
from KLADR_ADDRESS_2 K
left join KLADR_ABBREV S on ((S.ABBREV_LEVEL = K.ABBREV_LEVEL) and (S.ABBREV_ID = K.ABBREV_ID))
where K.ADDRESS_ID = 0 and K.ABBREV_LEVEL = 1
;
/* View: VW_REGIONS_20 */
CREATE VIEW VW_REGIONS_20(
ADDRESSREGION_ID,
ADDRESS_ID,
ADDRESS_VERSION,
ADDRESS_ACTUAL,
ADDRESS_NAME,
ABBREV_LEVEL,
ABBREV_ID,
ABBREV_POS,
ADDRESS_UNO,
ADDRESS_GNINMB,
ADDRESS_STATUS,
ADDRESS_INDEX,
OCATOREGION_ID,
OCATO1_ID,
OCATO2_ID,
OCATO3_ID,
ADDRESS_PAR,
ADDRESS_PARVERS,
ABBREV_MAINPOS,
ABBREV_NAME,
ABBREV_SHNAME)
AS
select
K.ADDRESSREGION_ID, K.ADDRESS_ID, K.ADDRESS_VERSION, K.ADDRESS_ACTUAL,
K.ADDRESS_NAME, K.ABBREV_LEVEL, K.ABBREV_ID, K.ABBREV_POS, K.ADDRESS_UNO,
K.ADDRESS_GNINMB, K.ADDRESS_STATUS, K.ADDRESS_INDEX,
K.OCATOREGION_ID, K.OCATO1_ID, K.OCATO2_ID, K.OCATO3_ID,
D.ADDRESS_PAR, D.ADDRESS_PARVERS,
S.ABBREV_POS, S.ABBREV_NAME, S.ABBREV_SHNAME
from KLADR_ADDRESS_20 K
left join KLADR_ADDRESS_20_DEPEND D on
(K.ADDRESSREGION_ID = D.ADDRESSREGION_ID and K.ADDRESS_ID = D.ADDRESS_ID and K.ADDRESS_VERSION = D.ADDRESS_VERSION)
left join KLADR_ABBREV S on ((S.ABBREV_LEVEL = K.ABBREV_LEVEL) and (S.ABBREV_ID = K.ABBREV_ID))
where K.ADDRESS_ID = 0 and K.ABBREV_LEVEL = 1
;
/* View: VW_STREET_2 */
CREATE VIEW VW_STREET_2(
ADDRESSREGION_ID,
ADDRESS_ID,
ADDRESS_PAR,
ADDRESS_NAME,
ABBREV_LEVEL,
ABBREV_ID,
ABBREV_POS,
ADDRESS_UNO,
ADDRESS_GNINMB,
ADDRESS_STATUS,
ADDRESS_INDEX,
OCATOREGION_ID,
OCATO1_ID,
OCATO2_ID,
OCATO3_ID,
ABBREV_MAINPOS,
ABBREV_NAME,
ABBREV_SHNAME,
ADDRESS_VERSION,
ADDRESS_PARVERS,
ADDRESS_ACTUAL)
AS
select
K.ADDRESSREGION_ID, K.ADDRESS_ID, K.ADDRESS_PAR, K.ADDRESS_NAME,
K.ABBREV_LEVEL, K.ABBREV_ID, K.ABBREV_POS, K.ADDRESS_UNO, K.ADDRESS_GNINMB,
K.ADDRESS_STATUS, K.ADDRESS_INDEX,
K.OCATOREGION_ID, K.OCATO1_ID, K.OCATO2_ID, K.OCATO3_ID,
S.ABBREV_POS, S.ABBREV_NAME, S.ABBREV_SHNAME, 0, 0, 0
from KLADR_ADDRESS_2 K
left join KLADR_ABBREV S on ((S.ABBREV_LEVEL = K.ABBREV_LEVEL) and (S.ABBREV_ID = K.ABBREV_ID))
where (K.ABBREV_LEVEL = 5)
;
/* View: VW_STREET_20 */
CREATE VIEW VW_STREET_20(
ADDRESSREGION_ID,
ADDRESS_ID,
ADDRESS_VERSION,
ADDRESS_ACTUAL,
ADDRESS_NAME,
ABBREV_LEVEL,
ABBREV_ID,
ABBREV_POS,
ADDRESS_UNO,
ADDRESS_GNINMB,
ADDRESS_STATUS,
ADDRESS_INDEX,
OCATOREGION_ID,
OCATO1_ID,
OCATO2_ID,
OCATO3_ID,
ADDRESS_PAR,
ADDRESS_PARVERS,
ABBREV_MAINPOS,
ABBREV_NAME,
ABBREV_SHNAME)
AS
select
K.ADDRESSREGION_ID, K.ADDRESS_ID, K.ADDRESS_VERSION, K.ADDRESS_ACTUAL,
K.ADDRESS_NAME, K.ABBREV_LEVEL, K.ABBREV_ID, K.ABBREV_POS, K.ADDRESS_UNO,
K.ADDRESS_GNINMB, K.ADDRESS_STATUS, K.ADDRESS_INDEX,
K.OCATOREGION_ID, K.OCATO1_ID, K.OCATO2_ID, K.OCATO3_ID,
D.ADDRESS_PAR, D.ADDRESS_PARVERS,
S.ABBREV_POS, S.ABBREV_NAME, S.ABBREV_SHNAME
from KLADR_ADDRESS_20 K
left join KLADR_ADDRESS_20_DEPEND D on
(K.ADDRESSREGION_ID = D.ADDRESSREGION_ID and K.ADDRESS_ID = D.ADDRESS_ID and K.ADDRESS_VERSION = D.ADDRESS_VERSION)
left join KLADR_ABBREV S on ((S.ABBREV_LEVEL = K.ABBREV_LEVEL) and (S.ABBREV_ID = K.ABBREV_ID))
where (K.ABBREV_LEVEL = 5)
;
/******************************************************************************/
/*** Primary Keys ***/
/******************************************************************************/
ALTER TABLE KLADR_ABBREV ADD CONSTRAINT PK_KLADR_ABBREV PRIMARY KEY (ABBREV_LEVEL, ABBREV_ID);
ALTER TABLE KLADR_ADDRESS_2 ADD CONSTRAINT PK_KLADR_ADDRESS_2 PRIMARY KEY (ADDRESSREGION_ID, ADDRESS_ID);
ALTER TABLE KLADR_ADDRESS_20 ADD CONSTRAINT PK_KLADR_ADDRESS_20 PRIMARY KEY (ADDRESSREGION_ID, ADDRESS_ID, ADDRESS_VERSION);
ALTER TABLE KLADR_ADDRESS_20_DEPEND ADD CONSTRAINT PK_KLADR_ADDRESS_20_DEPEND PRIMARY KEY (ADDRESSREGION_ID, ADDRESS_ID, ADDRESS_VERSION);
/******************************************************************************/
/*** Foreign Keys ***/
/******************************************************************************/
ALTER TABLE KLADR_ADDRESS_2 ADD CONSTRAINT FK_KLADR_ADDRESS_2_1 FOREIGN KEY (ABBREV_LEVEL, ABBREV_ID) REFERENCES KLADR_ABBREV (ABBREV_LEVEL, ABBREV_ID);
ALTER TABLE KLADR_ADDRESS_2 ADD CONSTRAINT FK_KLADR_ADDRESS_2_2 FOREIGN KEY (ADDRESSREGION_ID, ADDRESS_PAR) REFERENCES KLADR_ADDRESS_2 (ADDRESSREGION_ID, ADDRESS_ID);
ALTER TABLE KLADR_ADDRESS_20 ADD CONSTRAINT FK_KLADR_ADDRESS_20_1 FOREIGN KEY (ABBREV_LEVEL, ABBREV_ID) REFERENCES KLADR_ABBREV (ABBREV_LEVEL, ABBREV_ID);
ALTER TABLE KLADR_ADDRESS_20_DEPEND ADD CONSTRAINT FK_KLADR_ADDRESS_20_DEPEND_1 FOREIGN KEY (ADDRESSREGION_ID, ADDRESS_ID, ADDRESS_VERSION) REFERENCES KLADR_ADDRESS_20 (ADDRESSREGION_ID, ADDRESS_ID, ADDRESS_VERSION);
ALTER TABLE KLADR_ADDRESS_20_DEPEND ADD CONSTRAINT FK_KLADR_ADDRESS_20_DEPEND_2 FOREIGN KEY (ADDRESSREGION_ID, ADDRESS_PAR, ADDRESS_PARVERS) REFERENCES KLADR_ADDRESS_20 (ADDRESSREGION_ID, ADDRESS_ID, ADDRESS_VERSION);
/******************************************************************************/
/*** Indices ***/
/******************************************************************************/
CREATE INDEX KLADR_ADDRESS_2_IDX_1 ON KLADR_ADDRESS_2 (ADDRESSREGION_ID, ADDRESS_ID, ABBREV_LEVEL);
CREATE INDEX KLADR_ADDRESS_2_IDX_2 ON KLADR_ADDRESS_2 (ADDRESSREGION_ID, ADDRESS_PAR, ABBREV_LEVEL);
/******************************************************************************/
/*** Stored Procedures ***/
/******************************************************************************/
SET TERM ^ ;
ALTER PROCEDURE GET_ADDRESS_LIST_2 (
SRCH_STR VARCHAR(40),
ADDRESS_TYPE SMALLINT,
MAX_COUNT SMALLINT)
RETURNS (
ADR_REG SMALLINT,
ADR_ID BIGINT,
ADR_FULLNAME VARCHAR(500))
AS
DECLARE VARIABLE CNT SMALLINT;
BEGIN
IF (:ADDRESS_TYPE IS NULL) THEN ADDRESS_TYPE = 0;
IF (MAX_COUNT IS NULL) THEN MAX_COUNT = 100;
IF ((:MAX_COUNT > 100) OR (:MAX_COUNT < 5)) THEN MAX_COUNT = 100;
CNT = 0;
FOR
SELECT A.ADDRESSREGION_ID, A.ADDRESS_ID
FROM KLADR_ADDRESS_2 A
WHERE (A.ABBREV_LEVEL < 5) AND UPPER(A.ADDRESS_NAME) LIKE :SRCH_STR
ORDER BY A.ADDRESSREGION_ID
INTO :ADR_REG, :ADR_ID
DO BEGIN
SELECT SA FROM GET_FULLADDRESS_2(:ADDRESS_TYPE, :ADR_REG, :ADR_ID)
INTO :ADR_FULLNAME;
SUSPEND;
CNT = :CNT + 1;
IF (:CNT > :MAX_COUNT) THEN EXCEPTION EXC_MANYVALUES;
END
SUSPEND;
END
^
ALTER PROCEDURE GET_ADDRESS_LIST_20 (
SRCH_STR VARCHAR(40),
ADDRESS_TYPE SMALLINT,
MAX_COUNT SMALLINT)
RETURNS (
ADR_REG SMALLINT,
ADR_ID BIGINT,
ADR_VERS SMALLINT,
ADR_FULLNAME VARCHAR(500))
AS
DECLARE VARIABLE CNT SMALLINT;
BEGIN
IF (:ADDRESS_TYPE IS NULL) THEN ADDRESS_TYPE = 0;
IF (MAX_COUNT IS NULL) THEN MAX_COUNT = 100;
IF ((:MAX_COUNT > 100) OR (:MAX_COUNT < 5)) THEN MAX_COUNT = 100;
CNT = 0;
FOR
SELECT A.ADDRESSREGION_ID, A.ADDRESS_ID, A.ADDRESS_VERSION
FROM KLADR_ADDRESS_20 A
WHERE (A.ABBREV_LEVEL < 5) AND UPPER(A.ADDRESS_NAME) LIKE :SRCH_STR
ORDER BY A.ADDRESSREGION_ID
INTO :ADR_REG, :ADR_ID, :ADR_VERS
DO BEGIN
SELECT SA FROM GET_FULLADDRESS_20(:ADDRESS_TYPE, :ADR_REG, :ADR_ID, :ADR_VERS)
INTO :ADR_FULLNAME;
SUSPEND;
CNT = :CNT + 1;
IF (:CNT > :MAX_COUNT) THEN EXCEPTION EXC_MANYVALUES;
END
SUSPEND;
END
^
ALTER PROCEDURE GET_FULLADDRESS_2 (
ADDRESS_TYPE SMALLINT,
CR SMALLINT,
CA BIGINT)
RETURNS (
SA VARCHAR(500))
AS
DECLARE VARIABLE NOA BIGINT;
DECLARE VARIABLE NOP BIGINT;
DECLARE VARIABLE NOL INTEGER;
DECLARE VARIABLE NO3 INTEGER;
DECLARE VARIABLE SPINDEX INTEGER;
DECLARE VARIABLE SPINDEX1 INTEGER;
DECLARE VARIABLE MPOS SMALLINT;
DECLARE VARIABLE OPOS SMALLINT;
DECLARE VARIABLE SP1 VARCHAR(40) CHARACTER SET WIN1251;
DECLARE VARIABLE SP2 VARCHAR(40) CHARACTER SET WIN1251;
DECLARE VARIABLE SFX VARCHAR(2);
BEGIN
SA = '';
SPINDEX = 0;
NOA = CA;
NO3 = 5;
IF (:ADDRESS_TYPE IS NULL) THEN ADDRESS_TYPE = 0;
IF (ADDRESS_TYPE = 0) THEN SFX = ', ';
ELSE SFX = ',';
/* ПОСТРОЕНИЕ СТРОКИ АДРЕСА */
WHILE (NOA >= 0) DO
BEGIN
/* ДЛЯ НАЛОГОВОГО СЛУЧАЯ НАЗВАНИЯ РЕГИОНА НЕ ВЫЧИСЛЯЕТСЯ. ВЫХОДИМ */
IF ((ADDRESS_TYPE > 0) AND (NOA = 0)) THEN LEAVE;
NOP = -1;
FOR
SELECT A.ADDRESS_NAME, A.ADDRESS_PAR,
A.ABBREV_LEVEL, A.ABBREV_POS, A.ADDRESS_INDEX, B.ABBREV_SHNAME, B.ABBREV_POS
FROM KLADR_ADDRESS_2 A
LEFT JOIN KLADR_ABBREV B ON (B.ABBREV_LEVEL = A.ABBREV_LEVEL AND B.ABBREV_ID = A.ABBREV_ID)
WHERE (A.ADDRESSREGION_ID = :CR) AND (A.ADDRESS_ID = :NOA)
INTO :SP1, :NOP, :NOL,  POS, :SPINDEX1, :SP2, :MPOS
DO BEGIN
IF ((SPINDEX = 0) AND (SPINDEX1 <> 0)) THEN SPINDEX = SPINDEX1;
IF (ADDRESS_TYPE = 0) THEN
BEGIN
IF (:OPOS < 0) THEN OPOS = MPOS;
IF (:OPOS = 0) THEN SA = SP2 || ' ' || SP1 || SFX || SA;
ELSE
IF (:OPOS = 1) THEN SA = SP1 || ' ' || SP2 || SFX || SA;
ELSE SA = SP1 || SFX || SA;
END ELSE BEGIN
NOA = NOP;
WHILE (NO3 > NOL) DO
BEGIN
NO3 = NO3 - 1;
SA = SFX || SA;
END
SA = SP1 || ' ' || SP2 || SFX || SA;
NO3 = NO3 - 1;
END
END
IF ((NOA = 0) AND (NOP = 0)) THEN LEAVE;
NOA = NOP;
END
IF (ADDRESS_TYPE = 0) THEN
BEGIN
IF (:SPINDEX > 0) THEN SA = SPINDEX || SFX || SA;
END ELSE BEGIN
WHILE (NO3 > 1) DO
BEGIN
SA = SFX || SA;
NO3 = NO3 - 1;
END
IF (CR < 10) THEN SA = '0' || CR || SFX || SA;
ELSE SA = CR || SFX || SA;
IF (:SPINDEX <> 0) THEN SA = SFX || SPINDEX || SFX || SA;
ELSE SA = SFX || SFX || SA;
END
SUSPEND;
END
^
ALTER PROCEDURE GET_FULLADDRESS_20 (
ADDRESS_TYPE SMALLINT,
CR SMALLINT,
CA BIGINT,
CV SMALLINT)
RETURNS (
SA VARCHAR(500))
AS
DECLARE VARIABLE NOA BIGINT;
DECLARE VARIABLE NOP BIGINT;
DECLARE VARIABLE NOV INTEGER;
DECLARE VARIABLE NOL INTEGER;
DECLARE VARIABLE NO3 INTEGER;
DECLARE VARIABLE SPINDEX INTEGER;
DECLARE VARIABLE SPINDEX1 INTEGER;
DECLARE VARIABLE MPOS SMALLINT;
DECLARE VARIABLE OPOS SMALLINT;
DECLARE VARIABLE SP1 VARCHAR(40) CHARACTER SET WIN1251;
DECLARE VARIABLE SP2 VARCHAR(40) CHARACTER SET WIN1251;
DECLARE VARIABLE SFX VARCHAR(2);
BEGIN
SA = '';
SPINDEX = 0;
NOA = CA;
NOV = CV;
NO3 = 5;
IF (:ADDRESS_TYPE IS NULL) THEN ADDRESS_TYPE = 0;
IF (ADDRESS_TYPE = 0) THEN SFX = ', ';
ELSE SFX = ',';
/* ПОСТРОЕНИЕ СТРОКИ АДРЕСА */
WHILE (NOA >= 0) DO
BEGIN
/* ДЛЯ НАЛОГОВОГО СЛУЧАЯ НАЗВАНИЯ РЕГИОНА НЕ ВЫЧИСЛЯЕТСЯ. ВЫХОДИМ */
IF ((ADDRESS_TYPE > 0) AND (NOA = 0)) THEN LEAVE;
NOP = -1;
FOR
SELECT A.ADDRESS_NAME, D.ADDRESS_PAR, D.ADDRESS_PARVERS,
A.ABBREV_LEVEL, A.ABBREV_POS, A.ADDRESS_INDEX, B.ABBREV_SHNAME, B.ABBREV_POS
FROM KLADR_ADDRESS_20 A
LEFT JOIN KLADR_ABBREV B ON (B.ABBREV_LEVEL = A.ABBREV_LEVEL AND B.ABBREV_ID = A.ABBREV_ID)
LEFT JOIN KLADR_ADDRESS_20_DEPEND D ON (
D.ADDRESSREGION_ID = A.ADDRESSREGION_ID AND
D.ADDRESS_ID = A.ADDRESS_ID AND
D.ADDRESS_VERSION = A.ADDRESS_VERSION)
WHERE (A.ADDRESSREGION_ID = :CR) AND (A.ADDRESS_ID = :NOA) AND (A.ADDRESS_VERSION = :NOV)
INTO :SP1, :NOP, :NOV, :NOL, POS, :SPINDEX1, :SP2, :MPOS
DO BEGIN
IF ((SPINDEX = 0) AND (SPINDEX1 <> 0)) THEN SPINDEX = SPINDEX1;
IF (ADDRESS_TYPE = 0) THEN
BEGIN
IF (:OPOS < 0) THEN OPOS = MPOS;
IF (:OPOS = 0) THEN SA = SP2 || ' ' || SP1 || SFX || SA;
ELSE
IF (:OPOS = 1) THEN SA = SP1 || ' ' || SP2 || SFX || SA;
ELSE SA = SP1 || SFX || SA;
END ELSE BEGIN
NOA = NOP;
WHILE (NO3 > NOL) DO
BEGIN
NO3 = NO3 - 1;
SA = SFX || SA;
END
SA = SP1 || ' ' || SP2 || SFX || SA;
NO3 = NO3 - 1;
END
END
IF ((NOA = 0) AND (NOP = 0)) THEN LEAVE;
NOA = NOP;
END
--
IF (ADDRESS_TYPE = 0) THEN
BEGIN
IF (:SPINDEX > 0) THEN SA = SPINDEX || SFX || SA;
END ELSE BEGIN
WHILE (NO3 > 1) DO
BEGIN
SA = SFX || SA;
NO3 = NO3 - 1;
END
IF (CR < 10) THEN SA = '0' || CR || SFX || SA;
ELSE SA = CR || SFX || SA;
IF (:SPINDEX <> 0) THEN SA = SFX || SPINDEX || SFX || SA;
ELSE SA = SFX || SFX || SA;
END
SUSPEND;
END
^
ALTER PROCEDURE GET_REGION_LIST (
TYPEBASE SMALLINT)
RETURNS (
ADDRESSREGION_ID SMALLINT,
ADDRESS_NAME VARCHAR(100))
AS
DECLARE VARIABLE POSORIG INTEGER;
DECLARE VARIABLE POSMAIN INTEGER;
DECLARE VARIABLE ADRNAME VARCHAR(40);
DECLARE VARIABLE ABBRNAME VARCHAR(10);
BEGIN
IF (:TYPEBASE = 0) THEN
BEGIN
FOR
SELECT A.ADDRESSREGION_ID, A.ADDRESS_NAME, A.ABBREV_POS, A.ABBREV_MAINPOS, A.ABBREV_SHNAME
FROM VW_REGIONS_2 A
ORDER BY A.ADDRESSREGION_ID
INTO :ADDRESSREGION_ID, :ADRNAME,  OSORIG, OSMAIN, :ABBRNAME
DO BEGIN
IF (:ADDRESSREGION_ID < 10) THEN ADDRESS_NAME = '[0' || :ADDRESSREGION_ID || '] ';
ELSE ADDRESS_NAME = '[' || :ADDRESSREGION_ID || '] ';
IF (:POSORIG < 0) THEN POSORIG = OSMAIN;
IF (:POSORIG = 0) THEN ADDRESS_NAME = :ADDRESS_NAME || :ABBRNAME || ' ' || :ADRNAME;
ELSE
IF (:POSORIG = 1) THEN ADDRESS_NAME = :ADDRESS_NAME || :ADRNAME || ' ' || :ABBRNAME;
ELSE ADDRESS_NAME = :ADDRESS_NAME || :ADRNAME;
SUSPEND;
END
END ELSE BEGIN
FOR
SELECT A.ADDRESSREGION_ID, A.ADDRESS_NAME, A.ABBREV_POS, A.ABBREV_MAINPOS, A.ABBREV_SHNAME
FROM VW_REGIONS_20 A
ORDER BY A.ADDRESSREGION_ID
INTO :ADDRESSREGION_ID, :ADRNAME, OSORIG, OSMAIN, :ABBRNAME
DO BEGIN
IF (:ADDRESSREGION_ID < 10) THEN ADDRESS_NAME = '[0' || :ADDRESSREGION_ID || '] ';
ELSE ADDRESS_NAME = '[' || :ADDRESSREGION_ID || '] ';
IF (:POSORIG < 0) THEN POSORIG = OSMAIN;
IF (:POSORIG = 0) THEN ADDRESS_NAME = :ADDRESS_NAME || :ABBRNAME || ' ' || :ADRNAME;
ELSE
IF (:POSORIG = 1) THEN ADDRESS_NAME = :ADDRESS_NAME || :ADRNAME || ' ' || :ABBRNAME;
ELSE ADDRESS_NAME = :ADDRESS_NAME || :ADRNAME;
SUSPEND;
END
END
END
^
SET TERM ; ^
|
