这是从sql server 2000中导出的一段脚本.在oracle 9i 的pl/sql中肯定是行不通的呀. 我参照oracle 语法改了很长时间编译的时候总是出错. 望各位大虾能帮根据oracle的语法修改一下.跪谢了:--)还有SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON 各表示什么意思.????? CREATE PROCEDURE kctz_ckjs ASdeclare @spdm char(8), @sl integer, @ssljs integer, @jsljs integer, @xsljs integer, @sysljs integer, @sssljs integer, @ysjs integer, @dbjs integer truncate table t_ckspmx_js insert t_ckspmx_js (spdm,jsljs) select spdm,sum(sl) from t_ysdsp group by spdm declare yscx insensitive cursor for select spdm, sl=sum(sl) from t_ysdsp_cx group by spdm open yscx fetch yscx into @spdm,@sl while (@@fetch_status=0) begin if not exists(select * from t_ckspmx_js where spdm=@spdm) insert into t_ckspmx_js (spdm,jsljs) values (@spdm, @sl) else update t_ckspmx_js set jsljs=jsljs + @sl where spdm=@spdm fetch yscx into @spdm, @sl end close yscx deallocate yscxdeclare ps insensitive cursor for select spdm, sl=sum(ccsl) from t_psdsp group by spdm open ps fetch ps into @spdm,@sl while (@@fetch_status=0) begin if not exists(select * from t_ckspmx_js where spdm=@spdm) insert into t_ckspmx_js (spdm,xsljs) values (@spdm, @sl) else update t_ckspmx_js set xsljs=xsljs + @sl where spdm=@spdm fetch ps into @spdm, @sl end close ps deallocate ps declare pscx insensitive cursor for select spdm, sl=sum(ccsl) from t_psdsp_cx group by spdm open pscx fetch pscx into @spdm,@sl while (@@fetch_status=0) begin if not exists(select * from t_ckspmx_js where spdm=@spdm) insert into t_ckspmx_js (spdm,xsljs) values (@spdm, @sl) else update t_ckspmx_js set xsljs=xsljs + @sl where spdm=@spdm fetch pscx into @spdm, @sl end close pscx deallocate pscx declare pf insensitive cursor for select spdm, sl=sum(ccsl) from t_pfdsp group by spdm open pf fetch pf into @spdm,@sl while (@@fetch_status=0) begin if not exists(select * from t_ckspmx_js where spdm=@spdm) insert into t_ckspmx_js (spdm, xsljs) values (@spdm,@sl) else update t_ckspmx_js set xsljs=xsljs + @sl where spdm=@spdm fetch pf into @spdm,@sl end close pf deallocate pf declare pfcx insensitive cursor for select spdm, sl=sum(ccsl) from t_pfdsp_cx group by spdm open pfcx fetch pfcx into @spdm,@sl while (@@fetch_status=0) begin if not exists(select * from t_ckspmx_js where spdm=@spdm) insert into t_ckspmx_js (spdm, xsljs) values (@spdm,@sl) else update t_ckspmx_js set xsljs=xsljs + @sl where spdm=@spdm fetch pfcx into @spdm,@sl end close pfcx deallocate pfcx declare pt insensitive cursor for select spdm, sl=sum(sl) from t_ptdsp group by spdm open pt fetch pt into @spdm,@sl while (@@fetch_status=0) begin if not exists(select * from t_ckspmx_js where spdm=@spdm) insert into t_ckspmx_js (spdm, xsljs) values (@spdm, @sl) else update t_ckspmx_js set xsljs=xsljs + @sl where spdm=@spdm fetch pt into @spdm ,@sl end close pt deallocate pt declare ptcx insensitive cursor for select spdm, sl=sum(sl) from t_ptdsp_cx group by spdm open ptcx fetch ptcx into @spdm,@sl while (@@fetch_status=0) begin if not exists(select * from t_ckspmx_js where spdm=@spdm) insert into t_ckspmx_js (spdm, xsljs) values (@spdm, @sl) else update t_ckspmx_js set xsljs=xsljs + @sl where spdm=@spdm fetch ptcx into @spdm ,@sl end close ptcx deallocate ptcxdeclare pft insensitive cursor for select spdm, sl=sum(sl) from t_ptdsp_pf group by spdm open pft fetch pft into @spdm,@sl while (@@fetch_status=0) begin if not exists(select * from t_ckspmx_js where spdm=@spdm) insert into t_ckspmx_js (spdm, xsljs) values (@spdm, @sl) else update t_ckspmx_js set xsljs=xsljs + @sl where spdm=@spdm fetch pft into @spdm ,@sl end close pft deallocate pftdeclare pftcx insensitive cursor for select spdm, sl=sum(sl) from t_ptdsp_pf_cx group by spdm open pftcx fetch pftcx into @spdm,@sl while (@@fetch_status=0) begin if not exists(select * from t_ckspmx_js where spdm=@spdm) insert into t_ckspmx_js (spdm, xsljs) values (@spdm, @sl) else update t_ckspmx_js set xsljs=xsljs + @sl where spdm=@spdm fetch pftcx into @spdm ,@sl end close pftcx deallocate pftcx declare sy insensitive cursor for select spdm, sl=sum(sl) from t_sydsp group by spdm open sy fetch sy into @spdm,@sl while (@@fetch_status=0) begin if not exists(select * from t_ckspmx_js where spdm=@spdm) insert into t_ckspmx_js (spdm, sysljs) values (@spdm, @sl) else update t_ckspmx_js set sysljs=sysljs + @sl where spdm=@spdm fetch sy into @spdm ,@sl end close sy deallocate sy declare sycx insensitive cursor for select spdm, sl=sum(sl) from t_sydsp_cx group by spdm open sycx fetch sycx into @spdm,@sl while (@@fetch_status=0) begin if not exists(select * from t_ckspmx_js where spdm=@spdm) insert into t_ckspmx_js (spdm, sysljs) values (@spdm, @sl) else update t_ckspmx_js set sysljs=sysljs + @sl where spdm=@spdm fetch sycx into @spdm ,@sl end close sycx deallocate sycxdeclare ckkc insensitive cursor for select spdm, ssljs = sum(ssl) , jsljs = sum(jsl), xsljs = sum(xsl), sysljs = sum(sysl), sssljs = sum(sssl),ysjs=sum(ys),dbjs=sum(dbsl) from t_ckspmx group by spdm open ckkc fetch ckkc into @spdm , @ssljs , @jsljs , @xsljs , @sysljs , @sssljs , @ysjs,@dbjs while (@@fetch_status=0) begin if not exists(select * from t_ckspmx_js where spdm = @spdm) insert into t_ckspmx_js (spdm, sslck , jslck, xslck, syslck, ssslck,ysck,dbck) values (@spdm, @ssljs , @jsljs, @xsljs, @sysljs , @sssljs , @ysjs,@dbjs) else update t_ckspmx_js set sslck = sslck + @ssljs , jslck = jslck + @jsljs , xslck = xslck + @xsljs,syslck = syslck + @sysljs , ssslck = ssslck + @sssljs , ysck=ysck + @ysjs ,dbck=dbck+@dbjs where spdm = @spdm fetch ckkc into @spdm , @ssljs , @jsljs ,@xsljs , @sysljs , @sssljs , @ysjs,@dbjs end close ckkc deallocate ckkc /*update t_ckspmx_js set ssslck = jslck - xslck + syslck*/ update t_ckspmx_js set sssljs = sslck + jsljs - xsljs + sysljs delete from t_ckspmx_js where sssljs = ssslck+ysck+dbck; SET QUOTED_IDENTIFIER OFF ; SET ANSI_NULLS ON ;SET QUOTED_IDENTIFIER ON ; SET ANSI_NULLS ON ;
我参照oracle 语法改了很长时间编译的时候总是出错.
望各位大虾能帮根据oracle的语法修改一下.跪谢了:--)还有SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
各表示什么意思.?????
CREATE PROCEDURE kctz_ckjs ASdeclare @spdm char(8),
@sl integer,
@ssljs integer,
@jsljs integer,
@xsljs integer,
@sysljs integer,
@sssljs integer,
@ysjs integer,
@dbjs integer
truncate table t_ckspmx_js
insert t_ckspmx_js (spdm,jsljs)
select spdm,sum(sl) from t_ysdsp group by spdm
declare yscx insensitive cursor for
select spdm, sl=sum(sl) from t_ysdsp_cx group by spdm
open yscx
fetch yscx into @spdm,@sl
while (@@fetch_status=0)
begin
if not exists(select * from t_ckspmx_js where spdm=@spdm)
insert into t_ckspmx_js (spdm,jsljs) values (@spdm, @sl)
else
update t_ckspmx_js set jsljs=jsljs + @sl where spdm=@spdm
fetch yscx into @spdm, @sl
end
close yscx
deallocate yscxdeclare ps insensitive cursor for select spdm, sl=sum(ccsl) from t_psdsp group by spdm
open ps
fetch ps into @spdm,@sl
while (@@fetch_status=0)
begin
if not exists(select * from t_ckspmx_js where spdm=@spdm)
insert into t_ckspmx_js (spdm,xsljs) values (@spdm, @sl)
else update t_ckspmx_js set xsljs=xsljs + @sl where spdm=@spdm
fetch ps into @spdm, @sl
end
close ps
deallocate ps
declare pscx insensitive cursor for
select spdm, sl=sum(ccsl) from t_psdsp_cx group by spdm
open pscx
fetch pscx into @spdm,@sl
while (@@fetch_status=0)
begin
if not exists(select * from t_ckspmx_js where spdm=@spdm)
insert into t_ckspmx_js (spdm,xsljs) values (@spdm, @sl)
else
update t_ckspmx_js set xsljs=xsljs + @sl where spdm=@spdm
fetch pscx into @spdm, @sl
end
close pscx
deallocate pscx
declare pf insensitive cursor for select spdm, sl=sum(ccsl) from t_pfdsp group by spdm
open pf
fetch pf into @spdm,@sl
while (@@fetch_status=0)
begin
if not exists(select * from t_ckspmx_js where spdm=@spdm)
insert into t_ckspmx_js (spdm, xsljs) values (@spdm,@sl)
else
update t_ckspmx_js set xsljs=xsljs + @sl where spdm=@spdm
fetch pf into @spdm,@sl end
close pf
deallocate pf
declare pfcx insensitive cursor for
select spdm, sl=sum(ccsl) from t_pfdsp_cx group by spdm
open pfcx
fetch pfcx into @spdm,@sl
while (@@fetch_status=0)
begin
if not exists(select * from t_ckspmx_js where spdm=@spdm)
insert into t_ckspmx_js (spdm, xsljs) values (@spdm,@sl)
else
update t_ckspmx_js set xsljs=xsljs + @sl where spdm=@spdm
fetch pfcx into @spdm,@sl
end
close pfcx
deallocate pfcx
declare pt insensitive cursor for
select spdm, sl=sum(sl) from t_ptdsp group by spdm
open pt
fetch pt into @spdm,@sl
while (@@fetch_status=0)
begin
if not exists(select * from t_ckspmx_js where spdm=@spdm)
insert into t_ckspmx_js (spdm, xsljs) values (@spdm, @sl)
else
update t_ckspmx_js set xsljs=xsljs + @sl where spdm=@spdm
fetch pt into @spdm ,@sl
end
close pt
deallocate pt
declare ptcx insensitive cursor for
select spdm, sl=sum(sl) from t_ptdsp_cx group by spdm
open ptcx
fetch ptcx into @spdm,@sl
while (@@fetch_status=0)
begin
if not exists(select * from t_ckspmx_js where spdm=@spdm)
insert into t_ckspmx_js (spdm, xsljs) values (@spdm, @sl)
else
update t_ckspmx_js set xsljs=xsljs + @sl where spdm=@spdm
fetch ptcx into @spdm ,@sl
end
close ptcx
deallocate ptcxdeclare pft insensitive cursor for
select spdm, sl=sum(sl) from t_ptdsp_pf group by spdm
open pft fetch pft into @spdm,@sl
while (@@fetch_status=0)
begin
if not exists(select * from t_ckspmx_js where spdm=@spdm)
insert into t_ckspmx_js (spdm, xsljs) values (@spdm, @sl)
else
update t_ckspmx_js set xsljs=xsljs + @sl where spdm=@spdm
fetch pft into @spdm ,@sl
end
close pft
deallocate pftdeclare pftcx insensitive cursor for
select spdm, sl=sum(sl) from t_ptdsp_pf_cx group by spdm
open pftcx
fetch pftcx into @spdm,@sl
while (@@fetch_status=0)
begin
if not exists(select * from t_ckspmx_js where spdm=@spdm)
insert into t_ckspmx_js (spdm, xsljs) values (@spdm, @sl)
else update t_ckspmx_js set xsljs=xsljs + @sl where spdm=@spdm
fetch pftcx into @spdm ,@sl
end
close pftcx
deallocate pftcx
declare sy insensitive cursor for
select spdm, sl=sum(sl) from t_sydsp group by spdm
open sy
fetch sy into @spdm,@sl
while (@@fetch_status=0)
begin
if not exists(select * from t_ckspmx_js where spdm=@spdm)
insert into t_ckspmx_js (spdm, sysljs) values (@spdm, @sl)
else
update t_ckspmx_js set sysljs=sysljs + @sl where spdm=@spdm
fetch sy into @spdm ,@sl
end
close sy
deallocate sy
declare sycx insensitive cursor for
select spdm, sl=sum(sl) from t_sydsp_cx group by spdm
open sycx
fetch sycx into @spdm,@sl
while (@@fetch_status=0)
begin
if not exists(select * from t_ckspmx_js where spdm=@spdm)
insert into t_ckspmx_js (spdm, sysljs) values (@spdm, @sl)
else
update t_ckspmx_js set sysljs=sysljs + @sl where spdm=@spdm
fetch sycx into @spdm ,@sl
end
close sycx
deallocate sycxdeclare ckkc insensitive cursor for
select spdm, ssljs = sum(ssl) , jsljs = sum(jsl), xsljs = sum(xsl), sysljs = sum(sysl), sssljs = sum(sssl),ysjs=sum(ys),dbjs=sum(dbsl) from t_ckspmx group by spdm
open ckkc
fetch ckkc into @spdm , @ssljs , @jsljs , @xsljs , @sysljs , @sssljs , @ysjs,@dbjs
while (@@fetch_status=0)
begin
if not exists(select * from t_ckspmx_js where spdm = @spdm)
insert into t_ckspmx_js (spdm, sslck , jslck, xslck, syslck, ssslck,ysck,dbck) values (@spdm, @ssljs , @jsljs, @xsljs, @sysljs , @sssljs , @ysjs,@dbjs)
else
update t_ckspmx_js set sslck = sslck + @ssljs , jslck = jslck + @jsljs , xslck = xslck + @xsljs,syslck = syslck + @sysljs , ssslck = ssslck + @sssljs , ysck=ysck + @ysjs ,dbck=dbck+@dbjs where spdm = @spdm
fetch ckkc into @spdm , @ssljs , @jsljs ,@xsljs , @sysljs , @sssljs , @ysjs,@dbjs
end
close ckkc
deallocate ckkc
/*update t_ckspmx_js set ssslck = jslck - xslck + syslck*/
update t_ckspmx_js set sssljs = sslck + jsljs - xsljs + sysljs
delete from t_ckspmx_js where sssljs = ssslck+ysck+dbck;
SET QUOTED_IDENTIFIER OFF
;
SET ANSI_NULLS ON
;SET QUOTED_IDENTIFIER ON
;
SET ANSI_NULLS ON
;
而且在oralce里不用在参数前加@以示此是参数
找一本pl/sql的书看看
一般简单的存储过程不难的
也要注意哪些是sql server扩展的SQL
在ORACLE是不能用的