Загрузка курсов валют с ЦБ РФ из PL/SQL внутренними средствами Oracle

Встала задача грузить курсы валют прямо в базу Oracle. В принципе ничего сложного нет — wget-ом получаем файл, парсим и кидаем по табличкам. Однако это не самый прямой путь — ибо СУБД Oracle прекрасно может справиться с этой задачей полностью самостоятельно:

1. Создаем структуру для хранения информации:

1.1 табличка VALUTA — справочник валют.


CREATE TABLE VALUTA
(
IDVL NUMBER(3) NOT NULL,
NAME VARCHAR2(100 BYTE) NOT NULL,
SIMBOL VARCHAR2(1 BYTE),
MEMO VARCHAR2(256 BYTE),
DEF NUMBER(1),
ID VARCHAR2(10 BYTE),
NUMCODE NUMBER(3),
CHARCODE VARCHAR2(3 BYTE)
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE VALUTA IS 'Справочник валют';
COMMENT ON COLUMN VALUTA.ID IS 'числовой идентификатор валюты ЦБ - внутренний';
COMMENT ON COLUMN VALUTA.NUMCODE IS 'Стандартный числовой идентификатор валюты ЦБ';
COMMENT ON COLUMN VALUTA.CHARCODE IS 'Стандартный символьный идентификатор валюты - наиболее общеупотребимый';
COMMENT ON COLUMN VALUTA.IDVL IS 'Номер валюты';
COMMENT ON COLUMN VALUTA.NAME IS 'Наименование';
COMMENT ON COLUMN VALUTA.SIMBOL IS 'Символ';
COMMENT ON COLUMN VALUTA.MEMO IS 'Примечание';
COMMENT ON COLUMN VALUTA.DEF IS 'признак основной =1';

CREATE UNIQUE INDEX PK_VALUTA_IDVL ON VALUTA
(IDVL)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;

CREATE UNIQUE INDEX UK_VALUTA_NAME ON VALUTA
(NAME)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;

CREATE UNIQUE INDEX UK_VALUTA_SIMBOL ON VALUTA (SIMBOL)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;

ALTER TABLE VALUTA ADD ( CONSTRAINT PK_VALUTA_IDVL PRIMARY KEY
(IDVL)
USING INDEX
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
),
CONSTRAINT UK_VALUTA_SIMBOL
UNIQUE (SIMBOL)
USING INDEX
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
),
CONSTRAINT UK_VALUTA_NAME
UNIQUE (NAME)
USING INDEX
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
));


1.2 создаем табличку для хранения истории курсов:

CREATE TABLE VALUTA_COURSE
(
IDVL NUMBER(3) NOT NULL,
DATA DATE NOT NULL,
COURSE NUMBER(20,12) NOT NULL,
NOMINAL VARCHAR2(20 BYTE) NOT NULL,
VALUE VARCHAR2(20 BYTE) NOT NULL
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE VALUTA_COURSE IS 'Курсы валют автоматически наполняемый справочник';
COMMENT ON COLUMN VALUTA_COURSE.NOMINAL IS 'Номинал';
COMMENT ON COLUMN VALUTA_COURSE.VALUE IS 'Стоимость номинала';
COMMENT ON COLUMN VALUTA_COURSE.IDVL IS 'Номер валюты';
COMMENT ON COLUMN VALUTA_COURSE.DATA IS 'Дата начала действия курса';
COMMENT ON COLUMN VALUTA_COURSE.COURSE IS 'Сам курс валюты по отношению к основной валюте';

CREATE UNIQUE INDEX PK_VALUTA_COURSE_IDVL_DATA ON VALUTA_COURSE
(IDVL, DATA)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;

ALTER TABLE VALUTA_COURSE ADD (
CONSTRAINT PK_VALUTA_COURSE_IDVL_DATA
PRIMARY KEY
(IDVL, DATA)
USING INDEX
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
));


2. Cобственно загрузка:

CREATE OR REPLACE FUNCTION "LOAD_VAL_ON_DATE" (date_in date)
return number IS
PRAGMA AUTONOMOUS_TRANSACTION;

req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
name VARCHAR2 ( 256 );
value VARCHAR2 ( 4000 );
data_course date ;
data_load VARCHAR2 ( 20 );
htmlclob clob;

