在SQL server里面判断纪录是否存在:if exists(select idx from table where idx=10)
begin
........
endOrACLE里面这么判断:
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你们一般用什么方法进行这类判断?
begin
........
endOrACLE里面这么判断:
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你们一般用什么方法进行这类判断?
select count(*) into bolexists from table where idx=10
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)这种情况下,按照一楼的方法需要定义三个变量,实在感觉有点多余,想问一下,
大侠们遇到这种情况一般如何解决?
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;
WHERE EXISTS (select idx from table1 where idx=10 or idx=11 or idx=12)这样行嘛?
SELECT count(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)
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;
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;