iv_tag:=0;select tag
into iv_tag
from table
where ...当搜索的结果为空时,会报no_data_found异常,如何实现从表中找到数据,当没有结果就返回一个0给iv_tag而不是报异常;
into iv_tag
from table
where ...当搜索的结果为空时,会报no_data_found异常,如何实现从表中找到数据,当没有结果就返回一个0给iv_tag而不是报异常;
into iv_tag
from table
where ...
CREATE TABLE TABLE1(ID INT,condition VARCHAR2(10));
INSERT INTO TABLE1 VALUES(1,'A');
INSERT INTO TABLE1 VALUES(2,'B');
INSERT INTO TABLE1(condition) VALUES('C');
COMMIT;create or replace procedure MYPROCDURE(condition_para varchar2) as
result_id INT;
begin
select NVL(id,0) as id into result_id from TABLE1 where condition = condition_para;
dbms_output.put_line(result_id);
end;
/
SQL> set serveroutput on
SQL> EXEC MYPROCDURE('A');
1PL/SQL 过程已成功完成。SQL> EXEC MYPROCDURE('C');
0PL/SQL 过程已成功完成。
iv_tag:=0;select tag
into iv_tag
from table
where ...exception
when no_data_found then
iv_tag:=0;
不是因为你没取到值,而是因为你没有找到值
如果在存储过程中的select语句没有找到值就会出现你那个错误
你可以增加一个容错处理
begin
select ..
exception
when no_data_found then
--处理
end ;
into iv_tag
from table
where ...
aaa number:=0;begin
select 1 into aaa from dual where 1=2;
exception
when no_data_found then
aaa:=-1;
when others then
aaa:=2;
end ;
试试 EXEC MYPROCDURE('X');
lcounts:number;select count(1) into lcounts
from table
where ...if lcounts<>1 then
--跳出去
else
end if select tag
into iv_tag
from table
where ...
lcounts:number;select count(1) into lcounts
from table
where ...if lcounts<>1 then
--跳出去
else
select tag
into iv_tag
from table
where ...
end if;
不过办法很多 此种最简单
begin
select a into v_a from tab where ...;
exception
when no_data_found then
v_a := 0;
when others then
--其他异常,由业务逻辑决定如何处理
end ;
...注意NO_DATA_FOUND异常不是对应记录相应列为null,而是没有符合条件的记录。
into iv_tag
from table
where ...
还是取数据前先做一下count比较好
select nvl(A.tag,0)
into iv_tag
from (select tag ,0 as temp table) A ,(select 0 as temp from dual) B
where A.temp (+)=B.temp
...
已试过,没有问题