sql@sql>select to_char(sysdate-mod(abs(dbms_random.random()), 365*40), 'YYYYMMDD') as birth_day, 2 to_char(sysdate-mod(abs(dbms_random.random()), 365*40), 'YYYYMMDD') as begin_date, 3 '驾驶员'||substr(sys_guid(),mod(abs(dbms_random.random()), 27),4) as name, 4 decode(mod(abs(dbms_random.random()),2),1,'F','M') as sex 5 from user_triggers 6 order by birth_day, begin_date;BIRTH_DA BEGIN_DA NAME S -------- -------- ---------- - 19730504 19901011 驾驶员84F5 F 19740504 19710415 驾驶员4137 F 19760904 19840320 驾驶员802E M 19780621 19810524 驾驶员0A00 F 19810517 20070410 驾驶员4F28 F 19820624 19910705 驾驶员E043 M 19840411 20021011 驾驶员2484 M 19850209 19770102 驾驶员A004 F 19880820 20040823 驾驶员0041 F 19911116 19910731 驾驶员3351 F 19920505 19961124 驾驶员EE04 F 19970420 19771205 驾驶员0413 F 20030205 19840525 驾驶员1B52 M 20041112 19961128 驾驶员0041 M14 rows selected.
vSex varchar2(1);
vID varchar2(18);
vName varchar2(13);
nLoop number(8);
mLoop number(3);
iLoop number(2);
vValid number(1);
nTmp number(5);
vTmp varchar2(17);
dGenDate Date;
begin
dGenDate := to_date('19790101','YYYYMMDD');
for nLoop in 1..2000 loop
vBirthday := trim(to_char(dGenDate + nLoop,'YYYYMMDD'));
fro mLoop in 1..1000 loop
vID := '120101'||vBirthday ||trim(to_char(mLoop));
vTmp := substr(vID,1,17);
nTmp := 0;
for iLoop in 1..17 loop
nTmp := nTmp + to_number(trim(substr(vTmp,iLoop,1)));
end loop;
vValid := trim(to_char(mod(nTmp,10));
vID := vID || vValid;
vName := trim(to_char(nLoop*1000+mLoop));
select DBMS_RANDOM.value() into nTmp from dual;
vSex := 'F';
if nTmp + 0.5 >1 then
vSex := 'M';
end if;
insert into 表(身份证ID,生日,姓名,性别)values(vID,vBirthday,vName,vSex);
end loop;
commit;
end loop;
往oracle里随机插入50W条记录,记录中有一个字段是“车牌号码”(格式为“京A V0668”),现在我想“京”的后面只能是A,B,C,D这四个字母;“V”的位置处可以是数字或字母,但是I和O这两个字母除外(因为跟1和0相象);“V”的后面是数字的组合。这条语句应该怎么写呢
2 to_char(sysdate-mod(abs(dbms_random.random()), 365*40), 'YYYYMMDD') as begin_date,
3 '驾驶员'||substr(sys_guid(),mod(abs(dbms_random.random()), 27),4) as name,
4 decode(mod(abs(dbms_random.random()),2),1,'F','M') as sex
5 from user_triggers
6 order by birth_day, begin_date;BIRTH_DA BEGIN_DA NAME S
-------- -------- ---------- -
19730504 19901011 驾驶员84F5 F
19740504 19710415 驾驶员4137 F
19760904 19840320 驾驶员802E M
19780621 19810524 驾驶员0A00 F
19810517 20070410 驾驶员4F28 F
19820624 19910705 驾驶员E043 M
19840411 20021011 驾驶员2484 M
19850209 19770102 驾驶员A004 F
19880820 20040823 驾驶员0041 F
19911116 19910731 驾驶员3351 F
19920505 19961124 驾驶员EE04 F
19970420 19771205 驾驶员0413 F
20030205 19840525 驾驶员1B52 M
20041112 19961128 驾驶员0041 M14 rows selected.
个人觉得如果是Date型加1,不会出现你说的情况。
另外,这段代码是随手写的,没有编译过。如果有错误,请指出来。
2 || chr(ascii('A')+ mod(abs(dbms_random.random()),4))
3 || ' '
4 || chr(ascii('A')+ mod(abs(dbms_random.random()),26))
5 || lpad(to_char(mod(abs(dbms_random.random()),10000)),4,'0') as card
6 from user_triggers;CARD
---------
京C Z5790
京C O6406
京B Y3992
京B I7493
京D J5187
京B M7427
京A E0564
京A E6637
京B E2871
京C S7859
京C C7431
京D T3931
京B J4480
京A B432314 rows selected.I和O的我没有去掉, 你只需要插入之后再把I和O的删除就可以了.
我在帖子的管理后台能看到给你们的分数,怎么在前台看不到呢?