Ms SqlServer存储过程转ORACLE格式,请求帮助!
wauo 2005-06-20 04:05:41 大家好,我工作中遇到Ms SqlServer存储过程转ORACLE格式,但是我对ORACLE一点都不懂。
请求大家帮忙翻译一下吗,或者否告诉我ORACLE的游标,临时表的怎么写,分不够另加。万分感谢!!!以下是Ms SqlServer存储过程。
CREATE PROCEDURE QX_Seach_SbLXBM AS
/*
===============================================
功能:显示缺陷设备类型数据
参数:
===============================================
*/
DECLARE @sql varchar(250)
DECLARE @SBBM int
DECLARE @SBLXBM int
DECLARE @SBLXMC varchar(50)
DECLARE @BMC varchar(100)
DECLARE @SYBDS varchar(100)
DECLARE @MCBDS varchar(100)
create table #SBLXBM(SBLXBM int,SBLXMC varchar(50))
create table #SY(SY varchar(100))
DECLARE Sy_Cursor CURSOR FOR --定义游标
SELECT distinct sb.SBBM,sb.BMC,sb.SYBDS,sb.MCBDS,sblx.SBLXBM,sblx.SBLXMC
FROM GIS_TM_SB sb,GIS_TM_SBLX sblx,LC_QX_QXNR qx
WHERE sb.SBLXBM = sblx.SBLXBM
And qx.SBBM = sb.SBBM
and sb.SYBDS <> ''
OPEN Sy_Cursor --打开游标
FETCH NEXT FROM Sy_Cursor
INTO @SBBM,@BMC,@SYBDS,@MCBDS,@SBLXBM,@SBLXMC --开始读取记录,并赋值到变量
WHILE @@FETCH_STATUS = 0
BEGIN
Set @Sql = 'insert into #SY select ' + @SYBDS +' From ' + @BMC + ' Where ' + @SYBDS + ' is not null'
EXECUTE(@Sql)
--把所有索引记录保存到临时表
if EXISTS(Select * From #SY) --如果临时表有记录,说明此设备有索引,可以显示
Begin
if Not EXISTS(Select * From #SBLXBM where SBLXBM = @SBLXBM) --如果临时表不存在此设备类型编号,则插入
Begin
insert into #SBLXBM values (@SBLXBM,@SBLXMC)
End
End
delete from #SY --清空索引临时表
FETCH NEXT FROM Sy_Cursor
INTO @SBBM,@BMC,@SYBDS,@MCBDS,@SBLXBM,@SBLXMC
END
CLOSE Sy_Cursor --关闭游标
DEALLOCATE Sy_Cursor
select * from #SBLXBM --显示临时表中索引设备类型
GO