话不多说直接上代码
insert into student
(ID,XM,ZJH,ZL,DJSJ,XYSJ)
select rownum as ID,
dbms_random.string('x',2) as XM,
trunc(dbms_random.value(100000000000000000,999999999999999999)) as ZJH,
decode(trunc(dbms_random.value(1,3)),1,'11111111',
2, 'abcdefg',
3,'返回值可以是数字,字符串,汉字等'
)as ZL,
to_char(sysdate+rownum/24/3600,'yy-mm-dd hh24:mi:ss') as DJSJ,
systimestamp as XYSJ
from xmltable('1 to 10000000')
上面SQL是利用了Oracle数据库语法的几个实用小技巧实现的:
1、利用xmltable(在10g开始支持XML后可用),如本例中的from xmltable(‘1 to 10000000’)即表示生成1000万条数据,也可以利用Oracle特有的“connect by”树形连接语法生成测试记录,只是将
from xmltable(‘1 to xx’)
换成
from dual
connect by level <= xx;
例“level <= 10”表示要生成10记录,但是connect by level有上限,如果超出上限,系统会报,"connect