一同學叫我幫忙改存儲過程sqlserver----->oralce數據中： sqlserver中存儲過程： SET?QUOTED_IDENTIFIER?ON????
GO?
SET?ANSI_NULLS?ON????
GO?


ALTER????PROCEDURE?[dbo].[getxftjbyxh]?
@xh?varchar(13),@nd?varchar(4),@jb?varchar(1)?
--with encryption????
as?
begin?
??SET?NOCOUNT?ON?
??DECLARE?@kcbm?varchar(7),@kcmc?VARCHAR(40),@cj?decimal(4,1),@xf?decimal(4,1),?
????@kcdlbm?varchar(1),@kclbbm?varchar(7),@tkcbm?varchar(7),@tkcmc?VARCHAR(40)?
??declare?@tmpk?int,@zybzbm?varchar(10),@t_xf?decimal(4,1)?
??select?@zybzbm=zybzbm?from?t_xs?where?xh=@xh?
??declare?@TEMP?table????
???????? (????
???????????????? kcbm?varchar(7),?
???????????????? kcmc?VARCHAR(40),????
???????????????? cj?decimal(4,1),?
????xf?decimal(4,1),?
??????kcdlbm?varchar(1),?
????kclbbm?varchar(7),?
????tkcbm?varchar(7),?
???????????????? tkcmc?VARCHAR(40)?
???????? )????
????
??DECLARE?t_cursor?CURSOR?FOR????
????select?v.kcbm,v.cj,b.kclbbm,b.kcdlbm,xf=(select?xf?from?b_kc?where?b_kc.kcbm=v.kcbm),?
????kcmc=(select?kcmc?from?b_kc?where?b_kc.kcbm=v.kcbm)?
????from?v_xscj v?left?outer?join?b_bzjh b????
????on?v.kcbm=b.kcbm?and?b.zfx='1'????
????and?b.zybzbm=@zybzbm?
????and?b.nd=@nd?and?b.jb=@jb?
????where?v.xh=@xh?
????order?by?b.kcdlbm,b.kclbbm?
????
??OPEN?t_cursor?
??FETCH?NEXT?FROM?t_cursor????
??INTO?@kcbm,@cj,@kclbbm,@kcdlbm,@xf,@kcmc?
????
??WHILE?@@FETCH_STATUS = 0?
??BEGIN?
????????if?@kclbbm?is?NULL?or?@kcdlbm?is?NULL?
????????begin?
????select?@tmpk=count(*)?from?b_ggjh?where?kcbm=@kcbm?
????if?@tmpk>0????
????begin?
?????????INSERT?INTO?@temp(kcbm,kcmc,cj,xf,kcdlbm,kclbbm,tkcbm,tkcmc)????
??????values(@kcbm,@kcmc,@cj,@xf,'B','0000000',NULL,NULL)????????
????end?
????else?
????begin?
?????????declare?t1?cursor?for?
?????????select?b.kcbm,b.kclbbm,b.kcdlbm,xf=(select?xf?from?b_kc?where?b_kc.kcbm=b.kcbm),kcmc=(select?kcmc?from?b_kc?where?b_kc.kcbm=b.kcbm)?
??????from?b_bzjh b,t_tdkc t?
??????where?b.kcbm=t.kcbm?and?t.tdkcbm=@kcbm?and?t.zybzbm=@zybzbm?
??????and?t.kcbm?not?in?(select?kcbm?from?v_xscj?where?xh=@xh)?
??????and?b.zybzbm=@zybzbm?and?b.nd=@nd?and?b.jb=@jb?and?b.zfx='1'????????????
?????????open?t1?
?????????fetch?next?from?t1?
????????????into?@tkcbm,@kclbbm,@kcdlbm,@t_xf,@tkcmc?
?????????if?@@FETCH_STATUS=0?
?????????begin?
?????????????????while?@@FETCH_STATUS=0?
?????????????????????????????????????????????begin?
????????????????????INSERT?INTO?@temp(kcbm,kcmc,cj,xf,kcdlbm,kclbbm,tkcbm,tkcmc)????
????????????????????values(@kcbm,@kcmc,@cj,@t_xf,@kcdlbm,@kclbbm,@tkcbm,@tkcmc)????????
?????????????????????????????????fetch?next?from?t1?
????????????????????????????????????????????????into?@tkcbm,@kclbbm,@kcdlbm,@t_xf,@tkcmc????
?????????????????????????????????????????????end????
?????????end?????
????????????????????????else?
?????????begin?
????????????????????????????????INSERT?INTO?@temp(kcbm,kcmc,cj,xf,kcdlbm,kclbbm,tkcbm,tkcmc)????
?????????????????????????????values(@kcbm,@kcmc,@cj,@xf,NULL,NULL,NULL,NULL)????????????????
????????????????????????end????????????
?????????CLOSE?t1?
????????????DEALLOCATE?t1????????
????end?
????????end?
????????else?
????????begin?
????INSERT?INTO?@temp(kcbm,kcmc,cj,xf,kcdlbm,kclbbm,tkcbm,tkcmc)????
?????????????????values(@kcbm,@kcmc,@cj,@xf,@kcdlbm,@kclbbm,NULL,NULL)?
????????end?????
????????FETCH?NEXT?FROM?t_cursor????
????INTO?@kcbm,@cj,@kclbbm,@kcdlbm,@xf,@kcmc????
????????????????END?
??close?t_cursor?
??deallocate?t_cursor?
??select?*?from?@TEMP????
end?


