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;