最初由 tangpl 发布
[B]SQL> DECLARE
2V1 VARCHAR2(10);
3BEGIN
4SELECT ENAME INTO V1 FROM EMP WHERE ENAME='SDF';
5DBMS_OUTPUT.PUT_LINE(V1);
6END;
7/
DECLARE
*
ERROR 位于第 1 行:
ORA-01403: 未找到数据
ORA-06512: 在line 4
在没找到数据时如何不让产生例外?
THX
[/B]
谢了。
不好意思,是我表达的不清楚,
我要它不产生例外,不是怎样处理例外。
我要的是下面的效果,看:
SQL> DECLARE
V1 VARCHAR2(10);
BEGIN
SELECT 'E' INTO V1 FROM DUAL
WHERE EXISTS (SELECT ENAME FROM EMP WHERE ENAME='AAAASAWSS');
if (v1='E') then
DBMS_OUTPUT.PUT_LINE(V1);
else
...
end if;
END;
8 /
DECLARE
*
ERROR 位于第 1 行:
ORA-01403: 未找到数据
ORA-06512: 在line 4
If you select something into a variable, there will always be an exception if no data found. Why do you not want to use exception?
One good exception would be no_data_found exception.
Another alternative someone is already mentioned before. You can use count(*).
For example:
Declare
v number;
Begin
select count(*)
into v
from emp
where ename='ABA';
if v=0
then dbms_output.put_line('No data is found');
else
dbms_output.put_line('There are '||v||' records found');
end if;
end;
/
谢谢各位的答复。
我想是我表达的不清楚,大家没理解我的目的:
DECLARE
V1 VARCHAR2(10);
V2 VARCHAR2(10);
V3 VARCHAR2(10);
....
BEGIN
V1='0';
V2='0';
V3='0';
...
SELECT '1' INTO V1 FROM DUAL WHERE EXISTS(SELECT * FROM TABLE1 WHERE COL1='AFD');
SELECT '1' INTO V2 FROM DUAL WHERE EXISTS(SELECT * FROM TABLE2 WHERE COL1='AFD');
SELECT '1' INTO V3 FROM DUAL WHERE EXISTS(SELECT * FROM TABLE3 WHERE COL1='AFD');
......
IF (V1='1') THEN
....
ELSE
...
END IF;
IF (V2='1') THEN
....
ELSE
...
END IF;
IF (V3='1') THEN
....
ELSE
...
END IF;
END;
所以:
1。用NO_DATA_FOUND例外不能满足要求;
2。用COUNT(*)加ROWNUM=1条件限制可以达到目的,但如上次所提的EXISTS问题一样,如果条件比较多,那程序会很长,特别是在EXISTS里SELECT较多时。
DECLARE
V1 VARCHAR2(10);
V2 VARCHAR2(10);
V3 VARCHAR2(10);
....
BEGIN
begin
SELECT '1' INTO V1 FROM TABLE1 WHERE COL1='AFD';
...........
exception when
others then
...........
end;
begin
SELECT '1' INTO V2 FROM TABLE2 WHERE COL2='AFD';
...........
exception when
others then
...........
end;
begin
SELECT '1' INTO V3 FROM TABLE3 WHERE COL3='AFD';
...........
exception when
others then
...........
end;
END;
/