【Oracle】-通过package来构建存储过程

第一部分：Package定义

包头语法格式

CREATE OR REPLACE PACKAGE package_name /*包头名称*/

 IS|AS pl/sql_package_spec  /*定义过程,函数以及返回类型,变量,常量及数据类型定义*/

 

包体语法格式

CREATE OR REPLACE PACKAGE BODY package_name/*包名必须与包头的包名一致*/

 IS | AS pl/sql_package_body        /*游标,函数,过程的具体定义*/

 

注：包体是与包头相互独立的，包体只能在包头完成编译后才能进行编译。包体中带有包头中描述的子程序的具体实现的代码段。

第二部分：存储过程调用方法

SQL> EXEC package_name.PROC_ORDER_NAME(10000000);

SQL> CALL package_name.PROC_ORDER_NAME(10000000);

注：打开PL/SQL的command window 窗口

 

第三部分：package模板

---包头部分，先执行

CREATE OR REPLACE PACKAGE PKG_UNA_TEST_DATA AS

  PROCEDURE PROC_EO_INBOUND_ORDER(CNT_NUM NUMBER);

END;

---包体部分，后执行

CREATE OR REPLACE PACKAGE BODY PKG_UNA_TEST_DATA AS

  PROCEDURE PROC_EO_INBOUND_ORDER(CNT_NUM NUMBER) IS

    --1、变量定义并赋初始值

V_CNT_NUM NUMBER := 0;

    V_CNT_TM  NUMBER;

    v_LOOP    NUMBER := 0;

    v_max     number;

    v_max_id  number;

  BEGIN

    --2、求分批执行的次数

V_CNT_TM := CNT_NUM / 1000;

--3、定义主键范围和位数，并求出当前最大值

    select max(ID)--ID表主键id

      into v_max_id

      from EO_INBOUND_ORDER

     where ID >= 100000000

       AND ID <= 500000000;

 

    if v_max_id > 0 then

      v_max := v_max_id + 1;

    else

      v_max := 100000000;

    end if;

 

    loop

      exit when v_LOOP >= V_CNT_TM;

      v_LOOP    := v_LOOP + 1;

      V_CNT_NUM := v_LOOP * 1000;

      insert into EO_INBOUND_ORDER

        (ID,

         CREATOR,

         CREATE_TIME,

         MODIFIER,

         MODIFY_TIME,

         REC_VER,

         REC_STATUS,

         ORG_ID,

         ORDER_TIME,

     RECEIVE_TIME,

     ORDER_NO,

     LOGISTIC_NO,

     ORDER_TYPE_CODE,

     STATUS_CODE,

     CUSTOMER_BASE_CODE,

     CUSTOMER_BASE_NAME,

     CUSTOMER_CODE,

     WAREHOUSER_CODE,

     WAREHOUSER_NAME,

     SUPPLIER_CODE,

     SUPPLIER_NAME,

     SHIPPER_CODE,

     SHIPPER_NAME,

     STATION_CODE,

     STATION_NAME,

         CONSIGNEE_CONTACT_CODE,

     CONSIGNEE_CONTACT_NAME,

     PLAN_ARRIVAL_TIME_FM,

     PLAN_ARRIVAL_TIME_TO)

     select (v_max + ROWNUM + V_CNT_NUM) ID,

          'admin' CREATOR,

           sysdate - rownum / 24  CREATE_TIME,

           'admin' MODIFIER,

           sysdate - rownum / 24 / 3600 MODIFY_TIME,

           0 REC_VER,

           0 REC_STATUS,

           108 ORG_ID,

           sysdate+rownum/24/60/60 ORDER_TIME,

           sysdate+rownum/24/60/60 RECEIVE_TIME,

           'IN'||to_char(sysdate, 'yyyymmdd')||(10000000 + ROWNUM) LOGISTIC_NO,

           'ERP'||to_char(sysdate, 'yyyymmdd')||(10000 + ROWNUM) LOGISTIC_NO,

          CASE MOD(ABS(DBMS_RANDOM.random), 3)

                 WHEN 1 THEN

                  'AI'

                 when 2 then

                  'PI'

                 ELSE

                  'RI'

                 END ORDER_TYPE_CODE,

          CASE MOD(ABS(DBMS_RANDOM.random), 7)

                 WHEN 1 THEN

                  'OMS_ORDER_NEW'

                 WHEN 2 THEN

                  'OMS_ORDER_ISSUEING'

                 when 3 then

                  'OMS_ORDER_COMPLETE'

                 WHEN 4 THEN

                  'OMS_ORDER_CANCEL'

                 when 5 then

                  'OMS_ORDER_SUBMIT'

                 when 6 then

                  'OMS_ORDER_ISSUE_SUCCESS'

                 ELSE

                  'OMS_ORDER_ISSUE_FAIL'

                 END STATUS_CODE,

         '58773096-8' CUSTOMER_BASE_CODE,

         '顺丰优选' CUSTOMER_BASE_NAME,

         '7550626410' CUSTOMER_CODE,

         'P571CSB' WAREHOUSER_CODE,

         '杭州良渚食品冷库' WAREHOUSER_NAME,

         '70165' SUPPLIER_CODE,

         'yd-思念' SUPPLIER_NAME,

         '0207718963' SHIPPER_CODE,

         '商业客户' SHIPPER_NAME,

         'P010CDA' STATION_CODE,

         '顺义中转配送部' STATION_NAME,

         '0207718963' CONSIGNEE_CONTACT_CODE,

         '商业客户' CONSIGNEE_CONTACT_NAME,

          sysdate PLAN_ARRIVAL_TIME_FROM,

          sysdate+rownum/24/60/60 PLAN_ARRIVAL_TIME_TO

          from dual

        connect by level <= 1000;

      COMMIT;

    END LOOP;

  END PROC_EO_INBOUND_ORDER;

END PKG_UNA_TEST_DATA;