oracle 随机数及相应函数

        先贴一段sql:

SELECT   *
      FROM   check_for_busi_result SAMPLE BLOCK (10) SEED (11)
     WHERE   ROWNUM <= 5;
     /*   1.sample只对单表生效，不能用于表连接、远程表、视图
          2.sample会使SQL自动使用CBO                     */
     
     /* sample  按记录采样   seed(3) 返回固定的结果集 */
      select count(*) from check_for_busi_result sample(18);
      
       SELECT   *
      FROM   check_for_busi_result SAMPLE (18)
     WHERE   ROWNUM <= 5;
     
     /* SAMPLE BLOCK (18) 按数据块采样  “采样表扫描”随机抽取10%的记录，再从中随机查询5条记录 */
     select count(*) from check_for_busi_result sample block(18) ;
     
         SELECT   *
      FROM   check_for_busi_result SAMPLE BLOCK (18) 
     WHERE   ROWNUM <= 5;
     /*  获取一个随机数
    --（0-10的整数） */
     SELECT   TRUNC (DBMS_RANDOM.VALUE (0, 10)) randomNum FROM DUAL;
     
     取表随机数据
      SELECT   *
      FROM   (  SELECT   *
                  FROM   check_for_busi_result  
              ORDER BY   DBMS_RANDOM.VALUE)
     WHERE   ROWNUM <= 3
     
      SELECT   *
      FROM   (  SELECT   *
                  FROM   check_for_busi_result
               
              ORDER BY   DBMS_RANDOM.VALUE(1,3))
     WHERE   ROWNUM <= 3
     
     SELECT   *
      FROM   (  SELECT   *
                  FROM   check_for_busi_result
                
              ORDER BY   DBMS_RANDOM.random)
     WHERE   ROWNUM <= 3
     
     
      SELECT   *
      FROM   (  SELECT   *
                  FROM   check_for_busi_result
                
              ORDER BY   sys_guid())
     WHERE   ROWNUM <= 3

 

贴个资源博客：https://blog.csdn.net/bbliutao/article/details/7727322；

转载于:https://my.oschina.net/u/3489292/blog/1787072