SET NOCOUNT ON
go
sp_configure 'show advanced options' , 1
go
Reconfigure with Override
go
sp_configure 'Ole Automation Procedures' , 1
go
Reconfigure with Override
Go
if exists (select * from sys.objects where type = 'P' AND name = 'IKITARIHARASI_MERKEZBANKASI_KUR')
drop procedure IKITARIHARASI_MERKEZBANKASI_KUR
go
Create proc IKITARIHARASI_MERKEZBANKASI_KUR
(@BegDate DATETIME,@EndDate DATETIME)
AS
BEGIN
IF not exists (select * from sys.tables where name = N'MERKEZBANKASI_DOVIZ_KURLARI' and type = 'U')
BEGIN
CREATE TABLE MERKEZBANKASI_DOVIZ_KURLARI (Tarih date,Kod NVarchar(5),[Türkçe Adı] varchar(100),[İngilizce Adı] varchar(100) ,[Alış] float ,[Satış] float,[Efektif Alış] float,[Efektif Satış] float)
CREATE CLUSTERED INDEX DXC1 ON MERKEZBANKASI_DOVIZ_KURLARI (Tarih)
CREATE NONCLUSTERED INDEX DC1 ON MERKEZBANKASI_DOVIZ_KURLARI (Kod,[Türkçe Adı]) INCLUDE(Tarih)
CREATE NONCLUSTERED INDEX DC2 ON MERKEZBANKASI_DOVIZ_KURLARI (Tarih,[Türkçe Adı]) INCLUDE(KOD)
END
DECLARE @YIL_AL Smallint, @AY_AL TinyInt, @GUN_AL TinyInt
DECLARE TARIHLISTELEME CURSOR FOR
WITH n1 AS (SELECT n FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))t(n))
, n2 AS (SELECT n.n FROM n1 AS n CROSS JOIN n1)
, n3 AS (SELECT n.n FROM n2 AS n CROSS JOIN n2)
, nums AS (SELECT 0 num UNION ALL SELECT Row_Number() OVER (ORDER BY(SELECT NULL)) num FROM n3)
, TARIHLISTE (BegDt, EndDt) AS (
SELECT
DateAdd(DAY, nums.num, @BegDate)
,DateAdd(MILLISECOND, -3, DateAdd(DAY, 1, DateAdd(week, nums.num,@BegDate)))
FROM nums
)
SELECT
YEAR(c.BegDt) YEAR_,
MONTH(C.BEGDT) MONTH_,
DAY(C.BEGDT) DAY_
FROM
TARIHLISTE c
WHERE
c.BegDt <= @EndDate
OPEN TARIHLISTELEME
FETCH NEXT FROM TARIHLISTELEME INTO @YIL_AL , @AY_AL , @GUN_AL
WHILE @@FETCH_STATUS=0
BEGIN
Declare @url as varchar(8000)
Declare @XmlYilAy NVarchar(6), @XmlTarih NVarchar(10)
Set @XmlYilAy = Right('0000' + cast(@YIL_AL as varchar(4)) , 4) + Right('00' + cast(@AY_AL as varchar(2)) , 2)
Set @XmlTarih = Right('00' + cast(@GUN_AL as varchar(2)) , 2) + Right('00' + cast(@AY_AL as varchar(2)) , 2) + Right('0000' + cast(@YIL_AL as varchar(4)) , 4)
If DateFromParts(@YIL_AL, @AY_AL, @GUN_AL) = DateAdd(dd,0,DateDiff(dd,0,GetDate()))
Set @url = 'https://www.tcmb.gov.tr/kurlar/today.xml'
else
Set @url = 'https://www.tcmb.gov.tr/kurlar/' + @XmlYilAy + '/' + @XmlTarih + '.xml'
declare @OBJ AS INT
declare @RESULT AS INT
EXEC @RESULT = SP_OACREATE 'MSXML2.XMLHTTP', @OBJ OUT
EXEC @RESULT = SP_OAMethod @OBJ , 'open' , null , 'GET', @url, false
EXEC @RESULT = SP_OAMethod @OBJ, send, NULL,''
If OBJECT_ID('tempdb..#XML') IS NOT Null DROP TABLE #XML
Create table #XML ( STRXML varchar(max))
Insert INTO #XML(STRXML) EXEC @RESULT = SP_OAGetProperty @OBJ, 'responseXML.xml'
DECLARE @XML AS XML
SELECT @XML = STRXML FROM #XML
DROP TABLE #XML
DECLARE @HDOC AS INT
EXEC SP_XML_PREPAREDOCUMENT @HDOC OUTPUT , @XML
Delete from MERKEZBANKASI_DOVIZ_KURLARI where tarih = DateFromParts(@YIL_AL, @AY_AL, @GUN_AL)
INSERT INTO MERKEZBANKASI_DOVIZ_KURLARI (Tarih,Kod,[Türkçe Adı],[İngilizce Adı],[Alış],[Satış],[Efektif Alış],[Efektif Satış])
SELECT DateFromParts(@YIL_AL, @AY_AL, @GUN_AL) As Tarih,
* FROM OPENXML(@HDOC, 'Tarih_Date/Currency')
With (Kod Varchar(5),Isim varchar(100) 'Isim',CurrencyName varchar(100) 'CurrencyName',ForexBuying float 'ForexBuying',ForexSelling float 'ForexSelling',BanknoteBuying float 'BanknoteBuying',BanknoteSelling float 'BanknoteSelling')
FETCH NEXT FROM TARIHLISTELEME INTO @YIL_AL , @AY_AL , @GUN_AL
END
CLOSE TARIHLISTELEME
DEALLOCATE TARIHLISTELEME
END
GO
EXECUTE IKITARIHARASI_MERKEZBANKASI_KUR '20191101','20191105'
GO
SELECT * FROM MERKEZBANKASI_DOVIZ_KURLARI
go
sp_configure 'show advanced options' , 1
go
Reconfigure with Override
go
sp_configure 'Ole Automation Procedures' , 1
go
Reconfigure with Override
Go
if exists (select * from sys.objects where type = 'P' AND name = 'IKITARIHARASI_MERKEZBANKASI_KUR')
drop procedure IKITARIHARASI_MERKEZBANKASI_KUR
go
Create proc IKITARIHARASI_MERKEZBANKASI_KUR
(@BegDate DATETIME,@EndDate DATETIME)
AS
BEGIN
IF not exists (select * from sys.tables where name = N'MERKEZBANKASI_DOVIZ_KURLARI' and type = 'U')
BEGIN
CREATE TABLE MERKEZBANKASI_DOVIZ_KURLARI (Tarih date,Kod NVarchar(5),[Türkçe Adı] varchar(100),[İngilizce Adı] varchar(100) ,[Alış] float ,[Satış] float,[Efektif Alış] float,[Efektif Satış] float)
CREATE CLUSTERED INDEX DXC1 ON MERKEZBANKASI_DOVIZ_KURLARI (Tarih)
CREATE NONCLUSTERED INDEX DC1 ON MERKEZBANKASI_DOVIZ_KURLARI (Kod,[Türkçe Adı]) INCLUDE(Tarih)
CREATE NONCLUSTERED INDEX DC2 ON MERKEZBANKASI_DOVIZ_KURLARI (Tarih,[Türkçe Adı]) INCLUDE(KOD)
END
DECLARE @YIL_AL Smallint, @AY_AL TinyInt, @GUN_AL TinyInt
DECLARE TARIHLISTELEME CURSOR FOR
WITH n1 AS (SELECT n FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))t(n))
, n2 AS (SELECT n.n FROM n1 AS n CROSS JOIN n1)
, n3 AS (SELECT n.n FROM n2 AS n CROSS JOIN n2)
, nums AS (SELECT 0 num UNION ALL SELECT Row_Number() OVER (ORDER BY(SELECT NULL)) num FROM n3)
, TARIHLISTE (BegDt, EndDt) AS (
SELECT
DateAdd(DAY, nums.num, @BegDate)
,DateAdd(MILLISECOND, -3, DateAdd(DAY, 1, DateAdd(week, nums.num,@BegDate)))
FROM nums
)
SELECT
YEAR(c.BegDt) YEAR_,
MONTH(C.BEGDT) MONTH_,
DAY(C.BEGDT) DAY_
FROM
TARIHLISTE c
WHERE
c.BegDt <= @EndDate
OPEN TARIHLISTELEME
FETCH NEXT FROM TARIHLISTELEME INTO @YIL_AL , @AY_AL , @GUN_AL
WHILE @@FETCH_STATUS=0
BEGIN
Declare @url as varchar(8000)
Declare @XmlYilAy NVarchar(6), @XmlTarih NVarchar(10)
Set @XmlYilAy = Right('0000' + cast(@YIL_AL as varchar(4)) , 4) + Right('00' + cast(@AY_AL as varchar(2)) , 2)
Set @XmlTarih = Right('00' + cast(@GUN_AL as varchar(2)) , 2) + Right('00' + cast(@AY_AL as varchar(2)) , 2) + Right('0000' + cast(@YIL_AL as varchar(4)) , 4)
If DateFromParts(@YIL_AL, @AY_AL, @GUN_AL) = DateAdd(dd,0,DateDiff(dd,0,GetDate()))
Set @url = 'https://www.tcmb.gov.tr/kurlar/today.xml'
else
Set @url = 'https://www.tcmb.gov.tr/kurlar/' + @XmlYilAy + '/' + @XmlTarih + '.xml'
declare @OBJ AS INT
declare @RESULT AS INT
EXEC @RESULT = SP_OACREATE 'MSXML2.XMLHTTP', @OBJ OUT
EXEC @RESULT = SP_OAMethod @OBJ , 'open' , null , 'GET', @url, false
EXEC @RESULT = SP_OAMethod @OBJ, send, NULL,''
If OBJECT_ID('tempdb..#XML') IS NOT Null DROP TABLE #XML
Create table #XML ( STRXML varchar(max))
Insert INTO #XML(STRXML) EXEC @RESULT = SP_OAGetProperty @OBJ, 'responseXML.xml'
DECLARE @XML AS XML
SELECT @XML = STRXML FROM #XML
DROP TABLE #XML
DECLARE @HDOC AS INT
EXEC SP_XML_PREPAREDOCUMENT @HDOC OUTPUT , @XML
Delete from MERKEZBANKASI_DOVIZ_KURLARI where tarih = DateFromParts(@YIL_AL, @AY_AL, @GUN_AL)
INSERT INTO MERKEZBANKASI_DOVIZ_KURLARI (Tarih,Kod,[Türkçe Adı],[İngilizce Adı],[Alış],[Satış],[Efektif Alış],[Efektif Satış])
SELECT DateFromParts(@YIL_AL, @AY_AL, @GUN_AL) As Tarih,
* FROM OPENXML(@HDOC, 'Tarih_Date/Currency')
With (Kod Varchar(5),Isim varchar(100) 'Isim',CurrencyName varchar(100) 'CurrencyName',ForexBuying float 'ForexBuying',ForexSelling float 'ForexSelling',BanknoteBuying float 'BanknoteBuying',BanknoteSelling float 'BanknoteSelling')
FETCH NEXT FROM TARIHLISTELEME INTO @YIL_AL , @AY_AL , @GUN_AL
END
CLOSE TARIHLISTELEME
DEALLOCATE TARIHLISTELEME
END
GO
EXECUTE IKITARIHARASI_MERKEZBANKASI_KUR '20191101','20191105'
GO
SELECT * FROM MERKEZBANKASI_DOVIZ_KURLARI
Yorumlar
Yorum Gönder