想迅速生成一个表格。 做了如下操作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.....
解决方案 »
- oracle 里SQL语句UNION怎么用??
- 递归返回查询中sql语句问题,谢谢
- SID 的问题---值得一看
- ora-04021 锁问题求解! 实在搞不定了 求救
- oracle 9i不能安装,不知道原因,怎么解决,xp下
- 我在装oracle 9i时,总报错:加在数据库时出错,请问是什么原因?问题怎样解决啊?谢谢!
- ORA-01033:ORACLE initalization or shutdow in progress
- SOS:安装oracle9i时出现加载数据库areasQueries出错,请指点!
- ORACLE配置的问题(在线等待!!!)
- 字段更新追加问题
- 关于用PL/SQL设置oracle自增列问题?
- 动态 列转行 or 行转列 请教
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')
v_birthday :=to_date(to_char(to_number(to_char(sysdate,'yyyymmdd'))-v_seqnextval),'yyyymmdd')
没有考虑mm和dd的限制。汗!!! 可以直接为sysdate