SQL> DECLARE 2 v_Count NUMBER; 3 BEGIN 4 SELECT COUNT(*) INTO v_Count FROM test WHERE 1 <> 1; 5 dbms_output.put_line('结果:' || v_Count); 6 END; 7 /结果:0PL/SQL procedure successfully completedSQL> DECLARE 2 v_Count NUMBER; 3 BEGIN 4 SELECT 1 INTO v_Count FROM test WHERE 1 <> 1; 5 dbms_output.put_line('结果:' || v_Count); 6 END; 7 /DECLARE v_Count NUMBER; BEGIN SELECT 1 INTO v_Count FROM test WHERE 1 <> 1; dbms_output.put_line('结果:' || v_Count); END;ORA-01403: 未找到数据 ORA-06512: 在line 4
条件不满足时,v_Count的值是0,其实这个问题自己测试一下就知道了
select into 语句不满足条件时,如果用的count则v_Count 为0,如果是字段,则报异常
条件不满足时,v_Count的值是执行该sql语句之前的值,不变。
SELECT COUNT(*) INTO v_Count FROM test WHERE 1 <> 1; SELECT 1 INTO v_Count FROM test WHERE 1 <> 1; ORACLE检测都是"未找到数据",但这2条语句返回的结果不一样, 第1条返回满足条件的记录数 第2条返回满足条件的结果集
2条语句并非都是"未找到数据",用count不会出现“未找到数据”异常,如下:SQL> DECLARE 2 v_Count NUMBER; 3 BEGIN 4 SELECT count(*) INTO v_Count FROM test WHERE 1 <> 1; 5 dbms_output.put_line('结果:' || v_Count); 6 7 EXCEPTION 8 WHEN OTHERS THEN 9 dbms_output.put_line('异常:' || SQLCODE || SQLERRM); 10 END; 11 /结果:0PL/SQL procedure successfully completedSQL> SQL> DECLARE 2 v_Count NUMBER; 3 BEGIN 4 SELECT a INTO v_Count FROM test WHERE 1 <> 1; 5 dbms_output.put_line('结果:' || v_Count); 6 7 EXCEPTION 8 WHEN OTHERS THEN 9 dbms_output.put_line('异常:' || SQLCODE || SQLERRM); 10 END; 11 /异常:100ORA-01403: 未找到数据PL/SQL procedure successfully completed
2 v_Count NUMBER;
3 BEGIN
4 SELECT COUNT(*) INTO v_Count FROM test WHERE 1 <> 1;
5 dbms_output.put_line('结果:' || v_Count);
6 END;
7 /结果:0PL/SQL procedure successfully completedSQL> DECLARE
2 v_Count NUMBER;
3 BEGIN
4 SELECT 1 INTO v_Count FROM test WHERE 1 <> 1;
5 dbms_output.put_line('结果:' || v_Count);
6 END;
7 /DECLARE
v_Count NUMBER;
BEGIN
SELECT 1 INTO v_Count FROM test WHERE 1 <> 1;
dbms_output.put_line('结果:' || v_Count);
END;ORA-01403: 未找到数据
ORA-06512: 在line 4
SELECT 1 INTO v_Count FROM test WHERE 1 <> 1;
ORACLE检测都是"未找到数据",但这2条语句返回的结果不一样,
第1条返回满足条件的记录数
第2条返回满足条件的结果集
2 v_Count NUMBER;
3 BEGIN
4 SELECT count(*) INTO v_Count FROM test WHERE 1 <> 1;
5 dbms_output.put_line('结果:' || v_Count);
6
7 EXCEPTION
8 WHEN OTHERS THEN
9 dbms_output.put_line('异常:' || SQLCODE || SQLERRM);
10 END;
11 /结果:0PL/SQL procedure successfully completedSQL>
SQL> DECLARE
2 v_Count NUMBER;
3 BEGIN
4 SELECT a INTO v_Count FROM test WHERE 1 <> 1;
5 dbms_output.put_line('结果:' || v_Count);
6
7 EXCEPTION
8 WHEN OTHERS THEN
9 dbms_output.put_line('异常:' || SQLCODE || SQLERRM);
10 END;
11 /异常:100ORA-01403: 未找到数据PL/SQL procedure successfully completed