当前位置: 代码迷 >> Oracle开发 >> exists 遇到的有关问题
  详细解决方案

exists 遇到的有关问题

热度:469   发布时间:2016-04-24 07:55:02.0
exists 遇到的问题
在SQL   server里面判断纪录是否存在:

if   exists(select   idx   from   table   where   idx=10)
begin
      ........
end

OrACLE里面这么判断:
DECLARE
    BOLEXISTS   NUMBER;
BEGIN
      BOLEXISTS   :=   0;            
      SELECT   1   INTO   BOLEXISTS   FROM   DUAL            
      WHERE     EXISTS   (select   idx   from   table   where   idx=10);
      DBMS_OUTPUT.put_line(BOLEXISTS);
END;

IF   BOLEXISTS   =   1   THEN
BEGIN
      ......
END;

但是,当select   idx   from   table   where   idx=10   不存在时,因该BOLEXISTS=0,
我这边在Pl/sql里面竟然返回
ORA-01403:   no   data   found
ORA-06512:   at   line   5

你们一般用什么方法进行这类判断?


------解决方案--------------------
一句话搞定,count(*)不会出现空的情况
select count(*) into bolexists from table where idx=10
------解决方案--------------------
或用case
------解决方案--------------------
可以利用异常段赋值
DECLARE
BOLEXISTS NUMBER;
BEGIN
SELECT 1 INTO BOLEXISTS FROM DUAL
WHERE EXISTS (select idx from table1 where idx=10)
or EXISTS (select idx from table2 where idx=11)
or EXISTS (select idx from table3 where idx=12)
DBMS_OUTPUT.put_line(BOLEXISTS);
exception
when no_data_found then
BOLEXISTS := 0;
END;
IF BOLEXISTS = 1 THEN
BEGIN
......
END;
------解决方案--------------------
SELECT count(*) INTO BOLEXISTS FROM DUAL
WHERE EXISTS (select idx from table1 where idx=10 or idx=11 or idx=12)

这样行嘛?
------解决方案--------------------
declare
v_count number;
begin
select count(idx)
into v_count
from table1
where idx in (10, 11, 12);

if v_count = 0 then
...
else
...
end if;

end;
------解决方案--------------------
异常判断,支持adaizi1980(阿代)
DECLARE
BOLEXISTS NUMBER;
BEGIN
BEGIN
SELECT 1 INTO BOLEXISTS FROM DUAL
WHERE EXISTS (select idx from table1 where idx in (10, 11, 12))
DBMS_OUTPUT.put_line(BOLEXISTS);
exception
when no_data_found then
BOLEXISTS := 0;
END;
......
END;

  相关解决方案