用游标查一条记录,然后以游标中一条记录的值为条件,在查询。怎么做。begin开始了好象不能再声明游标了,而且存储过程里只能设置游标记录?
create or replace procedure CREATE_PDH_TEST_DATA (EndID integer)is
adevice device%rowtype;
zdevice device%rowtype;
trnasSystemId integer := 1000000;pdhConnectionPortId integer := 1;
transSystemDeviceA integer;
transSystemDeviceZ integer;StartID integer := 1;
CURSOR C_Device IS select * from device where type =10 and sub_type =1001;
begin
while StartID <= EndID
Loop
open C_Device;
fetch C_Device into adevice;
EXIT WHEN C_Device%NOTFOUND;
fetch C_Device into zdevice;
EXIT WHEN C_Device%NOTFOUND;
insert into RMS.Trans_System(ID, NAME, HIERARCHY, TOPO_STRUCTURE, RATE, RANK, A_DEVICE_ID, Z_DEVICE_ID) Values (trnasSystemId, '测试'||trnasSystemId, 1, 1, 1, 1, adevice.id, zdevice.id);
commit;
trnasSystemId := trnasSystemId + 1;
--这里用游标中的记录为条件查询,不过好象begin开始了就不能再
--用游标了。而且不知道如何遍历游标啊~~
CURSOR A_PORT_BY_140 IS select * from port p where p.DEVICE_ID = adevice.id and p.REF_TYPE_ID = 4 ;
END Loop
close C_Device;
end CREATE_PDH_TEST_DATA;
create or replace procedure CREATE_PDH_TEST_DATA (EndID integer)is
adevice device%rowtype;
zdevice device%rowtype;
trnasSystemId integer := 1000000;pdhConnectionPortId integer := 1;
transSystemDeviceA integer;
transSystemDeviceZ integer;StartID integer := 1;
CURSOR C_Device IS select * from device where type =10 and sub_type =1001;
begin
while StartID <= EndID
Loop
open C_Device;
fetch C_Device into adevice;
EXIT WHEN C_Device%NOTFOUND;
fetch C_Device into zdevice;
EXIT WHEN C_Device%NOTFOUND;
insert into RMS.Trans_System(ID, NAME, HIERARCHY, TOPO_STRUCTURE, RATE, RANK, A_DEVICE_ID, Z_DEVICE_ID) Values (trnasSystemId, '测试'||trnasSystemId, 1, 1, 1, 1, adevice.id, zdevice.id);
commit;
trnasSystemId := trnasSystemId + 1;
--这里用游标中的记录为条件查询,不过好象begin开始了就不能再
--用游标了。而且不知道如何遍历游标啊~~
CURSOR A_PORT_BY_140 IS select * from port p where p.DEVICE_ID = adevice.id and p.REF_TYPE_ID = 4 ;
END Loop
close C_Device;
end CREATE_PDH_TEST_DATA;
就想问 用游标查一条记录,然后以游标中一条记录的值为条件,再查询。如何保存这个结果集啊。
谁帮我把上面的改改
我先弄一个游标。
open C_Device;
fetch C_Device into adevice;
EXIT WHEN C_Device%NOTFOUND;fetch C_Device into zdevice;
EXIT WHEN C_Device%NOTFOUND;
然后通过游标上的一条记录。查询再弄一个游标CURSOR A_PORT_BY_140 IS select * from port p where p.DEVICE_ID = adevice.id and p.REF_TYPE_ID = 4 ;
在begin以后就没法建了。而且这个不知道怎么循环啊。
iCount INTEGER;
iValue INTEGER;
然后再其后继续定义你的游标:
Cursor curTest is
select * from tablename t where t.Count = iCount and t.Value = iValue;
再在你的for循环内部使用这个游标就好了。
这种情况下你要注意你的变量先获取了内容然后再使用对应的游标就没什么问题。
----------------------------
p.DEVICE_ID = adevice.id 你把adevice当数据集了,建两个临时表吧,adevice和zdevice,
就可以这样用了
2 DEVICEID VARCHAR2(30),
3 LOCATIONLINK NUMBER(10)
4 ) on commit preserve rows;Table createdSQL>
SQL> Create global temporary TABLE zdevice(
2 DEVICEID VARCHAR2(30),
3 LOCATIONLINK NUMBER(10)
4 ) on commit preserve rows;Table created
SQL> create or replace procedure CREATE_PDH_TEST_DATA (
2 EndID integer
3 )
4 is
5 trnasSystemId integer := 1000000;
6 SSQL VARCHAR2(2000);
7 begin
8 FOR REC IN(select * from device)
9 LOOP
10 IF (REC.LOCATIONLINK > 15) THEN
11 SSQL := 'INSERT INTO ADEVICE SEECT '||''''||REC.DEVICEID||''''||','||''''||REC.LOCATIONLINK||''''||' FROM DUAL';
12 EXECUTE IMMEDIATE SSQL;
13 ELSE
14 SSQL := 'INSERT INTO ADEVICE SEECT '||''''||REC.DEVICEID||''''||','||''''||REC.LOCATIONLINK||''''||' FROM DUAL';
15 EXECUTE IMMEDIATE SSQL;
16 END IF;
17 commit;
18
19 FOR REC_1 IN(select * from DEVICE p INNER JOIN ADEVICE ON p.DEVICEID = adevice.DEVICEID)
20 LOOP
21 DBMS_OUTPUT.put_line('SUCCESS!');
22 END LOOP;
23 END LOOP;
24 end CREATE_PDH_TEST_DATA;
25 /Procedure created
create or replace procedure CREATE_PDH_TEST_DATA (EndID integer)is
adevice device%rowtype;
zdevice device%rowtype;
iRelationID NUMBER; --用来记录关系表的ID
trnasSystemId integer := 1000000;pdhConnectionPortId integer := 1;
transSystemDeviceA integer;
transSystemDeviceZ integer;StartID integer := 1;
CURSOR C_Device IS select * from device where type =10 and sub_type =1001;--根据变量iRelationID存储的内容来查,在你的LOOP循环内部使用。
CURSOR c_loop IS SELECT * FROM tblRelation R WHERE R.ID = iRelationID; begin
while StartID <= EndID
Loop
open C_Device;
fetch C_Device into adevice;
EXIT WHEN C_Device%NOTFOUND;
fetch C_Device into zdevice;
EXIT WHEN C_Device%NOTFOUND;
insert into RMS.Trans_System(ID, NAME, HIERARCHY, TOPO_STRUCTURE, RATE, RANK, A_DEVICE_ID, Z_DEVICE_ID) Values (trnasSystemId, '测试'||trnasSystemId, 1, 1, 1, 1, adevice.id, zdevice.id);
commit;
trnasSystemId := trnasSystemId + 1;
---------------------------------------------------
--用定义的游标:
For content in c_loop
end loop;
--------------------------------------------------- --这里用游标中的记录为条件查询,不过好象begin开始了就不能再
--用游标了。而且不知道如何遍历游标啊~~
CURSOR A_PORT_BY_140 IS select * from port p where p.DEVICE_ID = adevice.id and p.REF_TYPE_ID = 4 ;
END Loop
close C_Device;
end CREATE_PDH_TEST_DATA;
打開遊標取數據就ok了