oracle与sybase存储过程区别
oracle:
CREATE OR REPLACE PROCEDURE DMR_MaxDliuFZ
(
xTownRowid NUMBER, --区局ID --0表示所有区局
xStartTime Date, --开始时间
xEndTime Date --结束时间
)
as
vStartTime Date;datadate varchar2(8);vSQL1 varchar2(2048); vSQL2 varchar2(2048);vSQL3 varchar2(2048);
vSQL4 varchar2(2048);vSQL varchar2(18192);
begin
vStartTime:= trunc(xStartTime,'dd');
if (xTownRowid != 0) then
delete from DMR_MAXDLFZ where TOWNROWID=xTownRowid and ADLDATE>=vStartTime and ADLDATE<=trunc(xEndTime,'dd') ;
else
delete from DMR_MAXDLFZ where ADLDATE>=vStartTime and ADLDATE<=trunc(xEndTime,'dd') ;
end if;
commit;
while (vStartTime <= trunc(xEndTime,'dd')) loop
datadate:=to_char(vStartTime,'yyyyMMdd');
vsql1 := ' insert into DMR_MAXDLFZ(OI_IDF,ADL,ADLDATE,ADY,AFZ,BDL,BDLDATE,BDY,BFZ,CDL,CDLDATE,CDY,CFZ,TOWNROWID)
select qq.oi_idf,aa.dataa*ct.ctvalue ADL,aa.datadate ADLDATE,aav.dataa as ADY,
round((aa.dataa + aa.datab + aa.datac) * 0.6928*ct.ctvalue / (case when dmrt.transformersize = 0 then
1 else dmrt.transformerSize end) / 3 * 100,4) as AFZ,
bb.datab*ct.ctvalue BDL,bb.datadate BDLDATE,bbv.datab as BDY,
round((bb.dataa + bb.datab + bb.datac) * 0.6928*ct.ctvalue / (case when dmrt.transformersize = 0 then
1 else dmrt.transformerSize end) / 3 * 100, 4) as BFZ, ';
vsql2 := ' cc.datac*ct.ctvalue CDL, cc.datadate CDLDATE,
ccv.datac as CDY, round((cc.dataa + cc.datab + cc.datac) * 0.6928*ct.ctvalue / (case
when dmrt.transformersize = 0 then 1 else
dmrt.transformerSize
end) / 3 * 100,
4) as CFZ,dmrt.publicobjectrowid
from hz_zd_minmaxdl qq
inner join dmr_measurepoint dmrm on dmrm.measurepointrowid = qq.oi_idf
inner join dmr_transformer dmrt on dmrt.transformerrowid =
dmrm.transformerrowid
left join zysdliu{yyyyMMdd} aa on (qq.amaxdate = aa.datadate and
qq.oi_idf = aa.oi_idf) ';
vsql3 :=' left join zysdliu{yyyyMMdd} bb on (qq.bmaxdate = bb.datadate and
qq.oi_idf = bb.oi_idf)
left join zysdliu{yyyyMMdd} cc on (qq.cmaxdate = cc.datadate and
qq.oi_idf = cc.oi_idf)
left join zysdv{yyyyMMdd} aav on (aa.oi_idf = aav.oi_idf and
qq.amaxdate = aav.datadate)
left join zysdv{yyyyMMdd} bbv on (bb.oi_idf = bbv.oi_idf and
qq.bmaxdate = bbv.datadate) ';
vSQL4:=' left join zysdv{yyyyMMdd} ccv on (cc.oi_idf = ccv.oi_idf and
qq.cmaxdate = ccv.datadate)
inner join cj_dbctpt ct on ct.oi_idf = dmrm.measurepointrowid
where aa.oi_idf is not null
and bb.oi_idf is not null
and cc.oi_idf is not null
and qq.datadate >= '''||to_char(vStartTime,'yyyy-mm-dd')||''' and qq.datadate < '''||to_char(vStartTime+1,'yyyy-mm-dd')||'''';
vsql1:=replace(vsql1,'{yyyyMMdd}',datadate);
vsql2:=replace(vsql2,'{yyyyMMdd}',datadate);
vsql3:=replace(vsql3,'{yyyyMMdd}',datadate);
vsql4:=replace(vsql4,'{yyyyMMdd}',datadate);
vsql:= vsql1||vsql2||vsql3||vsql4;
if (xTownRowid != 0) then
vsql := vsql || ' and dmrt.publicObjectrowId = :1';
execute immediate vsql using xTownRowid;
else
execute immediate vsql;
end if;
vStartTime:= vStartTime + 1;
end loop;
commit;
end;
sybase:
CREATE PROCEDURE DMR_MaxDliuFZ
@xTownRowid numeric, --区局ID --0表示所有区局
@xStartTime datetime, --开始时间
@xEndTime datetime --结束时间
as
declare @vStartTime datetime,@datadate nvarchar(8),@vSQL1 varchar(2108), @vSQL2 varchar(2108),@vSQL3 varchar(2108),
@vSQL4 varchar(2108),@vSQL varchar(15000)
begin
select @vStartTime=convert(varchar(10),@xStartTime,111)
if (@xTownRowid != 0)
begin
delete from DMR_MAXDLFZ where TOWNROWID=@xTownRowid and ADLDATE>=@vStartTime and ADLDATE<=convert(varchar(10),@xEndTime,111)
end
else
begin
delete from DMR_MAXDLFZ where ADLDATE>=@vStartTime and ADLDATE<=convert(varchar(10),@xEndTime,111)
end
while (@vStartTime <= convert(varchar(10),@xEndTime,111))
begin
select @datadate=convert(varchar(10),@vStartTime,111)
select @vsql1 = ' insert into DMR_MAXDLFZ(OI_IDF,ADL,ADLDATE,ADY,AFZ,BDL,BDLDATE,BDY,BFZ,CDL,CDLDATE,CDY,CFZ,TOWNROWID)
select qq.oi_idf,aa.dataa*ct.ctvalue ADL,aa.datadate ADLDATE,aav.dataa as ADY,
round((aa.dataa + aa.datab + aa.datac) * 0.6928*ct.ctvalue / (case when dmrt.transformersize = 0 then
1 else dmrt.transformerSize end) / 3 * 100,4) as AFZ,
bb.datab*ct.ctvalue BDL,bb.datadate BDLDATE,bbv.datab as BDY,
round((bb.dataa + bb.datab + bb.datac) * 0.6928*ct.ctvalue / (case when dmrt.transformersize = 0 then
1 else dmrt.transformerSize end) / 3 * 100, 4) as BFZ, '
select @vsql2= ' cc.datac*ct.ctvalue CDL, cc.datadate CDLDATE,
ccv.datac as CDY, round((cc.dataa + cc.datab + cc.datac) * 0.6928*ct.ctvalue / (case
when dmrt.transformersize = 0 then 1 else
dmrt.transformerSize
end) / 3 * 100,
4) as CFZ,dmrt.publicobjectrowid
from hz_zd_minmaxdl qq
inner join dmr_measurepoint dmrm on dmrm.measurepointrowid = qq.oi_idf
inner join dmr_transformer dmrt on dmrt.transformerrowid =
dmrm.transformerrowid
left join zysdliu{yyyyMMdd} aa on (qq.amaxdate = aa.datadate and
qq.oi_idf = aa.oi_idf) '
select @vsql3 =' left join zysdliu{yyyyMMdd} bb on (qq.bmaxdate = bb.datadate and
qq.oi_idf = bb.oi_idf)
left join zysdliu{yyyyMMdd} cc on (qq.cmaxdate = cc.datadate and
qq.oi_idf = cc.oi_idf)
left join zysdv{yyyyMMdd} aav on (aa.oi_idf = aav.oi_idf and
qq.amaxdate = aav.datadate)
left join zysdv{yyyyMMdd} bbv on (bb.oi_idf = bbv.oi_idf and
qq.bmaxdate = bbv.datadate) '
select @vSQL4=' left join zysdv{yyyyMMdd} ccv on (cc.oi_idf = ccv.oi_idf and
qq.cmaxdate = ccv.datadate)
inner join cj_dbctpt ct on ct.oi_idf = dmrm.measurepointrowid
where aa.oi_idf is not null
and bb.oi_idf is not null
and cc.oi_idf is not null
and qq.datadate >= '''+convert(varchar(10),@vStartTime,111)+''' and qq.datadate < '''+convert(varchar(10),DateAdd(dd,1,@vStartTime),111)+''''
select @vsql1=str_replace(@vsql1,'{yyyyMMdd}',@datadate)
select @vsql2=str_replace(@vsql2,'{yyyyMMdd}',@datadate)
select @vsql3=str_replace(@vsql3,'{yyyyMMdd}',@datadate)
select @vsql4=str_replace(@vsql4,'{yyyyMMdd}',@datadate)
select @vsql= @vsql1+@vsql2+@vsql3+@vsql4
if (@xTownRowid != 0)
begin
select @vsql = @vsql + ' and dmrt.publicObjectrowId = ' + convert(varchar,@xTownRowid)
end
exec(@vsql)
select @vStartTime = DateAdd(dd,1,@vStartTime)
end
end
