把sqlserver中存储过程改写到oracle中-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

把sqlserver中存储过程改写到oracle中

简介:
一同学叫我帮忙改存储过程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,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章
最新文章
相关文章