Oracle SQLServer 的随机数问题[z]

From: http://www.heysky.net/archives/2006/10/oracle_sqlserver_random.html

 

在数据库操作中经常会碰到使用随机数的问题，几天前翔哥来问我关于 SQLServer 批量产生随机数的问题，记得之前他也问过我 Oracle 下面随机数的问题，当时找到了一些资料但没来得及整理，今天正好把 Oracle 和 SQLServer 下的随机数问题整理一下。

1. 产生随机数

Oracle
     Oracle 下随机数可以用 DBMS_RANDOM 包来实现，他调用 Oracle 内部随机数生成器来产生随机数，具体使用可以参考文档，另外 psoug 上有更详细的参考。

SQLServer 
     SQLServer 下可以用 RAND 函数产生随机数，不过这个函数似乎只能一次产生一个随机数，如果大批量产生随机数，都是重复的（在 Oracle 中使用 DBMS_RANDOM 产生的随机数不太会重复）：

SELECT TOP 5 RAND()
FROM sysobjects

0.72910389475358373
0.72910389475358373
0.72910389475358373
0.72910389475358373
0.72910389475358373

        之后 google 了一下，找到了邹建的一篇文章，巧妙地通过 CHECKSUM(NEWID()) 产生随机数，并用 RIGHT 函数获得其中几位（我觉得用 Left 更好，这样可以防止首位为 0，从而避免插入数据时位数变少），这样不但很好地实现了批量产生随机数，效率也不错。

2. 随机从表中取数

Oracle
      之前就是在 Oracle 下碰到了这个问题才去研究随机数，发现非常有意思，方法可以概括为两大类，一类就是根据随机数来 Order by 从而将数据随机排列。这类方法又可以分为两种方法，一种就是用 dbms_random 产生随机数：

select *
  from (select * from t order by dbms_random.value)
 where rownum < 50;

根据 jametong 所说，由于随机数发生器本身初始化需要时间，而且产生随机数的过程中会频繁读取数据库，所以这种方法效率最差。第二种方法是用 dbms_utility.get_hash_value 产生随机数：

select *
  from (select dbms_utility.get_hash_value(to_char(dbms_utility.get_time) ||
                                           tb1.col1,2,1048576) rand_num,
               a.*
          from tb1
         order by rand_num)
 where rand_num <= 50;

这种方法效率稍好。第二类方法就是用 sample 对表做采样，这是效率最高的方法：

SELECT emp FROM emp SAMPLE(10);

SAMPLE(n) 是一个很有用的方法，它随机从表中抽取 n% 的数据，但并不是一定是 n% 可能小于该值，所以如果总共 1000 行数据，要随机取 100 行，最好将 n 设得大于 10。另外，这种方法只能用于一个表，from 后不能有多个表。

SQLServer 
     SQLServer 下只想到了 Order by  的方法，由于 rand() 产生重复，所以还是可以用 NEWID() 的方法：

评论

5 楼 liudaoru 2008-06-27  

构造指定分为的整数：
select 77 + floor(dbms_random.value*15) from dual;

4 楼 liudaoru 2008-06-27  

1、如果dbms_output.put_line的内容不能显示，需要在命令行中先敲入set serveroutput on;只要敲一遍就好，之后dbms_output.put_line的内容就能显示出来了；
2、dbms_output.put_line每行只能显示255个字符，超过了就会报错，报错内容如下
      ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line

3 楼 liudaoru 2008-06-27  

From: http://www.cnblogs.com/happyday56/archive/2007/06/22/793102.htmlDeclare
p_SN int := 1;
Begin
Loop
Exit When (p_SN > 15);
DBMS_OUTPUT.PUT_LINE(p_SN);
p_SN := p_SN + 1;
End Loop;
End;

2 楼 liudaoru 2008-06-27  

loop循环：
create or replace procedure pro_test_loop is
i number;
begin
i:=0;
loop
  i:=i+1;
  dbms_output.put_line(i);
  if i>5 then
    exit;
  end if;
end loop;
end pro_test_loop;

while循环：
create or replace procedure pro_test_while is
i number;
begin
i:=0;
while i<5 loop
  i:=i+1;
  dbms_output.put_line(i);
end loop;
end pro_test_while;

for循环1：
create or replace procedure pro_test_for is
i number;
begin
i:=0;
for i in 1..5 loop
  dbms_output.put_line(i);
end loop;
end pro_test_for;

for循环2：
create or replace procedure pro_test_cursor is
userRow t_user%rowtype;
cursor userRows is
select * from t_user;
begin
for userRow in userRows loop
    dbms_output.put_line(userRow.Id||','||userRow.Name||','||userRows%rowcount);
end loop;
end pro_test_cursor; 

1 楼 liudaoru 2008-06-27  

关于Oracle取整的函数分别有以下几种:

　  1.取整(大) 
　　select ceil(-1.001) value from dual

　　2.取整（小）
　　select floor(-1.001) value from dual

　　3.取整（截取）
　　select trunc(-1.002) value from dual

       4.取整(舍入)
       select round(-1.001) value from dual