GO?
SET?QUOTED_IDENTIFIER?OFF????
GO?
SET?ANSI_NULLS?ON????
GO?
對應的oracle的存儲過程： --（1）定義游標(返回)?

create?or?replace?package TestPackage?is?
type TestResultSet?is?ref?cursor;?
end?TestPackage ;?

????

--實現創建臨時表，往臨時表中添加記錄，返回記錄集?

--（2）先用一存儲過創建臨時表?
create?or?replace?procedure?p_createtemptalbe????
authid?current_user?is?????
v_num number;?

begin?
--create temporary table????????
select?count(*)?into?v_num?from?user_tables?where?table_name='newtemptable';????
if?v_num<1?then????????
execute?immediate 'CREATE?GLOBAL?TEMPORARY?TABLE?newtemptable (?????????
kcbm varchar2(7),?
kcmc varchar2(40),????
cj number(4,1),?
xf number(4,1),?
kcdlbm varchar2(1),?
kclbbm varchar2(7),?
tkcbm varchar2(7),?
tkcmc varchar2(40)?????
)?ON?COMMIT?PRESERVE ROWS';????????
?????
end?if;?
end?p_createtemptalbe ;?

--（3）執行存儲過程?
call p_createtemptalbe();?




--（4）往臨時表中添加記錄，返回記錄集????


create?or?replace?procedure?getxftjbyxh (vxh?in?varchar2,vnd?in?varchar2,vjb?in?varchar2, pRecCur OUT TestPackage.TestResultSet )?
authid?current_user?is?????
v_num number;?
v_num_ct number;?
begin?
declare?
vkcbm varchar2(7);?
vkcmc varchar2(40);?
vcj number(4,1);?
vxf number(4,1);?
vkcdlbm varchar2(1);?
vkclbbm varchar2(7);?
vtkcbm varchar2(7);?
vtkcmc varchar2(40);?
????
vtmpk number;?
vt_xvzybzbm?varchar(10);?
vt_xf number(4,1);?



begin?
????
select?zybzbm?into?vzybzbm?from?t_xs?where?xh=vxh;?
v_num_ct :=1;?
declare????
--CURSOR cur_bookisbn???? IS select b.bookid,b.book_isbn from bookinfo b where b.publishid = '1000000158';?
Cursor?t_cursor?CURSOR?FOR????
????select?v.kcbm,v.cj,b.kclbbm,b.kcdlbm,(select?xf?from?b_kc?where????

b_kc.kcbm=v.kcbm) xf,?
????(select?kcmc?from?b_kc?where?b_kc.kcbm=v.kcbm) kcmc?
????from?v_xscj v?left?outer?join?b_bzjh b????
????on?v.kcbm=b.kcbm?and?b.zfx='1'????
????and?b.zybzbm=vzybzbm?
????and?b.nd=vnd?and?b.jb=vjb?
????where?v.xh=vxh?
????order?by?b.kcdlbm,b.kclbbm;?
begin?
--清空臨時表，一般是自動清空的?

