Create table
create table TL_SMS_SENDTASKLIST_
(
NUMSEQID NUMBER() not null
DATCREATETIME DATE default SYSDATE not null
DATSRCSENDTIME DATE not null
DATSRCENDTIME DATE not null
NUMSRCCLIENTID INTEGER
NUMDESTCLIENTID INTEGER
NUMMESSAGEFORMAT INTEGER not null
NUMMESSAGEPRIORITY INTEGER not null
VCDESTMOBILE VARCHAR() not null
VCSRCNUMBER VARCHAR() default
VCLINKID VARCHAR() default
VCMESSAGECONTENT VARCHAR() not null
NUMREPORTTYPE INTEGER
VCRESERVE VARCHAR() default
VCRESERVE VARCHAR() default
NUMOPERATORID INTEGER not null
NUMPRODUCTID INTEGER not null
NUMCHANNELID INTEGER default
NUMPROVINCEID INTEGER default
NUMCITYID INTEGER default
NUMCOUNTYID INTEGER default
NUMSENDGROUPID INTEGER default
NUMSRCSEQID NUMBER() default
NUMSVCID INTEGER not null
NUMMESSAGETYPE INTEGER not null
VCSERVICECODE VARCHAR() not null
NUMFEETYPE INTEGER default not null
NUMFEEVALUE INTEGER default not null
DATDEALTIME DATE
NUMDEALFLAG INTEGER not null
NUMMOSEQID NUMBER()
NUMSYSSEQID NUMBER()
NUMCOUNTMOBILE NUMBER() default not null
)
tablespace TYUSER_SMS_SENDTASKLIST
pctfree
initrans
maxtrans
storage
(
initial K
minextents
maxextents unlimited
);
Create/Recreate primary unique and foreign key constraints
alter table TL_SMS_SENDTASKLIST_
add constraint PK_TL_SMS_SENDTASKLIST_ primary key (NUMSEQID)
using index
tablespace TYUSER_SMS_SENDTASKLIST_IDX
pctfree
initrans
maxtrans
storage
(
initial K
minextents
maxextents unlimited
);
Create/Recreate indexes
create index TL_SMS_SENDTASKLIST__IDX on TL_SMS_SENDTASKLIST_ (NUMDEALFLAG NUMMESSAGEPRIORITY)
tablespace TYUSER_SMS_SENDTASKLIST_IDX
pctfree
initrans
maxtrans
storage
(
initial K
minextents
maxextents unlimited
);
创建序列
create sequence TLSEQ_SMS_SENDTASKLIST_
minvalue
maxvalue
start with
increment by
cache ;
插入数据
insert into tl_sms_sendtasklist_ (NUMSEQID DATCREATETIME DATSRCSENDTIME DATSRCENDTIME NUMSRCCLIENTID NUMDESTCLIENTID NUMMESSAGEFORMAT
NUMMESSAGEPRIORITY VCDESTMOBILE VCSRCNUMBER VCLINKID VCMESSAGECONTENT NUMREPORTTYPE VCRESERVE VCRESERVE NUMOPERATORID NUMPRODUCTID
NUMCHANNELID NUMPROVINCEID NUMCITYID NUMCOUNTYID NUMSENDGROUPID NUMSRCSEQID NUMSVCID NUMMESSAGETYPE VCSERVICECODE NUMFEETYPE NUMFEEVALUE
DATDEALTIME NUMDEALFLAG NUMMOSEQID NUMSYSSEQID NUMCOUNTMOBILE)
values (TLSEQ_SMS_SENDTASKLIST_nextval to_date( ddmmyyyy) to_date( ddmmyyyy) to_date( ddmmyyyy)
测试级联 null to_date( :: ddmmyyyy
hh:mi:ss) null null );
//批量插入数据
DECLARE
i INT;
BEGIN
i := ;
WHILE(i < )
LOOP
i := i + ;
insert into tl_sms_sendtasklist_ (NUMSEQID DATCREATETIME DATSRCSENDTIME DATSRCENDTIME NUMSRCCLIENTID NUMDESTCLIENTID NUMMESSAGEFORMAT
NUMMESSAGEPRIORITY VCDESTMOBILE VCSRCNUMBER VCLINKID VCMESSAGECONTENT NUMREPORTTYPE VCRESERVE VCRESERVE NUMOPERATORID NUMPRODUCTID
NUMCHANNELID NUMPROVINCEID NUMCITYID NUMCOUNTYID NUMSENDGROUPID NUMSRCSEQID NUMSVCID NUMMESSAGETYPE VCSERVICECODE NUMFEETYPE NUMFEEVALUE
DATDEALTIME NUMDEALFLAG NUMMOSEQID NUMSYSSEQID NUMCOUNTMOBILE)
values (TLSEQ_SMS_SENDTASKLIST_nextval to_date( ddmmyyyy) to_date( ddmmyyyy) to_date( ddmmyyyy)
测试级联 null to_date( :: ddmmyyyy
hh:mi:ss) null null );
END LOOP;
COMMIT;
END;
没有用序列
需要大量oracle测试数据时可以使用以下方法
DECLARE
i INT;
BEGIN
i := ;
WHILE(i < )
LOOP
i := i + ;
INSERT INTO TEST_TABLE(ID XM) VALUES(i 姓名 || i);
END LOOP;
COMMIT;
END;
