Oracle游标循环同一表上的另一个游标

Question

尝试在同一个表上查询并使用游标进行处理。houseno是唯一的，在addressRecords表中只能作为一条记录存在。编译程序时，USERS表空间会快速增长并迅速填满磁盘空间。有更好的写作方式吗？

DECLARE
plc VARCHAR2(200); 
cly VARCHAR2(200);
roadno VARCHAR2(200); 
houseno VARCHAR2(200);
owner VARCHAR2(200);
tenant VARCHAR2(200);,
name VARCHAR2(200);
address_query VARCHAR2(4000);
houseno_query VARCHAR2(4000);
addressRecords NUMBER := 0;
address_cursor sys_refcursor;
houseno_cursor sys_refcursor;
hno_counter NUMBER := 0;

BEGIN

houseno_query :='SELECT DISTINCT PLOTNO FROM CITY ORDER BY PLOTNO';
select count(*) INTO addressRecords from CITY;

OPEN houseno_cursor FOR houseno_query;
IF (addressRecords = 0) THEN
RAISE_APPLICATION_ERROR(-20505, 'no entries found');
END IF;

LOOP

FETCH houseno_cursor INTO houseno;

EXIT WHEN houseno_cursor%notfound;  


address_query :='SELECT PLOTNO,STREETNO,COLONY,AREA,OWNEDBY,RENTEDBY,IDENTITY FROM CITY WHERE PLOTNO=houseno';
OPEN address_cursor FOR address_query;

LOOP

FETCH address_cursor INTO houseno, roadno, cly, plc, owner, tenant, name;

-- print houseno
DBMS_OUTPUT.PUT_LINE('Processing house number : ' || houseno);
-- Insert unique house numbers
IF (hno_counter = 0) THEN
INSERT INTO HOUSEDETAILS
VALUES (houseno, roadno, cly, plc);     
END IF;

INSERT INTO PERSONDETAILS
VALUES (houseno, roadno, cly, plc, owner, tenant, name);

hno_counter := hno_counter + 1;

END LOOP;

CLOSE address_cursor;   

END LOOP;   

CLOSE houseno_cursor;

END;

Answer 1

查看您的代码，您可以更容易地将其写下来

我注意到你在同一张(大？(桌子上翻了几次。我写了一小部分代码，可能会让你知道如何也能做到这一点。我把SELECT语句移到了一个游标上，打开这个游标，然后循环这些语句。如果没有更多的记录，光标循环将自动结束

DECLARE
CURSOR cCity IS
SELECT * FROM City ORDER BY PlotNo;
BEGIN
FOR rCity IN cCity LOOP
--
INSERT INTO HouseDetails(ColumnA, ColumnB)
VALUES(rCity.HouseNo, rCity.RoadNo);

--Here you can also do an insert into PersonDetails table.

END LOOP;

COMMIT;

END;
/