理论上来说,%NOTFOUND是一个游标属性,并且如果最后一个FETCH语句没有提取到数据则为TRUE,否则为false。而NO_DATA_FOUND是一个预定义的异常,在SELECT ?INTO ?没有提取到纪录时将抛出一个异常。
两者的区别是,如果FETCH语句没有提取到数据,NO_DATA_FOUND不会抛出异常,换句话说,当使用游标时,NO_DATA_FOUND异常是不会被抛出的,%NOTFOUND会被设置为true。而在SELECT ?INTO ?语句中,NO_DATA_FOUND异常会被抛出,并且%NOTFOUND也会被设置为true。
如下:
SQL> DECLARE l_ename VARCHAR2(100);
BEGIN
SELECT ename INTO l_ename FROM emp
WHERE empno = ‘515’;
DBMS_OUTPUT.PUT_LINE(l_ename);
EXCEPTION
WHEN NO_DATA_FOUND THEN
IF ( SQL%NOTFOUND ) THEN
DBMS_OUTPUT.PUT_LINE(‘NOTFOUND’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘FOUND’);
END IF;
END;
/
NOTFOUND
SQL> DECLARE CURSOR c1 IS SELECT ename FROM emp WHERE empno = 1515;
l_ename VARCHAR2(100);
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO l_ename;
IF ( c1%NOTFOUND ) THEN
DBMS_OUTPUT.PUT_LINE(‘NOTFOUND’);
ELSE
DBMS_OUTPUT.PUT_LINE(l_ename);
END IF;
EXIT WHEN c1%NOTFOUND;
END LOOP;
CLOSE c1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘NO_DATA_FOUND’);
END;
/
NOTFOUND
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22396916/viewspace-711984/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22396916/viewspace-711984/