第一部分: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;