游标处理的行数

Question

declare
cid number;
cadd number;
ctras number;
pr varchar(2);
vad number;
cursor c1 IS
select ac_tras, cust_id, cust_addr from customer_master;
cursor c2 IS
select pr_adr from customer_address where cust_id = cid and cust_addr = cadd;
BEGIN
open c1;
LOOP
fetch c1 into ctras, cid, cadd;
EXIT WHEN C1%NOTFOUND;
OPEN c2;
LOOP
fetch c2 into pr;
if pr='Y'
THEN EXIT ;
ELSE
UPDATE customer_master 
set cust_addr = (select cust_addr from customer_address where pr_adr = 'Y' and cust_id = cid) where ac_tras = ctras;
END IF;
EXIT WHEN C2%NOTFOUND;
END LOOP;
Close C2;
END LOOP;
CLOSE C1;
END;

你好。在上面的代码中，我需要找到获取的行数。如何找到它。换句话说，我必须找到为游标 C1 处理了多少行或循环迭代了多少次

Answer 1

您可以使用

SQL%ROWCOUNT来回显更新的行数。
示例（将您的代码释义到相关部分，还添加COMMIT以确保理智）：

...
UPDATE customer_master
SET cust_addr =
  (SELECT cust_addr FROM customer_address WHERE pr_adr = 'Y' AND cust_id = cid
  )
WHERE ac_tras = ctras;
DBMS_OUTPUT.PUT_LINE('Updated ' || SQL%ROWCOUNT || ' records.');
...

源