执行一个PL/SQL 块,总是报这个错:ORA-01403: 未找到数据问题,在网上搜索了半天,找到一个解决办法。
比较彻底的办法就是添加一个异常处理~
另外,在其中还应用了存储循环跳转应用,很好地复习了存储过程开发!解决问题方法如下:
DECLARE
vn_sum_old NUMBER(10);
vn_sum_new NUMBER(10);
vn_count NUMBER(10) := 0;
cursor get_tcf is
SELECT account_id AS acct_id,user_idnew AS user_id,perserve01 AS acct_item_type_id from table_a
where city_code='921'
and fee_date='201107'
and table_source = 0
and perserve03 is null
and disct_rule_id=211100278;
BEGIN
for cur in get_tcf LOOP
SELECT SUM(charge) INTO vn_sum_old
FROM table_d
WHERE acct_item_type_id = cur.acct_item_type_id
AND acct_id = cur.acct_id
GROUP BY acct_id;
BEGIN --这是针对性异常处理的应用
SELECT NVL(SUM(fee),0) INTO vn_sum_new
FROM ucr_dtb1.table_c
WHERE
detail_item_code IN(
SELECT new_item_code FROM table_b@to_accttst
WHERE acct_item_type_id IN cur.acct_item_type_id
)
AND acct_id = cur.acct_id
GROUP BY acct_id ;
EXCEPTION
WHEN no_data_found THEN
--NULL;
GOTO point1; --这是循环跳转应用
END;
IF vn_sum_old = vn_sum_new THEN
UPDATE table_a SET perserve03 = '............'
WHERE
perserve01 = cur.acct_item_type_id
AND user_idnew = cur.user_id;
END IF;
IF vn_count = 100 THEN
COMMIT;
vn_count := 0;
END IF;
vn_count := vn_count+1;
<<point1>> --这是断点设置
NULL;
end loop;
DBMS_OUTPUT.PUT_LINE(vn_count);
END ;