SQL> DECLARE
2 l_pv NUMBER;
3 BEGIN
4 --确定绑定的OFFER在统计日的PV
5 SELECT (pv)
6 INTO l_pv
7 FROM cnmir.offerid_pv
8 WHERE offer_id=45755675675
9 AND report_time=20071108;
10
11 --防止日志数据异常,没有OFFER对应的PV值,此为数据异常
12 EXCEPTION
13 WHEN NO_DATA_FOUND THEN
14 l_pv:=-110;
15 DBMS_OUTPUT.put_line(l_pv);
16 END;
17 /
-110
PL/SQL procedure successfully completed.
SQL> DECLARE
2 l_pv NUMBER;
3 BEGIN
4 --确定绑定的OFFER在统计日的PV
5 SELECT SUM(pv)
6 INTO l_pv
7 FROM cnmir.offerid_pv
8 WHERE offer_id=45755675675
9 AND report_time=20071108;
10
11 --防止日志数据异常,没有OFFER对应的PV值,此为数据异常
12 EXCEPTION
13 WHEN NO_DATA_FOUND THEN
14 l_pv:=-110;
15 DBMS_OUTPUT.put_line(l_pv);
16 END;
17 /
PL/SQL procedure successfully completed.
请注意,前边一个有-110,后边的没有!对于聚集函数操作后,即使没有相应记录也不会报NO_DATA_FOUND错误,而是返回空值
[@more@]