把sqlserver中存储过程改写到oracle中
--（1）定义游标(返回) create or replace package TestPackage istype 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_num0 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;
页:
[1]