oracle pl sql %未找到
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13075538/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
pl sql %NOTFOUND
提问by user1050619
I'm just wondering why this piece of code is not working. I don't have any supplier id=1 in my table.
我只是想知道为什么这段代码不起作用。我的表中没有任何供应商 id=1。
DECLARE
VAR SUPP_NM VARCHAR(100);
VAR_SUPP_ID NUMBER := 1;
WHILE_VAR CHAR := 'Y';
BEGIN
SELECT SUPP_NM
INTO VAR_SUPP_NM
FROM TEST.SUPPLIER
WHERE SUPP_ID = VAR_SUPP_ID;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('SQL DATA NOT FOUND');
ELSIF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('DATA FOUND');
END IF;
END;
回答by Nick Krasnov
To catch the NO_DATA_FOUNDexception rewrite your code as follows by adding exceptionsection:
要捕获NO_DATA_FOUND异常，请按如下方式添加exception部分重写您的代码：
DECLARE
VAR_SUPP_NM VARCHAR2(100);
VAR_SUPP_ID NUMBER := 1;
WHILE_VAR CHAR := 'Y';
BEGIN
SELECT SUPP_NM
INTO VAR_SUPP_NM
FROM TEST.SUPPLIER
WHERE SUPP_ID = VAR_SUPP_ID;
DBMS_OUTPUT.PUT_LINE('DATA FOUND');
exception
when no_data_found
then DBMS_OUTPUT.PUT_LINE('SQL DATA NOT FOUND');
END;
Checking SQL%FOUNDor SQL%NOTFOUNDhave no meaning in the case of select intostatement, because if the select statement returns no rows it will always raise no_data_foundexception, except, if that select statement invokes aggregate function, it will always return data or null if no rows has been selected.
在语句的情况下检查SQL%FOUNDorSQL%NOTFOUND没有意义select into，因为如果 select 语句没有返回任何行，它总是会引发no_data_found异常，除非该 select 语句调用聚合函数，如果没有选择任何行，它将始终返回数据或 null。
Do not use varchardatatype, use varchar2datatype instead.
不要使用varchar数据类型，varchar2而是使用数据类型。
回答by Aurifier
Nicholas's answer is what you want if you want to use SELECT INTO. However, if it is more important that you are able to use %FOUNDor %NOTFOUND, consider FETCHing from a cursor instead:
如果您想使用SELECT INTO. 但是，如果您能够使用%FOUNDor更重要，请%NOTFOUND考虑FETCH从游标中使用ing ：
DECLARE
VAR SUPP_NM VARCHAR2(100);
VAR_SUPP_ID NUMBER := 1;
WHILE_VAR CHAR := 'Y';
CURSOR c1 IS
SELECT SUPP_NM
FROM TEST.SUPPLIER
WHERE SUPP_ID = VAR_SUPP_ID;
BEGIN
OPEN c1;
FETCH c1 INTO VAR_SUPP_NM;
IF c1%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('SQL DATA NOT FOUND');
ELSIF c1%FOUND THEN
DBMS_OUTPUT.PUT_LINE('DATA FOUND');
END IF;
CLOSE c1;
END;
