Oracle stored procedures BULK INSERT test data

Sometimes when you need to do a DB efficiency test, you need to simulate a lot of data. You can copy large amounts of data by executing stored procedures based on one raw data:

CREATE OR REPLACE PROCEDUREProc_msw_strsql isIINT;BEGINI:=1;  whileI<=100000LOOPINSERT     intoRep_dataf_07_0 (SELECTRep_dataf_07_itemid_seq.nextval as PKID, organ_id, report_id, item_id, Report_date, Create_date, ITEMV ALUE1, ITEMVALUE2, ITEMVALUE3, ITEMVALUE4, ITEMVALUE5, ITEMVALUE6, I          TEMVALUE7, ITEMVALUE8, ITEMVALUE9, ITEMVALUE10, ITEMVALUE11, ITEMVALUE12, ITEMVALUE13, ITEMVALUE14, ITEMVALUE15, ITEMVALUE16, ITEMVALUE17, ITEMVA          LUE18, ITEMVALUE19, ITEMVALUE20, ITEMVALUE21, ITEMVALUE22, ITEMVALUE23, ITEMVALUE24, ITEMVALUE25, ITEMVALUE26, ITEMVALUE27, ITEMVALUE28, Itemvalu          E29, ITEMVALUE30, ITEMVALUE31, ITEMVALUE32, ITEMVALUE33, ITEMVALUE34, ITEMVALUE35, ITEMVALUE36, ITEMVALUE37, ITEMVALUE38, ITEMVALUE39, ITEMVALUE4 0, ITEMVALUE41,          ITEMVALUE42, ITEMVALUE43, ITEMVALUE44, ITEMVALUE45, ITEMVALUE46, IT          EMVALUE47, ITEMVALUE48, ITEMVALUE49, ITEMVALUE50, ITEMVALUE51, ITEMVALUE52, ITEMVALUE53, ITEMVALUE54, ITEMVALUE55, ITEMVALUE56, ITEMVALUE57, ITEM          VALUE58, ITEMVALUE59, ITEMVALUE60, ITEMVALUE61, ITEMVALUE62, ITEMVALUE63, ITEMVALUE64, ITEMVALUE65, ITEMVALUE66, ITEMVALUE67, ITEMVALUE68, ITEMVA          LUE69, ITEMVALUE70, ITEMVALUE71, ITEMVALUE72, ITEMVALUE73, ITEMVALUE74, ITEMVALUE75, ITEMVALUE76, ITEMVALUE77, ITEMVALUE78, ITEMVALUE79, Itemvalu          E80, ITEMVALUE81, ITEMVALUE82, ITEMVALUE83, ITEMVALUE84, ITEMVALUE85,          ITEMVALUE86,ITEMVALUE87, ITEMVALUE88, ITEMVALUE89, ITEMVALUE90, ITEMVALUE91, ITEMVALUE92, ITEMVALUE93, ITEMVALUE94, ITEMVALUE95, ITEMVALUE96, ITEMVALUE97, IT EMVALUE98, ITEMVALUE99, ITEMVALUE100, ITEMVALUE101, ITEMVALUE102, ITEMVALUE10          3, ITEMVALUE104, ITEMVALUE105, ITEMVALUE106, ITEMVALUE107, ITEMVALUE108, ITEMVALUE109, ITEMVALUE110, ITEMVALUE111, ITEMVALUE112, ITEMVALUE113, IT EMVALUE114, ITEMVALUE115, ITEMVALUE116, ITEMVALUE117, ITEMVALUE118, Itemvalue          119, ITEMVALUE120, ITEMVALUE121, ITEMVALUE122, ITEMVALUE123, ITEMVALUE124,          ITEMVALUE125, ITEMVALUE126, ITEMVALUE127, ITEMVALUE128, ITEMVALUE129, ITEMVALUE130, ITEMVALUE131, ITEMVALUE132, ITEMVALUE133, ITEMVALUE134, ITEMVALUE135, Itemvalu         E136, ITEMVALUE137, ITEMVALUE138, ITEMVALUE139, ITEMVALUE140 fromRep_dataf_07_0WHEREPKID='332'      ); I:=I+1; ENDLOOP; COMMIT;END;

View Code

After writing the stored procedure, compile it. Then execute the code that calls the stored procedure:

begin Proc_msw_strsql; End;

View Code

Oracle stored procedures BULK INSERT test data