数据库造大量数据时可用模板

116 阅读 0 评论 77 点赞

我是靠谱客的博主孤独店员，这篇文章主要介绍数据库造大量数据时可用模板，现在分享给大家，希望可以做个参考。

CREATE OR REPLACE PACKAGE PKG_CREATE_TEST_DATA AS
  PROCEDURE PROC_EO_CUSTOMER_SERVICE_ORDER(CNT_NUM NUMBER);
END;
/

CREATE OR REPLACE PACKAGE BODY PKG_CREATE_TEST_DATA AS
  PROCEDURE PROC_EO_CUSTOMER_SERVICE_ORDER(CNT_NUM NUMBER) IS
    V_CNT_NUM NUMBER := 0;
    V_CNT_TM  NUMBER;
    v_LOOP    NUMBER := 0;
    v_max     number;
    v_max_id  number;
  BEGIN
    V_CNT_TM := CNT_NUM / 10000;
    select max(ECSO_ID)
      into v_max_id
      from EO_CUSTOMER_SERVICE_ORDER
     where ECSO_ID >= 10000000
       AND ECSO_ID <= 50000000;

    if v_max_id > 0 then
      v_max := v_max_id + 1;
    else
      v_max := 10000000;
    end if;

    loop
      exit when v_LOOP >= V_CNT_TM;
      v_LOOP    := v_LOOP + 1;
      V_CNT_NUM := v_LOOP * 10000;
      insert into EO_CUSTOMER_SERVICE_ORDER
        (ECSO_ID,
         CREATOR,
         CREATE_TIME,
         MODIFIER,
         MODIFY_TIME,
         REC_VER,
         REC_STATUS,
         ORG_ID,
         ECSO_ORDER_NO,
         ECSO_STATUS_CODE,
         ECSO_STATUS_NAME,
         ECSO_CUSTOMER_FLAG,
         ECSO_SHIPPER_COMPANY_NAME,
         ECSO_SHIPPER_CONTACT_NAME,
         ECSO_SHIPPER_CONTACT_PHONE,
         ECSO_ORIGINAL_CITY_CODE,
         ECSO_ORIGINAL_CITY_NAME,
         ECSO_ORIGINAL_DISTRICT_CODE,
         ECSO_ORIGINAL_DISTRICT_NAME,
         ECSO_DESTINATION_CITY_CODE,
         ECSO_DESTINATION_CITY_NAME,
         ECSO_DESTINATION_DISTRICT_CODE,
         ECSO_DESTINATION_DISTRICT_NAME,
         ECSO_ENTRUSTED_CONTENT,
         ECSO_GROSS_WEIGHT,
         ECSO_TEMPERATURE_LEVEL_NAME,
         ECSO_REMARK,
         ECSO_CAREER_TYPE,
         ECSO_CUSTOMER_MONTHLY_CARD,
         ECSO_TRANSPORT_NO,
         ECSO_FOLLOWERS,
         ECSO_FOLLOW_UP_TIME,
         ECSO_RECEIVE_FAIL_REASON,
         ECSO_ORIGINAL_PROVINCE_CODE,
         ECSO_ORIGINAL_PROVINCE_NAME,
         ECSO_DESTINATION_PROVINCE_CODE,
         ECSO_DESTINATION_PROVINCE_NAME)
        select (v_max + ROWNUM + V_CNT_NUM) ECSO_ID,
               '89003294' CREATOR,
               sysdate - rownum / 24 / 3600 CREATE_TIME,
               '89003294' MODIFIER,
               sysdate - rownum / 24 / 3600 MODIFY_TIME,
               0 REC_VER,
               0 REC_STATUS,
               100 ORG_ID,
               'LO' || to_char(sysdate, 'yyyymmdd') ||
               (v_max + ROWNUM + V_CNT_NUM) ECSO_ORDER_NO,
               CASE MOD(ABS(DBMS_RANDOM.random), 6)
                 WHEN 1 THEN
                  'OMS_ORDER_NEW'
                 WHEN 2 THEN
                  'OMS_ORDER_CANCEL'
                 when 3 then
                  'OMS_ORDER_SUBMIT'
                 WHEN 4 THEN
                  'OMS_ORDER_COMPLETE'
                 when 5 then
                  'OMS_ORDER_LANSHOU_SUCCESS'
                 ELSE
                  'OMS_ORDER_LANSHOU_FAILURE'
               END ECSO_STATUS_CODE,
               CASE MOD(ABS(DBMS_RANDOM.random), 4)
                 WHEN 1 THEN
                  '取消'
                 WHEN 2 THEN
                  '完成'
                 when 3 then
                  '新增'
                 ELSE
                  '审核'
               END ECSO_STATUS_NAME,
               CASE MOD(ABS(DBMS_RANDOM.random), 4)
                 WHEN 1 THEN
                  '1'
                 ELSE
                  '2'
               END ECSO_CUSTOMER_FLAG,
               '客户-' || (v_max + ROWNUM + V_CNT_NUM) ECSO_SHIPPER_COMPANY_NAME,
               '联系人-' || (v_max + ROWNUM + V_CNT_NUM) ECSO_SHIPPER_CONTACT_NAME,
               '电话-' || (v_max + ROWNUM + V_CNT_NUM) ECSO_SHIPPER_CONTACT_PHONE,
               (v_max + ROWNUM + V_CNT_NUM) ECSO_ORIGINAL_CITY_CODE,
               'city-' || (v_max + ROWNUM + V_CNT_NUM) ECSO_ORIGINAL_CITY_NAME,
               (v_max + ROWNUM + V_CNT_NUM) ECSO_ORIGINAL_DISTRICT_CODE,
               'district-' || (v_max + ROWNUM + V_CNT_NUM) ECSO_ORIGINAL_DISTRICT_NAME,
               (v_max + ROWNUM + V_CNT_NUM) ECSO_DESTINATION_CITY_CODE,
               'city-' || (v_max + ROWNUM + V_CNT_NUM) ECSO_DESTINATION_CITY_NAME,
               (v_max + ROWNUM + V_CNT_NUM) ECSO_DESTINATION_DISTRICT_CODE,
               'district-' || (v_max + ROWNUM + V_CNT_NUM) ECSO_DESTINATION_DISTRICT_NAME,
               'content-' || (v_max + ROWNUM + V_CNT_NUM) ECSO_ENTRUSTED_CONTENT,
               11 ECSO_GROSS_WEIGHT,
               11 ECSO_TEMPERATURE_LEVEL_NAME,
               '备注信息-' || (v_max + ROWNUM + V_CNT_NUM) ECSO_REMARK,
               CASE MOD(ABS(DBMS_RANDOM.random), 3)
                 WHEN 1 THEN
                  'FOOD'
                 when 2 then
                  'MEDICINE'
                 ELSE
                  'FOOD,MEDICINE'
               END ECSO_CAREER_TYPE,
               'card-' || (v_max + ROWNUM + V_CNT_NUM) ECSO_CUSTOMER_MONTHLY_CARD,
               'LO' || to_char(sysdate, 'yyyymmdd') ||
               (v_max + ROWNUM + V_CNT_NUM) ECSO_TRANSPORT_NO,
               '89003294' ECSO_FOLLOWERS,
               sysdate - rownum / 24 / 3600 ECSO_FOLLOW_UP_TIME,
               '失败原因-' || (v_max + ROWNUM + V_CNT_NUM) ECSO_RECEIVE_FAIL_REASON,
               (v_max + ROWNUM + V_CNT_NUM) ECSO_ORIGINAL_PROVINCE_CODE,
               'province-' || (v_max + ROWNUM + V_CNT_NUM) ECSO_ORIGINAL_PROVINCE_NAME,
               (v_max + ROWNUM + V_CNT_NUM) ECSO_DESTINATION_PROVINCE_CODE,
               'province-' || (v_max + ROWNUM + V_CNT_NUM) ECSO_DESTINATION_PROVINCE_NAME
          from dual
        connect by level <= 10000;
      COMMIT;
    END LOOP;
  END PROC_EO_CUSTOMER_SERVICE_ORDER;
END PKG_CREATE_TEST_DATA;