select?count(*)?into?v_num?from?newtemptable;?
if?v_num>0?then?
execute?immediate?'truncate table newtemptable';?
end?if?;?
????

OPEN?t_cursor;?
??FETCH????t_cursor?INTO?vkcbm,vcj,vkclbbm,vkcdlbm,vxf,vkcmc;?
??EXIT?WHEN?t_cursor %NOTFOUND;????
??BEGIN?
????????if?vkclbbm?is?NULL?or?vkcdlbm?is?NULL?then?
????????begin?
????select?count(*)?into?vtmpk?from?b_ggjh?where?kcbm=vkcbm;?
????if?vtmpk>0????then?
????begin?
?????????INSERT?INTO?newtemptable(kcbm,kcmc,cj,xf,kcdlbm,kclbbm,tkcbm,tkcmc)????
??????values(vkcbm,vkcmc,vcj,vxf,'B','0000000',NULL,NULL)???? ;?
????end;?
????else?
????begin?
?????????declare?t1?cursor?for?
?????????select?b.kcbm,b.kclbbm,b.kcdlbm,(select?xf?from?b_kc?where?b_kc.kcbm=b.kcbm) xf, (select?kcmc?from?b_kc?where?b_kc.kcbm=b.kcbm) kcmc?
??????from?b_bzjh b,t_tdkc t?
??????where?b.kcbm=t.kcbm?and?t.tdkcbm=vkcbm?and?t.zybzbm=vzybzbm?
??????and?t.kcbm?not?in?(select?kcbm?from?v_xscj?where?xh=vxh)?
??????and?b.zybzbm=vzybzbm?and?b.nd=vnd?and?b.jb=vjb?and?b.zfx='1';?????????????????????????????????????????
????????????begin?
????????????open?t1;?
????????????????????????????????????????????loop?
?????????????????????????????????????????fetch?t1?into?vtkcbm,vkclbbm,vkcdlbm,vt_xf,vtkcmc;?
?????????????????????????????????????????EXIT?WHEN?t1%NOTFOUND;?????????????????
?????????????????????????????????????????begin?
????????????????????INSERT?INTO?newtemptable(kcbm,kcmc,cj,xf,kcdlbm,kclbbm,tkcbm,tkcmc)????
????????????????????values(vkcbm,vkcmc,vcj,vt_xf,vkcdlbm,vkclbbm,vtkcbm,vtkcmc)????????????????
???????????????????????????????????????????????????????????? v_num_ct:=v_num_ct+1;?
????????????????????????????????????????????end????
?????????????end?loop;????
????????????????????????if?v_num_ct =1?then?
?????????begin?
????????????????????????????????INSERT?INTO?newtemptable(kcbm,kcmc,cj,xf,kcdlbm,kclbbm,tkcbm,tkcmc)????
?????????????????????????????values(vkcbm,vkcmc,vcj,vxf,NULL,NULL,NULL,NULL) ;?????????????
????????????????????????end?;?
?????????????????????????????????????end?if;?????????
?????????end?;?????????????
?????????CLOSE?t1;?
?????????????????end;?????????????
?????????????end;?
?????????????????????????end?if;?
????????end;?
????????else?
????????begin?
????INSERT?INTO?newtemptable(kcbm,kcmc,cj,xf,kcdlbm,kclbbm,tkcbm,tkcmc)????
?????????????????values(vkcbm,vkcmc,vcj,vxf,vkcdlbm,vkclbbm,NULL,NULL);?
????????end??;????
?????end?if;????
????????????????END;?
??close?t_cursor;?????????
????end;?
end;?
?????

open?pRecCur?for?select?*?from?newtemptable;?
end?getxftjbyxh;








本文轉自 yuwenhu 51CTO博客，原文鏈接：http://blog.51cto.com/yuwenhu/160496，如需轉載請自行聯系原作者

總結

以上是生活随笔為你收集整理的把sqlserver中存储过程改写到oracle中的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。