新博客http://www.cnblogs.com/zhjh256 欢迎访问
分类: Oracle
2008-01-06 21:10:44
理论上来说,%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;
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
CLOSE c1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘NO_DATA_FOUND’);
END;
/
NOTFOUND