熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

oracle批量插入數據

2013-11-13 22:15:05  來源: Oracle 

   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;


From:http://tw.wingwit.com/Article/program/Oracle/201311/18596.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.