-- функция загрузки валют с центробанка
-- входные параметры
-- date_in - дата на которую надо запрсить курсы
-- на выход - код выполнения
-- 1 -- успешно
-- 0 -- ошибка
-- -1 -- считнана иная дата

BEGIN
track:=0;
BEGIN
-- загрузка
req := UTL_HTTP.BEGIN_REQUEST ( 'http://www.cbr.ru/scripts/XML_daily.asp?date_req='||to_char(date_in,'DD/MM/YYYY'), method => 'GET' );
utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');
UTL_HTTP.SET_BODY_CHARSET(req,'CL8MSWIN1251');
resp := UTL_HTTP.GET_RESPONSE ( req );

LOOP
UTL_HTTP.READ_LINE ( resp, value, TRUE );
htmlclob := htmlclob || value;
END LOOP;

EXCEPTION
WHEN UTL_HTTP.END_OF_BODY
THEN UTL_HTTP.END_RESPONSE ( resp );
END;


В этой части мы запрашиваем с сайта XML c курсами на заданную дату
и считываем его в CLOB

Идем дальше — автоматически наполняем справочник валют
-- парсинг

-- 1. пополнение справочника валют - VALUTA

INSERT INTO VALUTA (IDVL,ID,NUMCODE,CHARCODE,NAME)
(
select sec_valuta.nextval IDVL,ID,NUMCODE,CHARCODE,NAME from
( select ID, to_number(NumCode) NumCode, CharCode, name
from (select d.extract('//@ID').getStringVal() ID,
d.extract('//Valute/NumCode/text()').getStringVal() NumCode,
d.extract('//Valute/CharCode/text()').getStringVal() CharCode,
d.extract('//Valute/Name/text()').getStringVal() Name
from table( cast( XMLSequence(XMLType(htmlclob ).extract('ValCurs/Valute')) as table_xmltype) ) d )
where NumCode!='NULL'
minus
select ID,NUMCODE NUMCODE,CHARCODE,NAME from valuta
)
);
commit ;


Теперь загрузка в табличку истории курсов валют
-- 2. Загрузка в VALUTA_COURSE
-- 2.1 извлечем дату

select to_date(d.extract('//ValCurs//@Date').getStringVal(),'DD/MM/YYYY') into data_course
from table( cast( XMLSequence(XMLType(htmlclob ).extract('ValCurs')) as table_xmltype )) d;

-- 2.2 Проверим ту ли дату мы получили которую мы хотели ?
if (date_in!=data_course) then
return (-1); -- считана иная дата
end if;

если нет в базе запрашеваемого, ЦБ-шный сервер отдает последний курс на последнюю дату — сотвественно мы может получить не те данные которые хотели. Отрабатываем это исключение.

теперь все нормально: собственно парсинг XML и загрузка в табличку:

-- 2.3 загрузка в VALUTA_COURSE
insert into valuta_course (IDVL,data,course,nominal,value)
(
select v.IDVL, data_course as data , to_number(e.value/e.nominal,'99999999.99999999999') course, e.nominal ,e.value from
(select
d.extract('//Valute/CharCode/text()').getStringVal() CharCode,
to_number(replace(replace(replace(d.extract('//Valute/Nominal/text()').getStringVal(),',','.'),' ',null),' ',null) ,'999999999999') Nominal,
to_number(replace(replace(replace(d.extract('//Valute/Value/text()') .getStringVal(),',','.'),' ',null),' ',null) ,'99999999.9999999') Value
from table( cast( XMLSequence(XMLType(htmlclob ).extract('ValCurs/Valute')) as table_xmltype )) d
) e , valuta v
where e.CharCode=v.CharCode
) ;

commit;
return(1);


Отработка исключений — собственно в рабочем скрипте, после rollback cтоит вызов логера.
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
return(1); -- игнорируем дубли
WHEN others THEN
rollback;
return(-1);
END;
-- ----------------------------------------
-- - Script information -
-- - функция загрузки валют с центробанка -
-- ----------------------------------------
-- - $Revision :: 1 -
-- - $JustDate :: ******* -
-- - $Author :: Nagisa -
-- - $NoKeywords :: -
-- ----------------------------------------
--;
/


3. Проверка — вызов select LOAD_VAL_ON_DATE(sysdate) from dual;

Итого: для загрузки курса валют нам остается настроить JOB для вызова этой функции.