oracle判断数据是否存在吗_如何判断数据是否在表中存在？

我们从这里可以看出fetch  cursor 并不比 count .....  rownum = 1  快

SQL> set serverout on

SQL> declare

2  n_cursor number;

3  n_count_rownum  number;

4  n_count  number;

5  n_time1 number;

6  n_time2 number;

7  n_time3 number;

8  n_time4 number;

9  cursor c is select object_id from t;

10  begin

11  n_time1 := dbms_utility.get_time;

12  select count(*) into n_count from t;

13  n_time2 := dbms_utility.get_time;

14

15  for i in 1..100000 loop

16  open c;

17  fetch c into n_cursor;

18  close c;

19  end loop;

20  n_time3 := dbms_utility.get_time;

21

22  for i in 1..100000 loop

23  select count(*) into n_count_rownum from t where rownum = 1;

24  end loop;

25

26  n_time4 := dbms_utility.get_time;

27

28  dbms_output.put_line('count: '||to_char(n_count));

29

30  dbms_output.put_line('count all  time : '||to_char(n_time2 - n_time1));

31  dbms_output.put_line(' cursor time: '||to_char(n_time3 - n_time2));

32  dbms_output.put_line('count rownum = 1 time: '||to_char(n_time4 - n_time3));

33

34

35  end;

36  /

count: 25377    -----------  t 表总记录数

count all  time : 1  ----------  count  all  消耗时间

cursor time: 2497   ----- 10万次通过 fetch  cursor 判定是否有记录时间

count rownum = 1 time: 2352  ---------  10万次通过 select count(*) from T where rownum = 1 判定是否有记录消耗时间

PL/SQL procedure successfully completed.

SQL>

下面是当表中不存在记录的时候消耗的时间

SQL> truncate table t;

Table truncated.

SQL> declare

2  n_cursor number;

3  n_count_rownum  number;

4  n_count  number;

5  n_time1 number;

6  n_time2 number;

7  n_time3 number;

8  n_time4 number;

9  cursor c is select object_id from t;

10  begin

11  n_time1 := dbms_utility.get_time;

12  select count(*) into n_count from t;

13  n_time2 := dbms_utility.get_time;

14

15  for i in 1..100000 loop

16  open c;

17  fetch c into n_cursor;

18  close c;

19  end loop;

20  n_time3 := dbms_utility.get_time;

21

22  for i in 1..100000 loop

23  select count(*) into n_count_rownum from t where rownum = 1;

24  end loop;

25

26  n_time4 := dbms_utility.get_time;

27

28  dbms_output.put_line('count: '||to_char(n_count));

29

30  dbms_output.put_line('count all  time : '||to_char(n_time2 - n_time1));

31  dbms_output.put_line(' cursor time: '||to_char(n_time3 - n_time2));

32  dbms_output.put_line('count rownum = 1 time: '||to_char(n_time4 - n_time3));

33

34

35  end;

36  /

count: 0

count all  time : 0

cursor time: 2902

count rownum = 1 time: 2427

PL/SQL procedure successfully completed.

SQL>