Sybase与Oracle存储过程的区别
来源：互联网 发布：linux为什么喜欢开源 编辑：程序博客网 时间：2024/05/17 19:58
Sybase Oracle 传入、传出参数 参数名前加@符号 正常命名 变量声明 declare后，变量名前加@符号 as后 用逗号,最后一个变量后无逗号 用分号，最后一个变量后有分号变量赋值 select @number='11' number:= '11' 无符号,最后一个变量后无逗号 用分号，最后一个变量后有分号存储过程调用 exec SAP_GN_LOG_02 @STR_DTE_CHAR, @END_DTE_CHAR exec SAP_GMN_LOG(STR_DTE_CHAR,END_DTE_CHAR) 控制台输出 DBMS_OUTPUT.PUTLINE(Result) print Resultend end 后不打分号 end后有分号语法 CREATE PROCEDURE DMR_MaxDliuFZ( CREATE OR REPLACE PROCEDURE DMR_MaxDliuFZ ( @xTownRowid numeric, xTownRowid NUMBER, --区局ID --0表示所有区局 @xStartTime datetime, xStartTime Date, --开始时间 @xEndTime datetime xEndTime Date --结束时间 ) ) as declare @vTownRowid Number, as vTownRowid Number @vStartTime datetime, vStartTime Date; @datadate nvarchar(8), datadate varchar2(8); @vSQL1 varchar(2108) vSQL1 varchar2(2048); begin begin select @vTownRowid=xTownRowid vTownRowid:= xTownRowid; select @vStartTime=convert(varchar(10),@xStartTime,111) vStartTime:= trunc(xStartTime,'dd'); commit; end end;
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
- Sybase与Oracle存储过程的区别
- sybase与oracle存储过程的写法对比
- sybase与oracle的区别
- oracle存储过程与函数的区别
- oracle中存储函数与存储过程的区别
- Sybase存储过程的使用
- Sybase中存储过程的建立与使用
- oracle与mysql中存储过程的区别
- Oracle与MySQL存储过程以及游标使用的区别
- db2 下面调用存储过程与Oracle调用的区别
- oracle存储过程与函数区别
- Oracle 存储过程 与 函数 区别
- Oracle 存储过程 与 函数 区别
- 如何快速导出SYBASE的存储过程
- ODBC调用sybase的存储过程
- sybase存储过程的建立和使用
- ODBC调用sybase的存储过程
- sybase中几个有用的存储过程
- 嵌入式Linux设备驱动开发笔记（三）
- 弹出式线程&使用单线程代码多线程化
- ORA-12514 TNS 监听程序当前无法识别连接描述符中请求服务
- PHP操作文件夹文件、文件
- c# 无法加载DLL“###.dll”,: 找不到指定的模块。 (异常来自 HRESULT:0x8007007E)，解决办法总结
- Sybase与Oracle存储过程的区别
- redis-hash
- Glide 缓存策略 内存缓存和磁盘缓存
- 使用AMR9开发板（zedboard）基于opencv读取并处理网络监控相机rtsp视频流
- 淘淘商城系列——修改购物车商品数量
- Windows资源监视器软件的原理
- redis-list
- ThreadLocal 是什么，
- Ajax 传json 数组的问题给spring
