这个方法很多咯~比如
SQL> select * from student; STUDENTID
----------
1002SQL> select count(*) from student where studentId=1003; COUNT(*)
----------
0
SQL> select * from student; STUDENTID
----------
1002SQL> select count(*) from student where studentId=1003; COUNT(*)
----------
0
即使没有ID值时,在Pl/sql中也不会出现exception.
用count 来判断 ,肯定很SB的啦
count 要遍历怎么表吧?
如果第一个几是, 那下面的遍历就多余了吧
一楼的不能实现吗? 回楼主:
个人认为,如果数据比较少的话(比如说暂时处理的一些数据,或接口表等)可以考虑select count(*)或游标 ,如果数据量比较大的话,考虑用游标.
return number
as
num number;
begin
select count(*) into num from student where studentID=id;
return num;
end isStudent;
select count(rowid) from student where studentid='1003';
这个是索引查找
select count(*) from (select studentid from student where studentid=1003);
显式游标使用sql%notfound来判断
隐式游标使用异常no_data_found来判断
oracle 的sql写法其实很多的 大部分好的都可以在asktom上看到
在csdn上遇见的很多问题上面都有 可是大家好像不喜欢e文例子: [email protected]> create table t as select * from dba_users;Table [email protected]> insert into t select * from t;23 rows [email protected]> insert into t select * from t;23552 rows [email protected]> alter session set sql_trace=true
2 ;Session [email protected]> select count(*) from t where username='LG'; COUNT(*)
----------
[email protected]> select count(*) from dual where exists (select null from t where username='LG'); COUNT(*)
----------
[email protected]> alter session set sql_trace=false;Session [email protected]>
下面的是结果 ********************************************************************************select count(*)
from
t where username='LG'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.08 70 724 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.08 70 724 0 1Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 81 Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=724 pr=70 pw=0 time=84079 us)
2048 TABLE ACCESS FULL T (cr=724 pr=70 pw=0 time=14519 us)********************************************************************************select count(*)
from
dual where exists (select null from t where username='LG')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 4 0 0
Fetch 2 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 4 0 1Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 81 Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=4 pr=0 pw=0 time=367 us)
1 FILTER (cr=4 pr=0 pw=0 time=258 us)
1 FAST DUAL (cr=0 pr=0 pw=0 time=10 us)
1 TABLE ACCESS FULL T (cr=4 pr=0 pw=0 time=214 us)********************************************************************************