matlab调用SQL数据库代码示例(ODBC)

function [rtn dt name] = getsqldata(code,dtstart,dtend)
% code = '600000';
% dtstart = '2009-01-01';
% dtend = '2009-12-30';
%convert(varchar,b.tradingday,101) as dt
setdbprefs('datareturnformat','cellarray');
k = 0;
on = 1;
while(k <= 3 && on)
conn = database('jydb','sa','qwe123');
if conn.AutoCommit=='on'
on = 0;
end
k = k+1;
end
str1 = sprintf(['drop table rtntran DECLARE @StartDate DATETIME,@EndDate DATETIME,@StockCode VARCHAR(20) '...
'SET @StartDate = ''%s'' SET @EndDate = ''%s'' SET @StockCode = ''%s'' '...
'select distinct b.secucode,b.secuabbr,b.innercode,a.*,c.firstindustrycode,c.firstindustryname '...
'into #hs300 '...
'from jydb.dbo.LC_IndexComponent a,jydb.dbo.SecuMain b,jydb.dbo.LC_ExgIndustry c '...
'where IndexInnerCode=''3145'' and flag=1 and a.secuinnercode=b.innercode '...
'and https://www.360docs.net/doc/e39270119.html,panycode=https://www.360docs.net/doc/e39270119.html,panycode and c.standard=3 and B. SecuCategory =1 and c.IfPerformed = 1 '...
'select b.* into #sameindus from #hs300 a,#hs300 b where a.secucode=@stockcode '...
'and b.firstindustrycode=a.firstindustrycode '...
'SELECT A.InnerCode,https://www.360docs.net/doc/e39270119.html,panyCode,A.SecuCode,A.SecuAbbr,B.tradingday as dt , '...
'B.ClosePrice as price_close, '...
'B.ClosePrice * ISNULL(C.RatioAdjustingFactor,1) as price_after, '...
'B.ClosePrice *ISNULL(C.RatioAdjustingFactor,1)/ISNULL(D.RatioAdjustingFactor,1) as price_before '...
'into #rawdata '...
'FROM jydb.dbo.SecuMain A '...
'left JOIN jydb.dbo.QT_DailyQuote B ON A.InnerCode=B.InnerCode '...
'left JOIN jydb.dbo.QT_AdjustingFactor C ON A.InnerCode=C.InnerCode AND C.ExDiviDate=(SELECT TOP 1 ExDiviDate FROM jydb.dbo.QT_AdjustingFactor WHERE InnerCode=A.InnerCode AND ExDiviDate<=B.TradingDay ORDER BY ExDiviDate DESC) '...
'LEFT JOIN jydb.dbo.QT_AdjustingFactor D ON A.InnerCode=D.InnerCode AND D.ExDiviDate=(SELECT TOP 1 ExDiviDate FROM jydb.dbo.QT_AdjustingFactor WHERE InnerCode=A.InnerCode ORDER BY ExDiviDate DESC) '...
'WHERE A.InnerCode in (select innercode from #sameindus) and A.SecuCategory=1 '...
'and B.TradingDay between @startdate and @enddate '...
'order BY A.InnerCode,B.TradingDay '...
'select a.dt,a.secucode,a.secuabbr,a.price_after/b.price_after-1 as rtn '...
'into #datartn '...
'from #rawdata a,#rawdata b '...
'where a.innercode=b.innercode and b.dt=(select top 1 dt from #rawdata where dt'order BY a.dt,a.secucode '...
'select * into #y_rtn from #datartn where secucode=@stockcode order by secucode,dt '...
'select * into #x_rtn from #datartn where secucode!=@stockcode order by secucode,dt '...
'declare @sql varchar(8000) set @sql = ''select dt '' '...
'select @sql = @sql + '' , max(case secuabbr when '''''' + secuabbr + '''''' then rtn else NULL end) ['' + secuabbr + '']'' '...
'from (select distinct top 3000 secuabbr from #x_rtn order by secuabbr) as a '...
'set @sql = @sql + '' into rtntran from #x_rtn group by dt order by dt'' '...
'exec(@sql) '],dtstart,dtend,code);
str2 = ['select b.*,a.rtn from #y_rtn a left join rtntran b

on a.dt=b.dt order by b.dt '];
str3 = ['select distinct secuabbr from #x_rtn order by secuabbr'];

cur = exec(conn,str1);cur.Message
cur = exec(conn,str2);cur.Message
cur = fetch(cur);data = cur.data;
[m n] = size(data);dt = data(:,1);rtn = cell2mat(data(:,2:n));
cur = exec(conn,str3);cur.Message
cur = fetch(cur);name = cur.data;name = name';
cur = exec(conn,'drop table rtntran');
close(conn);
end

% cur=exec(conn,'declare @stockcode varchar(20) set @stockcode=s select * into #f from jydb.dbo.secumain where secucode=@stockcode');
% cur=exec(conn,'select * from #f');
% tmp=fetch(cur);
% a=tmp.data;
% stock='600000';tt=sprintf('declare @stockcode varchar(20) set @stockcode=%s select * into #f from jydb.dbo.secumain where secucode=@stockcode',stock)
% cur=exec(conn,tt);