一同學叫我幫忙改存儲過程sqlserver----->oralce資料中：
sqlserver中存儲過程：
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
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
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
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
else
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
SET QUOTED_IDENTIFIER OFF
對應的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;
--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 )
v_num_ct number;
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);
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
and b.zybzbm=vzybzbm
and b.nd=vnd and b.jb=vjb
where v.xh=vxh
order by b.kcdlbm,b.kclbbm;
--清空臨時表，一般是自動清空的
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;
if vkclbbm is NULL or vkcdlbm is NULL then
select count(*) into vtmpk from b_ggjh where kcbm=vkcbm;
if vtmpk>0 then
INSERT INTO newtemptable(kcbm,kcmc,cj,xf,kcdlbm,kclbbm,tkcbm,tkcmc)
values(vkcbm,vkcmc,vcj,vxf,'B','0000000',NULL,NULL) ;
end;
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
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
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;
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;
open pRecCur for select * from newtemptable;
end getxftjbyxh;
本文轉自 yuwenhu 51CTO部落格，原文連結：http://blog.51cto.com/yuwenhu/160496，如需轉載請自行聯系原作者
![Oracle的基本操作[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)