希望对楼主有帮助!
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));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; --显示临时表中索引设备类型
Oracle的游标比SQL Server的更科学、易懂, 参见
http://www.oracle.com.cn/viewthread.php?tid=56095&highlight=%D3%CE%B1%EA