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;