2 u012430520 u012430520 于 2015.07.28 15:22 提问

sqlserver存储过程转微oracle的存储过程求教,在线等

USE [chss_back]
GO
/****** 对象: StoredProcedure [dbo].[sp_Public_PyWbmGet] 脚本日期: 07/28/2015 10:54:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Public_PyWbmGet]
@tablename varchar(32),
@fieldname1 varchar(32),
@fieldname2 varchar(32),
@pybz char(1) = '0'
as
set nocount on

exec('declare @name varchar(255),@shortcut varchar(255)'+
' declare cs_py cursor for select '+@fieldname1+','+@fieldname2+
' from '+@tablename+
' where '+@fieldname2+ ' is null'+
' for update of '+@fieldname2+
' open cs_py'+
' fetch cs_py into @name,@shortcut'+
' while @@fetch_status=0'+
' begin exec sp_Public_PyWbm @name,'+@pybz+',@shortcut output'+
' update '+@tablename+' set '+@fieldname2+'=convert(varchar(40),@shortcut)'+
' where current of cs_py'+
' fetch cs_py into @name,@shortcut end'+
' close cs_py deallocate cs_py'
)
GO

4个回答

lzp_lrp
lzp_lrp   Ds   Rxr 2015.07.28 15:34
已采纳

上面太乱了

 1. declare
  2. type empdtlrec is record (empno number(4),
  3. ename varchar2(20),
  4. deptno number(2));
  5. empdtl empdtlrec;
  6. begin
  7. execute immediate ‘select empno, ename, deptno ’ ||
  8. ‘from emp where empno = 7934’
  9. into empdtl;
  10. end;

可参考

http://blog.csdn.net/xiooix2012/article/details/6895594

u012430520
u012430520 回复PB人生: 我采纳你了。你帮我转一下好吗。。。我用工具转的有错
2 年多之前 回复
u012430520
u012430520 回复PB人生: 是啊,以前没学过。突然要我改这个。。
2 年多之前 回复
lzp_lrp
lzp_lrp 回复fangjialea: 定义变量也不一样,你肯定需要学习一下oracle存储过程的写法
2 年多之前 回复
u012430520
u012430520 exec 之前的都不用变动吗
2 年多之前 回复
lzp_lrp
lzp_lrp   Ds   Rxr 2015.07.28 15:33

exec在oracle可以用execute来代替,例如

  1. declare   2. l_routin varchar2(100) := ‘gen2161.get_rowcnt’;   3. l_tblnam varchar2(20) := ‘emp’;   4. l_cnt number;   5. l_status varchar2(200);   6. begin   7. execute immediate ‘begin ’ || l_routin || ‘(:2, :3, :4); end;’   8. using in l_tblnam, out l_cnt, in out l_status;   9. if l_status != ‘OK’ then   10. dbms_output.put_line(‘error’);   11. end if;   12. end;
CSDNXIAON
CSDNXIAON   2015.07.28 17:01

Oracle存储过程(转)
----------------------同志你好,我是CSDN问答机器人小N,奉组织之命为你提供参考答案,编程尚未成功,同志仍需努力!

sina_2831808769
sina_2831808769   Rxr 2015.07.28 20:25
  1. declare   2. type empdtlrec is record (empno number(4),   3. ename varchar2(20),   4. deptno number(2));   5. empdtl empdtlrec;   6. begin   7. execute immediate ‘select empno, ename, deptno ’ ||   8. ‘from emp where empno = 7934’   9. into empdtl;   10. end;
Csdn user default icon
上传中...
上传图片
插入图片