Oracle Produre ile Doviz Kurlari Alma

create or replace PROCEDURE DOVIZ_KUR_GUNCELLE
IS
MerBank_url HTTPURITYPE;
MerBank_xml XMLTYPE;
BEGIN
MerBank_url :=
httpuritype.createuri('http://www.tcmb.gov.tr/kurlar/today.xml');
MerBank_xml := MerBank_url.getxml ();

FOR rec_
IN (SELECT EXTRACTVALUE (VALUE (x), '/Tarih_Date/@Tarih') Tarih,
EXTRACTVALUE (VALUE (p), '/Currency/@CurrencyCode') Dovkod,
EXTRACTVALUE (VALUE (p), '/Currency/ForexBuying') Doval,
EXTRACTVALUE (VALUE (p), '/Currency/ForexSelling') Dovsat,
EXTRACTVALUE (VALUE (p), '/Currency/BanknoteBuying') Efal,
EXTRACTVALUE (VALUE (p), '/Currency/BanknoteSelling') Efsat
FROM TABLE (XMLSEQUENCE (EXTRACT (MerBank_xml, '/Tarih_Date'))) x,
TABLE (XMLSEQUENCE (EXTRACT (VALUE (x), '/Tarih_Date/Currency'))) p
WHERE EXTRACTVALUE (VALUE (p), '/Currency/@CurrencyCode') IN
('USD', 'EUR'))
LOOP
BEGIN
INSERT INTO TAGE0002 (DOVKURKOD,
DOVKOD,
KURTAR,
DOVAL,
DOVSAT,
EFAL,
EFSAT)
SELECT 1,
TO_CHAR (rec_.Dovkod),
rec_.Tarih,
NVL (REPLACE (rec_.Doval, '.', ','), 0),
NVL (REPLACE (rec_.Dovsat, '.', ','), 0),
NVL (REPLACE (rec_.Efal, '.', ','), 0),
NVL (REPLACE (rec_.Efsat, '.', ','), 0)
FROM DUAL;

COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
UPDATE TAGE0002 SET DOVAL = NVL (REPLACE (rec_.Doval, '.', ','), 0),
DOVSAT = NVL (REPLACE (rec_.Dovsat, '.', ','), 0),
EFAL = NVL (REPLACE (rec_.Efal, '.', ','), 0),
EFSAT = NVL (REPLACE (rec_.Efsat, '.', ','), 0)
WHERE DOVKURKOD = 1 AND DOVKOD = TO_CHAR (rec_.Dovkod) AND KURTAR = rec_.Tarih ;
COMMIT;
WHEN OTHERS
THEN
NULL;
END;
END LOOP;
END;

Yorum ekle

Loading