想迅速生成一个表格。 做了如下操作create table employee(
employeeId char(8) primary key,
employeeName varchar(20),
gender varchar(6),
birthday date,
address varchar(200),
salary number,
departmentId char(3)
)
create sequence employeeId_seq
start with 1
maxvalue 9999
cache 10;
create or replace procedure employeeInsert_job
as
v_employeeId   employee.employeeId%type;
v_employeeName   employee.employeeName%type;
v_gender   employee.gender%type;
v_birthday   employee.birthday%type;
v_address  employee.address%type;
v_salary   employee.salary%type;
v_departmenId   employee.departmentId%type;
v_seqnextval       number;begin
select employeeId_seq.nextval into v_seqnextval from dual;
v_employeeId  := trim(to_char(v_seqnextval,'00000000'));
v_employeeName := 'king'||trim(to_char(v_seqnextval,'0000'));
v_birthday := to_date(to_char(to_number(to_char(sysdate,'yyyy-mm-dd'))-v_seqnextval),'yyyymmdd');
v_address  := 'NO.'||trim(to_char(v_seqnextval,'0000'))||' LongDongRoad';
v_salary := trunc(dbms_random.value(3300,30000));

         ----模5分成五个department
if(mod(v_seqnextval,5)= 0)then
v_departmenId := '001';
elsif(mod (v_seqnextval,5) = 1)then
v_departmenId := '002';
elsif(mod (v_seqnextval,5) = 2)then
v_departmenId := '003';
elsif(mod (v_seqnextval,5) = 3)then
v_departmenId := '004';
else
v_departmenId := '002';
end if;

----奇偶数产生性别
if (mod(v_seqnextval,2) = 0)then
v_gender := 'female';
else
v_gender := 'male';
end if;
insert into employee values(v_employeeId,v_employeeName,v_gender,v_birthday,v_address,v_salary,v_departmenId);
commit;
end;

alter system set job_queue_processes = 100;
declare
 v_job number;
 begin
  dbms_job.submit(v_job,'employeeInsert_job();',sysdate,'sysdate + (0.01/(24*60*60))');
  commit;
end;job failed after 17 try.....

解决方案 »

  1.   

    'sysdate + (0.01/(24*60*60))'???没0.01秒执行一次?执行的间隔时间要大于存储过程的执行时间
      

  2.   

    正解。。store procedure的时间是0.14 设为0.2好用 不过源代码里面有个小问题
    to_date(to_char(to_number(to_char(sysdate,'yyyy-mm-dd'))-v_seqnextval),'yyyymmdd')
    改为
    to_date(to_char(to_number(to_char(sysdate,'yyyymmdd'))-v_seqnextval),'yyyymmdd')
      

  3.   

    代码还有问题还是在
    v_birthday  :=to_date(to_char(to_number(to_char(sysdate,'yyyymmdd'))-v_seqnextval),'yyyymmdd')
    没有考虑mm和dd的限制。汗!!! 可以直接为sysdate