大家好,我工作中遇到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
ENDCLOSE Sy_Cursor --关闭游标
DEALLOCATE Sy_Cursor
select * from #SBLXBM --显示临时表中索引设备类型
GO
请求大家帮忙翻译一下吗,或者否告诉我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
ENDCLOSE Sy_Cursor --关闭游标
DEALLOCATE Sy_Cursor
select * from #SBLXBM --显示临时表中索引设备类型
GO
create table SBLXBM(SBLXBM number,SBLXMC varchar2(50));create table SY(SY varchar2(100));delare
sql varchar2(250);
SBBM number;
SBLXBM number;
SBLXMC varchar2(50);
BMC varchar2(100);
SYBDS varchar2(100);
MCBDS varchar2(100);
num number; CURSOR Sy_Cursor is --定义游标
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 is not null;
begin
OPEN Sy_Cursor --打开游标
FETCH Sy_Cursor
INTO SBBM,BMC,SYBDS,MCBDS,SBLXBM,SBLXMC; --开始读取记录,并赋值到变量
loop
Sql := 'insert into SY select '||SYBDS||' From '||BMC||' Where '||SYBDS||' is not null';
EXECUTE immediate Sql;
commit;
--把所有索引记录保存到临时表
Select 1 into num From SY where rownum < 2;
if num>0 then --如果临时表有记录,说明此设备有索引,可以显示
EXECUTE immediate 'Select 1 From SBLXBM where SBLXBM ='||SBLXBM into num;
if num < 1 then --如果临时表不存在此设备类型编号,则插入
insert into SBLXBM values (SBLXBM,SBLXMC);
commit;
End if;
End if;
delete from SY; --清空索引临时表
commit;
exit when Sy_Cursor%notfound;
end loop;
CLOSE Sy_Cursor; --关闭游标
end;
select * from SBLXBM; --显示临时表中索引设备类型
create table SBLXBM(SBLXBM number,SBLXMC varchar2(50));create table SY(SY varchar2(100));
select * from SBLXBM;
这样的语句的 , 返回结果集的时候要用指针游标;