oracle 中随机取一条记录的两种方法

 

V_COUNT INT:=0;

V_NUM INT :=0;

 

1:TBL_MYTABLE 表中要有一个值连续且唯一的列FID

 

BEGIN

SELECT COUNT(*)

INTO V_COUNT

FROM TBL_MYTABLE;

 

SELECT TRUNC(DBMS_RADOM.VALUE(1,V_COUNT+1)) INTO V_NUM FROM DUAL;

 

SELECT *

FROM TBL_MYTABLE T

WHERE T.FID=V_NUM;

 

EXCEPTION

WHEN NO_DATA_FOUND THEN

NULL;

END;

 

 

 

2:第二种方法很简便

BEGIN

SELECT *

FROM

(

SELECT *

FROM TBL_MYTABLE T

ORDER BY DBMS_RADOM.VALUE();

)

WHERE ROWNUM<2;

 

EXCEPTION

WHEN NO_DATA_FOUND THEN

NULL;

END;