ORACLE 里面怎么實(shí)現(xiàn)隨機(jī)循環(huán)？（網(wǎng)上收集整理）

你是不是想模擬一些隨機(jī)的測試數(shù)據(jù)
我提供一個(gè)不錯(cuò)的腳本給你
create or replace procedure gen_data( p_tname in varchar2, p_records in number )
authid current_user
as
? ? l_insert long;
? ? l_rows? ?number default 0;
begin

? ? dbms_application_info.set_client_info( 'gen_data ' || p_tname );
? ? l_insert := 'insert /*+ append */ into ' || p_tname ||
? ?? ?? ?? ?? ? ' select ';

? ? for x in ( select data_type, data_length,
? ?? ?? ?? ?? ? nvl(rpad(
'9',data_precision,'9')/power(10,data_scale),9999999999) maxval
? ?? ?? ?? ?? ???from user_tab_columns
? ?? ?? ?? ?? ? where table_name = upper(p_tname)
? ?? ?? ?? ?? ? order by column_id )
? ? loop
? ?? ???if ( x.data_type in ('NUMBER', 'FLOAT' ))
? ?? ???then
? ?? ?? ?? ?l_insert := l_insert || 'dbms_random.value(1,' || x.maxval || '),';
? ?? ???elsif ( x.data_type = 'DATE' )
? ?? ???then
? ?? ?? ?? ?l_insert := l_insert ||
? ?? ?? ?? ?? ?? ?'sysdate+dbms_random.value+dbms_random.value(1,1000),';
? ?? ???else
? ?? ?? ?? ?l_insert := l_insert || 'dbms_random.string(''A'',' ||
? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?x.data_length || '),';
? ?? ???end if;
? ? end loop;
? ? l_insert := rtrim(l_insert,',') ||
? ?? ?? ?? ?? ?? ?' from all_objects where rownum <= :n';

? ? loop
? ?? ???execute immediate l_insert using p_records - l_rows;
? ?? ???l_rows := l_rows + sql%rowcount;
? ?? ???commit;
? ?? ???dbms_application_info.set_module( l_rows || ' rows of ' || p_records, ''
);
? ?? ???exit when ( l_rows >= p_records );
? ? end loop;
end;
/


你只要用下面的方法就可以實(shí)現(xiàn)你隨機(jī)插入 100 條數(shù)據(jù)了
SQL> create table test( x varchar2(10) );

Table created.

SQL> exec gen_data('TEST', 100)
insert /*+ append */ into TEST select dbms_random.string('A',10) from
all_objects where rownum <= :n

PL/SQL procedure successfully completed.

SQL> select * from test;

X
----------
\JlLWWKogS
IUnoXAtHOR
...

100 rows selected.

SQL>

|----------------------------------------------------------------------------------------|
                           版權(quán)聲明  版權(quán)所有 @zhyiwww
            引用請注明來源 http://www.tkk7.com/zhyiwww   
|----------------------------------------------------------------------------------------|