把sqlserver中存储过程改写到oracle中
发布时间：2020-12-12 15:38:55 所属栏目：MsSql教程 来源：网络整理
导读：一同学叫我帮忙改存储过程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 encryp
|
一同学叫我帮忙改存储过程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, ????@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, ????xf decimal(4, ??????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,'B','0000000',NULL,NULL)???????? ???? end ???? else ???? begin ???????? declare t1 cursor for ???????? select b.kcbm,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,@t_xf,@tkcmc ???????? if @@FETCH_STATUS=0 ???????? begin ???????????????? while @@FETCH_STATUS=0 ???????????????????????????????????????????? begin ???????????????????? INSERT INTO @ temp(kcbm,tkcmc)???? ???????????????????? values(@kcbm,@tkcbm,@tkcmc)???????? ???????????????????????????????? fetch next from t1 ???????????????????????????????????????????????? into @tkcbm,@tkcmc???? ???????????????????????????????????????????? end???? ???????? end???? ???????????????????????? else ???????? begin ???????????????????????????????? INSERT INTO @ temp(kcbm,tkcmc)???? ???????????????????????????? values(@kcbm,NULL)???????????????? ???????????????????????? end???????????? ???????? CLOSE t1 ???????????? DEALLOCATE t1???????? ???? end ???????? end ???????? else ???????? begin ???? INSERT INTO @ temp(kcbm,tkcmc)???? ???????????????? values(@kcbm,NULL) ???????? end???? ???????? FETCH NEXT FROM t_cursor???? ???? INTO @kcbm,@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, xf number(4, 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,( 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,tkcmc)???? ?????? values(vkcbm,vkcmc,NULL)???? ; ???? end; ???? else ???? begin ???????? declare t1 cursor for ???????? select b.kcbm,( 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,vt_xf,vtkcmc; ???????????????????????????????????????? EXIT WHEN t1%NOTFOUND;???????????????? ???????????????????????????????????????? begin ???????????????????? INSERT INTO newtemptable(kcbm,tkcmc)???? ???????????????????? values(vkcbm,vtkcbm,vtkcmc)???????????????? ???????????????????????????????????????????????????????????? v_num_ct:=v_num_ct+1; ???????????????????????????????????????????? end???? ???????????? end loop;???? ???????????????????????? if v_num_ct =1 then ???????? begin ???????????????????????????????? INSERT INTO newtemptable(kcbm,tkcmc)???? ???????????????????????????? values(vkcbm,NULL) ;???????????? ???????????????????????? end ; ???????????????????????????????????? end if;???????? ???????? end ;???????????? ???????? CLOSE t1; ???????????????? end;???????????? ???????????? end; ???????????????????????? end if; ???????? end; ???????? else ???????? begin ???? INSERT INTO newtemptable(kcbm,tkcmc)???? ???????????????? values(vkcbm,NULL); ???????? end??;???? ???? end if;???? ???????????????? END; ?? close t_cursor;???????? ???? end; end; ???? open pRecCur for select * from newtemptable; end getxftjbyxh; （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |